From oracle-l-bounce@freelists.org Mon Jun 21 13:52:49 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i5LIqOY19666 for ; Mon, 21 Jun 2004 13:52:34 -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 i5LIqD619629 for ; Mon, 21 Jun 2004 13:52:24 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C9B4872C75F; Mon, 21 Jun 2004 13:35:59 -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 26802-79; Mon, 21 Jun 2004 13:35:59 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 03F8772C6AC; Mon, 21 Jun 2004 13:35:59 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 21 Jun 2004 13:34:24 -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 8142372C731 for ; Mon, 21 Jun 2004 13:34:23 -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 27255-04 for ; Mon, 21 Jun 2004 13:34:23 -0500 (EST) Received: from brmea-mail-4.sun.com (brmea-mail-4.Sun.COM [192.18.98.36]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0E5B972C6C5 for ; Mon, 21 Jun 2004 13:34:23 -0500 (EST) Received: from phys-giza-1 ([129.147.4.102]) by brmea-mail-4.sun.com (8.12.10/8.12.9) with ESMTP id i5LIrn53009666 for ; Mon, 21 Jun 2004 12:53:49 -0600 (MDT) Received: from conversion-daemon.giza-mail1.Central.Sun.COM by giza-mail1.Central.Sun.COM (iPlanet Messaging Server 5.2 HotFix 1.24 (built Dec 19 2003)) id <0HZO007019LFZ0@giza-mail1.Central.Sun.COM> (original mail from Daniel.Fink@Sun.COM) for oracle-l@freelists.org; Mon, 21 Jun 2004 12:55:39 -0600 (MDT) Received: from sun.com (sr1-ubrm-15.Central.Sun.COM [129.147.4.62]) by giza-mail1.Central.Sun.COM (iPlanet Messaging Server 5.2 HotFix 1.24 (built Dec 19 2003)) with ESMTP id <0HZO006WC9WG49@giza-mail1.Central.Sun.COM> for oracle-l@freelists.org; Mon, 21 Jun 2004 12:55:30 -0600 (MDT) Date: Mon, 21 Jun 2004 12:55:28 -0600 From: Daniel Fink Subject: Re: Question about Append hint in Insert In-reply-to: <52883.213.162.65.17.1087832520.bloek@pwebmail.utanet.at> To: oracle-l@freelists.org Message-id: <40D72F20.4040905@sun.com> MIME-version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-Accept-Language: en-us, en User-Agent: Mozilla/5.0 (X11; U; SunOS sun4u; en-US; rv:1.4) Gecko/20040414 References: <144301c45715$342c6170$3c02a8c0@JARAWIN> <52883.213.162.65.17.1087832520.bloek@pwebmail.utanet.at> X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 3234 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Daniel.Fink@Sun.COM Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org One other 'subtle' difference in this scenario is that you cannot perform an INDEX UNIQUE SCAN on the index when you are using an equality predicate. IIRC, the CBO will cost the allowable RANGE SCAN the same as the UNIQUE SCAN so there should not be differences in query plan selection nor execution time. If you are using explain plan/tkprof, etc. you will see a drastic difference in the card(inality) estimate, but this does not really factor in to the equation (in this case). Also, to keep things clear and supportable, make sure to document the reason for doing this. Regards, Daniel jaromir nemec wrote: > Hi Lex, > > Thanks for the correction of my misinterpretation. > Does it mean there are no other differences or things to keep in mind when > deciding between unique and non-unique index to support unique constraint? > > Thanks > Jaromir D.B.Nemec > > > >>one small correction: non-unique indexes do *not* allocate more space... > > > > > > ---------------------------------------------------------------- > 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 -----------------------------------------------------------------