Re: enabling primary key

"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:3AB73DE0.71B5_at_yahoo.com...
> Phillip wrote:
> >
> > Let's say I used this statement to create a primary key on a table:
> >
> > ALTER TABLE "SLSPH1EE" ADD CONSTRAINT "SLSPH1EEP1" PRIMARY KEY
> > ("SLS_ORG_CHILD","SLS_PRD_CHILD","SL
> > S_DET_NUM","SLS_YEAR","SLS_PERIOD","SLS_WEEK","SLS_DAY") USING INDEX
> > PCTFREE 10 INITRANS 2 MAXTRANS
> > 255 STORAGE (INITIAL 314572800 NEXT 314572800 MINEXTENTS 1 MAXEXTENTS
> > 2147483645 PCTINCREASE 0 FREEL
> > ISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
> > "SLSPH1INDXP_NU" ENABLE NOVALIDATE
> >
> > Then I disabled the primary key using the "alter table SLSPH1EE disable
> > primary key;" command. If I want to re-enable the primary key, does it
> > essentially run this statement again? In other words, does "alter table
> > SLSPH1EE enable primary key;" execute the above statement?
> >
> > What are some ways to improve the performance of re-enabling the primary
> > key? Please copy all responses to tienp_at_wholefoods.com. Thanks.
> >
> > --
> > Phillip
>
> disabling drops the underlying index, re-enabling recreates it.
>
> One option you could consider to is create a non-unique index and then
> add the primary key. The primary key can use this and disabling will
> not drop the index.

Perfectly true, all of it. However, there is one other thing that will cause a non-unique index to be created to enforce Primary Keys, without you having to define your own index.... make the constraint 'deferrable'. Deferrable constraints MUST use non-unique indexes, otherwise you would never be able to defer the constraint! The index would always be booting out violating records, even though the table is supposed to house them until commit time.

Deferrable constraints don't actually have to be deferred.... nothing to stop you creating the constraint 'deferrable initially immediate'... that will still use a non-unique index (and hence it won't be dropped when the constraint is disabled, and hence there won't be a mammoth rebuild when you re-enable). Of course, if you really want them deferred, then 'deferrable initially deferred' is your cup of tea.

I always recommend that all primary key and unique constraints should be created with the 'deferrable' key word for precisely this 'disable=potential loss of index' problem. I think if 600M of Index is suddenly going to do a disappearing act on me, I at least ought to be in charge of the process!

Regards
HJR
>
> hth
> connor
> --
> ===========================================
> Connor McDonald
> http://www.oracledba.co.uk (mirrored at
> http://www.oradba.freeserve.co.uk)
>
> "Some days you're the pigeon, some days you're the statue"
Received on Wed Mar 21 2001 - 01:28:03 CST