昨天贴了其中一部分,现在贴出全部,太大了。
运行的时候已经不出结果了。
别人写的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(+)
运行的时候已经不出结果了。
别人写的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(+)
看看执行计划
子查询嵌套太多极大影响了性能
可以在执行查询前创建会话级临时表:T_STAT_DT和QUOTE_APPROVAL
然后用这两个临时表代入查询
用parallel查询效率提高比较明显
索引是否有效的建立是影响SQL效率的一个很大的因素
我不知道能不能在workflow中创建session级的view
应该也没有执行计划
红色标记的字体不知是不是写错了,如果是的,那还有更多的优化空间,这里主要优化了表扫描及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
http://www.dbforums.com/oracle/998647-parameterized-view.html
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
倒数第二行,B.STAT_CD,有问题
STAT_CD是S_DOC_QUOTE表的
你帮我再看看,谢谢先
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