On Mar 3, 6:33 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
Sorry to ask question in between but this question related to this
question only.
Is there any way to increase the speed of distinct and group by
functions. As here also steve is trying to increasing the speed of
distinct.
Received on Mon Mar 05 2007 - 01:08:13 CST
