Re: Interview Index problem

aman.oracle.dba wrote:
> 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..................

Index over dept will be used .. CBO feels that selecting based on dept=10 will be of lesser cost as there are only 3 employees of this dept (assuming the std emp table in scott schema) Than the cost of selecting sail > 1000 as there are more employees with salary > 1000, so therefore .. even if you reverse the conditions (i.e. sal> 10000 and dept=10), the index of dept will be used.

I hope i'm clear.

Sachin Received on Tue May 30 2006 - 12:54:57 CDT