대용량 데이터베이스 솔루션 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가지 경우를 다 연습해야 한다.

대용량 데이터베이스솔루션 2

작가

이화식

출판

엔코아컨설팅

발매

1999.11.20

평점

리뷰보기