꿈꾸는 개발자, DBA 커뮤니티 구루비

반복연결(Loop Query)

Declare Cursor 를 통해 기준 테이블을 하나씩 Fetch 한 다음 거기서 얻은 상수값을 For Loop 에서 다시 연결하는 SQL을 수행하는 방법.

2.1.1. 전체범위 처리방식에서의 비교

조인(Nested Loops)과 반복연결(Loop Query) 방식의 비교

공통점 : TAB1의 자료 1000건을 읽어 1000번의 인덱스 스캔에 의한 TAB2 테이블 랜덤엑세스
차이점 : 조인은 단 하나의 SQL로 위 과정을 수행하지만 반복연결(Loop Query) 에서는 1001개의 SQL이 수행됨
조인은 SQL 내에서 조인된 테이블의 모든 컬럼을 마음대로 가공하여 사용할 수 있다.
반복연결(Loop Query)은 별도의 언어를 통해 어플리케이션 내에서 추가적인 가공을 해야 한다.
결국 조인이 유리하다.
항상 One SQL 이 유리할까?

{section}

반복연결이 유리할 수도 있는 경우 1

{column:width=20}

조인처리


SELECT a.fld1, ....., a.fldn
     , b.col1, ....., b.coln
  FROM tab2 b, tab1 a
 WHERE a.key1 = b.key2
   AND a.fld1 = '10'
 ORDER BY a.fld2
;

{column}

{column:width=20}

반복연결


DECLARE
CURSOR cur1 IS
SELECT a.fld1, ....., a.fldn
  FROM tab1 a
 WHERE a.fld1 = '10'
 ORDER BY a.fld2
;
BEGIN
  FOR a IN cur1 LOOP
    SELECT b.col1, ....., b.coln
      FROM tab2 b
     WHERE b.key2 = a.key1
    ;
  END LOOP;
END;
/

{column}

{column:width=20}

인라인뷰



SELECT a.fld1, ....., a.fldn
     , b.col1, ....., b.coln
  FROM tab2 b
     , (SELECT a.fld1, ....., a.fldn
          FROM tab1 a
         WHERE a.fld1 = '10'
         ORDER BY a.fld2
        ) a
 WHERE a.key1 = b.key2
;

{column}

{column:width=40}
{column}

{section}

1) 조인 후 정렬하는 쿼리
2) 우선 a 를 정렬 한 후 b를 반복적으로 읽어오도록 변경한 쿼리

b에 한해서는 부분범위 처리가 가능해 지므로 일부분만 읽어 올 경우엔 반복쿼리가 유리하다.
단, 온라인 조회가 아닌 전체 자료를 모두 읽어야 하는 경우라면 유리할 것이 없다.

3) 인라인뷰를 이용한 조인쿼리

인라인뷰를 이용한다면 반복쿼리의 효과를 낼 수 있다.
반복쿼리처럼 조인쿼리가 가능하므로 굳이 반복쿼리를 사용해야 할 이유가 없다.

{section}

반복연결이 유리할 수도 있는 경우 2

{column:width=20}

조인처리


SELECT b.부서명
     , SUM(a.매출액) 매출액
  FROM tab1 a, tab2 b
 WHERE a.부서코드 = b.부서코드
   AND a.매출일 LIKE '200503%'
 GROUP BY b.부서명
;

{column}

{column:width=20}

반복연결


DECLARE
CURSOR cur1 IS
SELECT a.부서코드
     , SUM(a.매출액) 매출액
  FROM tab1 a
 WHERE a.매출일 LIKE '200503%'
 GROUP BY a.부서코드
;
BEGIN
  FOR a IN cur1 LOOP
    SELECT b.부서명
      FROM tab2 b
     WHERE b.부서코드 = a.부서코드
    ;
  END LOOP;
END;
/

{column}

{column:width=20}

인라인뷰



SELECT b.부서명
     , a.매출액
  FROM (SELECT a.부서코드
             , SUM(a.매출액) 매출액
          FROM tab1 a
         WHERE a.매출일 LIKE '200503%'
         GROUP BY a.부서코드
        ) a
     , tab2 b
 WHERE WHERE b.부서코드 = a.부서코드
;

{column}

{column:width=40}
{column}

{section}

1) 조인 후 집계(Group By)하는 쿼리
2) 우선 a 를 Group By 한 후 b를 반복적으로 읽어오도록 변경한 쿼리

a를 Group By 하여 b를 읽어 올 횟수가 줄어든다.

3) 인라인뷰를 이용한 조인쿼리

인라인뷰를 이용한다면 반복쿼리의 효과를 낼 수 있다.
반복쿼리처럼 조인쿼리가 가능하므로 굳이 반복쿼리를 사용해야 할 이유가 없다.

{section}

반복연결이 유리할 수도 있는 경우 3

{column:width=20}

조인처리


SELECT a.*
     , DECODE(a.입출고구분, '1', b.거래처명
                          , '2', c.공정명
                          , '3', d.창고명  ) 입출고처명
  FROM 입출정보 a
     , 거래처 b
     , 공정 c
     , 창고 d
 WHERE a.입출일자 LIKE '200503%'
   AND b.거래처코드(+) = DECODE(a.입출고구분, '1', a.입출고처코드)
   AND c.공정코드  (+) = DECODE(a.입출고구분, '2', a.입출고처코드)
   AND d.창고코드  (+) = DECODE(a.입출고구분, '3', a.입출고처코드)
;

스칼라서브쿼리


SELECT a.*
     , DECODE(a.입출고구분
     , '1', (SELECT b.거래처명 FROM 거래처 b WHERE b.거래처코드 = a.입출고처코드)
     , '2', (SELECT c.공정명   FROM 공정   c WHERE c.공정코드   = a.입출고처코드)
     , '3', (SELECT d.창고명   FROM 창고   d WHERE d.창고코드   = a.입출고처코드)
     ) 입출고처명
  FROM 입출정보 a
 WHERE a.입출일자 LIKE '200503%'
;

{column}

{column:width=20}

반복연결


DECLARE
CURSOR cur1 IS
SELECT a.*
  FROM 입출정보 a
 WHERE a.입출일자 LIKE '200503%'
;
BEGIN
  FOR a IN cur1 LOOP
    IF a.입출고구분 = '1' THEN
      SELECT b.거래처명
        FROM 거래처 b
       WHERE b.거래처코드 = a.입출고처코드
      ;
    ELSIF a.입출고구분 = '2' THEN
      SELECT c.공정명
        FROM 공정 c
       WHERE c.공정코드 = a.입출고처코드
      ;
    ELSIF a.입출고구분 = '3' THEN
      SELECT d.창고명
        FROM 창고 d
       WHERE d.창고코드 = a.입출고처코드
      ;
    END IF;
  END LOOP;
END;
/

{column}

{column:width=20}
{column}

{column:width=40}
{column}

{section}

1) 연관된 모든 테이블을 아우터 조인

무조건 조인해야 하는 불리함
2) a의 구분값에 따라 b,c,d 를 선택하여 읽어오도록 변경한 쿼리
무조건 조인할 필요 없이 필요한 테이블만 조인하게 됨

3) 스칼라서브쿼리를 이용한 조인쿼리

스칼라서브쿼리를 이용한다면 반복쿼리의 효과를 낼 수 있다.
반복쿼리처럼 조인쿼리가 가능하므로 굳이 반복쿼리를 사용해야 할 이유가 없다.

2.1.2. 부분범위 처리방식에서의 비교

부분범위로 수행되는 SQL은 주어진 처리범위가 아무리 크더라도 매우 빠른 수행속도를 얻을 수 있다.

조인(Nested Loops)과 반복연결(Loop Query) 방식의 비교

공통점 : 부분범위에 필요한 운반단위만큼만 패치하고 멈출 수 있다.
차이점 : 조인은 단 하나의 SQL로 위 과정을 수행하지만 반복연결(Loop Query) 에서는 여러개의 SQL이 수행됨

비교	조인(One SQL)	반복연결(Loop Query)
컬럼가공	SQL 내에서 조인된 테이블의 모든 컬럼을 마음대로 가공	별도의 언어를 통해 어플리케이션 내에서 추가적인 가공
병렬처리	SQL 단위로 병렬처리 가능	Loop 내에서는 병렬처리 포기
튜닝	엑세스 경로를 변경해 준다거나 조인방법을 바꾸는 등 간단하게 수행속도를 향상시킬 수 있다.	엑세스 경로 를 바꾸려면 코딩을 다시 해야 함
코딩	SQL만 약간 고쳐주면 된다.	SQL 및 별도의 언어도 함께 수정되어야 하며 코딩량도 많다.

2.1. 조인과 반복연결(loop query)의 비교 (by manon94) [2011.08.15]

2.1.1. 전체범위 처리방식에서의 비교

반복연결이 유리할 수도 있는 경우 1

반복연결이 유리할 수도 있는 경우 2

반복연결이 유리할 수도 있는 경우 3

2.1.2. 부분범위 처리방식에서의 비교