Re: Rebuilding Indexes...

Hey Rachel,

Consider using a non-unique index for your primary key constraint. If you prebuild it and then add the constraint, Oracle will not drop the index when you drop the PK constraint, and you can control the index build that a way (and build it in parallel to boot).

hth,

Jack

> 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).
>
>
>

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).