Re: Bitmap index costing - how to influence

From: Randolf Geist <info_at_sqltools-plusplus.org>
Date: Sun, 29 Mar 2009 00:04:45 +0100
Message-Id: <569605209_at_web.de>



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_at_gmail.com>
> Gesendet: 28.03.09 22:47:01
> An: info_at_sqltools-plusplus.org
> CC: oracle-l_at_freelists.org
> 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
Received on Sat Mar 28 2009 - 18:04:45 CDT

Original text of this message