Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Partition and Index Usage

Partition and Index Usage

From: Larry Elkins <>
Date: Sat, 12 Oct 2002 08:03:19 -0800
Message-ID: <>


I'll be digging into this a bit more, playing around with a 10053 trace, reviewing the stats, and trying to tie back why this occurs, but here's the scenario.

Partitioned table, by month, on a date column called batch_date. 30 some odd million rows per partition, 750+ million rows altogether. Multiple local BMI's defined. A query of the form:

 WHERE CUST_ID = 12345 and

       Batch_Date between TO_DATE('01012002','MMDDYYYY') and TO_DATE('01312002','MMDDYYYY') I end up in some cases with a BITMAP MERGE operation, using the BMI indexes on both CUST_ID *and* BATCH_DATE. Now here's the rub, the batch date criteria already results in partition pruning for just that month, and, the batch date value is inclusive of *all* rows in that partition (batch_date has no time component, ok, technically it's midnight). So, using the BMI on batch date to merge with the BMI on cust id is wasted effort -- there will be no rows in that partition outside of the date range specified, and all rows in the partition are *in* that range -- the index on batch date does not, and cannot, exclude any rows in the partition. I can use a NO_INDEX hint to suppress the use of the BMI on batch date, and use just the BMI on cust id and see substantial improvement. Obviously I would prefer to get the stats squared away as opposed to using a hint, especially since hinting isn't feasible with the dynamic queries issued by the various ad-hoc tools used.

Anyway, it just seems strange to me that the CBO, on occasion, not always, will choose to include the usage of the index on batch date when it matches the partition boundaries and will do nothing as far as filtering rows. Oh yeah, since the upper boundary of the partition is defined as less than TO_DATE('02012002','MMDDYYYY'), and the criteria would leave wiggle room in there for dates on "01312002" that have a time component, I can change the criteria to be "BATCH_DATE >= TO_DATE('01012002','MMDDYYYY') and BATCH_DATE < TO_DATE('02012002','MMDDYYYY'). This would account for a time component
(though time component is midnight). But I still get the same BMI merge with
batch date on the handful of sample queries exhibiting this behavior.

Oh well, off to dig into the stats and play with 10053 traces. Just curious if someone has run into something similar. And yes, I could simply drop the index altogether, but that wouldn't help the folks querying on just a single day.


Larry G. Elkins

Please see the official ORACLE-L FAQ:
Author: Larry Elkins

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Sat Oct 12 2002 - 11:03:19 CDT

Original text of this message