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 -> Re: Cost of Access Plan using Bitmap Indexes

Re: Cost of Access Plan using Bitmap Indexes

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 22 Aug 2003 20:49:51 +0100
Message-ID: <bi5s7m$hs7$1$830fa17d@news.demon.co.uk>

There are many details that impact on the CBO strategy.

  1. Statistics on 8.1.7.4 do not allow for histograms to be generated at the table level, so for queries hitting multiple partitions, Oracle synthesises a histogram from the individual partition histograms. This affects the costing
  2. If you have the most selective column as the first column of the index, and it's the only one used, and the index has a good clustering factor, Oracle may produce costs that are unrealistically low. This could effect could be exaggerated by the IN list optimisation and the partitioning column effect.
  3. Bitmap indexes have no statistics regarding clustering, so the cost of using them can be way out - if the target data is highly clustered, then the cost will be overstated, the target data is randomly scattered then the cost will be understated. Whilst you can 'cheat' to correct this anomaly for b-tree indexes, it's not really possible for bitmap indexes. Perhaps the best you can do is to provide a fixed value for optimzer_index_cost_adj - 100 * 20/356 might be appropriate - but this is not a good idea if it is only relevant to a single table and a single index.
--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____Finland__September 22nd - 24th
____Norway___September 25th - 26th


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA_(CA, TX)_August
____USA__October
____UK___November


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


"Fabrizio Napolitano" <fnapolitano_at_belgacom.net> wrote in message
news:ed7203c4b342c95de8411a1ea7d9e834.119705_at_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 - 14:49:51 CDT

Original text of this message

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