DevStarSJ.github.io 으로 이사했습니다.: 대용량_Database

레이블이 대용량_Database_Solution인 게시물을 표시합니다. 모든 게시물 표시

2015년 1월 9일 금요일

대용량 데이터베이스 솔루션 2권 #18 중복된 IN과 INDEX Column수의 관계

3. 중복된 IN과 INDEX Column수의 관계

* INDEX Column 수에 따라
- 규칙 1 : 3개 Column 이하의 INDEX는 상수(변수포함) IN 조건은 정상적인 분리
- 규칙 2 : 4개 Column 이상의 INDEX의 중복 상수IN 조건은 2 Column까지 분리. 나머지는 CHECK 조건

SELECT *
  FROM TAB1
 WHERE 지역 = '서울'
   AND 부서코드 IN ('2110','2050')
   AND 매출구분 IN ('1','5');

제품 + 부서코드 + 매출구분 INDEX : 4개로 분리

제품 + 부서코드 + 매출구분 + 매출일자 INDEX : 2개로 분리
=>매출구분은 CHECK 조건
( /*+ USE_CONCAT */ HINT 를 사용하면 4개로 분리됨 )

SELECT *
  FROM TAB1
 WHERE 지역 IN ('서울', '대구')
   AND 부서코드 IN ('2110','2050')
   AND 매출구분 IN ('1','5');

제품 + 부서코드 + 매출구분 INDEX : 8개로 분리

제품 + 부서코드 + 매출구분 + 매출일자 INDEX : 4개로 분리

- 규칙 3 : 4개 Column 이상 INDEX의 상수(변수포함) IN 조건은 연속선에 해당하는 조건이 뒤에 오면 정상적으로 분리
(연속선 : =, LIKE, BETWEEN, >, < )
- 규칙 4 : 중복된 상수 IN 조건이 정상적인 분리를 하지 않으면 'USE_CONCAT' HINT로 정상적인 분리 가능
(함부로 사용하지 말것)

SELECT *
  FROM TAB1
 WHERE 지역 IN ('서울', '대구')
   AND 부서코드 IN ('2110','2050')
   AND 매출구분 IN ('1','5');   AND 매출일자 BETWEEN '20140801'AND '20140826';

제품 + 부서코드 + 매출구분 + 매출일자 INDEX : 8개로 분리

SELECT ...
  FROM TAB1
 WHERE 제품 = 'KH2200'
   AND 부서 IN (SELECT 부서 FROM DEPT
                WHERE 부서 LIKE '21%')
   AND 구분 IN ('1','5','7');

- 규칙 5 : Sub-query 뒤에 오는 IN 조건은 실행계획 분리에 기여하지 못함
(당연히 그렇지. Sub-query에서 몇개가 나올지 어떻게 알고 ?)

- 규칙 6 : Sun-query 앞에 위치한 IN 조건이 실행계획분리에 참여하면
Sub-query가 중복 수행함

SELECT ...
  FROM TAB1
 WHERE 제품 IN ('KH2200', 'MS3120')
   AND 부서 IN (SELECT 부서 FROM DEPT
                WHERE 부서 LIKE '21%')
   AND 구분 BETWEEN '3' AND '5';

(뒤에 상수 IN 조건이나 연속선이 오면 사용할수는 있으나, 중복실행을...)

CONCATENATION
  NESTED LOOPS
TABLE ACCESS BY ROWID OF DEPT
      INDEX RANGE SCAN OF 부서_PK
TABLE ACCESS BY ROWID OF TAB1
    INDEX RANGE SCAN OF IDX1
  NESTED LOOPS          -- 중복수행
    TABLE ACCESS BY ROWID OF DEPT
      INDEX RANGE SCAN OF 부서_PK
TABLE ACCESS BY ROWID OF TAB1
    INDEX RANGE SCAN OF IDX1

* 연결형태별 활용기준

조 건 유 형	3 Column 이하	4 Column 이상	HINT 사용
COL1 = constant COL2 IN (constant) COL3 IN (constant)	정상적인 분리	COL2까지만 분리	가능
COL1 = constant COL2 IN (subquery) COL3 IN (constant)	COL2까지만 분리	COL2까지만 분리	불가능
COL1 = constant COL2 IN (subquery) COL3 = constant	정상적인 분리	정상적인 분리
COL1 = constant COL2 IN (subquery) COL3 LIKE constant	정상적인 분리	정상적인 분리
COL1 IN (constant) COL2 IN (constant) COL3 = constant	정상적인 분리	정상적인 분리
COL1 IN (constant) COL2 IN (subquery) COL3 = constant	정상적인 분리 하지만 subquery반복	정상적인 분리하지만 subquery반복
COL1 IN (subquery) COL2 IN (constant) COL3 = constant	COL1까지만 분리	COL1까지만 분리	불가능
COL1 IN (constant) COL2 IN (subquery) COL3 LIKE constant	정상적인 분리 하지만 subquery반복	정상적인 분리 하지만 subquery반복
COL1 IN (subquery) COL2 = constant COL3 IN (constant)	COL3는 분리되지 않음	COL3는 분리되지 않음	불가능
COL1 IN (constant) COL2 = constant COL3 IN (constant)	정상적인 분리	COL3는 분리되지 않음	가능
COL1 = constant COL2 = constant COL3 IN (constant)	정상적인 분리	COL3는 분리되지 않음	가능

* 결합처리 실행계획이 불가능한 형태의 해결

INDEX : 제품 + 부서 + 구분 + 매출일자 일 경우

SELECT ...
  FROM TAB1
 WHERE 제품 = 'KH2200'
   AND 부서 LIKE :DEPT||'%'
   AND 구분 IN ('1','5')
   AND 매출일자 BETWEEN :S_DATE AND :E_DATE;

여기서 가장 똑똑한 조건이 매출일자 여서 매출일자를 반드시 INDEX에서 활용하고 싶다. 하지만, 부서의 LIKE 때문에 그게 안된다. 부서를 Sub-query로 만들어도 그건 불가능하다.

이럴땐 3개의 Table을 JOIN하여 카테시안 곱 만큼의 점을 만들어서 Main-query에 제공하면 된다.

SELECT ...
  FROM TAB1
 WHERE (제품, 부서, 구분, 매출일자) IN
       (SELECT 'KH2200', 부서, SUBSTR(NO,2,1), YMD
          FROM DEPT X, COPY_T Y, YMD_DUAL Z
         WHERE 부서 LIKE :DEPT||'%'
           AND Y.NO IN ('1', '5')
           AND YMD BETWEEN :S_DATE AND :E_DATA);

* Sub-query로 인한 Main-query 중복처리

SELECT *
  FROM ORDITEM
 WHERE 주문일자 BETWEEN '20140801' AND '20140810'
   AND 제품 IN (SELECT 제품 FROM PROD
                 WHERE 제품명 LIKE 'SM%')
   AND 금액 > 100000;

530 NESTED LOOPS
530   TABLE ACCESS BY ROWID OF PROD
530     INDEX RANGE SCAN OF PNAME_X
260 TABLE ACCESS BY ROWID OF ORDITEM
56800   INDEX RANGE SCAN OF IDX1

SELECT *
  FROM ORDITEM
 WHERE (주문일자, 제품) IN
       (SELECT YMD, 제품
          FROM PROD Y, YMD_DUAL X
         WHERE 제품명 LIKE 'SM%'
           AND YMD BETWEEN '20140801' AND '20140810')
   AND 금액 > 100000;

INDEX : 주문일자 + 제품코드

INDEX 선행 Column인 '주문일자'가 = 이 아니므로 Sub-query에서 제공한 530 Row마다 '주문일자 Between AND 제품 =' 로 수행되었다. 이럴 경우는 주문일자 와 제품 정보의 카테시안 곱 만큼 점을 만
들어서 제공을 하는게 더 좋다.

* 제공자 역할을 못하는 Sub-query의 해결

Sub-query를 제공자로 사용하더라도 Optimizer가 그것을 선행으로 실행하지 않고, JOIN처럼 실행하는 경우가 많다. 앞에서 이럴경우에 Sub-query의 결과를 가공하는 방법을 소개한 적이 있다. 이번에 확실히 방법들을 살펴보자.

1. 확실한 선처리 조건 부여

SELECT 종목, COUNT(*), SUM(계약금액)/1000
  FROM 계약내역
 WHERE 종목 IN (SELECT 종목
                 FROM 종목테이블
                WHERE 종목 > '') -- IDX 사용의 강력한 의지
   AND 계약일 = TO_CHAR(SYSDATE,'yyyymmdd')

2. Sub-query SELECT-LIST Column 가공

SELECT 종목, COUNT(*), SUM(계약금액)/1000
  FROM 계약내역
 WHERE 종목 IN (SELECT 종목||''   -- JOIN 못하게 다리를
                 FROM 종목테이블) -- 분질러 버림
   AND 계약일 = TO_CHAR(SYSDATE,'yyyymmdd')

3. Sub-query를 GROUP BY

SELECT 종목, COUNT(*), SUM(계약금액)/1000
  FROM 계약내역
 WHERE 종목 IN (SELECT 종목||''
                 FROM 종목테이블
                GROUP BY 종목)
   AND 계약일 = TO_CHAR(SYSDATE,'yyyymmdd')

가장 빠른 방법이어서 추천하는 방법이긴하나,
GROUP BY가 오래 걸릴 정도면 쓰면 안됨

4. HINT (PUSH_SUBQ) 사용

SELECT /*+ PUSH_SUBQ */
       종목, COUNT(*), SUM(계약금액)/1000
  FROM 계약내역
 WHERE 종목 IN (SELECT 종목||''
                 FROM 종목테이블)
   AND 계약일 = TO_CHAR(SYSDATE,'yyyymmdd')

정상적으로 작동하지 않는 경우가 많음
Optimizer가 PUSH_SUBQ 보다는 자기 판단을 더 믿음

* 논리합 연산자에서 STOP KEY 비효율 해결

SELECT *
  FROM 계약
 WHERE 종목코드 IN('01','02','04')
   AND 계약일자 BETWEEN '20140701'
                    AND '20140731'
   AND ROWNUM = 1;

- 이건 우리가 의도한 상황이 아니다. 당연히 저중에 하나만 찾으면 끝나길 바랬건만 전부다 찾고 난 뒤에 FILTER로 처리하고 있다.
- 제대로 동작하게 하려면 분리된 실행계획마다 COUNT(STOP KEY)가 있어야 한다.

    0 SELECT STATEMENT
    1   COUNT
    0     CONCATENATION
4680       FILTER
4680       TABLE ACCESS BY ROWID OF 계약4681           INDEX RANGE SCAN OF INDEX13927       FILTER
3927         TABLE ACCESS BY ROWID OF 계약3928           INDEX RANGE SCAN OF INDEX16720       FILTER
6720         TABLE ACCESS BY ROWID OF 계약6721           INDEX RANGE SCAN OF INDEX1

1. IN을 버림

SELECT *
  FROM 계약
 WHERE 종목코드 BETWEEN '01' AND '04'
   AND 종목코드 <> '03'
   AND 계약일자 LIKE '201407%'
   AND ROWNUM = 1;

    0 SELECT STATEMENT
    1   COUNT (STOP KEY)
    1     TABLE ACCESS BY ROWID OF 계약
1680       INDEX RANGE SCAN OF INDEX1

FILTER 처리는 없어졌으나 INDEX 선행 Column이 =가 아니므로 처리범위가 넓어질 수 있으므로 주의해야 함

2. UNION ALL로 따로 처리

SELECT *
  FROM (SELECT * FROM 계약
         WHERE 종목코드 = '01'
           AND 계약일자 LIKE '201407%' AND ROWNUM = 1
       UNION ALL
        SELECT * FROM 계약
         WHERE 종목코드 = '02'
           AND 계약일자 LIKE '201407%' AND ROWNUM = 1
       UNION ALL
        SELECT * FROM 계약
         WHERE 종목코드 = '04'
           AND 계약일자 LIKE '201407%' AND ROWNUM = 1)
 WHERE ROWNUM = 1;

0 SELECT STATEMENT
0   COUNT (STOP KEY)
0     VIEW
0       UNION-ALL
0         COUNT (STOP KEY)
0           TABLE ACCESS BY ROWID OF 계약
1             INDEX RANGE SCAN OF INDEX10         COUNT (STOP KEY)0           TABLE ACCESS BY ROWID OF 계약
1             INDEX RANGE SCAN OF INDEX10         COUNT (STOP KEY)
0           TABLE ACCESS BY ROWID OF 계약
1             INDEX RANGE SCAN OF INDEX1

4 IN을 고려한 결합INDEX 선정

* ACCESS 유형의 파악 (대용량 데이터베이스 솔루션 1권 참조)
- 우선적으로 모든 SQL에 대한 개선 및 검증이 필수이다.
- 사용된 모든 SQL 수집 및 유형 도출하는 것이 가장 중요하다. (줄기와 가지를 선별)
- ACCESS를 분석할 수 있는 기술력 확보가 전제 조건이므로 많은 연습이 필요하다.

* INDEX 선정시 IN조건 활용
- 먼저 Column 분포도와 결합분포도 그리고 조건 사용형태 및 특성 파악이 우선되어야 한다.
- 사용 Column이 유사한 ACCESS 형태를 Grouping하고,
- Grouping된 유형들을 모두 만족시킬 수 있도록 Column 순서 결정한다.
- Column 순서 결정에 가장 큰 영향을 미치는 요소는 조건에 사용된 연산자 형태인데,
- 연산자 현태가 매우 다양하다면 모든 경우를 만족시키기 위해 너무 많은 INDEX가 필요하게 된다.
- 그때, 적절한 IN 조건을 활용하여 필요한 INDEX 개수를 현저하게 감소시킬 수 있다.

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

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

리뷰보기

대용량 데이터베이스 솔루션 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권 #18 중복된 IN과 INDEX Column수의 관계

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