关于Oracle外键列上是否需要索引问题


关于Oracle外键列上是否需要索引问题
 
外键列上缺少索引会带来两个问题，限制并发性、影响性能。而这两个问题中的任意一个都可能会造成严重性能问题。
 
无论是Oracle的官方文档，还是在Tom的书中都说明了两种情况下可以忽略外键上的索引。其实我认为不需要那么麻烦，与增加一个
 
索引所带来的性能开销和磁盘空间开销相比，缺失索引可能引发的问题要严重得多。因此，我会选择在所有的外键列上添加索引，虽然
 
可能导致创建了部分多余的索引，但是这样相除了外键约束由于确实索引所带来的性能问题和并发性问题。
 
如果外键列上缺少索引，从主表关联子表的查询就只能对子表选择全表扫描的查询，这是显而易见的问题：
 
SQL> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));
表已创建。
 
SQL> ALTER TABLE T_P ADD PRIMARY KEY (ID);
表已更改。
 
SQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));
表已创建。
 
SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID);
表已更改。
 
SQL> INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;
已创建884行。
 
SQL> INSERT INTO T_C SELECT ROWNUM, MOD(ROWNUM, 884) + 1, OBJECT_NAME FROM ALL_OBJECTS;
已创建30339行。
 
SQL> COMMIT;
提交完成。
 
SQL> SELECT A.ID, A.NAME, B.NAME
2 FROM T_P A, T_C B
3 WHERE A.ID = B.FID
4 AND A.ID = 880;
 
ID         NAME                           NAME
---------- ------------------------------ ------------------------------
880        T_COMPRESS                     /eb2b6b5_Options1
880        T_COMPRESS                     DATE
880        T_COMPRESS                     DEF$_SCHEDULE
880        T_COMPRESS                     GV_$SESSION_EVENT
...
 
已选择34行。
 
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_P'
3 2 INDEX (UNIQUE SCAN) OF 'SYS_C002964' (UNIQUE)
4 1 FILTER
5 4 TABLE ACCESS (FULL) OF 'T_C'
 
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
190 consistent gets
0 physical reads
0 redo size
1829 bytes sent via SQL*Net to client
394 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
34 rows processed
 
由于缺少索引，上面的这个关联查询只能采用MERGE JOIN，而如果联立了外键列上的索引：
 
SQL> CREATE INDEX IND_T_C_FID ON T_C (FID);
索引已创建。
 
SQL> SELECT A.ID, A.NAME, B.NAME
2 FROM T_P A, T_C B
3 WHERE A.ID = B.FID
4 AND A.ID = 880;
 
ID         NAME                           NAME
---------- ------------------------------ ------------------------------
880        T_COMPRESS                     /e1538703_EntryInfoImpl
880        T_COMPRESS                     /7b832daf_ObjectStreamClassCom
880        T_COMPRESS                     java/awt/peer/ScrollbarPeer
880        T_COMPRESS                     /1982bd95_PermissionsEnumerato
...
 
已选择34行。
 
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_P'
3 2 INDEX (UNIQUE SCAN) OF 'SYS_C002964' (UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_C'
5 4 INDEX (RANGE SCAN) OF 'IND_T_C_FID' (NON-UNIQUE)
 
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
42 consistent gets
1 physical reads
0 redo size
1829 bytes sent via SQL*Net to client
394 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
34 rows processed
 
上面是影响性能的例子，下面看看外键列索引对并发性的影响：
 
SQL> SET AUTOT OFF
SQL> SELECT * FROM T_P WHERE ID < 5;
ID         NAME
---------- ------------------------------
1          SEG$
2          CLU$
3          OBJ$
4          FILE$
 
SQL> SELECT * FROM T_C WHERE ID < 5;
ID         FID        NAME
---------- ---------- ------------------------------
1          2          /1005bd30_LnkdConstant
2          3          /10076b23_OraCustomDatumClosur
3          4          /10297c91_SAXAttrList
4          5          /103a2e73_DefaultEditorKitEndP
 
下面在另一个会话中删除子表的一条记录：
 
SQL> SET SQLP 'SQL2> '
SQL2> DELETE T_C WHERE ID = 2;
已删除 1 行。
 
删除了一条为2的子表级联，其对应的主表记录ID为3，下面尝试在第一个会话新增一条ID为1000的记录，然后删除这条记录：
 
SQL> INSERT INTO T_P VALUES (1000, 'A');
已创建 1 行。
 
SQL> DELETE T_P WHERE ID = 1000;
已删除 1 行。
 
SQL> ROLLBACK;
回退已完成。
 
可以看到，并没有发生锁表的情况，这是因为子表外键列上有索引，删除主表的记录时，只会锁定子表参考主表的对应记录。
 
会话二回滚：
 
SQL2> ROLLBACK;
回退已完成。
 
下面删除外键索引：
SQL> DROP INDEX IND_T_C_FID;
索引已删除。
 
重复刚才的操作，在另一个会话执行删除操作：
SQL2> DELETE T_C WHERE ID = 2;
已删除 1 行。
 
在会话一重复插入和删除操作：
SQL> INSERT INTO T_P VALUES (1000, 'A');
已创建