Re: Distinct with index use

On Mar 5, 2:08 am, "peter" <hhh.datab..._at_gmail.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

Depending on your data distribution, volume, frequency of the same query requesting distinct/group by information, a materialized view may help: basically you'd pay the price for the full table/index scan once and reuse it multiple times by storing it in the materialized view and without having to change the query (due to query rewrite). Now, if the data in the main table is very dynamic, the materialized view may not be the best option, so you'd have to analyze and test whether this is applicable to your environment.

Valentin Received on Mon Mar 05 2007 - 09:57:55 CST