Received: (qmail 20458 invoked from network); 11 Nov 2011 01:55:01 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-85-25-126-90.inaddr.ip-pool.com with SMTP; 11 Nov 2011 01:54:59 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2014FE59967; Fri, 11 Nov 2011 02:54:50 -0500 (EST) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1320998090; bh=BzW+wnYpNFhYldjZDSmF3vq/Fu+tsl51mg3lfMtX Wi8=; h=From:To:Date:Subject:Message-ID:References:In-Reply-To: Content-type:Content-Transfer-Encoding:MIME-Version:Sender: Reply-To:List-help:List-unsubscribe:List-Id:List-subscribe: List-owner:List-post:List-archive; b=dPIeCag7xd4NYgJ1OoR30ITl1RUji VhJgOyF5gcFD7yJRSU8MuwG2IwhYT1fEjJPCPjztKiPkWGzwpB4u5VKZ5LzVJBgV/Gp vDe+VDlsAtSlvsxF/ruxnFW/seRNYHGLBBQiBzOJcnbOR4KEEYTybr42Q5ESoA8GGm9 rLln/ArA= X-Virus-Scanned: Debian amavisd-new at localhost.localdomain 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 MkKoh5w4vkRn; Fri, 11 Nov 2011 02:54:49 -0500 (EST) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6C404E5993C; Fri, 11 Nov 2011 02:54:06 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 11 Nov 2011 02:53:25 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BCADDE5954A for ; Fri, 11 Nov 2011 02:53:24 -0500 (EST) 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 Pf96qrdvxknC for ; Fri, 11 Nov 2011 02:53:24 -0500 (EST) Received: from morpheus.cronos.be (mx.cronos.be [94.143.184.115]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0C93DE58D6C for ; Fri, 11 Nov 2011 02:53:23 -0500 (EST) X-AuditID: ac1effb5-b7f876d00000063a-be-4ebcd4722c69 Received: from ws03-exch07.iconos.be (Unknown_Domain [10.0.10.69]) by morpheus.cronos.be (Cronos AntiSpam Gateway) with SMTP id FB.82.01594.274DCBE4; Fri, 11 Nov 2011 08:53:23 +0100 (CET) Received: from ws03-exch07.iconos.be ([10.0.10.69]) by ws03-exch07.iconos.be ([10.0.10.69]) with mapi; Fri, 11 Nov 2011 08:53:23 +0100 From: D'Hooge Freek To: "Joel.Patterson@crowley.com" , "oracle-l@freelists.org" Date: Fri, 11 Nov 2011 08:55:08 +0100 Subject: RE: constraint and index creation Thread-Topic: constraint and index creation Message-ID: <4814386347E41145AAE79139EAA398981939BEE414@ws03-exch07.iconos.be> References: <4814386347E41145AAE79139EAA398981939BEE31A@ws03-exch07.iconos.be> In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit MIME-Version: 1.0 X-Brightmail-Tracker: H4sIAAAAAAAAA+NgFmplleLIzCtJLcpLzFFi42LhYuBy1S2+ssfPYMV3Lot1Lz6yWCx7M43R gcnj6l5fj1MXpQKYorhtkhJLyoIz0/P07RK4M5oOL2Yq6Oao2PXyElsD4y62LkZODgkBE4lZ DWegbDGJC/fWA9lcHEICexklPrftYIJwOhgl7r2+ywpSxSagI9HVuIIZxBYRKJI4cvwNUJyD g0VAVeL8B3aQsLCAlsTL/ccZIUq0JVr7noKViAhYSbxfaAAS5hXwk3jyfQkLiC0kMItJYnqb DIjNCRSf8Oc62BhGoHu+n1rDBGIzC4hL3HoynwniTgGJJXvOM0PYohIvH/9jhagXlbjTvp4R ol5HYsHuT2wQtrbEsoWvmSH2CkqcnPmEZQKj6CwkY2chaZmFpGUWkpYFjCyrGIVy84sKMlJL i/WSi/Lz8ov1klI3MQKjY43c/607GF8eVj7EKMDBqMTDu/fkHj8h1sSy4srcQ4ySHExKorwn LwOF+JLyUyozEosz4otKc1KLDzFKcDArifAWngbK8aYkVlalFuXDpGQ4OJQkeFtA2gSLUtNT K9Iyc4ApACbNxMEJIrhAZvAAzdgEUshbXJCYW5yZDlF0ilFSSpw3CSQhAJLIKM2DG/CKURzo UmFep/NAWR5g2oLregU0kAlo4Gb33SADSxIRUlINjAa3YtZL6s8RvZWmaFXovfX9Tn6WuOm7 xGx9ktz+nwvsuyHOet/0b/N9Rzcmy3M/+r/vfJ/7xOsRK9Nd1nPuMzoFHu/ZbsQbd1krPKQ8 4qja7S5Hn5lvP++r3BQn6W5bO9tczS/n024u8/KK6Gh724+nE+9JrmQq6rv2puXTtefF1UWf lz31UmIpzkg01GIuKk4EAJd/Ps4YAwAA X-archive-position: 40011 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: Freek.DHooge@uptime.be Precedence: normal Reply-To: Freek.DHooge@uptime.be 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 Joel, The first example was to show that Oracle is considering the index created in the "using index" clause as an implicit index. The second example was to show that if the constraint is not explicitly named, the name of the index created using the "using index" clause is considered as a system generated name, despite the fact that you did name it. Only an index that is created separately from the constraint creation is considered as an explicit index. >> Also when dropping the constraint the index supporting it will also be dropped by default (if it >>is an unique index). >-- unless you use KEEP INDEX clause. True, that's why I said: "by default" and "(if it is an unique index)" It is actually not a bad idea to specify "keep index" or "drop index" every time to guarantee a consistent behaviour. Regards, Freek D'Hooge Uptime Oracle Database Administrator email: freek.dhooge@uptime.be tel +32(0)3 451 23 82 http://www.uptime.be disclaimer: www.uptime.be/disclaimer -- http://www.freelists.org/webpage/oracle-l