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

Question

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

Answer 1

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.

Answer 2

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.