중복사용된 IN조건이 결합처리 실행계획에 참여할 수 있는 기준과 약간의 보조장치를 곁들이면 우리의 의도대로 유도할 수 있는 방법, 그리고 도저히 방법이 없는 경우에도 이를 해결할 수 있는 몇가지 방법들을 설명할 것이다. 이러한 활용원리를 이해하면 엑세스 효율 향상을 위해 IN을 활용하는 방법은 에플리케이션을 개발하는데 있어서 하나의 확실한 무기가 될 것이다.
처리주관 인덱스에 속한 컬럼 중에서 중복 사용된 IN조건이 있다고 해서 모든 IN이 항상 결합처리 실행계획으로 분리되는 것이 아니다. 인덱스를 구성하고 있는 컬럼의 개수와 주어진 값이 상수값이냐, 서브쿼리냐에 따라 차이가 있다.
중복 사용된 IN조건이 실행계획에 미치는 영향을 알아보기 위해서 상수값으로 부여된 IN조건의 몇가지 형태를 살펴보기로 하자
( 단, 여기서 말하는 상수값이란 변수인 경우도 포함)
중복 사용된 IN조건에 서브쿼리가 포함되어 있으면 상수값들만 있을 때와는 큰 차이가 있다. 특히 서브쿼리의 IN 조건과 상수값의 IN 조건의 순서에 따라 실행계획은 크게 달라진다.
서브쿼리가 있는 중복 사용된 IN 조건이 실행계획에 미치는 영향을 알아보기 위해서 몇가지 형태를 살펴보기로 하자.
SELECT ..................
FROM TAB2
WHERE 제품 = 'KH2200'
AND 부서코드 IN ( SELECT 부서코드
FROM 부서
WHERE 부서코드 LIKE '21%' )
and 매출구분 IN ( '1', '5', '7' ) ;
이렇게 하기 곤란한 경우는 다음 장에서 제시하는 방법을 사용해야 한다.
지금까지 설명한 중복된 IN의 실행계획의 수립상태를 다음 도표를 통해 정리합니다.
(가정 : 여기서 사용된 인덱스는 COL1, COL2, COL3, COL4 순으로 구성)
'모든 점을 서브쿼리에서 생성하여 메인쿼리에 공급해 주는 방법' 으로 옵티마이저 대신에 우리가 '점 집합' 을 만드는 서브쿼리를 이용하여 메인쿼리에게 제공자 역활을 하도록 한다면 분리된 실행계획과 다를 것이 없다.
'점 집합' 이란 모두가 ' = ' 인 조건을 의미하므로, 메인쿼리는 항상 '=' 조건에 따라 수행하므로 불필요한 액세스가 발생하지 않는다. 이러한 '점 집합' 을 만드는 방법은 앞서 우리가 원하는 임의의 집합을 만들었던 다양한 방법들이 동원된다. 가령 데이터를 복제하거나 인라인뷰, 사용자 지정 저장형 함수 등을 활용한 것이다.
그러면 지금부터 사례를 통해 다양한 활용 방법들을 알아보기로 한다.
SELECT ..................
FROM TAB2
WHERE 제품 = 'KH2200'
and 부서코드 LIKE :DEPT||'%'
and 매출구분 IN ( '1', '5' )
and 매출일자 BETWEEN :S_DATE and :E_DATE ;
이러한 문제를 해결할 수 있는 다음 방법을 살펴보자.
SELECT ..................
FROM TAB2
WHERE ( 제품, 부서코드, 매출구분, 매출일자 ) IN
( SELECT 'KH2200',
부서코드,
substr(NO,2,1),
YMD
FROM 부서 x, COPY_T y, YMD_DUAL z
WHERE 부서코드 LIKE :DEPT||'%'
and y.NO IN ( '1', '5' )
and YMD BETWEEN :S_DATE and :E_DATE )
실무에서 자주 발생하는 주의 사항들을 유형별로 소개한다.
IN 서브쿼리가 우리가 원하는 공급자 역활을 했음에도 불구하고, 메인쿼리가 이 결과값을 제대로 사용할 수 없게 인덱스가 구성되어 있는 경우, 오히려 부하를 크게 증가시킨다.
좀더 쉽게 설명하면, 서브쿼리가 제공한 결과값이 '주류' 역활을 하지 못하고, '비주류' 역활을 하게 됨으로써, 제공받은 각각의 값들마다 메인쿼리가 중복해서 수행하게 된다는 것이다.
좀 더 자세한 내용을 알아보기 위해 다음과 같은 사례를 살펴보기로 하자.
SELECT *
FROM ORD_ITEM_T
WHERE 주문일자 BETWEEN '19980501' AND '19980510'
and 제품 코드 IN (SELECT 제품코드
FROM PRODUCT
WHERE 제품명 LIKE 'SM%')
and 금액 > 1000000 ;
이 SQL의 실행계획을 확인해 보자.
Rows Execution Plan
------- ---------------------------------------------------
530 NESTED LOOPS
530 TABLE ACCESS (BY ROWID) OF 'PRODUCT'
531 INDEX (RANGE SCAN) OF 'PRODUCT_NAME_IDX(NON-UNIQUE)'
260 TABLE ACCESS (BY ROWID) OF 'ORD_ITEM_T'
56800 INDEX (RANGE SCAN) OF 'ORD_ITEM_IDX(NON-UNIQUE)'
이러한 문제를 해결하기 위해 다음과 같은 방법을 사용해 보자.
SELECT *
FROM ORD_ITEM_T
WHERE ( 주문일자, 제품코드 ) IN
( SELECT YMD, 제품코드
FROM YMD_DUAL x, PRODUCT y
WHERE 주문일자 BETWEEN '19980501' AND '19980510'
AND 제품명 LIKE 'SM%' )
AND 금액 > 1000000 ;
공급자 역활을 위해 추가된 서브쿼리가 확인자로 사용되면 심각한 문제를 유발시킨다는 것을 언급했다. 이러한 문제가 발생하지 않도록 몇가지 방법을 소개한다.
다음의 SQL은 공급자 역활을 위한 서브쿼리가 확인자 역활을 하게 된 경우다.
SELECT 종목코드, count(*), sum(계약금액)/1000
FROM 계약내역
WHERE 종목코드 IN ( SELECT 종목코드
FROM 종목 )
AND 계약일 = to_char(sysdate,'yyyymmdd')
(1) 서브쿼리의 조건에 확실한 선처리 조건을 부여하는 방법
SELECT 종목코드, count(*), sum(계약금액)/1000
FROM 계약내역
WHERE 종목코드 IN ( SELECT 종목코드
FROM 종목코드테이블
WHERE 종목코드 > ' ' )
and 계약일 = to_char(sysdate,'yyyymmdd')
(2) 서브쿼리에 있는 SELECT-LIST 컬럼을 가공시키는 방법
이 방법은 IN의 서브쿼리에 사용된 SELECT-LIST인 '종목코드'를 강제로 가공시켜, 서브쿼리가 나중에 수행될 때 사용할 연결고리를 못쓰게 함으로써 옵티마이져가 서브쿼리를 먼저 수행하게 한다. (데이터베이스 버전에 따라 차이 있을 수 있음)
SELECT 종목코드, count(*), sum(계약금액)/1000
FROM 계약내역
WHERE 종목코드 IN ( SELECT 종목코드 || ''
FROM 종목코드테이블 )
AND 계약일 = to_char(sysdate,'yyyymmdd')
(3) 서브쿼리를 GROUP BY 시키는 방법
서브쿼리에 메인쿼리의 컬럼을 가지지 않고 그룹함수를 사용했다면 서브쿼리가 먼저 수행할 가능성이 매우 높다. 이와같이 옵티마이져 특성을 이용하여 서브쿼리가 먼저 실행되도록 유도한다.
SELECT 종목코드, count(*), sum(계약금액)/1000
FROM 계약내역
WHERE 종목코드 IN ( SELECT 종목코드
FROM 종목코드테이블
GROUP BY 종목코드 )
AND 계약일 = to_char(sysdate,'yyyymmdd')
(4) 힌트(PUSH_SUBQ)를 사용하는 방법
서브쿼리를 먼저 수행시켜 달라는 'PUSH_SUBQ' 힌트를 사용하여 서브쿼리가 먼저 수행되도록 할 수 있다.
SELECT /*+ PUSH_SUBQ */
종목코드, count(*), sum(계약금액)/1000
FROM 계약내역
WHERE 종목코드 IN ( SELECT 종목코드
FROM 종목코드테이블 )
AND 계약일 = to_char(sysdate, 'yyyymmdd')
조건을 만족하는 첫번째 로우를 찾으면 더 이상 처리하지 않고, 멈추도록 하기 위해서 EXISTS나 ROWNUM을 사용한다. 만약 처리주관 범위가 IN이나 OR일 때, 즉 결합처리 실행계획이 수립된 경우 ROWNUM을 사용하면 처리를 멈추기 못하고 전체 범위를 모두 처리한다.
이해를 돕기 위해 다음의 SQL을 보자. 1998년 1월에 발생한 계약 중에서 종목코드가 '01' 이거나 '02' 또는 '04' 인 데이터를 한건만 추출하고자 한다. (인덱스가 '종목코드 + 계약일자' 로 구성)
이러한 문제를 피할갈 수 있는 방법은 다음 SQL의 결합처리 실행계획을 수립하지 않도록 IN조건을 BETWEEN 으로 대치시킨 것이다.