Cost of Access Plan using Bitmap Indexes

Dear group,

busy day for me...

One more question that driving me nuts.

Environment:
Win2000 4 processor
Oracle 8.1.7.4

Table1 90.000.000 rows partitioned in 221 partitions

I had found a query against this table that was running for almost 5 hours.
explaining the plan of this query I noted that it was using a local btree index with 6 columns and only the first used in the sql with a "in" predicate (this column is also the first column in the partitioning key); that does not seem very good to me but for the CBO the cost is 1800.

I have noticed also a column with low cardinality (not used in the first index) used in an equal condition that should point to around 20.000 rows.

I tried to create a bitmap index on this single column. the optimizer did not choose it so I did force it with an hint. the cost was 356000

tried it anyway and the query completed in seconds.

Table and indexes are all analyzed!!!

I am studying the doc on the Optimizer right now but can anyone please give me a hint of where to look to understand this situation??

are there any parameter that need to be set in order to give the bitmap a more logical cost in the way to make the optimizer choose it on is own??

Thank you for the patience and for any advice.

Fabrizio

-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG