Re: Overhead of table with empty partitions

I would also experiment with dynamic sampling to see if the overhead of that is offset by improved cardinality estimates

• Original Message ---- From: John Thompson <jhthomp_at_gmail.com> To: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>; "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Wednesday, May 28, 2008 5:27:57 PM 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_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