前言：
而今姐妹们对“oracle无效索引”大体比较讲究，小伙伴们都需要了解一些“oracle无效索引”的相关资讯。那么小编在网摘上网罗了一些有关“oracle无效索引””的相关内容，希望咱们能喜欢，朋友们快快来学习一下吧！通过10046事件分析外键无索引导致阻塞
--为什么外键没有索引会导致阻塞 甚至死锁
测试
create table t1
(id number,
name varchar2(1)
);
alter table t1 add constraint pk_t1 primary key (id);
create table t2
(id number,
t1_id number,
name varchar2(1)
);
alter table t2 add constraint pk_t2 primary key (id);
alter table t2 add constraint fk_t2 foreign key (t1_id) references t1(id);
insert into t1 values(1, 'a');
insert into t1 values(2, 'b');
insert into t2 values(1, 1, 'c');
--会话1 开启10046trace分析
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered
--子表行被锁住
SQL> delete from t2 where id=1;
1 row deleted
--先暂停 去执行会话2
SQL> commit;
Commit complete
--关闭10046
SQL> alter session set events '10046 trace name context off';
Session altered
--查看trc文件目录
SQL> select * from v$diag_info where name like 'Default%';
会话2
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered
--在会话1 中锁住了t2 ,会话2因为t2是子表，也必须占有t2表，就锁住了，表这个时候会阻塞
SQL> delete from t1 where id=1;
1 row deleted
SQL> commit;
Commit complete
SQL> alter session set events '10046 trace name context off';
Session altered
SQL> select * from v$diag_info where name like 'Default%';
分析：会话2阻塞，所以看一下会话2的10046
根据上面查到的路径 linux下执行
tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_833.trc session2.txt sys=no
会在当前目录下生成session2.txt，里面有详细信息，因为涉及明细，所以不需要用tkprof 命令转化成session2.txt，直接看orcl_ora_833.trc即可，结果如下，会话2虽然删除t1表的数据，但是还是会去占用子级表t2，图中是会话2执行计划，用全表扫描，全表就被锁了，如果有外键索引，执行计划走的是索引扫描，就不会导致全表锁定，而是只锁定修改行，这时候t2的该行被会话1持有，还没释放，所以会话2会一直阻塞，直到会话1提交或者回滚，这时候会话2对t2是想要全表独占，如果这时候有外键索引，会话2对t2就不是全表独占，而是只占某些要跟新的行。从而不影响其他会话使用表的其他行，
特别：如果先删除主表的数据，主表要删除的数据在子表没有，且子表没被锁定时，那么删除主表这条记录后，不会锁定子表了，因为这时候子表没有被锁定，（我子表都没你主表的记录，你凭什么锁定我！！哈哈）
如果先删除子表的数据，这时候再删除主表的数据，即使删除的主表数据在子表没有值，会话也会被阻塞。因为多了先删除子表的数据的操作，子表数据已被锁定了（建外键索引解决）
总之，外键加索引是常识，原理是避免全表被独占，如下图官方文档。
原文地址：
无外键索引时，子表全被锁定
Share lock acquired 是独占全表的锁
有外键索引时，只有锁定子表的一行
详细参考
Oracle编程艺术深入数据库体系结构（第二版）第六章
附件
查看整个数据库下拥有主外键关系的所有表（排除一些系统用户）
找出在具体的外键字段是否有索引，脚本如下所示：
--查看整个数据库下拥有主外键关系的所有表（排除一些系统用户）
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' );
--找出在具体的外键字段是否有索引，脚本如下所示：
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无效索引
