Re: CBO choses wrong path - why???

"Vsevolod Afanassiev" <vafanassiev_at_yahoo.com> wrote in message news:1112921821.121560.214430_at_z14g2000cwz.googlegroups.com...
>I have a strange case of CBO chosing path that does not make any sense.
> In the simplified form the query is
>
> select * from TabA where col1 = :b1 order by ID;
>
> That's all. No joins, subqueries, one can hardly have a simpler query.
> Here ID is a primary key (naturally with unique index),
> while col1 is nearly unique (something like 29,000 values
> for 31,000 rows), and there is an index on "col1". Statistics has been
> collected and correct, I checked DBA_TABLES, DBA_INDEXES,
> DBA_TAB_COLUMNS.
>
> One would expect INDEX (RANGE SCAN) on col1. Instead I get
> following:
>
> SELECT STATEMENT GOAL: CHOOSE
> TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'SERVICE_HISTORY'
> INDEX GOAL: ANALYZED (FULL SCAN) OF 'P_SERVICE_HISTORY' (UNIQUE)
>
> where P_SERVICE_HISTORY is primary key (index on ID column).
> In other words it wants to order THE WHOLE TABLE first, and then select
> one row from it.
>
> If statistics is dropped then RBO takes over and does the right thing.
>
> Oracle 9.2.0.5.0 (64-bit) on Sun SPARC Solaris.
> There is nothing special in init.ora - db_file_multiblock_read_count,
> optimizer_index_cost_adjust
> and similar parameters are either at default values or close.
> The table does not have any LONG, BLOB, etc columns, not partitioned,
> etc.
>
> One more small detail: the path above has been obtained by setting
> event 10046 on a session created by 3-rd party program (written in C).
> I wasn't able to re-produce the problem in SQL*Plus, even with bind
> variables.
>
> Thanks
> Sev
>

It is technically possible for the optimizer to decide (based on the data distribution) that it is cheaper to use the index to avoid sorting the data. There are a couple of hidden parameters to fine-tune the algorithm - but given your comments about a 3rd part app doing it when SQL*Plus does not it may be that the app changes the optimizer mode

prior to 9i, a command to

    alter session set optimizer_mode = first_rows; could make this happen in far too many cases.

The 9i variant

    alter session set optimizer_mode = first_rows_1; is much safer.

The other option - check the bind type of :b1 and the column type on col1; it is possible that there is a bind type mismatch in the C which is making the index irrelevant.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005