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

Hi Steve,

Without an explain plan for the problem query, it is really difficult to say. Can you request that whoever does have the details and/or access run an explain plan on the query and post the results?

Regards,

Steve Received on Sat Mar 03 2007 - 07:27:27 CST