<고객별 마지막 주문을 구하는 SQL>
특정 그룹(GROUP BY)별로 마지막 데이터를 구하는 SQL을 알아보자. SQL에 따라 성능이 어떻게 변하는지도 살펴보도록 하자.
여기서 살펴보는 방법은 다음과 같다.
- WHERE절 서브쿼리
- 인라인-뷰(GROUP BY후, 조인)
- 분석함수와 ROW_NUMBER
- 마스터 테이블과 스칼라 서브쿼리를 활용
- 인라인-뷰의 KEEP함수(ROWID를 활용)
SQL BOOSTER 에 이어지는 이야기들입니다.~!
SQL BOOSTER 를 보신 분들께 좀 더 도움을 드리고자 추가로 작성한 내용입니다.
www.aladin.co.kr/shop/wproduct.aspx?ItemId=216383877
SQL BOOSTER
프로젝트 성공을 위한 SQL 필독서. 프로젝트를 진행하는 순서처럼 구성되어 있다. 프로젝트 투입을 위해 필요한 SQL 기술을 설명하고, 성능 테스트를 위해 필요한 기술을 설명한 뒤에 마지막으로
www.aladin.co.kr
설명의 편의상 반말체로 작성한 점 양해바랍니다. pdf 파일도 첨부드리니 다운 받아 보셔도 됩니다.
여기서 살펴보는 방법은 다음과 같다.
- WHERE절 서브쿼리
- 인라인-뷰(GROUP BY후, 조인)
- 분석함수와 ROW_NUMBER
- 마스터 테이블과 스칼라 서브쿼리를 활용
- 인라인-뷰의 KEEP함수(ROWID를 활용)
상황과 방법에 따라 다양한 결과가 나온다. 결과는 마지막에 있으니 살펴보기 바란다.
(1) 고객별 마지막 주문 데이터 가져오기.
T_ORD_JOIN 테이블에서 고객별 마지막 주문만 가져오는 SQL을 작성해보자. 먼저 아래와 같은 인덱스를 만들도록 한다.
X_T_ORD_JOIN_TEST 인덱스 생성
CREATE INDEX X_T_ORD_JOIN_TEST ON T_ORD_JOIN(CUS_ID,ORD_SEQ);인덱스를 만든 후에는, 아래와 같은 SQL로 고객별 마지막 주문을 가져올 수 있다.
[SQL-1] 고객별 마지막 주문 가져오기 – WHERE절 서브쿼리
SELECT *
FROM T_ORD_JOIN T1
WHERE T1.ORD_SEQ = (SELECT MAX(A.ORD_SEQ)
FROM T_ORD_JOIN A
WHERE A.CUS_ID = T1.CUS_ID);
WHERE절의 서브쿼리를 사용한 간단한 방법이다. FROM절의 T_ORD_JOIN을 순차적으로 읽어가면서 WHERE 절의 서브쿼리에 T1.CUS_ID를 공급하고, 해당 T1.CUS_ID의 마지막 ORD_SEQ를 찾아서 FROM절의 ORD_SEQ와 같으면 조회하는 방법이다. 이와 같은 SQL 작성 방법은 좋지 않다.
실제 실행된 실행계획을 살펴보면 아래와 같다.
[실행계획-1] 고객별 마지막 주문 가져오기
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 90 |00:00:00.81 | 11867 |
| 1 | NESTED LOOPS | | 1 | 90 |00:00:00.81 | 11867 |
| 2 | NESTED LOOPS | | 1 | 90 |00:00:00.81 | 11777 |
| 3 | VIEW | VW_SQ_1 | 1 | 90 |00:00:00.81 | 11594 |
| 4 | HASH GROUP BY | | 1 | 90 |00:00:00.81 | 11594 |
| 5 | INDEX FAST FULL SCAN | X_T_ORD_JOIN_TEST | 1 | 3224K|00:00:00.59 | 11594 |
|* 6 | INDEX UNIQUE SCAN | PK_T_ORD_JOIN | 90 | 90 |00:00:00.01 | 183 |
|* 7 | TABLE ACCESS BY INDEX ROWID| T_ORD_JOIN | 90 | 90 |00:00:00.01 | 90 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("T1"."ORD_SEQ"="MAX(A.ORD_SEQ)")
7 - filter("ITEM_1"="T1"."CUS_ID")
실행계획에서는 서브쿼리를 먼저 처리하고 있다. 실행계획의 3~5번 단계에서 X_T_ORD_JOIN_TEST 인덱스를 모두 읽어서 HASH GROUP BY 한 후에, 2번 단계에서 NL 조인 하는 것을 보면 알 수 있다. 오라클의 옵티마이져가 재치(?)를 발휘해 서브쿼리를 인라인-뷰처러 만들어 처리한 것이다. 실행계획의 총 Buffers 수치는 11,867이다. 기억하기 바란다.
고객별 마지막 주문을 구하는 또 다른 방법은 ROW_NUMBER 분석함수를 사용하는 것이다. 아래와 같다.
[SQL-2] 고객별 마지막 주문 가져오기 – ROW_NUMBER
SELECT T0.*
FROM (
SELECT T1.*
,ROW_NUMBER() OVER(PARTITION BY T1.CUS_ID ORDER BY T1.ORD_SEQ DESC) RNK
FROM T_ORD_JOIN T1
) T0
WHERE T0.RNK = 1;
분석함수의 OVER절과 PARTITION BY만 이해하고 있다면 어렵지 않게 사용할 수 있는 방법이다. (분석함수는 SQL BOOSTER 본서에서 자세하게 다루고 있다.) T_ORD_JOIN 테이블을 한 번만 접근하면 되므로 WHERE절 서브쿼리 방식보다 성능이 좋을 것 같지만, 오히려 좋지 못하다. T_ORD_JOIN 전체를 모두 읽어야 하기 때문이다. 실행계획을 확인해 보면 총 26,488의 Buffers가 발생한다.
지금 상황에서 필자가 추천하는 방법은 다음과 같다.
[SQL-3] 고객별 마지막 주문 가져오기 – M_CUS를 사용
SELECT T2.*
FROM (
SELECT (SELECT MAX(B.ORD_SEQ) FROM T_ORD_JOIN B WHERE B.CUS_ID = A.CUS_ID) ORD_SEQ
FROM M_CUS A
) T1
,T_ORD_JOIN T2
WHERE T1.ORD_SEQ = T2.ORD_SEQ
인라인-뷰에서(3~4 라인) M_CUS 테이블과 스칼라 서브쿼리로 고객별 마지막 ORD_SEQ를 구한 후에 T_ORD_JOIN과 조인 처리하는 방법이다. 실행계획을 살펴보면 다음과 같다.
[실행계획-3] 고객별 마지막 주문 가져오기 – M_CUS를 사용
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 90 |00:00:00.01 | 458 |
| 1 | NESTED LOOPS | | 1 | 90 |00:00:00.01 | 458 |
| 2 | NESTED LOOPS | | 1 | 90 |00:00:00.01 | 368 |
| 3 | INDEX FULL SCAN | PK_M_CUS | 1 | 90 |00:00:00.01 | 2 |
|* 4 | INDEX UNIQUE SCAN | PK_T_ORD_JOIN | 90 | 90 |00:00:00.01 | 366 |
| 5 | SORT AGGREGATE | | 90 | 90 |00:00:00.01 | 183 |
| 6 | FIRST ROW | | 90 | 90 |00:00:00.01 | 183 |
|* 7 | INDEX RANGE SCAN (MIN/MAX)| X_T_ORD_JOIN_TEST | 90 | 90 |00:00:00.01 | 183 |
| 8 | TABLE ACCESS BY INDEX ROWID | T_ORD_JOIN | 90 | 90 |00:00:00.01 | 90 |
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."ORD_SEQ"=)
7 - access("B"."CUS_ID"=:B1)
총 Buffers가 458로 획기적으로 줄어들었다.
필자는 여기서 이 방법을 추천했다. 하지만, 항상 이 방법이 좋은 것은 아니다. SQL BOOSTER에서 사용하고 있는 예제는 고객 수가 많지 않다. 그렇기 때문에 M_CUS를 이용해 마지막 주문을 가져오는 방법이 효율적이었던 것이다. 만약에 고객 수가 매우 많다면 이와 같은 방법은 성능이 더 나쁠 수 있다. 언제나 실행계획을 보고, 상황에 따라 좋은 방법을 찾아서 쓸 수 있어야 한다.
(2) 고객별 월별 마지막 주문 데이터 가져오기.
이번에는 고객+월별(ORD_YM=SUBSTR(T1.ORD_YMD,1,6)) 마지막 주문을 가져오는 SQL을 살펴보자.
테스트를 위해 인덱스를 먼저 만들도록 한다.
X_T_ORD_JOIN_TEST_2 인덱스 생성
CREATE INDEX X_T_ORD_JOIN_TEST_2 ON T_ORD_JOIN(CUS_ID,ORD_YMD,ORD_SEQ);여기서 가장 안 좋은 방법은 WHERE절의 서브쿼리를 사용하는 경우다. 아래와 같이 말이다. (아래 SQL은 필자 노트북에서 실행 결과가 나오지 않았다. 독자 여러분도 마찬가지일 수 있다.)
[SQL-4] 고객별 월별 마지막 주문 가져오기 – WHERE 절 서브쿼리
SELECT *
FROM T_ORD_JOIN T1
WHERE T1.ORD_SEQ = (SELECT MAX(A.ORD_SEQ)
FROM T_ORD_JOIN A
WHERE A.CUS_ID = T1.CUS_ID
AND A.ORD_YMD LIKE SUBSTR(T1.ORD_YMD,1,6)||'%'
)
ORDER BY T1.CUS_ID, T1.ORD_YMD DESC;
위 SQL은 필자 환경에서 매우 오랜 시간이 지나도 결과가 나오지 않았다. EXPLAIN PLAN FOR를 이용해 실행계획만 확인해보니 무지막지한 방법으로 SQL이 실행되고 있었다. 실행계획은 다음과 같다.
[실행계획-4] 고객별 월별 마지막 주문 가져오기 – WHERE 절 서브쿼리
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1792 | 136K| | 584K (95)| 01:56:58 |
|* 1 | FILTER | | | | | | |
| 2 | SORT GROUP BY | | 1792 | 136K| | 584K (95)| 01:56:58 |
|* 3 | HASH JOIN | | 5774M| 419G| 110M| 350K (92)| 01:10:04 |
| 4 | INDEX FAST FULL SCAN| X_T_ORD_JOIN_TEST_2 | 3224K| 73M| | 4237 (1)| 00:00:51 |
| 5 | TABLE ACCESS FULL | T_ORD_JOIN | 3224K| 166M| | 7257 (1)| 00:01:28 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T1"."ORD_SEQ"=MAX("A"."ORD_SEQ"))
3 - access("A"."CUS_ID"="T1"."CUS_ID")
filter("A"."ORD_YMD" LIKE SUBSTR("T1"."ORD_YMD",1,6)||'%')
실행계획의 Predicate Information을 보면 3번 단계에서 (A.CUS_ID = T1.CUS_ID) 조건으로 해시 조인이 되고 있다. 두 개의 T_ORD_JOIN 간에 같은 CUS_ID끼리 모두 조인이 되고 있는 것이다. 마치 카테시안-조인처럼 데이터가 연결되고 있는 것이다. 이번에는 오라클이 재치를 발휘하지 못했다.
위 SQL은 아래와 같이 인라인-뷰와 조인 조합으로 변경할 수 있다.
[SQL-5] 고객별 월별 마지막 주문 가져오기 – 인라인-뷰
SELECT T1.*
FROM (
SELECT A.CUS_ID
,SUBSTR(A.ORD_YMD,1,6) ORD_YM
,MAX(A.ORD_SEQ) MAX_ORD_SEQ
FROM T_ORD_JOIN A
GROUP BY A.CUS_ID
,SUBSTR(A.ORD_YMD,1,6)
) T0
,T_ORD_JOIN T1
WHERE T1.ORD_SEQ = T0.MAX_ORD_SEQ
ORDER BY T1.CUS_ID, T1.ORD_YMD DESC;
인라인-뷰에서 고객별 월별 마지막 ORD_SEQ를 구해서 T_ORD_JOIN과 조인하는 방법이다. 실행계획은 다음과 같다.
[실행계획-5] 고객별 월별 마지막 주문 가져오기 – 인라인-뷰
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 |00:00:01.64 | 42125 |
| 1 | SORT ORDER BY | | 1 | 50 |00:00:01.64 | 42125 |
|* 2 | HASH JOIN | | 1 | 1000 |00:00:02.94 | 42125 |
| 3 | VIEW | | 1 | 1000 |00:00:01.11 | 15668 |
| 4 | HASH GROUP BY | | 1 | 1000 |00:00:01.11 | 15668 |
| 5 | INDEX FAST FULL SCAN| X_T_ORD_JOIN_TEST_2 | 1 | 3224K|00:00:00.35 | 15668 |
| 6 | TABLE ACCESS FULL | T_ORD_JOIN | 1 | 3224K|00:00:00.34 | 26457 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ORD_SEQ"="T0"."MAX_ORD_SEQ")
인라인-뷰를 사용한 방법의 총 Buffers는 42,125다. 인라인-뷰 결과와 T_ORD_JOINN을 해시-조인 처리하고 있다. 필자 생각에는 인라인-뷰 결과와 T_ORD_JOIN을 NL 조인 하는 것이 성능이 더 좋을 것이라 생각된다. 인라인-뷰의 결과가 천 건밖에 안되기 때문이다. (직접 힌트를 사용해 NL 조인으로 테스트해보기 바란다.)
고객별 월별 마지막 주문을 구하기 위해서도 ROW_NUMBER 분석함수를 사용할 수 있다. 아래와 같이 PARTITION BY에 SUBSTR(T1.ORD_YMD,1,6)을 추가하면 된다.
[SQL-5] 고객별 월별 마지막 주문 가져오기 – ROW_NUMBER
SELECT T0.*
FROM (
SELECT T1.*
,ROW_NUMBER() OVER(PARTITION BY T1.CUS_ID, SUBSTR(T1.ORD_YMD,1,6)
ORDER BY T1.ORD_SEQ DESC) RNK
FROM T_ORD_JOIN T1
) T0
WHERE T0.RNK = 1
위 SQL의 실행계획을 확인해 보면, 총 26,490의 Buffers가 발생한다.
‘(1) 고객별 마지막 주문 데이터 가져오기’에서 성능이 가장 좋았던 방법은 M_CUS를 이용한 방법이었다. 하지만 여기서는 주문년월까지 포함되어야 하므로 M_CUS를 이용한 방법을 쉽게 사용할 수 없다. 하지만 일자(C_BAS_YMD) 테이블을 이용하면 이를 해결 할 수 있다. 아래와 같다.
[SQL-6] 고객별 월별 마지막 주문 가져오기 – M_CUS와 C_BAS_YMD
SELECT T2.*
FROM (
SELECT (SELECT MAX(B.ORD_SEQ)
FROM T_ORD_JOIN B
WHERE B.CUS_ID = A.CUS_ID AND B.ORD_YMD LIKE D.BAS_YM||'%') ORD_SEQ
FROM M_CUS A
,(SELECT DISTINCT C.BAS_YM FROM C_BAS_YMD C WHERE C.BAS_YMD LIKE '2017%') D
) T1
,T_ORD_JOIN T2
WHERE T1.ORD_SEQ = T2.ORD_SEQ
ORDER BY T2.CUS_ID, T2.ORD_YMD DESC;
SQL의 7번 라인을 보면 C_BAS_YMD에서 2017년에 해당하는 월(BAS_YM) 데이터 12건을 가져온 후에 M_CUS와 카테시안-조인하고 있다. 이와 같이 하면, M_CUS의 고객별로 201701부터 201712까지의 데이터가 만들어진다. (현재 T_ORD_JOIN에는 2017년 데이터만 있기 때문에 고객별 2017년 월별 데이터만 만들면 된다.) M_CUS의 고객별 월별 데이터를 이용해 마지막 ORD_SEQ를 구한 후에 다시 T_ORD_JOIN과 조인하는 방법이다. 실행계획은 다음과 같다.
[실행계획-6] 고객별 월별 마지막 주문 가져오기 – M_CUS와 C_BAS_YMD
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts |A-Rows | A-Time |Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1000 |00:00:01.48 | 21226 |
| 1 | SORT ORDER BY | | 1 | 1000 |00:00:01.48 | 21226 |
| 2 | NESTED LOOPS | | 1 | 1000 |00:00:01.09 | 21226 |
| 3 | NESTED LOOPS | | 1 | 1000 |00:00:00.96 | 20226 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 1080 |00:00:00.01 | 472 |
| 5 | VIEW | | 1 | 12 |00:00:00.01 | 469 |
| 6 | HASH UNIQUE | | 1 | 12 |00:00:00.01 | 469 |
| 7 | TABLE ACCESS BY INDEX ROWID| C_BAS_YMD | 1 | 365 |00:00:00.01 | 469 |
|* 8 | INDEX RANGE SCAN | PK_C_BAS_YMD | 1 | 365 |00:00:00.01 | 3 |
| 9 | BUFFER SORT | | 12 | 1080 |00:00:00.01 | 3 |
| 10 | INDEX FAST FULL SCAN | PK_M_CUS | 1 | 90 |00:00:00.01 | 3 |
|* 11 | INDEX UNIQUE SCAN | PK_T_ORD_JOIN | 1080 | 1000 |00:00:01.29 | 19754 |
| 12 | SORT AGGREGATE | | 1080 | 1080 |00:00:01.11 | 17752 |
|* 13 | INDEX RANGE SCAN | X_T_ORD_JOIN_TEST_2| 1080 | 3224K|00:00:01.25 | 17752 |
| 14 | TABLE ACCESS BY INDEX ROWID | T_ORD_JOIN | 1000 | 1000 |00:00:00.18 | 1000 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("C"."BAS_YMD" LIKE '2017%')
filter("C"."BAS_YMD" LIKE '2017%')
11 - access("T2"."ORD_SEQ"=)
13 - access("B"."CUS_ID"=:B1 AND "B"."ORD_YMD" LIKE :B2||'%')
filter("B"."ORD_YMD" LIKE :B1||'%')
총 Buffers가 21,226으로 줄어들었다. 일자 테이블까지 가져와서 카테시안-조인 처리하긴 했지만, 지금까지 SQL 중에는 IO 성능이 제일 좋다.
(3) 고객별 일자별 마지막 주문 데이터 가져오기.
이번에는 고객+일자별 마지막 주문을 가져오는 SQL을 살펴보자. WHERE 절 서브쿼리는 보나마나 성능이 좋지 못할 것이다.
고객별 일자별 마지막 주문을 조회하기 위해 아래와 같이 인라인-뷰를 사용해보자.
[SQL-7] 고객별 일자별 마지막 주문 가져오기 – 인라인-뷰
SELECT T1.*
FROM (
SELECT A.CUS_ID
,A.ORD_YMD
,MAX(A.ORD_SEQ) MAX_ORD_SEQ
FROM T_ORD_JOIN A
GROUP BY A.CUS_ID
,A.ORD_YMD
) T0
,T_ORD_JOIN T1
WHERE T1.ORD_SEQ = T0.MAX_ORD_SEQ
ORDER BY T1.CUS_ID, T1.ORD_YMD DESC;
실행계획을 확인해 보면 다음과 같다.
[실행계획-7] 고객별 일자별 마지막 주문 가져오기 – 인라인-뷰
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2594 |00:00:00.79 | 23452 |
| 1 | SORT ORDER BY | | 1 | 2594 |00:00:00.79 | 23452 |
| 2 | NESTED LOOPS | | 1 | 2594 |00:00:00.79 | 23452 |
| 3 | NESTED LOOPS | | 1 | 2594 |00:00:00.78 | 20858 |
| 4 | VIEW | | 1 | 2594 |00:00:00.78 | 15668 |
| 5 | HASH GROUP BY | | 1 | 2594 |00:00:00.78 | 15668 |
| 6 | INDEX FAST FULL SCAN | X_T_ORD_JOIN_TEST_2 | 1 | 3224K|00:00:00.36 | 15668 |
|* 7 | INDEX UNIQUE SCAN | PK_T_ORD_JOIN | 2594 | 2594 |00:00:00.01 | 5190 |
| 8 | TABLE ACCESS BY INDEX ROWID| T_ORD_JOIN | 2594 | 2594 |00:00:00.01 | 2594 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("T1"."ORD_SEQ"="T0"."MAX_ORD_SEQ")
이번에는 인라인-뷰의 결과와 T_ORD_JOIN이 NL 조인으로 처리되고 있다. (‘(2)’에서 인라인-뷰를 사용한 방법은 해시-조인으로 처리되었었다.) NL 조인이면서 총 Buffers는 23,452다.
그렇다면, (1)번에서 추천한 방법인 M_CUS를 사용한 방법은 어떨까? 고객+일자별 마지막 주문을 가져와야 하므로 그 방법은 좋지 못할 것으로 예상된다. 스칼라-서브쿼리가 반복적으로 많이 실행되기 때문이다. 실제 테스트 해보도록 하자.
[SQL-8] 고객별 일자별 마지막 주문 가져오기 – M_CUS와 C_BAS_YMD
SELECT T2.*
FROM (
SELECT (SELECT MAX(B.ORD_SEQ) FROM T_ORD_JOIN B
WHERE B.CUS_ID = A.CUS_ID AND B.ORD_YMD = D.BAS_YMD) ORD_SEQ
FROM M_CUS A
,(SELECT C.BAS_YMD FROM C_BAS_YMD C WHERE C.BAS_YMD LIKE '2017%') D
) T1
,T_ORD_JOIN T2
WHERE T1.ORD_SEQ = T2.ORD_SEQ
ORDER BY T2.CUS_ID, T2.ORD_YMD DESC;
실행계획을 확인해보면 아래와 같다.
[실행계획-8] 고객별 일자별 마지막 주문 가져오기 – M_CUS와 C_BAS_YM
------------------------------------------------------------------------------------------------------
| Id | Operation | Name |Starts | A-Rows | A-Time |Buffers |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2594 |00:00:00.13 | 36526 |
| 1 | SORT ORDER BY | | 1 | 2594 |00:00:00.13 | 36526 |
| 2 | NESTED LOOPS | | 1 | 2594 |00:00:00.15 | 36526 |
| 3 | NESTED LOOPS | | 1 | 2594 |00:00:00.15 | 33932 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 32850 |00:00:00.01 | 4 |
| 5 | INDEX FULL SCAN | PK_M_CUS | 1 | 90 |00:00:00.01 | 1 |
| 6 | BUFFER SORT | | 90 | 32850 |00:00:00.01 | 3 |
|* 7 | INDEX RANGE SCAN | PK_C_BAS_YMD | 1 | 365 |00:00:00.01 | 3 |
|* 8 | INDEX UNIQUE SCAN | PK_T_ORD_JOIN | 32850 | 2594 |00:00:00.11 | 33928 |
| 9 | SORT AGGREGATE | | 32850 | 32850 |00:00:00.08 | 28739 |
| 10 | FIRST ROW | | 32850 | 2594 |00:00:00.06 | 28739 |
|* 11 | INDEX RANGE SCAN (MIN/MAX)| X_T_ORD_JOIN_TEST_2| 32850 | 2594 |00:00:00.06 | 28739 |
| 12 | TABLE ACCESS BY INDEX ROWID | T_ORD_JOIN | 2594 | 2594 |00:00:00.01 | 2594 |
------------------------------------------------------------------------------------------------------
총 Buffers가 36,526이다. 인라인-뷰를 사용한 방법보다 IO가 좋지 못하다.
그렇다면 인라인-뷰를 사용한 방법을 좀 더 향상시킬 수는 없을까? 다음과 같이 KEEP 분석함수와 ROWID를 사용하는 방법이 있다.
[SQL-9] 고객별 일자별 마지막 주문 가져오기 – 인라인-뷰와 KEEP
SELECT T1.*
FROM (
SELECT A.CUS_ID
,A.ORD_YMD
,MAX(A.ROWID) KEEP(DENSE_RANK FIRST ORDER BY A.ORD_SEQ DESC) RID
FROM T_ORD_JOIN A
GROUP BY A.CUS_ID
,A.ORD_YMD
) T0
,T_ORD_JOIN T1
WHERE T1.ROWID = T0.RID
KEEP을 이용해 고객, 일별 마지막 주문SEQ의 ROWID를 가져와서, ROWID로 T_ORD_JOIN에 바로 접근하는 방법이다. PK_T_ORD_JOIN 인덱스를 경유하지 않아 성능에 이득이 있다. 실행계획은 다음과 같다.
[실행계획-9] 고객별 일자별 마지막 주문 가져오기 – 인라인-뷰와 KEEP
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2594 |00:00:01.50 | 18262 |
| 1 | NESTED LOOPS | | 1 | 2594 |00:00:01.50 | 18262 |
| 2 | VIEW | | 1 | 2594 |00:00:01.50 | 15668 |
| 3 | SORT GROUP BY | | 1 | 2594 |00:00:01.50 | 15668 |
| 4 | INDEX FAST FULL SCAN | X_T_ORD_JOIN_TEST_2 | 1 | 3224K|00:00:00.63 | 15668 |
| 5 | TABLE ACCESS BY USER ROWID| T_ORD_JOIN | 2594 | 2594 |00:00:00.01 | 2594 |
----------------------------------------------------------------------------------------------------
총 18,262로 Buffers가 줄어들었다.
마무리하기 전에, 여기서 만들었던 테스트 인덱스를 모두 제거하도록 하자.
테스트 인덱스 제거
DROP INDEX X_T_ORD_JOIN_TEST;
DROP INDEX X_T_ORD_JOIN_TEST_2;
지금까지 살펴본 내용을 종합해 보면 아래와 같다.
상황과 방법에 따라 다양한 성능이 나오고 있다. 하나의 규칙에 얽매이지 않고 항상 고민하고 연구해보기 바란다.
오늘은 여기까지입니다. 감사합니다.
'SQL > SQL BOOSTER' 카테고리의 다른 글
| 빅 테이블의 용량 산정 (0) | 2023.12.12 |
|---|---|
| SQL BOOSTER 이어지는 이야기 .12 (0) | 2020.10.28 |
| SQL BOOSTER 이어지는 이야기 .11 (0) | 2020.10.28 |
| SQL BOOSTER 이어지는 이야기 .10 (0) | 2020.10.27 |
| SQL BOOSTER 이어지는 이야기 .07 (0) | 2020.10.26 |
| SQL BOOSTER 이어지는 이야기 .06 (0) | 2020.10.26 |
| SQL BOOSTER 이어지는 이야기 .05 (0) | 2020.10.25 |
| SQL BOOSTER 이어지는 이야기 .04 (0) | 2020.10.25 |