外键索引测试
# 创建测试表
```
create table emp(emp_id number primary key, dept_id number);
create table dept(dept_id number primary key);
alter table emp add constraint emp_fk1 foreign key (dept_id) references dept(dept_id);
```
# 插入数据
```
insert into dept values(10);
insert into dept values(20);
insert into dept values(30);
insert into emp values(1,10);
insert into emp values(2,20);
insert into emp values(3,10);
commit;
```
# 删除数据
```
---session 1，不提交:
delete from emp where dept_id = 10;
---session 2:
delete from dept where dept_id = 30;
```
# 现象
外键无索引时，session2一直处于等待状态。
**实际过程中可能会遇到以下两种情况：**
- 使用ON DELETE CASCADE删除父表中的记录时子表会进行全表扫描。
- 父表和子表的连接查询时子表会进行全表扫描。
# 查询外键是否有索引SQL
## sql1
```
SELECT DISTINCT a.owner owner,
a.constraint_name cons_name,
a.table_name tab_name,
b.column_name cons_column,
NVL (c.column_name, ‘***Check index****‘) ind_column
FROM dba_constraints a, dba_cons_columns b, dba_ind_columns c
WHERE constraint_type = ‘R‘
AND a.owner = UPPER (‘&user_name‘)
AND a.owner = b.owner
AND a.constraint_name = b.constraint_name
AND b.column_name = c.column_name(+)
AND b.table_name = c.table_name(+)
AND b.position = c.column_position(+)
ORDER BY tab_name, ind_column;
```
## sql2
```
SELECT CASE
WHEN ind.index_name IS NOT NULL
THEN
CASE
WHEN ind.index_type IN (‘BITMAP‘) THEN ‘** Bitmp idx **‘
ELSE ‘indexed‘
END
ELSE
‘** Check idx **‘
END
checker,
ind.index_type,
cons.owner,
cons.table_name,
ind.index_name,
cons.constraint_name,
cons.cols
FROM ( SELECT c.owner,
c.table_name,
c.constraint_name,
LISTAGG (cc.column_name, ‘,‘)
WITHIN GROUP (ORDER BY cc.column_name)
cols
FROM dba_constraints c, dba_cons_columns cc
WHERE c.owner = cc.owner
AND c.owner = UPPER (‘&schema‘)
AND c.constraint_name = cc.constraint_name
AND c.constraint_type = ‘R‘
GROUP BY c.owner, c.table_name, c.constraint_name) cons
LEFT OUTER JOIN
( SELECT table_owner,
table_name,
index_name,
index_type,
cbr,
LISTAGG (column_name, ‘,‘) WITHIN GROUP (ORDER BY column_name)
cols
FROM ( SELECT ic.table_owner,
ic.table_name,
ic.index_name,
ic.column_name,
ic.column_position,
i.index_type,
CONNECT_BY_ROOT (ic.column_name) cbr
FROM dba_ind_columns ic, dba_indexes i
WHERE ic.table_owner = UPPER (‘&schema‘)
AND ic.table_owner = i.table_owner
AND ic.table_name = i.table_name
AND ic.index_name = i.index_name
CONNECT BY PRIOR ic.column_position - 1 =
ic.column_position
AND PRIOR ic.index_name = ic.index_name)
GROUP BY table_owner,
table_name,
index_name,
index_type,
cbr) ind
ON cons.cols = ind.cols
AND cons.table_name = ind.table_name
AND cons.owner = ind.table_owner
ORDER BY checker, cons.owner, cons.table_name;
```
> 引用：Oracle Database 12c Performance Tuning Recipes