Hello Tom,
My question is about disabling and enabling primary key constraint. My plan is to disable primary key constraint so that I can bulk load a table, then enable the primary key at the end. My problem is when I enable primary key, it does not rebuild the index that was used to create the primary key the first time. It uses some other index.
Here is a small demo:
SQL> DROP TABLE t PURGE;
Table dropped.
SQL>
SQL> CREATE TABLE t
2 (
3 a NUMBER (2),
4 b NUMBER (2),
5 c NUMBER (2),
6 d NUMBER (2),
7 e NUMBER (2)
8 );
Table created.
SQL>
SQL> ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY (a,b,c);
Table altered.
SQL>
SQL> CREATE INDEX i
2 ON t (a,
3 c,
4 b,
5 d);
Index created.
SQL>
SQL> SELECT constraint_name,
2 constraint_type,
3 table_name,
4 status,
5 index_name
6 FROM user_constraints
7 WHERE table_name = 'T';
CONSTRAINT_NAME C TABLE_NAME STATUS INDEX_NAME
--------------- - ---------- -------- ----------
PK_T P T ENABLED PK_T
SQL>
SQL> SELECT index_name,
2 table_name,
3 uniqueness,
4 status
5 FROM user_indexes
6 WHERE table_name = 'T';
INDEX_NAME TABLE_NAME UNIQUENES STATUS
---------- ---------- --------- --------
PK_T T UNIQUE VALID
I T NONUNIQUE VALID
SQL>
SQL> ALTER TABLE t DISABLE PRIMARY KEY KEEP INDEX;
Table altered.
SQL>
SQL> SELECT constraint_name,
2 constraint_type,
3 table_name,
4 status,
5 index_name
6 FROM user_constraints
7 WHERE table_name = 'T';
CONSTRAINT_NAME C TABLE_NAME STATUS INDEX_NAME
--------------- - ---------- -------- ----------
PK_T P T DISABLED
SQL>
SQL> SELECT index_name,
2 table_name,
3 uniqueness,
4 status
5 FROM user_indexes
6 WHERE table_name = 'T';
INDEX_NAME TABLE_NAME UNIQUENES STATUS
---------- ---------- --------- --------
PK_T T UNIQUE VALID
I T NONUNIQUE VALID
SQL>
SQL> DROP INDEX pk_t;
Index dropped.
SQL>
SQL> SELECT constraint_name,
2 constraint_type,
3 table_name,
4 status,
5 index_name
6 FROM user_constraints
7 WHERE table_name = 'T';
CONSTRAINT_NAME C TABLE_NAME STATUS INDEX_NAME
--------------- - ---------- -------- ----------
PK_T P T DISABLED
SQL>
SQL> SELECT index_name,
2 table_name,
3 uniqueness,
4 status
5 FROM user_indexes
6 WHERE table_name = 'T';
INDEX_NAME TABLE_NAME UNIQUENES STATUS
---------- ---------- --------- --------
I T NONUNIQUE VALID
SQL>
SQL> ALTER TABLE t ENABLE PRIMARY KEY;
Table altered.
SQL>
SQL> SELECT constraint_name,
2 constraint_type,
3 table_name,
4 status,
5 index_name
6 FROM user_constraints
7 WHERE table_name = 'T';
CONSTRAINT_NAME C TABLE_NAME STATUS INDEX_NAME
--------------- - ---------- -------- ----------
PK_T P T ENABLED I
SQL>
SQL> SELECT index_name,
2 table_name,
3 uniqueness,
4 status
5 FROM user_indexes
6 WHERE table_name = 'T';
INDEX_NAME TABLE_NAME UNIQUENES STATUS
---------- ---------- --------- --------
I T NONUNIQUE VALID
SQL>
When the primary key was created, it created PK_T index. I disabled the PK constraint and dropped the corresponding index (PK_T). After enabling the constraint, it used another index (I) on the table to support the primary key constraint. My problem is after I drop the index, I don't have the DDL of the index (PK_T). So I cannot do CREATE INDEX and use ALTER TABLE ENABLE PRIMARY KEY USING INDEX PK_T.
My question is what do I do so that when I enable the primary key (PK_T), Oracle creates PK_T index and not use the other index (I).
Thank you for your time.
unique and primary key constraints only create indexes WHEN and IF they need to - if existing indexes are better than good enough to support the constraint, we do not see the need or reason to create yet another index (it would be redundant).
If you explicitly want a certain set of indexes to exist, including the indexes that may be created by constraints, then you would need to explicitly tell us about those indexes.
You would do that by using the USING INDEX clause to explicitly tell us "we would like to have a redundant unique index created"
ops$tkyte%ORA10GR2> create table t ( x number, y number, z number );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t add constraint t_pk primary key(x);
Table altered.
ops$tkyte%ORA10GR2> create index t_idx on t(x,y);
Index created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t disable constraint t_pk;
Table altered.
ops$tkyte%ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';
INDEX_NAME UNIQUENES
------------------------------ ---------
T_IDX NONUNIQUE
ops$tkyte%ORA10GR2> alter table t enable constraint t_pk <b>using index( create unique index t_pk on t(x) );</b>
Table altered.
ops$tkyte%ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';
INDEX_NAME UNIQUENES
------------------------------ ---------
T_PK UNIQUE
T_IDX NONUNIQUE