Re: rebuild PK index

In article <81eb8t$vp5$1_at_nnrp1.deja.com>,   polguls_at_my-deja.com wrote:
> In article <7v7pcu$i4l$1_at_nnrp1.deja.com>,
> James Lorenzen <james_lorenzen_at_allianzlife.com> wrote:
> > To "drop" a PK index, "ALTER TABLE xxxxx DROP PRIMARY KEY;" You
[snip]
> > HTH
> > James
> Currently I'am rebuilding indexes to the different tablespace.
> But I do not want to rebuild primary constraints indexes as well as
> unique constraints. And I did not find any reference between
constraint
> and index in the data dictionary. Do you know how to determine if
> index 'belongs' to constraint?
>

In Oracle, both PRIMARY KEY and UNIQUE constraints are enforced by using indexes. The link between the index and the constraint that it is enforcing is the name IE (OWNER to OWNER and INDEX_NAME to CONSTRAINT_NAME). If I correctly understand what you are asking, the select for getting indexes to rebuild would be:
 — SELECT owner, index_name FROM DBA_INDEXES  — WHERE (owner, index_name) NOT IN (SELECT owner, constraint_name FROM dba_constraints)

This will return all indexes that are not supporting a constraint.

HTH
    James

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Nov 23 1999 - 09:51:32 CST