Table alter table t_new add constraint t_new_pk primary key(x) CREATE TABLE tĩ PARTITION part1 VALUES LESS THAN ( 1000000 ),ġ0 PARTITION part2 VALUES LESS THAN ( 2000000 ),ġ1 PARTITION junk VALUES LESS THAN (MAXVALUE)Ĥ where rownum insert /*+ append */ into t_newĤ where rownum alter table t add constraint t_pk primary key(x) using index local
WHY KEEP INDEX !?!?!?!?!?!?!?!ĭug around, turns out it can be useful when doing certain partition operations.Īlter table exchange partition with unique/primary keys for example. Ie, get successful constraint usage as long as we stay away from the problem index partition. ORA-00001: unique constraint (MCDONAC.TX) violated So according to Oracle, our constraint is just fine, even though its backed by an unusable index partition. SQL> select status, validated from user_constraints So we achieved the load without disabling the constraint at all.Even more interesting is: we still loaded in there with no problems.The constraint was not used (since we've loaded duplicates). So maybe this is the reason its there ? Well, lets repeat without using keep index.Ĭ:\oracle\ora92\bin>sqlldr userid=mcdonac/**** control=c:\c.ctl direct=true skip_index_maintenance=true Load completed - logical record count 11.
SQL> alter index TX modify partition p1 unusable Ģ select rownum, rownum, rownum from all_Objectsģ where rownum sqlldr userid=mcdonac/**** control=c:\c.ctl direct=true skip_index_maintenance=true SQL> select index_name from user_indexes where table_name = 'T' SQL> alter table T disable constraint TX keep index SQL> alter session set skip_unusable_indexes = true SQL> alter table T add constraint TX primary key ( p ) SQL> create unique index TX on T ( p ) local Without KEEP INDEX the entire index would need be to rebuilt/recreated."Ħ ( partition p1 values less than ( 100 ),ħ partition p2 values less than ( 200 ) ,Ĩ partition p3 values less than ( 300 )) Whereas we only want to rebuild those index *partitions* that are unusable. The KEEP INDEX becomes useful because the constraint applies to the whole table, enable or add constraint, exceptions into etc
rebuild just those index partitions that are unusable SQLLDR Direct load, some rows violate constraint, so 1 or more index Unique or PK constraint backed by local (or otherwise partitioned) unique On the other hand, you want to create constraints *after* indexes in order to avoid creating redundant indexes - but you can't be picky as to which tablespace the index that supports your constraint will be in. ORA-01408: such column list already indexedīut if you try to create the index in a specific tablespace or with specific storage parameters, you'd hit an unexpected error, if there already is a non-unique index that could be used to enforce the constraint.īottom line is, in your schema creation scripts, constraints should be created before indexes, if you plan on using "using index". (which makes my above point somewhat moot :-/ )*/ġ0:20:15 table t add constraint pk_t primary key(x) USING INDEX tablespace tools Īlter table t add constraint pk_t primary key(x) USING INDEX tablespace tools * Look - the index is still there, even without KEEP INDEX * See - it is using the non-unique index to enforce the PK */ To Yun: There's a bit of caveat with "using index":ġ0:17:24 table t add constraint pk_t primary key(x) So, I may well want to drop the constraint because the data I'm inserting/updating will be inconsistent, but I still need the (non-unique) index to help me cleanse it fast.
I mean, one wants/doesn't want a constraint for data integrity, an index - for performance. Well I guess constraints and indexes are different beasts, no?
0 kommentar(er)
