sql查询需要很长时间才能执行 [英] sql query is taking long time to execute
问题描述
SELECT / * + RULE * /
pv.vendor_name
--y.inventory_item_id，
，mc.segment1 Business_unit
，mc.segment2 Product_line
，y.segment1ITEM
，mtp.organization_code
，y.description Item_description
，y.attribute1 country_of_origin
，y.primary_uom_code
- ，y.attribute2Tarrif_Code
，（
SELECT mcr.attribute2 FROM mtl_cross_references mcr WHERE CROSS_REFERENCE_TYPE =''CA Purchase''
AND mcr.inventory_item_id = y.inventory_item_id
和ROWNUM< 2
）Tarrif_Code
- ，y.attribute3Tarrif_Class
，（
SELECT mcr.attribute3 FROM mtl_cross_references mcr WHERE CROSS_REFERENCE_TYPE =''CA Purchase''
AND mcr.inventory_item_id = y.inventory_item_id
AND ROWNUM< 2
）Tarrif_Class
，
（SELECT NVL（ffvv.description，0.00）FROM FND_FLEX_VALUES_VL ffvv
WHERE ffvv.flex_value
=
（
（
SELECT mcr。 attribute2 FROM mtl_cross_references mcr WHERE CROSS_REFERENCE_TYPE =''CA Purchase''
AND mcr.inventory_item_id = y.inventory_item_id
AND ROWNUM< 2
）
||''_''||
（
SELECT mcr.attribute3 FROM mtl_cross_references mcr WHERE CROSS_REFERENCE_TYPE =''CA购买''
和mcr.inventory_item_id = y.inventory_item_id
AND ROWNUM< 2
）
）
）Tarrif利率
，pv.ATTRIBUTE1Freight_Rate
，pq.currency_code
，pv.attribute2 Brokerage_Rate
，cic.item_cost Std_frozen_cost
，cic.COST_TYPE_ID
，cct.COST_TYPE
，pq.vendor_item_num
，pq.unit_price
，y.inventory_item_status_code item_status_code
，（moqd.primary_transaction_quantity）ON_hand_Qty
，NVL（（SELECT SUM（NVL（quantity_invoiced，0））FROM ra_customer_trx_all a1，ra_customer_trx_lines_all b1
WHERE a1.customer_trx_id = b1.customer_trx_id
AND b1.inventory_item_id = y.inventory_item_id
AND trx_date BETWEEN TRUNC（SYSDATE - 366）AN D TRUNC（SYSDATE-1）
AND a1.complete_flag =''Y''）+（SELECT SUM（NVL（数量，0））FROM XXAB.XXAB_BRM_SALES_ANALYSIS_REPT a
WHERE a.inventory_item_id = y.inventory_item_id
AND a.fiscal_date BETWEEN TRUNC（SYSDATE - 366）AND TRUNC（SYSDATE-1）），0）total_units_sold
FROM
apps.mtl_system_items_b y，
mtl_parameters mtp，
--FND_FLEX_VALUES_VL ffvv，
（SELECT inventory_item_id，organization_id， SUM（primary_transaction_quantity）primary_transaction_quantity
来自mtl_onhand_quantities_detail
GROUP BY organization_id，inventory_item_id）moqd
，CST_COST_TYPES_V cct
， mtl_item_categories mic
，mtl_categories_vl mc
，cst_item_costs cic
，MRP_ITEM_SOURCING ms
，po_vendors pv
，（SELECT NVL（b.vendor_product_num，'''）vendor_item_num，a.vendor_id，b.item_id，a.currency_code，MAX（b.unit_price）unit_price
来自po_headers_all a，po_lines_all b
WHERE a.type_lookup_code =''QuOTATION''
和a.po_header_id = b.po_header_id
GROUP BY NVL（b.vendor_product_num，''''），a.vendor_id，b.item_id，a.currency_code
）pq
WHERE
y.organization_id = mtp.organization_id - 85 GRW 20060406
AND moqd.inventory_item_id（+）= y.inventory_item_id
AND moqd.organization_id（+） = y.organization_id
AND mic.inventory_item_id = y.inventory_item_id
AND mic.organization_id = y.organization_id
AND mic.category_id = mc .category_id
AND cic.COST_TYPE_ID = cct.COST_TYPE_ID
- 和cic.cost_type_id（+）= 1
AND cic.inventory_item_id（ +）= y.inventory_item_id
AND cic.organization_id（+）= y.organization_id
AND mc.structure_id = 50242 ---- 50270 - 产品结构
AND mic.category_set_id = 1100000001
AND ms.inventory_item _id（+）= y.inventory_item_id
AND compile_designator（+）=''ACCO_MRP''
AND ms.vendor_id = pv.vendor_id（+）
AND pq.item_id（+）= ms.inventory_item_id
AND pq.vendor_id（+）= ms.vendor_id
--AND ffvv.flex_value（+ ）=（y.ATTRIBUTE2 || ''_''|| y.ATTRIBUTE3）
AND mtp.organization_code =''BRM''
SELECT /*+RULE*/
pv.vendor_name
--y.inventory_item_id,
,mc.segment1 Business_unit
,mc.segment2 Product_line
,y.segment1 "ITEM"
,mtp.organization_code
,y.description Item_description
,y.attribute1 country_of_origin
,y.primary_uom_code
--,y.attribute2 "Tarrif_Code"
,(
SELECT mcr.attribute2 FROM mtl_cross_references mcr WHERE CROSS_REFERENCE_TYPE = ''CA Purchase''
AND mcr.inventory_item_id = y.inventory_item_id
AND ROWNUM < 2
) "Tarrif_Code"
--,y.attribute3 "Tarrif_Class"
,(
SELECT mcr.attribute3 FROM mtl_cross_references mcr WHERE CROSS_REFERENCE_TYPE = ''CA Purchase''
AND mcr.inventory_item_id = y.inventory_item_id
AND ROWNUM < 2
) "Tarrif_Class"
,
(SELECT NVL(ffvv.description, 0.00) FROM FND_FLEX_VALUES_VL ffvv
WHERE ffvv.flex_value
=
(
(
SELECT mcr.attribute2 FROM mtl_cross_references mcr WHERE CROSS_REFERENCE_TYPE = ''CA Purchase''
AND mcr.inventory_item_id = y.inventory_item_id
AND ROWNUM < 2
)
||''_''||
(
SELECT mcr.attribute3 FROM mtl_cross_references mcr WHERE CROSS_REFERENCE_TYPE = ''CA Purchase''
AND mcr.inventory_item_id = y.inventory_item_id
AND ROWNUM < 2
)
)
) "Tarrif Rate"
,pv.ATTRIBUTE1 "Freight_Rate"
,pq.currency_code
,pv.attribute2 Brokerage_Rate
,cic.item_cost Std_frozen_cost
,cic.COST_TYPE_ID
,cct.COST_TYPE
,pq.vendor_item_num
,pq.unit_price
,y.inventory_item_status_code item_status_code
,(moqd.primary_transaction_quantity) ON_hand_Qty
,NVL((SELECT SUM(NVL(quantity_invoiced,0)) FROM ra_customer_trx_all a1, ra_customer_trx_lines_all b1
WHERE a1.customer_trx_id = b1.customer_trx_id
AND b1.inventory_item_id = y.inventory_item_id
AND trx_date BETWEEN TRUNC(SYSDATE - 366) AND TRUNC(SYSDATE-1)
AND a1.complete_flag=''Y'') + (SELECT SUM(NVL(quantity,0)) FROM XXAB.XXAB_BRM_SALES_ANALYSIS_REPT a
WHERE a.inventory_item_id = y.inventory_item_id
AND a.fiscal_date BETWEEN TRUNC(SYSDATE - 366) AND TRUNC(SYSDATE-1)),0) total_units_sold
FROM
apps.mtl_system_items_b y,
mtl_parameters mtp,
--FND_FLEX_VALUES_VL ffvv,
(SELECT inventory_item_id, organization_id, SUM(primary_transaction_quantity) primary_transaction_quantity
FROM mtl_onhand_quantities_detail
GROUP BY organization_id, inventory_item_id) moqd
, CST_COST_TYPES_V cct
, mtl_item_categories mic
, mtl_categories_vl mc
, cst_item_costs cic
, MRP_ITEM_SOURCING ms
, po_vendors pv
,(SELECT NVL(b.vendor_product_num,'' '') vendor_item_num,a.vendor_id, b.item_id, a.currency_code,MAX(b.unit_price) unit_price
FROM po_headers_all a, po_lines_all b
WHERE a.type_lookup_code =''QUOTATION''
AND a.po_header_id = b.po_header_id
GROUP BY NVL(b.vendor_product_num,'' '') ,a.vendor_id, b.item_id, a.currency_code
) pq
WHERE
y.organization_id=mtp.organization_id -- 85 GRW 20060406
AND moqd.inventory_item_id(+) = y.inventory_item_id
AND moqd.organization_id(+) = y.organization_id
AND mic.inventory_item_id = y.inventory_item_id
AND mic.organization_id = y.organization_id
AND mic.category_id = mc.category_id
AND cic.COST_TYPE_ID = cct.COST_TYPE_ID
--and cic.cost_type_id(+)=1
AND cic.inventory_item_id(+) = y.inventory_item_id
AND cic.organization_id(+) = y.organization_id
AND mc.structure_id = 50242 ----50270 --Product structure
AND mic.category_set_id = 1100000001
AND ms.inventory_item_id(+) = y.inventory_item_id
AND compile_designator(+)=''ACCO_MRP''
AND ms.vendor_id = pv.vendor_id(+)
AND pq.item_id(+)= ms.inventory_item_id
AND pq.vendor_id(+) = ms.vendor_id
--AND ffvv.flex_value(+) = (y.ATTRIBUTE2 || ''_'' || y.ATTRIBUTE3)
AND mtp.organization_code = ''BRM''
推荐答案
阅读这个 [ ^ ]
使用连接并尝试最小化子查询，添加索引（取决于您选择的数据集）
Hi,
Read this[^]
use joins and try to minimize sub Queries, add index ( depending on which you select you desire data set)
为了将来参考，您可能还会发现这些有关使用查询执行计划的文章有助于查找查询所需的位置优化。
PL-SQL
http://www.dba-oracle.com/plsql/t_plsql_plans.htm [ ^ ]
T-SQL
http://www.simple-talk.com/sql/performance/execution-plan-basics/ [ ^ ]
For future reference you may also find these article about using query execution plans helpful to find where your query needs to be optimized.
PL-SQL
http://www.dba-oracle.com/plsql/t_plsql_plans.htm[^]
T-SQL
http://www.simple-talk.com/sql/performance/execution-plan-basics/[^]
查看提供的优化提示链接，同时转换这对存储过程有帮助，因为SP'是预编译的并且执行得更快：
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index .jsp？topic = / com.ibm.db2.udb.doc / admin / c0005283.htm [ ^ ]
See the provided link for optimization ideas, also converting this to a stored procedure would help because SP''s are precompiled and execute faster:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0005283.htm[^]
这篇关于sql查询需要很长时间才能执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
