Re: Distinct with index use

On Mar 3, 5:27 am, "Steve Robin" <ocma..._at_gmail.com> wrote:
> select distinct dname from scott.dept;
> In this query oracle is not using index on dname.
> select distinct ename from scott.emp;
> In this query oracle is using index on ename and improving
> speed.
> How it is possible that some time oracle uses index and some
> time it doesn't. Can anyone pls help me and tell, when oracle uses
> index in distinct. Is index help to improve distinct query.
> If not then how can I increase speed because distinct ename are 315
> and total rows are 80546220. Right now it is taking huge time to
> distinct. Please give any suggestion.
>
> I apologise, I don't have full details right now. But you understand
> the situation and pls help me.
>
> Thanks
>
> Oracle : 9.2.0.8
> OS : Sun Solaris , Windows XP

The simple reason is that the cost based optimizer believes, based on the statistics in the system and the initialization parameters that are in effect, that the full tablescan will be less expensive than the index scan.

How long has it been since you used the DBMS_STATS package with the CASCADE option set to TRUE? Does the DEPT.DNAME column permit nulls while the EMP.ENAME does not?

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Mar 03 2007 - 07:33:36 CST