Once again, the Oracle foreign key must be indexed

Foreign key indexing is common sense and must be kept in mind. I did not want to write this simple case, but encountered several consecutive foreign keys without indexing caused performance problems, so write it.

A brother asked me to delete from Sa_sales_comm_detail s where s.sales_commission_id=24240; ---Delete 105 data is very slow, run dozens of seconds to hundreds of seconds

The total data for this table is only 350,000 rows, sales_commission_id is indexed, and the execution plan is indeed indexed. Walking index returns 105 data, it is impossible to run a few 10 seconds running hundreds of seconds.

Then I asked him select * from Sa_sales_comm_detail s where s.sales_commission_id=24240; Does this run slowly, he answers for 0.06 seconds.

SELECT * from Sa_sales_comm_detail s where s.sales_commission_id=24240; ---0.06 seconds.

Delete from Sa_sales_comm_detail s where s.sales_commission_id=24240; ---Dozens of seconds, hundreds of seconds.

Encountering this, directly doing 10046 Trace, part of the trace file is as follows

===================== 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#=104150Wait #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= file#=5 block#=87802 blocks=7 obj#=104150 tim=1374414810561384 wait #2: nam= ' db file sc attered 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 wait #2: tim=1374414810562029 ' db file nam= read ' scattered ela= f Ile#=5 block#=94649 blocks=8 obj#=104150 tim=1374414810562309 wait #2: nam= ' db file scattered read ' ela= 184 #=94674 blocks=7 obj#=104150 tim=1374414810562602 wait #2: nam= ' db file scattered read ' ela= 172 file#=5 block#=94705 Ks=8 obj#=104150 tim=1374414810562882 wait #2: nam= ' db file scattered read ' ela= 135 file#=5 block#=94714 blocks=7 4150 tim=1374414810563132 wait #2: nam= ' db file scattered read ' ela= 162 file#=5 block#=97529 blocks=8 obj#=104150 4414810563412 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 re Ad ' ela= 143 file#=5 block#=101666 blocks=7 obj#=104150 tim=1374414810564260 wait #2: nam= ' db file scattered read ' ela= 16 6 file#=5 block#=101681 blocks=8 obj#=104150 tim=1374414810564533 wait #2: nam= ' db file scattered read ' ela= 157 file#=5 b lock#=101690 blocks=7 obj#=104150 tim=1374414810564797 wait #2: nam= ' db file scattered read ' ela= 128 file#=5 block#=10169  7 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 tim=1374414810566148 wait #2: nam= ' db file scattered read ' ela= 302 file#=5 block#=102059 blocks=16 obj#=104150 4414810566690 wait #2: Nam= ' db file scattered read ' ela= 323 file#=5 block#=102075 blocks=16 obj#=104150 tim=1374414810567221 wait #2: nam= ' db fil E scattered read ' ela= 310 file#=5 block#=102091 blocks=16 obj#=104150 tim=1374414810567720 wait #2: nam= ' db file Scattere D read ' ela= 270 file#=5 block#=102107 blocks=16 obj#=104150 tim=1374414810568243 wait #2: nam= ' db file scattered read ' El A= 378 file#=5 block#=102123 blocks=16 obj#=104150 wait #2: tim=1374414810568814 ' db file nam= read ' scattered ela= E#=5 block#=102139 blocks=14 obj#=104150 tim=1374414810569252 wait #2: nam= ' db file scattered read ' ela= 527 #=108043 blocks=16 obj#=104150 tim=1374414810570016 wait #2: nam= ' db file scattered read ' ela= 309 file#=5 block#=108059 b locks=16 obj#=104150 tim=1374414810570543 wait #2: nam= ' db file scattered read ' ela= 281 file#=5 block#=108075 blocks=16 o  bj#=104150 tim=1374414810571075 wait #2: nam= ' db file scattered read ' ela= 356 file#=5 block#=108091 blocks=16 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 =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 cks=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 #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= file#=5 block#=109307 blocks=14 obj#=104150 tim=1374414810578133 wait #2: nam= ' db file sc attered read ' ela= 385 file#=7 block#=2699 blocks=16 obj#=104150 tim=1374414810578830

See from the trace file, when the delete is executed, the implied invocation of the

Select/*+ all_rows */COUNT (1) from "CMM". " Sa_sales_comm_reprot "where" sales_commission_detail_id =: 1

This SQL led to db file scattered read (multiple-read), which means that the above SQL was not indexed, took a full table scan, and let him index the sales_commission_detail_id column

After indexing, delete takes only 0 seconds.

Anyway remember that the foreign key above is indexed.