Re: Bitmap index costing - how to influence

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

--
http://www.freelists.org/webpage/oracle-l


Other related posts: