하나 이상의 컬럼으로 결합된 인덱스는 구성된 컬럼 순으로 '종속 정렬'
되어 있으므로 어떤 인덱스 컬럼의 조건이 '='이 아니라면 그 뒤에 오는 조건은 어떤 연산자를 가지더라도
이미 정렬상태가 파괴되므로 그 조건의 특정범위에서 처리를 중단할 수 없다.
SELECT * FROM TAB1
WHERE COL1 ='A'
AND COL2 BETWEEN '111' AND '112'
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
0 INDEX RANGE SCAN OF XAK_TAB1 (NONUNIQUE)
SELECT * FROM TAB1
WHERE COL1 ='A'
AND COL2 IN ( '112','111')
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
0 CONCATENATION
0 INDEX RANGE SCAN OF XAK_TAB1 (NONUNIQUE)
0 INDEX RANGE SCAN OF XAK_TAB1 (NONUNIQUE)
IN을 사용한 경우보다 BETWEEN 을 사용한 경우의 액세스 양이 많다.
COL2='111' 일때 COL1은 A,B,C,D로 정렬되어 있고
COL2='112' 일때 COL1은 A,B,C,D로 정렬되어 있지만
COL2값이 '111'과 '112' 사이에는 COL1이 정렬되어 있지 않다.
옵티마이져가 COL2='111' AND COL1='A'를 엑세스 한 후 다음 로우가 COL='A'가 아니면 점프해서 COL2='112' AND COL1='A'인 로우를 찾을 수 있다면 불필요한 엑세스가 일어나지 않지만 옵티마이져는 결코 그렇게 할 수 없다. BETWEEN인 경우 COL2는 점이 아닌 선분이므로 그 사이에 1111,1115,111.2,111.003등 셀 수 없이 많은 값들이 존재할 수 있기 때문이다.
이것이 바로 엑세스량을 늘어나게 하는 원인이 된다.
그러나 IN으로 바뀐 순간 111,112 사이에는 로우가 없다는 것이 확정됨으로 점프를 할 수 있게 된다.
SELECT *
FROM TAB1
WHERE 상품 ='PRINTER'
AND 매출일자 BETWEEN '19980302' AND '19980303'
상기의 SQL 은 약 600건의 로우를 추출하게 될 것이다.(부서 30*하루 10건 *2일 조회)
그러나 불행하게도 처리주관 범위는 100,000 로우가 된다는 문제가 있다.
인덱스의 두번째 구성 컬럼인 '부서코드'가 조건을 가지지 않으므로 '주류'는 '상품' 컬럼만 가능하며 '매출일자'는 '비주류' 역할만 하기 때문이다.
SELECT * FROM TAB1
WHERE 상품='PRINTER'
AND 부서코드 LIKE '%'
AND 매출일자 BETWEEN '19980302' AND '19980303'
위 쿼리는 의미가 없다.
매출일자 조건이 부활하려면 선행 컬럼인 '부서코드' 가 '='이 되어야 한다.
SELECT *
FROM TAB1
WHERE 상품 = 'PRINTER'
AND 부서코드 IN ( SELECT 부서코드 FROM 부서
WHERE 부서구문 = '영업' )
AND 매출일자 BETWEEN '19980302' AND '19980303';
서브쿼리가 제공자 역할을 하며 선행 칼럼인 부서코드 가 여러 개의 '=' 조건이 되면서
'매출일자' 조건이 '주류'로 화려한 부활
한가지 주의할 점은 내용적으로는 분명히 결합처리 실행계획으로 수행한 것이 확실하지만
실행계획을 확인해 보면 어디에서도 'CONCATENATION'이란 단어를 찾을 수 없다.
EXECUTION PLAN
-----------------------------------------------------
0 SELECT STATEMENT
1 0 NESTED LOOPS
2 1 TABLE ACCESS (BY ROWID) OF '부서'
3 2 INDEX (RANGE SCAN) OF '부서' ( NON UNIQUE )
4 1 TABLE ACCESS (BY ROWID) OF 'TAB1'
5 4 INDEX (RANGE SCAN) OF 'TAB1' ( NON UNIQUE )
만약 서브쿼리가 유일한 값이라는 보장이 없다면 다음과 같다.
SORT(UNIQUE)에서 중복된 값 제거.
EXECUTION PLAN
-----------------------------------------------------
0 SELECT STATEMENT
1 0 NESTED LOOPS
2 1 VIEW
3 2 SORT(UNIQUE)
4 3 TABLE ACCESS (BY ROWID) OF '부서'
5 4 INDEX (RANGE SCAN) OF '부서' ( NON UNIQUE )
4 1 TABLE ACCESS (BY ROWID) OF 'TAB1'
5 4 INDEX (RANGE SCAN) OF 'TAB1' ( NON UNIQUE )
이 실행계획이 'CONCATENATION'으로 표현되지 않은 것은 파싱을 할 때 분리될 개수를 알 수 없기 때문이다.
만약 여러분이 영업부서를 '상수값'을 사용하여 부서코드 IN ( '5110','5120',...;5490') 으로 지정 했다면
이때는 실행 획에 'CONCATENATION' 이 나타날 것이다.
여기서 중요한 것은 서브쿼리가 공급자 역할을 하여 단 한번만 수행되어야 한다는 점이다.
가장 자주 사용되는 형태는 앞서 예로 들었던 BETWEEN이나 LIKE,> 등의 연속선의 범위를 IN 을 활용하여
점으로 대치 하는 방법이다.
이 방법을 사용할 수 있으려면 점으로 표현할 수 있는 갯수가 부담되지 않아야 하며 고정적이어야 한다.
sql1:between 사용
SELECT *
FROM TAB1
WHERE 상품 ='PRINTER'
AND 처리구분 BETWEEN '2' AND '3'
AND 판매일자 LIKE '199804%';
slq2:in 사용
SELECT *
FROM TAB1
WHERE 상품 ='PRINTER'
AND 처리구분 IN ('2', '3' )
AND 판매일자 LIKE '199804%';
sql3:처리구분 조건이 아예 없는 경우
SELECT *
FROM TAB1
WHERE 상품 ='PRINTER'
AND 처리구분 IN ('1','2','3','4')
AND 판매일자 LIKE '199804%';
sql4:처리구분별로 정렬을 원한다면
SELECT *
FROM TAB1
WHERE 상품 ='PRINTER'
AND 처리구분 IN ('4','3','2','1')
AND 판매일자 LIKE '199804%';
in으로 비교할 값의 종류가 너무 많거나 사용자의 입력 조건에 따라 그 값이 변한다면 상수를 이용한 in를 사용할 수 없다. 이런 경우 서브쿼리를 이용하는데 이 경우 서브쿼리는 반드시 공급자 역할을 해야 한다.
SELECT *
FROM TAB1
WHERE 상품 ='PRINTER'
AND 부서 LIKE '210%'
AND 판매일자 BETWEEN '19980401' AND '19980415'
위 쿼리에서 상품,부서,판매일자로 결합인덱스를 구성할 수 있는 경우의 수는 매우 많다. 하지만 어떤 형태의 인덱스를 구성하더라도 부서와 판매일자가 =이 아니므로 많은 문제점을 유발 할 수 있다.
이런 경우 아래처럼 부서칼럼을 in조건으로 변화시키고 결합인덱스를 상품+부서+판매일자 로 한다.
SELECT *
FROM TAB1
WHERE 상품 ='PRINTER'
AND 부서 IN ( SELECT 부서 FROM 부서테이블 WHERE 부서 LIKE '210%')
AND 판매일자 BETWEEN '19980401' AND '19980415'
판매일자의 경우는 그 값의 다양성이 너무 많아 현존 테이블을 이용하기엔 역부족이므로 아래 모조 테이블을 이용한다.
일자를 기본으로 하는 달력테이블
* 여기서 FROM절의 테이블은 ROW수가 365000 이 넘는 테이블이면 어떤것이라도 좋다
CREATE TABLE YMD_DUAL
( YMD VARCHAR2(8), YMD_DATE DATE );
SELECT * FROM YMD_DUAL ;
DELETE YMD_DUAL ;
INSERT INTO YMD_DUAL SELECT TO_CHAR(TO_DATE('19591231','YYYYMMDD')+ROWNUM,'YYYYMMDD'), TO_DATE('19591231','YYYYMMDD')+ROWNUM
FROM CHECK_REBUILD
WHERE ROWNUM< =365000 ;
* 인덱스 생성
CREATE UNIQUE INDEX YMD_DUAL_PK1 ON YMD_DUAL ( YMD ) PCTFREE 0 ;
CREATE UNIQUE INDEX YMD_DUAL_PK2 ON YMD_DUAL ( YMD_DATE ) PCTFREE 0 ;
=========================================================================
* 년월로만 된 달력 생성
CREATE TABLE YM_DUAL (YM6, YM4)
AS SELECT DISTINCT SUBSTR(YMD,1,6),SUBSTR(YMD,3,4)
FROM YMD_DUAL ;
* 인덱스 생성
CREATE UNIQUE INDEX YM_DUAL_PK1 ON YM_DUAL ( YM6 ) PCTFREE 0 ;
CREATE INDEX YM_DUAL_PK2 ON YM_DUAL ( YM4 ) PCTFREE 0 ;
이제 between으로 된 판매일자를 in으로 변화시킨다.
SELECT * FROM TAB1
WHERE 상품 ='PRINTER'
AND 판매일자 IN ( SELECT YMD FROM YMD_DUAL WHERE YMD '19980401' AND '19980415' )
AND 부서 LIKE '210%'
여기서 주의할 점은 범위조건은 무조건 in으로 변경해야하는 것은 아니라는 점이다. 인덱스 전략이 확정된 다음 이 인덱스의 구성으로는 비효율이 발생될 수 밖에 없을 때 사용하는 것이 좋다.
아래 쿼리에서 용도구분 값을 (4-52 그림 4-2-3) 처럼 표현해야 한다면 어떻게 할 것인가?
SELECT *
FROM TAB3
WHERE 자재 ='KH010'
AND 용도구분 ???
AND 구매일자 BETWEEN :DATE1 AND :DATE2
SELECT *
FROM TAB3
WHERE 자재 ='KH010'
AND 용도구분 IN (
SELECT
CEIL(ROWNUM/4) /* 1~8 값 생성 */
||'0'
||MOD(ROWNUM,4)+1 /* 1~4 값 생성 */
FROM TABL3 WHERE ROWNUM <= 32
)
AND 구매일자 BETWEEN :DATE1 AND :DATE2
ROWNUM 을 활용하는 방법에서는 분류3,분류7도 4개씩 생성되는 문제점이 있다.
이를 해결하려면 어떻게 할 것인가?
SELECT *
FROM TAB3
WHERE 자재 ='KH010'
AND 용도구분 IN (
SELECT
TO_NUMBER(X.NO2)||(Y.NO2
FROM COPY_T X, COPY_T Y
WHERE X.NO2 < '08' /*분류1~8 생성 */
AND Y.NO2 <= DECODE(X.NO2,'03','03','07','03','04' /*분류3,7인 경우 3개만 생성*/
)
AND 구매일자 BETWEEN :DATE1 AND DATE2
옵티마이져가 SQL을 파싱할 때 상수값을 받았다는 것은 처리할 대상이 확정되었음을 의미한다.
그러나 변수값을 받은 경우는 실행 시에 어떤 값이 바인딩되어 실행될 지를 알 수 없는
상태에서 파싱하여야 하므로 이 두가지는 논리적으로 보더라도 분명 큰 차이가 있다.
SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE EMPNO IN (7876,7900,75667,7900)
* WHERE EMPNO IN (7876,7900,75667) 로 처리 된다.
SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE EMPNO IN (7900,7876,7900,7566)
* WHERE EMPNO IN (7900,7876,7566) 로 처리 된다.
만약 동적(DYNAMIC) SQL 을 사용하여 수행시킬 때 중복된 상수값이 만들어지더라도 중복된
실행을 하지 않으므로 조금도 걱정할 필요가 없다.
SELECT 부서코드, SUM(매출액)
FROM TAB4
WHERE 사업장 = '서울'
AND 매출구분 IN ( :B1, :B2, :B3, :B4, :B5 )
AND 매출일자 LIKE :C1||'%'
GROUP BY 부서코드
중복된 조건값이 지정된 경우
('A','B','A','','')
변수로 지정된 SQL은 파싱을 할 때는 어떤 값이 입력될 지를 알 수 없으므로 무조건
각각의 변수값에 대해 실행계획을 분리한다.
중복이 발생하는 2번의 경우 그 중복된 값이 NULL이냐 아니냐에 따라 매우 큰 차이가
발생 한다.
ROWS EXECUTION PLAN
-------------------------------------------------------------------
0 SELECT STATEMENT
12 SORT ( GROUP BY )
0 CONCATENATION
0 TABLE ACCESS (BY ROWID) OF 'TAB4'
0 INDEX (RANGE SCAN) OF 'T_IDX' ( NON-UNIQUE) -> B5 = ''
0 TABLE ACCESS (BY ROWID) OF 'TAB4'
0 INDEX (RANGE SCAN) OF 'T_IDX' ( NON-UNIQUE) -> B4 = ''
73764 TABLE ACCESS (BY ROWID) OF 'TAB4'
73765 INDEX (RANGE SCAN) OF 'T_IDX' ( NON-UNIQUE) -> B3 = 'A'
34475 TABLE ACCESS (BY ROWID) OF 'TAB4'
34475 INDEX (RANGE SCAN) OF 'T_IDX' ( NON-UNIQUE) -> B2 = 'B'
0 TABLE ACCESS (BY ROWID) OF 'TAB4'
73765 INDEX (RANGE SCAN) OF 'T_IDX' ( NON-UNIQUE) -> B1 = 'A'
NULL('')인 값은 중복은 전혀 인덱스가 일어나지 않는 반면에 어떤 존재하는 값에 대한
중복은 불필요한 액세스가 발생하고 있다. 여기서 주목해야 할 부분은 중복된 값에
대한 처리시 인덱스 액세스는 반복적으로 발생하지만 테이블 액세스는 중복되지
않는 다는 점이다.
이것은 데이타베이스 버그다. 오라클 버그다.
* 인덱스
INDEX1:COL1+COL2+COL3
INDEX2:COL2+COL4
INDEX3:COL3+COL4+COL5+COL1
*SQL
SELECT ...
FROM TAB1
WHERE COL1=:VAL1
AND COL2 LIKE :VAL2||'%'
AND COL3 IN('1','5')
AND COL4 BETWEEN :DATE1 AND :DATE2
위와 같은 상황일 때 어떤 인덱스를 사용하는 것이 가장 좋은가?
현재로서는 알 수 없다이다. 즉, 해당 테이블이 실 데이타를 분석해 보지 않고서는 아무런 결론을 내릴 수 없다.
이것은 IN 활용 전략은 인덱스 구조와 아주 밀접한 관계를 가지고 있으므로 먼저 인덱스 구조를 확실히 하는 것이 선행되어야 한다는 것을 의미 한다.
우리가 자주 사용하는 컬럼들의 개략적인 분포도는 알고 있어야 한다. 각각의 개별적인 컬럼의 분포도뿐만 아니라 이들이 다른 컬럼과 결합했을 때의 결합 분포도도 알고 있어야 한다.
결합인덱스의 컬럼이 3개 이하일 때는 대부분의 경우 정상적으로 결합처리 실행계획이 수립되지만
4개 이상인 경우는 상황에 따라 현격한 차이가 발생한다.
1) TAB1 의 인덱스가 '제품+부서코드+매출구분' 으로 구성되었다면 3개로 분리된
정상적인 결합처리 실행계획이 수립된다.
SELECT * FROM TAB1
WHERE 제품 = 'KH1101'
AND 부서코드 ='2110'
AND 매출구분 IN ('1','5','7' )
2) '제품+부서코드+매출구분+매출일자' 로 구성된 경우
SELECT * FROM TAB1
WHERE 제품 = 'KH1101'
AND 부서코드 ='2110'
AND 매출구분 IN ('1','5','7' )
AND 매출일자 LIKE '199805%'
*그러나 또다른 IN 조건이 나타나면 이 조건은 '주류'가 되지 못한다.( 체크조건이 된다 )
이런 경우 USE_CONCAT 힌트를 사용할 수 있다.
실행계획은 동일하면 처리범위도 같다라는 생각은 큰 착각이다.
1) IN 다음에 사용된 조건이 선분인 BETWEEN
SELECT * FROM TAB1
WHERE 지역 = '서울'
AND 구분 IN ('3','1')
AND 발생일자 BETWEEN '19980601' AND '19980602'
---------------------------------------------
CONCATENATION
TABLE ACCESS(BY ROWID) OF 'TAB1'
INDEX (RANGE SCAN) OF 'INDEX1' (NON-UNIQUE)
TABLE ACCESS(BY ROWID) OF 'TAB1'
INDEX (RANGE SCAN) OF 'INDEX1' (NON-UNIQUE)
'지역 = AND 구분 = AND 발생일자 BETWEEN' 이므로 모든 조건이 '주류' 역할
SQL1의 BETWEEN은 '연속선' 을 의미하므로 '시작점'과 '끝점'을 가진다.
그러므로 시작점에서 출발하여 스캔하다가 끝점을 마나는 순간 처리를 종료 할 수 있다.
즉, 처리범위는 지역+구분+발생일자이다.
2) 중첩된 IN
SELECT * FROM TAB1
WHERE 지역 = '서울'
AND 구분 IN ('3','1')
AND 발생일자 IN ('19980601','19980602')
---------------------------------------------
CONCATENATION
TABLE ACCESS(BY ROWID) OF 'TAB1'
INDEX (RANGE SCAN) OF 'INDEX1' (NON-UNIQUE)
TABLE ACCESS(BY ROWID) OF 'TAB1'
INDEX (RANGE SCAN) OF 'INDEX1' (NON-UNIQUE)
'지역= AND 구분= AND 발생일자 IN' 이므로 '지역'과 '구분' 조건만 '주류'
역할을 했고 '발생일자'는 '비주류' 역할을 한다.
그 이유는 결합인덱스의 컬럼이 4개 이상이고 연속된 IN을 사용하면
두번째 IN 조건(발생일자)은 결합처리 실행계획으로 분리되지 못하여
단지 체크기능만 담당했기 때문이다.
즉, 처리범위는 지역+구분을 만족하는 모든 로우이다.