Re: Interview Index problem

"aman.oracle.dba" <aman.oracle.dba_at_gmail.com> wrote in message news:1148934219.754175.228340_at_j33g2000cwa.googlegroups.com...
> Hello DBAs,
>
> "select empno,ename,sal,deptno from emp where deptno=10 and sal>1000"
>
> in the query given above I have 2 b-tree indexes on deptno and sal and
> using CBO, pls tell me oracle will use which index and
> why...................
>

The answer depends on the data volume and scatter, collection of statistics, and version of Oracle

If 1,000 is a very high salary, paid to just a few people, then the optimizer might use just the index on sal. If there are only a few people in department 10 in a very large company and there is a histogram on deptno then the optimizer might use the index on deptno. In circumstances where neither index seems to be a good individual choice, 9i might do a b-tree/bitmap conversion on the rowid ranges from both indexes, do a bitmap AND, then convert back to rowids to visit the table.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html