Re: using INDEX or not with same WHERE clause

"Lionel Mandrake" <nobody_at_nospam.nowhere.nohow> wrote in message news:<eLbU7.22672$Z03.11445968_at_news1.rdc1.mi.home.com>...
> "Sybrand Bakker" <oradba_at_sybrandb.demon.nl> wrote in message
> news:kk322ussnuvk9j4e9qsp0r7sukovj0uib8_at_4ax.com...
> > On 12 Dec 2001 07:08:39 -0800, nronayette_at_alphacsp.com (Nicolas
> > Ronayette) wrote:
> >
> > >Hello group
> > >
> > >I'm Working on Oracle parallel Server (2 nodes) 8.1.7.
> > >
> > >I've got a big table (>500 000 rows)
> > >
> > >I'm using EXPLAIN PLAN and plan_table to understand how CBO execute my
> > >query. I've done an "analyze table tlogs estimate statistics" before.
> > >
> > >Here is my query : select count(*) from tlogs where column1='A VALUE';
> > >
> > >This give me the result '9' in six second.
> > >
> > >and when i do a
> > >
> > >select column2 from tlogs where column1='A VALUE';
> > >
> > >This give me the nine rows in more than 2 minutes.
> > >
> > >Explain PLAN tell me that in the first query, the index on column1 is
> > >used, and in the second case, the index on column1 is NOT use and there
> > >is a full table scan.
> > >
> > >I don't understand why Oracle choosing an FTS in the second case (with
> > >the same WHERE condition)
> > >
> > >Thanks for your help.
> >
> > In the second case the table record needs to be selected, in the first
> > case it isn't selected.
> > The optimizer decided a full table scan is cheaper in terms of I/O
> > This depends on
> > the number of I/O for an index range scan (assuming the index is not
> > unique) vs a full table scan
> > In turn this depends on the selectivity of the index and (as it is a
> > hardcoded literal) the presence of a histogram for that column
> > and ...
> > *accurate* statistics.
> > If you set event 10051
> > (alter session set event='10051 trace name context forever, level 1')
> > the optimizer will dump an explanation of it's stupid decisions to the
> > trace file. Further details on this in the book of Jonathan Lewis
> > Practical Oracle 8i
> >
> > Hth
> >
> >
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > To reply remove -verwijderdit from my e-mail address
>
>
> > If you set event 10051...
> I think Sybrand means event '10053', not '10051'...
>
> > the optimizer will dump an explanation of it's stupid decisions...
> I take it you aren't happy with the CBO's decision algorithms?
>
> -LM

I have seen many cases where there is no reasonable explanation at all for not using an index, even if you tried everything to force using the correct index.
I admit the database version was 7.3.4 and the optimizer_index_cost_adj parameter would have come in handy. However, I still find the CBO somewhat unpredictable.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Thu Dec 20 2001 - 04:25:50 CST