but if you direct load dups into a table with a unique
cons/index, won't the index be left as 'UNUSABLE' thus
necessitating an index rebuild anyway. If the index
was non-unique, then this is not a problem, but in
this case, you don't need KEEP INDEX anyway.
Happy New Year
Cheers
Connor
---
Yes, but at least the index definition will be preserved so that you could
do a simple ALTER INDEX ... REBUILD rather than finding and firing off a
script. Particularly useful if you have a lots of partitioned indexes.
Arup
- Original Message -
To: Multiple recipients of list ORACLE-L
yes but :)
It's a partitioned index. Yes, the partition goes into an UNUSABLE
state. If I drop the constraint without keep index and without saving
off the statement to rebuild it properly, I drop the ENTIRE index and I
end up with a non-partitioned index in the schema owner's default
tablespace
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
On the contrary, Connor, it's of tremendous political value, if not
practical ;)
Besides, the fact that unique indexes may have null values whereas PKs
can't.
I have found the use in this case:
In the US, we all have a Social Security Number (SSN), a sort of like a
birth mark ever since we were
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
Connor
Connor McDonald 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
