[20200906][轉載]FK on delete.txt
[20200906][轉載]FK on delete.txt
--//連結:
--//裡面提到刪除主鍵記錄會探查外來鍵的索引相關記錄,即使外來鍵表相關記錄已經刪除.
--//我僅僅重複測試例子:
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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2717457/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20190825]Join View and delete.txtViewdelete
- 如何批量使外來鍵(FK)失效
- 使用PL/SQL找到兩個表中的相似值FKSQL
- 【fk_index】外來鍵中有無索引的區別Index索引
- [20120514]Invisible Indexes and FK問題.txtIndex
- imp 匯入遇到 FK (Foreign Key) 導致錯誤處理
- 【轉載】ElasticsearchElasticsearch
- session轉載Session
- KPI(轉載)KPI
- JIT(轉載)
- 轉載方法
- 彼得反轉原理(轉載)
- 轉載快取快取
- 轉載oracle awrOracle
- 轉載資料
- 轉載 Java容器Java
- RFM模型(轉載)模型
- SERVQUAL模型(轉載)模型
- SIPOC模型(轉載)模型
- 流程圖(轉載)流程圖
- DAGMAR模式(轉載)模式
- ALIEDIM模型(轉載)模型
- 冰山模型(轉載)模型
- GROW模型(轉載)模型
- QQTC模型(轉載)QT模型
- EBK模式(轉載)模式
- KANO模型(轉載)模型
- SEM模型(轉載)模型
- TOFA模型(轉載)模型
- 希斯模型(轉載)模型
- 排序法(轉載)排序
- SWOT分析 (轉載)
- BSV模型(轉載)模型
- MM模型(轉載)模型
- RACI模型(轉載)模型
- SAGE模型(轉載)模型
- 暗箱模式(轉載)模式
- 彼得原理(轉載)