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@flash To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
.net> cc:
Sent by: Subject: Partition and Index Usage
[EMAIL PROTECTED]
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
[EMAIL PROTECTED]
214.954.1781
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Larry Elkins
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
