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_at_jlcomp.demon.co.uk> 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_at_gmail.com>
> To: <oracle-l_at_freelists.org>
> 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.
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l