[20200906][转载]FK on delete.txt

[20200906][转载]FK on delete.txt --//链接:https://jonathanlewis.wordpress.com/2020/08/28/fk-on-delete/ --//里面提到删除主键记录会探查外键的索引相关记录,即使外键表相关记录已经删除. --//我仅仅重复测试例子: 1.环境: SYS@book> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 2.建立测试表: create table child as with generator as (         select                 rownum id         from dual         connect by                 level <= 1e4    -- > comment to avoid wordpress format issue ) select         trunc((rownum-1)/1200)  n1,         lpad('x',80)            idx_padding,         lpad(rownum,8,'0')      small_vc from         generator       v1,         generator       v2 where         rownum <= 6e4           -- > comment to avoid wordpress format issue ; create index child_ix on child(n1, idx_padding) pctfree 95; create table parent as select         id,         lpad(rownum,8,'0')      small_vc,         lpad('x',80)            padding from    (         select                 distinct n1             id         from                 child         ) ; alter table parent add constraint par_pk primary key(id); alter table child add constraint chi_fk_par foreign key(n1) references parent; begin         dbms_stats.gather_table_stats(                 ownname          => user,                 tabname          =>'PARENT',                 method_opt       => 'for all columns size 1'         );         dbms_stats.gather_table_stats(                 ownname          => user,                 tabname          =>'CHILD',                 method_opt       => 'for all columns size 1'         ); end; / select  index_name, num_rows, distinct_keys, leaf_blocks, avg_leaf_blocks_per_key from    user_indexes where   TABLE_NAME in ('PARENT','CHILD') --//原链接这里有点小错误. INDEX_NAME             NUM_ROWS DISTINCT_KEYS LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY -------------------- ---------- ------------- ----------- ----------------------- CHILD_IX                  59649            51       29824                     584 PAR_PK                       50            50           1                       1 select  object_id, object_name from    user_objects where object_NAME in ('CHILD_IX','CHILD','PARENT','PAR_PK') order by object_id;  OBJECT_ID OBJECT_NAME ---------- --------------------      28870 CHILD      28871 CHILD_IX      28872 PARENT      28873 PAR_PK delete from child where n1 = 10; commit; delete from child where n1 = 20; commit; delete from child where n1 = 30; commit; delete from child where n1 = 40; commit; execute dbms_stats.gather_table_stats(user, 'child', cascade=>true) alter system flush buffer_cache; --//继续: alter session set events '10046 trace name context forever, level 8'; alter session set tracefile_identifier = 'del'; delete from parent where id = 10; commit; --//delete from parent where id = 40; --//commit; alter session set tracefile_identifier = ''; alter session set events '10046 trace name context off'; --//查看转储: ===================== PARSING IN CURSOR #840174784 len=32 dep=0 uid=81 oct=7 lid=81 tim=3157568290 hv=351885383 ad='7ff1373af40' sqlid='0u6t174agkq27' delete from parent where id = 10 END OF STMT PARSE #840174784:c=93600,e=416974,p=38,cr=262,cu=0,mis=1,r=0,dep=0,og=1,plh=3366423708,tim=3157568288 WAIT #840174784: nam='db file scattered read' ela= 9075 file#=11 block#=2248 blocks=8 obj#=28873 tim=3157578375 WAIT #840174784: nam='db file scattered read' ela= 744 file#=11 block#=2240 blocks=8 obj#=28872 tim=3157579555 WAIT #840174784: nam='db file sequential read' ela= 12350 file#=10 block#=5634 blocks=1 obj#=0 tim=3157592190 WAIT #840174784: nam='db file scattered read' ela= 16816 file#=11 block#=1064 blocks=8 obj#=28871 tim=3157609406 WAIT #840174784: nam='db file sequential read' ela= 9677 file#=11 block#=9502 blocks=1 obj#=28871 tim=3157627909 WAIT #840174784: nam='db file sequential read' ela= 6342 file#=11 block#=8560 blocks=1 obj#=28871 tim=3157634511 WAIT #840174784: nam='db file sequential read' ela= 4953 file#=11 block#=8483 blocks=1 obj#=28871 tim=3157639687 WAIT #840174784: nam='db file sequential read' ela= 371 file#=11 block#=8484 blocks=1 obj#=28871 tim=3157649320 WAIT #840174784: nam='db file sequential read' ela= 575 file#=11 block#=8485 blocks=1 obj#=28871 tim=3157650202 WAIT #840174784: nam='db file sequential read' ela= 325 file#=11 block#=8486 blocks=1 obj#=28871 tim=3157650769 WAIT #840174784: nam='db file sequential read' ela= 388 file#=11 block#=8487 blocks=1 obj#=28871 tim=3157651507 WAIT #840174784: nam='db file sequential read' ela= 386 file#=11 block#=8488 blocks=1 obj#=28871 tim=3157652252 --//可以发现大量的db file sequential read.对象是obj#=28871.也就是CHILD_IX索引. .... WAIT #840174784: nam='db file sequential read' ela= 291 file#=11 block#=9095 blocks=1 obj#=28871 tim=3158100146 WAIT #840174784: nam='db file sequential read' ela= 294 file#=11 block#=9096 blocks=1 obj#=28871 tim=3158100578 WAIT #840174784: nam='db file sequential read' ela= 320 file#=11 block#=9097 blocks=1 obj#=28871 tim=3158101016 WAIT #840174784: nam='db file sequential read' ela= 285 file#=11 block#=9098 blocks=1 obj#=28871 tim=3158101434 WAIT #840174784: nam='db file sequential read' ela= 279 file#=11 block#=9099 blocks=1 obj#=28871 tim=3158101904 WAIT #840174784: nam='db file sequential read' ela= 270 file#=11 block#=9100 blocks=1 obj#=28871 tim=3158102315 EXEC #840174784:c=93601,e=533421,p=628,cr=1,cu=609,mis=0,r=1,dep=0,og=1,plh=3366423708,tim=3158102458 STAT #840174784 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE  PARENT (cr=1 pr=628 pw=0 str=1 time=533263 us)' STAT #840174784 id=2 cnt=1 pid=1 pos=1 obj=28873 op='INDEX UNIQUE SCAN PAR_PK (cr=1 pr=8 pw=0 str=1 time=9546 us cost=0 size=3 card=1)' WAIT #840174784: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=28871 tim=3158103205 WAIT #840174784: nam='SQL*Net message from client' ela= 479 driver id=1413697536 #bytes=1 p3=0 obj#=28871 tim=3158103769 CLOSE #840174784:c=0,e=11,dep=0,type=0,tim=3158103890 ===================== PARSING IN CURSOR #836677696 len=6 dep=0 uid=81 oct=44 lid=81 tim=3158104194 hv=3480936638 ad='0' sqlid='23wm3kz7rps5y' commit END OF STMT --//tkprof: SQL ID: 0u6t174agkq27 Plan Hash: 3366423708 delete from parent where  id = 10 call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.01          0          0          0           0 Execute      1      0.09       0.53        628          1        609           1 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        2      0.09       0.54        628          1        609           1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 81   Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------          0          0          0  DELETE  PARENT (cr=1 pr=628 pw=0 time=533263 us starts=1)          1          1          1   INDEX UNIQUE SCAN PAR_PK (cr=1 pr=8 pw=0 time=9546 us starts=1 cost=0 size=3 card=1)(object id 28873) Elapsed times include waiting on following events:   Event waited on                             Times   Max. Wait  Total Waited   ----------------------------------------   Waited  ----------  ------------   db file scattered read                          3        0.01          0.02   db file sequential read                       604        0.02          0.38   SQL*Net message to client                       1        0.00          0.00   SQL*Net message from client                     1        0.00          0.00 ******************************************************************************** --//存在604个db file sequential read. --//继续测试删除其它存在子键的情况. SCOTT@test01p> delete from parent where id = 11; delete from parent where id = 11 * ERROR at line 1: ORA-02292: integrity constraint (SCOTT.CHI_FK_PAR) violated - child record found