DevStarSJ.github.io 으로 이사했습니다.: 대용량 데이터베이스 솔루션 2권 #17 IN을 활용한 Access 효율화

4.2 IN을 활용한 ACCESS 효율화

* IN 연산자

1. 수학적 의미
    A * (B + C) = AB + AC
   WHERE A AND ( B OR C) => (A AND B) OR (A AND C)
   WHERE A = 'A' AND B IN ('1', '3') => WHERE (A = '1' AND B = '1') OR (A = '1' AND B = '3')

2. 기하학적 의미

=> COL BETWEEN 'A' AND 'G'

=> COL IN ('A', 'B', 'C', 'D', 'E', 'F', 'G')
(Optimizer 수행시 실행계획이 다르다.)

* IN의 CONCATENATION 실행계획

SELECT * FROM TAB1
 WHERE COL1 = 'B'
   AND COL2 BETWEEN '111' AND '112'

TABLE ACCESS BY ROWID OF TAB1

INDEX RANGE SCAN OF A_INDEX

SELECT * FROM TAB1
 WHERE COL1 = 'B'
   AND COL2 IN ('112', '111') -- Optimizer는 뒤에서부터 실행

CONCATENATION -- 2개의 실행계획

TABLE ACCESS BY ROWID OF TAB1
INDEX RANGE SCAN OF INDEX1
TABLE ACCESS BY ROWID OF TAB1
INDEX RANGE SCAN OF INDEX1

* SQL 내에서 WHERE 조건 효율화

SELECT ...
  FROM TAB1
 WHERE COL1 = 'ABC'
   AND COL2 LIKE '12%'
   AND COL3 = '1234'
   AND CLO4 > 10000
   AND COL5 = 'OPER';

* INDEX : COL1 + COL2 + COL3 로 되어 있을때
* 여당 : COL1, COL2, COL3 (INDEX 내의 Column)
* 여당내 주류 : COL1, COL2 (INDEX를 활용하는 Column)
* 여당내 비주류 : COL3 (INDEX를 사용하지 못하는 Column)
* 야당 : COL4, COL5 (INDEX에 없는 Column)
* 효율화란 ?
- INDEX 전략 : 능력있는 야당을 어떻게 여당으로 영입할 것인가 ?
- IN의 활용 : 당내 비주류를 어떻게 주류로 끌어들일 것인가 ?

* IN 의 효율화 사례

INDEX : 상품 + 부서코드 + 매출일자

매출의 90%가 PRINTER 라고 할때

SELECT ...
  FROM TAB1
 WHERE 상품 = 'PRINTER'
   AND 매출일자 BETWEEN '19980302' AND '19980303';

부서코드가 조건에 없어서 매출일자는 INDEX를 활용 못한다. 이건 Table의 90% 범위를 Single-Block Index-Scan을 해야한다. 그래 ? 그럼 부서코드를 조건으로 주면되지머.

SELECT ...
  FROM TAB1
 WHERE 상품 = 'PRINTER'
   AND 부서코드 LIKE '%'
   AND 매출일자 BETWEEN '19980302' AND '19980303';

장난하냐 ? LIKE '%' ? 저건 조건을 준게 아니다. 여전히 처음꺼랑 실행계획이 같다.

SELECT ...
  FROM TAB1
 WHERE 상품 = 'PRINTER'
   AND 부서코드 IN (SELECT 부서코드 FROM 부서
                    WHERE 부서구분 = 'S')
   AND 매출일자 BETWEEN '19980302' AND '19980303';

판매부서에서만 찾으면 되기 때문에 IN을 사용하여 부서코드에 조건을 주었다. 이제는 2일간의 자료만 INDEX-SCAN 하게 된다.

* IN을 활용한 실행계획 개선방법

1. 상수값을 이용한 IN 조건 추가

INDEX : 상품 + 처리구분 + 판매일자 로 구성
처리구분은 1 ~ 4 의 정수값

조회 Form의 UI 구성
- 상품 : 1개의 Text Input
- 구분 : 2개의 Text Input을 이용한 ? ~ ? 표현
- 판매일자 : Calender를 이용한 날짜입력

SELECT *
  FROM TAB1
 WHERE 상품 = 'PRINTER'
   AND 처리구분 BETWEEN :VAL1 AND :VAL2
   AND 판매일자 LIKE '201407%';

처리구분이 = 이 아니라 BETWEEN이라 판매일자를 INDEX에서 활용이 불가능하다.

이런 경우에는 처리구분을 BETWEEN 대신 상수값을 이용하여 IN 으로 대입하는게 좋다.
가능하다면 구분 UI를 Text Input에서 Checkbox 4개를 이용하여 1, 2, 3, 4를 각각 Check하게 하여 Check 되었을 경우는 해당값, 안되었을땐 NULL로 처리하게 하는게 좋다.
NULL은 IN 안에 있어도 아무런 영향을 미치지 않는다.

SELECT *
  FROM TAB1
 WHERE 상품 = 'PRINTER'
   AND 처리구분 IN (:VAL4, :VAL3, :VAL2, :VAL1)
   AND 판매일자 LIKE '201407%';

Parsing은 역순으로 일어나므로 IN 안에 값도 역순으로 넣어줘야 INDEX를 순서대로 SCAN하게 된다.

2. Sub-query를 이용한 IN 조건 추가 (현존Table을 이용하여 집합으로 다리를 바꿈)

INDEX : 상품 + 부서 + 판매일자 로 구성

SELECT *
  FROM TAB1
 WHERE 상품 = 'PRINTER'
   AND 부서 LIKE :VAL1||'%'
   AND 판매일자 LIKE '201407%';

부서에서 LIKE가 들어가는 바람에 판매일자를 INDEX에서 활용하지 못한다. 최근 1달의 Data를 찾기위해 10년치의 Data를 다 읽고있다.
이럴땐 DEPT Table을 이용하여 Sub-query로 부서들의 집합을 찾아서 IN을 활용해주는게 좋다.

SELECT *
  FROM TAB1
 WHERE 상품 = 'PRINTER'
   AND 부서 IN (SELECT 부서 FROM DEPT
                WHERE 부서 LIKE :VAL1||'%')
   AND 판매일자 LIKE '201407%';

NESTED LOOP
VIEW
    SORT UNIQUE      TABLE ACCESS BY ROWID OF 부서
        INDEX RANGE SCAN OF 부서_PK
TABLE ACCESS BY ROWID OF TAB1
    INDEX RANGE SCAN OF INDEX1
1. 먼저 Sub-query가 수행되어 n개를 제공
   (반드시 공급자가 되어야한)
2. 상품 =, 부서 =, 판매일자 LIKE 범위가 Sub-query 결과만큼 수행

3. Dummy Table을 이용하는 방법

3개의 Table을 DB에 미리 만들어놓고 이 Table을 Sub-query에 이용하거나 JOIN에 활용한다.

- 각 Table의 Column마다 UNIQUE INDEX를 생성해 둘 것
- YMD_DUAL 은 일자를 점으로 만들어주기 위해 사용
- YM_DUAL은 월별 기간을 점으로 만듬
- COPY_T는 Data 복제나 임의의 값 생성을 위해 사용

INDEX : 상품 + 판매일자 + 부서

SELECT *
  FROM TAB1
 WHERE 상품 = 'PRINTER'
   AND 판매일자 BETWEEN '20140701' AND '20140720'
   AND 부서 LIKE :VAL1||'%';

SELECT *
  FROM TAB1
 WHERE 상품 = 'PRINTER'
   AND 판매일자 IN (SELECT YMD FROM YMD_DUAL
                    WHERE YMD BETWEEN '20140701' AND '20140720')
   AND 부서 LIKE :VAL1||'%';

INDEX : 상품 + 구분 + 생산일자
(구분은 A01 ~ A10, B01 ~ B15)

SELECT *
  FROM TAB1
 WHERE 상품 = 'PRINTER'
   AND 구분 LIKE :TYPE||'%'
   AND 생산일자 = '20140824';

SELECT *
  FROM TAB1
 WHERE 상품 = 'PRINTER'
   AND 구분 IN (SELECT :TYPE||NO FROM COPY_T
                WHERE NO <= DECODE(:TYPE,'A',10,15))
   AND 생산일자 = '20140824';

INDEX : 자재 + 용도 + 구매일자

SELECT *
  FROM TAB1
 WHERE 자재 = 'KH101'
   AND 판매일자 BETWEEN '20140701' AND '20140720';

용도에는 총 30개의 Data가 있고, 용도에 대해서는 Check조건이 없는데 판매일자를 INDEX에서 활용하기 위해서는 어떻게든 용도에 조건을 줘야한다. 용도의 모든 Data가 강제로 출력되는 SQL을 만들어야 한다.

4. ROWNUM을 활용

SELECT *
  FROM TAB1
 WHERE 자재 = 'KH101'
   AND 용도 IN (SELECT CEIL(ROWNUM/4)||'0'||
                       MOD(ROWNUM,4) + 1
                 FROM TAB1
                WHERE ROWNUM <= 32)
   AND 판매일자 BETWEEN '20140701' AND '20140720';

5. 임의의 집합 활용

SELECT *
  FROM TAB1
 WHERE 자재 = 'KH101'
   AND 용도 IN (SELECT TO_NUMBER(X.NO)||Y.NO2
                 FROM COPY_T X, COPY_T Y
                WHERE X.NO <= '8'
                  AND Y.NO2 DECODE(X.NO,'3','03','7','03','04'))
   AND 판매일자 BETWEEN '20140701' AND '20140720';

* IN 조건의 상수와 변수의 차이

1. 상수

SELECT EMPNO, ENAME, SAL
  FROM EMP
 WHERE EMPNO IN (100,200,300,200);

동일한 상수값이 한번이상이면 뒤에 값은 무시됨

CONCATENATION
TABLE ACCESS BY ROWID OF EMP
    INDEX UNIQUE SCAN OF EMP_PK    -- 300
TABLE ACCESS BY ROWID OF EMP
    INDEX UNIQUE SCAN OF EMP_PK    -- 200 TABLE ACCESS BY ROWID OF EMP
    INDEX UNIQUE SCAN OF EMP_PK    -- 100

2. 변수

SELECT EMPNO, ENAME, SAL
  FROM EMP
 WHERE EMPNO IN (:V4. :V3, :V2, :V1);

단, V1, V2는 NULL, V3, V4는 'A'라고 할때 실행계획이다.
상수값은 실행계획이 만들어질때는 어떤값인지 모르기 때문에 다 따로 잡힌다. 그러나 실행될 당시 NULL이면 Binding 없이 0이며, 중복값일 경우는 일단 INDEX Access는 일어나지만 이미 중복된 값이라 Table Access는 없다.

     CONCATENATION
   0 TABLE ACCESS BY ROWID OF EMP
   0     INDEX UNIQUE SCAN OF EMP_PK    -- V1: NULL
   0 TABLE ACCESS BY ROWID OF EMP
   0     INDEX UNIQUE SCAN OF EMP_PK    -- V2: NULL
7000 TABLE ACCESS BY ROWID OF EMP
7001     INDEX UNIQUE SCAN OF EMP_PK    -- V3: 'A'    0 TABLE ACCESS BY ROWID OF EMP
7001     INDEX UNIQUE SCAN OF EMP_PK    -- V4: 'A'

* IN 조건 추가 대상 Column의 선정

- IN 조건이 항상 유리해지는 것은 아니다.
- 먼저 전략적인 INDEX 구성. 그 다음에 해당 INDEX 구성과 아주 밀접하게 IN을 사용
- 적절하지 못한 INDEX 구성에 적용하면 오히려 느려진다. : 500만건에 대해서 = 로 처리 ?
- Data 분포도도 감안하여 판단하여야 한다.

SELECT ...
  FROM TAB1
 WHERE COL1 = :V1
   AND COL2 LIKE :V2||'%'
   AND COL3 IN ('1','5')
   AND COL4 BETWEEN :D1 AND :D2;

INDEX1: COL1 + COL2 + COL3
INDEX2: COL2 + COL4
INDEX3: COL3 + COL4 + COL5 + COL1

어떤 INDEX를 사용해야 할까 ?

INDEX1을 쓰고 싶은데...
- 일단 COL2를 IN 조건으로 변환해야 하고
- :V2가 입력되지 않았거나 범위가 매우 넓다면 오히려 다른걸 사용하는게 좋을수도 있다.
INDEX2를 쓰고 싶은데 ...
- 일단 COL2를 IN 조건으로 변환해야 하고
- :V2와 COL4의 범위가 좁다면 이게 확실히 유리하겠다.
INDEX3를 쓰고 싶은데...
- COL4, COL5의 범위가 좁다면 유리할 수 있다.

* 결합INDEX Column수에 따른 차이

- Column이 3개 이하인 경우는 대부분 정상적인 결합처리 실행계획이 수립되나
- Column이 4개 이상인 경우는 3번째 이후 Column들에 대해서 사용자가 신경을 써줘야 한다.
- 왜냐면 Optimizer는 특별히 정해준 Case 이외에는 3번째 이후 Column부터 실행계획을 나누는것보다 그냥 Scan하는게 더 빠르다고 판단해버리기 때문이다.

SELECT *
  FROM TAB1
 WHERE 제품 = 'KH101'
   AND 부서코드 = '2110'
   AND 매출구분 IN ('1','5','7');

제품 + 부서코드 + 매출구분 INDEX : 정상

제품 + 부서코드 + 매출구분 + 매출일자 INDEX : 비정상
=> 제품, 부서코드까지는 INDEX를 사용해서 바로 찾아가고, 매출구분부터는 FULL-SCAN
( /*+ USE_CONCAT */ HINT 를 사용해야 함 )

SELECT *
  FROM TAB1
 WHERE 제품 = 'KH101'
   AND 부서코드 = '2110'
   AND 매출구분 IN ('1','5','7')
   AND 매출일자 LIKE '201407%';

제품 + 부서코드 + 매출구분 INDEX : 정상

제품 + 부서코드 + 매출구분 + 매출일자 INDEX : 정상
=> 3번째 Column부터 IN이 나와도 실행계획을 분리안하는데, 그 IN 뒤에 연속선(=, LIKE, BETWEEN, <, > ) 이 있으면 그것을 INDEX에서 사용하기 위해서 앞의 IN을 분리함

4개이상 Column을 가지는 결합INDEX 사용시에는 주의해야 할 사항이 있다.
같은 SQL문이라도 3개 이하 Column의 결합INDEX에서는 최적으로 동작하는데, 4개이상의 결합INDEX에서는 그렇지 않은 경우가 있다. 심지어 실행계획은 양쪽 다 동일한데도 말이다.

SELECT *
  FROM TAB1
 WHERE 지역 = '서울'
   AND 구분 IN ('3','1')
   AND 발생일자 BETWEEN '201407'
                   AND '201408';

INDEX : 지역+구분+발생일자+부서코드

CONCATENATION
TABLE ACCESS BY ROWID OF TAB1
INDEX RANGE SCAN OF IDX1
TABLE ACCESS BY ROWID OF TAB1
INDEX RANGE SCAN OF IDX1

<== 3번째 이후 연속선 (BETWEEN)으로 인하여 실행계획에 참여

발생일자는 CHECK 조건으로 처리되어 ==>

실행계획에 참여하지 않음

SELECT *
  FROM TAB1
 WHERE 지역 = '서울'
   AND 구분 IN ('3','1')
   AND 발생일자 IN ('201407',
                   '201408');

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

작가: 이화식
출판: 엔코아컨설팅
발매: 1999.11.20
평점

리뷰보기

DevStarSJ.github.io 으로 이사했습니다.

페이지

Post List

2015년 1월 9일 금요일

대용량 데이터베이스 솔루션 2권 #17 IN을 활용한 Access 효율화

댓글 없음:

댓글 쓰기