MERGE statement internals
- From: Edgar Chupit <chupit@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 8 Oct 2004 22:24:31 +0300
Dear List,
I'm facing strange oddity when tracing MERGE statements. For each
MERGE statement I get one parse error of the "strange" query from
dual. I would like to know why does Oracle needs to query dual and is
it a bug that this query is incorrect.
The merge statement is like this:
merge /* */ into t1 d
using ( select 1 x from dual union all select 2 x from dual union all
select 3 x from dual ) s
on (d.id = s.x)
when
matched then
update set d.f = null
when not matched then
insert (id,f) values(s.x,1)
but it can be any merge statement, and that interesting query from
dual for this statement looks like this:
SELECT 1 FROM DUAL WHERE d.id = s.
Naturally that this query will return parse error because it is
constructed incorrectly, but then why does Oracle tries to parse it?
Here is an raw trace output (I've trimmed some unnecessary lines):
Oracle Database 10g Release 10.1.0.3.0 - Production
System name: Linux
*** MODULE NAME:(SQL*Plus) 2004-10-08 21:45:42.413
=====================
PARSE ERROR #8:len=35 dep=1 uid=0 oct=3 lid=0 tim=1071544084388521 err=904
SELECT 1 FROM DUAL WHERE d.id = s.
=====================
PARSING IN CURSOR #13 len=493 dep=1 uid=0 oct=3 lid=0
tim=1071544084392090 hv=2584065658 ad='78f60b64'
select
t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag,
0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,ts.logicalread
from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
END OF STMT
PARSE #13:c=0,e=228,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084392076
EXEC #13:c=0,e=190,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084393044
FETCH #13:c=0,e=321,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=1071544084393485
=====================
PARSING IN CURSOR #8 len=773 dep=1 uid=0 oct=3 lid=0
tim=1071544084394233 hv=2885603983 ad='78f6bb48'
select
i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,ist.logicalread
from ind$ i, ind_stats$ ist, (select enabled, min(cols)
unicols,min(to_number(bitand(defer,1)))
deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where
obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+)
and i.obj# = ist.obj#(+) and i.bo#=:1
END OF STMT
PARSE #8:c=10000,e=300,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084394214
EXEC #8:c=0,e=226,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084395217
FETCH #8:c=0,e=574,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084395953
=====================
PARSING IN CURSOR #22 len=348 dep=1 uid=0 oct=3 lid=0
tim=1071544084396710 hv=2512561537 ad='78f92660'
select
name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property,
nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from
col$ where obj#=:1 order by intcol#
END OF STMT
PARSE #22:c=0,e=315,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084396690
EXEC #22:c=0,e=224,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084397506
FETCH #22:c=0,e=269,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1071544084397919
FETCH #22:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1071544084398065
FETCH #22:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084398183
=====================
PARSING IN CURSOR #23 len=69 dep=1 uid=0 oct=3 lid=0
tim=1071544084399008 hv=1471956217 ad='78f376dc'
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1
END OF STMT
PARSE #23:c=0,e=498,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084398991
EXEC #23:c=0,e=142,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084399547
FETCH #23:c=0,e=131,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084399786
=====================
PARSING IN CURSOR #24 len=146 dep=1 uid=0 oct=3 lid=0
tim=1071544084400411 hv=2107929772 ad='78f832ac'
select
con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0)
from cdef$ where obj#=:1
END OF STMT
PARSE #24:c=0,e=443,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084400397
EXEC #24:c=0,e=169,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084401169
FETCH #24:c=0,e=63,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084401350
=====================
PARSING IN CURSOR #25 len=169 dep=1 uid=0 oct=3 lid=0
tim=1071544084403457 hv=1173719687 ad='78f80ff4'
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from
objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
grantee# order by col#, grantee#
END OF STMT
PARSE #25:c=0,e=459,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084403439
EXEC #25:c=10000,e=209,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084404073
FETCH #25:c=0,e=146,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084404349
=====================
PARSING IN CURSOR #26 len=151 dep=1 uid=0 oct=3 lid=0
tim=1071544084405009 hv=4139184264 ad='78f78e24'
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from
objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order
by grantee#
END OF STMT
PARSE #26:c=0,e=430,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084404996
EXEC #26:c=0,e=176,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084405585
FETCH #26:c=0,e=106,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084405812
=====================
PARSING IN CURSOR #27 len=175 dep=1 uid=0 oct=3 lid=0
tim=1071544084406796 hv=1729330152 ad='78ffcb2c'
select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled from
obj$ o,user$ u,trigger$ t where t.baseobject=:1 and t.obj#=o.obj# and
o.owner#=u.user# order by o.obj#
END OF STMT
PARSE #27:c=0,e=278,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084406778
EXEC #27:c=0,e=244,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084407317
FETCH #27:c=0,e=114,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=1071544084407512
STAT #27 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=1 pr=0
pw=0 time=197 us)'
STAT #27 id=2 cnt=0 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=1 pr=0
pw=0 time=127 us)'
STAT #27 id=3 cnt=0 pid=2 pos=1 obj=0 op='NESTED LOOPS (cr=1 pr=0
pw=0 time=121 us)'
STAT #27 id=4 cnt=0 pid=3 pos=1 obj=84 op='TABLE ACCESS BY INDEX ROWID
TRIGGER$ (cr=1 pr=0 pw=0 time=109 us)'
STAT #27 id=5 cnt=0 pid=4 pos=1 obj=128 op='INDEX RANGE SCAN
I_TRIGGER1 (cr=1 pr=0 pw=0 time=76 us)'
STAT #27 id=6 cnt=0 pid=3 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID
OBJ$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #27 id=7 cnt=0 pid=6 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1
(cr=0 pr=0 pw=0 time=0 us)'
STAT #27 id=8 cnt=0 pid=2 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$
(cr=0 pr=0 pw=0 time=0 us)'
STAT #27 id=9 cnt=0 pid=8 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER#
(cr=0 pr=0 pw=0 time=0 us)'
=====================
PARSING IN CURSOR #27 len=210 dep=1 uid=0 oct=3 lid=0
tim=1071544084410679 hv=864012087 ad='78d007b8'
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1
and intcol#=:2
END OF STMT
PARSE #27:c=0,e=219,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=1071544084410656
EXEC #27:c=0,e=158,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=1071544084411411
FETCH #27:c=0,e=147,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=3,tim=1071544084411674
EXEC #27:c=0,e=154,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=1071544084412609
FETCH #27:c=0,e=141,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=3,tim=1071544084412906
EXEC #27:c=0,e=122,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=1071544084413519
FETCH #27:c=10000,e=79,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=3,tim=1071544084413720
=====================
PARSING IN CURSOR #20 len=255 dep=0 uid=63 oct=189 lid=63
tim=1071544084417815 hv=3092374566 ad='730b3570'
merge /* */ into t1 d
using ( select 1 x from dual union all select 2 x from dual union
all select 3 x from dual ) s
on (d.id = s.x)
when
matched then
update set d.f = null
when not matched then
insert (id,f) values(s.x,1)
END OF STMT
PARSE
#20:c=30000,e=30995,p=0,cr=27,cu=0,mis=1,r=0,dep=0,og=1,tim=1071544084417792
EXEC #20:c=0,e=4460,p=0,cr=8,cu=7,mis=0,r=3,dep=0,og=1,tim=1071544084422793
WAIT #20: nam='SQL*Net message to client' ela= 6 p1=1413697536 p2=1 p3=0
WAIT #20: nam='SQL*Net message from client' ela= 1047205 p1=1413697536 p2=1 p3=0
XCTEND rlbk=0, rd_only=0
WAIT #0: nam='log file sync' ela= 5 p1=124 p2=0 p3=0
WAIT #0: nam='log file sync' ela= 8267 p1=124 p2=0 p3=0
STAT #20 id=1 cnt=2 pid=0 pos=1 obj=0 op='MERGE (cr=8 pr=0 pw=0 time=4173 us)'
STAT #20 id=2 cnt=3 pid=1 pos=1 obj=0 op='VIEW (cr=7 pr=0 pw=0 time=3178 us)'
STAT #20 id=3 cnt=3 pid=2 pos=1 obj=0 op='HASH JOIN OUTER (cr=7 pr=0
pw=0 time=3160 us)'
STAT #20 id=4 cnt=3 pid=3 pos=1 obj=0 op='VIEW (cr=0 pr=0 pw=0 time=115 us)'
STAT #20 id=5 cnt=3 pid=4 pos=1 obj=0 op='UNION-ALL (cr=0 pr=0 pw=0
time=94 us)'
STAT #20 id=6 cnt=1 pid=5 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0
time=10 us)'
STAT #20 id=7 cnt=1 pid=5 pos=2 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0
time=10 us)'
STAT #20 id=8 cnt=1 pid=5 pos=3 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0 time=9 us)'
STAT #20 id=9 cnt=2 pid=3 pos=2 obj=218362 op='TABLE ACCESS FULL
OBJ#(218362) (cr=7 pr=0 pw=0 time=426 us)'
and the test case is like this:
drop table t1;
create table t1 (id integer, f integer);
insert into t1 values (1,1);
insert into t1 values (2,1);
commit;
exec dbms_stats.gather_table_stats(ora_login_user,'T1');
exec dbms_support.start_trace(true,false);
merge /* */ into t1 d
using ( select 1 x from dual union all select 2 x from dual union
all select 3 x from dual ) s
on (d.id = s.x)
when
matched then
update set d.f = null
when not matched then
insert (id,f) values(s.x,1)
/
--
Edgar
--
//www.freelists.org/webpage/oracle-l
Other related posts:
