Oracle does not use an index

Dear group readers,

at first the system:

Database: Oracle 10g (10.1.0.4)
2-Processor machine HP/UX 11.11 64 bit
SAAN system as storage device behind
4 GB RAM to make it simple, we have a database table which contains all airports
(about 10000 rows) and the complete location hierarchy (flatted), i.e.

create table dim_airports
(

airport_code char(3),
city_code char(3),
country_code char(2),
conti_name varchar2(20)
)

There are indexes on each column, thus 4 indexes. the table has been analyzed using dbms_stats.gather_table_stats...

When I query the table using the statement

select count(*) from dim_airports where country_code='DE' (which results in 214 rows)

then Oracle does not use the index on country_code, but does a full table scan. I have to adjust the optimizer_index_cost_adj to 1 or 2 to make Oracle think that it's cheaper to use the index. But my own timings clearly show (using explain plan and tkprof) that using the index is always faster when querying country_code.

Of course, such a simple query is of no concern, but the real queries against the system are much more complicated, use temp table transformations (i.e. access this dimension table using a full scan instead of an index range scan) etc., and then the difference between using the index and not using it is 4 secs against 45 secs...

What other possibilities are there to "adjust" the index cost? Or is this simply assumed to be a bug in the cost calculation which we should live with?

I only know of two parameters:

optimizer_index_cost_adj
optimizer_index_caching

the second one does not seem to have any influence...

Best regards,
  Alex Received on Fri Nov 04 2005 - 02:25:18 CST