Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the reason for the lack of index of foreign keys in Oracle". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

In the ORACLE database, when you define a foreign key constraint, ORACLE does not automatically create the corresponding index, but must manually create the index on the column related to the foreign key constraint. So is it necessary to create an index on the foreign key field? If necessary, how can you find out the related tables on the foreign key field that are not indexed and generate a script for the corresponding index?

The influence of missing index of foreign key

The lack of indexes on foreign key columns can cause three problems, limiting concurrency, affecting performance, and possibly causing deadlocks. So for most scenarios, we should try to consider creating indexes on foreign keys.

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Affect performance. If the child table foreign key does not create an index, the child table will be scanned fully when the parent table queries the associated child table. Affects how tables are joined.

Affect concurrency. Whether you update the primary key of the parent table or delete a parent record, a table lock is added to the child table (no changes to the child table are allowed until this statement is complete). This unnecessarily locks more rows and affects concurrency

In special cases, deadlocks can also be caused.

Let's take a look at a simple example to see whether a full table scan is performed on a child table when the foreign key is missing an index, as shown below. Table EMP has a primary foreign key relationship with 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

As shown above, when the foreign key field has no index, when the parent table is associated with the child table, the child table will perform a full table scan. Next, after I create the index in the foreign key field, I can avoid the child table scan.

CREATE INDEX SCOTT.IX_DEPTNO ON SCOTT.EMP ("DEPTNO") TABLESPACE USERS

Of course, the amount of data in these two tables is too small, and there is little difference in performance. When the amount of data increases, the performance difference will be more obvious. As shown in the following example, we construct a case of parent and child tables 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); 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 >

The above script constructs the examples and data for testing, so let's compare and see if the foreign keys are indexed:

SQL > set linesize 1200 SQL > set autotrace traceonly SQL > select p.id, p. Namethec. Childcare 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 >

After creating the index, let's take a look at its execution plan and notice the difference in the execution plan before and after creating the index, as shown below:

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.childcare 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

Next, let's take a look at the situation in which missing foreign keys 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 insert two pieces of test data in a distributed manner:

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 statement in session 1 (session ID is 789):

SQL > show user; USER is "TEST" SQL > select * from v$mystat where rownum=1; SID STATISTIC# VALUE-789 01 SQL > delete from dead_lock_foreign where fid=1; 1 line deleted.

2: execute the following SQL statement in session 2 (session ID is 766):

SQL > show user; USER is "TEST" SQL > select * from v$mystat where rownum=1; SID STATISTIC# VALUE-76601 SQL > delete from dead_lock_foreign where fid=2; 1 row deleted.

3: then delete the record with id 1 in dead_lock_parent in session 1 (session ID is 789):

SQL > delete from dead_lock_parent where id=1

At this point you will find that the session is blocked, we can use the following SQL to query the 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.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 (766789) ORDER BY SID

If you can't understand the above information, you can take a look at the following script. I'm 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 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 (766789) 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 1 766 TEST XXXX\ GET253194 TM DEAD_LOCK_PARENT Row Exlusive None 0789 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

Then execute the following SQL in session 2 to delete the record of id=2 in the main table

SQL > delete from dead_lock_parent where id=2

You will find that Deadlock will appear in conversation 1.

If you create an index on a foreign key field, there will be no deadlock in this case. I won't repeat it here. You can test it if you are interested.

Foreign key creation index recommendation (Foreign Key Indexing Tips)

Although adding indexes may result in some additional performance overhead (increased DML operation overhead) and disk space overhead, these additional costs are negligible compared to the performance improvements. If there are no other special cases, it is recommended that all foreign key fields be indexed. The Oracle Oracle Database 9i/10g/11g programming Art book describes when foreign keys are not needed to be indexed. The following three conditions must be met:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Rows in the parent table are not deleted.

Intentionally or unintentionally, the unique / primary key field value of the parent table is not updated.

It is not joined from the parent table to the child table, or, more generally, the foreign key column does not support an important access path to the child table, and you do not use these foreign keys in the predicate to select data from the child table.

Find out the unindexed foreign key

First of all, we can find out which tables in the entire database have primary foreign key relationships and list primary foreign key constraints through the following script.

-- View all tables with primary and foreign key relationships in the entire database (excluding some system users)

-- View all tables with primary and foreign key relationships in the entire 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_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')

-- View all tables that have primary and foreign key relationships under a Schema

-- View all tables under a Schema that have primary and foreign key relationships: 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')

-- check whether a specific table has a primary foreign key relationship with other tables

-- check whether a specific table has primary foreign key relationships with other tables 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')

Next we need to find out whether there is an index in the specific foreign key field, as shown in the following script:

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)) | MAX (DECODE (POSITION, COLUMN_NAME,1,30),'| | SUBSTR (COLUMN_NAME,1,30) |'", NULL) | | MAX (DECODE (POSITION, NULL),'|" | | SUBSTR (COLUMN_NAME,1,30) |'"| NULL)) | | MAX (DECODE (POSITION, 5 COLUMN_NAME,1,30,'|'"| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL)) | | MAX (DECODE (POSITION, 6 COLUMN_NAME,1,30),'|'"| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL) | MAX (DECODE (POSITION, 7)) | |'"| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL) | | MAX (POSITION, 8 NULL,'| |'"| |'" | | SUBSTR (COLUMN_NAME,1,30) | |'", NULL) | | MAX (DECODE (POSITION, 9 NULL) |'" | | SUBSTR (COLUMN_NAME,1,30) | |'"| NULL)) | | MAX (DECODE (POSITION, 10 COLUMN_NAME,1,30,'|'"| | 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, 2jue') '|' "| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL)) | | MAX (COLUMN_POSITION, 3 COLUMN_NAME,1,30,'| |'"| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL) | | MAX (COLUMN_POSITION, 4 COLUMN_POSITION') | '|' "| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL) | | MAX (COLUMN_POSITION, 5 NULL,'| |'"| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL) | | MAX (COLUMN_POSITION, 6 NULL) | '|' "| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL) | | MAX (COLUMN_POSITION, 7 NULL,'| |'"| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL) | | MAX (COLUMN_POSITION, 8 NULL) | |'"| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL) | | MAX (COLUMN_POSITION, 9 NULL,'| |'"| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL) | | MAX (COLUMN_POSITION, 10 Magi') | '|' "| | 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)

If you have ORACLE 11g or above and the database has the parsing function LISTAGG, you can use the following script

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

Automatically generate scripts to create foreign key indexes

The above scripts have been able to find out which foreign key fields have been indexed or not indexed. At this time, if the table with a lack of index in the foreign key field creates the index manually, if the number is large, then the workload is also very large. The following script can be used to automatically generate missing indexes

/ *-script function description:-for fields in the database where foreign keys lack an index Generate the corresponding index (excluding some system accounts, such as sys, system), if the foreign key index exceeds ten fields-then the script cannot generate the corresponding index correctly, and of course few foreign keys are set to more than 10 fields. In addition, the index table is empty-the space is the same as the data table space, if there is a separation, it is recommended to make adjustments here. * / 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 (POSITION, 2 COLUMN_NAME,1,30,'| |'"| | SUBSTR (COLUMN_NAME,1,30) |'", NULL) | | MAX (POSITION, 3 NULL) |'"| | SUBSTR (COLUMN_NAME,1,30) |'" | NULL)) | | MAX (DECODE (POSITION, 4 COLUMN_NAME,1,30,'|'"| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL)) | | MAX (DECODE (POSITION, 5 COLUMN_NAME,1,30),'|'"| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL)) | MAX (POSITION (POSITION, 6)) | '|' "| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL) | | MAX (POSITION, 7POSITION, NULL) | MAX (SUBSTR (COLUMN_NAME,1,30) | |'"| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL) | | MAX (POSITION (POSITION, 8POSITION),'| | "| | SUBSTR (COLUMN_NAME,1,30) | |'" | NULL)) | | MAX (DECODE (POSITION, 9 COLUMN_NAME,1,30,'| |'"| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL)) | | MAX (DECODE (POSITION, 10 COLUMN_NAME,1,30),'|'"| | 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, 2jue') '|' "| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL)) | | MAX (COLUMN_POSITION, 3 COLUMN_NAME,1,30,'| |'"| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL) | | MAX (COLUMN_POSITION, 4 COLUMN_POSITION') | '|' "| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL) | | MAX (COLUMN_POSITION, 5 NULL,'| |'"| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL) | | MAX (COLUMN_POSITION, 6 NULL) | '|' "| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL) | | MAX (COLUMN_POSITION, 7 NULL,'| |'"| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL) | | MAX (COLUMN_POSITION, 8 NULL) | |'"| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL) | | MAX (COLUMN_POSITION, 9 NULL,'| |'"| | SUBSTR (COLUMN_NAME,1,30) | |'", NULL) | | MAX (COLUMN_POSITION, 10 Magi') | '|' "| | 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)

-- the script uses the analysis function LISTAGG, which is suitable for ORACLE 11g and above. If the database version is Oracle 11g or above, you can use this script to replace the above script.

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 This is the end of the content of "what is the reason for the lack of index of foreign keys in Oracle". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.