Besides just trying the drop index, is there a query to show which index enforces the constraint for the table?

When I do the following, the first index created is used to enforce the constraint, but it is less appropriate than the unique index?


--> tried with 8i, 9i, 10g

drop table t;

create table t (x int, a int, b int);

create index t_x_a on t (x, a);

create index t_x_b on t (x, b);

create UNIQUE index t_uk on t (x);

alter table t add constraint t_pk primary key (x);

drop index t_x_a;


drop table t;

they are all equally appropriate.

but this shows
a) how to see (in 9i and above) which one is used
b) how to pick which one to use

ops$tkyte@ORA9IR2> create table t (x int, a int, b int);
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_x_a on t (x, a);
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_x_b on t (x, b);
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create UNIQUE index t_uk on t (x);
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t add constraint t_pk primary key (x) using index t_uk;
 
Table altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select constraint_name, constraint_type, INDEX_NAME
  2    from user_constraints
  3   where table_name = 'T';
 
CONSTRAINT_NAME                C INDEX_NAME
------------------------------ - ------------------------------
T_PK                           P T_UK
 

Hello Tom,
Ours is a financial reporting system. Our FACT data is distribued into 2 tables. All Current and futuristic data is tored in a table called CUBE_MSTR_CURR and historical data is stored in a table called DRI_888_MASTER_HISTORY.

During a month end we move that months data into DRI_888_MASTER_HISTORY. Both these fact table have a primary key with LOCAL INDEXES.

Structure of DRI_888_MASTER_HISTORY & CUBE_MSTR_CURR
Name Null? Type
----------------------------------------- -------- ----------------------------
V_UNIT_CDE NOT NULL VARCHAR2(4)
V_SEGMENT NOT NULL VARCHAR2(20)
V_ROW_CDE NOT NULL VARCHAR2(4)
V_COL_CDE NOT NULL VARCHAR2(4)
V_MONTH NOT NULL VARCHAR2(6)
V_SCENARIO NOT NULL VARCHAR2(5)
N_AMOUNT NOT NULL NUMBER(22,7)
F_REPORT_TYPE NOT NULL CHAR(1)
F_PROC_FLAG CHAR(1)
D_DATETIME DATE

We first disable the primary key on each of the 2 tables and then exchange the table's corresponding month partition. This way our data gets archived into the history table.

Till last week we had global indexes on our tables and now we have migrated them to Local indexes. We get the following error while performing the operation.

See below. Kindly request your help.

20071018:12:42:35 The dynamic SQL statement is ALTER TABLE CUBE_MSTR_CURR DISABLE PRIMARY KEY
20071018:12:42:35 Completed DISABLE Primary Key on CUBE_MSTR_CURR
20071018:12:42:35 Swapping previous month's partition from cube_mstr_curr with table temp_swap_table
20071018:12:42:35 The dynamic SQL statement is ALTER TABLE cube_mstr_curr EXCHANGE PARTITION P200706 WITH TABLE temp_swap_table
20071018:12:42:36 DISABLE Primary Key in DRI_888_MASTER_HISTORY
20071018:12:42:36 The dynamic SQL statement is ALTER TABLE DRI_888_MASTER_HISTORY DISABLE PRIMARY KEY
20071018:12:42:36 Completed DISABLE Primary Key on DRI_888_MASTER_HISTORY
20071018:12:42:36 Swapping temp_swap_table with Previous Month's Partition in DRI_888_MASTER_HISTORY
20071018:12:42:36 The dynamic SQL statement is ALTER TABLE dri_888_master_history EXCHANGE PARTITION P200706 WITH TABLE temp_swap_table
20071018:12:42:37 Deleting Flash Values from History
20071018:12:42:37 The dynamic SQL statement is DELETE dri_888_master_history PARTITION (P200706) WHERE V_SCENARIO = 'FLASH'
20071018:12:42:37 ENABLE Primary Key in CUBE_MSTR_CURR
20071018:12:42:37 The dynamic SQL statement is ALTER TABLE CUBE_MSTR_CURR ENABLE PRIMARY KEY
20071018:12:42:37 Error while ENABLE Primary Key - CUBE_MSTR_CURR
20071018:12:42:37 Error Message : ORA-14063: Unusable index exists on unique/primary constraint key

Hello Tom,
The structure of the table is as given below.

Name Null? Type
----------------------------------------- -------- ----------------------------
V_UNIT_CDE NOT NULL VARCHAR2(4)
V_SEGMENT NOT NULL VARCHAR2(20)
V_ROW_CDE NOT NULL VARCHAR2(4)
V_COL_CDE NOT NULL VARCHAR2(4)
V_MONTH NOT NULL VARCHAR2(6)
V_SCENARIO NOT NULL VARCHAR2(5)
N_AMOUNT NOT NULL NUMBER(22,7)
F_REPORT_TYPE NOT NULL CHAR(1)
F_PROC_FLAG CHAR(1)
D_DATETIME DATE



Its Range partitioned by v_month and LIST Sub partitioned by V_SCENARION.

The primary key is on the below

V_UNIT_CDE
V_SEGMENT
V_ROW_CDE
V_COL_CDE
V_MONTH
V_SCENARIO

Initially we had a Global Index on the primary key combination and now we have implemented Local Indexes when we hit the error.

Regards