Oracle中关于外键缺少索引的原因是什么

2025-03-16 技术教程

本篇内容介绍了“Oracle中关于外键缺少索引的原因是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

在ORACLE数据库中，定义外键约束时，ORACLE是不会自动创建对应索引的，必须手动在外键约束相关的列上创建索引。那么外键字段上是否有必要创建索引呢?如果有必要的话，巡检时，如何找出外键字段上没有创建索引的相关表，并生成对应的索引的脚本呢?

外键缺失索引的影响

外键列上缺少索引会带来三个问题，限制并发性、影响性能、还有可能造成死锁。所以对于绝大部分场景，我们应该尽量考虑在外键上面创建索引

鸿蒙官方战略合作共建——HarmonyOS技术社区

影响性能。如果子表外键没有创建索引，那么当父表查询关联子表时，子表将进行全表扫描。影响表连接方式。

影响并发。无论是更新父表主键，或者删除一个父记录，都会在子表中加一个表锁(在这条语句完成前，不允许对子表做任何修改)。这就会不必要地锁定更多的行，而影响并发性

在特殊情况下，还有可能造成死锁。

我们先来看看一个简单的例子，看看当外键缺失索引时，子表是否进行全表扫描，如下所示，表EMP与DEPT存在主外键关系：

SQL>setautotraceon;SQL>SQL>SELECTD.DEPTNO,COUNT(*)2FROMSCOTT.EMPEINNERJOINSCOTT.DEPTDONE.DEPTNO=D.DEPTNO3GROUPBYD.DEPTNO;DEPTNOCOUNT(*)--------------------306205103ExecutionPlan----------------------------------------------------------Planhashvalue:4067220884---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||3|9|4(25)|00:00:01||1|HASHGROUPBY||3|9|4(25)|00:00:01||*2|TABLEACCESSFULL|EMP|14|42|3(0)|00:00:01|---------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-filter("E"."DEPTNO"ISNOTNULL)Statistics----------------------------------------------------------1recursivecalls0dbblockgets7consistentgets0physicalreads0redosize665bytessentviaSQL*Nettoclient524bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)3rowsprocessed

如上所示，当外键字段没有索引时，父表与子表关联时，子表会进行全表扫描，下面，我在外键字段创建索引后，就能避免子表表扫描了。

CREATEINDEXSCOTT.IX_DEPTNOONSCOTT.EMP("DEPTNO")TABLESPACEUSERS;

当然这两个表的数据量实在是太少了，性能上差别不大，当数据量增长上去后，这个性能差异就会比较明显了。如下例子所示，我们构造一个数据量相对较大的父表与子表的案例：

createtableparent_tb_test(idnumber(10),namevarchar2(32),constraintpk_parent_tb_testprimarykey(id));createtablechild_tb_test(c_idnumber(10),f_idnumber(10),child_namevarchar2(32),constraintpk_child_tb_testprimarykey(c_id),foreignkey(f_id)referencesparent_tb_test);beginforindex_numin1..10000loopinsertintoparent_tb_testselectindex_num,'kerry'||to_char(index_num)fromdual;ifmod(index_num,100)=0thencommit;endif;endloop;commit;end;/declareindex_numnumber:=1;beginforindex_parentin1..10000loopforindex_childin1..1000loopinsertintochild_tb_testselectindex_num,index_parent,'child'||to_char(index_child)fromdual;index_num:=index_num+1;ifmod(index_child,1000)=0thencommit;endif;endloop;endloop;commit;end;/SQL>executedbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'PARENT_TB_TEST',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FORALLCOLUMNSSIZEAUTO');PL/SQLproceduresuccessfullycompleted.SQL>executedbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'CHILD_TB_TEST',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FORALLCOLUMNSSIZEAUTO');PL/SQLproceduresuccessfullycompleted.SQL>

上面脚本构造了测试用的例子和数据，那么我们对比看看外键有无索引的区别：

SQL>setlinesize1200SQL>setautotracetraceonlySQL>selectp.id,p.name,c.child_name2fromtest.parent_tb_testp3innerjointest.child_tb_testconp.id=c.f_id4wherep.id=1000;1000rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:901213199--------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1009|44396|4706(21)|00:00:07||1|NESTEDLOOPS||1009|44396|4706(21)|00:00:07||2|TABLEACCESSBYINDEXROWID|PARENT_TB_TEST|1|31|1(0)|00:00:01||*3|INDEXUNIQUESCAN|PK_PARENT_TB_TEST|1||1(0)|00:00:01||*4|TABLEACCESSFULL|CHILD_TB_TEST|1009|13117|4705(21)|00:00:07|--------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------3-access("P"."ID"=1000)4-filter("C"."F_ID"=1000)Statistics----------------------------------------------------------0recursivecalls0dbblockgets32855consistentgets32772physicalreads0redosize29668bytessentviaSQL*Nettoclient1218bytesreceivedviaSQL*Netfromclient68SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1000rowsprocessedSQL>

创建索引后，我们再来看看其执行计划，注意对比创建索引前后，执行计划的差异，如下所示：

SQL>createindexix_child_tb_testonchild_tb_test(f_id);SQL>setlinesize1200SQL>setautotracetraceonlySQL>selectp.id,p.name,c.child_name2fromtest.parent_tb_testp3innerjointest.child_tb_testconp.id=c.f_id4wherep.id=1000;

接下来，我们再来看看外键缺失索引影响并发，以及造成死锁的情况，如下所示，创建表dead_lock_parent与dead_lock_foreign，两者存在主外键关系，分布插入两条测试数据：

SQL>createtabledead_lock_parent(idnumberprimarykey,namevarchar2(32));Tablecreated.SQL>createtabledead_lock_foreign(fidnumber,fnamevarchar2(32),foreignkey(fid)referencesdead_lock_parent);Tablecreated.SQL>insertintodead_lock_parentvalues(1,'kerry');1rowcreated.SQL>insertintodead_lock_foreignvalues(1,'kerry_fk');1rowcreated.SQL>insertintodead_lock_parentvalues(2,'jimmy');1rowcreated.SQL>insertintodead_lock_foreignvalues(2,'jimmy_fk');1rowcreated.SQL>commit;Commitcomplete.SQL>

1：在会话1(会话ID为789)里面执行下面SQL语句：

SQL>showuser;USER为"TEST"SQL>select*fromv$mystatwhererownum=1;SIDSTATISTIC#VALUE------------------------------78901SQL>deletefromdead_lock_foreignwherefid=1;已删除1行。

2：在会话2(会话ID为766)里面执行下面SQL语句：

SQL>showuser;USERis"TEST"SQL>select*fromv$mystatwhererownum=1;SIDSTATISTIC#VALUE------------------------------76601SQL>deletefromdead_lock_foreignwherefid=2;1rowdeleted.

3：接着在会话1(会话ID为789)里执行删除dead_lock_parent中id为1的记录：

SQL>deletefromdead_lock_parentwhereid=1;

此时你会发现会话被阻塞了，我们可以用下面SQL查询具体的阻塞信息。

COLMODE_HELDFORA14;COLLOCK_TYPEFORA8;COLMODE_REQUESTEDFORA10;COLOBJECT_TYPEFORA14;COLOBJECT_NAMEFORA20;SELECTLK.SID,DECODE(LK.TYPE,'TX','Transaction','TM','DML','UL','PL/SQLUserLock',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.LOCKWAITFROMV$LOCKLK,DBA_OBJECTSOB,V$SESSIONSEWHERELK.TYPEIN('TM','UL')ANDLK.SID=SE.SIDANDLK.ID1=OB.OBJECT_ID(+)ANDSE.SIDIN(766,789)ORDERBYSID;

上面信息如果不能让你理解，那么可以看看下面脚本，相信你能看得更详细。

SQL>SELECTS.SIDSID,S.USERNAMEUSERNAME,S.MACHINEMACHINE,L.TYPETYPE,O.OBJECT_NAMEOBJECT_NAME,DECODE(L.LMODE,0,'None',1,'Null',2,'RowShare',3,'RowExlusive',4,'Share',5,'Sh/RowExlusive',6,'Exclusive')lmode,DECODE(L.REQUEST,0,'None',1,'Null',2,'RowShare',3,'RowExlusive',4,'Share',5,'Sh/RowExlusive',6,'Exclusive')request,L.BLOCKBLOCKFROMV$LOCKL,V$SESSIONS,DBA_OBJECTSOWHEREL.SID=S.SIDANDUSERNAME!='SYSTEM'ANDO.OBJECT_ID(+)=L.ID1ANDS.SIDIN(766,789)ORDERBYS.SID;SIDUSERNAMEMACHINETYOBJECT_NAMELMODEREQUESTBLOCK-----------------------------------------------------------------------------------------766TESTXXXX\GET253194TXExclusiveNone0766TESTXXXX\GET253194TMDEAD_LOCK_FOREIGNRowExlusiveNone1766TESTXXXX\GET253194TMDEAD_LOCK_PARENTRowExlusiveNone0789TESTDB-Server.locaTXExclusiveNone0ldomain789TESTDB-Server.locaTMDEAD_LOCK_PARENTRowExlusiveNone0ldomain789TESTDB-Server.locaTMDEAD_LOCK_FOREIGNRowExlusiveSh/RowExlusive0ldomain

接着在会话2里面执行下面SQL，删除主表中id=2的记录

SQL>deletefromdead_lock_parentwhereid=2;

你会发现会话1就会出现Deadlock

如果你在外键字段上创建索引，那么这种情况下的操作就不会出现死锁。在这里就不再赘述。有兴趣可以测试一下.

外键创建索引建议(Foreign Key Indexing Tips)

虽然增加索引，可能会带来一些额外的性能开销(DML操作开销增加)和磁盘空间方面的开销，但是相比其带来的性能改善而言，这些额外的开销其实完全可以忽略。如果没有其他特殊情况，建议所有的外键字段都加上索引。在Oracle Oracle Database 9i/10g/11g编程艺术这本书中介绍了在什么时候不需要对外键加索引. 必须满足下面三个条件：

鸿蒙官方战略合作共建——HarmonyOS技术社区

不会删除父表中的行。

不论是有意还是无意，总之不会更新父表的唯一/主键字段值。

不会从父表联结到子表，或者更通俗的讲，外键列不支持子表的一个重要访问路径，而且你在谓词中没有使用这些外键累从子表中选择数据。

找出未索引的外键

我们首先可以通过下面脚本，找到整个数据库中那些表有主外键关系，并列出主外键约束.

--查看整个数据库下拥有主外键关系的所有表(排除一些系统用户)

--查看整个数据库下拥有主外键关系的所有表（排除一些系统用户）SELECTDC.OWNERAS"PARENT_TABLE_OWNER",DC.TABLE_NAMEAS"PARENT_TABLE_NAME",DC.CONSTRAINT_NAMEAS"PRIMARYCONSTRAINTNAME",DF.CONSTRAINT_NAMEAS"REFERENCEDCONSTRAINTNAME",DF.OWNERAS"CHILD_TABLE_OWNER",DF.TABLE_NAMEAS"CHILD_TABLE_NAME"FROMDBA_CONSTRAINTSDC,(SELECTC.OWNER,C.CONSTRAINT_NAME,C.R_CONSTRAINT_NAME,C.TABLE_NAMEFROMDBA_CONSTRAINTSCWHERECONSTRAINT_TYPE='R')DFWHEREDC.CONSTRAINT_NAME=DF.R_CONSTRAINT_NAMEANDDC.OWNERNOTIN('SYSTEM','SYS','DBSNMP','EXFSYS','ORDDATA','CTXSYS','OLAPSYS','MDSYS','SYSMAN');

--查看某个Schema下拥有主外键关系的所有表

--查看某个Schema下拥有主外键关系的所有表SELECTDC.OWNERAS"PARENT_TABLE_OWNER",DC.TABLE_NAMEAS"PARENT_TABLE_NAME",DC.CONSTRAINT_NAMEAS"PRIMARYCONSTRAINTNAME",DF.CONSTRAINT_NAMEAS"REFERENCEDCONSTRAINTNAME",DF.OWNERAS"CHILD_TABLE_OWNER",DF.TABLE_NAMEAS"CHILD_TABLE_NAME"FROMDBA_CONSTRAINTSDC,(SELECTC.OWNER,C.CONSTRAINT_NAME,C.R_CONSTRAINT_NAME,C.TABLE_NAMEFROMDBA_CONSTRAINTSCWHERECONSTRAINT_TYPE='R')DFWHEREDC.CONSTRAINT_NAME=DF.R_CONSTRAINT_NAMEANDDC.OWNER=UPPER('&OWNER');

--查看某个具体的表是否和其它表拥有主外键关系

--查看某个具体的表是否和其它表拥有主外键关系SELECTDC.OWNERAS"PARENT_TABLE_OWNER",DC.TABLE_NAMEAS"PARENT_TABLE_NAME",DC.CONSTRAINT_NAMEAS"PRIMARYCONSTRAINTNAME",DF.CONSTRAINT_NAMEAS"REFERENCEDCONSTRAINTNAME",DF.OWNERAS"CHILD_TABLE_OWNER",DF.TABLE_NAMEAS"CHILD_TABLE_NAME"FROMDBA_CONSTRAINTSDC,(SELECTC.OWNER,C.CONSTRAINT_NAME,C.R_CONSTRAINT_NAME,C.TABLE_NAMEFROMDBA_CONSTRAINTSCWHERECONSTRAINT_TYPE='R')DFWHEREDC.CONSTRAINT_NAME=DF.R_CONSTRAINT_NAMEANDDC.OWNER=UPPER('&OWNER')ANDDC.TABLE_NAME=UPPER('&TABLE_NAME');

接下来我们要找出在具体的外键字段是否有索引，脚本如下所示：

SELECTCON.OWNER,CON.TABLE_NAME,CON.CONSTRAINT_NAME,CON.COL_LIST,'NoIndexed'ASINDEX_STATUSFROM(SELECTCC.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_LISTFROMDBA_CONSTRAINTSDC,DBA_CONS_COLUMNSCCWHEREDC.OWNER=CC.OWNERANDDC.CONSTRAINT_NAME=CC.CONSTRAINT_NAMEANDDC.CONSTRAINT_TYPE='R'ANDDC.OWNERNOTIN('SYS','SYSTEM','OLAPSYS','SYSMAN','MDSYS','ADMIN')GROUPBYCC.OWNER,CC.TABLE_NAME,CC.CONSTRAINT_NAME)CONWHERENOTEXISTS(SELECT1FROM(SELECTTABLE_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_LISTFROMDBA_IND_COLUMNSWHERETABLE_OWNERNOTIN('SYS','SYSTEM','OLAPSYS','SYSMAN','MDSYS')GROUPBYTABLE_OWNER,TABLE_NAME,INDEX_NAME)COLWHERECON.OWNER=COL.TABLE_OWNERANDCON.TABLE_NAME=COL.TABLE_NAMEANDCON.COL_LIST=SUBSTR(COL.COL_LIST,1,LENGTH(CON.COL_LIST)));

如果是ORACLE 11g或以上版本，数据库有分析函数LISTAGG的话，可以使用下面脚本

SELECTCASEWHENB.TABLE_NAMEISNULLTHEN'NOINDEXED'ELSE'INDEXED'ENDASSTATUS,A.TABLE_OWNERASTABLE_OWNER,A.TABLE_NAMEASTABLE_NAME,A.CONSTRAINT_NAMEASFK_NAME,A.FK_COLUMNSASFK_COLUMNS,B.INDEX_NAMEASINDEX_NAME,B.INDEX_COLUMNSASINDEX_COLUMNSFROM(SELECTA.OWNERASTABLE_OWNER,A.TABLE_NAMEASTABLE_NAME,A.CONSTRAINT_NAMEASCONSTRAINT_NAME,LISTAGG(A.COLUMN_NAME,',')WITHINGROUP(ORDERBYA.POSITION)FK_COLUMNSFROMDBA_CONS_COLUMNSA,DBA_CONSTRAINTSBWHEREA.CONSTRAINT_NAME=B.CONSTRAINT_NAMEANDB.CONSTRAINT_TYPE='R'ANDA.OWNER=B.OWNERANDA.OWNERNOTIN('SYS','SYSTEM','OLAPSYS','SYSMAN','MDSYS')GROUPBYA.OWNER,A.TABLE_NAME,A.CONSTRAINT_NAME)A,(SELECTTABLE_OWNER,TABLE_NAME,INDEX_NAME,LISTAGG(C.COLUMN_NAME,',')WITHINGROUP(ORDERBYC.COLUMN_POSITION)INDEX_COLUMNSFROMDBA_IND_COLUMNSCGROUPBYTABLE_OWNER,TABLE_NAME,INDEX_NAME)BWHEREA.TABLE_NAME=B.TABLE_NAME(+)ANDA.TABLE_OWNER=B.TABLE_OWNER(+)ANDB.INDEX_COLUMNS(+)LIKEA.FK_COLUMNS||'%'ORDERBY1DESC

自动生成创建外键索引的脚本

上面的这些脚本已经能找出那些外键字段已经建立或未建立索引，此时如果对外键字段缺少索引的表手工创建索引的话，如果数量很多的话，那么工作量也非常大，下面可以用这个脚本自动生成缺失的索引

/*******************************************************************************************--脚本功能描述：--对于数据库中外键缺少索引的字段，生成对应的索引（排除一些系统账号，例如sys、system）,如果外键索引超过十个字段--那么这个脚本就不能正确的生成对应的索引，当然也很少有外键设置在超过10个字段的。另外索引表空--空间跟数据表空间相同，如有分开的话，建议在此处再做调整。********************************************************************************************/SELECT'CREATEINDEX'||OWNER||'.'||REPLACE(CONSTRAINT_NAME,'FK_','IX_')||'ON'||OWNER||'.'||TABLE_NAME||'('||COL_LIST||')TABLESPACE'||(SELECTTABLESPACE_NAMEFROMDBA_TABLESWHEREOWNER=CON.OWNERANDTABLE_NAME=CON.TABLE_NAME)ASCREATE_INDEXES_ON_FOREIGN_KEYFROM(SELECTCC.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_LISTFROMDBA_CONSTRAINTSDC,DBA_CONS_COLUMNSCCWHEREDC.OWNER=CC.OWNERANDDC.CONSTRAINT_NAME=CC.CONSTRAINT_NAMEANDDC.CONSTRAINT_TYPE='R'ANDDC.OWNERNOTIN('SYS','SYSTEM','OLAPSYS','SYSMAN','MDSYS','ADMIN')GROUPBYCC.OWNER,CC.TABLE_NAME,CC.CONSTRAINT_NAME)CONWHERENOTEXISTS(SELECT1FROM(SELECTTABLE_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_LISTFROMDBA_IND_COLUMNSWHERETABLE_OWNERNOTIN('SYS','SYSTEM','OLAPSYS','SYSMAN','MDSYS')GROUPBYTABLE_OWNER,TABLE_NAME,INDEX_NAME)COLWHERECON.OWNER=COL.TABLE_OWNERANDCON.TABLE_NAME=COL.TABLE_NAMEANDCON.COL_LIST=SUBSTR(COL.COL_LIST,1,LENGTH(CON.COL_LIST)));

--脚本使用分析函数LISTAGG，适用于ORACLE 11g以及以上版本，如果数据库版本是Oracle 11g及以上，就可以使用此脚本替代上面脚本。

SELECT'CREATEINDEX'||OWNER||'.'||REPLACE(CONSTRAINT_NAME,'FK_','IX_')||'ON'||OWNER||'.'||TABLE_NAME||'('||FK_COLUMNS||')TABLESPACE'||(SELECTTABLESPACE_NAMEFROMDBA_TABLESWHEREOWNER=CON.OWNERANDTABLE_NAME=CON.TABLE_NAME)CREATE_INDEXES_ON_FOREIGN_KEYFROM(SELECTCC.OWNER,CC.TABLE_NAME,CC.CONSTRAINT_NAME,LISTAGG(CC.COLUMN_NAME,',')WITHINGROUP(ORDERBYCC.POSITION)FK_COLUMNSFROMDBA_CONS_COLUMNSCC,DBA_CONSTRAINTSDCWHERECC.CONSTRAINT_NAME=DC.CONSTRAINT_NAMEANDDC.CONSTRAINT_TYPE='R'ANDCC.OWNER=DC.OWNERANDDC.OWNERNOTIN('SYS','SYSTEM','OLAPSYS','SYSMAN','MDSYS','ADMIN')GROUPBYCC.OWNER,CC.TABLE_NAME,CC.CONSTRAINT_NAME)CONWHERENOTEXISTS(SELECT1FROM(SELECTTABLE_OWNER,TABLE_NAME,INDEX_NAME,LISTAGG(COLUMN_NAME,',')WITHINGROUP(ORDERBYCOLUMN_POSITION)FK_COLUMNSFROMDBA_IND_COLUMNSWHEREINDEX_OWNERNOTIN('SYS','SYSTEM','OLAPSYS','SYSMAN','MDSYS','ADMIN')GROUPBYTABLE_OWNER,TABLE_NAME,INDEX_NAME)COLWHERECON.OWNER=COL.TABLE_OWNERANDCON.TABLE_NAME=COL.TABLE_NAMEANDCON.FK_COLUMNS=SUBSTR(COL.FK_COLUMNS,1,LENGTH(CON.FK_COLUMNS)))ORDERBY1;

“Oracle中关于外键缺少索引的原因是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！