[ORACLE] 계층 쿼리 (hierarchical query)

계층 쿼리란 무엇일까? 주로 트리 구조 데이터를 쿼리하는데 사용되며, 재귀쿼리(자기 자신을 반복적으로 참조, 부모-자식 참조)의 일종으로 계층적 데이터를 처리하는 쿼리이다. 

 

oracle 에서는 사용되는 계층 쿼리의 키워드는 어떤 것들이 있을까?

  CONNECT BY PRIOR: 계층 관계를 정의
  START WITH: 계층 쿼리의 루트 노드를 지정
  LEVEL: 현재 행의 계층 레벨을 반환
  SYS_CONNECT_BY_PATH: 경로를 반환

CONNECT_BY_ISLEAF : 현재 행이 트리에서 리프(마지막 노드)인지 여부, 1또는 0의 값을 갖고 1은 리프노드, 0은 리프노드가 아님을 뜻한다.

 

예제를 생성해보자 !!!

 

테이블 생성

 CREATE TABLE "TEST"."COMMENTS" 
   (	"COMMENT_SEQ" NUMBER(*,0), 
	"LVL" NUMBER(*,0) DEFAULT 0, 
	"CONTENT" VARCHAR2(50 BYTE), 
	"PARENT_COMMENT_SEQ" NUMBER(*,0)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Index COMMENTS_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "TEST"."COMMENTS_PK" ON "TEST"."COMMENTS" ("COMMENT_SEQ") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  Constraints for Table COMMENTS
--------------------------------------------------------

  ALTER TABLE "TEST"."COMMENTS" MODIFY ("COMMENT_SEQ" NOT NULL ENABLE);
  ALTER TABLE "TEST"."COMMENTS" MODIFY ("LVL" NOT NULL ENABLE);
  ALTER TABLE "TEST"."COMMENTS" ADD CONSTRAINT "COMMENTS_PK" PRIMARY KEY ("COMMENT_SEQ")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  TABLESPACE "USERS"  ENABLE;

 

데이터 생성

INSERT INTO COMMENTS (COMMENT_SEQ, LVL, CONTENT, PARENT_COMMENT_SEQ) VALUES (1, 0, '1번 댓글', NULL);
INSERT INTO COMMENTS (COMMENT_SEQ, LVL, CONTENT, PARENT_COMMENT_SEQ) VALUES (2, 0, '2번 댓글', NULL);
INSERT INTO COMMENTS (COMMENT_SEQ, LVL, CONTENT, PARENT_COMMENT_SEQ) VALUES (3, 1, '1번 대댓글', 1);
INSERT INTO COMMENTS (COMMENT_SEQ, LVL, CONTENT, PARENT_COMMENT_SEQ) VALUES (4, 2, '1번 대대댓글', 3);

 

계층 쿼리 구현(부모-자식)

   LEVEL을 이용해서 생성된 컬럼 LVL과 비교

SELECT comment_seq, content, parent_comment_seq, lvl, LEVEL
FROM COMMENTS
START WITH parent_comment_seq IS NULL
CONNECT BY PRIOR comment_seq = parent_comment_seq;

 

 

계층 경로 반환

SELECT comment_seq, content, parent_comment_seq, lvl, LEVEL,  
       SYS_CONNECT_BY_PATH(content, ' -> ') AS path
FROM COMMENTS
START WITH parent_comment_seq IS NULL
CONNECT BY PRIOR comment_seq = parent_comment_seq;

 

여러 쿼리

-- 댓글 전체 경로 조회
SELECT comment_seq, content, SYS_CONNECT_BY_PATH(content, ' -> ') AS path
FROM COMMENTS
START WITH parent_comment_seq IS NULL
CONNECT BY PRIOR comment_seq = parent_comment_seq;

-- 특정 레벨까지 댓글 조회
SELECT comment_seq, content, LEVEL
FROM COMMENTS
START WITH parent_comment_seq IS NULL
CONNECT BY PRIOR comment_seq = parent_comment_seq
AND LEVEL <= 3;

-- 리프 노드(말단 댓글)찾기
SELECT comment_seq, content, CONNECT_BY_ISLEAF as is_leaf
FROM COMMENTS
START WITH parent_comment_seq IS NULL
CONNECT BY PRIOR comment_seq = parent_comment_seq;