Re: using INDEX or not with same WHERE clause

"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 Received on Wed Dec 19 2001 - 20:09:14 CST