04번. IT 힌트얻기/▶ DB Tuning
[Tuning] 분석함수의 활용 (셀프조인의 해결)
카이사르&키케로
2011. 10. 12. 15:19
Analytic Functions 의 활용
-> 셀프 조인의 해결 #1
Original Source -- View Merge 현상 발생
Tuning-01 (order by절 추가하여 View merge 현상 해결)
서브쿼리를 줄이기
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
4 NESTED LOOPS
4 VIEW
4 SORT (GROUP BY)
20 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
20 INDEX (RANGE SCAN) OF 'CUSTOMER_IDX2' (NON-UNIQUE)
4 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
4 INDEX (UNIQUE SCAN) OF 'CUSTOMER_PK' (UNIQUE)
분석함수 사용하기
- MAX(~) KEEP(DENSE RANK LAST ORDER BY column ASC)
분석함수 사용하기
- MAX(~) KEEP(DENSE RANK FIRST ORDER BY column DESC)
분석함수 사용
- FIRST_VALUE(~) OVER (PARTITION BY ~ ORDER BY ~ DESC WINDOWING 절)
분석함수 사용 - 하나로 합치기
분석함수 사용
- LAST_VALUE(~) OVER (PARTITION BY ~ ORDER BY ~ASC WINDOWING 절)
SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
LAST_VALUE(STSDAT)
OVER(PARTITION BY BRNCOD, CMPID
ORDER BY STSDAT ASC, STS ASC
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) MAX_DAT,
LAST_VALUE(STS)
OVER(PARTITION BY BRNCOD, CMPID
ORDER BY STSDAT ASC, STS ASC
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) MAX_STS,
AMT
FROM CUSTOMER
WHERE STSDAT <= '20031130') C
WHERE C.STSDAT = C.MAX_DAT
AND C.STS = C.MAX_STS
AND C.AMT > 0;
분석함수 사용 - 하나로 합치기
분석함수 사용
- MAX (~) OVER (PARTITION BY ~)
분석함수 사용
- MAX (~) OVER (PARTITION BY ~) + WINDOWING 절 추가하기
-> 셀프 조인의 해결 #1
Original Source -- View Merge 현상 발생
SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
FROM CUSTOMER C,
/* 최종상태*/
(SELECT B.BRNCOD, B.CMPID, B.STSDAT, MAX(B.STS) MAX_STS
FROM CUSTOMER B,
/* 최종날짜 */
(SELECT BRNCOD, CMPID, MAX(STSDAT) MAX_DAT
FROM CUSTOMER
WHERE STSDAT <= '20031130'
GROUP BY BRNCOD, CMPID) A
WHERE B.BRNCOD = A.BRNCOD
AND B.CMPID = A.CMPID
AND B.STSDAT = A.MAX_DAT
GROUP BY B.BRNCOD, B.CMPID, B.STSDAT) D
WHERE C.BRNCOD = D.BRNCOD
AND C.CMPID = D.CMPID
AND C.STSDAT = D.STSDAT
AND C.STS = D.MAX_STS
AND C.AMT > 0
FROM CUSTOMER C,
/* 최종상태*/
(SELECT B.BRNCOD, B.CMPID, B.STSDAT, MAX(B.STS) MAX_STS
FROM CUSTOMER B,
/* 최종날짜 */
(SELECT BRNCOD, CMPID, MAX(STSDAT) MAX_DAT
FROM CUSTOMER
WHERE STSDAT <= '20031130'
GROUP BY BRNCOD, CMPID) A
WHERE B.BRNCOD = A.BRNCOD
AND B.CMPID = A.CMPID
AND B.STSDAT = A.MAX_DAT
GROUP BY B.BRNCOD, B.CMPID, B.STSDAT) D
WHERE C.BRNCOD = D.BRNCOD
AND C.CMPID = D.CMPID
AND C.STSDAT = D.STSDAT
AND C.STS = D.MAX_STS
AND C.AMT > 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 2 12 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 2 12 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 2 12 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 2 12 0 4
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
4 FILTER
8 SORT (GROUP BY)
16 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
41 NESTED LOOPS
8 NESTED LOOPS
4 VIEW <---------------------------
4 SORT (GROUP BY)
20 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
20 INDEX (RANGE SCAN) OF 'CUSTOMER_IDX2' (NON-UNIQUE)
8 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
16 INDEX (RANGE SCAN) OF 'CUSTOMER_IDX2' (NON-UNIQUE)
32 INDEX (RANGE SCAN) OF 'CUSTOMER_IDX2' (NON-UNIQUE)
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
4 FILTER
8 SORT (GROUP BY)
16 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
41 NESTED LOOPS
8 NESTED LOOPS
4 VIEW <---------------------------
4 SORT (GROUP BY)
20 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
20 INDEX (RANGE SCAN) OF 'CUSTOMER_IDX2' (NON-UNIQUE)
8 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
16 INDEX (RANGE SCAN) OF 'CUSTOMER_IDX2' (NON-UNIQUE)
32 INDEX (RANGE SCAN) OF 'CUSTOMER_IDX2' (NON-UNIQUE)
Tuning-01 (order by절 추가하여 View merge 현상 해결)
SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
FROM CUSTOMER C,
/* 최종상태*/
(SELECT B.BRNCOD, B.CMPID, B.STSDAT, MAX(B.STS) MAX_STS
FROM CUSTOMER B,
/* 최종날짜 */
(SELECT BRNCOD, CMPID, MAX(STSDAT) MAX_DAT
FROM CUSTOMER
WHERE STSDAT <= '20031130'
GROUP BY BRNCOD, CMPID) A
WHERE B.BRNCOD = A.BRNCOD
AND B.CMPID = A.CMPID
AND B.STSDAT = A.MAX_DAT
GROUP BY B.BRNCOD, B.CMPID, B.STSDAT
ORDER BY B.BRNCOD, B.CMPID, B.STSDAT) D <- order by를 추가해서 view merge 현상을 해결
WHERE C.BRNCOD = D.BRNCOD
AND C.CMPID = D.CMPID
AND C.STSDAT = D.STSDAT
AND C.STS = D.MAX_STS
AND C.AMT > 0
FROM CUSTOMER C,
/* 최종상태*/
(SELECT B.BRNCOD, B.CMPID, B.STSDAT, MAX(B.STS) MAX_STS
FROM CUSTOMER B,
/* 최종날짜 */
(SELECT BRNCOD, CMPID, MAX(STSDAT) MAX_DAT
FROM CUSTOMER
WHERE STSDAT <= '20031130'
GROUP BY BRNCOD, CMPID) A
WHERE B.BRNCOD = A.BRNCOD
AND B.CMPID = A.CMPID
AND B.STSDAT = A.MAX_DAT
GROUP BY B.BRNCOD, B.CMPID, B.STSDAT
ORDER BY B.BRNCOD, B.CMPID, B.STSDAT) D <- order by를 추가해서 view merge 현상을 해결
WHERE C.BRNCOD = D.BRNCOD
AND C.CMPID = D.CMPID
AND C.STSDAT = D.STSDAT
AND C.STS = D.MAX_STS
AND C.AMT > 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 1 12 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 1 12 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 1 12 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 1 12 0 4
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
4 NESTED LOOPS
4 VIEW
4 SORT (GROUP BY)
8 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
21 NESTED LOOPS
4 VIEW
4 SORT (GROUP BY)
20 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
20 INDEX (RANGE SCAN) OF 'CUSTOMER_IDX2'(NON-UNIQUE)
16 INDEX (RANGE SCAN) OF 'CUSTOMER_IDX2' (NON-UNIQUE)
4 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
4 INDEX (UNIQUE SCAN) OF 'CUSTOMER_PK' (UNIQUE)
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
4 NESTED LOOPS
4 VIEW
4 SORT (GROUP BY)
8 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
21 NESTED LOOPS
4 VIEW
4 SORT (GROUP BY)
20 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
20 INDEX (RANGE SCAN) OF 'CUSTOMER_IDX2'(NON-UNIQUE)
16 INDEX (RANGE SCAN) OF 'CUSTOMER_IDX2' (NON-UNIQUE)
4 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
4 INDEX (UNIQUE SCAN) OF 'CUSTOMER_PK' (UNIQUE)
서브쿼리를 줄이기
SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
FROM CUSTOMER C,
(SELECT BRNCOD, CMPID,
SUBSTR(MAX(STSDAT||STS),1,8) MAX_DAT,
SUBSTR(MAX(STSDAT||STS),9,1) MAX_STS
FROM CUSTOMER
WHERE STSDAT <= '20031130'
GROUP BY BRNCOD, CMPID
ORDER BY BRNCOD, CMPID) B
WHERE C.BRNCOD = B.BRNCOD
AND C.CMPID = B.CMPID
AND C.STSDAT = B.MAX_DAT
AND C.STS = B.MAX_STS
AND C.AMT > 0
FROM CUSTOMER C,
(SELECT BRNCOD, CMPID,
SUBSTR(MAX(STSDAT||STS),1,8) MAX_DAT,
SUBSTR(MAX(STSDAT||STS),9,1) MAX_STS
FROM CUSTOMER
WHERE STSDAT <= '20031130'
GROUP BY BRNCOD, CMPID
ORDER BY BRNCOD, CMPID) B
WHERE C.BRNCOD = B.BRNCOD
AND C.CMPID = B.CMPID
AND C.STSDAT = B.MAX_DAT
AND C.STS = B.MAX_STS
AND C.AMT > 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 9 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 9 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 9 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 9 0 4
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
4 NESTED LOOPS
4 VIEW
4 SORT (GROUP BY)
20 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
20 INDEX (RANGE SCAN) OF 'CUSTOMER_IDX2' (NON-UNIQUE)
4 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'
4 INDEX (UNIQUE SCAN) OF 'CUSTOMER_PK' (UNIQUE)
분석함수 사용하기
- MAX(~) KEEP(DENSE RANK LAST ORDER BY column ASC)
SELECT BRNCOD, CMPID,
MAX(STSDAT) KEEP(DENSE_RANK LAST ORDER BY STSDAT ASC , STS ASC) A,
MAX(STS) KEEP(DENSE_RANK LAST ORDER BY STSDAT ASC, STS ASC) B,
MAX(AMT) KEEP(DENSE_RANK LAST ORDER BY STSDAT ASC, STS ASC) C
FROM CUSTOMER
WHERE STSDAT <= '20031130'
GROUP BY BRNCOD, CMPID
MAX(STSDAT) KEEP(DENSE_RANK LAST ORDER BY STSDAT ASC , STS ASC) A,
MAX(STS) KEEP(DENSE_RANK LAST ORDER BY STSDAT ASC, STS ASC) B,
MAX(AMT) KEEP(DENSE_RANK LAST ORDER BY STSDAT ASC, STS ASC) C
FROM CUSTOMER
WHERE STSDAT <= '20031130'
GROUP BY BRNCOD, CMPID
분석함수 사용하기
- MAX(~) KEEP(DENSE RANK FIRST ORDER BY column DESC)
SELECT BRNCOD, CMPID,
MAX(STSDAT) KEEP(DENSE_RANK FIRST ORDER BY STSDAT DESC, STS DESC) A,
MAX(STS) KEEP(DENSE_RANK FIRST ORDER BY STSDAT DESC, STS DESC) B,
MAX(AMT) KEEP(DENSE_RANK FIRST ORDER BY STSDAT DESC, STS DESC) C
FROM CUSTOMER
WHERE STSDAT <= '20031130'
GROUP BY BRNCOD, CMPID
MAX(STSDAT) KEEP(DENSE_RANK FIRST ORDER BY STSDAT DESC, STS DESC) A,
MAX(STS) KEEP(DENSE_RANK FIRST ORDER BY STSDAT DESC, STS DESC) B,
MAX(AMT) KEEP(DENSE_RANK FIRST ORDER BY STSDAT DESC, STS DESC) C
FROM CUSTOMER
WHERE STSDAT <= '20031130'
GROUP BY BRNCOD, CMPID
분석함수 사용
- FIRST_VALUE(~) OVER (PARTITION BY ~ ORDER BY ~ DESC WINDOWING 절)
SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
FIRST_VALUE(STSDAT)
OVER(PARTITION BY BRNCOD, CMPID
ORDER BY STSDAT DESC, STS DESC
ROWS UNBOUNDED PRECEDING) MAX_DAT,
FIRST_VALUE(STS)
OVER(PARTITION BY BRNCOD, CMPID
ORDER BY STSDAT DESC, STS DESC
ROWS UNBOUNDED PRECEDING) MAX_STS,
AMT
FROM CUSTOMER
WHERE STSDAT <= '20031130') C
WHERE C.STSDAT = C.MAX_DAT
AND C.STS = C.MAX_STS
AND C.AMT > 0;
FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
FIRST_VALUE(STSDAT)
OVER(PARTITION BY BRNCOD, CMPID
ORDER BY STSDAT DESC, STS DESC
ROWS UNBOUNDED PRECEDING) MAX_DAT,
FIRST_VALUE(STS)
OVER(PARTITION BY BRNCOD, CMPID
ORDER BY STSDAT DESC, STS DESC
ROWS UNBOUNDED PRECEDING) MAX_STS,
AMT
FROM CUSTOMER
WHERE STSDAT <= '20031130') C
WHERE C.STSDAT = C.MAX_DAT
AND C.STS = C.MAX_STS
AND C.AMT > 0;
분석함수 사용 - 하나로 합치기
SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
FIRST_VALUE(STSDAT||STS)
OVER(PARTITION BY BRNCOD, CMPID
ORDER BY STSDAT||STS DESC
ROWS UNBOUNDED PRECEDING) MAX_DTSTS,
AMT
FROM CUSTOMER
WHERE STSDAT <= '20031130') C
WHERE C.STSDAT = SUBSTR(C.MAX_DTSTS,1,8)
AND C.STS = SUBSTR(C.MAX_DTSTS,9,1)
AND C.AMT > 0;
FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
FIRST_VALUE(STSDAT||STS)
OVER(PARTITION BY BRNCOD, CMPID
ORDER BY STSDAT||STS DESC
ROWS UNBOUNDED PRECEDING) MAX_DTSTS,
AMT
FROM CUSTOMER
WHERE STSDAT <= '20031130') C
WHERE C.STSDAT = SUBSTR(C.MAX_DTSTS,1,8)
AND C.STS = SUBSTR(C.MAX_DTSTS,9,1)
AND C.AMT > 0;
분석함수 사용
- LAST_VALUE(~) OVER (PARTITION BY ~ ORDER BY ~ASC WINDOWING 절)
SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
LAST_VALUE(STSDAT)
OVER(PARTITION BY BRNCOD, CMPID
ORDER BY STSDAT ASC, STS ASC
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) MAX_DAT,
LAST_VALUE(STS)
OVER(PARTITION BY BRNCOD, CMPID
ORDER BY STSDAT ASC, STS ASC
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) MAX_STS,
AMT
FROM CUSTOMER
WHERE STSDAT <= '20031130') C
WHERE C.STSDAT = C.MAX_DAT
AND C.STS = C.MAX_STS
AND C.AMT > 0;
분석함수 사용 - 하나로 합치기
SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
LAST_VALUE(STSDAT||STS)
OVER(PARTITION BY BRNCOD, CMPID
ORDER BY STSDAT||STS ASC
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) MAX_DTSTS,
AMT
FROM CUSTOMER
WHERE STSDAT <= '20031130') C
WHERE C.STSDAT = SUBSTR(C.MAX_DTSTS,1,8)
AND C.STS = SUBSTR(C.MAX_DTSTS,9,1)
AND C.AMT > 0;
FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
LAST_VALUE(STSDAT||STS)
OVER(PARTITION BY BRNCOD, CMPID
ORDER BY STSDAT||STS ASC
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) MAX_DTSTS,
AMT
FROM CUSTOMER
WHERE STSDAT <= '20031130') C
WHERE C.STSDAT = SUBSTR(C.MAX_DTSTS,1,8)
AND C.STS = SUBSTR(C.MAX_DTSTS,9,1)
AND C.AMT > 0;
분석함수 사용
- MAX (~) OVER (PARTITION BY ~)
SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
MAX(STSDAT||STS)
OVER(PARTITION BY BRNCOD, CMPID) MAX_DTSTS,
AMT
FROM CUSTOMER
WHERE STSDAT <= '20031130') C
WHERE C.STSDAT = SUBSTR(C.MAX_DTSTS,1,8)
AND C.STS = SUBSTR(C.MAX_DTSTS,9,1)
AND C.AMT > 0;
FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
MAX(STSDAT||STS)
OVER(PARTITION BY BRNCOD, CMPID) MAX_DTSTS,
AMT
FROM CUSTOMER
WHERE STSDAT <= '20031130') C
WHERE C.STSDAT = SUBSTR(C.MAX_DTSTS,1,8)
AND C.STS = SUBSTR(C.MAX_DTSTS,9,1)
AND C.AMT > 0;
분석함수 사용
- MAX (~) OVER (PARTITION BY ~) + WINDOWING 절 추가하기
SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
MAX(STSDAT||STS)
OVER(PARTITION BY BRNCOD, CMPID ORDER BY STSDAT DESC,STS DESC
ROW UNBOUNDED PRECEDING ) MAX_DTSTS,
AMT
FROM CUSTOMER
WHERE STSDAT <= '20031130') C
WHERE C.STSDAT = SUBSTR(C.MAX_DTSTS,1,8)
AND C.STS = SUBSTR(C.MAX_DTSTS,9,1)
AND C.AMT > 0;
FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
MAX(STSDAT||STS)
OVER(PARTITION BY BRNCOD, CMPID ORDER BY STSDAT DESC,STS DESC
ROW UNBOUNDED PRECEDING ) MAX_DTSTS,
AMT
FROM CUSTOMER
WHERE STSDAT <= '20031130') C
WHERE C.STSDAT = SUBSTR(C.MAX_DTSTS,1,8)
AND C.STS = SUBSTR(C.MAX_DTSTS,9,1)
AND C.AMT > 0;
SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
MAX(STSDAT||STS)
OVER(PARTITION BY BRNCOD, CMPID ORDER BY STSDAT ASC, STS ASC
ROW BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) MAX_DTSTS,
AMT
FROM CUSTOMER
WHERE STSDAT <= '20031130') C
WHERE C.STSDAT = SUBSTR(C.MAX_DTSTS,1,8)
AND C.STS = SUBSTR(C.MAX_DTSTS,9,1)
AND C.AMT > 0;
FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
MAX(STSDAT||STS)
OVER(PARTITION BY BRNCOD, CMPID ORDER BY STSDAT ASC, STS ASC
ROW BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) MAX_DTSTS,
AMT
FROM CUSTOMER
WHERE STSDAT <= '20031130') C
WHERE C.STSDAT = SUBSTR(C.MAX_DTSTS,1,8)
AND C.STS = SUBSTR(C.MAX_DTSTS,9,1)
AND C.AMT > 0;