2011-11-29 外键无索引

巩固知识点1： 外键上无索引的危害
在编程艺术上就看到tom说过外键上没有索引是造成死锁的主要原因，今天在PUB上看到一贴，也是外键上没有索引的案例：

同事用主键=value作为条件delete一个大表
delete from crm_application_instances where N_APP_INSTANCE_ID = 35151 ;


按理应该很快，但实际慢的没谱,磁盘的b%迅速飙升到100%，一看肯定是全表扫描了。

查看v$sql_plan，计划很对
对语句进行跟踪发现在执行delete之后又去执行了一个本身的全表扫描，语句看起来比较怪
select count(1)
from
"ADMGEMALTO"."CRM_APPLICATION_INSTANCES" where "N_PARENT_SD_ID" = :1

正是这个语句引起的全表扫描：

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      5.76      67.34     217039     218261          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      5.76      67.34     217039     218261          0           0

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
      0   TABLE ACCESS FULL CRM_APPLICATION_INSTANCES (cr=0 pr=0 pw=0 time=0 us cost=206007 size=13 card=1)

第一直觉是因为某个trigger引起的。（惭愧，第一直觉错了）
结果查遍dba_source和dba_triggers无获

后来折腾一会才发现，这个表的字段N_PARENT_SD_ID上加了外键，引用了自己的主键。
而这个奇怪的语句是oracle是主表数据删除时，为了检查子表数据自动生成的SQL。
进一步检查发现，该大表作为父表，还被另外2个大表引用，外键上都没有建索引。
引起的性能问题是很可怕的。

对于这个问题TOM是这样说的：
如果外键的删除策略是 ON DELETE CASCADE，而且没有对子表加索引，那么删除主表中的每一行都会对子表做一个全表扫描，这个全表扫描可能是不必要的，而且如果从主表删除多行，主表中每删除一行就要扫描一次子表

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24383181/viewspace-712278/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24383181/viewspace-712278/