Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Bizarre index creation - something wrong?

Re: Bizarre index creation - something wrong?

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Wed, 07 Jun 2006 09:44:26 +0200
Message-ID: <8o0d82lumoivfkfsv21h7dqj7v0g7gde3d@4ax.com>


On 7 Jun 2006 00:27:14 -0700, "Paulie" <plinehan_at_yahoo.com> wrote:

>Hi all,
>
>I've just started a new job working with COBOL and a
>legacy application that is used by some very (very)
>large companies. There is a tool that we use here to
>create new tables and I just saw it working for the
>first time. It created the tables find and then produced
>an index script - see below.
>
>--------index script-----------------
>
>CREATE UNIQUE INDEX XX20301IX0
>ON ABC203
>(DSTRCT_CODE,
>SUPPLIER_NO)
>TABLESPACE M3043_3SW_IDXMSF
>PCTFREE 10
>STORAGE (INITIAL 40K NEXT 40K
>PCTINCREASE 0 MAXEXTENTS 500)
>;
>
>COMMIT;
>ALTER TABLE ABC203
>ADD PRIMARY KEY
>(DSTRCT_CODE,
>SUPPLIER_NO);
>COMMIT;
>
>---------- end index script ----------
>
>
>Now, my question is - Are these indexes not
>one and the same thing - I thought Oracle by
>default created an index on a primary key
>anyway (logically enough)? Any thoughts
>or comments appreciated.
>
>
>Paul...

No version and the behavior is version dependent. No idea why people always think we know their version of Oracle, and keep track of it. Should we stop responding completely to posts without platform and version?

Anyway, it is evidently documented, that Oracle doesn't create an index to validate a primary key when there is already a correct index present.
In other words: revert the two commands and you will get an error. (such column list already indexed)

--
Sybrand Bakker, Senior Oracle DBA
Received on Wed Jun 07 2006 - 02:44:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US