how to identify implicitly created constraint indexes

[Advocate]

Can anyone tell me how to identify if a pk index has been :

a. explicitly created by a user
or
b. has been implicitly created by oracle when a user creates a pk constraint

info from data dictionary index and constraint tables seems to be identical

i already know one destructive way ... disable the constraint and see if the index is auto dropped

but i would really like a non-intrusive methodology ...


regards, david - no such thing as problems - only solutions.

 

[SantaMufasa]

Advocate,

First of all, a unique index looks no different from the unique index that results from the existence of a PK. Therefore, doesn't the existence of a PK constraint trump the existence of a unique index on a column with no PK constraint?

Let us know if my reasoning resolves your issue.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Advocate]

Hi Mufasa,

what are you doing up so late/early ?!! here is just gone 4pm and nearly finish time ...

working on re-engineering some existing partition swapping ...

inserting into non-partitioned table ...

disable constraints
make indexes unusable
skip_unusable_indexes

(to be re-enabled after large data load etc)

but get errors when trying to insert records into table with unique indexes enforcing unique constraint (ORA_01502)

so disable constraint but leave index alone ... works ok

however, would like to identify all explicitly created pk indexes (followed by pk constraint) and replace with indexes auto created by pk constraint

hence, the need to identify them ...
hope that does not sound too confusing ...




regards, david - no such thing as problems - only solutions.

 

[SantaMufasa]

David,

I'm still a bit fuzzy on your need, but does this SQL query help you out...it identifies all of the tables with explicit PK constraints (and the PK constraint names):

col a heading 'Table Owner.Table Name' format a40
select owner||'.'||table_name a, constraint_name
from dba_constraints
where [b]constraint_type = 'P'[/b] -- Primary Keys
order by owner, table_name;

Table Owner.Table Name                   CONSTRAINT_NAME
---------------------------------------- ------------------------------
...
TEST.ADJUSTMENTTYPE                      SYS_C009670
TEST.APOSTOL                             SYS_C009747
TEST.GEEK                                OPPORTUNITIES_PK
TEST.LOUIS                               LOUIS_ID_PK
TEST.PUBLISHER                           SYS_C009648
TEST.ROBERTO                             SYS_C009909
TEST.S_CUSTOMER                          S_CUSTOMER_ID_PK
...

Let us know how this differs from what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Advocate]

i have several tables which have previously been created using following steps :

1. create unique index (intended for primary key)
2. create pk constraint using previous index

i wish to replace these with an index which is implicitly created by a pk constraint

this will allow me to disable constraint, which will automatically disable index before data load

i need to be able to identify which tables have been created using explicit indexes/constraints pk combinations
and
replace them with pk constraints with implicit indexes

and here lies the problem ... how do i non-destructively identify indexes created in one of two different ways ???

thank you for your perseverence ...


regards, david - no such thing as problems - only solutions.

 

[SantaMufasa]

Advocate,

I believe that you are making the problem into more than it needs to be. Once you place a PRIMARY KEY constraint on a table, pre-existing unique index or not, if you disable the PRIMARY KEY constraint, it will disable the index (and the constraint), allowing you to do the load without PK involvement. Here is proof of that concept:

create unique index advocate_UK on advocate(x);

Index created.

alter table advocate add constraint advocate_PK primary key(x);

Table altered.

insert into advocate values (1);

1 row created.

insert into advocate values (1);

*
ERROR at line 1:
ORA-00001: unique constraint (TEST.ADVOCATE_PK) violated

alter table advocate disable constraint advocate_PK;

Table altered.

insert into advocate values (1);

1 row created. <-- This is a duplicate value disobeying the UNIQUE enforcement...[b]IF IT WAS BEING ENFORCED...BUT IT'S NOT.[/b]

alter table advocate enable constraint advocate_PK;
*
ERROR at line 1:
ORA-02437: cannot validate (TEST.ADVOCATE_PK) - primary key violated

So, does the above not prove that your concern is unnecessary?

Let me know.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Advocate]

Dave, one of us is missing the point and i am worried that it is me, but ...

i noticed that you have not made pk unique index unusable
ie
ALTER INDEX advocate_UK UNUSABLE;

simply disabling the pk constraint does not seem to affect the explicit pk index

i have been asked to disable pk constraints and make pk index unusable during data loads ... not my decision but tech architect etc

after i disable pk constraint (with explicit index) and alter index unusable, i get ORA-01502 ...

***************************************
ORA-01502: index 'string.string' or partition of such index is in unusable state
Cause: An attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation
Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition
***************************************

hence aim to get rid of explicit unique indexes ... but i first need to identify them ...

if we ignore the reasons for a moment and go back to original question ... how do i identify and differentiate between explicit/implicit pk indexes ?



regards, david - no such thing as problems - only solutions.

 

[SantaMufasa]

...simply disabling the pk constraint does not seem to affect the explicit pk index.

First, I disagree with your assertion that "simply disabling the pk constraint does not seem to affect the explicit pk index". In my proof-of-concept, earlier, when I disabled the PK, the index that enforces UNIQUENESS also is disabled, as evidenced by the fact that I was able to successfully INSERT a duplicate record. Had the UNIQUE INDEX not been disabled, then it would have thrown an error. If/when you re-enable the PK constraint, then the index is refreshed/re-built and re-enforces the UNIQUE constraint. Until then, the index is disabled.

Additionally, do you have a documentation reference that uses (and defines) the terms "explicit/implicit pk indexes"? As far as I know, that terminology does not represent Oracle nomenclature.

If a declarative PK constraint exists at all, then (regardless of whether its UNIQUE INDEX pre-dated the creation of the constraint), the index enforces the uniqueness of the PK column's data, and the PK constraint enforces the NOT NULL characteristic of the PK column(s). Without the declarative PK constraint, then the UNIQUE INDEX can still only enforce UNIQUENESS; a CHECK constraint would need to exist to enforce NOT NULL on the column.

The concept of an "explicit/implicit pk index" is a concept that, I believe, does not exist in the Oracle World. Resultingly, I heartily disagree with your "tech architect, et. al." that are trying to make it an issue.

If you re-state your business/technical objectives on this topic (rather than become distracted by some possible/perceived "data voodoo" of a tech architect [who may be mis-informed about Oracle behaviour]) I believe we can achieve your goals.

(BTW, if someone can show me the business/performance benefits of causing a standard index to become "UNUSABLE", then I am happy to retract my mild criticism and deliver a deserved "atta girl/boy" to the architect. But all of the reading I have seen regarding the "UNUSABLE" clause show possible benefits only in extremely obscure situations.)

Let me know.



Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Advocate]

Dave,

Oracle does not have a monopoly on the english dictionary and i believe that my use of the terminology explicit/implict is self-explanatory without the need for pedantic further explanation

if i can not get an answer to my original question, then i shall have to look elsewhere and drop this thread here - howver, if i do get an answer elsewhere, i will try to update this thread in case anyone else has a similar quandry

thanks for your contributions and appreciate your insights as usual ...


regards, david - no such thing as problems - only solutions.

 

[SantaMufasa]

Can anyone tell me how to identify if a pk index has been :

a. explicitly created by a user
or
b. has been implicitly created by oracle when a user creates a pk constraint

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SantaMufasa]

(Don't you just hate it when you hit the [Submit Post] button instead of the [Edit Post] button? Sorry for the "dangling quote", above.)

I re-posted the original question to answer it explicitly...And the answer is "No". Had I answered it directly in the first place, perhaps David (Advocate) would be a bit less torqued at me right now.

My "jousting" in earlier replies resulted from the fact that 1) Oracle doesn't have a way of telling you whether an index is "explicitly or implicitly" created and 2) whether or not it is explicit or implicit should not make any difference to you or your processing...If you disable the constraint, the index is disabled as well.

So, Dave, I hope we can still be "techno buds". If you do find a different answer to you question, please post it here and it will be my "Ya'-learn-something-new-every-day" experience.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Advocate]

dave, i cant find a way to differentiate either ... of course ill still buy you a guinness if i get to utah !!!



regards, david - no such thing as problems - only solutions.

 

[SantaMufasa]

...Thanks, Dave. And insofar as this thread is concerned, how does it fit into your motto, "No such thing as problems - only solutions?"

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SJSFoxPro]

I'm tempted to NOT get in the middle of this, but I feel compelled to emphasize some of Mufasa's concerns... and try to give you another lead in answering your original question.

There is an old article at asktom.oracle.com that might interest you about querying the data dictionary for indexes
created automatically by Oracle during the creation of constraints. Search "Automatic Indexes". In it, Tom makes it
clear that "We do not directly keep track of this but we can deduce it (almost)." If your standards have been to create the index and constraint with the same name, this likely won't help. But, it might help you end your search so you can move on.

There is a note in the Oracle SQL Reference: when you specify a unique constraint, Oracle implicitly creates an index on the unique key. If you are defining uniqueness for purposes of query performance, then Oracle Corporation recommends that you instead create the unique index explicitly using a CREATE UNIQUE INDEX statement. (That may be where you came to use the terms implicitly and explicitly with regards to index creation!)

I agree with Dave, and he clearly provided an example demonstrating you need only disable the constraint. Also, in your example when you disabled your constraint, then tried to make the index unusable, you received a message indicating that "An attempt has been made to access an index ... that has been marked unusable by a ... a DDL operation." I'm confused why you think an implicitly created index would react any differently than your explicitly created index? I agree with Dave, your problem is to make the Tech Arch's understand how Oracle works.

Before you make a major change to your database to change your index creation methodology to satisfy the Tech Archs, they/you need to think about the repercussions (query performance, locking, etc.) which drives table creation
with constraints based on explicitly created indexes.

Good luck!