Re: Bitmap index costing - how to influence

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Sun, 29 Mar 2009 12:52:25 -0500
Message-ID: <203315c10903291052t462b5056wd8c0e0af2e9d8474_at_mail.gmail.com>

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_at_gmail.com> 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_at_sqltools-plusplus.org
> > wrote:
>
>> > Why use undocumented OPT_PARAM  when INDEX_COMBINE is documented and
>> > provided?
>>
>> Greg,
>> ....
>
>
>
>
>

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

Received on Sun Mar 29 2009 - 12:52:25 CDT