[Oracle] 계층형 쿼리 Hierarchical Queries

1. 개인적으로 좋아하진 않는다

DBA 입장에서 계층형 쿼리를 선호하지 않는다. 

그 이유는 

1) 데이터가 잘못 될 경우, 순환 참조 (무한 loop) 문제가 발생할 수 있다

2) 힌트 적용이 잘 안 되고, Plan이 의도하지 않는대로 풀릴 수 있다.

3) 최초 개발자가 아닌 경우 비즈니스 로직을 이해하기 어렵다.

등이 있다.

 

그러나 개발자는 개발의 용이성, 데이터 모델의 단순성 등을 이유로 계층형 쿼리를 선호하곤 한다.

 

2. 계층형 쿼리 작성시 숙지할 내용들

계층형 쿼리를 사용할 경우, Oracle의 매뉴얼 

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Hierarchical-Queries.html

을 참고하며

 

아래 내용을 기억하면 좋다.

 

1) CONNECT BY 절 읽는 순서

  ① START WITH

  ② CONNECT BY

  ③ WHERE 조건

 

2) NOCYCLE 추가시 순환참조를 방지한다 (추가적인 데이터가 있어도 계층형으로 더 파고들지 않는다)

    +  CONNECT_BY_ISLEAF 컬럼으로 추가 자식이 있는지 확인할 수 있다

      (참고 url : https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Hierarchical-Query-Pseudocolumns.html )

 

+ 일부 데이터가 순환참조를해 에러가 발생하는 경우,   

  ( ORA-01436 CONNECT BY loop in user data 에러 발생시 )

   NOCYCLE 파라미터 추가로 비교적 단순하게 문제를 해결할 수도 있다. 

 

3. 이미 운영 중인 쿼리를 튜닝할때

아래와 같은 employees 테이블에 인덱스 2건이 있다고 가정해보면, 

employees_ix01 : employee_id

employees_ix02 : manager_id

 

데이터양과 통계정보에 따른 차이는 있겠지만,

table full scan으로 plan이 풀리는 경우 부하를 유발할 수 있으며, 

동일한 테이블을 2번 참조하므로 힌트 작성시에도 애매할 수 있다.

 

이럴때는 

1) /*+ use_nl(employees) index(employees) */  와 같은 형태로

    nested loop join과 index를 사용하라는 힌트 만으로도

    통계정보에 따라 ix01, ix02를 적절히 사용해 use_nl로 풀릴 수가 있다.

 

2) /*+ use_nl(employees) index(employees employees_ix01 employees_ix02) */  또는 이런 형태로 

     ix01, ix02를 우선적으로 고려해 plan을 수립하라는 힌트를 사용할 수 있다.

 

* 데이터 예시 : 

SELECT employee_id, last_name, manager_id
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME                 MANAGER_ID
----------- ------------------------- ----------
        101 Kochhar                          100
        108 Greenberg                        101
        109 Faviet                           108
        110 Chen                             108
        111 Sciarra                          108
        112 Urman                            108
        113 Popp                             108
        200 Whalen                           101
        203 Mavris                           101
        204 Baer                             101
. . .