RE: Partition and Index Usage

 Hi Larry,

First I would suggest doing daily partitioning and dropping the index on the batch_date.

Regarding your sql: partitions eliminations never substitutes the necessity to validate any predicates on the partitioning key in the where clause.

If it's not feasible to partition by day, I would drop the BMI on the batch_date and include the batch_date in the cust_id BMI (local index).

Regards,

Waleed

-----Original Message-----
To: Multiple recipients of list ORACLE-L Sent: 10/12/02 12:03 PM

Listers,

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.

8.1.7.4

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.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).