> unique constraint, unique index:
> - "keep index" redundant because effectively retains
> the constraint anyway (because you still can't insert
> dups)
>
you can insert dups via sqlloader using direct=true
so in my case, this would indeed be helpful and without the "keep
index" I lose the index when I do an alter table drop constraint
Keep index sounds like it will help me in this scenario:
primary key constraint with unique index
insert dups via sqlloader & direct=true
drop constraint with keep index
recreate constraint with exceptions into exceptions table
delete dups
re-enable constraint
this doesn't happen often, and we are working to fix the app so it
doesn't put the dups into the input file for the sqlload. However,
until it gets fixed, I need to do the above so that we actually have
usable indexes on the partitioned fact tables
- Connor McDonald <hamcdc_at_yahoo.co.uk> wrote:
> I'm a little doubtful about the value of 'keep index'.
>
> Consider the scenarios:
>
> unique constraint, non-unique index:
> - "keep index" redundant because its kept anyway
>
> unique constraint, unique index:
> - "keep index" redundant because effectively retains
> the constraint anyway (because you still can't insert
> dups)
>
>
> So far, the only use for KEEP INDEX I've found is the
> scenario where you:
>
> - decided that column(s) X was the primary key
> - created a unique index on it
> - created a primary key constraint on it
> - loaded the data
> - decided actually X was NOT the primary key, just a
> unique value
> - decided that X could allow nulls as well
> - dropped the primary kept, kept the index and then
> added a unique constraint...
>
> I would contend that this is a rare occurrence ?
>
> Cheers
> Connor
>
>
> --- Rachel Carmichael <wisernet100_at_yahoo.com> wrote:
> > sigh. I need to find time to read ALL the docs.
> > Yeah, that'll happen.
> > If I can find a parallel universe where time runs at
> > a different rate.
> >
> > Thanks, I'll test this out as well.
> >
> >
> > --- Arup Nanda <arupnanda_at_hotmail.com> wrote:
> > > In 9.2, you can keep the index by using the KEEP
> > INDEX key words.
> > >
> > > ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX
> > >
> > > This will keep the index but drop the constraint.
> > Talk about having
> > > your
> > > cake and eating it too...;)
> > >
> > > HTH
> > >
> > > Arup
> > > ----- Original Message -----
> > > To: "Multiple recipients of list ORACLE-L"
> > <ORACLE-L_at_fatcity.com>
> > > Sent: Friday, December 27, 2002 4:39 PM
> > >
> > >
> > > > it'll have to wait until Monday, I'm not at work
> > until then. I'll
> > > try
> > > > it with a non-unique then
> > > >
> > > > Hey, if it works, it saves me tons of time, I
> > learn something new
> > > and I
> > > > had fun developing the single SQL statement to
> > rebuild the
> > > constraint
> > > > and index. Win-win
> > > >
> > > >
> > > > Rachel
> > > >
> > > > --- Denny Koovakattu <groups_at_koovakattu.com>
> > wrote:
> > > > >
> > > > >
> > > > > I don't have access to 9.2.0.1 right now.
> > But can you try
> > > creating
> > > > > a non-
> > > > > unique index instead of the unique index. If
> > you create a unique
> > > > > index, it gets
> > > > > dropped. That's the behavior on 8.1.x also.
> > But if it's a
> > > non-unique
> > > > > index, it
> > > > > shouldn't get dropped.
> > > > >
> > > > > Regards,
> > > > > Denny
> > > > >
> > > > > Quoting Rachel Carmichael
> > <wisernet100_at_yahoo.com>:
> > > > >
> > > > > > 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
>
=== message truncated ===
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).
Received on Sun Dec 29 2002 - 19:33:41 CST
