Re: Bitmap index costing - how to influence
- From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- To: info@xxxxxxxxxxxxxxxxxxxxx, kutrovsky.oracle@xxxxxxxxx
- Date: Sun, 29 Mar 2009 12:52:25 -0500
Christo
Another option is try inserting a row with really high value for that
column firstviewdate and that should bring down selectivity of 'between '
operator. In turn, that might lead to use of bitmap index.
See below, in my test setup, cardinality estimate reduced from 11K to 990
and use of bitmap index. There are exactly 100 values between 0 and 99 for
n2. But, make sure that there are no histograms on that column if you go
this route :-)
explain plan for select * from btmap where n2 between 10 and 19;
Of course, I don't know much about this application, so YMMV.
For example,
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11091 | 2036K| 752 (1)| 00:00:10 |
|* 1 | TABLE ACCESS FULL| BTMAP | 11091 | 2036K| 752 (1)| 00:00:10 |
---------------------------------------------------------------------------
insert into btmap values (9999999, 999999999, 'xxxxx');
exec dbms_stats.gather_table_stats('cbo2','btmap', estimate_percent =>100,
cascade =>true,
method_opt =>' for all columns size 1');
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 990 | 181K| 207
(0)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID | BTMAP | 990 | 181K| 207
(0)| 00:00:03 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | |
| |
|* 3 | BITMAP INDEX RANGE SCAN | BTMAP_I2 | | |
| |
-----------------------------------------------------------------------------------------
--
Cheers
Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com
Specialists in Performance, Recovery and EBS11i
Blog: http://orainternals.wordpress.com
On Sun, Mar 29, 2009 at 10:10 AM, Riyaj Shamsudeen <
riyaj.shamsudeen@xxxxxxxxx> wrote:
> Hi Christo
> May be, you are only interested in finding a generic solution for all
> bitmap indices in that atabase. But, How many columns are in that bitmap
> index chosen? Do you get any relief if you create single column bitmap
> index?
>
> --
> Cheers
>
> Riyaj Shamsudeen
> Principal DBA,
> Ora!nternals - http://www.orainternals.com
> Specialists in Performance, Recovery and EBS11i
> Blog: http://orainternals.wordpress.com
>
>
>
>
> On Sun, Mar 29, 2009 at 7:02 AM, Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx
> > wrote:
>
>> > Why use undocumented OPT_PARAM when INDEX_COMBINE is documented and
>> > provided?
>>
>> Greg,
>> ....
>
>
>
>
>
Other related posts:
