Re: Bitmap index costing - how to influence
- From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
- To: Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx>
- Date: Sun, 29 Mar 2009 00:04:45 +0100
Chris,
I'm not sure if the description of my suggestion was clear enough. I'm aware of
that using the "optimizer_index_cost_adj" influences both bitmap and b*tree
index costs, but my proposal is to influence only this particular query using
the hint, which prevents any other queries from being affected by this setting,
rather than modifying the global setting.
If you can't modify the query directly, use a SQL profile to add the hint. You
can construct an arbitrary SQL profile using the undocumented
dbms_sqltune.import_sql_profile procedure.
Of course things look different if you have numerous queries that need to be
modified. The FORCE_MATCH option of SQL profiles can be of great help in case
literals are used.
Using SQL profiles you could also try to specifically fudge some of the
statistics used for the cost calculation, e.g. the OPT_ESTIMATE hint for an
INDEX_SCAN or the INDEX_STATS hint.
Since the cost calculation is mainly driven by the selectivity based on the
column statistics of the corresponding column, you could either try to
manipulate the columns statistics in the dictionary, or again, use the
COLUMN_STATS hint as part of a SQL profile, e.g. COLUMN_STATS("FACT_TABLE2",
"DIM_FK2", scale, distinct=100) index(fact_table2) to fudge the statistics only
for particular query executions.
Chris Antognini has written a very good wrap-up of SQL profiles outlining the
different possibilities: http://antognini.ch/papers/SQLProfiles_20060622.pdf
I'm not aware of a means to influence the cost calculation of only a specific
bitmap index.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
> -----Ursprüngliche Nachricht-----
> Von: "Christo Kutrovsky" <kutrovsky.oracle@xxxxxxxxx>
> Gesendet: 28.03.09 22:47:01
> An: info@xxxxxxxxxxxxxxxxxxxxx
> CC: oracle-l@xxxxxxxxxxxxx
> Betreff: Re: Bitmap index costing - how to influence
> Thanks Randalf,
>
> I can't modify the query, and I've set this globally for the database
> already as a workarround.
>
> However this affects costing calculations for b-tree indexes.
>
> I am looking for something that affects only bitmaps, and ideally only
> specific bitmap indexes.
____________________________________________________________________
Psssst! Schon vom neuen WEB.DE MultiMessenger gehört?
Der kann`s mit allen: http://www.produkte.web.de/messenger/?did=3123
--
//www.freelists.org/webpage/oracle-l
Other related posts:
