Re: Which index to create ?

The index is not used if the column b allows NULL values. Change the column to not null if you can.
Then index full scan or index fast full scan can be used for finding distinct values. Note that you need CBO for that.

Another way would be to create a materialized view on this distinct query and set query_rewrite to true. This requires some care, since MV won't be used if it's contents get out of sync with table contents (in case query_rewrite_integrity is not set to stale_tolerated).

Tanel.

"Keith Jamieson" <keith_jamieson_at_hotmail.com> wrote in message news:JSYAb.3668$nm6.19128_at_news.indigo.ie...
> What makes you think that the full tablescan is the performance
bottleneck?
> As far as I can see you are selecting all the rows in the table therefore
a
> full table scan is quite appropriate.
>
> Keith Jamieson
>
>
>
> "André Hartmann" <andrehartmann_at_hotmail.com> wrote in message
> news:3fd43484$1_at_olaf.komtel.net...
> > Hi,
> >
> > I have a question regarding the tuning of one of my SQL statements.
> > Suppose there is a table X in my Schema which has columns A, B, C (all
> > INTEGER) where A is the primary key. Suppose further that the table is
> > pretty large: 10 million rows or more. Quite frequently I want to do
this
> > statement:
> >
> > SELECT DISTINCT B FROM X
> >
> > The explain plan tells me there is a full table scan on X. So I thought
it
> > might help to create an index on the B column but I still get a full
table
> > scan. I believe that the full table scan is the main performance
> > bottleneck... how can I get rid of the full table scan ?
> >
> > André
> >
> >
>
>
Received on Mon Dec 08 2003 - 12:19:50 CST