라비앙블루

1. 들어가기

지난 글에 이어, 이번 글에서는 Oracle 계층형 쿼리에서 활용할 수 있는 연산자와 절(clause)에 대해 알아봅니다.

세부 주제는 다음과 같습니다.

• CONNECT BY 절 실행 순서
• CONNECT_BY_ROOT 연산자
• CONNECT_BY_ISLEAF 연산자
• SYS_CONNECT_BY_PATH 연산자
• ORDER SIBLINGS BY 절

2. 계층형 쿼리의 논리적 실행 순서: JOIN, CONNECT BY, WHERE

우리가 실제로 쓰는 대부분의 쿼리는 WHERE 절을 포함합니다. 그렇다면 CONNECT BY 절이 있는 계층형 쿼리에서는 WHERE 절을 어떻게 사용해야 할까요? Oracle 공식 문서에 따르면, 계층형 쿼리에서 각 절은 다음 순서로 처리됩니다. 

1. 테이블 간 JOIN에 사용되는 WHERE 절
2. CONNECT BY 절
3. 그 외 WHERE 절

(링크: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Hierarchical-Queries.html#GUID-0118DF1D-B9A9-41EB-8556-C6E7D6A5A84E'Oracle processes hierarchical queries as follows:...'로 시작하는 문단 참고)

예시를 확인하기 위해 추가로 DEPT 테이블을 생성하고, EMP 테이블에는 DEPT_CODE 컬럼을 추가합니다.

-- EMP 테이블에 DEPT_CODE 컬럼 추가
ALTER TABLE EMP ADD DEPT_CODE NUMBER;

-- EMP 테이블 각 레코드의 DEPT_CODE 값 업데이트
UPDATE EMP SET DEPT_CODE = 1 WHERE ID = 1;
-- 2번과 그 직간접 부하 직원은 모두 DEPT_CODE = 2 
UPDATE EMP SET DEPT_CODE = 2 WHERE ID = 2; 
UPDATE EMP SET DEPT_CODE = 2 WHERE ID IN (
	SELECT ID FROM EMP WHERE ID != 2 CONNECT BY PRIOR ID = MANAGER_ID START WITH ID = 2 
);
-- 2번과 그 직간접 부하 직원은 모두 DEPT_CODE = 3
UPDATE EMP SET DEPT_CODE = 3 WHERE ID = 3;
UPDATE EMP SET DEPT_CODE = 3 WHERE ID IN (
	SELECT ID FROM EMP WHERE ID != 3 CONNECT BY PRIOR ID = MANAGER_ID START WITH ID = 3 
);

-- DEPT 테이블 생성
CREATE TABLE DEPT (
	DEPT_CODE number,
	name varchar2(100),
	CONSTRAINT PK_DEPT PRIMARY KEY(DEPT_CODE )
);

INSERT INTO DEPT VALUES (1, 'HEAD');
INSERT INTO DEPT VALUES (2, 'Sales');
INSERT INTO DEPT VALUES (3, 'Development');

1번 직원(총책임에 해당하는 'HEAD')과 Sales 부서에 해당하는 직원을 조회하는 계층형 쿼리를 실행해 봅시다.

SELECT
	id, emp.name, manager_id, dept.dept_code, dept.name, LEVEL
FROM emp, dept
WHERE emp.dept_code = dept.dept_code
	AND dept.name IN ('HEAD', 'Sales')
CONNECT BY
	PRIOR id = manager_id
START WITH id = 1;

위 SQL의 실행계획은 다음과 같습니다. (DBeaver에서 조회) EMP와 DEPT 테이블 간 조회를 위한 HASH JOIN이 가장 먼저 실행되고, 그 다음 CONNECT BY 작업이, 마지막으로는 WHERE 절의 FILTER 작업이 실행되고 있습니다다.

위 쿼리 결과는 다음과 같습니다. 

ID|NAME  |MANAGER_ID|DEPT_CODE|NAME |LEVEL|
--+------+----------+---------+-----+-----+
 1|EMP_1 |         0|        1|HEAD |    1|
 2|EMP_2 |         1|        2|Sales|    2|
 4|EMP_4 |         2|        2|Sales|    3|
 8|EMP_8 |         4|        2|Sales|    4|
 9|EMP_9 |         4|        2|Sales|    4|
 5|EMP_5 |         2|        2|Sales|    3|
10|EMP_10|         5|        2|Sales|    4|
11|EMP_11|         5|        2|Sales|    4|

그런데 만약 가장 말단의 직원(LEVEL = 4)은 결과에서 제외하고 싶다면 어떻게 하면 될까요? WHERE 절에 조건을 추가하면 됩니다. CONNECT BY 절이 먼저 평가/실행되고 그 다음 WHERE 절이 적용되기 때문에, 계층형 쿼리 결과로 값이 정해지는 'LEVEL' 컬럼을 가지고도 필터링을 할 수 있습니다.

SELECT
	id, name, manager_id, LEVEL
FROM EMP
WHERE LEVEL <> 4 -- 말단 직원을 결과에서 필터링
	AND dept.name IN ('HEAD', 'Sales')
CONNECT BY manager_id = PRIOR id 
START WITH id in 1;

결과는 아래와 같습니다. LEVEL = 4에 해당하는 말단 직원이 결과에서 제외되었습니다.

ID|NAME |MANAGER_ID|DEPT_CODE|NAME |LEVEL|
--+-----+----------+---------+-----+-----+
 1|EMP_1|         0|        1|HEAD |    1|
 2|EMP_2|         1|        2|Sales|    2|
 4|EMP_4|         2|        2|Sales|    3|
 5|EMP_5|         2|        2|Sales|    3|

CONNECT BY 절과 WHERE 절의 실행순서를 고려하면 불필요한 WHERE 절을 제거할 수도 있습니다. 우선, 위에서 실행했던 쿼리를 다시 한 번 보겠습니다.

SELECT
	id, emp.name, manager_id, dept.dept_code, dept.name, LEVEL
FROM emp, dept
WHERE emp.dept_code = dept.dept_code
	AND dept.name IN ('HEAD', 'Sales')
CONNECT BY
	PRIOR id = manager_id
START WITH id = 1;

위 쿼리는 전 직원의 계층 관계를 CONNECT BY 절에서 먼저 평가하고, 그 전체 결과에 대해 부서 이름을 기준으로 필터링 하고 있습니다. 하지만 애초부터 우리가 궁금한 내용(총책임 및 영업부서 직원들)에 대해서만 계층 관계를 계산하게 하면, 연산의 효율성이 조금은 더 높아지지 않을까요?

SELECT
	id, emp.name, manager_id, dept.dept_code, dept.name, LEVEL
FROM emp, dept
WHERE emp.dept_code = dept.dept_code
CONNECT BY
	PRIOR id = manager_id AND DEPT.NAME IN ('HEAD', 'Sales')
START WITH id = 1;

두 쿼리는 똑같은 결과를 반환합니다. 하지만 실행계획은 서로 다릅니다. 두 번째 쿼리의 경우, WHERE 절에 의한 FILTER 작업이 수행되지 않습니다.

이와 같이 효율적으로 수행되는 계층형 쿼리를 작성하기 위해서는 이와 같은 WHERE 절, CONNECT BY 절의 실행 순서를 참고할 필요가 있습니다.

3. 계층형 쿼리 연산자

3.1. CONNECT_BY_ROOT 연산자

CONNECT_BY_ROOT는 root row의 컬럼을 참조하기 위해 사용되는 연산자입니다.

1번 직원을 루트로 하여 조회하는 계층형 쿼리에서, 각 행에 루트 행의 'name' 컬럼 값을 표시하고자 하면 다음과 같이 SQL문을 작성하면 됩니다.

SELECT
	id,
	name,
	manager_id,
	LEVEL,
	CONNECT_BY_ROOT name AS ROOT_NAME -- 루트 행의 'name' 컬럼 값
FROM EMP
CONNECT BY manager_id = PRIOR id
START WITH id IN (1);

ID|NAME  |MANAGER_ID|LEVEL|ROOT_NAME|
--+------+----------+-----+---------+
 1|EMP_1 |         0|    1|EMP_1    |
 2|EMP_2 |         1|    2|EMP_1    |
 4|EMP_4 |         2|    3|EMP_1    |
 8|EMP_8 |         4|    4|EMP_1    |
 9|EMP_9 |         4|    4|EMP_1    |
 5|EMP_5 |         2|    3|EMP_1    |
10|EMP_10|         5|    4|EMP_1    |
11|EMP_11|         5|    4|EMP_1    |
 3|EMP_3 |         1|    2|EMP_1    |
 6|EMP_6 |         3|    3|EMP_1    |
12|EMP_12|         6|    4|EMP_1    |
13|EMP_13|         6|    4|EMP_1    |
 7|EMP_7 |         3|    3|EMP_1    |
14|EMP_14|         7|    4|EMP_1    |
15|EMP_15|         7|    4|EMP_1    |

CONNECT_BY_ROOT 연산자는 단항 연산자(unary operator)입니다. 따라서 루트 행의 여러 컬럼을 합쳐서 조회하고 싶으면, 이들을 괄호로 묶어서 하나의 식으로 표현해야 합니다.

SELECT
	id,
	name,
	manager_id,
	LEVEL,
	CONNECT_BY_ROOT ('Name: ' || name || ', ID: ' || id ) AS ROOT_NAME -- 루트 행의 'name' 컬럼 값
FROM EMP
CONNECT BY manager_id = PRIOR id
START WITH id IN (1);

ID|NAME  |MANAGER_ID|LEVEL|ROOT_NAME         |
--+------+----------+-----+------------------+
 1|EMP_1 |         0|    1|Name: EMP_1, ID: 1|
 2|EMP_2 |         1|    2|Name: EMP_1, ID: 1|
 4|EMP_4 |         2|    3|Name: EMP_1, ID: 1|
 8|EMP_8 |         4|    4|Name: EMP_1, ID: 1|
 9|EMP_9 |         4|    4|Name: EMP_1, ID: 1|
 5|EMP_5 |         2|    3|Name: EMP_1, ID: 1|
10|EMP_10|         5|    4|Name: EMP_1, ID: 1|
11|EMP_11|         5|    4|Name: EMP_1, ID: 1|
 3|EMP_3 |         1|    2|Name: EMP_1, ID: 1|
 6|EMP_6 |         3|    3|Name: EMP_1, ID: 1|
12|EMP_12|         6|    4|Name: EMP_1, ID: 1|
13|EMP_13|         6|    4|Name: EMP_1, ID: 1|
 7|EMP_7 |         3|    3|Name: EMP_1, ID: 1|
14|EMP_14|         7|    4|Name: EMP_1, ID: 1|
15|EMP_15|         7|    4|Name: EMP_1, ID: 1|

3.2. CONNECT_BY_ISLEAF 연산자

CONNECT_BY_ISLEAF 연산자는 계층형 쿼리의 결과에서 이 행이 계층 관계의 가장 말단, 즉 잎 노드(leaf node)에 해당하는지 여부를 참(1)과 거짓(0)으로 반환하는 연산자입니다.

SELECT
	id, name, manager_id, LEVEL,
	CASE WHEN connect_by_isleaf = 0 THEN 'No' ELSE 'Yes' END AS isleaf
FROM EMP
CONNECT BY manager_id = PRIOR id
START WITH id in 1;

위 쿼리를 조회하면, 다음과 같이 계층 가장 아래의 직원들(LEVEL 4 직원들)에 대해서만 ISLEAF 컬럼 값이 'No'로 출력됩니다.

ID|NAME  |MANAGER_ID|LEVEL|ISLEAF|
--+------+----------+-----+------+
 1|EMP_1 |         0|    1|No    |
 2|EMP_2 |         1|    2|No    |
 4|EMP_4 |         2|    3|No    |
 8|EMP_8 |         4|    4|Yes   |
 9|EMP_9 |         4|    4|Yes   |
 5|EMP_5 |         2|    3|No    |
10|EMP_10|         5|    4|Yes   |
11|EMP_11|         5|    4|Yes   |
 3|EMP_3 |         1|    2|No    |
 6|EMP_6 |         3|    3|No    |
12|EMP_12|         6|    4|Yes   |
13|EMP_13|         6|    4|Yes   |
 7|EMP_7 |         3|    3|No    |
14|EMP_14|         7|    4|Yes   |
15|EMP_15|         7|    4|Yes   |

3.3. SYS_CONNECT_BY_PATH 함수

SYS_CONNECT_BY_PATH 함수는 루트 행에서 현재 행까지 조회되는 값을 이어서 붙여주는 역할을 합니다.

예를 들어 최상위 직원으로부터 각 직원까지 업무가 하달될 때 거치는 직원의 순서를 확인하고 싶다면, 아래와 같이 계층형 쿼리를 작성하면 됩니다. SYS_CONNECT_BY_PATH 함수의 두 번째 인자로 슬래시(/) 문자를 넣어서 각 직원의 이름이 구분되도록 합니다. 

SELECT
	id,
	name,
	manager_id,
	LEVEL,
	SYS_CONNECT_BY_PATH(name, '/') AS DIRECTION_CHAIN
FROM EMP
CONNECT BY PRIOR id = manager_id
START WITH id IN (1);

ID|NAME  |MANAGER_ID|LEVEL|DIRECTION_CHAIN          |
--+------+----------+-----+-------------------------+
 1|EMP_1 |         0|    1|/EMP_1                   |
 2|EMP_2 |         1|    2|/EMP_1/EMP_2             |
 4|EMP_4 |         2|    3|/EMP_1/EMP_2/EMP_4       |
 8|EMP_8 |         4|    4|/EMP_1/EMP_2/EMP_4/EMP_8 |
 9|EMP_9 |         4|    4|/EMP_1/EMP_2/EMP_4/EMP_9 |
 5|EMP_5 |         2|    3|/EMP_1/EMP_2/EMP_5       |
10|EMP_10|         5|    4|/EMP_1/EMP_2/EMP_5/EMP_10|
11|EMP_11|         5|    4|/EMP_1/EMP_2/EMP_5/EMP_11|
 3|EMP_3 |         1|    2|/EMP_1/EMP_3             |
 6|EMP_6 |         3|    3|/EMP_1/EMP_3/EMP_6       |
12|EMP_12|         6|    4|/EMP_1/EMP_3/EMP_6/EMP_12|
13|EMP_13|         6|    4|/EMP_1/EMP_3/EMP_6/EMP_13|
 7|EMP_7 |         3|    3|/EMP_1/EMP_3/EMP_7       |
14|EMP_14|         7|    4|/EMP_1/EMP_3/EMP_7/EMP_14|
15|EMP_15|         7|    4|/EMP_1/EMP_3/EMP_7/EMP_15|

참고로 SYS_CONNECT_BY_PATH의 첫 번째 파라미터로 컬럼 이름 하나만 들어가는 것은 아니고, 하나의 값으로 환원될 수 있는 식을 전달할 수 있습니다. 예를 들면 다음과 같이 작성할 수도 있습니다.

SELECT
	id,
	name,
	manager_id,
	LEVEL,
	SYS_CONNECT_BY_PATH(name || '(' || id || ')', '/') AS DIRECTION_CHAIN
FROM EMP
CONNECT BY PRIOR id = manager_id
START WITH id IN (1);

ID|NAME  |MANAGER_ID|LEVEL|DIRECTION_CHAIN                       |
--+------+----------+-----+--------------------------------------+
 1|EMP_1 |         0|    1|/EMP_1(1)                             |
 2|EMP_2 |         1|    2|/EMP_1(1)/EMP_2(2)                    |
 4|EMP_4 |         2|    3|/EMP_1(1)/EMP_2(2)/EMP_4(4)           |
 8|EMP_8 |         4|    4|/EMP_1(1)/EMP_2(2)/EMP_4(4)/EMP_8(8)  |
 9|EMP_9 |         4|    4|/EMP_1(1)/EMP_2(2)/EMP_4(4)/EMP_9(9)  |
 5|EMP_5 |         2|    3|/EMP_1(1)/EMP_2(2)/EMP_5(5)           |
10|EMP_10|         5|    4|/EMP_1(1)/EMP_2(2)/EMP_5(5)/EMP_10(10)|
11|EMP_11|         5|    4|/EMP_1(1)/EMP_2(2)/EMP_5(5)/EMP_11(11)|
 3|EMP_3 |         1|    2|/EMP_1(1)/EMP_3(3)                    |
 6|EMP_6 |         3|    3|/EMP_1(1)/EMP_3(3)/EMP_6(6)           |
12|EMP_12|         6|    4|/EMP_1(1)/EMP_3(3)/EMP_6(6)/EMP_12(12)|
13|EMP_13|         6|    4|/EMP_1(1)/EMP_3(3)/EMP_6(6)/EMP_13(13)|
 7|EMP_7 |         3|    3|/EMP_1(1)/EMP_3(3)/EMP_7(7)           |
14|EMP_14|         7|    4|/EMP_1(1)/EMP_3(3)/EMP_7(7)/EMP_14(14)|
15|EMP_15|         7|    4|/EMP_1(1)/EMP_3(3)/EMP_7(7)/EMP_15(15)|

4. 계층형 쿼리의 결과 정렬: ORDER SIBLINGS BY

ORDER SIBLINGS BY 절을 살펴보기 전에 미리 알아두어야 하는 점은, 일단 계층형 쿼리에서 반환하는 결과 자체가 부모 행 아래 자식 행이 재귀적으로 나타나는 형식으로, 어느 정도는 정렬이 되어 있다는 것입니다. 

SELECT
	id,
	name,
	manager_id,
	LEVEL
FROM EMP
CONNECT BY PRIOR id = manager_id
START WITH id IN (1);

ID|NAME  |MANAGER_ID|LEVEL|
--+------+----------+-----+
 1|EMP_1 |         0|    1|
 2|EMP_2 |         1|    2|
 4|EMP_4 |         2|    3|
 8|EMP_8 |         4|    4|
 9|EMP_9 |         4|    4|
 5|EMP_5 |         2|    3|
10|EMP_10|         5|    4|
11|EMP_11|         5|    4|
 3|EMP_3 |         1|    2|
 6|EMP_6 |         3|    3|
12|EMP_12|         6|    4|
13|EMP_13|         6|    4|
 7|EMP_7 |         3|    3|
14|EMP_14|         7|    4|
15|EMP_15|         7|    4|

ORDER SIBLINGS BY 절에는 형제 행들, 즉, 부모 행이 같은 서로 다른 행들을 어떻게 정렬할지에 대한 기준이 기술되어야 합니다. 예를 들어, LEVEL이 같은(즉, 부모 행이 같은) 직원에 대해서는 부서명 오름차순으로 표시하고 싶다면 다음과 같이 쿼리를 작성하면 됩니다.

SELECT
	id,
	EMP.name,
	manager_id,
	LEVEL,
	DEPT.NAME
FROM EMP, DEPT
WHERE EMP.DEPT_CODE = DEPT.DEPT_CODE 
CONNECT BY PRIOR id = manager_id
START WITH id IN (1)
ORDER SIBLINGS BY DEPT.NAME ASC;

ID|NAME  |MANAGER_ID|LEVEL|NAME       |
--+------+----------+-----+-----------+
 1|EMP_1 |         0|    1|HEAD       |
 3|EMP_3 |         1|    2|Development| -- 'Sales'보다 'Development'가 사전상 앞서기 때문에 LEVEL이 같은 직원2보다 먼저 나타남.
 6|EMP_6 |         3|    3|Development|
12|EMP_12|         6|    4|Development|
13|EMP_13|         6|    4|Development|
 7|EMP_7 |         3|    3|Development|
14|EMP_14|         7|    4|Development|
15|EMP_15|         7|    4|Development|
 2|EMP_2 |         1|    2|Sales      |
 4|EMP_4 |         2|    3|Sales      |
 8|EMP_8 |         4|    4|Sales      |
 9|EMP_9 |         4|    4|Sales      |
 5|EMP_5 |         2|    3|Sales      |
10|EMP_10|         5|    4|Sales      |
11|EMP_11|         5|    4|Sales      |

결과에서도 확인되지만 ORDER SIBLINGS BY로 정렬해도 부모 행 아래에 자식 행이 나타나는 형식은 유지됩니다.

즉, 어떤 부모 행이 직계 자식 행을 여럿 가지고 있을 경우, 그 중 하나의 직계 자식 행을 골라서 여기 속하는 다른 자식 행을 끝까지 표시하고, 그러고 나서야 다른 직계 자식 행에 대해서 똑같은 작업을 하는 것입니다.

이러한 작업은 프로그래밍으로 치면 깊이우선탐색 같은 것이고, ORDER SIBLINGS BY 절에는 이러한 깊이우선탐색을 실행할 때 어떤 순서로 직계 자식 행을 선택(표시)할지에 대해서 기술해야 하는 것이지요.

만약 각 행의 LEVEL 순서 오름차순으로 정렬하고 싶다면 어떻게 해야 할까요?

그때에는 평범한 ORDER BY 절을 사용하면 됩니다.

SELECT
	id,
	EMP.name,
	manager_id,
	LEVEL,
	DEPT.NAME
FROM EMP, DEPT
WHERE EMP.DEPT_CODE = DEPT.DEPT_CODE 
CONNECT BY PRIOR id = manager_id
START WITH id IN (1)
ORDER BY LEVEL, id;

ID|NAME  |MANAGER_ID|LEVEL|NAME       |
--+------+----------+-----+-----------+
 1|EMP_1 |         0|    1|HEAD       |
 2|EMP_2 |         1|    2|Sales      |
 3|EMP_3 |         1|    2|Development|
 4|EMP_4 |         2|    3|Sales      |
 5|EMP_5 |         2|    3|Sales      |
 6|EMP_6 |         3|    3|Development|
 7|EMP_7 |         3|    3|Development|
 8|EMP_8 |         4|    4|Sales      |
 9|EMP_9 |         4|    4|Sales      |
10|EMP_10|         5|    4|Sales      |
11|EMP_11|         5|    4|Sales      |
12|EMP_12|         6|    4|Development|
13|EMP_13|         6|    4|Development|
14|EMP_14|         7|    4|Development|
15|EMP_15|         7|    4|Development|

5. 나가기

이상 계층형 쿼리의 논리적 실행 순서, 활용 가능한 연산자, 정렬 방법에 대해서 알아 보았습니다.

다음 글에서는 계층형 쿼리에서의 순환(사이클) 탐지/처리 방법에 대해서 알아 보겠습니다. 계층형 데이터는 정의상 순환 관계를 갖지 않아야 하므로, 사이클 처리 역시 계층형 쿼리를 다룰 때 주요하게 고려해야 하는 부분입니다.


오류에 대한 지적이나 질문은 댓글로 남겨 주세요. 감사합니다.

1. 들어가기: 계층형 구조(hierarchical structure)란?

어떤 데이터의 집합이 계층구조를 가진다는 건 무슨 뜻일까요? 각 데이터의 높고 낮음, 또는 앞서고 뒤따름을 구분하는 기준이 하나 이상 존재하며, 이를 바탕으로 데이터를 조직화 할 수 있다는 뜻일 겁니다.

이러한 계층형 구조는 인간의 일상 생활에서 쉽게 찾을 수 있습니다. 조직의 지시/보고 체계, 회사 간 지배구조 등, 그래서 흔히 계층구조를 '피라미드형 구조'라고 부르기도 합니다. 가장 높은 등급의 사람이 하나 있고, 명령은 위에서 아래로만 흐르는 구조 말입니다.

프로그래밍 자료구조 중에서는 '트리'가 대표적인 계층형 구조입니다. 트리는 정의상 부모노드와 자식노드가 일대다 관계입니다. 모든 자식노드는 직/간접적으로 하나의 부모노드에 연결되어 있고, 이 부모노드를 통해서만 접근 가능한 구조이지요.

그렇다면 '계층형 구조는 트리'라고 말해도 될까요?

저는 이 명제가 대부분의 상황과 데이터에 맞는 말이지만, 이와 다른 상황도 현실에 얼마든지 있다고 봅니다. 회사를 예로 들어 보면, 한 명의 직원이 하나의 업무에 대해 두 명 이상의 임원에게 보고해야 하는 경우가 있습니다. 물론, 이는 바람직한 업무 운영 방식이 아니기는 합니다만, 이것 또한 계층형 구조로 볼 수 있습니다.  또 다른 예로, 데이터를 분석할 때에도 어떤 데이터에 시간적 또는 논리적으로 선행하는 데이터가 둘 이상인 경우도 얼마든지 있을 것입니다.

하지만 이것은 확실하게 말할 수 있습니다. '계층형 구조에는 사이클이 없다', 또는 '사이클이 있으면 계층형 구조가 아니다'라고 말입니다. 극단적인 예로 과장이 대리에게, 대리가 주임에게 업무를 지시했는데, 이 주임이 사실은 회장님 아들이어서 다시 과장에게 업무를 암묵적으로 지시하는 상황을 생각해 봅시다. 이건 한마디로 위아래가 없는 상황, 계층을 구분할 수 없는 상태입니다.

즉 최소한, 전체 데이터 집합을 구성하는 데이터들의 높고 낮음이 한 방향으로 결정될 수 있어야 그 데이터는 계층형 구조를 가진다고 말할 수 있습니다. 그런 의미에서 계층형 구조를 가져야 하는 데이터에 사이클이 있지는 않은지 확인하는 것 역시 계층형 데이터를 다룰 때 중요한 문제 중 하나가 될 것입니다.

2. Oracle 계층형 쿼리 작성 실습

Oracle에서는 계층형 구조 데이터를 갖는 테이블을 다루기 위한 여러 가지 도구를 제공합니다.

이어지는 내용에서는 이 중 가장 기본적인 것들을 사용하는 방법을 다룹니다.

• CONNECT BY 절
• START WITH 절
• PRIOR 키워드

2.1. 테이블 CREATE

CREATE TABLE EMP (
	id NUMBER,
	name VARCHAR2(100),
	manager_id NUMBER,
	CONSTRAINT PK_EMP PRIMARY KEY(id)
);

2.2. 테이블 데이터 INSERT

  직원 17명에 해당하는 데이터를 EMP 테이블에 INSERT 합니다.

INSERT ALL
  INTO	EMP (ID,NAME,MANAGER_ID) VALUES (1,'EMP_1',0)
  INTO	EMP (ID,NAME,MANAGER_ID) VALUES (2,'EMP_2',1)
  INTO	EMP (ID,NAME,MANAGER_ID) VALUES (3,'EMP_3',1)
  INTO	EMP (ID,NAME,MANAGER_ID) VALUES (4,'EMP_4',2)
  INTO	EMP (ID,NAME,MANAGER_ID) VALUES (5,'EMP_5',2)
  INTO	EMP (ID,NAME,MANAGER_ID) VALUES (6,'EMP_6',3)
  INTO	EMP (ID,NAME,MANAGER_ID) VALUES (7,'EMP_7',3)
  INTO	EMP (ID,NAME,MANAGER_ID) VALUES (8,'EMP_8',4)
  INTO	EMP (ID,NAME,MANAGER_ID) VALUES (9,'EMP_9',4)
  INTO	EMP (ID,NAME,MANAGER_ID) VALUES (10,'EMP_10',5)
  INTO	EMP (ID,NAME,MANAGER_ID) VALUES (11,'EMP_11',5)
  INTO	EMP (ID,NAME,MANAGER_ID) VALUES (12,'EMP_12',6)
  INTO	EMP (ID,NAME,MANAGER_ID) VALUES (13,'EMP_13',6)
  INTO	EMP (ID,NAME,MANAGER_ID) VALUES (14,'EMP_14',7)
  INTO	EMP (ID,NAME,MANAGER_ID) VALUES (15,'EMP_15',7)
  INTO	EMP (ID,NAME,MANAGER_ID) VALUES (16,'EMP_16',8)
  INTO	EMP (ID,NAME,MANAGER_ID) VALUES (17,'EMP_17',8)
SELECT * FROM DUAL;

위 데이터가 가지고 있는 계층형 구조를 그림으로 표현하면 아래와 같습니다. (화살표를 쏘는 쪽: 매니저, 받는 쪽: 일반 직원)  

2.3. 실습 목표

  우리의 목표는 각 직원이 보고 체계에서 몇 번째 등급인지를 보여주는 데이터를 출력하는 것입니다. 가장 상위 직급의 직원일수록 등급(LEVEL) 값이 작고(1등급), 하위 직급의의 직원일수록 값이 큽니다(4등급).

점진적 학습을 위해 의도적으로 틀린 SQL을 중간에 집어 넣었습니다.

2.4. CONNECT BY 절

  CONNECT BY 절에는 부모 행과 자식 행이 연결되는 조건을 기술합니다. 예를 들어 다음과 같이 SQL을 작성해 봅시다.

```SQL
SELECT id, name, manager_id, LEVEL
FROM EMP
CONNECT BY manager_id = id;
```

ID|NAME  |MANAGER_ID|LEVEL|
--+------+----------+-----+
 5|EMP_5 |         2|    1|
 8|EMP_8 |         4|    1|
 6|EMP_6 |         3|    1|
 2|EMP_2 |         1|    1|
 7|EMP_7 |         3|    1|
 3|EMP_3 |         1|    1|
 4|EMP_4 |         2|    1|
15|EMP_15|         7|    1|
 1|EMP_1 |         0|    1|
11|EMP_11|         5|    1|
13|EMP_13|         6|    1|
12|EMP_12|         6|    1|
10|EMP_10|         5|    1|
14|EMP_14|         7|    1|
 9|EMP_9 |         4|    1|

이 쿼리는 우리가 목표한 결과를 반환하지 않습니다. LEVEL 컬럼의 값이 모두 1인데, 이는 모든 행이 루트라는 뜻입니다. 왜 이런 일이 발생했을까요? 그 이유는 어떤 행이 루트가 되어야 하는지에 대한 정보가 쿼리에 기술되지 않았기 때문입니다. 즉, 모든 행이 루트로 취급된 것이지요.

2.5. START WITH 절

  어떤 행이 루트가 되어야 하는지에 대한 정보는 START WITH 절에 기술합니다. 현재 연습용 데이터에서 루트 행의 `id` 값은 '1'이라고 정했습니다. 따라서, 위 SQL을 아래와 같이 수정해 보겠습니다.

SELECT id, name, manager_id, LEVEL
FROM EMP
CONNECT BY manager_id = id
START WITH id = 1;

ID|NAME |MANAGER_ID|LEVEL|
--+-----+----------+-----+
 1|EMP_1|         0|    1|

이 쿼리 역시 우리가 목표한 결과를 반환하지 않습니다. 달랑 루트 행 하나만 반환되었습니다. 왜 이런 일이 생겼을까요? 그 이유는 부모 행의 어떤 컬럼을 기준으로 자식 행을 연결할지에 대한 내용이 CONNECT BY 절에 기술되지 않았기 때문입니다.

2.6. PRIOR 키워드
  CONNECT BY 절에서 어떤 컬럼이 **부모** 행의 컬럼임을 나타내기 위해서는 *PRIOR* 키워드를 앞에 붙여서 수식해 주어야 합니다. 즉, 아래와 같이 수정할 수 있습니다.

SELECT
	id, name, manager_id, LEVEL
FROM EMP
CONNECT BY manager_id = PRIOR id
START WITH id = 1;

위 쿼리는 부모 행의 `id` 컬럼과 같은 값을 `manager_id` 컬럼 값으로 가지고 있는 자식 행을 연결하라고 지시합니다.

이렇게 하면, 이 쿼리는 드디어 우리가 원하는 결과를 반환합니다. 

ID|NAME  |MANAGER_ID|LEVEL|
--+------+----------+-----+
 1|EMP_1 |         0|    1|
 2|EMP_2 |         1|    2|
 4|EMP_4 |         2|    3|
 8|EMP_8 |         4|    4|
 9|EMP_9 |         4|    4|
 5|EMP_5 |         2|    3|
10|EMP_10|         5|    4|
11|EMP_11|         5|    4|
 3|EMP_3 |         1|    2|
 6|EMP_6 |         3|    3|
12|EMP_12|         6|    4|
13|EMP_13|         6|    4|
 7|EMP_7 |         3|    3|
14|EMP_14|         7|    4|
15|EMP_15|         7|    4|

`LEVEL` 의사컬럼(pseudo-column)을 기준으로 ORDER BY 하면 아래와 같습니다.

ID |NAME   |MANAGER_ID|LEVEL|
---+-------+----------+-----+
  1|EMP_1  |         0|    1|
  2|EMP_2  |         1|    2|
  3|EMP_3  |         1|    2|
  4|EMP_4  |         2|    3|
  5|EMP_5  |         2|    3|
  6|EMP_6  |         3|    3|
  7|EMP_7  |         3|    3|
  8|EMP_8  |         4|    4|
  9|EMP_9  |         4|    4|
 10|EMP_10 |         5|    4|
 11|EMP_11 |         5|    4|
 12|EMP_12 |         6|    4|
 13|EMP_13 |         6|    4|
 14|EMP_14 |         7|    4|
 15|EMP_15 |         7|    4|

2.7. 루트 조건(시작 조건)이 명확하지 않으면 어떤 결과가 나올까?

만약 바로 위 SQL에서 START WITH 절을 빼면 어떻게 될까요? 이 경우 모든 행이 루트로 간주될 수 있고, 이에 따라 가능한 계층형 구조의 경우의 수가 모두 나옵니다. 전체 데이터를 하나의 트리로 본다면, 이 트리 안에 포함되는 모든 하위트리(subtree) 구조를 조회하는 것과 마찬가지이지요.

SELECT
	id, name, manager_id, LEVEL
FROM EMP
CONNECT BY manager_id = PRIOR id;

직원4(EMP_4)를 예로 들겠습니다. 직원4는 직원1을 루트로 하는 계층구조에서는 LEVEL 3에 위치하지만, 직원2를 루트로 하는 계층 구조에서는 LEVEL2에 위치합니다. 그리고 자기 자신을 루트로 하는 경우에는 LEVEL1에 위치하게 됩니다. 그림으로 표현하면 다음과 같습니다.

단, 루트가 언제나 하나여만 하는 것은 아닙니다. START WITH 절에 조건을 어떻게 기술하는지에 따라 루트가 되는 행을 여러 개 지정할 수도 있지요. 예를 들어, 다음 쿼리는 총 몇 개 행을 반환할까요? 참고로, 아래 쿼리에 포함된 CONNECT_BY_ROOT는 계층의 루트가 되는 행의 데이터를 조회하기 위해 사용하는 연산자입니다.

SELECT id, name, manager_id, LEVEL, CONNECT_BY_ROOT(ID) AS rootid
FROM EMP
CONNECT BY manager_id = PRIOR id
START WITH id IN (1, 2)
ORDER BY rootid, level;

위 쿼리는 직원1(1번 노드)로 시작하는 계층 구조와 직원2(2번 노드)로 시작하는 계층 구조를 모두 조회합니다. 아래는 위 쿼리를 실행한 결과입니다. 실습 시작 부분에 첨부된 트리 그림을 보시고 아래 결과행이 트리의 어떤 부분에 대응하는 것인지 관찰해 보시면, CONNECT BY ~ START WITH 절 의미를 이해하시는데 도움이 되리라 생각합니다.

ID|NAME  |MANAGER_ID|LEVEL|ROOTID|
--+------+----------+-----+------+
 1|EMP_1 |         0|    1|     1| --- 1번 노드로 시작하는 트리
 2|EMP_2 |         1|    2|     1|
 3|EMP_3 |         1|    2|     1|
 7|EMP_7 |         3|    3|     1|
 6|EMP_6 |         3|    3|     1|
 5|EMP_5 |         2|    3|     1|
 4|EMP_4 |         2|    3|     1|
13|EMP_13|         6|    4|     1|
11|EMP_11|         5|    4|     1|
10|EMP_10|         5|    4|     1|
 9|EMP_9 |         4|    4|     1|
 8|EMP_8 |         4|    4|     1|
12|EMP_12|         6|    4|     1|
15|EMP_15|         7|    4|     1|
14|EMP_14|         7|    4|     1|
 2|EMP_2 |         1|    1|     2| -- 2번 노드로 시작하는 트리
 4|EMP_4 |         2|    2|     2|
 5|EMP_5 |         2|    2|     2|
 8|EMP_8 |         4|    3|     2|
 9|EMP_9 |         4|    3|     2|
10|EMP_10|         5|    3|     2|
11|EMP_11|         5|    3|     2|

2.8. 실습 목표 정답

결론적으로, 우리가 작성하려고 했던 쿼리(= 각 직원이 보고 체계에서 몇 번째 등급인지를 보여주는 데이터를 출력하는 쿼리)는 다음과 같이 작성하시면 됩니다.

SELECT
  id,
  name,
  manager_id,
  LEVEL
FROM EMP
CONNECT BY manager_id = PRIOR id
START WITH id = 1;

3. 나가기: 계층형 데이터 다루기 - 세부사항

데이터의 계층 관계를 조회하는 것이 끝은 아닙니다. 데이터를 골랐으면 필터링도 해야 하고, 정렬도 해야 하고, 어떤 데이터가 최상위 데이터를 갖지 않는지, 어떤 데이터에서는 더 이상 계층이 이어지지 않는지 등 확인하고 싶을 수도 있고, 계층형 데이터를 가지고도 일반적인 쿼리 관련 작업을 수행할 수 있어야 합니다. 이에 대해서는, 후속 포스트에서 다루겠습니다.


오류에 대한 지적이나 질문은 댓글로 남겨 주세요. 감사합니다. :) 

1. 이 글의 주제

Java의 Process 클래스로 실행한 서브프로세스가 hang 상태에 빠졌던 현상을 해결했던 경험을 나눕니다.

2. 결론 (TL;DR)

  2-1) 문제 현상 발생 과정

• 서브프로세스가 순서대로 작업을 처리하면서 표준 출력의 버퍼에 쌓은 데이터가 처리되지 않아서 버퍼가 가득 찼습니다.
• 이 상태에서 서브프로세스가 추가로 출력을 시도하니 버퍼에 빈 공간이 생길 때까지 기다리는 상태가 되었고, 해당 시점부터 프로그램이 사실상 실행을 멈추게 되었습니다.
• 이러한 문제는 서브프로세스를 Java의 'Process' 클래스로 생성 및 실행했을 때만 발생하였고, 부모 프로세스가 셸스크립트이거나 C로 작성한 프로그램일 때에는 발생하지 않았습니다.

  2-2) 해결 방법

• 서브프로세스의 표준 출력을 리디렉션 시켜서 버퍼에 내용이 쌓이지 않도록 하였습니다.
• ProcessBuilder의 redirectOutput 메서드를 사용할 수 있습니다.
• 또는, 서브프로세스의 표준출력(즉, 부모 프로세스 입장에서는 입력 스트림)을 받아다가 읽어서 처리해도 됩니다.

3. 배경

  3-1). Java 프로그램 (부모 프로세스)

• 'subproc'이라는 프로세스를 리스트 형식의 데이터를 파라미터로 주면서 실행하는 프로그램입니다.
• 프로세스 생성에는 Java API 'ProcessBuilder'를 사용합니다.
• 코드는 문제 현상의 배경 설명을 위해 단순하게 재구성되었습니다.

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;

public class ParentProc {

    public static void main(String[] args) throws IOException, InterruptedException
    {
        ProcessBuilder processBuilder = new ProcessBuilder("./subproc", "'1,2,3,4,5,6,7,8,9,10'");

        Process process = processBuilder.start();

        int exitCode = process.waitFor();
        System.out.println("Subprocess exited with code: " + exitCode);
    }
}

3-2) C 프로그램 (서브프로세스)

• 리스트 형식의 데이터를 파라미터로 받아서 각 요소에 대한 통신 작업을 처리하는 프로그램입니다.
• 코드는 문제 현상의 배경 설명을 위해 단순하게 재구성되었습니다.

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <unistd.h>

int main(int argc, char *argv[])
{
    char *data = argv[1];
    char data_copy[512] = { 0, };
    char buffer[1024 * 10 + 1] = { 0, };
    const char *DELIMITER = ",";
    char *token = NULL;

    snprintf(data_copy, 1024 * 10, "%s", data);
    memset(buffer, 'a', 1024 * 10);

    token = strtok(data, DELIMITER);
    while (token != NULL)
    {
        printf("  %s\n", token);
        token = strtok(NULL, DELIMITER);
        printf("%s", buffer); // 각 요소에 대해 작업을 처리하면서 printf 로그를 출력하는 부분
        sleep(5);
    }

    return 0;
}

4. 발생한 문제 현상

• 서브프로세스가 인자로 받은 리스트 데이터의 일부에 대해서만 작업을 처리하고 나서, 특정 시점부터 작업을 더 이상 진행하지 못하고 멈추는 현상이 발견되었습니다.
• 예를 들어 1번부터 10번 사용자에 대한 작업을 순서대로 처리해야 하는데, 4번 사용자까지만 처리되고 이후 사용자에 대한 데이터는 전혀 처리하지 못 하는 현상이 발생한 것입니다.
• 강제로 종료시키거나(kill), 실행 시 타임아웃을 설정하지 않는 이상 프로세스가 종료되지 않는 hang 상태에 빠졌습니다.

5. 문제 해결 과정

  5-1) 해결 단서: 프로그램 콜스택

• gdb(GNU Debugger)로 서브프로세스의 콜스택을 조회해 보니, printf 함수 호출에서 멈추어 있는 것이 확인됩니다.
• 참고로, 아래 gdb 로그는 현상 재연을 위해 Ubuntu 컨테이너에서 실행한 결과입니다.

프로그램이 printf 에서 멈출만한 이유를 검색해 보니, '출력 스트림의 버퍼가 꽉 차지는 않았는지' 확인해 보라는 조언을 찾을 수 있었습니다. '출력 스트림도 Linux 환경에서 파일처럼 접근할 수 있을 것 같다'는 추정을 바탕으로, 스트림의 버퍼에 존재하는 데이터의 양(바이트 개수)을 조회하는 프로그램을 작성하였습니다.

  5-2) 디버깅 프로그램

• PID, FD(file descriptor)를 파라미터로 받아서, 해당 파일의 입력 버퍼에 있는 데이터 길이(바이트 개수)를 출력하는 프로그램입니다.
• 입력 버퍼에 쌓인 데이터 크기를 조회하기 위해서 리눅스 ioctl 시스템 함수와 FIONREAD 옵션을 사용합니다.
• ioctl은 디바이스 입출력 제어 및 정보 취득을 위해 사용되는 시스템 함수입니다. 
• 표준출력의 file descriptor 값은 1이므로, 다음과 같이 실행합니다. -> ./debugproc {서브프로세스 PID} 1

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>
#include <fcntl.h>
#include <sys/ioctl.h>

int main(int argc, char *argv[])
{
    pid_t ps_pid = atoi(argv[1]);
    int ps_fd = atoi(argv[2]);
    int fd;
    int readable_bytes = -1;
    int pipe_size = 0;

    char proc_fd_path[64];
    snprintf(proc_fd_path, sizeof(proc_fd_path), "/proc/%d/fd/%d", ps_pid, ps_fd);

    fd = open(proc_fd_path, O_RDONLY | O_NONBLOCK);
    if (fd < 0)
    {
        perror("open proc fd failed");
        return 1;
    }

    if (ioctl(fd, FIONREAD, &readable_bytes) < 0)
    {
        perror("ioctl FIONREAD failed");
    }
    else
    {
        printf("Bytes available to read: %d\n", readable_bytes);
    }

    return 0;
}

디버깅 프로그램으로 서브프로세스의 버퍼 상태를 확인해 보니, 할당된 크기인 65536 바이트를 모두 채운 뒤 그 값이 줄어들지 않는 것을 확인할 수 있었습니다. 시간이 아무리 많이 지나도 수동으로 종료하지 않는 이상 동일한 상태를 유지하였습니다.

6. 해결책

문제 현상은 Java 프로그램이 ProcessBuilder로 서브프로세스의 출력을 제대로 처리하지 않아서 발생한 것이었으며, 이에 따라서 취한 해결책은 매우 단순했습니다: 서브프로세스의 stdout을 리디렉션 시켜 출력이 폐기되도록 하면 끝이었어요.

실제 문제가 발생한 서브프로그램은 Java 프로그램이 셸스크립트를 실행 -> 셸스크립트가 특정 프로그램을 실행하는 구조였기 때문에, 셸스크립트에서 프로그램을 실행하는 명령어에 stdout, stderr를 모두 /dev/null 로 리디렉션 처리하였습니다.

Java 프로그램 수준에서의 리디렉션 처리는 저도 이 글을 작성하면서 알게 되었는데, 다음과 같은 방법으로 가능합니다.

•  ProcessBuilder 클래스의 redirectOutput 메서드를 사용하여 리디렉션 처리
  • 인자로 ProcessBuilder.Redirect.INHERIT을 주면 프로세스가 Java 프로세스의 Output device를 상속합니다.
  • Java 9 버전부터는 ProcessBuilder.Redirect.DISCARD 옵션이 존재하고, 이것으로 설정하면 서브프로세스의 출력 데이터는 폐기됩니다.
  • 필요한 경우, Output 외에도 Error 스트림에 대한 처리도 함께 해주어야 할 것입니다. 
  • ProcessBuilder 클래스: https://docs.oracle.com/javase/8/docs/api/java/lang/ProcessBuilder.html
  • Redirect 옵션: https://docs.oracle.com/javase/9/docs/api/java/lang/ProcessBuilder.Redirect.html

public static void main(String[] args) throws IOException, InterruptedException
{
    ProcessBuilder processBuilder = new ProcessBuilder("./subproc", "'1,2,3,4,5,6,7,8,9,10'");

    processBuilder.redirectOutput(ProcessBuilder.Redirect.INHERIT);
    Process process = processBuilder.start();

    int exitCode = process.waitFor();
    System.out.println("Subprocess exited with code: " + exitCode);
}

• 서브프로세스의 출력(부모프로세스 입장에서는 InputStream)을 받아다가 Java 프로그램 단에서 읽어서 처리하는 방법

public static void main(String[] args) throws IOException, InterruptedException
{
    ProcessBuilder processBuilder = new ProcessBuilder("./subproc", "'1,2,3,4,5,6,7,8,9,10'");
    Process process = processBuilder.start();

    BufferedReader reader = new BufferedReader(new InputStreamReader(process.getInputStream()));
    String line;
    while ((line = reader.readLine()) != null)
    {
        System.out.println(line);
    }

    int exitCode = process.waitFor();
    System.out.println("Subprocess exited with code: " + exitCode);
}

7. 시사점

필요에 따라 Java 프로그램에서 프로그래머의 통제 범위 밖에 있는 프로그램이나 스크립트를 실행해야 하는 경우가 있습니다. 해당 프로그램이 어떤 식으로 출력을 처리하고 있는지가 호출자에게 알려지지 않는 경우가 있는 것입니다.

그런데 Java의 ProcessBuilder는 서브프로세스의 출력/에러 스트림에 대한 기본(디폴트) 처리 옵션을 따로 지정하지 않는 것으로 보입니다. 참고로 C로 작성한 프로그램이나 셸스크립트로 subproc을 호출할 경우, 별도의 설정 없이도 서브프로세스의 출력이 부모프로세스로 전달되는 것을 확인할 수 있었습니다.

Java ProcessBuilder 사용 시 이러한 특징을 고려하여, 외부 프로그램을 호출할 때에는 그 프로그램의 출력 스트림, 에러 스트림을 프로그래머가 직접 주의하여 처리할 필요가 있을 것 같습니다.

제 글에는 언제나 오류가 있을 수 있습니다.

오류나 문의 사항은 댓글로 부탁 드립니다.