Re: Overhead of table with empty partitions

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