修改外來鍵為validate時需要驗證資料是否符合外來鍵約束
修改外來鍵為validate時需要驗證資料是否符合外來鍵約束，驗證如下：
1，版本：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
2，建立測試表
create table tb1116 (id number,other_col char(2000)) ;
begin
for i in 1..100000 loop
insert into tb1116 values (i,i||'a');
end loop;
end;
/
commit;
alter table tb1116 add constraint pk_tb1116 primary key(id);
create table tb1116_child (id number,other_col char(2000));
begin
for i in 1..100000 loop
insert into tb1116_child values (i,i||'a');
end loop;
end;
/
Commit;
3，測試
set timing on
SQL> select a.sid,b.spid from v$session a,v$process b
2 where a.paddr=b.addr and a.sid in(select sid from v$mystat where rownum < 2);
SID SPID
---------- ------------
735 22784
Elapsed: 00:00:00.16
SQL> alter table tb1116_child add constraint fk_tb1116_child foreign key (id) references tb1116 (id) novalidate;
Table altered.
Elapsed: 00:00:00.06
SQL> alter session set events = '10046 trace name context forever,level 12';
Session altered.
Elapsed: 00:00:00.07
SQL> alter table tb1116_child modify constraint fk_tb1116_child validate;
Table altered.
Elapsed: 00:00:13.52
SQL> SQL> alter session set events = '10046 trace name context off';
Session altered.
Elapsed: 00:00:00.00
SQL>
4，統計資訊分析：
在另外一個會話中，在執行“alter table tb1116_child modify constraint fk_tb1116_child validate;”前後分別獲取會話的統計資訊：
create table st_b
as
SELECT n.name, st.value
FROM v$sesstat st, v$statname n
WHERE n.statistic# = st.statistic#
AND n.name LIKE '%read%'
AND st.sid = 735;
create table st_c
as
SELECT n.name, st.value
FROM v$sesstat st, v$statname n
WHERE n.statistic# = st.statistic#
AND n.name LIKE '%read%'
AND st.sid = 735;
SQL> SELECT st_c.name, st_b.value, st_c.value, st_c.value - st_b.value
2 FROM st_c, st_b
3 WHERE st_c.name = st_b.name
4 and st_c.value - st_b.value>0
5 order by 4;
NAME VALUE VALUE ST_C.VALUE-ST_B.VALUE
---------------------------------------------------------------- ---------- ---------- ---------------------
rollbacks only - consistent read gets 228 229 1
data blocks consistent reads - undo records applied 228 229 1
no work - consistent read gets 37270 37621 351
physical read total multi block requests 1658 2728 1070
physical read total IO requests 2730 5717 2987
physical read IO requests 2730 5717 2987
physical reads cache prefetch 24262 39756 15494
physical reads 26880 45210 18330
physical reads cache 26880 45210 18330
cleanouts only - consistent read gets 33334 66668 33334
session logical reads 867769 1035122 167353
physical read bytes 220200960 370360320 150159360
physical read total bytes 220200960 370360320 150159360
13 rows selected
SQL> select 18330*8192 from dual;
18330*8192
----------
150159360
5，trace檔案分析：
5.1，validate實際物理讀18330，邏輯讀167340：
=====================
PARSING IN CURSOR #2 len=67 dep=0 uid=62 ct=15 lid=62 tim=1348678049686346 hv=1029529076 ad='319e0934'
alter table tb1116_child modify constraint fk_tb1116_child validate
END OF STMT
PARSE #2:c=0,e=21654,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1348678049686338
BINDS #2:
EXEC #2:c=1744109,e=13112196,p=18330,cr=167340,cu=13,mis=0,r=0,dep=0,og=1,tim=1348678062811129
WAIT #2: nam='log file sync' ela= 58845 buffer#=6713 p2=0 p3=0 obj#=146189 tim=1348678062870186
WAIT #2: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=146189 tim=1348678062870312
WAIT #2: nam='SQL*Net message from client' ela= 376 driver id=1650815232 #bytes=1 p3=0 obj#=146189 tim=1348678062870742
5.2，更改外來鍵validate時，實際還是需要進行資料驗證的，見如下SQL，
select /*+ all_rows ordered */ A.rowid, :1, :2, :3 from
"BNET"."TB1116_CHILD" A , "BNET"."TB1116" B
where( "A"."ID" is not null) and( "B"."ID" (+)= "A"."ID") and( "B"."ID" is null)
該SQL執行期間的等待事件也說明了進行了資料驗證。
SQL> SELECT owner, segment_name
2 FROM dba_extents
3 WHERE file_id = 7
4 AND 221740 BETWEEN block_id AND block_id + blocks - 1;
OWNER SEGMENT_NAME
------------------------------ ---------------------
BNET TB1116_CHILD
=====================
PARSING IN CURSOR #1 len=185 dep=1 uid=0 ct=3 lid=0 tim=1348678049807211 hv=1103103188 ad='31a2b034'
select /*+ all_rows ordered */ A.rowid, :1, :2, :3 from "BNET"."TB1116_CHILD" A , "BNET"."TB1116" B where( "A"."ID" is not null) and( "B"."ID" (+)= "A"."ID") and( "B"."ID" is null)
END OF STMT
PARSE #1:c=12001,e=3512,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1348678049807205
WAIT #1: nam='db file sequential read' ela= 11383 file#=7 block#=221723 blocks=1 obj#=146189 tim=1348678052569862
BINDS #1:
kkscoacd
Bind#0
acdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=01 csi=873 siz=32 ff=0
kxsbbbfp=b7e73c80 bln=32 avl=06 flg=05
value="BNET"
Bind#1
acdty=01 mxl=32(12) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=01 csi=873 siz=32 ff=0
kxsbbbfp=b7e73c54 bln=32 avl=12 flg=05
value="TB1116_CHILD"
Bind#2
acdty=01 mxl=32(15) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=01 csi=873 siz=32 ff=0
kxsbbbfp=b7e73c28 bln=32 avl=15 flg=05
value="FK_TB1116_CHILD"
EXEC #1:c=40002,e=2927821,p=614,cr=440,cu=1,mis=1,r=0,dep=1,og=1,tim=1348678052735246
WAIT #1: nam='db file scattered read' ela= 12669 file#=7 block#=221740 blocks=5 obj#=146189 tim=1348678052748095
WAIT #1: nam='db file sequential read' ela= 2993 file#=7 block#=221724 blocks=1 obj#=146189 tim=1348678052751253
WAIT #1: nam='db file sequential read' ela= 5964 file#=7 block#=221725 blocks=1 obj#=146189 tim=1348678052757280
WAIT #1: nam='db file scattered read' ela= 13132 file#=8 block#=142105 blocks=8 obj#=146189 tim=1348678052770790
WAIT #1: nam='db file scattered read' ela= 12636 file#=9 block#=30306 blocks=7 obj#=146189 tim=1348678052783911
WAIT #1: nam='db file scattered read' ela= 19731 file#=10 block#=22009 blocks=8 obj#=146189 tim=1348678052804125
WAIT #1: nam='db file scattered read' ela= 17063 file#=11 block#=82378 blocks=7 obj#=146189 tim=1348678052821756
WAIT #1: nam='db file scattered read' ela= 14849 file#=12 block#=165993 blocks=8 obj#=146189 tim=1348678052837052
WAIT #1: nam='db file scattered read' ela= 16521 file#=15 block#=174802 blocks=7 obj#=146189 tim=1348678052854166
WAIT #1: nam='db file scattered read' ela= 12538 file#=16 block#=137529 blocks=8 obj#=146189 tim=1348678052867150
WAIT #1: nam='db file scattered read' ela= 20571 file#=19 block#=230770 blocks=7 obj#=146189 tim=1348678052888219
WAIT #1: nam='db file scattered read' ela= 19559 file#=20 block#=237241 blocks=8 obj#=146189 tim=1348678052908320
WAIT #1: nam='db file scattered read' ela= 13110 file#=6 block#=31690 blocks=7 obj#=146189 tim=1348678052922006
WAIT #1: nam='db file scattered read' ela= 223 file#=7 block#=221745 blocks=8 obj#=146189 tim=1348678052922686
WAIT #1: nam='db file scattered read' ela= 117 file#=8 block#=142114 blocks=7 obj#=146189 tim=1348678052923350
WAIT #1: nam='db file scattered read' ela= 143 file#=9 block#=30313 blocks=8 obj#=146189 tim=1348678052923914
WAIT #1: nam='db file scattered read' ela= 16960 file#=10 block#=22282 blocks=7 obj#=146189 tim=1348678052941339
WAIT #1: nam='db file scattered read' ela= 147 file#=11 block#=82385 blocks=8 obj#=146189 tim=1348678052941976
WAIT #1: nam='db file scattered read' ela= 28915 file#=12 block#=20235 blocks=16 obj#=146189 tim=1348678052971378
WAIT #1: nam='db file sequential read' ela= 19 file#=7 block#=221726 blocks=1 obj#=146189 tim=1348678052971664
WAIT #1: nam='db file scattered read' ela= 2165 file#=12 block#=20251 blocks=16 obj#=146189 tim=1348678052974627
WAIT #1: nam='db file scattered read' ela= 330 file#=12 block#=20267 blocks=16 obj#=146189 tim=1348678052975848
WAIT #1: nam='db file scattered read' ela= 2482 file#=12 block#=20283 blocks=16 obj#=146189 tim=1348678052979200
WAIT #1: nam='db file scattered read' ela= 341 file#=12 block#=20299 blocks=16 obj#=146189 tim=1348678052980473
WAIT #1: nam='db file scattered read' ela= 394 file#=12 block#=20315 blocks=16 obj#=146189 tim=1348678052981843
WAIT #1: nam='db file scattered read' ela= 214 file#=12 block#=20331 blocks=16 obj#=146189 tim=1348678052982956
WAIT #1: nam='db file scattered read' ela= 149 file#=12 block#=20347 blocks=14 obj#=146189 tim=1348678052983974
WAIT #1: nam='db file scattered read' ela= 19517 file#=15 block#=22795 blocks=16 obj#=146189 tim=1348678053004261
WAIT #1: nam='db file scattered read' ela= 218 file#=15 block#=22811 blocks=16 obj#=146189 tim=1348678053006270
WAIT #1: nam='db file scattered read' ela= 26484 file#=15 block#=22827 blocks=16 obj#=146189 tim=1348678053033680
WAIT #1: nam='db file scattered read' ela= 231 file#=15 block#=22843 blocks=16 obj#=146189 tim=1348678053035017
WAIT #1: nam='db file sequential read' ela= 37486 file#=7 block#=221727 blocks=1 obj#=146189 tim=1348678053073306
WAIT #1: nam='db file scattered read' ela= 8806 file#=15 block#=22859 blocks=16 obj#=146189 tim=1348678053082281
WAIT #1: nam='db file scattered read' ela= 24229 file#=15 block#=22875 blocks=16 obj#=146189 tim=1348678053107541
WAIT #1: nam='db file scattered read' ela= 1728 file#=15 block#=22891 blocks=16 obj#=146189 tim=1348678053110313
WAIT #1: nam='db file sequential read' ela= 17 file#=15 block#=22907 blocks=1 obj#=146189 tim=1348678053111265
WAIT #1: nam='db file scattered read' ela= 138 file#=15 block#=22909 blocks=12 obj#=146189 tim=1348678053111580
WAIT #1: nam='db file scattered read' ela= 51434 file#=16 block#=19979 blocks=16 obj#=146189 tim=1348678053163734
WAIT #1: nam='db file scattered read' ela= 25890 file#=16 block#=19995 blocks=16 obj#=146189 tim=1348678053190783
WAIT #1: nam='db file scattered read' ela= 397 file#=16 block#=20011 blocks=16 obj#=146189 tim=1348678053192311
WAIT #1: nam='db file scattered read' ela= 24988 file#=16 block#=20027 blocks=16 obj#=146189 tim=1348678053218325
WAIT #1: nam='db file scattered read' ela= 355 file#=16 block#=20043 blocks=16 obj#=146189 tim=1348678053219708
WAIT #1: nam='db file scattered read' ela= 25448 file#=16 block#=20059 blocks=16 obj#=146189 tim=1348678053246091
WAIT #1: nam='db file scattered read' ela= 378 file#=16 block#=20075 blocks=16 obj#=146189 tim=1348678053247445
WAIT #1: nam='db file scattered read' ela= 245 file#=16 block#=20091 blocks=14 obj#=146189 tim=1348678053248612
WAIT #1: nam='db file scattered read' ela= 7260 file#=17 block#=528139 blocks=16 obj#=146189 tim=1348678053256687
WAIT #1: nam='db file sequential read' ela= 44 file#=7 block#=221728 blocks=1 obj#=146189 tim=1348678053256934
WAIT #1: nam='db file scattered read' ela= 412 file#=17 block#=528155 blocks=16 obj#=146189 tim=1348678053258225
WAIT #1: nam='db file scattered read' ela= 581 file#=17 block#=528171 blocks=16 obj#=146189 tim=1348678053259755
WAIT #1: nam='db file scattered read' ela= 7838 file#=17 block#=528187 blocks=16 obj#=146189 tim=1348678053268465
WAIT #1: nam='db file scattered read' ela= 648 file#=17 block#=528203 blocks=16 obj#=146189 tim=1348678053270120
WAIT #1: nam='db file scattered read' ela= 5467 file#=17 block#=528219 blocks=16 obj#=146189 tim=1348678053276634
WAIT #1: nam='db file scattered read' ela= 240 file#=17 block#=528235 blocks=16 obj#=146189 tim=1348678053277803
WAIT #1: nam='db file scattered read' ela= 144 file#=17 block#=528251 blocks=14 obj#=146189 tim=1348678053278964
WAIT #1: nam='db file scattered read' ela= 33228 file#=18 block#=19595 blocks=16 obj#=146189 tim=1348678053313019
WAIT #1: nam='db file scattered read' ela= 19482 file#=18 block#=19611 blocks=16 obj#=146189 tim=1348678053333527
WAIT #1: nam='db file scattered read' ela= 210 file#=18 block#=19627 blocks=16 obj#=146189 tim=1348678053334682
WAIT #1: nam='db file scattered read' ela= 19191 file#=18 block#=19643 blocks=16 obj#=146189 tim=1348678053354737
WAIT #1: nam='db file sequential read' ela= 14359 file#=8 block#=142089 blocks=1 obj#=146189 tim=1348678053369970
WAIT #1: nam='db file scattered read' ela= 202 file#=18 block#=19659 blocks=16 obj#=146189 tim=1348678053370410
WAIT #1: nam='db file scattered read' ela= 15679 file#=18 block#=19675 blocks=16 obj#=146189 tim=1348678053386974
WAIT #1: nam='db file scattered read' ela= 354 file#=18 block#=19691 blocks=16 obj#=146189 tim=1348678053388279
WAIT #1: nam='db file scattered read' ela= 246 file#=18 block#=19707 blocks=14 obj#=146189 tim=1348678053389402
WAIT #1: nam='db file scattered read' ela= 41334 file#=19 block#=14859 blocks=16 obj#=146189 tim=1348678053431503
WAIT #1: nam='db file scattered read' ela= 13901 file#=19 block#=14875 blocks=16 obj#=146189 tim=1348678053446589
WAIT #1: nam='db file scattered read' ela= 378 file#=19 block#=14891 blocks=16 obj#=146189 tim=1348678053447979
WAIT #1: nam='db file scattered read' ela= 18265 file#=19 block#=14907 blocks=16 obj#=146189 tim=1348678053467128
WAIT #1: nam='db file sequential read' ela= 13198 file#=8 block#=142090 blocks=1 obj#=146189 tim=1348678053481278
WAIT #1: nam='db file scattered read' ela= 335 file#=19 block#=14923 blocks=16 obj#=146189 tim=1348678053481824
WAIT #1: nam='db file scattered read' ela= 17073 file#=19 block#=14939 blocks=16 obj#=146189 tim=1348678053499795
WAIT #1: nam='db file scattered read' ela= 345 file#=19 block#=14955 blocks=16 obj#=146189 tim=1348678053501084
WAIT #1: nam='db file scattered read' ela= 245 file#=19 block#=14971 blocks=14 obj#=146189 tim=1348678053502254
WAIT #1: nam='db file scattered read' ela= 35961 file#=20 block#=16651 blocks=16 obj#=146189 tim=1348678053538988
WAIT #1: nam='db file scattered read' ela= 20635 file#=20 block#=16667 blocks=16 obj#=146189 tim=1348678053560640
WAIT #1: nam='db file scattered read' ela= 213 file#=20 block#=16683 blocks=16 obj#=146189 tim=1348678053561863
WAIT #1: nam='db file scattered read' ela= 19256 file#=20 block#=16699 blocks=16 obj#=146189 tim=1348678053582014
WAIT #1: nam='db file scattered read' ela= 226 file#=20 block#=16715 blocks=16 obj#=146189 tim=1348678053583345
WAIT #1: nam='db file scattered read' ela= 29838 file#=20 block#=16731 blocks=16 obj#=146189 tim=1348678053614071
WAIT #1: nam='db file scattered read' ela= 213 file#=20 block#=16747 blocks=16 obj#=146189 tim=1348678053615300
WAIT #1: nam='db file scattered read' ela= 130 file#=20 block#=16763 blocks=14 obj#=146189 tim=1348678053616329
WAIT #1: nam='db file scattered read' ela= 38578 file#=6 block#=44555 blocks=16 obj#=146189 tim=1348678053655683
WAIT #1: nam='db file sequential read' ela= 57 file#=8 block#=142091 blocks=1 obj#=146189 tim=1348678053656014
WAIT #1: nam='db file scattered read' ela= 25274 file#=6 block#=44571 blocks=16 obj#=146189 tim=1348678053682209
WAIT #1: nam='db file scattered read' ela= 217 file#=6 block#=44587 blocks=16 obj#=146189 tim=1348678053683536
WAIT #1: nam='db file scattered read' ela= 35744 file#=6 block#=44603 blocks=16 obj#=146189 tim=1348678053720207
WAIT #1: nam='db file scattered read' ela= 338 file#=6 block#=44619 blocks=16 obj#=146189 tim=1348678053721554
WAIT #1: nam='db file scattered read' ela= 47970 file#=6 block#=44635 blocks=16 obj#=146189 tim=1348678053770487
WAIT #1: nam='db file scattered read' ela= 344 file#=6 block#=44651 blocks=16 obj#=146189 tim=1348678053771942
WAIT #1: nam='db file scattered read' ela= 244 file#=6 block#=44667 blocks=14 obj#=146189 tim=1348678053773138
WAIT #1: nam='db file scattered read' ela= 113363 file#=7 block#=80907 blocks=16 obj#=146189 tim=1348678053887392
WAIT #1: nam='db file scattered read' ela= 30720 file#=7 block#=80923 blocks=16 obj#=146189 tim=1348678053919261
WAIT #1: nam='db file scattered read' ela= 330 file#=7 block#=80939 blocks=16 obj#=146189 tim=1348678053920635
WAIT #1: nam='db file scattered read' ela= 30267 file#=7 block#=80955 blocks=16 obj#=146189 tim=1348678053951872
WAIT #1: nam='db file sequential read' ela= 68 file#=8 block#=142092 blocks=1 obj#=146189 tim=1348678053952902
WAIT #1: nam='db file scattered read' ela= 323 file#=7 block#=80971 blocks=16 obj#=146189 tim=1348678053953411
WAIT #1: nam='db file scattered read' ela= 23543 file#=7 block#=80987 blocks=16 obj#=146189 tim=1348678053977930
WAIT #1: nam='db file scattered read' ela= 226 file#=7 block#=81003 blocks=16 obj#=146189 tim=1348678053979156
WAIT #1: nam='db file scattered read' ela= 136 file#=7 block#=81019 blocks=14 obj#=146189 tim=1348678053980186
WAIT #1: nam='db file scattered read' ela= 46278 file#=8 block#=70155 blocks=16 obj#=146189 tim=1348678054027248
WAIT #1: nam='db file scattered read' ela= 236 file#=8 block#=70171 blocks=16 obj#=146189 tim=1348678054028450
WAIT #1: nam='db file scattered read' ela= 18797 file#=8 block#=70187 blocks=16 obj#=146189 tim=1348678054048144
WAIT #1: nam='db file scattered read' ela= 220 file#=8 block#=70203 blocks=16 obj#=146189 tim=1348678054049287
WAIT #1: nam='db file scattered read' ela= 18324 file#=8 block#=70219 blocks=16 obj#=146189 tim=1348678054068467
WAIT #1: nam='db file sequential read' ela= 19 file#=8 block#=142093 blocks=1 obj#=146189 tim=1348678054068655
WAIT #1: nam='db file scattered read' ela= 225 file#=8 block#=70235 blocks=16 obj#=146189 tim=1348678054069714
WAIT #1: nam='db file scattered read' ela= 11372 file#=8 block#=70251 blocks=16 obj#=146189 tim=1348678054081945
WAIT #1: nam='db file scattered read' ela= 161 file#=8 block#=70267 blocks=14 obj#=146189 tim=1348678054083005
WAIT #1: nam='db file scattered read' ela= 54398 file#=9 block#=71179 blocks=16 obj#=146189 tim=1348678054138262
WAIT #1: nam='db file scattered read' ela= 8205 file#=9 block#=71195 blocks=16 obj#=146189 tim=1348678054148278
WAIT #1: nam='db file scattered read' ela= 457 file#=9 block#=71211 blocks=16 obj#=146189 tim=1348678054149787
WAIT #1: nam='db file scattered read' ela= 30170 file#=9 block#=71227 blocks=16 obj#=146189 tim=1348678054180850
WAIT #1: nam='db file scattered read' ela= 348 file#=9 block#=71243 blocks=16 obj#=146189 tim=1348678054182186
WAIT #1: nam='db file scattered read' ela= 18012 file#=9 block#=71259 blocks=16 obj#=146189 tim=1348678054201078
WAIT #1: nam='db file scattered read' ela= 340 file#=9 block#=71275 blocks=16 obj#=146189 tim=1348678054202355
WAIT #1: nam='db file scattered read' ela= 248 file#=9 block#=71291 blocks=14 obj#=146189 tim=1348678054203480
WAIT #1: nam='db file scattered read' ela= 41915 file#=10 block#=72203 blocks=16 obj#=146189 tim=1348678054246155
WAIT #1: nam='db file sequential read' ela= 22 file#=8 block#=142094 blocks=1 obj#=146189 tim=1348678054246415
WAIT #1: nam='db file scattered read' ela= 354 file#=10 block#=72219 blocks=16 obj#=146189 tim=1348678054247837
WAIT #1: nam='db file scattered read' ela= 18302 file#=10 block#=72235 blocks=16 obj#=146189 tim=1348678054267047
WAIT #1: nam='db file scattered read' ela= 322 file#=10 block#=72251 blocks=16 obj#=146189 tim=1348678054268268
WAIT #1: nam='db file scattered read' ela= 12385 file#=10 block#=72267 blocks=16 obj#=146189 tim=1348678054281511
WAIT #1: nam='db file scattered read' ela= 276 file#=10 block#=72283 blocks=16 obj#=146189 tim=1348678054282716
WAIT #1: nam='db file scattered read' ela= 11433 file#=10 block#=72299 blocks=16 obj#=146189 tim=1348678054295011
WAIT #1: nam='db file scattered read' ela= 137 file#=10 block#=72315 blocks=14 obj#=146189 tim=1348678054296133
WAIT #1: nam='db file scattered read' ela= 56297 file#=11 block#=21387 blocks=16 obj#=146189 tim=1348678054353198
WAIT #1: nam='db file scattered read' ela= 20210 file#=11 block#=21403 blocks=16 obj#=146189 tim=1348678054374403
WAIT #1: nam='db file scattered read' ela= 214 file#=11 block#=21419 blocks=16 obj#=146189 tim=1348678054375573
WAIT #1: nam='db file scattered read' ela= 18727 file#=11 block#=21435 blocks=16 obj#=146189 tim=1348678054395161
WAIT #1: nam='db file sequential read' ela= 19 file#=8 block#=142095 blocks=1 obj#=146189 tim=1348678054396062
WAIT #1: nam='db file scattered read' ela= 1398 file#=11 block#=21451 blocks=16 obj#=146189 tim=1348678054397573
WAIT #1: nam='db file scattered read' ela= 17644 file#=11 block#=21467 blocks=16 obj#=146189 tim=1348678054416107
WAIT #1: nam='db file scattered read' ela= 272 file#=11 block#=21483 blocks=16 obj#=146189 tim=1348678054417367
WAIT #1: nam='db file scattered read' ela= 245 file#=11 block#=21499 blocks=14 obj#=146189 tim=1348678054418521
WAIT #1: nam='db file scattered read' ela= 19843 file#=12 block#=20363 blocks=16 obj#=146189 tim=1348678054439181
WAIT #1: nam='db file scattered read' ela= 353 file#=12 block#=20379 blocks=16 obj#=146189 tim=1348678054440553
WAIT #1: nam='db file scattered read' ela= 19098 file#=12 block#=20395 blocks=16 obj#=146189 tim=1348678054460576
WAIT #1: nam='db file scattered read' ela= 340 file#=12 block#=20411 blocks=16 obj#=146189 tim=1348678054461832
WAIT #1: nam='db file scattered read' ela= 19419 file#=12 block#=20427 blocks=16 obj#=146189 tim=1348678054482124
WAIT #1: nam='db file scattered read' ela= 393 file#=12 block#=20443 blocks=16 obj#=146189 tim=1348678054483535
WAIT #1: nam='db file scattered read' ela= 45694 file#=12 block#=20459 blocks=16 obj#=146189 tim=1348678054530110
WAIT #1: nam='db file scattered read' ela= 5176 file#=12 block#=20475 blocks=14 obj#=146189 tim=1348678054536323
WAIT #1: nam='db file scattered read' ela= 83307 file#=15 block#=23691 blocks=16 obj#=146189 tim=1348678054620432
WAIT #1: nam='db file sequential read' ela= 34 file#=8 block#=142096 blocks=1 obj#=146189 tim=1348678054620775
WAIT #1: nam='db file scattered read' ela= 335 file#=15 block#=23707 blocks=16 obj#=146189 tim=1348678054621977
...
WAIT #1: nam='db file sequential read' ela= 59 file#=12 block#=20195 blocks=1 obj#=146189 tim=1348678062749440
WAIT #1: nam='db file sequential read' ela= 89 file#=12 block#=20196 blocks=1 obj#=146189 tim=1348678062767557
WAIT #1: nam='db file sequential read' ela= 28 file#=12 block#=20197 blocks=1 obj#=146189 tim=1348678062768040
FETCH #1:c=1612101,e=10048390,p=17716,cr=166843,cu=0,mis=0,r=0,dep=1,og=1,tim=1348678062783701
STAT #1 id=1 cnt=0 pid=0 pos=1 bj=0 p='NESTED LOOPS ANTI (cr=166843 pr=17716 pw=0 time=10048411 us)'
STAT #1 id=2 cnt=100000 pid=1 pos=1 bj=146191 p='TABLE ACCESS FULL TB1116_CHILD (cr=66841 pr=17507 pw=0 time=12912789 us)'
STAT #1 id=3 cnt=100000 pid=1 pos=2 bj=146190 p='INDEX UNIQUE SCAN PK_TB1116 (cr=100002 pr=209 pw=0 time=1606348 us)'
=====================
PARSING IN CURSOR #3 len=528 dep=2 uid=0 ct=3 lid=0 tim=1348678049837514 hv=3472836316 ad='31a2a914'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN 1 ELSE 0 END),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("A") FULL("A") NO_PARALLEL_INDEX("A") */ 1 AS C1, CASE WHEN "A"."ID" IS NOT NULL THEN 1 ELSE 0 END AS C2, "A"."ID" AS C3 FROM "BNET"."TB1116_CHILD" SAMPLE BLOCK (0.187740 , 1) SEED (1) "A") SAMPLESUB
END OF STMT
PARSE #3:c=4000,e=5130,p=0,cr=2,cu=0,mis=1,r=0,dep=2,og=1,tim=1348678049837505
BINDS #3:
EXEC #3:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=1348678049837690
WAIT #3: nam='db file sequential read' ela= 12817 file#=8 block#=142107 blocks=1 obj#=146189 tim=1348678049850640
WAIT #3: nam='db file sequential read' ela= 12746 file#=10 block#=22281 blocks=1 obj#=146189 tim=1348678049863552
WAIT #3: nam='db file parallel read' ela= 2422825 files=13 blocks=125 requests=125 obj#=146189 tim=1348678052286812
WAIT #3: nam='db file scattered read' ela= 19673 file#=16 block#=20682 blocks=16 obj#=146189 tim=1348678052548504
WAIT #3: nam='db file sequential read' ela= 34 file#=16 block#=20852 blocks=1 obj#=146189 tim=1348678052552929
WAIT #3: nam='db file sequential read' ela= 3226 file#=16 block#=21243 blocks=1 obj#=146189 tim=1348678052556227
WAIT #3: nam='db file sequential read' ela= 31 file#=19 block#=15636 blocks=1 obj#=146189 tim=1348678052556525
WAIT #3: nam='db file sequential read' ela= 18 file#=6 block#=201047 blocks=1 obj#=146189 tim=1348678052556711
FETCH #3:c=8000,e=2720371,p=273,cr=359,cu=1,mis=0,r=1,dep=2,og=1,tim=1348678052558087
STAT #3 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT GROUP BY (cr=359 pr=273 pw=0 time=2720374 us)'
STAT #3 id=2 cnt=189 pid=1 pos=1 bj=146191 p='TABLE ACCESS SAMPLE TB1116_CHILD (cr=359 pr=273 pw=0 time=13397 us)'
BINDS #3:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7e7328c bln=22 avl=04 flg=05
value=146189
Bind#1
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7e73268 bln=24 avl=03 flg=05
value=1001
=====================
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/18922393/viewspace-749549/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Javaweb-約束-外來鍵約束JavaWeb
- Oracle定義約束 外來鍵約束Oracle
- 【實驗】【外來鍵】小議外來鍵約束對應用程式的影響
- SQL的主鍵和外來鍵約束SQL
- 教你mysql如何增加外來鍵約束MySql
- oracle外來鍵約束的總結Oracle
- MySQL禁用恢復外來鍵約束MySql
- mysql資料庫匯入外來鍵約束問題MySql資料庫
- mysql~資料完整性考慮~外來鍵約束MySql
- 資料庫約束 主鍵-唯一性-Check-外來鍵資料庫
- mysql 刪除老是報外來鍵約束MySql
- 資料完整性約束：主鍵、外來鍵、各種約束的建立刪除語句
- [資料庫]資料庫中為什麼不推薦使用外來鍵約束資料庫
- AppBoxFuture(七): 分散式外來鍵約束APP分散式
- mysql不能新增外來鍵約束怎麼辦MySql
- SQL外來鍵約束的含義及建立SQL
- mysql啟動和關閉外來鍵約束MySql
- Oracle外來鍵約束中NULL的處理OracleNull
- 詳解外來鍵約束（foreign key）
- (轉)oracle資料庫中所有外來鍵約束失效SQL語句Oracle資料庫SQL
- 分散式資料庫環境中，外來鍵約束的問題？？分散式資料庫
- PostgreSQL11preview-支援陣列外來鍵約束SQLView陣列
- 外來鍵約束drop table cascade constraintsAI
- SQL SERVER中找出拙劣的約束，索引，外來鍵SQLServer索引
- Mysql關於資料庫是否應該使用外來鍵約束詳解說明創磅MySql資料庫
- 遷移後處理外來鍵約束的問題
- 【Foreign Key】Oracle外來鍵約束三種刪除行為Oracle
- 父表修改與外來鍵的關係（主鍵DML與外來鍵的關係）
- 小議Oracle外來鍵約束脩改行為（七）Oracle
- 小議Oracle外來鍵約束脩改行為（六）Oracle
- 小議Oracle外來鍵約束脩改行為（五）Oracle
- 小議Oracle外來鍵約束脩改行為（四）Oracle
- 小議Oracle外來鍵約束脩改行為（三）Oracle
- 小議Oracle外來鍵約束脩改行為（二）Oracle
- 小議Oracle外來鍵約束脩改行為（一）Oracle
- 查詢外來鍵約束、子表欄位等資訊的SQLSQL
- 資料庫外來鍵,用是不用?資料庫
- 查詢(看)表的主鍵、外來鍵、唯一性約束和索引索引