CODEWITHSUNDEEP
sqloracle-database
Jordan Parmer
Jordan Parmer

Reputation: 37254

In Oracle, will a compound index still be used if a where clause contains some additional fields?

So let's say I have a compound index on a table that indexes 4 fields.

create index idx_comp_index
on mytable ( fielda, fieldb, fieldc, fieldd );

If I query that table using all four of those fields in my where clause plus an additional field or two, will the index still be used?

select *
from mytable
where fielda = 'i'
  and fieldb = 'love'
  and fieldc = 'vim'
  and fieldd = 'so'
  and fielde = 'much';  -- additional field not indexed

Upvotes: 2

Views: 200

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132750

Certainly, if this uses an index:

select *
from mytable
where fielda = 'i'
  and fieldb = 'love'
  and fieldc = 'vim'
  and fieldd = 'so';

then I see no reason why this would not:

select *
from mytable
where fielda = 'i'
  and fieldb = 'love'
  and fieldc = 'vim'
  and fieldd = 'so'
  and fielde = 'much';  -- additional field not indexed

If instead of "select *" you had "select fielda" then the query without fielde might be answered using only the index, whereas the query with fielde could not.

Upvotes: 0

dcp
dcp

Reputation: 55467

The answer is, "it depends". The best way to determine such things is to look at the optimizer plan, which will tell you the query plan, along with what indexes are being used.

Check out "explain plan" on google.

Whether or not Oracle will use an index basically comes down to whether the optimizer determines that it's more expensive to use the index than not to use it. In some cases, the optimizer may determine it's faster not to use the index, and most of the time, the optimizer is exactly right.

The other things to take into account are to make sure you have up to date statistics on your tables, since that is what the optimizer uses to determine the query plan.

Upvotes: 3

Related Questions