Click here to Skip to main content
12,894,700 members (56,860 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL
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'
Posted 14-Jan-13 6:44am
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Hi,

Read this[^]

use joins and try to minimize sub Queries, add index ( depending on which you select you desire data set)
  Permalink  
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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/[^]
  Permalink  
v3
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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[^]
  Permalink  
Comments
Jörgen Andersson 15-Jan-13 15:54pm
   
You need to update your knowledge, there are usually no or small performance benefits, sometimes even performance hits using stored procs. There are others benefits though.
http://www.scarydba.com/2009/09/30/pre-compiled-stored-procedures-fact-or-myth/
http://www.scarydba.com/2009/10/05/ad-hoc-queries-dont-reuse-execution-plans-myth-or-fact/

Same thing is valid for Oracle

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.170424.1 | Last Updated 14 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100