Indexing methodology in RBO
If I have independent non-unique indexes on two columns and both are being
referred in the where clause of a query, which one will be given preference?
Will this rule be consistent in RBO?
select col1, col2, col3, col4 from am79 where col1 = 1 -- col1 is indexed. and col2 = 'amar'; -- col2 is indexed.
Oracle uses both the indexes for the query. The index preference depends on the position of the column in where clause and the operator being used (and/or). I have created the following table which should resolve most of the doubts about Oracle's methodology in RBO environment. The table shows what index oracle will prefer if column1 and column2 have indexes on them (or both combined) and if both are being used in the where clause, with "=" operator:
------------------------------------------------------------------------------------- Type of indexes on: | Index used by column1(a) column2(b) column1+column2(c) | Oracle 8.1.7.1 ------------------------------------------------------------------------------------- non-unique non-unique c non-unique non-unique a + b non-unique non-unique non-unique c unique non-unique a unique non-unique a unique unique b (the most recent index created!). unique unique unique c ------------------------------------------------------------------------------------- -In case of non-unique, both indexes are used. -In case of unique, indexes are not combined for execution plan, any one is taken. -I am doubtful about 2nd last case, but this is how it is working as of now! -Don't create bitmap & function-based indexes, they will not work. -Preference is given to the index available with the "=" operator column, than others. -------------------------------------------------------------------------------------Best viewed in medium text size. Please refresh this page (F5) to view the latest information.
This page was create on 15-dec-2000 and last updated on 15-dec-2000.
please forward all queries to amar_padhi@fastmail.fm