From oracle-l-bounce@freelists.org Wed Apr 28 10:58:54 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i3SFwd905163 for ; Wed, 28 Apr 2004 10:58:49 -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 i3SFwT605124 for ; Wed, 28 Apr 2004 10:58:39 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A44F372D3C9; Wed, 28 Apr 2004 10:45:44 -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 19638-35; Wed, 28 Apr 2004 10:45:44 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 951FF72DB80; Wed, 28 Apr 2004 10:43:37 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 28 Apr 2004 10:42:18 -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 DB21772D9A5 for ; Wed, 28 Apr 2004 10:42: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 18659-35 for ; Wed, 28 Apr 2004 10:42:17 -0500 (EST) Received: from maillnx-us312.fmr.com (maillnx-us312.fmr.com [192.223.178.27]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2089E72D8D6 for ; Wed, 28 Apr 2004 10:40:35 -0500 (EST) Received: from virmmk110nts.fmr.com (virmmk110nts.fmr.com [172.25.107.117]) by maillnx-us312.fmr.com (Switch-3.1.2/Switch-3.1.0) with SMTP id i3SFrGpg008658 for ; Wed, 28 Apr 2004 11:53:16 -0400 Received: from MSGBOSCLF2WIN.fmr.com ([10.46.17.175]) by MSGMROIM01WIN.DMN1.FMR.COM with Microsoft SMTPSVC(5.0.2195.6713); Wed, 28 Apr 2004 11:53:15 -0400 X-MimeOLE: Produced By Microsoft Exchange V6.0.6521.0 content-class: urn:content-classes:message Subject: RE: Index-Organized Table experiences MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit Date: Wed, 28 Apr 2004 11:53:15 -0400 Message-ID: <42BBD772AC30EA428B057864E203C9991158F3@MSGBOSCLF2WIN.DMN1.FMR.COM> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Index-Organized Table experiences Thread-Index: AcQtK2v4EkIpkQ39TRqjLGVAcarHAgADE5Sw From: "Khedr, Waleed" To: X-OriginalArrivalTime: 28 Apr 2004 15:53:15.0929 (UTC) FILETIME=[EB1EA890:01C42D38] X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 3966 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Waleed.Khedr@FMR.COM Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Main reason deciding to use IOT is avoiding an extra rowid access to the table to fetch some other columns not part of the index and are expensive to include them in the index.=20 In your case the table has two columns only that will be part of the primary key. So no real gain of using IOT except saving some disk space (no need for the table segment) and removing some confusion for the optimizer. On the other hand, direct loading the IOT will be slower than direct loading a table and parallel building an index. Hope it helps, Waleed -----Original Message----- From: Daniel Fink [mailto:Daniel.Fink@Sun.COM] Sent: Wednesday, April 28, 2004 10:15 AM To: oracle-l@freelists.org Subject: Index-Organized Table experiences We are looking to implement IOTs for a couple of intersection entities in a 10g db. I would like to hear from those brave enough to actually use IOTs what is the good, the bad and the ugly.=20 example: Employee (heap table) Project (heap table) There is a many-to-many relationship between the tables (1 employee can be on many projects and 1 project can have many employees). The emp_project table is the intersection entity containing emp_id and project_id as the only columns. There are FK constraints on each of the columns. The combination of emp_id and project_id is unique. This situation *sounds* like the right one for an IOT, otherwise we would have 1 table and 2 indexes (1 on each column). My main concerns are: 1) Integrity/performance 2) Locking behavior (do I need to adhere to the traditional "index all foreign keys" rule to prevent excessive locking?) 3) Any especially nasty gotchas Thanks, Daniel ---------------------------------------------------------------- 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 -----------------------------------------------------------------