Re: Which index to create ?

well,

  i have optimized many statements before in my application and the biggest performance gains occurred whenever i was able to get rid of a full table scan.

  I thought that when there is an index on the B column it would be easier for Oracle to figure out the different B values in the table ... instead of going through all the rows one might just pick the different index values. oh well, am i too naive here ? is a full tablescan indeed inevitable ?

"Keith Jamieson" <keith_jamieson_at_hotmail.com> schrieb im Newsbeitrag 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 - 06:43:59 CST