Re: Index on more than one column

Kjetil Skotheim wrote:
>
> If for instance I have done a
>
> create index on person (lastname, firstname)
>
> Will this index be ignored when I only use lastname= in a
> where clause?
>
> Will this index be ignored when I only use firstname= in a
> where clause?
>
> (please resond in email if you can).
>
> --
> Kjetil Skotheim, 22239859 (hjemme), 22852823 (jobb)

Kjetil,
If using rule-based optimization, Oracle will always use the index if the leading part of the index is used in the where clause. ( assuming that there are no other higher level indexes defined ). Oracle will not use the index if the leading part of the index is not used.

If running cost-based optimization and if statistics are gathered for the table/index, then Oracle will make a decision based on the cost of executing the SQL. It will also base its decision on the actual data in the table and the where clause.

Consider this scenario : table emp has 1 million rows - min( emp_no ) = 1 and max( emp_no ) = 999,999 - say emp_no is the primary key.

1 . For the SQL "select 'x' from emp where emp_no > 10", Oracle ( running cost-based opt ) WILL NOT make use of the primary key index and will choose to do a full table scan.

2 . For the SQL "select 'x' from emp where emp_no < 10", Oracle ( running cost-based opt ) WILL make use of the primary key index and will choose to do an index scan.

In the above examples, Oracle has based its decision to use the index or otherwise on the data values/distribution. Oracle uses the data in user_tables, user_tab_columns to get info on the actual data spread.

Try running an explain plan on your SQL and check frm your DBA as to what is the optimizer mode. You may want to alter the optimizer_goal for the session before doing the explain.

Hope all this helps

-- 
Cheers

Sridhar Subramaniam
Avion Consulting Services
Sydney - Australia
Email : ssubrama_at_nibucorp.ccdn.otc.com.au / avion_at_ozemail.com.au

Disclaimer : All opinions are truly and just mine.