SQL BOOSTER 이어지는 이야기 .04

<SQL을 변경한 성능 개선 .02>

 

개발 현장에는 다양한 요구 사항의 SQL이 있다. 예를 들어, 주문(T_ORD_JOIN)이 두 건 이상인 고객의 주문 로우 데이터를 보여주는 SQL을 작성해야 한다고 가정해 보자. 아래와 같이 SQL을 작성할 수 있다.

 

SQL BOOSTER 에 이어지는 이야기들입니다.~!
SQL BOOSTER 를 보신 분들께 좀 더 도움을 드리고자 추가로 작성한 내용입니다.

www.aladin.co.kr/shop/wproduct.aspx?ItemId=216383877

SQL BOOSTER

설명의 편의상 반말체로 작성한 점 양해바랍니다.  pdf 파일도 첨부드리니 다운 받아 보셔도 됩니다.

SQL_Booster_이어지는이야기04.pdf

0.17MB

 

[SQL-1] 주문이 두 건 이상인 고객의 주문 로우 데이터 보여주기
SELECT  /*+ GATHER_PLAN_STATISTICS */
        T1.CUS_ID ,T1.ORD_YMD ,T1.ORD_QTY
FROM    (
            SELECT  A.CUS_ID
            FROM    T_ORD_JOIN A
            GROUP BY A.CUS_ID
            HAVING COUNT(*)>=2
        ) T0
        ,T_ORD_JOIN T1
WHERE   T1.CUS_ID = T0.CUS_ID
ORDER BY T1.CUS_ID ,T1.ORD_YMD;

 

인라인-뷰에서 주문이 두 건 이상인 고객을 찾아서 T_ORD_JOIN과 다시 조인해서 조회하는 SQL이다. 위 SQL의 실행계획은 다음과 같다.

[실행계획-1] 주문이 두 건 이상인 고객의 주문 데이터 보여주기
-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |      1 |   1100 |00:00:02.98 |   47234 |
|   1 |  SORT ORDER BY                |                |      1 |   1100 |00:00:02.98 |   47234 |
|   2 |   NESTED LOOPS                |                |      1 |   3224K|00:00:02.75 |   47228 |
|   3 |    NESTED LOOPS               |                |      1 |   3224K|00:00:00.96 |   18187 |
|   4 |     VIEW                      |                |      1 |     90 |00:00:00.32 |    9025 |
|*  5 |      FILTER                   |                |      1 |     90 |00:00:00.32 |    9025 |
|   6 |       HASH GROUP BY           |                |      1 |     90 |00:00:00.31 |    9025 |
|   7 |        INDEX FAST FULL SCAN   | X_T_ORD_JOIN_1 |      1 |   3224K|00:00:00.23 |    9025 |
|*  8 |     INDEX RANGE SCAN          | X_T_ORD_JOIN_1 |     90 |   3224K|00:00:00.34 |    9162 |
|   9 |    TABLE ACCESS BY INDEX ROWID| T_ORD_JOIN     |   3224K|   3224K|00:00:00.96 |   29041 |
-------------------------------------------------------------------------------------------------

실행계획을 보면, 7번 단계에서 X_T_ORD_JOIN_1 인덱스를 FAST FULL SCAN해서 두 건 이상인 고객을 찾아 낸 후, 다시 T_ORD_JOIN과 조인을 처리하고 있다. SQL을 작성한 대로 실행계획이 만들어졌다. 총 Buffers를 보면 47,234다. X_T_ORD_JOIN_1 인덱스를 모두 읽어서 처리했기 때문이다.

사실 T_ORD_JOIN 테이블의 데이터는 해당 테스트에 적당하게 구성되어 있지 않다. 모든 고객의 주문이 두 건 이상이기 때문이다. 그냥 조회해도 결과는 같다.

어쨌든 현업의 요구 사항 자체가 이와 같다면, 데이터 구성에 상관 없이 위와 같은 SQL을 작성해야만 한다. 조건에 맞는 모든 데이터를 조회해야 한다면, 위의 SQL도 나쁘다고 할 수는 없다. 하지만 우선 조회가 가능한 몇 건을 조회해야 한다면 어떨까? 우선 몇 건 조회만을 위해서는 너무 많은 IO가 발생했다.

아래와 같이 SQL을 변경해보면 어떨까?

[SQL-2] 주문이 두 건 이상인 고객의 주문 로우 데이터 보여주기 – EXISTS 사용
SELECT  /*+ GATHER_PLAN_STATISTICS */
        T1.CUS_ID ,T1.ORD_YMD ,T1.ORD_QTY
FROM    T_ORD_JOIN T1
WHERE   EXISTS(
            SELECT  /*+ USE_NL(A) */
                    *
            FROM    T_ORD_JOIN A
            WHERE   A.CUS_ID = T1.CUS_ID
	  AND     A.ROWID != T1.ROWID)
ORDER BY T1.CUS_ID ,T1.ORD_YMD;

T_ORD_JOIN을 CUS_ID와 ORD_YMD 순서로 읽어가면서, CUS_ID는 같으면서 ROWID가 다른 데이터가 존재할 때만 조회를 하고 있다. 실행계획을 제어하기 위해 EXISTS 서브쿼리에 USE_NL 힌트도 사용했다. [SQL-1]의 결과와 정렬 순서는 약간 차이가 있을 수 있지만, 우선 몇 건만 조회하기에는 성능이 최적인 SQL이다. 실행계획을 살펴보자.

[실행계획-2] 주문이 두 건 이상인 고객의 주문 로우 데이터 보여주기 – EXISTS 사용
------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |      1 |   1100 |00:00:00.01 |      94 |
|   1 |  NESTED LOOPS SEMI           |                |      1 |   1100 |00:00:00.01 |      94 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_ORD_JOIN     |      1 |   1100 |00:00:00.01 |      60 |
|   3 |    INDEX FULL SCAN           | X_T_ORD_JOIN_2 |      1 |   1100 |00:00:00.01 |      28 |
|*  4 |   INDEX RANGE SCAN           | X_T_ORD_JOIN_1 |   1100 |   1100 |00:00:00.01 |      34 |
------------------------------------------------------------------------------------------------

총 Buffers가 94로 개선되었다. SQL-1은 총 Buffers가 47,234였다.

SQL을 변경해서 성능을 개선했다. 물론, 우선 몇 건만 보여주는 경우이면서 적절한 인덱스가 있을 때만 사용할 수 있는 방법이다.

 

현업의 요구 사항은 무궁무진하다. 요구 사항에 따라 SQL을 무작정 작성할 것이 아니라, 성능을 향상 할 방법을 고민해 보고 다양한 방법으로 SQL을 작성해보기 바란다. SQL은 언제나 변화무쌍하다.

 

 

오늘은 여기까지입니다. 감사합니다.