pros and cons (now: KEEP INDEX) 2004-04-01 - By Connor McDonald
Has anyone come up with a reason why you 'd want the concept of a KEEP INDEX option? Consider the
scenarios
a) unique constraint, unique index
KEEP INDEX serves no purpose (constraint is gone, but unique index still enforces it)
b) 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 (See ORA-01502.ora-code.com): index 'PD71986.T_U ' or partition of such index is in unusable state
Cheers
Connor
--- Richard Foote <richard.foote@(protected) > 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@(protected) >
> To: <oracle-l@(protected) >
> 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@(protected)
> 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@(protected)
"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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
