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

ORACLE에서 제공하는 계층 쿼리 CONNECT BY 구문은 계급적이고 수직적인 순환구조의 데이터를 표현하는 데 특화돼 있다. 이번 시간에는 CONECTY BY 구문이 최적의 성능을 보장받을 수 있는 방법을 설명한다.

[리스트 1]의 예제를 살펴보자

[리스트 1] 사용 예제

SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.LOC, C.JOB, LEVEL
  FROM EMP A, DEPT B, JOB_SAL C
 WHERE B.LOC='CHICAGO'
   AND A.DEPTNO =B.DEPTNO(+)
   AND A.JOB=C.JOB(+)
 START WITH A.JOB='PRESIDENT'
CONNECT BY PRIOR A.EMPNO=A.MGR; 


10.2.0.4 실행계획)
Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=9 Card=2 Bytes=164)
1    0   FILTER
2    1     CONNECT BY (WITH FILTERING)
3    2       NESTED LOOPS (OUTER) (Cost=10 Card=3 Bytes=246)
4    3         HASH JOIN (OUTER) (Cost=7 Card=3 Bytes=186)
5    4           TABLE ACCESS (FULL) OF 'SCOTT.EMP' (TABLE) (Cost=3 Card=3 Bytes=111)
6    4           TABLE ACCESS (FULL) OF 'SCOTT.JOB_SAL' (TABLE) (Cost=3 Card=2 Bytes=50)
7    3         TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.DEPT' (TABLE) (Cost=1 Card=1 Bytes=20)
8    7           INDEX (UNIQUE SCAN) OF 'SCOTT.PK_DEPT' (INDEX (UNIQUE)) (Cost=0 Card=1)
9    2       NESTED LOOPS (OUTER) (Cost=9 Card=2 Bytes=164)
10    9         HASH JOIN (OUTER) (Cost=7 Card=2 Bytes=124)
11   10           HASH JOIN
12   11             CONNECT BY PUMP
13   11             TABLE ACCESS (FULL) OF 'SCOTT.EMP' (TABLE) (Cost=3 Card=2 Bytes=74)
14   10           TABLE ACCESS (FULL) OF 'SCOTT.JOB_SAL' (TABLE) (Cost=3 Card=2 Bytes=50)
15    9         TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.DEPT' (TABLE) (Cost=1 Card=1 Bytes=20)
16   15           INDEX (UNIQUE SCAN) OF 'SCOTT.PK_DEPT' (INDEX (UNIQUE)) (Cost=0 Card=1)
----------------------------------------------------------

ORACLE의 Optimizer는 계층질의(CONNECT BY) 구문에 대해 [리스트 1]의 예제처럼 실행계획을 작성한다. 즉, 접근 순서는 START WITH → CONNECT → WHERE이다.

그렇기 때문에 대용량 데이터에서 쿼리의 최적 성능을 보장 받기 위해서는 먼저 읽는 START WITH 절의 EMP 테이블의 JOB 컬럼에 INDEX가 매우 중요하고, 그 다음으로는 CONNECT BY 절의 PRIOR 쪽 칼럼이 상수가 되기 때문에 MGR 컬럼의 INDEX가 중요하다.

여기서 한 가지 주의해야 할 점은 이러한 순서로 작업이 진행되기 때문에 만약 WHERE 조건 절을 통해 필터링한 데이터를 계층으로 만들어야 할 경우에는, 조건에 맞는 데이터를 먼저 가져온 후에 계층 쿼리로 풀어야 한다는 것이다.

만약 그렇지 않으면 WHERE 절이 START WITH과 CONNECT BY 다음으로 읽히기 때문에 필터링한 데이터만 계층으로 가져오지 못하고 중복이나 혼합이 발생할 가능성도 있다.

또한 [리스트 1]의 실행 계획을 보면 동일한 테이블에 대해 두 번씩 읽는 비효율이 발생하고 있다.

이는 해당 쿼리에서 추출하고자 하는 데이터가 대용량이었다면 심각한 성능 저하로 이어질 수 있으며, 이러한 경우에는 최종 결과 데이터에 꼭 필요한 데이터를 가능하다면 부분범위 처리를 통해 미리 추출한 후, 추출된 데이터를 계층화할 수 있도록 유도하는 것이 중요하다.

하지만 이렇게 테이블을 두 번씩 읽는 문제는 11g로 버전 업그레이드되면서 한 번만 읽는 형태로 해결됐고 이러한 근거에서 CONNECT BY 절은 11g에서 성능적인 측면이 매우 향상된 것으로 볼 수 있다.

[리스트 2] 11g에서 개선된 CONNECT BY 절

11.2.0.1 실행계획) 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=14 Bytes=2K)
   1    0   FILTER
   2    1     CONNECT BY (NO FILTERING WITH START-WITH)
   3    2       HASH JOIN (OUTER) (Cost=10 Card=14 Bytes=1K)
   4    3         HASH JOIN (OUTER) (Cost=7 Card=14 Bytes=1K)
   5    4           TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=532)
   6    4           TABLE ACCESS (FULL) OF 'JOB_SAL' (TABLE) (Cost=3 Card=2 Bytes=82)
   7    3         TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=4 Bytes=80)
----------------------------------------------------------

마지막으로 CONNECT BY 구문의 활용을 높여줄 수 있는 관련 함수들에 대해 알아보자.

CONNECT_BY_ROOT

계층 쿼리에서 LEVEL의 최상위 로우에 대한 정보를 제공한다.

[리스트 3] CONNECT_BY_ROOT 사용 예제

SELECT  LPAD(' ' , 3*(LEVEL -1))|| ENAME ENAME 
      , EMPNO
      , CONNECT_BY_ROOT EMPNO "ROOT EMPNO" 
      , LEVEL
  FROM EMP
 START WITH JOB='PRESIDENT'
CONNECT BY PRIOR EMPNO=MGR
   AND ENAME LIKE '%S%';

SYS_CONNECT_BY_PATH

계층구조 쿼리에서 현재 로우까지의 PATH 정보를 제공한다.

[리스트 4] SYS_CONNECT_BY_PATH 사용 예제

SELECT  LPAD(' ' , 3*(LEVEL -1))|| ENAME ENAME 
      , SYS_CONNECT_BY_PATH(ENAME,'/') "PATH"
  FROM EMP
 START WITH JOB='PRESIDENT'
CONNECT BY PRIOR EMPNO=MGR
   AND ENAME LIKE '%S%';

CONNECT_BY_ISLEAF

계층구조에서 로우의 최하위 레벨(Leaf) 여부를 반환한다(최하위 1, 아니면 0).

[리스트 5] CONNECT_BY_ISLEAF 사용 예제

SELECT  LEVEL
      , SUBSTR(SYS_CONNECT_BY_PATH(ENAME,','),2) PATH
  FROM EMP
 WHERE CONNECT_BY_ISLEAF = 1
 START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO=MGR;

ORDER SIBLINGS BY

계층구조 쿼리에서 상관관계를 유지하면서 정렬이 가능하게 한다.

[리스트 6] ORDER SIBLINGS BY 사용 예제

-- 계층에 맞게 정렬
SELECT  LPAD(' ' , 3*(LEVEL -1))|| ENAME ENAME 
      , ENAME ENAME2
      , EMPNO
      , LEVEL
  FROM EMP
 START WITH JOB='PRESIDENT'
CONNECT BY NOCYCLE PRIOR EMPNO=MGR
 ORDER SIBLINGS BY ENAME2;
 
-- ORDER BY 정렬은 계층에 관계없이 정렬
SELECT  LPAD(' ' , 3*(LEVEL -1))|| ENAME ENAME 
      , ENAME ENAME2
      , EMPNO
      , LEVEL
  FROM EMP
 START WITH JOB='PRESIDENT'
CONNECT BY NOCYCLE PRIOR EMPNO=MGR
 ORDER BY ENAME2;

이렇듯 복잡할 것 같은 데이터 계층의 표현이 ORACLE에서는 간단한 구문을 통해 구현 가능함을 확인할 수 있다. 이에 우리는 성능의 최적화까지 보장 받을 수 있도록 실행계획을 분석하고 해당 구문을 사용함으로써 한층 업그레이드된 SQL 활용 능력을 갖게 될 것이다