oraclearora_at_googlemail.com wrote:
> 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
You are both clear and incorrect. Reread Jonathan's response. Much as
Connor was able to create any hit ratio desired one can easily modify
the data and optimizer settings to either use or not use the indexes.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue May 30 2006 - 14:23:25 CDT
