RE: Partition and Index Usage

> Hi Larry,
>
> First I would suggest doing daily partitioning and dropping the
> index on the
> batch_date.

That's been kicked around. It's not a bad idea -- it would make sure the index, since it wouldn't exist, doesn't get in the way. FWIW, it's partitioned on a monthly basis to fall more in line with bulk maintenance operations that are sometimes performed. External feed discovers a problem and resends a month? A simple exchange partition novalidate after the data is loaded in staging and verified. The pruning is another benefit, but not the only reason it's partitioned by month -- 99% of the queries are for month, 2 months, quarter, year, etc. Going to a more granular level will just result in more partitions being examined. Would we get better performance? Maybe, maybe not. Would have to test, and will not be able to build a full blown test for another month or so until an additional 4.2 TB disk space comes on-line. And yeah, where *are* the aggregates ;-)

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

And that was really the whole reason for posing this question -- why does the CBO even consider that index, in this particular case where a month range is specified, the same as the partition. It *should* know that the batch date index can be no more or no less than what the partition comprises. But the CBO obviously isn't thinking that way and is evaluating the index selectivity just like it does all the other BMI's. And in some cases decides to go ahead and include it, doing the bitmap merge with multiple other BMI's on which criteria exists. Just thought it was odd that it doesn't take it into consideration, seeing if the boundaries of the criteria on the batch date are the same as the boundaries for the partition. Probably a good reason for it -- it just escapes me what it might be. Maybe to consider an index join between multiple BMI's avoiding hitting the table at all, but it's not doing that.

>
> 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).

This is actually done in a couple of cases on some other tables, but more for the reason of being able to resolve some specific queries against those tables entirely in the index.

Anyway, good thoughts and suggestions, I appreciate it. Each of them could address this specific issue.

I'm still going to go ahead and dig into the 10053 and stats. Try to get it to include the batch date index when helpful, and avoid it when it’s not. Or I could always set the distinct keys to 1 or drop the index ;-). FWIW, this isn't a huge problem -- the performance when it includes the batch date even when a month is specified is still quite good, we just know it could be even better. We use Usage Tracker from Ambeo, plus my ongoing monitoring, and the query execution time numbers overall for the system are *extremely* good. We just have that occasional ad-hoc query that could be a bit better (sometimes a lot better, but it’s not the batch date index that is causing problems on those that really need some help ;-)). But we have identified the solutions for those.

But yes, your suggestions are certainly things to keep in mind when encountering the non-selective usage of the index on batch date. Just not sure if it’s something we have the time to pursue right now. And if you follow the classic example of when to composite partition, this table is screaming for it -- range on batch date, hash on another column (no meaning generated numeric value that is always specified).

>
> 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).