Re: Which index to create ?

Well, there are all sorts of tricks and techniques you can follow. I suppose in your case you can create an index on the b column and then use an index hint, or alternatively force the use of the rule based optimizer, by applying the rule hint.
Well Full Table scans can be a problem, there are many times when a full table scan is more efficient than an index scan.

There is another technique, which is by adding a where clause such as where 1 = 1
or in your case where B = B.

There are a few pertinent questions to ask such as:

How big is this table? (How many rows)
How long does it take to retrieve the rows without the distinct, compared to with a distinct.

Are rows regularly deleted from the table (Not truncated). This can have the effect of leaving a large number of empty oracle blocks which oracle has to read in order to perform a full table scan, especially if the table previously contained a large number of rows.

Have you performed a trace (tkprof) to determine if there are any other factors affecting the table, eg Network, etc.

"André Hartmann" <andrehartmann_at_hotmail.com> wrote in message news:3fd47210$1_at_olaf.komtel.net...
> 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 - 09:02:45 CST