Re: Bitmap index costing - how to influence

great idea.
I'd just go one step further and not manipulate the data (and risk wrong results in some rare/unexpected conditions) but only manipulate the minval/maxval using DBMS_STATS.SET_COLUMN_STATS.

It might not be very comfortable to do so, depending on the data type. But even histograms can be brought to (fake) values which can lead to a kind of fine grained CBO tuning ;-)

Unfortunately it's all wild guess without any useable information about the table, column, index & query.

br
  Martin

Am 29.03.2009 um 19:52 schrieb Riyaj Shamsudeen:

> 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




application/pkcs7-signature attachment: smime.p7s