Oracle中關(guān)于外鍵缺少索引的原因是什么

本篇內(nèi)容介紹了“Oracle中關(guān)于外鍵缺少索引的原因是什么”的有關(guān)知識，在實(shí)際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

在ORACLE數(shù)據(jù)庫中，定義外鍵約束時(shí)，ORACLE是不會自動創(chuàng)建對應(yīng)索引的，必須手動在外鍵約束相關(guān)的列上創(chuàng)建索引。那么外鍵字段上是否有必要創(chuàng)建索引呢?如果有必要的話，巡檢時(shí)，如何找出外鍵字段上沒有創(chuàng)建索引的相關(guān)表，并生成對應(yīng)的索引的腳本呢?

外鍵缺失索引的影響

外鍵列上缺少索引會帶來三個(gè)問題，限制并發(fā)性、影響性能、還有可能造成死鎖。所以對于絕大部分場景，我們應(yīng)該盡量考慮在外鍵上面創(chuàng)建索引

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

2. 影響性能。如果子表外鍵沒有創(chuàng)建索引，那么當(dāng)父表查詢關(guān)聯(lián)子表時(shí)，子表將進(jìn)行全表掃描。影響表連接方式。

3. 影響并發(fā)。無論是更新父表主鍵，或者刪除一個(gè)父記錄，都會在子表中加一個(gè)表鎖(在這條語句完成前，不允許對子表做任何修改)。這就會不必要地鎖定更多的行，而影響并發(fā)性

4. 在特殊情況下，還有可能造成死鎖。

我們先來看看一個(gè)簡單的例子，看看當(dāng)外鍵缺失索引時(shí)，子表是否進(jìn)行全表掃描，如下所示，表EMP與DEPT存在主外鍵關(guān)系：

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

如上所示，當(dāng)外鍵字段沒有索引時(shí)，父表與子表關(guān)聯(lián)時(shí)，子表會進(jìn)行全表掃描，下面，我在外鍵字段創(chuàng)建索引后，就能避免子表表掃描了。

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

當(dāng)然這兩個(gè)表的數(shù)據(jù)量實(shí)在是太少了，性能上差別不大，當(dāng)數(shù)據(jù)量增長上去后，這個(gè)性能差異就會比較明顯了。如下例子所示，我們構(gòu)造一個(gè)數(shù)據(jù)量相對較大的父表與子表的案例：

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>

上面腳本構(gòu)造了測試用的例子和數(shù)據(jù)， 那么我們對比看看外鍵有無索引的區(qū)別：

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>

創(chuàng)建索引后，我們再來看看其執(zhí)行計(jì)劃，注意對比創(chuàng)建索引前后，執(zhí)行計(jì)劃的差異，如下所示：

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;

接下來，我們再來看看外鍵缺失索引影響并發(fā)，以及造成死鎖的情況，如下所示，創(chuàng)建表dead_lock_parent與dead_lock_foreign，兩者存在主外鍵關(guān)系，分布插入兩條測試數(shù)據(jù)：

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)里面執(zhí)行下面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)里面執(zhí)行下面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)里執(zhí)行刪除dead_lock_parent中id為1的記錄：

SQL> delete from dead_lock_parent where id=1;

此時(shí)你會發(fā)現(xiàn)會話被阻塞了，我們可以用下面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;

上面信息如果不能讓你理解，那么可以看看下面腳本，相信你能看得更詳細(xì)。

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里面執(zhí)行下面SQL，刪除主表中id=2的記錄

SQL> delete from dead_lock_parent where id=2;

你會發(fā)現(xiàn)會話1就會出現(xiàn)Deadlock

如果你在外鍵字段上創(chuàng)建索引，那么這種情況下的操作就不會出現(xiàn)死鎖。在這里就不再贅述。有興趣可以測試一下.

外鍵創(chuàng)建索引建議(Foreign Key Indexing Tips)

雖然增加索引，可能會帶來一些額外的性能開銷(DML操作開銷增加)和磁盤空間方面的開銷，但是相比其帶來的性能改善而言，這些額外的開銷其實(shí)完全可以忽略。如果沒有其他特殊情況，建議所有的外鍵字段都加上索引。在Oracle  Oracle Database 9i/10g/11g編程藝術(shù)這本書中介紹了在什么時(shí)候不需要對外鍵加索引. 必須滿足下面三個(gè)條件：

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

2. 不會刪除父表中的行。

3. 不論是有意還是無意，總之不會更新父表的唯一/主鍵字段值。

4. 不會從父表聯(lián)結(jié)到子表， 或者更通俗的講，外鍵列不支持子表的一個(gè)重要訪問路徑，而且你在謂詞中沒有使用這些外鍵累從子表中選擇數(shù)據(jù)。

找出未索引的外鍵

我們首先可以通過下面腳本，找到整個(gè)數(shù)據(jù)庫中那些表有主外鍵關(guān)系，并列出主外鍵約束.

--查看整個(gè)數(shù)據(jù)庫下?lián)碛兄魍怄I關(guān)系的所有表(排除一些系統(tǒng)用戶)

--查看整個(gè)數(shù)據(jù)庫下?lián)碛兄魍怄I關(guān)系的所有表（排除一些系統(tǒng)用戶）    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' );

--查看某個(gè)Schema下?lián)碛兄魍怄I關(guān)系的所有表

--查看某個(gè)Schema下?lián)碛兄魍怄I關(guān)系的所有表    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');

--查看某個(gè)具體的表是否和其它表擁有主外鍵關(guān)系

--查看某個(gè)具體的表是否和其它表擁有主外鍵關(guān)系   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或以上版本，數(shù)據(jù)庫有分析函數(shù)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

自動生成創(chuàng)建外鍵索引的腳本

上面的這些腳本已經(jīng)能找出那些外鍵字段已經(jīng)建立或未建立索引，此時(shí)如果對外鍵字段缺少索引的表手工創(chuàng)建索引的話，如果數(shù)量很多的話，那么工作量也非常大，下面可以用這個(gè)腳本自動生成缺失的索引

/******************************************************************************************* --腳本功能描述： --  對于數(shù)據(jù)庫中外鍵缺少索引的字段，生成對應(yīng)的索引（排除一些系統(tǒng)賬號，例如sys、system）,如果外鍵索引超過十個(gè)字段 --  那么這個(gè)腳本就不能正確的生成對應(yīng)的索引，當(dāng)然也很少有外鍵設(shè)置在超過10個(gè)字段的。另外索引表空 --  空間跟數(shù)據(jù)表空間相同，如有分開的話，建議在此處再做調(diào)整。 ********************************************************************************************/ 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) ) )  ;

--腳本使用分析函數(shù)LISTAGG， 適用于ORACLE 11g以及以上版本，如果數(shù)據(jù)庫版本是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;

“Oracle中關(guān)于外鍵缺少索引的原因是什么”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！