계층 쿼리의 사용법
계급적이고 수직적인 데이터를 표현하는 방법으로 ORACLE에서는 CONNECT BY 구문을 제공하고 있다. 하지만 많은 개발자들은 사용이 어렵다는 점과 성능상의 이유로 해당 구문의 사용을 기피하고 있다. 이러한 이유로 CONNECT BY 구문의 다양한 사용법과 해당 구문으로 최적의 성능을 보장받을 수 있는 방법에 대해 알아볼 필요가 있다
첫 번째로 START WITH, CONNECT BY 기본 사용문법 및 각 구문의 특징에 대해 알아보자.
SELECT column_list
FROM table_joins | tables
[WHERE join_conditions and/or filtering_conditions]
[START WITH conditions]
CONNECT BY [NOCYCLE] conditions
START WITH 절은 계층관계가 시작되는 루트 행을 지정하기 위한 구문이다. 해당 구문은 생략이 가능하지만 생략하게 되면 대상 테이블 내에 존재하는 모든 행을 루트 행으로 간주하고 계층관계를 검색해 결과 행들 중의 일부는 중복 행이 발생할 가능성이 있다. 그러므로 CONNECT BY 구문을 사용할 때는 START WITH 절을 사용하는 것을 권장한다. CONNECT BY 절의 조건은 한 쌍의 행에 대한 부모-자식관계를 정의하기 위한 구문이다. 해당 구문은 조건 내에 PRIOR 연산자를 이용해 부모 행의 컬럼 값을 지정한다. 부모 행의 컬럼 값과 같은 값을 가지는 모든 행은 자식 행이 된다. 그럼 위의 문법을 활용한 간단한 사용예제와 실행 순서를 살펴보자.
기본 사용(Top-Down 방식)
<리스트 1> 사용 예제SELECT LPAD(' ' , 3*(LEVEL -1))|| ENAME ENAME, EMPNO, MGR, JOB, LEVELFROM EMPSTART WITH JOB='PRESIDENT'CONNECT BY PRIOR EMPNO= MGR[실행순서]
1) JOB=‘PRESIDENT’ 행으로 계층데이터 시작
2) 해당 행의 EMPNO 검색
3) 검색된 EMPNO와 같은 값을 가지는 MGR을 검색해 하위 계층화
PRIOR 위치 변경(Buttom-Up 방식)
<리스트 2> 사용 예제SELECT LPAD(' ' , 3*(LEVEL -1))|| ENAME ENAME, EMPNO, MGR, JOB, LEVELFROM EMPSTART WITH JOB='PRESIDENT'CONNECT BY EMPNO=PRIOR MGR[실행순서]
1) JOB=‘PRESIDENT’ 행으로 계층데이터 시작
2) 해당 행의 MGR 검색
3) 검색된 MGR과 같은 값을 가지는 EMPNO를 검색해 하위 계층화
WHERE 조건
<리스트 3> 사용 예제SELECT LPAD(' ' , 3*(LEVEL -1))|| ENAME ENAME, EMPNO, MGR, JOB, LEVELFROM EMPWHERE ENAME LIKE '%S%'START WITH JOB='PRESIDENT'CONNECT BY PRIOR EMPNO= MGR[실행순서]
1) JOB=‘PRESIDENT’ 행으로 계층데이터 시작
2) 해당 행의 EMPNO 검색
3) 검색된 EMPNP와 같은 값을 가지는 MGR을 검색해 하위 계층화
4) 계층화가 끝나면 ENAME LIKE ‘%S%’ 조건으로 필터링
위의 WHERE 조건은 계층화가 진행된 후에 필터링되고 있어 중복이나 혼합데이터가 발생할 수 있다. 이러한 경우에는 WHERE 조건절의 데이터를 먼저 추출하고, 추출된 데이터를 계층화할 수 있게 다음과 같이 쿼리를 수정해야 한다.
SELECT LPAD(‘ ’ , 3*(LEVEL -1))|| ENAME ENAME
, EMPNO, MGR, JOB, LEVEL
FROM (SELECT *
FROM EMP
WHERE ENAME LIKE ‘%S%’)
START WITH JOB=’PRESIDENT’
CONNECT BY PRIOR EMPNO= MGR
[실행순서]
1) ENAME LIKE ‘%S%’ 필터링데이터 추출
2) JOB=‘PRESIDENT’ 행으로 계층데이터 시작
2) 해당 행의 EMPNO 검색
3) 검색된 EMPNP와 같은 값을 가지는 MGR을 검색해 하위 계층화
START WITH 절과 CONNECT 절 사이 AND 조건
<리스트 4> 사용 예제SELECT LPAD(' ' , 3*(LEVEL -1))|| ENAME ENAME, EMPNO, MGR, JOB, LEVELFROM EMPSTART WITH JOB='PRESIDENT'AND ENAME LIKE '%K%'CONNECT BY PRIOR EMPNO= MGR[실행순서]
1) JOB=‘PRESIDENT’이고 ENAME LIKE ‘%K%’ 행으로 계층데이터 시작
2) 해당 행의 EMPNO 검색
3) 검색된 EMPNO와 같은 값을 가지는 MGR을 검색해 하위 계층화
CONNECT BY 절 다음 AND 조건
<리스트 5> 사용 예제SELECT LPAD(' ' , 3*(LEVEL -1))|| ENAME ENAME, EMPNO, MGR, JOB, LEVELFROM EMPSTART WITH JOB='PRESIDENT'CONNECT BY PRIOR EMPNO= MGRAND ENAME LIKE '%S%'[실행순서]
1) JOB=‘PRESIDENT’ 행으로 계층데이터 시작
2) 해당 행의 EMPNO 검색
3) 검색된 EMPNO와 같은 값을 가지는 MGR과 ENAME LIKE ‘%S%’인 행을 검색해 하위 계층화
지금까지 계층적 질의에 대한 SQL의 기본문법과 간단한 사용 예를 설명했다. 다음 기회에는 계층적 질의문의 실행계획과 이를 참조해 최적의 성능을 보장 받을 수 있는 방법을 알아본다.
