我这有三个查询视图 请DBA高手们帮我看看, 何处可优化优化啊,多谢指教啦。
1.SELECT e.goodsid,
d.goodsname,
d.goodstype,
d.prodarea,
e.agentid,
f.agentname,
f.paramitem,
f.supplyid,
a.supplyname,
d.factid,
k.storageqty,
k.busidtlqty,
f.buyerid,
aa.employeename buyername
FROM agent_goods e,
goods d ,
agent f,
(SELECT aa.goodsid,
ab.agentid,
SUM(aa.goodsqty) storageqty,
SUM(aa.goodsqty-aa.outqty) busidtlqty
FROM zx_lj_cxstqty_v aa,
batch_def ab
WHERE aa.batchid=ab.batchid
GROUP BY aa.goodsid,
ab.agentid
) k,
supplyer a,
employee aa
WHERE e.goodsid=d.goodsid
AND e.agentid =f.agentid
AND e.goodsid = k.goodsid(+)
AND e.agentid =k.agentid(+)
AND f.supplyid =a.supplyid
AND f.buyerid =aa.employeeid(+) ;
2.SELECT a.SUDOCID,
b.CREDATE,
b.SUPPLYID,
b.SUPPLYNAME,
b.ENTRYID,
e.ENTRYNAME,
a.SUDOCDTLID,
a.PRINTNO,
a.PRINTLINE,
b.INPUTMANID,
a.DEPTID,
a.GOODSID,
a.GOODSDTLID,
a.STORAGEID,
a.BATCHID,
a.GOODSQTY,
i.PACKSIZE,
a.UNITPRICE,
a.TOTAL_LINE,
a.COSTINGPRICE,
a.COSTINGMONEY,
a.PAYMODE,
a.SUTYPEID,
a.USESTATUS,
a.MEMO,
a.SETTLEFLAG,
a.SETTLEDQTY,
a.SETTLEDMONEY,
a.PLNPAYDATE,
a.PLNPAYQTY,
a.CORRECTFLAG,
a.CERTID,
h.employeename INPUTMANNAME,
a.PAYLIMIT,
g.STORAGENO,
f.goodsno,
a.agentid,
c.agentname,
d.paramitem
FROM SU_Dtl a,
SU_Doc b,
agent c,
INTEREST_RATE_PARAM d,
entry e,
goods f,
st_def g,
employee h,
goods_detail i
WHERE a.SUDOCID = b.SUDOCID
AND a.agentid =c.agentid
AND c.paramid =d.paramid(+)
AND b.entryid =e.entryid
AND a.goodsid =f.goodsid
AND a.storageid =g.storageid
AND b.inputmanid=h.employeeid(+)
AND a.goodsdtlid=i.goodsdtlid
AND a.USESTATUS = 13.SELECT "SA_DTL_V"."SALESID",
"SA_DOC_V"."CREDATE",
"SA_DOC_V"."CUSTOMID",
"SA_DOC_V"."CUSTOMNAME",
"SA_DOC_V"."SALERID",
"SA_DOC_V"."SALERNAME",
"SA_DOC_V"."SALESDEPTID",
"SA_DOC_V"."SALEDEPTNAME",
"SA_DOC_V".entryid,
SA_DOC_V.ENTRYNAME,
"SA_DTL_V"."SALESDTLID",
"SA_DTL_V"."SATYPEID",
"SA_DTL_V"."PRINTNO",
"SA_DTL_V"."PRINTLINE",
"SA_DTL_V"."GOODSID",
"SA_DTL_V"."GOODSQTY",
"SA_DTL_V"."UNITPRICE",
"SA_DTL_V"."TOTAL_LINE",
"SA_DTL_V"."NOTAXMONEY",
"SA_DTL_V"."GOODSUSEUNIT",
"SA_DTL_V"."GOODSUSEQTY",
"SA_DTL_V"."DISCOUNT",
sa_dtl_v.BATCHID,
sa.totaline1,
sa.totaline2,
sa.dtlprice1,
sa.dtlprice2,
SA_DTL_V.Realsaprice,
sa_dtl_v.realsatotal,
SA_DTL_V.intrealsaprice,
sb.agentid,
SA_DTL_V.realprofit
FROM "SA_DTL_V",
SA_DOC_V,
(SELECT a.sadtlid,
SUM(DECODE(a.comefrom, 1, a.totalline, 0)) totaline1,
SUM(DECODE(a.comefrom, 2, a.totalline, 0)) totaline2,
SUM(DECODE(a.comefrom, 1, a.dtlprice, 0)) dtlprice1,
SUM(DECODE(a.comefrom, 2, a.dtlprice, 0)) dtlprice2
FROM zx_sa_xieyi a
GROUP BY a.sadtlid
) sa,
batch_def sb
WHERE "SA_DTL_V"."SALESID" = "SA_DOC_V"."SALESID"
AND "SA_DTL_V"."USESTATUS" = 2
AND SA_DTL_V.SALESDTLID = sa.sadtlid(+)
AND SA_DTL_V.BATCHID =sb.batchid ;
1.SELECT e.goodsid,
d.goodsname,
d.goodstype,
d.prodarea,
e.agentid,
f.agentname,
f.paramitem,
f.supplyid,
a.supplyname,
d.factid,
k.storageqty,
k.busidtlqty,
f.buyerid,
aa.employeename buyername
FROM agent_goods e,
goods d ,
agent f,
(SELECT aa.goodsid,
ab.agentid,
SUM(aa.goodsqty) storageqty,
SUM(aa.goodsqty-aa.outqty) busidtlqty
FROM zx_lj_cxstqty_v aa,
batch_def ab
WHERE aa.batchid=ab.batchid
GROUP BY aa.goodsid,
ab.agentid
) k,
supplyer a,
employee aa
WHERE e.goodsid=d.goodsid
AND e.agentid =f.agentid
AND e.goodsid = k.goodsid(+)
AND e.agentid =k.agentid(+)
AND f.supplyid =a.supplyid
AND f.buyerid =aa.employeeid(+) ;
2.SELECT a.SUDOCID,
b.CREDATE,
b.SUPPLYID,
b.SUPPLYNAME,
b.ENTRYID,
e.ENTRYNAME,
a.SUDOCDTLID,
a.PRINTNO,
a.PRINTLINE,
b.INPUTMANID,
a.DEPTID,
a.GOODSID,
a.GOODSDTLID,
a.STORAGEID,
a.BATCHID,
a.GOODSQTY,
i.PACKSIZE,
a.UNITPRICE,
a.TOTAL_LINE,
a.COSTINGPRICE,
a.COSTINGMONEY,
a.PAYMODE,
a.SUTYPEID,
a.USESTATUS,
a.MEMO,
a.SETTLEFLAG,
a.SETTLEDQTY,
a.SETTLEDMONEY,
a.PLNPAYDATE,
a.PLNPAYQTY,
a.CORRECTFLAG,
a.CERTID,
h.employeename INPUTMANNAME,
a.PAYLIMIT,
g.STORAGENO,
f.goodsno,
a.agentid,
c.agentname,
d.paramitem
FROM SU_Dtl a,
SU_Doc b,
agent c,
INTEREST_RATE_PARAM d,
entry e,
goods f,
st_def g,
employee h,
goods_detail i
WHERE a.SUDOCID = b.SUDOCID
AND a.agentid =c.agentid
AND c.paramid =d.paramid(+)
AND b.entryid =e.entryid
AND a.goodsid =f.goodsid
AND a.storageid =g.storageid
AND b.inputmanid=h.employeeid(+)
AND a.goodsdtlid=i.goodsdtlid
AND a.USESTATUS = 13.SELECT "SA_DTL_V"."SALESID",
"SA_DOC_V"."CREDATE",
"SA_DOC_V"."CUSTOMID",
"SA_DOC_V"."CUSTOMNAME",
"SA_DOC_V"."SALERID",
"SA_DOC_V"."SALERNAME",
"SA_DOC_V"."SALESDEPTID",
"SA_DOC_V"."SALEDEPTNAME",
"SA_DOC_V".entryid,
SA_DOC_V.ENTRYNAME,
"SA_DTL_V"."SALESDTLID",
"SA_DTL_V"."SATYPEID",
"SA_DTL_V"."PRINTNO",
"SA_DTL_V"."PRINTLINE",
"SA_DTL_V"."GOODSID",
"SA_DTL_V"."GOODSQTY",
"SA_DTL_V"."UNITPRICE",
"SA_DTL_V"."TOTAL_LINE",
"SA_DTL_V"."NOTAXMONEY",
"SA_DTL_V"."GOODSUSEUNIT",
"SA_DTL_V"."GOODSUSEQTY",
"SA_DTL_V"."DISCOUNT",
sa_dtl_v.BATCHID,
sa.totaline1,
sa.totaline2,
sa.dtlprice1,
sa.dtlprice2,
SA_DTL_V.Realsaprice,
sa_dtl_v.realsatotal,
SA_DTL_V.intrealsaprice,
sb.agentid,
SA_DTL_V.realprofit
FROM "SA_DTL_V",
SA_DOC_V,
(SELECT a.sadtlid,
SUM(DECODE(a.comefrom, 1, a.totalline, 0)) totaline1,
SUM(DECODE(a.comefrom, 2, a.totalline, 0)) totaline2,
SUM(DECODE(a.comefrom, 1, a.dtlprice, 0)) dtlprice1,
SUM(DECODE(a.comefrom, 2, a.dtlprice, 0)) dtlprice2
FROM zx_sa_xieyi a
GROUP BY a.sadtlid
) sa,
batch_def sb
WHERE "SA_DTL_V"."SALESID" = "SA_DOC_V"."SALESID"
AND "SA_DTL_V"."USESTATUS" = 2
AND SA_DTL_V.SALESDTLID = sa.sadtlid(+)
AND SA_DTL_V.BATCHID =sb.batchid ;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货