RE: Overhead of table with empty partitions
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <jhthomp@xxxxxxxxx>, "'Jonathan Lewis'" <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 May 2008 11:20:58 -0400
Following up on JL's typically excellent point. Being a point of sale
application, I'll take a wild leap of faith that your inserts are all pretty
much into the current day. IF that is the case, then you should consider a
rolling window of partitions (increasing to 60 if you need the fixed 30 to
be available) such that you exchange out the oldest and exchange in the new
partition daily (presumably you might need to have one extra day ahead
emptly and available to avoid timing issues about where to insert at
precisely one tick after midnight until you get to 11g which will neatly
handle that for you).
Then all the old days will be "average full" in cardinality, the current day
will range from empty to "average full +/- your maximum variance of rows per
day", and the extra day ahead will be empty until it becomes the current day
(which in 11g+ could mean it doesn't exist until you need it. I'll leave
that future possibility out in these calculations, and the results only
improve when that becomes true.
So using 30 rolling, you'd have 28 "average full", 1 "half full", and 1
"empty", so the averaged cardinality would be about 28.5/30ths accurate as
compared to the JL's worst case where you have 29 empty partitions when you
whack and recreate partitions in chunks of 30. (Your mileage will vary
somewhat with the variance of rows per day.)
Likely you will want a "today" set of queries that are specifically on the
current day's partition to make it easy for the optimizer to know that, and
if operational considerations allow it you might implement some method for
injecting accuracy into the current day's partition's stats. A handy dandy
synonym identifying the current day's partition is often useful for that,
though it may cause a parse storm if altering the synonym invalidates a lot
and your queries fly fast and furious.
Regards,
mwf
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of John Thompson
Sent: Wednesday, May 28, 2008 5:28 PM
To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx
Subject: Re: Overhead of table with empty partitions
Thanks Jonathan. I'm going to run some tests to see if I can produce the
results you mentioned. This is a point of sale database, and queries
against this table need to be quick and more importantly, consistent.
Thanks again!
On 5/28/08, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> wrote:
The effect will be version dependent, and query dependent.
You may get unlucky on queries that cannot identify a single
partition at optimisation time - leading Oracle to estimate a cardinality as
the average cardinality implied by having 30
partitions (worst case "real cardinality / 30").
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com <http://jonathanlewis.wordpress.com/>
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message ----- From: "John Thompson" <jhthomp@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, May 28, 2008 4:57 PM
Subject: Overhead of table with empty partitions
Say I've got a table that's partitioned by day and has 30 days worth of
partitions. Every 30 days I'll create another 30 days worth of partitions
and drop the previous 30 days worth. Stats are running everyday so the
thinking is that having 29 or so days worth of empty partitoins will not
cause any SQL performance issues. Testing has shown this to be the case,
but wanted to see if there's any insight on having many empty partitions.
Other related posts:
