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
);
SQL> select * from user_tables;
no rows selected
SQL> select * from user_indexes;
no rows selected
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
CHILD_TB_TEST
PARENT_TB_TEST
SQL> select index_name from user_indexes;
INDEX_NAME
------------------------------
PK_CHILD_TB_TEST
PK_PARENT_TB_TEST
在ORACLE数据库中，定义外键约束时，ORACLE是不会自动创建对应索引的，必须手动在外键约束相关的列上创建索引。那么外键字段上是否有必要创建索引呢？如果有必要的话，巡检时，如何找出外键字段上没有创建索引的相关表，并生成对应的索引的脚本呢？
外键缺失索引影响
外键列上缺少索引会带来三个问题，限制并发性、影响性能、还有可能造成死锁。所以对于绝大部分场景，我们应该尽量考虑在外键上面创建索引
影响性能。 如果子表外键没有创建索引，那么当父表查询关联子表时，子表将进行全表扫描。影响表连接方式。
影响并发。 无论是更新父表主键，或者删除一个父记录，都会在子表中加一个表锁（在这条语句完成前，不允许对子表做任何修改）。这就会不必要
地锁定更多的行，而影响并发性
3：在特殊情况下，还有可能造成死锁。
我们先来看看一个简单的例子，看看当外键缺失索引时，子表是否进行全表扫描，如下所示，表EMP与DEPT存在主外键关系：
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;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'PARENT_TB_TEST',
tabname => 'TEST',
estimate_percent => 100,
method_opt => 'for all columns size 1',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
上面脚本构造了测试用的例子和数据， 那么我们对比看看外键有无索引的区别：
---高级执行计划:
11G：
alter session set statistics_level=all; ---再运行SQL
select p.id, p.name, c.child_name
from test.parent_tb_test p
inner join test.child_tb_test c
on p.id = c.f_id
where p.id = 1000;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dzm01p60dbuva, child number 0
-------------------------------------
select p.id, p.name, c.child_name from test.parent_tb_test p inner
join test.child_tb_test c on p.id = c.f_id where p.id = 1000
Plan hash value: 901213199
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.28 | 32875 | 32793 |
| 1 | NESTED LOOPS | | 1 | 1000 | 1000 |00:00:00.28 | 32875 | 32793 |
| 2 | TABLE ACCESS BY INDEX ROWID| PARENT_TB_TEST | 1 | 1 | 1 |00:00:00.01 | 3 | 0 |
|* 3 | INDEX UNIQUE SCAN | PK_PARENT_TB_TEST | 1 | 1 | 1 |00:00:00.01 | 2 | 0 |
|* 4 | TABLE ACCESS FULL | CHILD_TB_TEST | 1 | 1000 | 1000 |00:00:00.28 | 32872 | 32793 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("P"."ID"=1000)
4 - filter("C"."F_ID"=1000)
23 rows selected.
此时走全表扫描；
SQL> with cons as (select /*+ materialize */ owner, table_name, constraint_name
from dba_constraints
where owner = 'TEST'
AND constraint_type = 'R'),
idx as (
select /*+ materialize */ table_owner,table_name, column_name
from dba_ind_columns
where table_owner = 'TEST')
select owner,table_name,constraint_name,column_name
from dba_cons_columns
where (owner,table_name, constraint_name) in
(select * from cons)
and (owner,table_name, column_name) not in
(select * from idx); 2 3 4 5 6 7 8 9 10 11 12 13 14
OWNER TABLE_NAME CONSTRAINT_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
TEST CHILD_TB_TEST SYS_C0011365 F_ID
接下来，我们再来看看外键缺失索引影响并发，以及造成死锁的情况，如下所示，创建表dead_lock_parent与dead_lock_foreign，两者存在主外键关系，分布插入两条测试数据：
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>
SQL> set linesize 200
SQL> set pagesize 200
SQL> select * from v$mystat where rownum=1;
SID STATISTIC# VALUE
---------- ---------- ----------
133 0 0
SQL> select * from dead_lock_foreign;
FID FNAME
---------- --------------------------------
1 kerry_fk
2 jimmy_fk
SQL> delete from dead_lock_foreign where fid=1;
1 row deleted.
SQL> set linesize 200
SQL> set pagesize 200
SQL> select * from v$mystat where rownum=1;
SID STATISTIC# VALUE
---------- ---------- ----------
195 0 0
SQL> delete from dead_lock_foreign where fid=2;
1 row deleted.
接着在会话1-133上,执行里执行删除dead_lock_parent中id为1的记录：
delete from dead_lock_parent where id=1;
SQL> select * from v$mystat where rownum=1;
SID STATISTIC# VALUE
---------- ---------- ----------
133 0 0
SQL> select * from dead_lock_foreign;
FID FNAME
---------- --------------------------------
1 kerry_fk
2 jimmy_fk
SQL> delete from dead_lock_foreign where fid=1;
1 row deleted.
SQL> delete from dead_lock_parent where id=1;
SESSION_ID SQL_ID MACHINE BLOCKING_SESSION SAMPLE_TIME MODULE PROGRAM EVENT SQL_TEXT
1 133 027sq2bwan3mv node2 195 24-9月 -19 07.43.19.848 下午 SQL*Plus sqlplus@node2 (TNS V1-V3) enq: TM - contention delete from dead_lock_parent where id=1
2 133 027sq2bwan3mv node2 195 24-9月 -19 07.43.18.848 下午 SQL*Plus sqlplus@node2 (TNS V1-V3) enq: TM - contention delete from dead_lock_parent where id=1
3 133 027sq2bwan3mv node2 195 24-9月 -19 07.43.17.848 下午 SQL*Plus sqlplus@node2 (TNS V1-V3) enq: TM - contention delete from dead_lock_parent where id=1
133 被195 堵塞:
133执行的sql:
SQL> select sql_text from v$sqlarea where (address,hash_value) in
(select /*+unnest*/ DECODE(sql_hash_value,0,prev_sql_addr,sql_address),DECODE(sql_hash_value,0,prev_hash_value,sql_hash_value) from
v$session where sid =133) 2 3 ;
SQL_TEXT
--------------------------------------------------------------------------------
delete from dead_lock_parent where id=1
195 执行的SQL：
SQL> select sql_text from v$sqlarea where (address,hash_value) in
(select /*+unnest*/ DECODE(sql_hash_value,0,prev_sql_addr,sql_address),DECODE(sql_hash_value,0,prev_hash_value,sql_hash_value) from
v$session where sid =195) 2 3
4 ;
SQL_TEXT
--------------------------------------------------------------------------------
delete from dead_lock_foreign where fid=2