Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Strange CBO Behaviour with a Bitmap Index

Strange CBO Behaviour with a Bitmap Index

From: Jimbo1 <jamestheboarder_at_googlemail.com>
Date: 1 Feb 2006 02:41:51 -0800
Message-ID: <1138790511.656693.286940@g49g2000cwa.googlegroups.com>


Hi there,

I have a question to ask about the behaviour of the Cost Based Optimiser in relation to Bitmap Indexes in Oracle 8i (8.1.7.4).

I have a table, which I shall call REPORT_USAGE_TABLE on this user group. I have a column in this table named REPORT_POPULATED. This column can either store the character 'N' or 'Y'.

I expect around 75% of the REPORT_POPULATED column values to be 'N', and the remainder to be 'Y'. The REPORT_USAGE_TABLE stores over 23 million rows. I decided that as this table will only ever see a few en-mass inserts that will be batch, not user driven, creating a bitmap index on the REPORT_POPULATED column could be a sensible option. At some point in time, I will want to delete all the rows from the REPORT_USAGE_TABLE where REPORT_POPULATED = 'Y'.

Okay, I gathered full statistics on the table BEFORE I created the bitmap index, using the DBMS_STATS.GATHER_TABLE_STATS procedure (that's probably not relevant, but I thought I'd mention it anyway). After creating the bitmap index on the REPORT_USAGE column, I gathered full stats on this index via the DBMS_STATS.GATHER_INDEX_STATS procedure.

To save space here, it would be easier if you'd accept it as given that the statistics exist. I can supply them for any doubters amongst you, but I'm guessing most people will not want to see a stream of SQLPlus output here. ;o)

Okay, here's my problem:

If I run the query:

SELECT * FROM report_usage_table
 WHERE report_populated = 'Y';

The new bitmap index is chosen by the CBO. That's great; exactly what I want.

However, if I run the DML statement:

DELETE FROM report_usage_table
WHERE report_populated = 'Y';

The bitmap index is not used by the CBO for this statement, and instead it is electing to full table scan over 23 million rows. That's definitely not what I want to see happening.

One thing I haven't mentioned that is probably relevant is that the REPORT_USAGE_TABLE is range partitioned by month. As a result of this, I've had to create the new bitmap index as a local index that is also range partitioned by month.

Please don't respond if all you've got to add is that we should upgrade to 10g. Believe me, I have asked this question of the decision makers here myself, and it's taken an effort on my part as a contractor to get them to accept that we should move to the cost-based from the rule-based optimiser.

Thanks in advance for any help/insights.

James Received on Wed Feb 01 2006 - 04:41:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US