SELECT
item.item_cd,
SUM(odr_qty) AS forecast0
FROM
V_PUCH_ODR_GROSS_ITEM item,
T_OD od
WHERE
od.od_typ = '2' AND -- ÊÇÈÎÎñ
od.odr_sts_typ = '1' AND -- ¼Æ»®×´Ì¬
item.item_cd = od.item_cd(+)
GROUP BY
item.item_cd
其中视图 V_PUCH_ODR_GROSS_ITEM item如下:
SELECT
DISTINCT item_cd
FROM
T_RLSD_PUCH_ODR PuchOrd,
M_VEND_CTRL Vendor
WHERE
PuchOrd.puch_odr_sts_typ = '2'
PuchOrd.vend_cd = Vendor.vend_cd AND
Vendor.import_trn_typ = 1 T_RLSD_PUCH_ODR,T_OD 表的数据都在数十万条,各位帮帮忙,如何优化一下速度!
item.item_cd,
SUM(odr_qty) AS forecast0
FROM
V_PUCH_ODR_GROSS_ITEM item,
T_OD od
WHERE
od.od_typ = '2' AND -- ÊÇÈÎÎñ
od.odr_sts_typ = '1' AND -- ¼Æ»®×´Ì¬
item.item_cd = od.item_cd(+)
GROUP BY
item.item_cd
其中视图 V_PUCH_ODR_GROSS_ITEM item如下:
SELECT
DISTINCT item_cd
FROM
T_RLSD_PUCH_ODR PuchOrd,
M_VEND_CTRL Vendor
WHERE
PuchOrd.puch_odr_sts_typ = '2'
PuchOrd.vend_cd = Vendor.vend_cd AND
Vendor.import_trn_typ = 1 T_RLSD_PUCH_ODR,T_OD 表的数据都在数十万条,各位帮帮忙,如何优化一下速度!
FROM v_puch_odr_gross_item item, t_od od
WHERE item.item_cd = od.item_cd AND od.odr_sts_typ = '1' AND od.od_typ = '2'
GROUP BY item.item_cd
其中视图 V_PUCH_ODR_GROSS_ITEM item如下:SELECT DISTINCT item_cd
FROM t_rlsd_puch_odr puchord, m_vend_ctrl vendor
WHERE puchord.vend_cd = vendor.vend_cd
AND puchord.puch_odr_sts_typ = '2'
AND vendor.import_trn_typ = 1;
(select SUM(odr_qty) from T_OD od
where od.od_typ = '2'
AND od.odr_sts_typ = '1'
AND PuchOrd.item_cd = od.item_cd) forecast0
from T_RLSD_PUCH_ODR PuchOrd,M_VEND_CTRL Vendor
where PuchOrd.puch_odr_sts_typ = '2'
PuchOrd.vend_cd = Vendor.vend_cd AND
Vendor.import_trn_typ = 1;
//如果item_cd是M_VEND_CTRL Vendor中的列,PuchOrd.item_cd就换成Vendor.item_cd
//必须有下列索引
//(M_VEND_CTRL.import_trn_typ,M_VEND_CTRL.vend_cd)
//(T_RLSD_PUCH_ODR.vend_cd,T_RLSD_PUCH_ODR.puch_odr_sts_typ)
//(T_OD.item_cd,T_OD.odr_sts_typ,T_OD.od_typ)列的次序无关