Oracle 계층형 쿼리 사용하기 (부모 <-> 자식 검색), 성능 확인하기

/**
1. 홍길동과 그의 부서를 검색한다.
2. 그 부서의 부모 부서는 모두 쿼리 결과로 나온다.
*/
WITH EMP_DEPT AS (
    SELECT DEPT_ID, DEPT_NM, EMP_ID, EMP_NM
    FROM EMP
    JOIN DEPT 
    ON DEPT.DEPT_ID = EMP.DEPT_ID
    WHERE EMP_NM LIKE '%홍길동%'
    )
SELECT DEPT_ID, DEPT_NM, EMP_ID, EMP_NM FROM EMP_DEPT
UNION ALL 
SELECT DEPT_ID, DEPT_NM, '' AS EMP_ID, '' AS EMP_NM
FROM DEPT
START WITH DEPT_ID IN (SELECT DISTINCT DEPT_ID FROM EMP_DEPT)  -- 자식 노드에서 시작
CONNECT BY DEPT_ID = PRIOR PARENT_DEPT_ID ;

-- 계층형 쿼리(부모 -> 자식 찾기)
 SELECT LEVEL, SG_CAT_UUID, PAR_SG_CAT_UUID, SG_CAT_NM
   FROM SG_CAT
  START WITH PAR_SG_CAT_UUID = 'ROOT'
CONNECT BY PRIOR SG_CAT_UUID = PAR_SG_CAT_UUID;

-- 역방향(자식 -> 부모를 찾는 방향)
 SELECT LEVEL, SG_CAT_UUID, PAR_SG_CAT_UUID, SG_CAT_NM
   FROM SG_CAT
  START WITH SG_CAT_UUID = #{검색 대상 UUID}  -- 자식 노드에서 시작
CONNECT BY PRIOR PAR_SG_CAT_UUID = SG_CAT_UUID;

 

Oracle start with ... connect by prior 을 사용하면 계층형 쿼리를 아주 쉽게 사용할 수 있다.

 

자식 -> 부모 또는 부모 -> 자식으로 재귀로 root 까지 또는 leaf 까지 찾아갈 수 있어, 계층형 데이터 구조로 쿼리를 뽑아야할 때 아주 유용함.

 

 

자식 검색 결과로 부모까지 가져오기.

요구사항 예시) EMP 를 검색하고 그에 맞는 부서 정보를 가져와서 hierarchy 구조로 보여주려고 한다.

 

 

[ 실행계획 확인하기 ]

SG_CAT_UUID 는  SG_CAT 테이블의 PK 이기에 index 가 걸려있어 성능 상 문제도 없음.

EXPLAIN PLAN FOR
 SELECT LEVEL, SG_CAT_UUID, PAR_SG_CAT_UUID, SG_CAT_NM
   FROM SG_CAT
  START WITH SG_CAT_UUID in ('0d015864-2af6-4828-9610-031e6fec28e4')  -- 자식 노드에서 시작
CONNECT BY PRIOR PAR_SG_CAT_UUID = SG_CAT_UUID;
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- "This is an adaptive plan" 란?
Oracle에서 실행 계획을 확인할 때 "This is an adaptive plan"이라는 메시지가 나타나면, Oracle의 Adaptive Query Optimization(적응형 쿼리 최적화) 기능이 작동하고 있다는 뜻

결과 확인 >>

 

✔️ INDEX RANGE SCAN 이 나타나면 인덱스를 사용하고 있음!
❌ TABLE ACCESS FULL 이 나오면 풀 테이블 스캔 발생 (인덱스가 적용되지 않음).

 

Plan hash value: ${해시값}

 

✅ Plan Hash Value의 역할

1. 동일한 실행 계획을 구분하는 ID
   • 실행 계획이 생성될 때 각 실행 계획마다 해시 값이 부여됨.
   • 동일한 SQL 문이 실행될 때, 실행 계획이 변경되었는지 확인하는 데 사용됨.
2. SQL 성능 모니터링 및 튜닝에 활용
   • 동일한 SQL이 실행될 때 Plan Hash Value가 다르면 실행 계획이 변경되었음을 의미.
   • 예상치 못한 실행 계획 변경이 발생하면, 성능 저하 원인을 분석할 수 있음.
3. AWR(Automatic Workload Repository) 또는 V$SQL_PLAN에서 실행 계획 추적
   • 과거 실행된 SQL의 실행 계획을 확인할 때 유용함.