这个sql跑起来有些慢，是否有办法调优？

Question

这个sql跑起来有些慢，是否有办法调优？
SELECT A.BUSINESS_UNIT , 
A.INV_ITEM_ID , 
A.CM_BOOK , 
A.DT_TIMESTAMP , 
A.SEQ_NBR , 
A.CM_DT_TIMESTAMP , 
A.CM_SEQ_NBR , 
'415' , 
'A' , 
A.TRANSACTION_DATE , 
A.STORAGE_AREA , 
A.INV_LOT_ID , 
A.SERIAL_ID , 
A.CONSIGNED_FLAG , 
SUM(A.QTY_BASE) , 
A.UNIT_MEASURE_STD , 
0 , 
'N' 
FROM PS_CM_DEPLETE A , PS_CM_CSTACCTG X 
WHERE X.PROCESS_INSTANCE = :1 
AND X.REQUEST_ID = :2 
AND X.BUSINESS_UNIT = A.BUSINESS_UNIT 
AND X.CM_BOOK = A.CM_BOOK 
AND A.CM_DUMMY_FLG = 'N' 
AND EXISTS ( SELECT 'X' FROM PS_CM_RTVADJ_VW B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.CM_BOOK = B.CM_BOOK AND A.DT_TIMESTAMP = B.DT_TIMESTAMP AND A.SEQ_NBR = B.SEQ_NBR AND B.CM_UNIT_COST_VO-B.CM_UNIT_COST_STD-B.CM_UNIT_COST_VSUM <> 0) 
AND NOT EXISTS 
( SELECT 'X' 
FROM PS_CM_VARIANC_COST C 
WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT 
AND A.INV_ITEM_ID = C.INV_ITEM_ID 
AND A.CM_BOOK = C.CM_BOOK 
AND (A.DT_TIMESTAMP = C.DT_TIMESTAMP) 
AND A.SEQ_NBR = C.SEQ_NBR 
AND (A.CM_DT_TIMESTAMP_A = C.CM_DT_TIMESTAMP_A) 
AND A.CM_SEQ_NBR_A = C.CM_SEQ_NBR_A 
) 
GROUP BY A.BUSINESS_UNIT , A.INV_ITEM_ID , A.CM_BOOK , A.DT_TIMESTAMP , A.SEQ_NBR , A.CM_DT_TIMESTAMP , A.CM_SEQ_NBR , A.TRANSACTION_DATE , A.STORAGE_AREA , A.INV_LOT_ID , A.SERIAL_ID , A.CONSIGNED_FLAG , A.UNIT_MEASURE_STD
 

Answer 1

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |     1 |   212 |       |    20M (33)| 68:06:52 |
|   1 |  HASH GROUP BY                 |                    |     1 |   212 |  3433M|    20M (33)| 68:06:52 |
|*  2 |   FILTER                       |                    |       |       |       |            |          |
|*  3 |    HASH JOIN RIGHT ANTI        |                    |  7690K|  1554M|       |  1010  (38)| 00:00:13 |
|   4 |     INDEX FULL SCAN            | PS_CM_VARIANC_COST |     1 |    99 |       |     0   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS BY INDEX ROWID| PS_CM_DEPLETE      |   854K|    77M|       |    70   (0)| 00:00:01 |
|   6 |      NESTED LOOPS              |                    |  7690K|   828M|       |   634   (1)| 00:00:08 |
|*  7 |       INDEX RANGE SCAN         | PSBCM_CSTACCTG     |     9 |   162 |       |     1   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN         | IND_PS_CM_DEPLETE  |  1769K|       |       |     3   (0)| 00:00:01 |
|*  9 |    FILTER                      |                    |       |       |       |            |          |
|  10 |     HASH GROUP BY              |                    |     1 |    60 |       |     3  (34)| 00:00:01 |
|* 11 |      TABLE ACCESS FULL         | PS_CM_RTVADJ_TMP   |     4 |   240 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Answer 2

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |     1 |   212 |       |    20M (33)| 68:06:52 |
|   1 |  HASH GROUP BY                 |                    |     1 |   212 |  3433M|    20M (33)| 68:06:52 |
|*  2 |   FILTER                       |                    |       |       |       |            |          |
|*  3 |    HASH JOIN RIGHT ANTI        |                    |  7690K|  1554M|       |  1010  (38)| 00:00:13 |
|   4 |     INDEX FULL SCAN            | PS_CM_VARIANC_COST |     1 |    99 |       |     0   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS BY INDEX ROWID| PS_CM_DEPLETE      |   854K|    77M|       |    70   (0)| 00:00:01 |
|   6 |      NESTED LOOPS              |                    |  7690K|   828M|       |   634   (1)| 00:00:08 |
|*  7 |       INDEX RANGE SCAN         | PSBCM_CSTACCTG     |     9 |   162 |       |     1   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN         | IND_PS_CM_DEPLETE  |  1769K|       |       |     3   (0)| 00:00:01 |
|*  9 |    FILTER                      |                    |       |       |       |            |          |
|  10 |     HASH GROUP BY              |                    |     1 |    60 |       |     3  (34)| 00:00:01 |
|* 11 |      TABLE ACCESS FULL         | PS_CM_RTVADJ_TMP   |     4 |   240 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Answer 3

SELECT A.BUSINESS_UNIT,
      .      
      .  
  FROM PS_CM_DEPLETE A, PS_CM_CSTACCTG X
 WHERE X.PROCESS_INSTANCE = :1
   AND X.REQUEST_ID = :2
   AND X.BUSINESS_UNIT = A.BUSINESS_UNIT
   AND X.CM_BOOK = A.CM_BOOK
   AND A.CM_DUMMY_FLG = 'N'
为主表，
EXISTS和NOT EXISTS为A，B两个子表
和主表外联条件是
A.连接字段不为空
B.连接字段为空

Answer 4

关注下_______________________________
DBA请进群QQ群：88039805

Answer 5

楼主，想办法把那个很长的SQL语句编程若干个短一点的SQL语句吧