How to optimize this query ?
Date: Wed, 27 Feb 2008 04:27:33 -0800 (PST)
Message-ID: <543cafc6-5c0c-4505-aadb-47886cdb11fa@s8g2000prg.googlegroups.com>
Hi gurus,
I have View query like this :
CREATE VIEW V_STOCK_DETAIL AS
SELECT NVL(STOCK.PRODUCT, TRANS.PRODUCT) PRODUCT,
NVL(STOCK.WH_CODE, TRANS.WH_CODE) WH_CODE, NVL(STOCK.QTY, 0) QTY_BEG, ROUND(NVL(TRANS.QTY_IN, 0) / CON.Coefficient, 5) QTY_IN, ROUND(NVL(TRANS.QTY_OUT, 0) / CON.Coefficient, 5) QTY_OUT FROM STOCK FULL JOIN ( SELECT PRODUCT, WH_CODE, SUM(QTY_IN) QTY_IN, SUM(QTY_OUT) QTY_OUT FROM MV_TRANS_STOCK GROUP BY PRODUCT, WH_CODE ) TRANS ON TRANS.PRODUCT = STOCK.PRODUCT AND TRANS.WH_CODE = STOCK.WH_CODEJOIN MV_CONVERT_UOM CON ON CON.PRODUCT = NVL(STOCK.PRODUCT, TRANS.PRODUCT) MV_TRANS_STOCK is Materialized View, Fast Refresh on Commit.
I dont make the above query MV because I need fast refresh so avoiding the front end application to heavy on refresh the whole MV.
The question is : what can I do to make the query above faster /more efficient ?
Thank you for your help,
xtanto
PS : below execution plan on query :
EXPLAIN PLAN FOR
SELECT * FROM V_STOCK_DETAIL_2 WHERE PRODUCT = '29620' AND WH_CODE =
'G32JKL'
[pre]
Plan hash value: 2449339973
| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
| 0 | SELECT STATEMENT | | 34186 |
4106K| | 451 (3)| 00:00:06 | | | |
| 1 | PX COORDINATOR | |
| | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10005 | 34186 |
4106K| | 451 (3)| 00:00:06 | Q1,05 | P->S | QC (RAND) | |* 3 | HASH JOIN BUFFERED | | 34186 | 4106K| | 451 (3)| 00:00:06 | Q1,05 | PCWP | |
| 4 | PX RECEIVE | | 1758 |
21096 | | 4 (0)| 00:00:01 | Q1,05 | PCWP | |
| 5 | PX SEND BROADCAST | :TQ10002 | 1758 |
21096 | | 4 (0)| 00:00:01 | Q1,02 | P->P | BROADCAST |
| 6 | PX BLOCK ITERATOR | | 1758 |
21096 | | 4 (0)| 00:00:01 | Q1,02 | PCWC | |
| 7 | MAT_VIEW ACCESS FULL | MV_CONVERT_UOM | 1758 |
21096 | | 4 (0)| 00:00:01 | Q1,02 | PCWP | |
| 8 | VIEW | | 34186 |
3705K| | 446 (3)| 00:00:06 | Q1,05 | PCWP | |
| 9 | UNION-ALL | |
| | | | | Q1,05 | PCWP | |
| 10 | HASH GROUP BY | | 34178 |
4038K| 9960K| 250 (4)| 00:00:03 | Q1,05 | PCWP | |
| 11 | PX RECEIVE | | 34178 |
4038K| | 250 (4)| 00:00:03 | Q1,05 | PCWP | |
| 12 | PX SEND HASH | :TQ10003 | 34178 |
4038K| | 250 (4)| 00:00:03 | Q1,03 | P->P | HASH |
| 13 | HASH GROUP BY | | 34178 |
4038K| 9960K| 250 (4)| 00:00:03 | Q1,03 | PCWP | | |* 14 | FILTER | |
| | | | | Q1,03 | PCWC | |
|* 15 | HASH JOIN OUTER | | 34178 | 4038K| | 247 (3)| 00:00:03 | Q1,03 | PCWP | |
| 16 | BUFFER SORT | |
| | | | | Q1,03 | PCWC | |
| 17 | PX RECEIVE | | 26026 |
1372K| | 56 (2)| 00:00:01 | Q1,03 | PCWP | |
| 18 | PX SEND HASH | :TQ10000 | 26026 |
1372K| | 56 (2)| 00:00:01 | | S->P | HASH |
| 19 | TABLE ACCESS FULL | STOCK | 26026 |
1372K| | 56 (2)| 00:00:01 | | | |
| 20 | PX RECEIVE | | 82239 |
5380K| | 189 (2)| 00:00:03 | Q1,03 | PCWP | |
| 21 | PX SEND HASH | :TQ10001 | 82239 |
5380K| | 189 (2)| 00:00:03 | Q1,01 | P->P | HASH |
| 22 | PX BLOCK ITERATOR | | 82239 |
5380K| | 189 (2)| 00:00:03 | Q1,01 | PCWC | |
| 23 | MAT_VIEW ACCESS FULL| MV_TRANS_STOCK | 82239 |
5380K| | 189 (2)| 00:00:03 | Q1,01 | PCWP | |
| 24 | HASH GROUP BY | | 8
| 288 | | 196 (3)| 00:00:03 | Q1,05 | PCWP | |
| 25 | PX RECEIVE | | 8
| 288 | | 195 (2)| 00:00:03 | Q1,05 | PCWP | |
| 26 | PX SEND HASH | :TQ10004 | 8
| 288 | | 195 (2)| 00:00:03 | Q1,04 | P->P | HASH |
| 27 | NESTED LOOPS ANTI | | 8
| 288 | | 195 (2)| 00:00:03 | Q1,04 | PCWP | |
| 28 | PX BLOCK ITERATOR | |
| | | | | Q1,04 | PCWC | |
|* 29 | MAT_VIEW ACCESS FULL | MV_TRANS_STOCK | 8
| 168 | | 191 (3)| 00:00:03 | Q1,04 | PCWP | |
|* 30 | INDEX RANGE SCAN | STOCK_PK | 1
| 15 | | 1 (0)| 00:00:01 | Q1,04 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
3 -
access("CON"."PRODUCT"=NVL("STOCK"."PRODUCT","TRANS"."PRODUCT"))
14 - filter(NVL("STOCK"."PRODUCT","PRODUCT")='29620' AND
NVL("STOCK"."WH_CODE","WH_CODE")='G32JKL')
15 - access("WH_CODE"(+)="STOCK"."WH_CODE" AND
"PRODUCT"(+)="STOCK"."PRODUCT")
29 - filter(NVL(NULL,"PRODUCT")='29620' AND
NVL(NULL,"WH_CODE")='G32JKL')
30 - access("PRODUCT"="STOCK"."PRODUCT" AND
"WH_CODE"="STOCK"."WH_CODE")
[/pre]
Received on Wed Feb 27 2008 - 06:27:33 CST