| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Partition and Index Usage
Larry,
Perhaps this is out of the question, but...
What about the possibility of creating a batch_month column and using that to partition the table. Leave the bitmap index on batch_date for those who need it and let the other users go into the table using batch_month = 'blah'. Unfortunately I realise that this isn't a pure back-end fix, but perhaps it is worth considering. Depending on what you are querying perhaps an aggregated table (materialized view even?) is the next step to reduce the volume to something a little easier for Oracle - but that's really getting into the end-user / application part of town.
Regards,
Mark.
PS: Sorry I couldn't provide a simple solution but I'm not sure that there is one.
"Larry Elkins"
<elkinsl_at_flash To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
.net> cc:
Sent by: Subject: Partition and Index Usage
root_at_fatcity.c
om
13/10/02 02:03
Please respond
to ORACLE-L
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-LReceived on Sun Oct 13 2002 - 19:23:22 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Richard INET: mrichard_at_transurban.com.au 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).
 |
 |