Having issues running this query.
In 8i iam using the rule optimzer and this query runs in 6 minutes
Have imported all the data into a 10g database running a optimzer mode as all_rows and the same query takes 30 minutes.
Query is as follows
select distinct b.part_no,
b.contract,
lot_batch_no,
avg(DECODE(Purchase_Order_
API.Calc_O
rder_Total
(a.ORDER_N
O),0,.01,
round(a.buy_unit_price *
((Purchase_Order_API.Calc_
Order_Tota
l(a.ORDER_
NO) +nvl(
PURCHASE_ORDER_CHARGE_API.
Get_Charge
_Total_Bas
e (a.order_no),0)
)/ Purchase_Order_API.Calc_Or
der_Total(
a.ORDER_NO
)),4))) Price
from purchase_order_line a,
receipt_inventory_location
b
where a.contract = b.contract
and a.contract in ('SPRO','SPALD')
and a.order_no = b.order_no
and a.line_no = b.line_no
and a.release_no = b.release_no
and a.wanted_delivery_date > SYSDATE-790
group by b.part_no, lot_batch_no, b.contract
Explain plan from 8i is
xecution Plan
--------------------------
----------
----------
----------
-
0 SELECT STATEMENT Optimizer=RULE
1 0 SORT (GROUP BY)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'RECEIPT_INVENTORY_LOCATIO
N_TAB
'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'PURCHASE_ORDER_LINE_
TAB'
5 4 INDEX (UNIQUE SCAN) OF 'PURCHASE_ORDER_LINE_PK' (UNI
QUE)
From 10g the plan is
Id | Operation | Name | Rows | Bytes |
Cost |
--------------------------
----------
----------
----------
----------
----------
---
-------
0 | SELECT STATEMENT | | 107K| 9885K|
16833 |
1 | HASH GROUP BY | | 107K| 9885K|
16833 |
2 | HASH JOIN | | 107K| 9885K|
14503 |
3 | TABLE ACCESS FULL | PURCHASE_ORDER_LINE_TAB | 257K| 13M|
10375 |
4 | INDEX FAST FULL SCAN| RECEIPT_INVENTORY_LOCATION
_PK | 660K| 23M|
1725 |
Any help on this would be much appreciated
Start Free Trial
