From oracle-l-bounce@freelists.org Tue Aug 31 12:26:53 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i7VHQrA26047 for ; Tue, 31 Aug 2004 12:26:53 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i7VHQrI26042 for ; Tue, 31 Aug 2004 12:26:53 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2423272EBCD; Tue, 31 Aug 2004 12:29:27 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 22841-97; Tue, 31 Aug 2004 12:29:27 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6EC1D72EC36; Tue, 31 Aug 2004 12:29:26 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 31 Aug 2004 12:27:58 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D783D72EB3C for ; Tue, 31 Aug 2004 12:27:57 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 22778-69 for ; Tue, 31 Aug 2004 12:27:57 -0500 (EST) Received: from imcsvr-2.aetvn.com (imcsvr-2.aetvn.com [216.89.67.159]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7023672EB34 for ; Tue, 31 Aug 2004 12:27:57 -0500 (EST) Received: by imcsvr-2.aetvn.com with Internet Mail Service (5.5.2653.19) id ; Tue, 31 Aug 2004 13:30:51 -0400 Message-ID: <30462D80AA52E74698512ADCC4F7EAA31223970A@EXCHANGE> From: "Gogala, Mladen" To: "'oracle-l@freelists.org'" Subject: RE: Partitioning opinion Date: Tue, 31 Aug 2004 13:30:48 -0400 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-type: text/plain X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 8744 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Mladen.Gogala@aetn.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Harvinder, partitions are tables in disguise. In Oracle7 there were things called "partition views". In Oracle8, there was a logical leap with respect to the notion of "segment". Before Oracle8 there was 1-to-1 correspondence ("bijective mapping") between tables and data segments and between indexes and index segments. That is no longer true. With partitioning option, the bijective mapping is broken and a table can have more then a single segment. Why am I telling you all that? If you take a look at traces generated by the event 10053, you'll see that partition pruning is done first, then the access path to the particular partition(s) is determined. If an optimizer decides that the desired result lies within a single partition, and it will consider all indexes it has on that partition to resolve the query, regardless whether they're global or local. Local indexes have one B-tree for each partition, which (hopefully) has fewer entries then then the global B-tree index and is of fewer degree (level). Local indexes are easier to search because of the sheer size. The dark secret of the partitioning option is that partitions are tables in disguise, and should be treated as such. -- Mladen Gogala A & E TV Network Ext. 1216 > -----Original Message----- > From: Harvinder Singh [mailto:Harvinder.Singh@MetraTech.com] > Sent: Tuesday, August 31, 2004 1:02 PM > To: oracle-l@freelists.org > Subject: Partitioning opinion > > > Hi, > > We have a table having 14 columns and contain historical data > and we are planning to implement partitioning so that we will > keep only 12 months data online and purge the old partition > every month. We will be using Range partitioning on Date > column name Interv and have primary key on (id,Interv). Data > is never updated and only deleted for archiving that we are > planning to do as drop partition so for most of the time only > inserts will go against this table and few Selects. For > performance of select we have to add 2 more non-unique global > indexes. So the schema will look like: Primary key index on > (id,Interv) Does non-prefix local indexes be Ok or we should > change the order of PK to (interv,id) to have prefix local > index? Non-unique index on 3 columns Non-unique index on 3 > columns Should we partition these global non-unique indexes > or it does not matter since partitions will not be used by > optimizer for pruning? > > Thanks > --Harvinder > > > > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@freelists.org > put 'unsubscribe' in the subject line. > -- > Archives are at http://www.freelists.org/archives/oracle-l/ > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------