Re: Which Index With This Constraint?

Konstantin Kivi <konst_at_sirena.rinet.ru> wrote:

>... <user_at_nowhere.net> wrote:
>> How do I discover which index a given constraint uses?

>> I defined indexes on specific tables and columns, then defined constraints
>> which apply to the same columns. I think Oracle may manage to understand
>> and use the existing indexes.

>> How can I verify that? Can't find anything in dba_constraints,
>> dba_indexes.

>> Thanks for any info!

>> A. Gordon
>> aog_at_capgroup.com

>I don't know the answer to your question (but I think can figure it out)
>but you may name your constraint explicitly and in this case
>the name of the index will be the same as the constraint
>name. You can also make use of STORAGE and USING INDEX TABLESPASE
>to control parameters of the index.

>

>--
>Sincerely Yours, Konstantin Kivi, Russia, konst_at_sirena.rinet.ru
> aka <k-kivi_at_usa.net>, 2:5020/457.24_at_fidonet.org
>

>

Konstantin is correct about the storage parameter and using index clauses on the add constraint clause (Very valuable).

But to answer your question, Oracle is smart enough to know to use existing indexes to enforce constraints. You will not be able to drop the unique index until you disable the primary key constraint.

I tried this with a simple one column table. I created a unique index on the table and then added an unnamed primary key constraint.

Then I tried to drop the index. The error I got was

drop index test_col1
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

BUT, it gets even more interesting. I did a

alter table test disable constraint sys_C0010208;

Table altered.

and attempted to drop the index but it was ALREADY dropped by the disable clause above.

mmmm Received on Thu Dec 03 1998 - 17:47:35 CST