本文转载自微信公众号「DBA闲思杂想录」,作者潇湘隐者 。转载本文请联系DBA闲思杂想录公众号。
在ORACLE数据库中,定义外键约束时,ORACLE是不会自动创建对应索引的,必须手动在外键约束相关的列上创建索引。那么外键字段上是否有必要创建索引呢?如果有必要的话,巡检时,如何找出外键字段上没有创建索引的相关表,并生成对应的索引的脚本呢?
外键缺失索引的影响
外键列上缺少索引会带来三个问题,限制并发性、影响性能、还有可能造成死锁。所以对于绝大部分场景,我们应该尽量考虑在外键上面创建索引
- 影响性能。如果子表外键没有创建索引,那么当父表查询关联子表时,子表将进行全表扫描。影响表连接方式。
- 影响并发。无论是更新父表主键,或者删除一个父记录,都会在子表中加一个表锁(在这条语句完成前,不允许对子表做任何修改)。这就会不必要地锁定更多的行,而影响并发性
- 在特殊情况下,还有可能造成死锁。
我们先来看看一个简单的例子,看看当外键缺失索引时,子表是否进行全表扫描,如下所示,表EMP与DEPT存在主外键关系:
- SQL> set autotrace on;
- SQL>
- SQL> SELECT D.DEPTNO, COUNT(*)
- 2 FROM SCOTT.EMP E INNER JOIN SCOTT.DEPT D ON E.DEPTNO =D.DEPTNO
- 3 GROUP BY D.DEPTNO;
- DEPTNO COUNT(*)
- ---------- ----------
- 30 6
- 20 5
- 10 3
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 4067220884
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 3 | 9 | 4 (25)| 00:00:01 |
- | 1 | HASH GROUP BY | | 3 | 9 | 4 (25)| 00:00:01 |
- |* 2 | TABLE ACCESS FULL| EMP | 14 | 42 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter("E"."DEPTNO" IS NOT NULL)
- Statistics
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 7 consistent gets
- 0 physical reads
- 0 redo size
- 665 bytes sent via SQL*Net to client
- 524 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 3 rows processed
如上所示,当外键字段没有索引时,父表与子表关联时,子表会进行全表扫描,下面,我在外键字段创建索引后,就能避免子表表扫描了。
- CREATE INDEX SCOTT.IX_DEPTNO ON SCOTT.EMP ("DEPTNO") TABLESPACE USERS;
当然这两个表的数据量实在是太少了,性能上差别不大,当数据量增长上去后,这个性能差异就会比较明显了。如下例子所示,我们构造一个数据量相对较大的父表与子表的案例:
- create table parent_tb_test
- (
- id number(10),
- name varchar2(32),
- constraint pk_parent_tb_test primary key(id)
- );
- create table child_tb_test
- (
- c_id number(10),
- f_id number(10),
- child_name varchar2(32),
- constraint pk_child_tb_test primary key(c_id),
- foreign key(f_id) references parent_tb_test
- );
- begin
- for index_num in 1 .. 10000 loop
- insert into parent_tb_test
- select index_num , 'kerry' || to_char(index_num) from dual;
- if mod(index_num,100) = 0 then
- commit;
- end if;
- end loop;
- commit;
- end;
- /
- declare index_num number :=1;
- begin
- for index_parent in 1 .. 10000 loop
- for index_child in 1 .. 1000 loop
- insert into child_tb_test
- select index_num, index_parent, 'child' || to_char(index_child) from dual;
- index_num := index_num +1;
- if mod(index_child,1000) = 0 then
- commit;
- end if;
- end loop;
- end loop;
- commit;
- end;
- /
- SQL> execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'PARENT_TB_TEST', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
- PL/SQL procedure successfully completed.
- SQL> execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'CHILD_TB_TEST', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
- PL/SQL procedure successfully completed.
- SQL>
上面脚本构造了测试用的例子和数据, 那么我们对比看看外键有无索引的区别:
- SQL> set linesize 1200
- SQL> set autotrace traceonly
- SQL> select p.id , p.name,c.child_name
- 2 from test.parent_tb_test p
- 3 inner join test.child_tb_test c on p.id = c.f_id
- 4 where p.id=1000;
- 1000 rows selected.
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 901213199
- --------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1009 | 44396 | 4706 (21)| 00:00:07 |
- | 1 | NESTED LOOPS | | 1009 | 44396 | 4706 (21)| 00:00:07 |
- | 2 | TABLE ACCESS BY INDEX ROWID| PARENT_TB_TEST | 1 | 31 | 1 (0)| 00:00:01 |
- |* 3 | INDEX UNIQUE SCAN | PK_PARENT_TB_TEST | 1 | | 1 (0)| 00:00:01 |
- |* 4 | TABLE ACCESS FULL | CHILD_TB_TEST | 1009 | 13117 | 4705 (21)| 00:00:07 |
- --------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - access("P"."ID"=1000)
- 4 - filter("C"."F_ID"=1000)
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 32855 consistent gets
- 32772 physical reads
- 0 redo size
- 29668 bytes sent via SQL*Net to client
- 1218 bytes received via SQL*Net from client
- 68 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1000 rows processed
- SQL>
创建索引后,我们再来看看其执行计划,注意对比创建索引前后,执行计划的差异,如下所示:
- SQL> create index ix_child_tb_test on child_tb_test(f_id);
- SQL> set linesize 1200
- SQL> set autotrace traceonly
- SQL> select p.id , p.name,c.child_name
- 2 from test.parent_tb_test p
- 3 inner join test.child_tb_test c on p.id = c.f_id
- 4 where p.id=1000;
接下来,我们再来看看外键缺失索引影响并发,以及造成死锁的情况,如下所示,创建表dead_lock_parent与dead_lock_foreign,两者存在主外键关系,分布插入两条测试数据:
- SQL> create table dead_lock_parent( id number primary key, name varchar2(32));
- Table created.
- SQL> create table dead_lock_foreign(fid number, fname varchar2(32), foreign key(fid) references dead_lock_parent);
- Table created.
- SQL> insert into dead_lock_parent values( 1, 'kerry');
- 1 row created.
- SQL> insert into dead_lock_foreign values(1, 'kerry_fk');
- 1 row created.
- SQL> insert into dead_lock_parent values(2, 'jimmy');
- 1 row created.
- SQL> insert into dead_lock_foreign values(2, 'jimmy_fk');
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL>
1:在会话1(会话ID为789)里面执行下面SQL语句:
- SQL> show user;
- USER 为 "TEST"
- SQL> select * from v$mystat where rownum=1;
- SID STATISTIC# VALUE
- ---------- ---------- ----------
- 789 0 1
- SQL> delete from dead_lock_foreign where fid=1;
- 已删除 1 行。
2:在会话2(会话ID为766)里面执行下面SQL语句:
- SQL> show user;
- USER is "TEST"
- SQL> select * from v$mystat where rownum=1;
- SID STATISTIC# VALUE
- ---------- ---------- ----------
- 766 0 1
- SQL> delete from dead_lock_foreign where fid=2;
- 1 row deleted.
3:接着在会话1(会话ID为789)里执行删除dead_lock_parent中id为1的记录:
- SQL> delete from dead_lock_parent where id=1;
此时你会发现会话被阻塞了,我们可以用下面SQL查询具体的阻塞信息。
- COL MODE_HELD FOR A14;
- COL LOCK_TYPE FOR A8;
- COL MODE_REQUESTED FOR A10;
- COL OBJECT_TYPE FOR A14;
- COL OBJECT_NAME FOR A20;
- SELECT LK.SID,
- DECODE(LK.TYPE,
- 'TX',
- 'Transaction',
- 'TM',
- 'DML',
- 'UL',
- 'PL/SQL User Lock',
- LK.TYPE) LOCK_TYPE,
- DECODE(LK.LMODE,
- 0,
- 'None',
- 1,
- 'Null',
- 2,
- 'Row-S (SS)',
- 3,
- 'Row-X (SX)',
- 4,
- 'Share',
- 5,
- 'S/Row-X (SSX)',
- 6,
- 'Exclusive',
- TO_CHAR(LK.LMODE)) MODE_HELD,
- DECODE(LK.REQUEST,
- 0,
- 'None',
- 1,
- 'Null',
- 2,
- 'Row-S (SS)',
- 3,
- 'Row-X (SX)',
- 4,
- 'Share',
- 5,
- 'S/Row-X (SSX)',
- 6,
- 'Exclusive',
- TO_CHAR(LK.REQUEST)) MODE_REQUESTED,
- OB.OBJECT_TYPE,
- OB.OBJECT_NAME,
- LK.BLOCK,
- SE.LOCKWAIT
- FROM V$LOCK LK, DBA_OBJECTS OB, V$SESSION SE
- WHERE LK.TYPE IN ('TM', 'UL')
- AND LK.SID = SE.SID
- AND LK.ID1 = OB.OBJECT_ID(+)
- AND SE.SID IN (766,789)
- ORDER BY SID;
上面信息如果不能让你理解,那么可以看看下面脚本,相信你能看得更详细。
- SQL> SELECT S.SID SID,
- S.USERNAME USERNAME,
- S.MACHINE MACHINE,
- L.TYPE TYPE,
- O.OBJECT_NAME OBJECT_NAME,
- DECODE(L.LMODE, 0, 'None',
- 1, 'Null',
- 2, 'Row Share',
- 3, 'Row Exlusive',
- 4, 'Share',
- 5, 'Sh/Row Exlusive',
- 6, 'Exclusive') lmode,
- DECODE(L.REQUEST, 0, 'None',
- 1, 'Null',
- 2, 'Row Share',
- 3, 'Row Exlusive',
- 4, 'Share',
- 5, 'Sh/Row Exlusive',
- 6, 'Exclusive') request,
- L.BLOCK BLOCK
- FROM V$LOCK L,
- V$SESSION S,
- DBA_OBJECTS O
- WHERE L.SID = S.SID
- AND USERNAME != 'SYSTEM'
- AND O.OBJECT_ID(+) = L.ID1
- AND S.SID IN ( 766,789)
- ORDER BY S.SID;
- SID USERNAME MACHINE TY OBJECT_NAME LMODE REQUEST BLOCK
- ---------- -------- -------------- -- -------------------- --------------- --------------- -----
- 766 TEST XXXX\GET253194 TX Exclusive None 0
- 766 TEST XXXX\GET253194 TM DEAD_LOCK_FOREIGN Row Exlusive None 1
- 766 TEST XXXX\GET253194 TM DEAD_LOCK_PARENT Row Exlusive None 0
- 789 TEST DB-Server.loca TX Exclusive None 0
- ldomain
- 789 TEST DB-Server.loca TM DEAD_LOCK_PARENT Row Exlusive None 0
- ldomain
- 789 TEST DB-Server.loca TM DEAD_LOCK_FOREIGN Row Exlusive Sh/Row Exlusive 0
- ldomain
接着在会话2里面执行下面SQL,删除主表中id=2的记录
- SQL> delete from dead_lock_parent where id=2;
你会发现会话1就会出现Deadlock
如果你在外键字段上创建索引,那么这种情况下的操作就不会出现死锁。在这里就不再赘述。有兴趣可以测试一下.
外键创建索引建议(Foreign Key Indexing Tips)
虽然增加索引,可能会带来一些额外的性能开销(DML操作开销增加)和磁盘空间方面的开销,但是相比其带来的性能改善而言,这些额外的开销其实完全可以忽略。如果没有其他特殊情况,建议所有的外键字段都加上索引。在Oracle Oracle Database 9i/10g/11g编程艺术这本书中介绍了在什么时候不需要对外键加索引. 必须满足下面三个条件:
- 不会删除父表中的行。
- 不论是有意还是无意,总之不会更新父表的唯一/主键字段值。
- 不会从父表联结到子表, 或者更通俗的讲,外键列不支持子表的一个重要访问路径,而且你在谓词中没有使用这些外键累从子表中选择数据。
找出未索引的外键
我们首先可以通过下面脚本,找到整个数据库中那些表有主外键关系,并列出主外键约束.
--查看整个数据库下拥有主外键关系的所有表(排除一些系统用户)
- --查看整个数据库下拥有主外键关系的所有表(排除一些系统用户)
- SELECT DC.OWNER AS "PARENT_TABLE_OWNER",
- DC.TABLE_NAME AS "PARENT_TABLE_NAME",
- DC.CONSTRAINT_NAME AS "PRIMARY CONSTRAINT NAME",
- DF.CONSTRAINT_NAME AS "REFERENCED CONSTRAINT NAME",
- DF.OWNER AS "CHILD_TABLE_OWNER",
- DF.TABLE_NAME AS "CHILD_TABLE_NAME"
- FROM DBA_CONSTRAINTS DC,
- (SELECT C.OWNER,
- C.CONSTRAINT_NAME,
- C.R_CONSTRAINT_NAME,
- C.TABLE_NAME
- FROM DBA_CONSTRAINTS C
- WHERE CONSTRAINT_TYPE = 'R') DF
- WHERE DC.CONSTRAINT_NAME =DF.R_CONSTRAINT_NAME
- AND DC.OWNER NOT IN ( 'SYSTEM', 'SYS', 'DBSNMP', 'EXFSYS',
- 'ORDDATA', 'CTXSYS', 'OLAPSYS', 'MDSYS',
- 'SYSMAN' );
--查看某个Schema下拥有主外键关系的所有表
- --查看某个Schema下拥有主外键关系的所有表
- SELECT DC.OWNER AS "PARENT_TABLE_OWNER",
- DC.TABLE_NAME AS "PARENT_TABLE_NAME",
- DC.CONSTRAINT_NAME AS "PRIMARY CONSTRAINT NAME",
- DF.CONSTRAINT_NAME AS "REFERENCED CONSTRAINT NAME",
- DF.OWNER AS "CHILD_TABLE_OWNER",
- DF.TABLE_NAME AS "CHILD_TABLE_NAME"
- FROM DBA_CONSTRAINTS DC,
- (SELECT C.OWNER,
- C.CONSTRAINT_NAME,
- C.R_CONSTRAINT_NAME,
- C.TABLE_NAME
- FROM DBA_CONSTRAINTS C
- WHERE CONSTRAINT_TYPE = 'R') DF
- WHERE DC.CONSTRAINT_NAME = DF.R_CONSTRAINT_NAME
- AND DC.OWNER =UPPER('&OWNER');
--查看某个具体的表是否和其它表拥有主外键关系
- --查看某个具体的表是否和其它表拥有主外键关系
- SELECT DC.OWNER AS "PARENT_TABLE_OWNER",
- DC.TABLE_NAME AS "PARENT_TABLE_NAME",
- DC.CONSTRAINT_NAME AS "PRIMARY CONSTRAINT NAME",
- DF.CONSTRAINT_NAME AS "REFERENCED CONSTRAINT NAME",
- DF.OWNER AS "CHILD_TABLE_OWNER",
- DF.TABLE_NAME AS "CHILD_TABLE_NAME"
- FROM DBA_CONSTRAINTS DC,
- (SELECT C.OWNER,
- C.CONSTRAINT_NAME,
- C.R_CONSTRAINT_NAME,
- C.TABLE_NAME
- FROM DBA_CONSTRAINTS C
- WHERE CONSTRAINT_TYPE = 'R') DF
- WHERE DC.CONSTRAINT_NAME = DF.R_CONSTRAINT_NAME
- AND DC.OWNER =UPPER('&OWNER')
- AND DC.TABLE_NAME=UPPER('&TABLE_NAME');
接下来我们要找出在具体的外键字段是否有索引,脚本如下所示:
- SELECT CON.OWNER ,
- CON.TABLE_NAME,
- CON.CONSTRAINT_NAME,
- CON.COL_LIST,
- 'No Indexed' AS INDEX_STATUS
- FROM
- (SELECT CC.OWNER, CC.TABLE_NAME, CC.CONSTRAINT_NAME,
- MAX(DECODE(POSITION, 1, '"' ||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(POSITION, 2,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(POSITION, 3,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(POSITION, 4,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(POSITION, 5,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(POSITION, 6,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(POSITION, 7,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(POSITION, 8,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(POSITION, 9,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(POSITION, 10,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) COL_LIST
- FROM DBA_CONSTRAINTS DC, DBA_CONS_COLUMNS CC
- WHERE DC.OWNER = CC.OWNER
- AND DC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
- AND DC.CONSTRAINT_TYPE = 'R'
- AND DC.OWNER NOT IN ('SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN', 'MDSYS', 'ADMIN')
- GROUP BY CC.OWNER, CC.TABLE_NAME, CC.CONSTRAINT_NAME
- ) CON
- WHERE NOT EXISTS (
- SELECT 1 FROM
- ( SELECT TABLE_OWNER, TABLE_NAME,
- MAX(DECODE(COLUMN_POSITION, 1, '"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(COLUMN_POSITION, 2,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(COLUMN_POSITION, 3,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(COLUMN_POSITION, 4,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(COLUMN_POSITION, 5,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(COLUMN_POSITION, 6,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(COLUMN_POSITION, 7,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(COLUMN_POSITION, 8,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(COLUMN_POSITION, 9,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(COLUMN_POSITION, 10,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) COL_LIST
- FROM DBA_IND_COLUMNS
- WHERE TABLE_OWNER NOT IN ('SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN', 'MDSYS')
- GROUP BY TABLE_OWNER, TABLE_NAME, INDEX_NAME ) COL
- WHERE CON.OWNER = COL.TABLE_OWNER
- AND CON.TABLE_NAME = COL.TABLE_NAME
- AND CON.COL_LIST = SUBSTR(COL.COL_LIST, 1, LENGTH(CON.COL_LIST) ) ) ;
如果是ORACLE 11g或以上版本,数据库有分析函数LISTAGG的话,可以使用下面脚本
- SELECT CASE
- WHEN B.TABLE_NAME IS NULL THEN 'NO INDEXED'
- ELSE 'INDEXED'
- END AS STATUS,
- A.TABLE_OWNER AS TABLE_OWNER,
- A.TABLE_NAME AS TABLE_NAME,
- A.CONSTRAINT_NAME AS FK_NAME,
- A.FK_COLUMNS AS FK_COLUMNS,
- B.INDEX_NAME AS INDEX_NAME,
- B.INDEX_COLUMNS AS INDEX_COLUMNS
- FROM (SELECT A.OWNER AS TABLE_OWNER,
- A.TABLE_NAME AS TABLE_NAME,
- A.CONSTRAINT_NAME AS CONSTRAINT_NAME,
- LISTAGG(A.COLUMN_NAME, ',')
- WITHIN GROUP (ORDER BY A.POSITION) FK_COLUMNS
- FROM DBA_CONS_COLUMNS A,
- DBA_CONSTRAINTS B
- WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
- AND B.CONSTRAINT_TYPE = 'R'
- AND A.OWNER = B.OWNER
- AND A.OWNER NOT IN ( 'SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN',
- 'MDSYS' )
- GROUP BY A.OWNER,
- A.TABLE_NAME,
- A.CONSTRAINT_NAME) A,
- (SELECT TABLE_OWNER,
- TABLE_NAME,
- INDEX_NAME,
- LISTAGG(C.COLUMN_NAME, ',')
- WITHIN GROUP (ORDER BY C.COLUMN_POSITION) INDEX_COLUMNS
- FROM DBA_IND_COLUMNS C
- GROUP BY TABLE_OWNER,
- TABLE_NAME,
- INDEX_NAME) B
- WHERE A.TABLE_NAME = B.TABLE_NAME(+)
- AND A.TABLE_OWNER = B.TABLE_OWNER(+)
- AND B.INDEX_COLUMNS(+) LIKE A.FK_COLUMNS
- || '%'
- ORDER BY 1 DESC
自动生成创建外键索引的脚本
上面的这些脚本已经能找出那些外键字段已经建立或未建立索引,此时如果对外键字段缺少索引的表手工创建索引的话,如果数量很多的话,那么工作量也非常大,下面可以用这个脚本自动生成缺失的索引
- /*******************************************************************************************
- --脚本功能描述:
- -- 对于数据库中外键缺少索引的字段,生成对应的索引(排除一些系统账号,例如sys、system),如果外键索引超过十个字段
- -- 那么这个脚本就不能正确的生成对应的索引,当然也很少有外键设置在超过10个字段的。另外索引表空
- -- 空间跟数据表空间相同,如有分开的话,建议在此处再做调整。
- ********************************************************************************************/
- SELECT 'CREATE INDEX ' || OWNER || '.' || REPLACE(CONSTRAINT_NAME,'FK_','IX_') ||
- ' ON ' || OWNER || '.' || TABLE_NAME || ' (' || COL_LIST ||') TABLESPACE '
- || (SELECT TABLESPACE_NAME FROM DBA_TABLES WHERE OWNER= CON.OWNER AND TABLE_NAME= CON.TABLE_NAME)
- AS CREATE_INDEXES_ON_FOREIGN_KEY
- FROM
- (SELECT CC.OWNER, CC.TABLE_NAME, CC.CONSTRAINT_NAME,
- MAX(DECODE(POSITION, 1, '"' ||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(POSITION, 2,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(POSITION, 3,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(POSITION, 4,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(POSITION, 5,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(POSITION, 6,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(POSITION, 7,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(POSITION, 8,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(POSITION, 9,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(POSITION, 10,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) COL_LIST
- FROM DBA_CONSTRAINTS DC, DBA_CONS_COLUMNS CC
- WHERE DC.OWNER = CC.OWNER
- AND DC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
- AND DC.CONSTRAINT_TYPE = 'R'
- AND DC.OWNER NOT IN ('SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN', 'MDSYS', 'ADMIN')
- GROUP BY CC.OWNER, CC.TABLE_NAME, CC.CONSTRAINT_NAME
- ) CON
- WHERE NOT EXISTS (
- SELECT 1 FROM
- ( SELECT TABLE_OWNER, TABLE_NAME,
- MAX(DECODE(COLUMN_POSITION, 1, '"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(COLUMN_POSITION, 2,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(COLUMN_POSITION, 3,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(COLUMN_POSITION, 4,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(COLUMN_POSITION, 5,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(COLUMN_POSITION, 6,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(COLUMN_POSITION, 7,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(COLUMN_POSITION, 8,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(COLUMN_POSITION, 9,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
- MAX(DECODE(COLUMN_POSITION, 10,', '||'"'||
- SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) COL_LIST
- FROM DBA_IND_COLUMNS
- WHERE TABLE_OWNER NOT IN ('SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN', 'MDSYS')
- GROUP BY TABLE_OWNER, TABLE_NAME, INDEX_NAME ) COL
- WHERE CON.OWNER = COL.TABLE_OWNER
- AND CON.TABLE_NAME = COL.TABLE_NAME
- AND CON.COL_LIST = SUBSTR(COL.COL_LIST, 1, LENGTH(CON.COL_LIST) ) ) ;
--脚本使用分析函数LISTAGG, 适用于ORACLE 11g以及以上版本,如果数据库版本是Oracle 11g及以上,就可以使用此脚本替代上面脚本。
- SELECT 'CREATE INDEX '
- || OWNER
- || '.'
- || REPLACE(CONSTRAINT_NAME,'FK_','IX_')
- || ' ON '
- || OWNER
- || '.'
- || TABLE_NAME
- || ' ('
- || FK_COLUMNS
- ||') TABLESPACE '
- ||
- (
- SELECT TABLESPACE_NAME
- FROM DBA_TABLES
- WHERE OWNER= CON.OWNER
- AND TABLE_NAME= CON.TABLE_NAME) CREATE_INDEXES_ON_FOREIGN_KEY
- FROM (
- SELECT CC.OWNER,
- CC.TABLE_NAME,
- CC.CONSTRAINT_NAME,
- LISTAGG(CC.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY CC.POSITION) FK_COLUMNS
- FROM DBA_CONS_COLUMNS CC,
- DBA_CONSTRAINTS DC
- WHERE CC.CONSTRAINT_NAME = DC.CONSTRAINT_NAME
- AND DC.CONSTRAINT_TYPE = 'R'
- AND CC.OWNER = DC.OWNER
- AND DC.OWNER NOT IN ( 'SYS',
- 'SYSTEM',
- 'OLAPSYS',
- 'SYSMAN',
- 'MDSYS',
- 'ADMIN' )
- GROUP BY CC.OWNER,
- CC.TABLE_NAME,
- CC.CONSTRAINT_NAME) CON
- WHERE NOT EXISTS
- (
- SELECT 1
- FROM (
- SELECT TABLE_OWNER,
- TABLE_NAME,
- INDEX_NAME,
- LISTAGG(COLUMN_NAME, ',') WITHIN GROUP (ORDER BY COLUMN_POSITION) FK_COLUMNS
- FROM DBA_IND_COLUMNS
- WHERE INDEX_OWNER NOT IN ( 'SYS',
- 'SYSTEM',
- 'OLAPSYS',
- 'SYSMAN',
- 'MDSYS',
- 'ADMIN' )
- GROUP BY TABLE_OWNER,
- TABLE_NAME ,INDEX_NAME) COL
- WHERE CON.OWNER = COL.TABLE_OWNER
- AND CON.TABLE_NAME = COL.TABLE_NAME
- AND CON.FK_COLUMNS = SUBSTR(COL.FK_COLUMNS, 1, LENGTH(CON.FK_COLUMNS)) )
- ORDER BY 1;
参考资料:
http://www.dba-oracle.com/t_foreign_key_indexing.htm