4.서브쿼리(Subquery)를 이용한 데이터 연결

4.1 개념 및 특징

4.1.1 먼저 수행하는 서브쿼리의 조인과의 차이

제공자역할

4.1.2 나중 수행하는 서브쿼리의 조인과의 차이

SELECT 자재코드, 자재명, 규격, 안전재고, .................

FROM 자재 x

WHERE 자재구분 = '배관자재'

      and 안전재고 >= (SELECT 재고수량

                                    FROM   자재일일재고

                                    WHERE  y.자재코드 = x.자재코드

                                           and  y.년월일 = to_char(sysdate, 'yyyymmdd'));

Execution Plan

\

---\-

SELECT STATEMENT

  {*}FILTER{*}

    TABLE ACCESS (BY ROWID) OF '자재'

      INDEX (RANGE SCAN) OF '자재구분_INDEX' (NON-UNIQUE)

    TABLE ACCESS (BY ROWID) OF '자재일일재고'

      INDEX (RANGE SCAN) OF '년월일_INDEX' (NON-UNIQUE)

\*FILTER 가 나타난 이유는 서브쿼리가 M쪽이므로 그대로 연결하면 원래의 메인쿼리의 집합이 늘어나기 때문이다.
메인쿼리의 해당 건마다 서브쿼리에 대응되는 로우가 하나라도 있는 것이 확인되면 해당 건에 대한 처리를 종료함으로 써 메인쿼리의 집합은 변하지 않는다.     
서브쿼리의 집합은 언제나 1이 되어야 하믄로 먼저 수행될 때에는 유일한 집합을 만들어 메인쿼리에 결과를 공급하고,
나중에 수행되면 존재 유무만 판단하는 방식으로 처리한다.

 

4.2 서브쿼리의 실행계획

서브쿼리도 조인과 마찬가지로 처리방식과 순서의 차이로 인해 발생하는 하는 차이는 크다.

=> 고객테이블 Index : '납입자'
=> 청구테이블 Index : '고객번호+청구년월'

1600초 : 서브쿼리가 나중에 수행됨.
- 연결고리가 '고객번호'

확인자 역할을 하는 서브쿼리: 수행시간 1600초

제공자 역할을 하는 서브쿼리: 수행시간 0.1초

UPDATE 청구 x

   SET 입금액 = nvl(입금액,0) + :in_amt

WHERE  청구년월 = '199803'

   and 고객번호 IN (SELECT 고객번호

                                 FROM 고객 y

                                 WHERE 납입자 = :in_cust

                                          and y.고객번호 = x.고객번호 );

UPDATE 청구 x

   SET 입금액 = nvl(입금액,0) + :in_amt

WHERE  청구년월 = '199803'

   and 고객번호 IN (SELECT 고객번호

                                 FROM 고객 y

                                 WHERE 납입자 = :in_cust );

4.2.1 서브쿼리의 실행 순서

제공자: 서브쿼리 내에 메인쿼리의 항목이 존재하지 않아야 한다.
서브쿼리가 제공한 결과를 받는 메인쿼리의 컬럼이 반드시 처리주관(Driving Column) 컬럼이 되어야 한다.



SELECT * 
FROM TAB1 
WHERE COL1 = '111' 
  and COL2 IN (SELECT FLD2
               FROM TAB2 
               WHERE FLD3 like 'ABC%') 
  and COL3 IN (1,5) 
  and COL4 like '123%'

인덱스 구조에 따라 구분되어지는 상황

COL1: 확인자 역활 (인덱스가 COL1+COL3, COL1+COL4, COL3+COL4+COL1 동일)
COL1+COL2: 서브쿼리는 제공자 역활
COL4+COL2: 제공자 역활, COL4가 '='이 아닌 'like"로 사용되었기에 비효율적인 액세스가 발생
COL1+COL3+COL2: COL2 제공자 , 연속으로 사용된 IN은 상황에 따라 '결합처리' 실행계획이 될수도 있고 그렇지 않을 서브쿼리가 항상 1쪽이라면 서브쿼리를 사용하기보다는 조인을 사용하기 바란다.
서브쿼리를 사용했을 때 제공자가 되느냐, 확인자가 되느냐에 따라서 엄청난 차이를 가져온다.
최소의 인덱스로 다양한 액세스 형태를 처리할 수 있는 종합적인 전략을 세울 수 있음을 명심하기 바란다.

4.2.2 SORT MERGE형태의 수행

서브쿼리가 M의 집합일 때는 SORT(UNIQUE)를 수행하여 1의 집합을 만든 후 머지한다.

4.2.3 필터(Filter) 형식으로 처리되는 경우


SELECT * 
FROM   ORDER x 
WHERE ORDDATE LIKE '9706%' 
AND    EXISTS (SELECT 'X' 
               FROM   DEPT y 
               WHERE y.DEPTNO = x.SALDEPT 
               AND   y.TYPE1='1') 
====================================================
ROWS    Execution Plan 
----    --------------------------------------------------- 
3200    FILTER 
3200      TABLE ACCESS (BY ROWID) OF 'ORDER' 
3201        INDEX (RANGE SCAN) OF 'ORDDATE_INDEX' (NON_UNIQUE) 
10        TABLE ACCESS (BY ROWID) OF 'DEPT' 
10          INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UIQUE)

① 먼저 ORDDATE_INDEX에서 '9706%'를 만족하는 첫번째 로우를 읽고 그 ROWID로 ORDER 테이블의 해당 로우를 액세스한다.
② 그 로우가 가지고 있는 SALDEPT와 버퍼에 있는 DEPT와 비교한 결과가 같지 않으므로 DEPT 테이블의 기본키를 이용해 액세스한 후 체크한다. 체크결과 조건을 만족하면 운반단위에 태우고 아니면 버린다
③ 액세스한 DEPT 테이블의 비교컬럼값들을 버퍼에 저장한다.
④ ORDDATE_IDEX의 두번째 로우에 대한 ORDER 테이블 로우를 액세스한 후 버퍼와 체크한다. 이때 ORDER 테이블의 SALDEPT와 버퍼의 DEPT가 동일하면 버퍼와의 비교만 수행하며, DEPT 테이블은 액세스하지 않는다. 버퍼의 DEPT와 일치하지 않을 때만 DEPT테이블을 액세스하여 비교하고 그 값을 다시 버퍼에 저장한다. 버퍼는 하나의 값만 저장할 수 있으므로 앞서 저장된 값은 갱신된다.
⑤ 이와 같은 방법으로 ORDDATE_INDEX 의 처리범위가 완료될 때까지 수행한다

4.3 유형별 활용

4.3.1 M:M관계의 비교

구분	인라인 뷰 조인	UNION,GROUP	사용자지정 저장형 함수	서브쿼리
M:M관계의 데이터 연결	○	○	○	○
결과의 추출을 원할 때	○	○	○	X
다양한 추출컬럼이 필요할 때	○	○	△	X
양측 OUTER 조인	X	○	X	X
독자적으로 범위를 줄일 수 있을 때	○	○	○	○
다른 쪽에서 결과를 받는 것이 유리	X	X	○	○
배타적 관계의 연결	X	○	○	△
연결할 집합이 유사하지 않을 때	○	△	○	○
부분범위처리	△	X	○	△
기본키와 외부키가 아닌 경우의 연결	○	○	○	○
단순히 조건 체크만 원할 때	△	X	△	○
단순히 조건의 상수값만 제공할 때	△	X	△	○

4.3.2 부정형(Anti)조인

연결고리 조건중에 부정형이 들어있을경우 어렵다.
NOT IN, NOT EXISTS 를 사용한다


SELECT *
FROM TAB1
WHERE COL1 like 'ABC%'
  and  NOT EXISTS  (SELECT FLD2
                    FROM TAB2
                    WHERE FLD2 =  COL2
                    AND   FLD3 between '19980101' and '19980131');

필터형식으로 처리되는 부정형 조인의 최대 장점은 NESTED LOOPS 조인과 같이 선행 집합에서 요구한 루우들에 대해서만 수행한다는 것이다.
부분범의처리가 가능하다는 것.
부정형으로 연결되었더라도 부분범위처리 자격은 계속 유지된다.

4.3.3 부분범위처리로의 유도

제공자 역할을 할 때 부분범위처리로 유도하는 방법
확인자의 역할을 할 때 부분범위처리 유도


SELECT COL1, COL2, COL4, COL5 
FROM TAB1 
WHERE COL3 between '1110' and '3999' 
  AND EXISTS (SELECT ' ' 
                FROM  TAB2 
               WHERE  FLD1 = COL1 
                 AND  FLD2 = COL2 
                 AND  FLD3 like '199803%' 
               GROUP BY FLD1, FLD2 
HAVING sum(FLD4) > 0);

TAB1 과 TAB2는 1:M 의 관계를 갖는다
상수값을 제공하는 서브쿼리는 부분범위처리를 안하고, 메인쿼리를 부분범이처리가 되도록 유도.

4.3.4 ANY, ALL을 활용한 서브쿼리

ALL 경우


SELECT *
FROM TAB1
WHERE COL1 >= ALL (SELECT FLD1
FROM TAB2
WHERE FLD2 LIKE 'ABC%');

---------------------------------------------------------------------

SELECT *
FROM TAB1
WHERE COL1 >= (SELECT MAX(FLD1 )
FROM TAB2
WHERE FLD2 LIKE 'ABC%');
=> ALL를 사용하면 주로 필터처리 실행계획이 수립되면 대부분 확인자 역활

ANY 경우



SELECT *
FROM TAB1
WHERE COL1 >= ANY(SELECT FLD1
FROM TAB2
WHERE FLD2 LIKE 'ABC%');

---------------------------------------------------------------------

SELECT *
FROM TAB1
WHERE COL1 >= (SELECT MIN(FLD1 )
FROM TAB2
WHERE FLD2 LIKE 'ABC%');

 => ANY를 사용하는 경우는 서브쿼리를 확인자로 사용, MIN는 제공자 역활

ANY대신 SOME 사용가능
ANY와 ALL은 =,<>,>,<,>=,<= 연산자와 같이 사용

4.3.5 발생 데이터의 목록처리

4.4 서브쿼리 활용시 주의사항

4.4.1 조인문에서 서브쿼리의 실행순서

힌트사용
먼저 수행시킬 집합을 인라인뷰로 묶고 나중에 조인할 집합을 연결하여 필요시 힌트를 사용하여 실행계획 유도
사용자지정 저장형 함수를 활용

4.4.2 MIN, MAX값을 가진 로우 액세스

문서에 대하여

최초작성자 : 김도희
최초작성일 : 2009년 5월 12일
이 문서는 오라클클럽 01.대용량 데이터베이스 스터디 모임에서 작성하였습니다.

4. 서브쿼리 이용한 데이터 연결 (by kdoho) [2009.05.12]