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 its 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 its 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 its 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','MMDD') and
> TO_DATE('01312002','MMDD')
>
> 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 partiti
Re: CodeNotes for Oracle9i...
I would think the authors should read and publish the books in .mp3 format, you can get like 10 hours worth on one cd. Rachel how about you start, bwahahahahahahaha joe DENNIS WILLIAMS wrote: I have an odd question about these on-line books. Can I copy-and-paste the text? Like many of you, I seem to end up with long commutes (why are the best jobs never in your neighborhood?). I find listening to books on CD to be a better use of time than reading bumper stickers. Nobody ever seems to issue Oracle books on audio. So I got a text-to-voice program, and it works pretty good to create an audio version of a book. But many of these eBooks zealously protect their text and prevent you from doing copy-and-paste on the text. Fortunately Oracle makes their books readily available. Any ideas are welcome. And my apologies to the authors on the list that are going "he wants to do WHAT with my book!!". Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, October 10, 2002 7:54 PM To: Multiple recipients of list ORACLE-L I believe the Book Safari is changing. It is supposed to be more flexible now. http://www.oreilly.com/news/new_safari_0902.html Jared "Grabowy, Chris" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/08/2002 01:04 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:CodeNotes for Oracle9i... So every few months my Lookout reminder pops up to remind me to check out what new Oracle books have been released. I stumbled upon a new book called CodeNotes for Oracle9i on Amazon.com, but the interesting part is that it is available in eBook format. Here's the (probably broken) link... http://www.amazon.com/exec/obidos/tg/detail/-/B6ISCN/qid=1034101493/sr=1 -25/ref=sr_1_25/104-5919725-7522346?v=glance The eBook version is $9.95 and the shipping is free (big grin), while the paperback is $13.97 plus shipping. Are you comfortable reading an eBook? You decide. At any rate, I will probably break down and buy the eBook. Not so much because the book is great (or not) but because I hope to send a message to publishers to publish more books in the eBook format, which is also why I posted this message. I know that O'Reilly has the Safari Bookshelf website, but I found it to be restrictive and pricey. BTW, if for some reason you are or will be using .Net, the CodeNotes eBook version is free... http://www.codenotes.com/do/downloads/downloadsNETbook -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa 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).
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','MMDD') and
TO_DATE('01312002','MMDD')
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','MMDD'), 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','MMDD') and
BATCH_DATE
< TO_DATE('02012002','MMDD'). 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Khedr, Waleed
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).
OT: Programming Languages Talking ...
Title: OT: Programming Languages Talking ... (Found this on /. while I am upgrading my 8i db to 9201) http://weblog.burningbird.net/archives/000581.php This is worth a reading ... I liked it hope you will too. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Partition and Index Usage
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','MMDD') and
TO_DATE('01312002','MMDD')
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','MMDD'), 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','MMDD') and BATCH_DATE
< TO_DATE('02012002','MMDD'). 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).
