There are many details that impact on the CBO strategy.
- 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
- 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.
- 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
