Post List

2015년 1월 9일 금요일

대용량 데이터베이스 솔루션 2권 #12 SUB-QUERY

SUB-QUERY를 이용한 DATA LINK



JOIN 은 2개의 Table이 동격으로 연결되지만,
Sub-Query 과 Main-Query는 엄연히 주종관계로 연결된다.
1 : M, M : M 의 관계에서 Sub-Query 쪽의 M 은 1로 처리가 되기 때문에
관계 형태에 따라 JOIN 과 Sub-query 는 전혀 다른 집합이 생성되기도 한다.

Sub-query의 종류

1. 먼저 수행하는 Sub-query : 제공자

제공자가 되기위해서는 2가지 조건을 만족해야 한다.
- Sub-query의 수행결과를 Main-query의 처리주관 INDEX에 제공할 수 있어야 한다.
- Sub-query 내에 Main-query Column이 없어야 한다.

SELECT COL1, COL2
  FROM TAB1 X
 WHERE KEY1 IN (SELECT KEY2
                  FROM TAB2 Y
                 WHERE Y.COL1 ...
                   AND Y.COL2 ...);
NESTED LOOPS
  VIEW
    SORT(UNIQUE)
      TABLE ACCESS BY ROWID OF TAB2
        INDEX RANGE SCAN OF COL1_IDX
  TABLE ACCESS BY ROWID OF TAB1
    INDEX RANGE SCAN OF KEY1_IDX

- 제공자가 무조건 먼저 수행되지는 않는다. Optmizer가 판단하여 Main-query보다 나중에 수행되는 JOIN이 되기도 한다.
- 만약 Sort-Merge, Hash JOIN으로 수행될 때는 제공자 역할을 하지 못한다.
- 제공자의 경우는 M을 1로 만들기 위해 UNIQUE하게 만들어서 제공한다.
  그러므로 Sub-query 사용시 M : M 연결은 M : 1 로 연결된 결과가 생성된다.

제공자로 사용할 때는 INDEX 전략과 잘 맞아떨어져야 한다.
SELECT COL1, COL2
  FROM TAB1
 WHERE KEY1 LIKE 'ABC%'
   AND KEY2 IN (SELECT KEY2
                  FROM TAB2 Y
                 WHERE Y.COL1 ...
                   AND Y.COL2 ...);
NESTED LOOPS
  VIEW
    SORT(UNIQUE)
      TABLE ACCESS BY ROWID OF TAB2
        INDEX RANGE SCAN OF COL1_IDX
  TABLE ACCESS BY ROWID OF TAB1
    INDEX RANGE SCAN OF IDX_KEY1_KEY2


INDEX 가 KEY1 + KEY2로 되어 있다면 어떻게 될까 ?
이건 INDEX 에서는 상식이지만 앞에 Column가 LIKE 이므로 Sub-query가 KEY1의 범위에 따라 매번 수행된다.
하지만 실행계획을 한번보자. 앞의 예제의 실행계획과 차이가 아에 없다.
그러나 KEY1 LIKE, KEY2 = 형태의 범위처리가 Sub-query가 제공한 만큼 반복수행된다.
즉, INDEX 선두 Column이 '='이 아니므로 KEY2는 단지 CHECK 기능만 담당한다.
    (제공자 역할이 아닌 확인자 역할을 하게 된다.)

2. 나중에 수행하는 Sub-query : 확인자

확인자는 Main-query가 먼저 수행되면서 CHECK 조건으로 활용된다.
먼저 Main-query에서 1개의 ROW가 추출되면 그 ROW의 값을 상수로 전달받아 Sub-query가 수행되는 형식이다.
SELECT COL1, COL2
  FROM TAB1 X
 WHERE KEY1 IN (SELECT KEY2
                  FROM TAB2 Y
                 WHERE Y.COL1 >= X.FLD1
                   AND Y.COL2 ...)
   AND EXISTS (SELECT ''
                 FROM TAB3 Z
                WHERE Z.COL = X.FLD);
- Sub-query 내에 Main-Query Column이 있으면 논리적으로 제공자 역할을 할 수 없으므로 확인자 역할을 수행한다.
- EXISTS를 사용한 경우는 대부분의 경우 Sub0query 내에 Main-query Column이 존재하고 결과는 제공받을 Main-query Column이 존재하지 않으므로 대게 확인자 역할을 하게 된다.
- 간혹 Main-query Table과 Sub-query Table이 SORT_MERGE JOIN 으로 수행되는 경우도 있다.
- 제공자 역할로 만든 Sub-query가 확인자 역할을 하게 되면 심각한 Overhead가 발생할 수도 있다.

확인자 Sub-query의 실행계획을 한번 보자

SELECT 사번, 성명, 직급, 입사일
  FROM 사원 X
 WHERE 부서 = '연구소'
   AND 사번 IN (SELECT 사번
                 FROM 가족 Y
                WHERE Y.사번 = X.사번
                  AND Y.생년월일 < '19500101');
FILTER
  TABLE ACCESS (BY ROWID) OF 사원
        INDEX (RANGE SCAN) OF 부서_IDX
  TABLE ACCESS (BY ROWID) OF 가족
    INDEX (RANGE SCAN) OF PK_INDEX


가족중 1950년 이전 출생자가 3명이 있는 경우 1 : M 관계이므로 사원 Data가 3번 나타나지만,
Sub-query의 경우 해당 조건이 나타날 경우 1번만 CEHCK하고 STOP이므로 1번만 나타난다.
1 : M 관계를 1 : 1 관계로 연결하며, M : M 관계를 M : 1 관계로 연결되는 결과가 생성된다.

JOIN 처럼 SORT_MERGE형태로 수행될 수 있다.

SELECT 사번, 성명, 직급, 입사일
  FROM 사원
 WHERE 직책 = '과장'
   AND 부서 IN (SELECT 부서
                 FROM 근태
                WHERE 일자 LIKE '201407%'
                  AND 근태유형 = '무단결근');
MERGE JOIN
  SORT (JOIN)
    TABLE ACCESS (FULL) OF 사원
  SORT (JOIN)
    VIEW
      SORT (UNIQUE)
        TABLE ACCESS (BY ROWID) OF 근태
          INDEX (RANGE SCAN) OF IDX1

사원Table 과 근태Table이 부서로 M : M 관계이다.
연결조건인 '부서' Column에 양쪽 모두 INDEX가 없다면 높은 확률로 SORT_MERGE 처리된다.
M : M JOIN 이므로 Sub-query 결과는 UNIQUE를 만든 후 JOIN을 수행한다.

FILTER 형식의 수행

SELECT *
  FROM ORDER X
 WHERE ORDDATE LIKE '201407%'
   AND EXISTS (SELECT ''
                 FROM CUST Y
                WHERE Y.CUST_NO = X.CUST_NO
                  AND CUST_TYPE = '1')
FILTER
  TABLE ACCESS BY ROWID OF ORDER
        INDEX RANGE SCAN OF ORDDATE_IDX
  TABLE ACCESS BY ROWID OF CUST
    INDEX UNIQUE SCAN CUST_PK




수행속도는 다음과 같다.
1. ORDER Table에서 조건에 맞는 ROW 들은 RANGE SCAN 한다.
2. 가장 먼저 찾은 ROW로 CUST Table에서 찾는다.
3. 조건에 맞는것을 찾으면 Memory 상 Buffer에 저장한다. ( 11 : 1 )
4. 이제부터 11 조건은 CUST에 가서 찾지 않고 Buffer 상에서 Check 한다.
5. Buffer 상에 없는 ROW는 2번과정을 반복하고, 있으면 바로 Memory에서 Check하고 넘어간다.

FILTER 의 경우 Hit Ratio의 영향을 많이 받는다.
경우에 따라서는 더 느린 경우도 많다.

 Access 경로와 수행속도

증권회사 전산 System에서 고객의 주문Table에 2000만건이 있는데, 그중 1달Data가 500만건 정도가 있다.
INDEX는 CUST_CO + REQ_YM 으로 잡혀있다.

UPDATE REQT X                                 -- 2000만 건 FULL SCAN
   SET IN_AMT = IM_AMT + :AMT
 WHERE REQ_YM = '201407'
   AND CUSTNO IN (SELECT CUSTNO               -- 500만 회 반복 수행 : 확인자
                    FROM CUST Y
                   WHERE PAY_CUST = :CUST
                     AND X.CUST = Y.CUST)      -- 수행시간 : 16,000 초 (4시간 반 정도?)
위 SQL문 마지막에 보면 X.CUST = Y.CUST 가 있다.
저걸 왜 적어놨을까 ?
AND CUST IN (SELECT CUST 라는거 자체가 X.CUST = Y.CUST 를 수행하는건데
왜 굳에 하나 더 적어서 제공자가 될 수 있는 조건을 확인자로 만들었을까 ?
UPDATE REQT X                                  -- 500만건 해당 고객만 처리
   SET IN_AMT = IM_AMT + :AMT
 WHERE REQ_YM = '201407'
   AND CUSTNO IN (SELECT CUSTNO                -- 1회만 먼저 수행 : 제공자
                    FROM CUST Y
                   WHERE PAY_CUST = :CUST)     -- 수행시간 : 0.1 초

ANTI JOIN

SELECT COUNT(*)
  FROM TAB1
 WHERE COL1 LIKE 'ABC%'
   AND COL2 NOT IN (SELECT FLD2
                      FROM TAB2
                     WHERE FLD3 LIKE '2013%');
FILTER
  TABLE ACCESS FULL OF TAB1
    INDEX RANGE SCAN OF COL1_INDEX
  TABLE ACCESS BY ROWID OF TAB2
    INDEX TANGE SCAN OF FLD3_INDEX


COL1 의 처리범위가 매우 넓다면 Sub-query가 다량의 Random Access를 발생하게 되어 엄청난 Overhead가 발생한다.
이럴 땐 SORT_MERGE ANTI JOIN 이나 HASH ANTI JOIN 으로 실행계획이 되도록 유도하는게 좋다.

1. MERGE ANTI JOIN

SELECT COUNT(*)
  FROM TAB1
 WHERE COL1 LIKE 'ABC%'
   AND COL2 IS NOT NULL
   AND COL2 NOT IN (SELECT /*+ MERGE_AJ */
                           FLD2
                      FROM TAB2
                     WHERE FLD3 LIKE '2013%'
                       AND FLD2 IS NOT NULL);
MERGE JOIN (ANTI)
  SORT (JOIN)
    TABLE ACCESS BY ROWID OF TAB11
      INDEX RANGE SCAN OF COL1_INDEX
  SORT (UNIQUE)
    TABLE ACCESS BY ROWID OF TAB2
      INDEX TANGE SCAN OF FLD3_INDEX



IS NOT NULL을 넣어야만 MERGE로 실행계획이 생긴다.

2. HASH ANTI JOIN

SELECT COUNT(*)
  FROM TAB1
 WHERE COL1 LIKE 'ABC%'
   AND COL2 IS NOT NULL
   AND COL2 NOT IN (SELECT /*+ HASH_AJ */
                           FLD2
                      FROM TAB2
                     WHERE FLD3 LIKE '2013%'
                       AND FLD2 IS NOT NULL);
HASH JOIN (ANTI)
  TABLE ACCESS BY ROWID OF TAB11
    INDEX RANGE SCAN OF COL1_INDEX
  VIEW
    TABLE ACCESS BY ROWID OF TAB2
      INDEX TANGE SCAN OF FLD3_INDEX




이것도 IN NOT NULL 을 넣어야만 가능하고, HINT만 MERGE_AJ 를 HASH_AJ 로 바꿨는데, 실행계획이 바뀌었다.

Sub-query를 이용한 부분범위처리
SELECT X.COL1, X.COL2, MIN(X.COL4), MIN(X.COL5)
  FROM TAB1 X, TAB2 Y
 WHERE X.COL1 = Y.FLD1
   AND X.COL2 = Y.FLD2
   AND X.COL3 BETWEEN '11' AND '99'
   AND Y.FLD3 LIKE '201407%'
 GROUP BY COL1, COL2
HAVING SUM(FLD4) > 0;
위의 SQL문은 전체범위 처리이다. 이것을 Sub-query를 이용하여 부분범위처리로 유도가 가능하다.
SELECT 에 TAB1의 Column 만을 출력하기 때문에 Sub-query로는 TAB2를 넣어야 한다.
그러니 TAB2의 처리범위가 보다 좁다면 Sub-query를 제공자로 만들어주는게 좋고,
TAB1의 처리범위가 보다 좁다면 TAB2를 확인자로 만들어서 FILTER 처리하도록 유도하는게 더 좋다.
JOIN에서 더 똑똑한 조건을 가진 TABLE을 먼저 Driving 되도록 유도해주는 것이 더 좋은데 그것과 같은거라고 생각하면 된다.

SELECT *
  FROM TAB1
 WHERE (COL1, COL2)
    IN (SELECT FLD1, FLD2            -- 제공자 : TAB2가 똑똑
          FROM TAB2
         WHERE FLD3 LIKE '201407%'
         GROUP BY COL1, COL2
         HAVING SUM(FLD4) > 0)
   AND COL3 BETWEEN '11' AND '99';
SELECT COL1, COL2, COL4, COL5
  FROM TAB1
 WHERE COL3 BETWEEN '11' AND '99'
   AND EXISTS (SELECT ''            -- 확인자 : TAB1이 똑똑
                 FROM TAB2
                WHERE FLD1 = COL1
                  AND FLD2 = COL2
                  AND FLD3 LIKE '201407%'
                GROUP BY FLD1, FLD2
               HAVING SUM(FLD4) > 0);


발생Data의 목록 처리 


위 프로그램에서 총 60국가 중 실제 실적 Data가 있는 것은 10개 정도밖에 없다. 그래서 사용자들이 국가를 선택하고 검색을 눌렀는데, Data가 없다고 뜨는 경우가 많아서 불편하다고 실제로 Data가 있는 국가만 Combo-Box로 나오게 해달라고 사용자의 요구가 있었다. 사실 이런 경우는 현업에서 정말 흔하게 자주 접하게 된다. 그래서 Combo-Box를 누를때마다 SQL문을 DB CALL로 실행해서 Combo-Box에 목록을 만들어주도록 수정했는데, 문제는 Combo-Box를 누를때마다 엄청난 시간을 기다려야 한다는 것이다. 그 SQL문은 다음과 같다.
SELECT DISTINCT 국가명
  FROM 국가 X, 수출실적 Y
 WHERE X.국가CODE = Y.국가CODE
   AND Y.수출년월 BETWEEN '201301' AND '201312';
세상에 DISTINCT 라니... 현업에서 왠만하면 절대 쓰지 말아야 할 키워드 이다.
다량 Data를 가진 수출실적 Table을 전체범위처리하게 된다. 그래서 속도가 느릴 수 밖에 없다.
어차피 우리가 필요한건 실적이 있냐 없냐다.
각 국가별로 실적Table에 1 Row라도 있다는걸 확인하면 된다.
국가 Table이 실적Table에 비해 훨씬 Data가 작기 때문에 확인자 Sub-query로 유도하면 된다.
SELECT 국가명
  FROM 국가 X
 WHERE EXISTS (SELECT ''
                 FROM 수출실적
                WHERE X.국가CODE = Y.국가CODE
                  AND Y.수출년월 BETWEEN '201301' AND '201312');
MIN, MAX 값을 가진 ROW Access

특정 시점이나 기간 동안에서 가장 높은값(MAX) 또는 가장 낮은값(MIN)을 찾을 경우가 많다.
이럴땐 어떻게 처리해야 할까 ?

예를 들어서 2013년 12월 31일 기준으로 고객의 최대 금액은 얼마였고, 그건 언제 바뀐 것인가를 찾아야 할때 어떻게 해야할까 ?
SELECT 금액, 변경일
  FROM HISTORY
 WHERE SEQ = (SELECT MAX(SEQ)
                FROM HISTORY
               WHERE 변경일 < '20131231'
                 AND CUSTID = :V_CUSTID);
이렇게 하면 될듯하다.

다른 SQL문을 하나 더 보자.
SELECT 종목, 고객번호, 변경회차, 변경일, 금액
  FROM 변경내역 X
 WHERE 변경회차 = (SELECT MAX(Y.변경회차)
                    FROM 변경내역 Y
                   WHERE Y.고객번호 = X.고객번호
                     AND Y.변경일 LIKE '201312%')
   AND 종목 = '15'
   AND 변경일 LIKE '201312%';
- Sub-query는 확인자 역할을 담당한다.
- 모든 처리 대상에 대해 중복 Access가 발생하며,
- 종목 + 변경일 , 고객번호 + 변경일 의 2개의 INDEX가 필요하다.
- 부분범위처리가 더 유리한 경우 이런식으로 하면 된다.

만약 전체범위처리가 더 유리한 경우는 아래와 같이 처리하면 된다.
SELECT '15' 종목, 고객번호,
       SUBSTR(VAL,1,3) 변경회차,
       SUBSTR(VAL,4,8) 변경일,
       SUBSTR(VAL,12,15) 금액
  FROM (SELECT 고객번호,
               MAX(RPAD(변경회차,3)||변경일||금액) VAL
          FROM 변경내역
         WHERE 종목 = '15'
           AND 변경일 LIKE '201312%'
         GROUP BY 고객번호);
- 결합한 Column의 MAX를 취해 분할하였다.
- 모든 처리대상에 대해 한번 Access 하였으며
- 종목 + 변경일 Access만 필요하다.
- 전체범위 처리를 하였다.

전체적인 수행속도는 아래가 당연히 더 빠르겠지만, 경우에 따라서는 부분범위 처리를 해야할 경우도 있으므로 2가지 경우를 다 연습해야 한다.

작가
이화식
출판
엔코아컨설팅
발매
1999.11.20
평점
블로거의 오늘의 책에 참여한 포스트 입니다

댓글 없음:

댓글 쓰기