Path: news.f.de.plusline.net!news-fra1.dfn.de!news-stu1.dfn.de!news.germany.com!postnews.google.com!8g2000cwh.googlegroups.com!not-for-mail
From: "Steve Robin" <ocmaman@gmail.com>
Newsgroups: comp.databases.oracle.server
Subject: Distinct with index use
Date: 3 Mar 2007 02:27:34 -0800
Organization: http://groups.google.com
Lines: 20
Message-ID: <1172917654.070600.151320@8g2000cwh.googlegroups.com>
NNTP-Posting-Host: 124.7.66.2
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1172917666 25378 127.0.0.1 (3 Mar 2007 10:27:46 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Sat, 3 Mar 2007 10:27:46 +0000 (UTC)
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: 8g2000cwh.googlegroups.com; posting-host=124.7.66.2;
   posting-account=4oCEww0AAAAsSv-RtCnmY2wDAzH-T9gL
Xref: news.f.de.plusline.net comp.databases.oracle.server:194917

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