Few days ago, we got a task to create an new index on (A,B,C) for table T, but the table T already have a primary key constraint on (A,B) and a unique index on (A,B). This was to get a current SQL run more quickly with lower consistent gets by accessing index only. How will you do it?
We can get the primary constraint using the new index need to be created without change the definition of the primary key, so we could drop the unique index created on (A,B) columns to save disk storage. As following:
SQL> CREATE TABLE T_OBJECTS AS
2 SELECT * FROM ALL_OBJECTS WHERE ROWNUM < 1000;
Table created.
SQL> CREATE UNIQUE INDEX T_OBJECTS_IDX
2 ON T_OBJECTS (OBJECT_ID, OBJECT_NAME);
Index created.
SQL> ALTER TABLE T_OBJECTS ADD CONSTRAINT PK_T_OBJECTS
2 PRIMARY KEY (OBJECT_ID) USING INDEX T_OBJECTS_IDX;
Table altered.
SQL> SELECT INDEX_NAME FROM USER_INDEXES
2 WHERE TABLE_NAME='T_OBJECTS';
INDEX_NAME
------------------------------
T_OBJECTS_IDX
However what should be considered in production? You must make decision whether the storage is really important to you. Because drop an index may change the running SQL's plan. And the new index will be bigger than the old one because we add one extra column to it, sometime the cost will be changed for using bigger index. And if the primary constraint is disabled or dropped, duplicated rows can be inputed by application.
There is also another thing we need take it into consideration, let's create a trigger on this table :
CREATE TRIGGER TRG_T_OBJECTS AFTER INSERT ON T_OBJECTS FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20002,'my error!');
end;
/
In case when there is an unique index on (A,B) :
SQL> insert into t_objects SELECT * FROM ALL_OBJECTS WHERE ROWNUM < 2;
insert into t_objects SELECT * FROM ALL_OBJECTS WHERE ROWNUM < 2
*
ERROR at line 1:
ORA-00001: unique constraint (ANYSQL.PK_T_OBJECTS) violated
In case when there is an unique index on (A,B,C) :
SQL> insert into t_objects SELECT * FROM ALL_OBJECTS WHERE ROWNUM < 2;
insert into t_objects SELECT * FROM ALL_OBJECTS WHERE ROWNUM < 2
*
ERROR at line 1:
ORA-20002: my error!
ORA-06512: at "ANYSQL.TRG_T_OBJECTS", line 2
ORA-04088: error during execution of trigger 'ANYSQL.TRG_T_OBJECTS'
So if your application always try to insert duplicate rows, you need take this into account too.