select
c.destccode,
a.cgcode,
a.splrtupric,
b.ncicvtupric,
c.totalupric,
a.sumprice,
a.orderclass,
a.gnamedesc,
a.fcode
--c.qty,
--a.sumprice * b.qty as stomoneyfrom (select null destccode,
at.cgcode,
at.splrtupric,
0.0 ncicvtupric,
0.0 totalupric,
0.0 sumprice,
at.orderclass,
c.gnamedesc,
at.fcode
from askquotelist at, cgcode c
where at.cgcode = c.cgcode
and at.ccode = '4AC9'
and at.fcode = 'RMB'
and at.status = '70'
and at.cgicode = '26550CJ000'
and ((at.rtexcetodate is null and at.rtexcedate <= TO_DATE('2009-08-15','yyyy-mm-dd'))
or (at.rtexcedate < TO_DATE('2009-08-15', 'yyyy-mm-dd') and
TO_DATE('2009-09-15', 'yyyy-mm-dd') <= at.rtexcetodate))
) a,
(select null destccode,
at.cgcode,
0.0 splrtupric,
at.ncicvtupric,
0.0 totalupric,
0.0 sumprice,
at.orderclass,
c.gnamedesc,
at.fcode
from askquotelist at, cgcode c
where at.cgcode = c.cgcode
and at.ccode = '4AC9'
and at.fcode = 'RMB'
and at.status = '70'
and at.cgicode = '26550CJ000'
and ((at.vtexcetodate is null and
at.vtexcedate <= TO_DATE('2009-08-15', 'yyyy-mm-dd')) or
(at.vtexcedate < TO_DATE('2009-08-15','yyyy-mm-dd') and
TO_DATE('2009-09-15', 'yyyy-mm-dd') <= at.vtexcetodate))
) b,
( select distinct pt.destccode,
pt.cgcode,
0.0 splrtupric,
0.0 ncicvtupric,
pt.totalupric,
0.0 sumprice,
pt.orderclass,
c.gnamedesc,
null fcode
from packingcost pt,cgcode c
where pt.cgcode = c.cgcode
and pt.ccode = '4AC9'
and pt.status = '70'
and pt.cgicode = '26550CJ000'
and ((pt.excetodate is null and
pt.excedate <= TO_DATE('2009-08-15', 'yyyy-mm-dd')) or
(pt.excedate <= TO_DATE('2009-08-15', 'yyyy-mm-dd') and
TO_DATE('2009-09-15', 'yyyy-mm-dd') < pt.excetodate))
) c ,
(select st.cgcode,
sum(st.qty) as qty,
st.orderclass,
st.destccode,
st.fcode
from stocklist st
where st.dc = '1'
and st.status = '70'
and (st.tradebody = 'NCIC' or st.tradebody = 'NCIC-NSL') and
st.fcode = 'RMB'
and st.stockyear = '2009'
and st.stockmonth = '08'
and st.stockday = '1'
group by st.cgcode, st.orderclass, st.destccode, st.fcode) d where a.orderclass = b.orderclass and a.orderclass = c.orderclass and a.orderclass = d.orderclass
and c.destccode = d.destccode and a.fcode = d.fcode and a.cgcode = d.cgcode
c.destccode,
a.cgcode,
a.splrtupric,
b.ncicvtupric,
c.totalupric,
a.sumprice,
a.orderclass,
a.gnamedesc,
a.fcode
--c.qty,
--a.sumprice * b.qty as stomoneyfrom (select null destccode,
at.cgcode,
at.splrtupric,
0.0 ncicvtupric,
0.0 totalupric,
0.0 sumprice,
at.orderclass,
c.gnamedesc,
at.fcode
from askquotelist at, cgcode c
where at.cgcode = c.cgcode
and at.ccode = '4AC9'
and at.fcode = 'RMB'
and at.status = '70'
and at.cgicode = '26550CJ000'
and ((at.rtexcetodate is null and at.rtexcedate <= TO_DATE('2009-08-15','yyyy-mm-dd'))
or (at.rtexcedate < TO_DATE('2009-08-15', 'yyyy-mm-dd') and
TO_DATE('2009-09-15', 'yyyy-mm-dd') <= at.rtexcetodate))
) a,
(select null destccode,
at.cgcode,
0.0 splrtupric,
at.ncicvtupric,
0.0 totalupric,
0.0 sumprice,
at.orderclass,
c.gnamedesc,
at.fcode
from askquotelist at, cgcode c
where at.cgcode = c.cgcode
and at.ccode = '4AC9'
and at.fcode = 'RMB'
and at.status = '70'
and at.cgicode = '26550CJ000'
and ((at.vtexcetodate is null and
at.vtexcedate <= TO_DATE('2009-08-15', 'yyyy-mm-dd')) or
(at.vtexcedate < TO_DATE('2009-08-15','yyyy-mm-dd') and
TO_DATE('2009-09-15', 'yyyy-mm-dd') <= at.vtexcetodate))
) b,
( select distinct pt.destccode,
pt.cgcode,
0.0 splrtupric,
0.0 ncicvtupric,
pt.totalupric,
0.0 sumprice,
pt.orderclass,
c.gnamedesc,
null fcode
from packingcost pt,cgcode c
where pt.cgcode = c.cgcode
and pt.ccode = '4AC9'
and pt.status = '70'
and pt.cgicode = '26550CJ000'
and ((pt.excetodate is null and
pt.excedate <= TO_DATE('2009-08-15', 'yyyy-mm-dd')) or
(pt.excedate <= TO_DATE('2009-08-15', 'yyyy-mm-dd') and
TO_DATE('2009-09-15', 'yyyy-mm-dd') < pt.excetodate))
) c ,
(select st.cgcode,
sum(st.qty) as qty,
st.orderclass,
st.destccode,
st.fcode
from stocklist st
where st.dc = '1'
and st.status = '70'
and (st.tradebody = 'NCIC' or st.tradebody = 'NCIC-NSL') and
st.fcode = 'RMB'
and st.stockyear = '2009'
and st.stockmonth = '08'
and st.stockday = '1'
group by st.cgcode, st.orderclass, st.destccode, st.fcode) d where a.orderclass = b.orderclass and a.orderclass = c.orderclass and a.orderclass = d.orderclass
and c.destccode = d.destccode and a.fcode = d.fcode and a.cgcode = d.cgcode
不过,你可以考虑先在 select 后加个hints试试select /*+ ordered no_merge(a) no_merge(b) no_merge(c) use_hash(a b c)*/