Re: Bitmap index costing - how to influence
- From: Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx>
- To: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 1 Apr 2009 17:07:21 -0400
Randolf, those are some good suggestions, I will explore them
On Sat, Mar 28, 2009 at 7:04 PM, Randolf Geist
<info@xxxxxxxxxxxxxxxxxxxxx> wrote:
> 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
>
>
--
Christo Kutrovsky
Senior DBA
The Pythian Group - www.pythian.com
I blog at http://www.pythian.com/blogs/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
