CBO choses wrong path - why???

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 Received on Thu Apr 07 2005 - 19:57:01 CDT