日期：2011-01-25 22:55:00 来源：本站整理

Oracle外键列上能否需求索引?-性能调优[Oracle防范]

赞助商链接

分享到： QQ空间新浪微博腾讯微博人人网

　　本文“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
2 FOREIGN KEY (FID)
3 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
2 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
.
.
.
880 T_COMPRESS sun/io/ByteToCharCp1251
880 T_COMPRESS /5ba3839f_DirStateFactoryResul
880 T_COMPRESS USER_INDEXTYPES

已挑选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
.
.
.
880 T_COMPRESS /9ebda46b_GetInterface
880 T_COMPRESS /c71f85e7_DefaultPopupFactory
880 T_COMPRESS /7b549d81_DataFormatException

已挑选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