DevStarSJ.github.io 으로 이사했습니다.: 대용량 데이터베이스 솔루션 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 개수를 현저하게 감소시킬 수 있다.