再一次强调，ORACLE外键必须加索引

最新推荐文章于 2021-07-18 10:29:39 发布

robinson1988

最新推荐文章于 2021-07-18 10:29:39 发布

阅读量1.4w

点赞数

分类专栏： SQL TUNING

本文链接：https://blog.csdn.net/robinson1988/article/details/38421991

版权

SQL TUNING 专栏收录该内容

85 篇文章 48 订阅

订阅专栏

外键加索引是常识，必须牢记。本来不想写这种简单案例，但是连续遇到好几起外键不加索引导致性能问题，所以还是写一下。

一个兄弟问我 delete from Sa_Sales_Comm_Detail s where s.sales_commission_id=24240; ---删除105条数据非常慢，要跑几十秒到上百秒

这个表总数据才35万行，sales_commission_id 列有索引，执行计划也确实是走了索引。走索引返回105 条数据，不可能跑几十秒跑上百秒的。

之后我问他 select * from Sa_Sales_Comm_Detail s where s.sales_commission_id=24240; 这个跑得慢吗，他回答 0.06秒。

select * from Sa_Sales_Comm_Detail s where s.sales_commission_id=24240; ---0.06秒

delete from Sa_Sales_Comm_Detail s where s.sales_commission_id=24240; ---几十秒几百秒

遇到这种，直接做10046 trace，部分的trace文件如下

=====================
PARSING IN CURSOR #4 len=111 dep=1 uid=0 oct=3 lid=0 tim=1374414810328412 hv=4234506700 ad='99cc8678'
 select /*+ all_rows */ count(1) from "CMM"."SA_SALES_PER_SPLIT_DETAIL" where "SALES_COMMISSION_DETAIL_ID" = :1
END OF STMT
PARSE #4:c=4000,e=4619,p=0,cr=0,cu=2,mis=1,r=0,dep=1,og=1,tim=1374414810328406
EXEC #4:c=999,e=1841,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1374414810330416
FETCH #4:c=226965,e=221384,p=0,cr=6867,cu=0,mis=0,r=1,dep=1,og=1,tim=1374414810551844
=====================
PARSING IN CURSOR #2 len=106 dep=1 uid=0 oct=3 lid=0 tim=1374414810557316 hv=1936840180 ad='8ae35660'
 select /*+ all_rows */ count(1) from "CMM"."SA_SALES_COMM_REPROT" where "SALES_COMMISSION_DETAIL_ID" = :1
END OF STMT
PARSE #2:c=5000,e=5152,p=0,cr=0,cu=2,mis=1,r=0,dep=1,og=1,tim=1374414810557310
EXEC #2:c=3000,e=2081,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1374414810559571
WAIT #2: nam='db file scattered read' ela= 384 file#=5 block#=83604 blocks=5 obj#=104150 tim=1374414810560401
WAIT #2: nam='db file scattered read' ela= 154 file#=5 block#=87785 blocks=8 obj#=104150 tim=1374414810561125
WAIT #2: nam='db file scattered read' ela= 141 file#=5 block#=87802 blocks=7 obj#=104150 tim=1374414810561384
WAIT #2: nam='db file scattered read' ela= 204 file#=5 block#=94633 blocks=8 obj#=104150 tim=1374414810561704
WAIT #2: nam='db file scattered read' ela= 160 file#=5 block#=94642 blocks=7 obj#=104150 tim=1374414810562029
WAIT #2: nam='db file scattered read' ela= 167 file#=5 block#=94649 blocks=8 obj#=104150 tim=1374414810562309
WAIT #2: nam='db file scattered read' ela= 184 file#=5 block#=94674 blocks=7 obj#=104150 tim=1374414810562602
WAIT #2: nam='db file scattered read' ela= 172 file#=5 block#=94705 blocks=8 obj#=104150 tim=1374414810562882
WAIT #2: nam='db file scattered read' ela= 135 file#=5 block#=94714 blocks=7 obj#=104150 tim=1374414810563132
WAIT #2: nam='db file scattered read' ela= 162 file#=5 block#=97529 blocks=8 obj#=104150 tim=1374414810563412
WAIT #2: nam='db file scattered read' ela= 234 file#=5 block#=98314 blocks=7 obj#=104150 tim=1374414810563758
WAIT #2: nam='db file scattered read' ela= 154 file#=5 block#=99513 blocks=8 obj#=104150 tim=1374414810564008
WAIT #2: nam='db file scattered read' ela= 143 file#=5 block#=101666 blocks=7 obj#=104150 tim=1374414810564260
WAIT #2: nam='db file scattered read' ela= 166 file#=5 block#=101681 blocks=8 obj#=104150 tim=1374414810564533
WAIT #2: nam='db file scattered read' ela= 157 file#=5 block#=101690 blocks=7 obj#=104150 tim=1374414810564797
WAIT #2: nam='db file scattered read' ela= 128 file#=5 block#=101697 blocks=8 obj#=104150 tim=1374414810565025
WAIT #2: nam='db file scattered read' ela= 335 file#=5 block#=102027 blocks=16 obj#=104150 tim=1374414810565576
WAIT #2: nam='db file scattered read' ela= 355 file#=5 block#=102043 blocks=16 obj#=104150 tim=1374414810566148
WAIT #2: nam='db file scattered read' ela= 302 file#=5 block#=102059 blocks=16 obj#=104150 tim=1374414810566690
WAIT #2: nam='db file scattered read' ela= 323 file#=5 block#=102075 blocks=16 obj#=104150 tim=1374414810567221
WAIT #2: nam='db file scattered read' ela= 310 file#=5 block#=102091 blocks=16 obj#=104150 tim=1374414810567720
WAIT #2: nam='db file scattered read' ela= 270 file#=5 block#=102107 blocks=16 obj#=104150 tim=1374414810568243
WAIT #2: nam='db file scattered read' ela= 378 file#=5 block#=102123 blocks=16 obj#=104150 tim=1374414810568814
WAIT #2: nam='db file scattered read' ela= 253 file#=5 block#=102139 blocks=14 obj#=104150 tim=1374414810569252
WAIT #2: nam='db file scattered read' ela= 527 file#=5 block#=108043 blocks=16 obj#=104150 tim=1374414810570016
WAIT #2: nam='db file scattered read' ela= 309 file#=5 block#=108059 blocks=16 obj#=104150 tim=1374414810570543
WAIT #2: nam='db file scattered read' ela= 281 file#=5 block#=108075 blocks=16 obj#=104150 tim=1374414810571075
WAIT #2: nam='db file scattered read' ela= 356 file#=5 block#=108091 blocks=16 obj#=104150 tim=1374414810571658
WAIT #2: nam='db file scattered read' ela= 273 file#=5 block#=108107 blocks=16 obj#=104150 tim=1374414810572138
WAIT #2: nam='db file scattered read' ela= 381 file#=5 block#=108123 blocks=16 obj#=104150 tim=1374414810572715
WAIT #2: nam='db file scattered read' ela= 318 file#=5 block#=108139 blocks=16 obj#=104150 tim=1374414810573241
WAIT #2: nam='db file scattered read' ela= 302 file#=5 block#=108155 blocks=14 obj#=104150 tim=1374414810573745
WAIT #2: nam='db file scattered read' ela= 280 file#=5 block#=109195 blocks=16 obj#=104150 tim=1374414810574226
WAIT #2: nam='db file scattered read' ela= 362 file#=5 block#=109211 blocks=16 obj#=104150 tim=1374414810574795
WAIT #2: nam='db file scattered read' ela= 333 file#=5 block#=109227 blocks=16 obj#=104150 tim=1374414810575357
WAIT #2: nam='db file scattered read' ela= 331 file#=5 block#=109243 blocks=16 obj#=104150 tim=1374414810575904
WAIT #2: nam='db file scattered read' ela= 377 file#=5 block#=109259 blocks=16 obj#=104150 tim=1374414810576483
WAIT #2: nam='db file scattered read' ela= 349 file#=5 block#=109275 blocks=16 obj#=104150 tim=1374414810577059
WAIT #2: nam='db file scattered read' ela= 344 file#=5 block#=109291 blocks=16 obj#=104150 tim=1374414810577601
WAIT #2: nam='db file scattered read' ela= 320 file#=5 block#=109307 blocks=14 obj#=104150 tim=1374414810578133
WAIT #2: nam='db file scattered read' ela= 385 file#=7 block#=2699 blocks=16 obj#=104150 tim=1374414810578830

从trace文件里面看到，执行delete的时候，隐含的调用了

select /*+ all_rows */ count(1) from "CMM"."SA_SALES_COMM_REPROT" where "SALES_COMMISSION_DETAIL_ID" = :1

这个sql 导致了 db file scattered read(多块读) ，也就是说上面的sql 没走索引，走了全表扫描，于是让他在 SALES_COMMISSION_DETAIL_ID 列上面建立索引

建立索引之后，delete只需要0.几秒即可。

反正记住，外键上面要建立索引。