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: Which Index With This Constraint?

Re: Which Index With This Constraint?

From: Thomas Pall <tpall_at_bga.com>
Date: 6 Dec 98 21:58:05 GMT
Message-ID: <366afded.0@feed1.realtime.net>


Quite right. But be careful about the reverse. If you disable the constraint, Oracle drops the index. When you reinable the constraint it recreates the index. So specifying STORAGE and PLACEMENT is always important if you don't want indexes placed where you don't want them with storage params you may not want.

Murray Kaiser (mkaiser_at_pop.andara.com) wrote:
: 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 Sun Dec 06 1998 - 15:58:05 CST

Original text of this message

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