Re: Distinct with index use

On Mar 3, 2: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

Oracle sometimes has to use a sort to eliminate duplicates with a distinct. Could it be that one of your constraints has noduplicates so doesn't have to sort, where sorting might be more favorable to a full scan? Similar to Charles' (excellent!) demo with nulls.

jg

--
@home.com is bogus.
"In its dramatic 1972 The Limits to Growth report, the group of
prominent experts known as The Club of Rome wrote that only 550
billion barrels of oil remained and that they would run out by 1990."
Foreign Affairs, 2000.
"More recently, Cambridge Energy Research Associates, an energy
consultant, put the estimate at 4.8 trillion barrels. " - New York
Times News Service, today.