帮忙优化一个SQL，分数不够可以另外再加

[sunnyheart]

昨天贴了其中一部分，现在贴出全部，太大了。
运行的时候已经不出结果了。
别人写的SQL，实在是懒得去一步一步的改了，请各位帮忙。
前题是不能改数据库，比如建Index之类的。

SELECT
S_DOC_QUOTE.ROW_ID ,
--省略一堆 S_DOC_QUOTE的字段,
S_PAYMENT_TERM.NAME ,
S_DOC_ORDER.TAX_EXEMPT_FLG ,
--省略一堆 S_DOC_ORDER的字段 ,
S_USER.LOGIN ,
(CASE WHEN S_DOC_QUOTE.STAT_CD = 'In Progress'
THEN
S_DOC_QUOTE.CREATED
ELSE
T_STAT_DT.QUOTE_LAST_STAT_DT
END
) AS QUOTE_LAST_STAT_DT ,
QUOTE_APPROVAL.CREATED
FROM
V_DOC_QUOTE S_DOC_QUOTE,
S_DOC_ORDER,
S_USER,
S_POSTN,
S_PRI_LST,
S_PAYMENT_TERM,
(SELECT
Q.ROW_ID,
Q.STAT_CD,
MAX(A.OPERATION_DT) AS QUOTE_LAST_STAT_DT
FROM S_DOC_QUOTE Q, S_AUDIT_ITEM A
WHERE Q.ROW_ID = A.RECORD_ID
AND A.FIELD_NAME = 'Status'
AND A.BUSCOMP_NAME = 'Quote'
GROUP BY Q.ROW_ID, Q.STAT_CD) T_STAT_DT,
（SELECT A.ROW_ID,G.CREATED FROM S_DOC_QUOTE A,
(
SELECT MAX(B.REV_NUM) MAXREV,B.QUOTE_NUM
FROM S_DOC_QUOTE B
GROUP BY B.QUOTE_NUM
) C,
(
SELECT E.RECORD_ID AS RECORD_ID, MAX(E.CREATED) AS CREATED
FROM S_AUDIT_ITEM E
WHERE E.BUSCOMP_NAME = 'Quote' AND E.FIELD_NAME = 'Status' AND E.NEW_VAL = 'Approved'
GROUP BY E.RECORD_ID
) G
WHERE A.QUOTE_NUM = C.QUOTE_NUM AND A.REV_NUM = MAXREV AND A.ROW_ID = G.RECORD_ID) QUOTE_APPROVAL
WHERE S_DOC_QUOTE.ROW_ID = S_DOC_ORDER.PAR_ROW_ID(+)
AND S_DOC_QUOTE.SALES_REP_POSTN_ID = S_POSTN.ROW_ID(+)
AND S_POSTN.PR_EMP_ID = S_USER.ROW_ID(+)
AND S_DOC_QUOTE.PRI_LST_ID = S_PRI_LST.ROW_ID(+)
AND S_DOC_QUOTE.PAYMENT_TERM_ID = S_PAYMENT_TERM.ROW_ID(+)
AND S_DOC_QUOTE.ROW_ID = T_STAT_DT.ROW_ID(+)
AND S_DOC_QUOTE.ROW_ID = QUOTE_APPROVAL.row_id(+)

[yonghengdizhen]

[Quote=引用楼主 sunnyheart 的回复:]
昨天贴了其中一部分，现在贴出全部，太大了。
运行的时候已经不出结果了。
别人写的SQL，实在是懒得去一步一步的改了，请各位帮忙。
前题是不能改数据库，比如建Index之类的。

SELECT
  S_DOC_QUOTE.ROW_ID ,
  --省略一堆 S_DOC_QUOTE的字段,
S_PAYMENT_TERM.NAME ,
S_DOC_ORDER.TAX_EXEMPT_FLG ,
--省略一堆 S_DOC_ORDER的字段 ,
S_USER.LOGIN ,
(CASE WHEN S_DOC_QUOTE.STAT_CD = 'In Progress'
THEN
S_DOC_QUOTE.CREATED
    ELSE
T_STAT_DT.QUOTE_LAST_STAT_DT
    END
) AS QUOTE_LAST_STAT_DT ,
QUOTE_APPROVAL.CREATED
FROM
V_DOC_QUOTE S_DOC_QUOTE,
S_DOC_ORDER,
S_USER,
S_POSTN,
S_PRI_LST,
S_PAYMENT_TERM,
(SELECT
  Q.ROW_ID,
  Q.STAT_CD,
  MAX(A.OPERATION_DT) AS QUOTE_LAST_STAT_DT
FROM S_DOC_QUOTE Q, S_AUDIT_ITEM A
WHERE Q.ROW_ID = A.RECORD_ID
  AND A.FIELD_NAME = 'Status'
  AND A.BUSCOMP_NAME = 'Quote'
GROUP BY Q.ROW_ID, Q.STAT_CD) T_STAT_DT,
（SELECT A.ROW_ID,G.CREATED FROM S_DOC_QUOTE A,
(
SELECT MAX(B.REV_NUM) MAXREV,B.QUOTE_NUM
FROM S_DOC_QUOTE B
GROUP BY B.QUOTE_NUM
) C,
(
SELECT E.RECORD_ID AS RECORD_ID, MAX(E.CREATED) AS CREATED
FROM S_AUDIT_ITEM E
  WHERE E.BUSCOMP_NAME = 'Quote' AND E.FIELD_NAME = 'Status' AND E.NEW_VAL = 'Approved'
GROUP BY  E.RECORD_ID
) G    
WHERE A.QUOTE_NUM = C.QUOTE_NUM AND A.REV_NUM = MAXREV AND A.ROW_ID = G.RECORD_ID) QUOTE_APPROVAL
WHERE S_DOC_QUOTE.ROW_ID = S_DOC_ORDER.PAR_ROW_ID(+)
AND S_DOC_QUOTE.SALES_REP_POSTN_ID = S_POSTN.ROW_ID(+)
AND S_POSTN.PR_EMP_ID = S_USER.ROW_ID(+)
AND S_DOC_QUOTE.PRI_LST_ID = S_PRI_LST.ROW_ID(+)
AND S_DOC_QUOTE.PAYMENT_TERM_ID = S_PAYMENT_TERM.ROW_ID(+)
AND S_DOC_QUOTE.ROW_ID = T_STAT_DT.ROW_ID(+)
AND S_DOC_QUOTE.ROW_ID = QUOTE_APPROVAL.row_id(+)
[/Quote]

红色标记的字体不知是不是写错了,如果是的,那还有更多的优化空间,这里主要优化了表扫描及group by操作

SELECT /*+use_hash(A B C D E F G) PARALLEL(A,2)*/ /*如果运行环境有足够的CPU和内存资源,可以考虑加上大表上的并行度*/
  A.ROW_ID , 
  --省略一堆 S_DOC_QUOTE的字段, 
  F.NAME , 
  B.TAX_EXEMPT_FLG , 
  --省略一堆 S_DOC_ORDER的字段 , 
  D.LOGIN , 
  (CASE WHEN A.STAT_CD='In Progress' THEN A.CREATED ELSE G.QUOTE_LAST_STAT_DT END) AS QUOTE_LAST_STAT_DT , 
  G.CREATED 
FROM /*V_DOC_QUOTE */S_DOC_QUOTE A, 
LEFT JOIN S_DOC_ORDER B ON A.ROW_ID = B.PAR_ROW_ID
LEFT JOIN S_POSTN C ON A.SALES_REP_POSTN_ID = C.ROW_ID
LEFT JOIN S_USER D ON C.PR_EMP_ID = D.ROW_ID
LEFT JOIN S_PRI_LST E ON A.PRI_LST_ID = E.ROW_ID
LEFT JOIN S_PAYMENT_TERM F ON A.PAYMENT_TERM_ID = F.ROW_ID
LEFT JOIN
(
SELECT /*+use_hash(A B)*/
    A.ROW_ID,
		B.STAT_CD,
		MAX(A.OPERATION_DT)                                                                                             AS QUOTE_LAST_STAT_DT,
    MAX(CASE WHEN A.REV_NUM=A.MAX_REV AND B.NEW_VAL='Approved' THEN B.CREATED END) OVER(PARTITION BY A.ROW_ID) AS CREATED
  FROM
  (
    SELECT ROW_ID,
            REV_NUM,
            MAX(REV_NUM) OVER(PARTITION BY QUOTE_NUM) AS MAX_REV
    FROM S_DOC_QUOTE
  ) A
  JOIN S_AUDIT_ITEM B ON A.ROW_ID=B.RECORD_ID AND B.FIELD_NAME='Status' AND B.FIELD_NAME='Status' 
 GROUP BY A.ROW_ID,B.STAT_CD
) G ON A.ROW_ID = G.ROW_ID

[小灰狼W]

语句上看起来很难再优化
看看执行计划
子查询嵌套太多极大影响了性能
可以在执行查询前创建会话级临时表：T_STAT_DT和QUOTE_APPROVAL
然后用这两个临时表代入查询

[ojuju10]

用临时表或者视图减少表的嵌套；
用parallel查询效率提高比较明显

[hebo2005]

可能最有效的优化方式首先被楼主否决了
索引是否有效的建立是影响SQL效率的一个很大的因素

[cosio]

建议把执行计划贴上来看看!

[sunnyheart]

这是在ETL中的SQL，因为执行太慢，总是无法抽取数据
我不知道能不能在workflow中创建session级的view
应该也没有执行计划

[l1t]

6楼的提示不错

[sunnyheart]

顶上去，看看有没有别的回答

[yonghengdizhen]

前面写得有点错误,红色位置为修改后的,

SELECT /*+use_hash(A B C D E F G) PARALLEL(A,2)*/ /*如果运行环境有足够的CPU和内存资源,可以考虑加上大表上的并行度*/
  A.ROW_ID , 
  --省略一堆 S_DOC_QUOTE的字段, 
  F.NAME , 
  B.TAX_EXEMPT_FLG , 
  --省略一堆 S_DOC_ORDER的字段 , 
  D.LOGIN , 
  (CASE WHEN A.STAT_CD='In Progress' THEN A.CREATED ELSE G.QUOTE_LAST_STAT_DT END) AS QUOTE_LAST_STAT_DT , 
  G.CREATED 
FROM /*V_DOC_QUOTE */S_DOC_QUOTE A, 
LEFT JOIN S_DOC_ORDER B ON A.ROW_ID = B.PAR_ROW_ID
LEFT JOIN S_POSTN C ON A.SALES_REP_POSTN_ID = C.ROW_ID
LEFT JOIN S_USER D ON C.PR_EMP_ID = D.ROW_ID
LEFT JOIN S_PRI_LST E ON A.PRI_LST_ID = E.ROW_ID
LEFT JOIN S_PAYMENT_TERM F ON A.PAYMENT_TERM_ID = F.ROW_ID
LEFT JOIN
(
SELECT /*+use_hash(A B)*/
    A.ROW_ID,
        B.STAT_CD,
        MAX(A.OPERATION_DT)                                                                                             AS QUOTE_LAST_STAT_DT,
    MAX(CASE WHEN A.REV_NUM=A.MAX_REV AND B.NEW_VAL='Approved' THEN B.CREATED END) OVER(PARTITION BY A.ROW_ID) AS CREATED
  FROM
  (
    SELECT ROW_ID,
            REV_NUM,
            MAX(REV_NUM) OVER(PARTITION BY QUOTE_NUM) AS MAX_REV
    FROM S_DOC_QUOTE
  ) A
  JOIN S_AUDIT_ITEM B ON A.ROW_ID=B.RECORD_ID AND E.BUSCOMP_NAME = 'Quote' AND B.FIELD_NAME='Status' 
 GROUP BY A.ROW_ID,B.STAT_CD
) G ON A.ROW_ID = G.ROW_ID

[palm_civet]

S_DOC_QUOTE,S_AUDIT_ITEM用的比较多，并且有group by，数据量大的情况下会比较慢，可以使用临时表，或者最简单的用with clause

http://www.oracle.com/technology/products/oracle9i/daily/oct10.html

V_DOC_QUOTE是个视图吧，检查一下逻辑是否复杂，是否可以优化，有的话同样用with clause，也可以用参数视图优化
http://www.dbforums.com/oracle/998647-parameterized-view.html

[sunnyheart]

to yonghengdizhen:

多谢，但是语句还是有问题
倒数第二行，B.STAT_CD，有问题
STAT_CD是S_DOC_QUOTE表的
你帮我再看看，谢谢先

[yonghengdizhen]

SELECT /*+use_hash(A B C D E F G) PARALLEL(A,2)*/ /*如果运行环境有足够的CPU和内存资源,可以考虑加上大表上的并行度*/
  A.ROW_ID , 
  --省略一堆 S_DOC_QUOTE的字段, 
  F.NAME , 
  B.TAX_EXEMPT_FLG , 
  --省略一堆 S_DOC_ORDER的字段 , 
  D.LOGIN , 
  (CASE WHEN A.STAT_CD='In Progress' THEN A.CREATED ELSE G.QUOTE_LAST_STAT_DT END) AS QUOTE_LAST_STAT_DT , 
  G.CREATED 
FROM /*V_DOC_QUOTE */S_DOC_QUOTE A, 
LEFT JOIN S_DOC_ORDER B ON A.ROW_ID = B.PAR_ROW_ID
LEFT JOIN S_POSTN C ON A.SALES_REP_POSTN_ID = C.ROW_ID
LEFT JOIN S_USER D ON C.PR_EMP_ID = D.ROW_ID
LEFT JOIN S_PRI_LST E ON A.PRI_LST_ID = E.ROW_ID
LEFT JOIN S_PAYMENT_TERM F ON A.PAYMENT_TERM_ID = F.ROW_ID
LEFT JOIN
(
SELECT /*+use_hash(A B)*/
    A.ROW_ID,
        A.STAT_CD,
        MAX(A.OPERATION_DT)                                                                                             AS QUOTE_LAST_STAT_DT,
    MAX(CASE WHEN A.REV_NUM=A.MAX_REV AND B.NEW_VAL='Approved' THEN B.CREATED END) OVER(PARTITION BY A.ROW_ID) AS CREATED
  FROM
  (
    SELECT ROW_ID,
           STAT_CD,
            REV_NUM,
            MAX(REV_NUM) OVER(PARTITION BY QUOTE_NUM) AS MAX_REV
    FROM S_DOC_QUOTE
  ) A
  JOIN S_AUDIT_ITEM B ON A.ROW_ID=B.RECORD_ID AND E.BUSCOMP_NAME = 'Quote' AND B.FIELD_NAME='Status' 
 GROUP BY A.ROW_ID,A.STAT_CD
) G ON A.ROW_ID = G.ROW_ID