From oracle-l-bounce@freelists.org Wed Jan 26 19:26:26 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j0R1QQuT016283 for ; Wed, 26 Jan 2005 19:26:26 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j0R1QQem016279 for ; Wed, 26 Jan 2005 19:26:26 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8C7EE6501D; Wed, 26 Jan 2005 19:25:38 -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 25235-04; Wed, 26 Jan 2005 19:25:38 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1501564C3B; Wed, 26 Jan 2005 19:25:38 -0500 (EST) X-BrightmailFiltered: true X-Brightmail-Tracker: AAAAAA== From: "Bill Coulam" To: Subject: non-prefixed indexes on composite partitioned tables Date: Wed, 26 Jan 2005 18:26:28 -0600 Message-ID: MIME-Version: 1.0 Content-type: text/plain X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-ELNK-Trace: c737d492b26b24be9996dd6012f1f49b239a348a220c260900325cb2b7219c30bb4300c3221bb7ee666fa475841a1c7a350badd9bab72f9c350badd9bab72f9c X-Originating-IP: 64.123.202.158 Content-Transfer-Encoding: 8bit X-archive-position: 15300 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: bill.coulam@scgo.com Precedence: normal Reply-To: bill.coulam@scgo.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at example.com X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL autolearn=ham version=2.60 X-Spam-Level: Amusing background: My current shop has 90 instances. The previous data architect read about partitioning and thought it sounded good. A couple tests showed better response, so he said to himself, "If partitioning is good, subpartitioning must be even better!" So every table over 50K rows here is composite-hash partitioned, pre-created back in 2003 out to 2006. Some of them have 394 subpartitions. No archival strategy. Every row is still here. One of the largest, the one in my example below has 55 columns, and 13 indexes so far, some of them heinous composites. My favorite: on tables where there wasn't an obvious, selective candidate for the hash, he picked any ol' column, often favoring one that has a couple of distinct values. Anyway, so I'm developing a quick report to give me a bird's eye of the mess, er I mean the tables, indexes, partitioning on each, etc. user_part_indexes seems to be reporting that certain indexes are non-prefixed, even when they use the table's range key as their first column. For example, table BOGUS (table and column names changed) is range partitioned on mydate and hash partitioned on mycode. Here is a sampling of a few indexes on BOGUS, gleaned from user_part_tables and user_part_indexes BOGUS RANGE(mydate) + HASH(mycode) BOGUS_PK NORMAL GLOBAL NON-PARTITIONED columns: c1 BOGUS_UK RANGE(mydate) + HASH(mycode)LOCAL PREFIXED columns: mydate c2 c3 c4 mycode c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 BOGUS_COMP2_LIDX RANGE(mydate) + HASH(mycode)LOCAL NON_PREFIXED columns: c6 mkt_cd mydate mycode c4 c2 BOGUS_COMP3_LIDX RANGE(mydate) + HASH(mycode)LOCAL PREFIXED columns: mydate end_dt_gmt c14 c2 c3 c4 mycode c10 c11 c6 mkt_cd c1 BOGUS_COMP4_LIDX RANGE(mydate) + HASH(mycode)LOCAL NON_PREFIXED columns: mydate c16 BOGUS_COMP6_LIDX RANGE(mydate) + HASH(mycode)LOCAL NON_PREFIXED columns: mycode c3 BOGUS_COMP7_LIDX RANGE(mydate) + HASH(mycode)LOCAL NON_PREFIXED columns: mydate c2 end_dt_gmt c6 mkt_cd I expected that indexes leading with the range-keyed date would be considered PREFIXED, like the UK, comp3, comp4 and comp7. Instead comp4 and comp7 are non-prefixed. Why? I can see the relationship that you seem to need both mydate and mycode to be prefixed, but if so, why wouldn't mycode be required as the second column, as mydate is required as the first in order to be prefixed? -- http://www.freelists.org/webpage/oracle-l