Oracle查询存在外键约束但未创建对应索引的情况---Alfred Zhao

1.Oracle提供的脚本

如果要求管控严格，可以考虑使用Oracle官方提供的脚本。

Script to Check for Foreign Key Locking Issues for a Specific User (Doc ID 1019527.6)

但也要注意，该脚本是查询当前连接用户存在外键约束但未创建对应索引的情况，且创建了表ck_log的，脚本最开始还会先删除表ck_log，如果业务不允许创建与业务无关的表，甚至业务可能有这种名字的表就一定要注意了！

vi tfsfkchk.sql

SET ECHO off REM NAME: TFSFKCHLK.SQL REM USAGE:"@path/tfsfkchk" REM -------------------------------------------------------------------------- REM REQUIREMENTS: REM None -- checks only the USER_ views REM -------------------------------------------------------------------------- REM This file checks the current users Foreign Keys to make sure of the REM following: REM REM 1) All the FK columns are have indexes to prevent a possible locking REM problem that can slow down the database. REM REM 2) Checks the ORDER OF THE INDEXED COLUMNS. To prevent the locking REM problem the columns MUST be index in the same order as the FK is REM defined. REM REM 3) If the script finds and miss match the script reports the correct REM order of columns that need to be added to prevent the locking REM problem. REM REM REM REM ------------------------------------------------------------------------- REM Main text of script follows: drop table ck_log; create table ck_log ( LineNum number, LineMsg varchar2(2000)); declare t_CONSTRAINT_TYPE user_constraints.CONSTRAINT_TYPE%type; t_CONSTRAINT_NAME USER_CONSTRAINTS.CONSTRAINT_NAME%type; t_TABLE_NAME USER_CONSTRAINTS.TABLE_NAME%type; t_R_CONSTRAINT_NAME USER_CONSTRAINTS.R_CONSTRAINT_NAME%type; tt_CONSTRAINT_NAME USER_CONS_COLUMNS.CONSTRAINT_NAME%type; tt_TABLE_NAME USER_CONS_COLUMNS.TABLE_NAME%type; tt_COLUMN_NAME USER_CONS_COLUMNS.COLUMN_NAME%type; tt_POSITION USER_CONS_COLUMNS.POSITION%type; tt_Dummy number; tt_dummyChar varchar2(2000); l_Cons_Found_Flag VarChar2(1); Err_TABLE_NAME USER_CONSTRAINTS.TABLE_NAME%type; Err_COLUMN_NAME USER_CONS_COLUMNS.COLUMN_NAME%type; Err_POSITION USER_CONS_COLUMNS.POSITION%type; tLineNum number; cursor UserTabs is select table_name from user_tables order by table_name; cursor TableCons is select CONSTRAINT_TYPE, CONSTRAINT_NAME, R_CONSTRAINT_NAME from user_constraints where OWNER = USER and table_name = t_Table_Name and CONSTRAINT_TYPE = 'R' order by TABLE_NAME, CONSTRAINT_NAME; cursor ConColumns is select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, POSITION from user_cons_columns where OWNER = USER and CONSTRAINT_NAME = t_CONSTRAINT_NAME order by POSITION; cursor IndexColumns is select TABLE_NAME, COLUMN_NAME, POSITION from user_cons_columns where OWNER = USER and CONSTRAINT_NAME = t_CONSTRAINT_NAME order by POSITION; DebugLevel number := 99; -- >>> 99 = dump all info` DebugFlag varchar(1) := 'N'; -- Turn Debugging on t_Error_Found varchar(1); begin tLineNum := 1000; open UserTabs; LOOP Fetch UserTabs into t_TABLE_NAME; t_Error_Found := 'N'; exit when UserTabs%NOTFOUND; -- Log current table tLineNum := tLineNum + 1; insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, NULL ); tLineNum := tLineNum + 1; insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Checking Table '||t_Table_Name); l_Cons_Found_Flag := 'N'; open TableCons; LOOP FETCH TableCons INTO t_CONSTRAINT_TYPE, t_CONSTRAINT_NAME, t_R_CONSTRAINT_NAME; exit when TableCons%NOTFOUND; if ( DebugFlag = 'Y' and DebugLevel >= 99 ) then begin tLineNum := tLineNum + 1; insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found CONSTRAINT_NAME = '|| t_CONSTRAINT_NAME); tLineNum := tLineNum + 1; insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found CONSTRAINT_TYPE = '|| t_CONSTRAINT_TYPE); tLineNum := tLineNum + 1; insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found R_CONSTRAINT_NAME = '|| t_R_CONSTRAINT_NAME); commit; end; end if; open ConColumns; LOOP FETCH ConColumns INTO tt_CONSTRAINT_NAME, tt_TABLE_NAME, tt_COLUMN_NAME, tt_POSITION; exit when ConColumns%NOTFOUND; if ( DebugFlag = 'Y' and DebugLevel >= 99 ) then begin tLineNum := tLineNum + 1; insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, NULL ); tLineNum := tLineNum + 1; insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found CONSTRAINT_NAME = '|| tt_CONSTRAINT_NAME); tLineNum := tLineNum + 1; insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found TABLE_NAME = '|| tt_TABLE_NAME); tLineNum := tLineNum + 1; insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found COLUMN_NAME = '|| tt_COLUMN_NAME); tLineNum := tLineNum + 1; insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found POSITION = '|| tt_POSITION); commit; end; end if; begin select 1 into tt_Dummy from user_ind_columns where TABLE_NAME = tt_TABLE_NAME and COLUMN_NAME = tt_COLUMN_NAME and COLUMN_POSITION = tt_POSITION; if ( DebugFlag = 'Y' and DebugLevel >= 99 ) then begin tLineNum := tLineNum + 1; insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Row Has matching Index' ); end; end if; exception when Too_Many_Rows then if ( DebugFlag = 'Y' and DebugLevel >= 99 ) then begin tLineNum := tLineNum + 1; insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Row Has matching Index' ); end; end if; when no_data_found then if ( DebugFlag = 'Y' and DebugLevel >= 99 ) then begin tLineNum := tLineNum + 1; insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'NO MATCH FOUND' ); commit; end; end if; t_Error_Found := 'Y'; select distinct TABLE_NAME into tt_dummyChar from user_cons_columns where OWNER = USER and CONSTRAINT_NAME = t_R_CONSTRAINT_NAME; tLineNum := tLineNum + 1; insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Changing data in table '||tt_dummyChar ||' will lock table ' ||tt_TABLE_NAME); commit; tLineNum := tLineNum + 1; insert into ck_log ( LineNum, LineMsg ) values ( tLineNum,'Create an index on table '||tt_TABLE_NAME ||' with the following columns to remove lock problem'); open IndexColumns ; loop Fetch IndexColumns into Err_TABLE_NAME, Err_COLUMN_NAME, Err_POSITION; exit when IndexColumns%NotFound; tLineNum := tLineNum + 1; insert into ck_log ( LineNum, LineMsg ) values ( tLineNum,'Column = '||Err_COLUMN_NAME||' ('||Err_POSITION||')'); end loop; close IndexColumns; end; end loop; commit; close ConColumns; end loop; if ( t_Error_Found = 'N' ) then begin tLineNum := tLineNum + 1; insert into ck_log ( LineNum, LineMsg ) values ( tLineNum,'No foreign key errors found'); end; end if; commit; close TableCons; end loop; commit; end; / select LineMsg from ck_log where LineMsg NOT LIKE 'Checking%' AND LineMsg NOT LIKE 'No foreign key%' order by LineNum /2.网络搜索到的脚本

这个是网络搜到的脚本，实测可用，也不需要建立中间表，但是也只能查询当前连接用户，对于DBA而言，也不够方便。

--外键无索引：（当前登陆用户） column con format a20 word_wrapped column idx format a20 word_wrapped column table_name format a30 word_wrapped select decode( b.table_name, NULL, '****', 'ok' ) Status, a.table_name, a.columns con, b.columns idx from ( select substr(a.table_name,1,30) table_name, substr(a.constraint_name,1,30) 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)) || max(decode(position,11,', '||substr(column_name,1,30),NULL)) || max(decode(position,12,', '||substr(column_name,1,30),NULL)) || max(decode(position,13,', '||substr(column_name,1,30),NULL)) || max(decode(position,14,', '||substr(column_name,1,30),NULL)) || max(decode(position,15,', '||substr(column_name,1,30),NULL)) || max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns from user_cons_columns a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = 'R' group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a, ( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_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)) || max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) || max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) || max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) || max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) || max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) || max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns from user_ind_columns group by substr(table_name,1,30), substr(index_name,1,30) ) b where a.table_name = b.table_name (+) and b.columns (+) like a.columns || '%' /3.改为可以指定用户的脚本

这个是修改后的脚本，主要就是将user_*的视图改为dba_*，然后手工输入指定对应的业务用户，从而方便DBA查询指定用户下的情况。 vi check_fk_info.sql

--外键无索引：（指定要查询的用户） column con format a20 word_wrapped column idx format a20 word_wrapped column table_name format a30 word_wrapped select decode( b.table_name, NULL, '****', 'ok' ) Status, a.table_name, a.columns con, b.columns idx from ( select substr(a.table_name,1,30) table_name, substr(a.constraint_name,1,30) 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)) || max(decode(position,11,', '||substr(column_name,1,30),NULL)) || max(decode(position,12,', '||substr(column_name,1,30),NULL)) || max(decode(position,13,', '||substr(column_name,1,30),NULL)) || max(decode(position,14,', '||substr(column_name,1,30),NULL)) || max(decode(position,15,', '||substr(column_name,1,30),NULL)) || max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns from dba_cons_columns a, dba_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = 'R' and a.owner = upper('&owner') group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a, ( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_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)) || max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) || max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) || max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) || max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) || max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) || max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns from dba_ind_columns where INDEX_OWNER = upper('&index_owner') group by substr(table_name,1,30), substr(index_name,1,30) ) b where a.table_name = b.table_name (+) and b.columns (+) like a.columns || '%' /4.测试脚本使用

比如使用修改过的脚本，查询业务SCOTT用户下的情况：

sys@DEMO> @check_fk_info.sql Enter value for owner: scott old 25: and a.owner = upper('&owner') new 25: and a.owner = upper('scott') Enter value for index_owner: scott old 45: where INDEX_OWNER = upper('&index_owner') new 45: where INDEX_OWNER = upper('scott') STAT TABLE_NAME CON IDX ---- ------------------------------ -------------------- -------------------- **** EMP DEPTNO sys@DEMO>

上面就是查询到EMP表在DEPTNO列上存在外键约束，且没有索引，如果需要进一步查看表的建表语句，可以这样查询：

--查询建表语句； sys@DEMO> set long 999999 pages 1000 select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual; DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT') -------------------------------------------------------------------------------- CREATE TABLE "SCOTT"."EMP" ( "EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0), CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE, CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"

Oracle的规范是建议在业务用户下表存在外键约束的，都要在对应列上建立相应的索引。实际也遇到过很多客户的生产环境因外键没有创建索引而导致一些死锁和性能相关的问题。

---来自腾讯云社区的---Alfred Zhao