From oracle-l-bounce@freelists.org Thu Mar 25 14:58:29 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2PKwTn15248 for ; Thu, 25 Mar 2004 14:58:29 -0600 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 i2PKwRo15222 for ; Thu, 25 Mar 2004 14:58:28 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A0C14393814; Thu, 25 Mar 2004 15:54:17 -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 16767-94; Thu, 25 Mar 2004 15:54:17 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E7AC2393529; Thu, 25 Mar 2004 15:52:38 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 25 Mar 2004 15:51:08 -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 035F6391670 for ; Thu, 25 Mar 2004 15:48:49 -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 15716-84 for ; Thu, 25 Mar 2004 15:48:48 -0500 (EST) Received: from flamingo.mail.pas.earthlink.net (flamingo.mail.pas.earthlink.net [207.217.120.232]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id AF00D391E4F for ; Thu, 25 Mar 2004 15:48:20 -0500 (EST) Received: from huey.psp.pas.earthlink.net ([207.217.78.220]) by flamingo.mail.pas.earthlink.net with esmtp (Exim 3.33 #1) id 1B6bt6-00001D-00 for oracle-l@freelists.org; Thu, 25 Mar 2004 12:55:40 -0800 Message-ID: <1945160.1080248140003.JavaMail.root@huey.psp.pas.earthlink.net> Date: Thu, 25 Mar 2004 15:55:39 -0500 (GMT-05:00) From: david wendelken To: oracle-l@freelists.org Subject: Partitioning Question and FKs to Non-Partitiond Tables Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Earthlink Zoo Mail 1.0 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 1838 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: davewendelken@earthlink.net Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org I've been working thru partitioning issues and have a question on a specific topic. Guidance would be more than welcome! FACT) I have a natural way to partition many of the data tables in the application. I have a partitioning data column in those tables. I'll call it segment_value and it's part of the natural primary key. There's no other reasonable way to partition things that would also make sense from a maintenance perspective. FACT) However, segment_value is not particularly unique, the other primary key column in the partitioned tables is unique by itself. So, I've put segment_value as the second column of the primary key, not the first column. BELIEF) I think that was a good thing to do. But I could be wrong. FACT) Some reference tables should not be partitioned because, well, they shouldn't be. They are used equally across all partitions. SAD FACT) I will need to load, flush, and re-load some partitions. The exchange option looks promising for this. SAD FACT) I'm short-handed on my project and short on schedulable down-time, too. BELIEF) Local indexes on partitioned tables make it easier to take care of the above sad fact, with minimal impact on people making use of the other partitions. Global indexes slow some maintenance activities down. CONCLUSION) Use local indexes where possible. QUESTION) When I do an FK from a partitioned table to a non-partitioned table, it won't have segment_value in the primary key. So, the FK index wouldn't naturally have segment_value in it. That would seem to make it a global index. Any way out of this, to end up with local indexes? I haven't (yet) tried to see if I can get away with stuffing segment_value on the back of the same index used by the foreign key and partition by that. Not a lot of hope, but I might get lucky. Any comments before I spend the time to test out the idea? ---------------------------------------------------------------- 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 -----------------------------------------------------------------