本帖最后由 KEN6503 于 2015-6-12 07:38 编辑
下面是改写前后的query。那位帮忙看看改写的对不起对。谢谢
因为红字部分被用来join了两次， 所以想通过改写， 只读一次红字的部分。改写成join后用蓝字部分join condition。
现在的query。
SELECT A.GBAPP,
DEPOSIT.GBACCT
GLB_DETAIL.GDACCT
FROM (SELECT GBACCT,
GBAPP
FROM (SELECT GBACCT,
GBAPP
FROM TRANS_DTL
UNION ALL
SELECT GBACCT,
GBAPP
FROM TIMES_DTL) B
WHERE EXISTS
(SELECT 1
FROM CROSSREF_DTL, CUST_DTL
WHERE CROSSREF_DTL.GBACCT = B.GBACCT
AND CROSSREF_DTL.GBCIF = CUST_DTL.GBNBR)) DEPOSIT,
(SELECT LOAN.GBACCT,
LOAN.GBAPP,
GLB_DETAIL.GDACCT,
LOAN.GLNAFG
FROM (SELECT *
FROM LOAN_DTL
WHERE EXISTS
(SELECT 1
FROM CROSSREF_DTL, CUST_DTL
WHERE CROSSREF_DTL.GBACCT = LOAN_DTL.GBACCT
AND CROSSREF_DTL.GBCIF =CUST_DTL.GBNBR)) LOAN,
GLB_DETAIL
WHERE TO_NUMBER (GLB_DETAIL.GDACCT) = LOAN.GBACCT(+)) A
WHERE TO_NUMBER (A.GDACCT) = DEPOSIT.GBACCT(+);
改写后:
SELECT LOAN_DEPOSIT.GBAPP,
LOAN_DEPOSIT.GBACCT_2,
D.GDACCT
FROM (SELECT A.GBACCT,
A.GBAPP,
C.GBACCT GBACCT_2,
C.GBAPP GBAPP_2
FROM LOAN_DTL A,
(SELECT DISTINCT GBACCT
FROM CROSSREF_DTL, CUST_DTL
WHERE CROSSREF_DTL.GLCCIF = CUST_DTL.GLNBR) B,
(SELECT GBACCT,
GBAPP
FROM TRANS_DTL
UNION ALL
SELECT GBACCT,
GBAPP
FROM TIMES_DTL) C
WHERE (A.GBACCT(+) = B.GBACCT AND B.GBACCT = C.GBACCT (+) AND NOT (A.GBACCT IS NULL AND B.GBACCT IS NULL))) LOAN_DEPOSIT, GLB_DTL D
WHERE D.GDACCT = LOAN_DEPOSIT.GBACCT(+);
|
