Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Cost of Access Plan using Bitmap Indexes

Cost of Access Plan using Bitmap Indexes

From: Fabrizio Napolitano <fnapolitano_at_belgacom.net>
Date: Fri, 22 Aug 2003 16:20:26 +0000 (UTC)
Message-ID: <ed7203c4b342c95de8411a1ea7d9e834.119705@mygate.mailgate.org>


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
Received on Fri Aug 22 2003 - 11:20:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US