几天前接到一个任务在现在的表上建一个索引, 发现这个表的现在有一个主键索引是建在(A,B)这两列上的, 而要建的索引是在(A,B,C)列上的, 主要目标是要让一个执行频率比较高的SQL不去访问表, 以减少逻辑读, 提升性能. 这时你会如何去考虑?
实际在增加约束时是可以指定约束使用那个索引的, 这个主键可以使用新建的这个三个列的索引, 从儿可以删除掉原来的建在两个列上的索引, 以节约空间. 看如下的演示:
SQL> CREATE TABLE T_OBJECTS AS
2 SELECT * FROM ALL_OBJECTS WHERE ROWNUM < 1000;
Table created.
SQL> CREATE 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
在真实的环境中应用时, 需要考虑是否真的要这样做, 要看这个节约下来的存贮是否有意义, 因为索引加了一列变大了, 可能会使层次变高, 从而改变了一些现有的SQL的执行成本. 另外的坏处是如果约束被禁用了, 那么可能会有重复记录进入到表, 这是另外需要考虑的问题.
这样做的另一个负页影响可以看如下的例子, 先建一个AFTER INSERT类型的Trigger:
CREATE TRIGGER TRG_T_OBJECTS AFTER INSERT ON T_OBJECTS FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20002,'my error!');
end;
/
接下来测试一下两种不同的索引的情况, 会有如下区别, 在(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
而在(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'
因此如果你的应用会经常插入重复值, 就得考虑这一点了.