IN 연산자는 OR 연산자의 한가지 유형에 불과하지만 매우 독특한 특성을 가진다.
기존의 인덱스를 변경시키지 않고서도 결합인덱스를 스캔할 대 불필요한 범위를 액세스하는 것을
생략(SKIP)할 수 있도록 '징검다리'를 놓아준다.
결합 인덱스를 사용할 때 조건의 연산자의 '=' 여부에 따라 처리할 범위가 매우 미묘하고도 크게
변하는 것을 살펴보았다. 그 이유는 하나 이상의 컬럼으로 결합된 인덱스는 구성된 컬럼 순으로 '종속 정렬'
되어 있으므로 어떤 인덱스 컬럼의 조건이 '='이 아니라면 그 뒤에 오는 조건은 어떤 연산자를 가지더라도
이미 정렬상태가 파괴되므로 그 조건의 특정범위에서 처리를 중단할 수 없기 때문이다.
그림 4-2-1
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 을 상요한 경우의 액세스 양이 많다.
문제의 초점은 어디서 멈출 수 있느냐에 있다. 액세스한 COL1 이 'A'가 아니면 멈출수 있어야
불필요한 - COL1 이 'A'가 아닌 - 로우를 액세스하지 않겠지만 선행 컬럼인 COL2 BETWEEN 범위에
있는 COL1 은 정렬이 파괴되어 있으므로 어쩔수 없이 다음 로우를 계혹해서 읽지 않을 수 없다.
결국 WHERE BETWEEN 절의 COL1= 'A' 라는 조건은 액세스 범위를 직접 줄여주는 데 사용되지 못하고
단지 읽어둔 인덱스 로우를 취할 것인지, 버릴 것인지를 결정하는 체크조건으로만 사용되었다.
전제조건 : 인덱스가 '상품+부서코드+매출일자' 로 구성
상품이 'PRINTER'인 로우가 10만, 영업부서는 30가지 이며, 각 부서별 하루 평균 매출건수는 약 10건 이라고 가정
SELECT * FROM TAB1
WHERE 상품 ='PRINTER'
AND 매출일자 BETWEEN '19980302' AND '19980303'
상기의 SQL 은 약 600건의 로우를 추출하게 될 것이다. 그러나 불행하게도 처리주관 범위는 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 )
만약 서브쿼리가 유일한 값이라는 보장이 없다면 다음과 같다.
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 을 활용하여
점으로 대치 하는 방법이다.
전제조건 : 인덱스 '상품+처리구분+판매일자', '처리구분' 칼럼은 '1','2','3','4' 만 가진다.
SELECT *
FROM TAB1
WHERE 상품 ='PRINTER'
AND 처리구분 BETWEEN '2' AND '3'
AND 판매일자 LIKE '199804%';
BETWEEN 을 IN 으로 변경
SELECT *
FROM TAB1
WHERE 상품 ='PRINTER'
AND 처리구분 IN ('2', '3' )
AND 판매일자 LIKE '199804%';
처리구분 조건이 아예 없다면
SELECT *
FROM TAB1
WHERE 상품 ='PRINTER'
AND 처리구분 IN ('1','2','3','4')
AND 판매일자 LIKE '199804%';
처리구분별로 정렬을 원한다면
SELECT *
FROM TAB1
WHERE 상품 ='PRINTER'
AND 처리구분 IN ('4','3','2','1')
AND 판매일자 LIKE '199804%';
테이블 설계 시 주의 사항
1. 인덱스 구성 전략
| ONE 칼럼 인덱스 | 결합인덱스 |
|---|---|
| 절대 NULL 값을 허용하지 말고 반드시 '상수값'을 부여하라는 것이다. |
2. 상수값을 가진 IN과 서브쿼리를 가진 IN 은 여러 가지 차이가 있으므로 가능한 상수값을 조건으로 주는 것이 유리하다.
SELECT *
FROM TAB1
WHERE 상품 ='PRINTER'
AND 처리구분 BETWEEN :VAL1 AND :VAL2
AND 판매일자 LIKE '199804%';
SELECT *
FROM TAB1
WHERE 상품 ='PRINTER'
AND 처리구분 IN ( :VAL1, :VAL2, :VAL3, :VAL4 )
AND 판매일자 LIKE '199804%';
IN은 인덱스의 구성순서가 자신의 바로 다음에 위치한 칼럼이 조건을 가지고 있을
때 이 컬럼의 효율을 증가시킬 수 있을 뿐이다.
여기서 말하는 서브쿼리란 반드시 제공자 역할을 하는 경우에만 해당한다.
만약 결합인덱스의 액세스 효율을 향상시키기 위해 징검다리 역할로 부여한 IN 서브쿼리가 확인자 역할을 해버린다면 우리는 전혀
언듣 것이 없을 뿐만 아니라 쓸데없이 부하만 가중시키게 된다.
| 제공자 역할 |
|---|
| \- 서브쿼리가 제공자 역할을 하기 위해서는 서브쿼리 내에 메인쿼리의 칼럼을 갖지 않아야 하며 서브쿼리가 제공한 값을 받은 메인쿼리의 컬럼이 반드시 처리주관 조건이 되어야 한다. |
서브쿼리를 사용하여 IN 조건을 추가한 경우는
SELECT *
FROM TAB1
WHERE 상품 ='PRINTER'
AND 부서 LIKE '210%'
AND 판매일자 BETWEEN '19980401' AND '19980415'
인덱스 구성형태에 따른 차이점과 문제점 및 해결책
1. 상품+부서+판매일자 : 부서 '=' 로 사용되지 않았으므로 판매일자 조건은' 주류가 아니라 '비주류' 역할 ( CHECK 조건 )
3. 부서+상품+판매일자 : 부서 '=' 로 사용되지 않았으므로 상품 및 판매일자 조건 모두 '주류'가 아니다.
만약 부서가 '='이 되면 상품, 판매일자가 같이 다시 '주류'로 살아난다.
SELECT *
FROM TAB1
WHERE 상품 ='PRINTER'
AND 부서 IN ( SELECT 부서 FROM 부서테이블 WHERE 부서 LIKE '210%')
AND 판매일자 BETWEEN '19980401' AND '19980415'
일자를 기본으로 하는 달력테이블
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 ;
여기서 FROM절의 테이블은 ROW수가 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 ;
Dummy Table 을 사용하는 예제
SELECT * FROM TAB1
WHERE 상품 ='PRINTER'
AND 판매일자 IN ( SELECT YMD FROM YMD_DUAL WHERE YMD '19980401' AND '19980415' )
AND 부서 LIKE '210%'
SELECT *
FROM TAB3
WHERE 자재 ='KH010'
AND 구매일자 BETWEEN :DATE1 AND :DATE2
SELECT *
FROM TAB3
WHERE 자재 ='KH010'
AND 용도구분 IN ( SELECT CEIL(ROWNUM/4)||'0'||MOD(ROWNUM,4)+4 FROM TABL3 WHERE ROWNUM <= 32 )
AND 구매일자 BETWEEN :DATE1 AND :DATE2
Copy_T Table 이란 ?
CopyT는 복제테이블이라고 하는데 용도는 테이블 레코드를 인위적으로 늘릴때 사용합니다.
세로에서 가로로로 늘릴때는 DECODE()를 사용하여 인위적으로 만들 수 있으나 가로를 세로로
만들려면 레코드를 해당 컬럼만큼 인위적으로 늘려야 하는데 가공방법이 없기 때문에 CopyT을
이용하여 인위적으로 레코드를 늘립니다.
원리는 '1:M'조인을 이용하는건데요. '1'은 해당레코드 'M'은 CopyT이 되겠죠.
'M'쪽에서 '1'쪽을 조인하면 'M'쪽의 레코드는 늘어나지 않지만 '1'쪽은 레코드가 늘어나겠죠?
이를 이용해서 세로를 가로로 만드는것을 해결할 수 있습니다.
출처 : http://blog.naver.com/xsoft
Copy_T Table 생성
CREATE TABLE COPY_T ( NO, NO2 ) AS
SELECT ROWNUM, SUBSTR(TO_CHAR(ROWNUM,'09',2,2)
FROM CHECK_REBUILD
WHERE ROWNUM < = 31 ;
임의의 집합을 생성하여 제공하는 예저
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'
AND Y.NO2 <= DECODE(X.NO2,'03','03','07','03','04')
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('')인 값은 중복은 전혀 인덱스가 일어나지 않는 반면에 어떤 존재하는 값에 대한
중복은 불필요한 액세스가 발생하고 있다. 여기서 주목해야 할 부분은 중복된 값에
대한 처리시 인덱스 액세스는 반복적으로 발생하지만 테이블 액세스는 중복되지
않는 다는 점이다.
이것은 데이타베이스 버그다. 오라클 버그다.
IN 활용 전략은 인덱스 구조와 아주 밀접한 관계를 가지고 있으므로 먼저 인덱스 구조를 확실히 하는 것이 선행되어야 한다는 것을 의미 한다.
우리가 자주 상요하는 컬럼들의 개략적인 분포도는 알고 있어야 한다. 각각의 개별적인 컬럼의 분포도뿐만 아니라 이들이 다른 컬럼과 결합했을
때의 결합 분포도도 알고 있어야 한다.
결합인덱스의 컬럼이 3개 이하일 때는 대부분의 경우 정상적으로 결합처리 실행계획이 수립되지만
4개 이상인 경우는 상황에 따라 현격한 차이가 발생한다.
TAB1 의 인덱스가 '제품+부서코드+매출구분' 으로 구성되었다면 3개로 분리된
정상적인 결합처리 실행계획이 수립된다.
SELECT * FROM TAB1
WHERE 제품 = 'KH1101'
AND 부서코드 ='2110'
AND 매출구분 IN ('1','5','7' )
'제품+부서코드+매출구분+매출일자' 로 구성되었다면 다음과 같은 실행계획이
수립된다.
SELECT * FROM TAB1
WHERE 제품 = 'KH1101'
AND 부서코드 ='2110'
AND 매출구분 IN ('1','5','7' )
AND 매출일자 LIKE '199805%'
이 네번째 컬럼의 연산자가 '='처럼 하나의 점이거나, LIKE, BETWEEN, >, <= 등과 같은
연속선이면 정상적인 실행계획이 나타난다. 그러나, 연속선이 아닌 여러 개의 점,
즉, 또 다른 IN 조건이 나타나면 이 조건은 '주류'가 되지 못한다.( 체크조건이 된다 )
TEST 8i
SELECT *
FROM TAB10
WHERE JE ='KH1101'
AND BU ='2110'
AND MAE1 IN ('1','5','7')
AND MAE2 IN ('19980501','19980506')
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
3 TABLE ACCESS BY INDEX ROWID TAB10
4 INDEX RANGE SCAN OF XAK_TAB10 (NONUNIQUE)
SELECT /*+ USE_CONCAT */ *
FROM TAB10
WHERE JE ='KH1101'
AND BU ='2110'
AND MAE1 IN ('1','5','7')
AND MAE2 IN ('19980501','19980506')
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
3 CONCATENATION
0 TABLE ACCESS BY INDEX ROWID TAB10
1 INDEX RANGE SCAN OF XAK_TAB10 (NONUNIQUE)
1 TABLE ACCESS BY INDEX ROWID TAB10
2 INDEX RANGE SCAN OF XAK_TAB10 (NONUNIQUE)
0 TABLE ACCESS BY INDEX ROWID TAB10
1 INDEX RANGE SCAN OF XAK_TAB10 (NONUNIQUE)
1 TABLE ACCESS BY INDEX ROWID TAB10
2 INDEX RANGE SCAN OF XAK_TAB10 (NONUNIQUE)
0 TABLE ACCESS BY INDEX ROWID TAB10
1 INDEX RANGE SCAN OF XAK_TAB10 (NONUNIQUE)
1 TABLE ACCESS BY INDEX ROWID TAB10
2 INDEX RANGE SCAN OF XAK_TAB10 (NONUNIQUE)
인덱스 컬럼의 개수에 따라, 혹은 연산자에 따라 결합처리 실행계획이 정상적으로
수립되지 않을 수가 있으므로 사용자는 주의해야 한다.
실행계획은 동일하나 처리범위의 차이가 발생 할 수 있다.
전제 조건 : 인덱스 '지역+구분+발생일자+부서코드
SQL 1
SELECT * FROM TAB1
WHERE 지역 = '서울'
AND 구분 IN ('3','1')
AND 발생일자 BETWEEN '19980601' AND '19980602'
'지역 = AND 구분 = AND 발생일자 BETWEEN' 이므로 모든 조건이 '주류' 역할
SQL1의 BETWEEN은 '연속선' 을 의미하므로 '시작점'과 '끝점'을 가진다.
그러므로 시작점에서 출발하여 스캔하다가 끝점을 마나는 순간 처리를 종료 할 수 있다.
SQL 2
SELECT * FROM TAB1
WHERE 지역 = '서울'
AND 구분 IN ('3','1')
AND 발생일자 IN ('19980601','19980602')
'지역= AND 구분= AND 발생일자 IN' 이므로 '지역'과 '구분' 조건만 '주류'
역할을 했고 '발생일자'는 '비주류' 역할을 한다.
그 이유는 결합인덱스의 컬럼이 4개 이상이면 연속된 IN을 사용했기
때문에 뒤에 사용된 IN 조건이 결합처리 실행계획으로 분리되지 못하여
단지 체크기능만 담당했기 때문이다.
SQL2의 IN은 각각이 '독립된 점'으로 그 컬럼의 범위를 의미하지 않으므로 단순히
체크기능만 하게 되는 것이다. 논리적으로 보면 IN 조건에 있는 점들을 정렬하여
가장 앞선 점에서 출발하여 마지막 점까지 수행한 후 종료할 수는 있다.
EXECUTION PLAN
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)
IN 조건이 인덱스 내에서 체크기능으로 사용된다면 LIKE나 BETWEEN과 같은 연속점보다 훨씬 불리해진다.
IN 조건을 사용했을 때 우리가 원하는 만큼으로 실행계획이 분리되었는지를 확인할 필요가 있다.
특히 결합처리 실행계획을 수립하는 데 도움을 주지 못하는 조건을 억지로 IN 으로 바꾸면
오히려 불리해진다는 사실을 명심해야 한다.
\- 테이블 정보의 Point 는 INDEX 중에서 가장 중요한 칼럼은 SC_NO 라는 것입니다.
\- 이것만 체크하시구 WHERE 절에 따른 Access/Fiter Predicates 를 체크 해보셔요
1.
select * from SC_MONTH_CNTR
where BL_ON_YY = '1999'
and BL_ON_MM='Apr'
and SC_TP='CWN'
and SC_NO ='09808';
\- Access Prdicates
"BL_ON_YY"='1999' AND "BL_ON_MM"='Apr' AND "SC_TP"='CWN' AND "SC_NO"='09808'
\- Fiter Predicates
null
2.
select * from SC_MONTH_CNTR
where BL_ON_YY = '1999'
and BL_ON_MM='Apr'
and SC_TP='CWN'
\- Access Prdicates
"BL_ON_YY"='1999' AND "BL_ON_MM"='Apr' AND "SC_TP"='CWN'
\- Fiter Predicates
null
3.
select * from SC_MONTH_CNTR
where BL_ON_YY = '1999'
and BL_ON_MM='Apr'
and SC_TP='CWN'
and to_char(SC_NO) ='09808';
\- Access Prdicates
"BL_ON_YY"='1999' AND "BL_ON_MM"='Apr' AND "SC_TP"='CWN'
\- Fiter Predicates
"SC_NO"='09808'
4.
select * from SC_MONTH_CNTR
where BL_ON_YY = '1999'
and BL_ON_MM='Apr'
and to_char(SC_TP)='CWN'
and SC_NO ='09808';
\- Access Prdicates
"BL_ON_YY"='1999' AND "BL_ON_MM"='Apr' AND "SC_NO"='09808'
\- Fiter Predicates
"SC_NO"='09808' AND "SC_TP"='CWN'
These two columns are extremely helpful for checking why the optimizer isn't
doing what you expect, and they merit a little description.
As a quick approximation, though, you could say that
the access_predicates tells you how Oracle is getting into a table, or joining two tables;
the filter_predicates tells you how Oracle is discarding the irrelevant data items
(which could be table rows or index entries) after getting into an object.
dbms_xplan.display shows the predicates by default.