In an ORACLE database, when a foreign key constraint is defined, ORACLE does not automatically create the corresponding index; it must manually create the index on the columns associated with the foreign key constraint.Is it necessary to create an index on a foreign key field?If necessary, when patrolling, how can I find out the related tables on foreign key fields that do not have indexes created and generate scripts for the corresponding indexes?
Foreign Key Missing Index Impact
Missing an index on a foreign key column can cause three problems, limiting concurrency, affecting performance, and possibly causing deadlocks.So for most scenarios, we should try to create an index on the foreign key
1. Influences performance.If the child table foreign key does not create an index, the child table will be scanned in full when the parent table queries the associated child table.Affects how tables are joined.
2. Affect concurrency.Whether updating the primary key of the parent table or deleting a parent record, a table lock is added to the child table (no modifications to the child table are allowed until this statement is complete).This will be unnecessary
Lock more rows, affecting concurrency
3: Deadlock may also occur under special circumstances.
Let's start with a simple example of whether a subtable scans a full table when a foreign key is missing an index, as shown below, where table EMP has a primary foreign key relationship to 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.DEPTNO3 GROUP BY D.DEPTNO;DEPTNO COUNT(*)---------- ----------30 620 510 3Execution 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 calls0 db block gets7 consistent gets0 physical reads0 redo size665 bytes sent via SQL*Net to client524 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)3 rows processed
As shown above, when a foreign key field does not have an index, the parent table scans the child table when it is associated with a child table. Below, I can avoid scanning the child table once the foreign key field has been indexed.
CREATE INDEX SCOTT.IX_DEPTNO ON SCOTT.EMP ("DEPTNO") TABLESPACE USERS;
Of course, the amount of data in these two tables is really too small, the performance difference is not big, when the amount of data increases, the performance difference will be more obvious.As an example below, we construct a parent and child table with a relatively large amount of data:
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);beginfor index_num in 1 .. 10000 loopinsert into parent_tb_testselect index_num , 'kerry' || to_char(index_num) from dual;if mod(index_num,100) = 0 thencommit;end if;end loop;commit;end;/declare index_num number :=1;beginfor index_parent in 1 .. 10000 loopfor index_child in 1 .. 1000 loopinsert into child_tb_testselect index_num, index_parent, 'child' || to_char(index_child) from dual;index_num := index_num +1;if mod(index_child,1000) = 0 thencommit;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>
The above script constructs examples and data for testing, so let's compare the foreign keys to see if they have an index:
SQL> set linesize 1200SQL> set autotrace traceonlySQL> select p.id , p.name,c.child_name2 from test.parent_tb_test p3 inner join test.child_tb_test c on p.id = c.f_id4 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 calls0 db block gets32855 consistent gets32772 physical reads0 redo size29668 bytes sent via SQL*Net to client1218 bytes received via SQL*Net from client68 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1000 rows processedSQL>
After creating the index, let's look at its execution plan again and note the differences between the execution plans before and after the index is created, as follows:
SQL> create index ix_child_tb_test on child_tb_test(f_id);SQL> set linesize 1200SQL> set autotrace traceonlySQL> select p.id , p.name,c.child_name2 from test.parent_tb_test p3 inner join test.child_tb_test c on p.id = c.f_id4 where p.id=1000;
Next, let's look at how missing foreign key indexes affect concurrency and cause deadlocks. As shown below, create tables dead_lock_parent and dead_lock_foreign, which have a primary foreign key relationship, and distribute and insert two test data:
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: Execute the following SQL statements in Session 1 (Session ID 789):
SQL> show user;USER by "TEST"SQL> select * from v$mystat where rownum=1;SID STATISTIC# VALUE---------- ---------- ----------789 0 1SQL> delete from dead_lock_foreign where fid=1;One row has been deleted.
2: Execute the following SQL statements in Session 2 (Session ID 766):
SQL> show user;USER is "TEST"SQL> select * from v$mystat where rownum=1;SID STATISTIC# VALUE---------- ---------- ----------766 0 1SQL> delete from dead_lock_foreign where fid=2;1 row deleted.
3: Next, delete the record with ID 1 in dead_lock_parent in session 1 (session ID 789):
SQL> delete from dead_lock_parent where id=1;
You will find that the session is blocked and we can use the following SQL to query for specific blocking information.
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.LOCKWAITFROM V$LOCK LK, DBA_OBJECTS OB, V$SESSION SEWHERE LK.TYPE IN ('TM', 'UL')AND LK.SID = SE.SIDAND LK.ID1 = OB.OBJECT_ID(+)AND SE.SID IN (766,789)ORDER BY SID;
If you don't understand the above information, you can look at the script below and be sure you can see it in more detail.
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 BLOCKFROM V$LOCK L,V$SESSION S,DBA_OBJECTS OWHERE L.SID = S.SIDAND USERNAME != 'SYSTEM'AND O.OBJECT_ID(+) = L.ID1AND 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 0766 TEST XXXX\GET253194 TM DEAD_LOCK_FOREIGN Row Exlusive None 1766 TEST XXXX\GET253194 TM DEAD_LOCK_PARENT Row Exlusive None 0789 TEST DB-Server.loca TX Exclusive None 0ldomain789 TEST DB-Server.loca TM DEAD_LOCK_PARENT Row Exlusive None 0ldomain789 TEST DB-Server.loca TM DEAD_LOCK_FOREIGN Row Exlusive Sh/Row Exlusive 0ldomain
Next execute the following SQL in Session 2, deleting the record with id=2 in the main table
SQL> delete from dead_lock_parent where id=2;
You will find Deadlock in Session 1
If you create an index on a foreign key field, there will be no deadlock in this case.Not to be repeated here.Interested in testing.
Foreign Key Indexing Tips
Although increasing the index may incur additional performance overhead (increased DML operation overhead) and disk space overhead, these additional overhead can actually be ignored in comparison to the performance improvements it brings.If there are no other special cases, it is recommended that all foreign key fields be indexed.This book on the programming art of Oracle Oracle Database 9i/10g/11g describes when it is not necessary to index foreign keys. The following three conditions must be met:
1: Rows in the parent table will not be deleted.
2: The unique/primary key field values of the parent table will not be updated, intentionally or unintentionally.
3: You will not join from the parent table to the child table, or more generally, foreign key columns do not support an important access path to the child table, and you do not use these foreign keys in the predicate to accumulate data from the child table.
Find foreign keys that are not indexed
We can first find out which tables in the entire database have primary foreign key relationships and list the primary foreign key constraints through the following script.
--View all tables with primary foreign key relationships across the database (excluding some system users)
--View all tables with primary foreign key relationships across the database (excluding some system users)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_NAMEFROM DBA_CONSTRAINTS CWHERE CONSTRAINT_TYPE = 'R') DFWHERE DC.CONSTRAINT_NAME =DF.R_CONSTRAINT_NAMEAND DC.OWNER NOT IN ( 'SYSTEM', 'SYS', 'DBSNMP', 'EXFSYS','ORDDATA', 'CTXSYS', 'OLAPSYS', 'MDSYS','SYSMAN' );
--View all tables with primary foreign key relationships under a Schema
--View a Schema All tables below that have primary foreign key relationshipsSELECT 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_NAMEFROM DBA_CONSTRAINTS CWHERE CONSTRAINT_TYPE = 'R') DFWHERE DC.CONSTRAINT_NAME = DF.R_CONSTRAINT_NAMEAND DC.OWNER =UPPER('&OWNER');
--See if a specific table has a primary foreign key relationship with another table
--See if a specific table has a primary foreign key relationship with another tableSELECT 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_NAMEFROM DBA_CONSTRAINTS CWHERE CONSTRAINT_TYPE = 'R') DFWHERE DC.CONSTRAINT_NAME = DF.R_CONSTRAINT_NAMEAND DC.OWNER =UPPER('&OWNER')AND DC.TABLE_NAME=UPPER('&TABLE_NAME');
Next, we want to find out if there is an index on a specific foreign key field, and the script looks like this:
SELECT CON.OWNER ,CON.TABLE_NAME,CON.CONSTRAINT_NAME,CON.COL_LIST,'No Indexed' AS INDEX_STATUSFROM(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_LISTFROM DBA_CONSTRAINTS DC, DBA_CONS_COLUMNS CCWHERE DC.OWNER = CC.OWNERAND DC.CONSTRAINT_NAME = CC.CONSTRAINT_NAMEAND 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) CONWHERE 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_LISTFROM DBA_IND_COLUMNSWHERE TABLE_OWNER NOT IN ('SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN', 'MDSYS')GROUP BY TABLE_OWNER, TABLE_NAME, INDEX_NAME ) COLWHERE CON.OWNER = COL.TABLE_OWNERAND CON.TABLE_NAME = COL.TABLE_NAMEAND CON.COL_LIST = SUBSTR(COL.COL_LIST, 1, LENGTH(CON.COL_LIST) ) ) ;
If you have the analysis function LISTAGG in your database for ORACLE 11g or above, you can use the following script
SELECT CASEWHEN 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_COLUMNSFROM (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_COLUMNSFROM DBA_CONS_COLUMNS A,DBA_CONSTRAINTS BWHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAMEAND B.CONSTRAINT_TYPE = 'R'AND A.OWNER = B.OWNERAND 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_COLUMNSFROM DBA_IND_COLUMNS CGROUP BY TABLE_OWNER,TABLE_NAME,INDEX_NAME) BWHERE 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
Automatically generate script to create foreign key index
These scripts can already find out which foreign key fields are indexed or not. If you manually create an index on a table that lacks an index on a foreign key field, if there is a large number of tables, the workload will be enormous. You can use this script to automatically generate the missing index below
/*******************************************************************************************--Description of scripting capabilities:-- For fields in the database where the foreign key is missing an index, generate the corresponding index (excluding some system accounts, such as sys, system), if the foreign key index exceeds ten fields-- This script will not generate the appropriate index correctly, and of course few foreign keys are set in more than 10 fields.Additional index table is empty-- The space is the same as the data table space, and it is recommended that you make adjustments here if there is any separation.********************************************************************************************/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_KEYFROM(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_LISTFROM DBA_CONSTRAINTS DC, DBA_CONS_COLUMNS CCWHERE DC.OWNER = CC.OWNERAND DC.CONSTRAINT_NAME = CC.CONSTRAINT_NAMEAND 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) CONWHERE 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_LISTFROM DBA_IND_COLUMNSWHERE TABLE_OWNER NOT IN ('SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN', 'MDSYS')GROUP BY TABLE_OWNER, TABLE_NAME, INDEX_NAME ) COLWHERE CON.OWNER = COL.TABLE_OWNERAND CON.TABLE_NAME = COL.TABLE_NAMEAND CON.COL_LIST = SUBSTR(COL.COL_LIST, 1, LENGTH(CON.COL_LIST) ) ) ;
--The script uses the analysis function LISTAGG for ORACLE 11g and above. If the database version is Oracle 11g or above, you can use this script instead of the above script.
SELECT 'CREATE INDEX '|| OWNER|| '.'|| REPLACE(CONSTRAINT_NAME,'FK_','IX_')|| ' ON '|| OWNER|| '.'|| TABLE_NAME|| ' ('|| FK_COLUMNS||') TABLESPACE '||(SELECT TABLESPACE_NAMEFROM DBA_TABLESWHERE OWNER= CON.OWNERAND TABLE_NAME= CON.TABLE_NAME) CREATE_INDEXES_ON_FOREIGN_KEYFROM (SELECT CC.OWNER,CC.TABLE_NAME,CC.CONSTRAINT_NAME,LISTAGG(CC.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY CC.POSITION) FK_COLUMNSFROM DBA_CONS_COLUMNS CC,DBA_CONSTRAINTS DCWHERE CC.CONSTRAINT_NAME = DC.CONSTRAINT_NAMEAND DC.CONSTRAINT_TYPE = 'R'AND CC.OWNER = DC.OWNERAND DC.OWNER NOT IN ( 'SYS','SYSTEM','OLAPSYS','SYSMAN','MDSYS','ADMIN' )GROUP BY CC.OWNER,CC.TABLE_NAME,CC.CONSTRAINT_NAME) CONWHERE NOT EXISTS(SELECT 1FROM (SELECT TABLE_OWNER,TABLE_NAME,INDEX_NAME,LISTAGG(COLUMN_NAME, ',') WITHIN GROUP (ORDER BY COLUMN_POSITION) FK_COLUMNSFROM DBA_IND_COLUMNSWHERE INDEX_OWNER NOT IN ( 'SYS','SYSTEM','OLAPSYS','SYSMAN','MDSYS','ADMIN' )GROUP BY TABLE_OWNER,TABLE_NAME ,INDEX_NAME) COLWHERE CON.OWNER = COL.TABLE_OWNERAND CON.TABLE_NAME = COL.TABLE_NAMEAND CON.FK_COLUMNS = SUBSTR(COL.FK_COLUMNS, 1, LENGTH(CON.FK_COLUMNS)) )ORDER BY 1;
Reference material: