Re: Rebuilding Indexes...

Rachel,

Try a pre-created non-unique index. This should remain after the constraint it dropped, and can be used to enforce the primary key constraint (not to mention be created in parallel nologging mode.)

hth,

Jack

> 9.2.0.1 Solaris, and yes, it does drop it
>
> I created a unique index in the primary key columns
> I created the primary key constraint without specifying an index
> I checked that the index existed, it did
> I dropped the primary key constraint
> I checked that the index existed, it didn't
>
> try it.... I tried various combinations before posting this note
>
>
> --- Denny Koovakattu <groups_at_koovakattu.com> wrote:
> >
> >
> > If you build a separate index to enforce the primary key, Oracle
> > shouldn't
> > drop it when you disable or drop the primary key.
> >
> > Regards,
> > Denny
> >
> > Quoting Rachel Carmichael <wisernet100_at_yahoo.com>:
> >
> > > Here's a reason:
> > >
> > > have you ever tried to find the three duplicate rows in a 12
> > million
> > > row table without using the primary key constraint? I've had to
> > > disable
> > > or drop the constraint in order to use the exceptions table. Once
I
> > do
> > > that, even if I've built a separate index that enforces the
primary
> > > key
> > > constraint, Oracle drops the index. So I HAVE to rebuild it. If I
> > > allow
> > > the index to be rebuilt when I re-enable the primary key
> > constraint,
> > > it
> > > builds it in the default tablespace of the table owner, not where
I
> > > want it.
> > >
> > > if anyone has a better way to fix this problem, I'm more than
happy
> > to
> > > hear it! It's a data warehouse and the third party app has a bug
we
> > > can't find and on occasion sqlloads (via direct path) duplicate
> > rows
> > >
> > > Rachel
> > >
> > > --- Jared Still <jkstill_at_cybcon.com> wrote:
> > > >
> > > > Though I have published a script for determining indexes that
> > > > need to be rebuilt, and then rebuilding them, I have to say
that
> > > > this is almost never necessary.
> > > >
> > > > Why are you rebuilding indexes? About the only reason for ever
> > > > doing so is that the BLEVEL >= 5.
> > > >
> > > > goto asktom.oracle.com, and do a search on 'index rebuild'.
> > > >
> > > > Currently, the third article may be of interest.
> > > >
> > > > Jared
> > > >
> > > > On Thursday 26 December 2002 12:24, Richard Huntley wrote:
> > > > > Anyone have any useful scripts for doing this?
> > > > >
> > > > > TIA,
> > > > > Rich
> > > >
> > > > ----------------------------------------
> > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment:
> > 1"
> > > > Content-Transfer-Encoding: 7bit
> > > > Content-Description:
> > > > ----------------------------------------
> > > > --
> > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > > --
> > > > Author: Jared Still
> > > > INET: jkstill_at_cybcon.com
> > > >
> > > > Fat City Network Services -- 858-538-5051
> > http://www.fatcity.com
> > > > San Diego, California -- Mailing list and web hosting
> > > services
> > > >
> > >
> > --------------------------------------------------------------------
-
> > > > To REMOVE yourself from this mailing list, send an E-Mail
message
> > > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and
> > in
> > > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > > (or the name of mailing list you want to be removed from). You
> > may
> > > > also send the HELP command for other information (like
> > subscribing).
> > > >
> > >
> > >
> > > __________________________________________________
> > > Do you Yahoo!?
> > > Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> > > http://mailplus.yahoo.com
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Rachel Carmichael
> > > INET: wisernet100_at_yahoo.com
> > >
> > > Fat City Network Services -- 858-538-5051
http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting
> > services
> > >
> > --------------------------------------------------------------------
-
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and
in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You
may
> > > also send the HELP command for other information (like
> > subscribing).
> > >
> > >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Denny Koovakattu
> > INET: groups_at_koovakattu.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting
services
> > --------------------------------------------------------------------
-
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> http://mailplus.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Rachel Carmichael
> INET: wisernet100_at_yahoo.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>

Thanks,

Jack Silvey

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jack Silvey
  INET: oracle-l_at_warehousedba.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).