Re: using INDEX or not with same WHERE clause

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 Received on Wed Dec 19 2001 - 16:07:44 CST