这个procedure捞一个月资料,3万笔左右,居然一个晚上都没跑完。
我先前测试的时候,也就15分钟,现在慢的原因也有测试环境变慢了,可是现在要上线,大家帮我优化一下,我是在想不出来了。谢过了----get_VSPO_REBATE这个pocedure每个月初会执行,用于捞取开过的po
--Procedure get_VSPO_REBATE(errbuf OUT VARCHAR2, retcode OUT Varchar2)
Procedure get_VSPO_REBATE
Is
v_to_date Date;
v_fm_date Date;
v_start_date Date;
v_latest_quo Number;
v_po_line_id Number;
v_latest_quo_start_date Date;
Cursor v_cursor Is
Select Distinct vri.Vendor_Id
,vri.vendor_site_id
,vri.Item_Id
,vri.Item_No
,vri.Currency_Code
,vri.organization_id
,trunc(vri.rt_date) rt_date
From vspace.vspo_rcv_quo_info vri;
Begin ---先清空上个月的资料
Delete
From vspace.Vspo_Rcv_Quo_Info vri
Where vri.Creation_Date< Sysdate-180;
-- EXECUTE IMMEDIATE 'TRUNCATE table vspace.Vspo_Rcv_Quo_Info';
commit;
select to_date('2007/03/01','yyyy/mm/dd') Into v_fm_date from dual;
select to_date('2007/03/02','yyyy/mm/dd') Into v_to_date from dual;
/* select to_date(to_char(sysdate-20, 'yyyy/mm')||'/01','yyyy/mm/dd') Into v_fm_date from dual;
select to_date(to_char(trunc(sysdate),'yyyy/mm')||'/01','yyyy/mm/dd')-1 Into v_to_date from dual;*/
Select trunc(sysdate-365) Into v_start_date From dual;
-----增加栏位msi.inventory_item_id用于串latest_quo
Insert Into vspace.Vspo_Rcv_Quo_Info
(
VENDOR_ID
,VENDOR_SITE_ID
,VENDOR_NAME
,VENDOR_SITE_CODE
,PO_NO
---------------------5
,PO_LINE_NO
,BUYER
,BUYER_ID
,ITEM_NO
,ITEM_ID
---------------------10
,UOM
,CURRENCY_CODE
,ORGANIZATION_ID---用于串quo_site
,RT_NO
,RT_DATE
---------------------15
,DELIVER_DATE
,RT_PRICE
,DELIVER_QTY
,INVOICE_NO
,CREATION_DATE
---------------------20
,ORG_CODE
)
Select
rt.Vendor_Id
,rt.vendor_site_id
,pv.Vendor_Name
,pvsa.Vendor_Site_Code
,pha.Segment1 PO_NO
----------------------5
,pla.Line_Num PO_Line_No
,he.last_name Buyer
,pa.Agent_Id BuyerId
,msi.Segment1 ItemNo
,pla.item_id
---------------------10
,rt.Uom_Code
,rt.Currency_Code
,rt.Organization_Id
,rsh.Receipt_Num RT_No
,rsh.creation_date RT_DATE
------------------------10
,trunc(RT.Transaction_Date) DELIVER_DATE
,rt.Po_Unit_Price RT_Price
,rt.Quantity DELIVER_Qty
,rsh.Attribute14 InvoiceNo
,trunc(Sysdate) As CREATION_DATE
------------------------20
,mp.organization_code
From po.Rcv_Transactions rt
,po.Rcv_Shipment_Headers rsh
,po.po_vendors pv
,po.Po_Vendor_Sites_All pvsa
,po.po_headers_all pha
,po.po_lines_all pla
,po.Po_Agents pa
,inv.Mtl_System_Items_b msi
,hr_employees he
,mtl_parameters mp
Where mp.organization_id=rt.organization_id
and rt.Shipment_Header_Id=rsh.Shipment_Header_Id
and rt.Po_Header_Id=pha.Po_Header_Id
and pvsa.Vendor_Id=pha.Vendor_Id
and pv.Vendor_Id=pha.Vendor_Id
and pvsa.Vendor_Site_Id=pha.Vendor_Site_Id
and rt.Po_Line_Id=pla.Po_Line_Id
and pha.Agent_Id=pa.Agent_Id
and he.employee_id=pa.agent_id
and pla.Item_Id=msi.Inventory_Item_Id
and msi.Organization_Id=rt.Organization_Id
and rt.Transaction_Type = 'DELIVER'
and pha.Type_Lookup_Code='STANDARD'
and rt.transaction_date between v_fm_date and v_to_date
And msi.Segment1 Between '00.000' And '99.zzzzz'
and rt.Organization_Id<>91;
Commit;
------------po_line_id-----------------------
---循环vspace.vspo_rcv_quo_info这个table,传入所需变量,然后select处最大的po_line_id再select出unit_price,
---即latest_quo. For sms In v_cursor Loop ----loop cursor
Select max(pla.Po_Line_Id)
Into v_po_line_id
From po.po_headers_all pha
,po.po_lines_all pla
,vspace.VSINV_ORG_MAPPING vom
Where pha.Po_Header_Id=pla.Po_Header_Id
and pha.Start_Date >= v_start_date
and pha.Start_Date < sms.rt_date+1---小于收货日期
and pha.Type_Lookup_Code='QUOTATION'
and pha.status_lookup_code='A'
and pha.Vendor_Id=sms.vendor_id
and pha.Vendor_Site_Id=sms.vendor_site_id
--and pha.currency_code=sms.currency_code
and pla.Item_Id=sms.item_id
and vom.Organization_Id=sms.organization_id
and (pha.attribute12=vom.Quo_Site or pha.attribute12 Is Null);
If v_po_line_id Is Not Null
Then
Select pla.Unit_Price,pha.start_date
Into v_latest_quo,v_latest_quo_start_date
From po.po_lines_all pla,po.po_headers_all pha
Where pha.po_header_id=pla.po_header_id
and pla.Po_Line_Id=v_po_line_id;
Update vspace.vspo_rcv_quo_info vri
Set vri.Latest_Quo=v_latest_quo,vri.quo_start_date=v_latest_quo_start_date
Where vri.Vendor_Id=sms.vendor_id
and vri.vendor_site_id=sms.vendor_site_id
and vri.Item_Id=sms.item_id
and vri.organization_id=sms.organization_id
and trunc(vri.rt_date)=sms.rt_date;
End If;
End Loop;
Commit;
Delete
From vspace.Vspo_Rcv_Quo_Info vrqi
Where vrqi.Rt_Price=vrqi.Latest_Quo;
Commit;
End;----end get_VSPO_REBATE
我先前测试的时候,也就15分钟,现在慢的原因也有测试环境变慢了,可是现在要上线,大家帮我优化一下,我是在想不出来了。谢过了----get_VSPO_REBATE这个pocedure每个月初会执行,用于捞取开过的po
--Procedure get_VSPO_REBATE(errbuf OUT VARCHAR2, retcode OUT Varchar2)
Procedure get_VSPO_REBATE
Is
v_to_date Date;
v_fm_date Date;
v_start_date Date;
v_latest_quo Number;
v_po_line_id Number;
v_latest_quo_start_date Date;
Cursor v_cursor Is
Select Distinct vri.Vendor_Id
,vri.vendor_site_id
,vri.Item_Id
,vri.Item_No
,vri.Currency_Code
,vri.organization_id
,trunc(vri.rt_date) rt_date
From vspace.vspo_rcv_quo_info vri;
Begin ---先清空上个月的资料
Delete
From vspace.Vspo_Rcv_Quo_Info vri
Where vri.Creation_Date< Sysdate-180;
-- EXECUTE IMMEDIATE 'TRUNCATE table vspace.Vspo_Rcv_Quo_Info';
commit;
select to_date('2007/03/01','yyyy/mm/dd') Into v_fm_date from dual;
select to_date('2007/03/02','yyyy/mm/dd') Into v_to_date from dual;
/* select to_date(to_char(sysdate-20, 'yyyy/mm')||'/01','yyyy/mm/dd') Into v_fm_date from dual;
select to_date(to_char(trunc(sysdate),'yyyy/mm')||'/01','yyyy/mm/dd')-1 Into v_to_date from dual;*/
Select trunc(sysdate-365) Into v_start_date From dual;
-----增加栏位msi.inventory_item_id用于串latest_quo
Insert Into vspace.Vspo_Rcv_Quo_Info
(
VENDOR_ID
,VENDOR_SITE_ID
,VENDOR_NAME
,VENDOR_SITE_CODE
,PO_NO
---------------------5
,PO_LINE_NO
,BUYER
,BUYER_ID
,ITEM_NO
,ITEM_ID
---------------------10
,UOM
,CURRENCY_CODE
,ORGANIZATION_ID---用于串quo_site
,RT_NO
,RT_DATE
---------------------15
,DELIVER_DATE
,RT_PRICE
,DELIVER_QTY
,INVOICE_NO
,CREATION_DATE
---------------------20
,ORG_CODE
)
Select
rt.Vendor_Id
,rt.vendor_site_id
,pv.Vendor_Name
,pvsa.Vendor_Site_Code
,pha.Segment1 PO_NO
----------------------5
,pla.Line_Num PO_Line_No
,he.last_name Buyer
,pa.Agent_Id BuyerId
,msi.Segment1 ItemNo
,pla.item_id
---------------------10
,rt.Uom_Code
,rt.Currency_Code
,rt.Organization_Id
,rsh.Receipt_Num RT_No
,rsh.creation_date RT_DATE
------------------------10
,trunc(RT.Transaction_Date) DELIVER_DATE
,rt.Po_Unit_Price RT_Price
,rt.Quantity DELIVER_Qty
,rsh.Attribute14 InvoiceNo
,trunc(Sysdate) As CREATION_DATE
------------------------20
,mp.organization_code
From po.Rcv_Transactions rt
,po.Rcv_Shipment_Headers rsh
,po.po_vendors pv
,po.Po_Vendor_Sites_All pvsa
,po.po_headers_all pha
,po.po_lines_all pla
,po.Po_Agents pa
,inv.Mtl_System_Items_b msi
,hr_employees he
,mtl_parameters mp
Where mp.organization_id=rt.organization_id
and rt.Shipment_Header_Id=rsh.Shipment_Header_Id
and rt.Po_Header_Id=pha.Po_Header_Id
and pvsa.Vendor_Id=pha.Vendor_Id
and pv.Vendor_Id=pha.Vendor_Id
and pvsa.Vendor_Site_Id=pha.Vendor_Site_Id
and rt.Po_Line_Id=pla.Po_Line_Id
and pha.Agent_Id=pa.Agent_Id
and he.employee_id=pa.agent_id
and pla.Item_Id=msi.Inventory_Item_Id
and msi.Organization_Id=rt.Organization_Id
and rt.Transaction_Type = 'DELIVER'
and pha.Type_Lookup_Code='STANDARD'
and rt.transaction_date between v_fm_date and v_to_date
And msi.Segment1 Between '00.000' And '99.zzzzz'
and rt.Organization_Id<>91;
Commit;
------------po_line_id-----------------------
---循环vspace.vspo_rcv_quo_info这个table,传入所需变量,然后select处最大的po_line_id再select出unit_price,
---即latest_quo. For sms In v_cursor Loop ----loop cursor
Select max(pla.Po_Line_Id)
Into v_po_line_id
From po.po_headers_all pha
,po.po_lines_all pla
,vspace.VSINV_ORG_MAPPING vom
Where pha.Po_Header_Id=pla.Po_Header_Id
and pha.Start_Date >= v_start_date
and pha.Start_Date < sms.rt_date+1---小于收货日期
and pha.Type_Lookup_Code='QUOTATION'
and pha.status_lookup_code='A'
and pha.Vendor_Id=sms.vendor_id
and pha.Vendor_Site_Id=sms.vendor_site_id
--and pha.currency_code=sms.currency_code
and pla.Item_Id=sms.item_id
and vom.Organization_Id=sms.organization_id
and (pha.attribute12=vom.Quo_Site or pha.attribute12 Is Null);
If v_po_line_id Is Not Null
Then
Select pla.Unit_Price,pha.start_date
Into v_latest_quo,v_latest_quo_start_date
From po.po_lines_all pla,po.po_headers_all pha
Where pha.po_header_id=pla.po_header_id
and pla.Po_Line_Id=v_po_line_id;
Update vspace.vspo_rcv_quo_info vri
Set vri.Latest_Quo=v_latest_quo,vri.quo_start_date=v_latest_quo_start_date
Where vri.Vendor_Id=sms.vendor_id
and vri.vendor_site_id=sms.vendor_site_id
and vri.Item_Id=sms.item_id
and vri.organization_id=sms.organization_id
and trunc(vri.rt_date)=sms.rt_date;
End If;
End Loop;
Commit;
Delete
From vspace.Vspo_Rcv_Quo_Info vrqi
Where vrqi.Rt_Price=vrqi.Latest_Quo;
Commit;
End;----end get_VSPO_REBATE
主要在循环cursor ,update的时候比较慢。
select max(pla.po_line_id)和update这些逻辑都是不能改的。
我想问的是除了循环cursor,有没有别的方法。
因为,抓latest_quo不能一次抓出来。如果嵌套sql,这样会更慢。
所以,先抓出别的栏位。然后,用cursor来返回给下面,捞latest_quo所需的条件。
主要逻辑是这样。
建议使用
跟踪以确定哪段SQL,耗资源.另外建议把他分为几个过程,
可以考虑使用函数来代替游标.
我所知道的就这么多.