Re: orace sql query

On Jun 23, 6:18 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Charles Hooper wrote:
> > On Jun 23, 2:10 pm, DA Morgan <damor..._at_psoug.org> wrote:
> >> fitzjarr..._at_cox.net wrote:
> >>> >From the documentation:
> >>> "Oracle enforces all PRIMARY KEY constraints using indexes.
> >> To the best of my knowledge the document is incorrect.
>
> >> The index is used to speed the search for a duplicate value ... not
> >> to enforce it.
>
> >> I will gladly stand corrected on this if someone has reason to
> >> believe otherwise but were that not true you could not enforce
> >> uniqueness with a non-unique index.
>
> >> I didn't find the support I wanted in the time I have available
> >> right now but let me quote from Tom Kyte:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3...
> >> =========================================================================
> >> "The relationship between indexes and constraints is:
>
> >> o a constraint MIGHT create an index or use an existing index to
> >> efficient[ly] enforce itself. For example, a PRIMARY KEY constraint
> >> will either create an index (unique or non-unique depending) or it will
> >> find an existing suitable index and use it.
>
> >> o an index has nothing to do with a constraint. An index is an index.
>
> >> So, a constraint MIGHT create/use and index. An INDEX is an INDEX,
> >> nothing more, nothing less."
> >> =========================================================================
>
> >> Please send me the link to the document in question. Thanks.
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damor..._at_x.washington.edu (replace x with u to respond)
> >> Puget Sound Oracle Users Groupwww.psoug.org
>
> > I am now a bit confused on this topic, or are we reading things
> > differently?
>
> > Create a generic table without a primary key:
> > CREATE TABLE T1 (
> > C1 VARCHAR2(20),
> > C2 VARCHAR2(20));
>
> > Table created.
>
> > Create a unique index on the C1 column of the table:
> > CREATE UNIQUE INDEX IND_T1 ON T1(C1);
>
> > Index created.
>
> > Specify that column C1 is the primary key for T1:
> > ALTER TABLE T1 MODIFY (PRIMARY KEY (C1));
>
> > Table altered.
>
> > Take a look at the indexes for the table:
> > SELECT
> > INDEX_NAME
> > FROM
> > DBA_INDEXES
> > WHERE
> > TABLE_NAME='T1';
>
> > INDEX_NAME
> > ----------
> > IND_T1
>
> > DROP INDEX IND_T1;
>
> > ERROR at line 1:
> > ORA-02429: cannot drop index used for enforcement of unique/primary
> > key
>
> > The above seems to be in agreement with the posts by David
> > Fitzjarrell, Ed Stevens, Frank van Bortel, and the Oracle
> > documentation. Or, is this not a valid test?
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
>
> I know what the error message says.
>
> And I know what the document Frank posted the link to says.
>
> And until corrected by someone I stand by what I wrote.
>
> Consider this:
>
> SQL> select constraint_name, constraint_type, deferrable
> 2 from user_constraints
> 3 where table_name = 'UCLASS';
>
> CONSTRAINT_NAME C DEFERRABLE
> ------------------------------ - --------------
> PK_UCLASS P DEFERRABLE
> CC_UCLASS_CLASS_NAME C NOT DEFERRABLE
>
> SQL> SELECT index_name, index_type, uniqueness
> 2 FROM user_indexes
> 3 WHERE table_name = 'UCLASS';
>
> INDEX_NAME INDEX_TYPE UNIQUENES
> ------------------------------ --------------------------- ---------
> PK_UCLASS NORMAL NONUNIQUE
>
> SQL> DROP INDEX pk_uclass;
> DROP INDEX pk_uclass
> *
> ERROR at line 1:
> ORA-02429: cannot drop index used for enforcement of unique/primary key
>
> I don't believe a non-unique index is being used for enforcement of
> a unique condition. Any comment Tom? Jonathan? Richard? Howard? Except
> to speed the up the search.

Unless you look at the code that oracle is using internally I think this discussion is amusing at best. Oracle could well have 2 or more pieces of code that get invoked depending if the index is unique or not, etc.

The interpretation by Charles appears to make the most sense to me but again unless you look at the actual code that oracle is using, what the heck difference does it make?

Does it work either way ( having a primary key define on either a unique or non unique index )?

I think it does. Received on Sat Jun 23 2007 - 17:50:41 CDT