RE: (not) dropping indexes with PK constraints in 10g

your suggestions are right, but my users had their problems the other way round: The relied on the index BEING dropped (i.e. not using the "drop index" clause) and now found that it still remains in place in 10g.

"skip_unusable_indexes" will not do with unique constraints.

I suppose that's another reason why non-unique indexes supporting PK constraints were introduced, apart from the local partitioning capability.

> From memory, I thought, even back in 8i days, if index supporting PK was
> unique, then it was dropped when constraint is disabled.

Well, have a look at the bugs 3835171 and 1240495. It seems like it *shouldn't* have been that way in 8i.

Best regards,
Uwe

Am Do, 20.08.2009, 15:13, schrieb Bobak, Mark:
> Hi Uwe,
>
>
> From memory, I thought, even back in 8i days, if index supporting PK was
> unique, then it was dropped when constraint is disabled. If it's
> non-unique, then it's retained when constraint disabled.
>
> Anyhow, assuming you want to retain explicitly created index, it seems to
> me, your process should do something like this:
>
> Alter table xxx disable constraint cons1 keep index;
> Alter index pk_ind unusable;
> Alter session set skip_unusable_indexes = true;
> <do data load here>
> Alter index pk_ind rebuild nologging;
> Alter table xxx enable constraint cons1;
>
>
> Hope that helps,
>
> -Mark
>
>
> -----Original Message-----

[deleted]

--
http://www.freelists.org/webpage/oracle-l