[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