Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 19580 invoked from network); 28 May 2008 15:34:06 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by air964.startdedicated.com with SMTP; 28 May 2008 15:34:06 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 49E7B856CD9; Wed, 28 May 2008 16:30:38 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 24659-01; Wed, 28 May 2008 16:30:38 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C0FF5856C9F; Wed, 28 May 2008 16:30:37 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 28 May 2008 15:51:43 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0EDA785667B for ; Wed, 28 May 2008 15:51:43 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 16982-05 for ; Wed, 28 May 2008 15:51:42 -0400 (EDT) Received: from smtp801.mail.ird.yahoo.com (smtp801.mail.ird.yahoo.com [217.146.188.61]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 606D1856680 for ; Wed, 28 May 2008 15:51:41 -0400 (EDT) Received: (qmail 34345 invoked from network); 28 May 2008 19:55:08 -0000 Received: from unknown (HELO Primary) (oracle.expert@btinternet.com@86.130.247.35 with login) by smtp801.mail.ird.yahoo.com with SMTP; 28 May 2008 19:55:07 -0000 X-YMail-OSG: OBfPbY4VM1mfPg1BV3ALfFnIsqTloU9m7A6Ks5DJIBEeI3AmA56O.EV4oqWncpwxKWF0ttB2ay06PE0w2L2A2yuCfHjg23AnE7BANQEGebcF58qu5LWRUeRYwR0cAlJG8RtT153G.4rOt5UJC92c_WrA X-Yahoo-Newman-Property: ymail-3 Message-ID: <009901c8c0fc$bc745010$4201a8c0@Primary> From: "Jonathan Lewis" To: , References: <11d63ad10805280857u40611494x7f70b794cdad9a13@mail.gmail.com> Subject: Re: Overhead of table with empty partitions Date: Wed, 28 May 2008 20:55:01 +0100 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 X-archive-position: 8435 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: jonathan@jlcomp.demon.co.uk Precedence: normal Reply-to: jonathan@jlcomp.demon.co.uk List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain 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" 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. > -- http://www.freelists.org/webpage/oracle-l