Re: pros and cons (now: KEEP INDEX)

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Fri, 2 Apr 2004 02:08:04 +0100 (BST)
Message-ID: <20040402010804.82112.qmail@web25203.mail.ukl.yahoo.com>

Has anyone come up with a reason why you'd want the concept of a KEEP INDEX option? Consider the scenarios

unique constraint, unique index KEEP INDEX serves no purpose (constraint is gone, but unique index still enforces it)
unique constraint, non-unique index KEEP INDEX serves no purpose (index stays anyway)

I'm guessing 'keep' was included for completeness, as the complement to the 'drop index' syntax

Am I missing something obvious? My initial thought was that it would be useful for partition level ops, eg

disable constraint keep index
modify local index for partition X unusable
load into partition X
rebuild local index for partition X
enable constraint

so that only the single index partition requires work. But that doesn't work for a unique index:

SQL> create table T ( x number, y number, z number ) 2 partition by range (x)
3 ( partition p1 values less than (100), 4 partition p2 values less than (200));

Table created.

SQL>
SQL> create unique index T_U on T ( x, y ) local;

Index created.

SQL>
SQL> alter table T add constraint T_UQ unique (x,y);

Table altered.

SQL>
SQL> alter table T disable constraint T_UQ keep index;

Table altered.

SQL>
SQL> alter index T_U modify partition p1 unusable;

Index altered.

SQL>
SQL> alter session set skip_unusable_indexes = true;

Session altered.

SQL>
SQL> insert into T values (1,1,1);
insert into T values (1,1,1)
*
ERROR at line 1:
ORA-01502: index 'PD71986.T_U' or partition of such index is in unusable state

Cheers
Connor

Richard Foote <richard.foote_at_bigpond.com> wrote: > Hi Mark,
>
> The index doesn't have to be dropped if you use the KEEP INDEX option (9i).
>
> Cheers
>
> Richard
>
> From: "Bobak, Mark" <Mark.Bobak_at_il.proquest.com>
> To: <oracle-l_at_freelists.org>
> Sent: Wednesday, March 31, 2004 10:41 AM
> Subject: RE: pros and cons
>
>
> No gotchas that I'm aware of.
>
> I look at it as an adavantage, because when you're doing table maintenance,
> you can disable the PK/UK constraint, and the corresponding index won't be
> dropped. In the case of a unique index, any time you diable the
> corresponding constraint, the index will be dropped. (It has to be, else
> the column would still have the unique property, due to the index.)
>
>
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------

Connor McDonald

Co-author: "Mastering Oracle PL/SQL - Practical Solutions" - available now
web: http://www.oracledba.co.uk
web: http://www.oaktable.net

email: connor_mcdonald_at_yahoo.com

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"

WIN FREE WORLDWIDE FLIGHTS - nominate a cafe in the Yahoo! Mail Internet Cafe Awards www.yahoo.co.uk/internetcafes

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Apr 01 2004 - 19:04:29 CST