Re: Was: Rebuilding Indexes, Now: KEEP INDEX

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 anyway (because you still can't insert
> dups)

As it's done now it can be useful for some real life cases, some of them can be very rare. However keep option was introduced, so it could mean that somebody spent at least some time to design it or make a stub(?), right?

I think the reasons are:

. To have consistent statement semantics (DROP / KEEP)

. Perhaps this functionality will be extended in the further Oracle RDBMS editions

. Performance of Exchanging Partitions can be improved, please refer to docs/metalink.

. In case some apps. table reorganization needs to be done e.g. primary key is based in unique index, however the constraint has to be extened -- suppose an application is going to support multi-organization feature or whatever else as a temporary solution KEEP index can be used -- I suspect that it sounds like not well thought design but life is cruel -- sometimes is good to have indexes... Thread participants have provided some examples already.

Kind Regards,

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vladimir Begun
  INET: vladimir.begun_at_oracle.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).