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
>
> 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: