RE: Overhead of table with empty partitions

From: Mark W. Farnham <>
Date: Thu, 29 May 2008 11:20:58 -0400
Message-ID: <019b01c8c19f$9a278060$>

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.  



From: [] On Behalf Of John Thompson
Sent: Wednesday, May 28, 2008 5:28 PM
To: Jonathan Lewis; 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 <> 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").


Jonathan Lewis <>

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ

  • Original Message ----- From: "John Thompson" <> To: <> 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.  

Received on Thu May 29 2008 - 10:20:58 CDT

Original text of this message