Re: Bitmap index costing - how to influence
From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Fri, 27 Mar 2009 22:33:49 +0100
Message-Id: <11939B83-3E4E-4231-A130-A9F08C53134E_at_gmail.com>
> I am pretty much stuck on a bitmap costing problem.
>
> I have a query on a table with 10M rows (709MB), and the predicates
> are correctly estimated to return 800 000 rows. As a result with the
> famous 80/20 split, my bitmap index access path cost is ~170 000. The
> full table scan cost is ~20 000. Obviously Oracle choose FTS.
>
> When executed with a hint, the query touches about 8000 block from the
> table, and needless to say, is significantly faster.
>
> Any ideas on how to influence bitmap index cost, relative to full
> table scan cost? Anything goes as long as it doesn't break b-tree
> index costing.
>
> Thing's I've already considered, but somewhat ruled out
> - adjust "mbrc" system statistic - event at max (128) cost is still
> 133 000 vs 16 000 fts
> - optimizer_index_cost_adj - i will have to set this to 10 (10 times
> cheaper) for bitmaps to be used. I am concerned about proper
> calculations of b-tree indexes vs full table scans
>
> Any other ideas?
Date: Fri, 27 Mar 2009 22:33:49 +0100
Message-Id: <11939B83-3E4E-4231-A130-A9F08C53134E_at_gmail.com>
> I am pretty much stuck on a bitmap costing problem.
>
> I have a query on a table with 10M rows (709MB), and the predicates
> are correctly estimated to return 800 000 rows. As a result with the
> famous 80/20 split, my bitmap index access path cost is ~170 000. The
> full table scan cost is ~20 000. Obviously Oracle choose FTS.
>
> When executed with a hint, the query touches about 8000 block from the
> table, and needless to say, is significantly faster.
>
> Any ideas on how to influence bitmap index cost, relative to full
> table scan cost? Anything goes as long as it doesn't break b-tree
> index costing.
>
> Thing's I've already considered, but somewhat ruled out
> - adjust "mbrc" system statistic - event at max (128) cost is still
> 133 000 vs 16 000 fts
> - optimizer_index_cost_adj - i will have to set this to 10 (10 times
> cheaper) for bitmaps to be used. I am concerned about proper
> calculations of b-tree indexes vs full table scans
>
> Any other ideas?
Just an idea: manipulate the statistics of your index manually. This should led the CBO to other calculations and decisions.
Maybe not the finest way, but at least a method to manipulate only one object rather than changing instance parameters.
hth
Martin
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 27 2009 - 16:33:49 CDT
- application/pkcs7-signature attachment: smime.p7s