Oracle – 계층적 쿼리 (level)

Connect by 계층적 쿼리는 오라클만이 가진 기능 중 하나로, 데이터를 선택하여 계층적인 순서 그대로 리턴하는데 사용된다.

예를 들면,  아래와 같이 직원 테이블이 있다고 생각 하자.

 기본적인 SQl을 사용하여 계층 관계를 표현하는것은 불가능하다. 하지만 재귀 PL/SQL 루틴과 connect by 를 사용한다면 표현이 가능하다.

재귀 PL/SQL은개발과 처리 과정에서 다소 많은 시간이 필요로 한다는 단점이 있으며, 변경사항이 있을 때 다른 저장 프로시저를 만들거나 보다 복잡하게 변경해야 한다는 점도 무시 할 수 없다.

이에 오라클에서는 connect by라는 확장된 select 구문을 지원한다.

select lpad(' ',(level-1)*2,' ')||직원 직원, 직급

  from 직원
start with 직원 = '개똥'
connect by 직속상사 = prior 직원

결과

* start with

select 구문의 start with 절은 계층 구조가 어떤 행에서 시작하는지 지정하는 기능을 한다.

 정의 : start with <조건>

where 절의 내용으로 쓸 수 있는 조건이라면 start with로도 사용이 가능하며, 하나 이상의 조건을 결함하는 것도 가능하다.

 ex) start with 직원='개똥' and 직원='순희'

start with 적의 조건에 맞는 행은 결과셋의 루트 노드가 된다. 주의할점은 조건에 맞는 행이 한 번 이상 등장할 경우이다.

예를 들면 start with 직원 ='개똥'and 직원 ='순희' 사용하면 개똥 이 순희 하위에 있기 때문에 순희 트리가 두 번 만들어지게 된다.

(한번은 개똥의 하위에서, 그리고 한 번은 루트로서) 

select lpad(' ',(level-1)*2,' ')||직원 직원, 직급

  from 직원
start with 직원 = '개똥' or 직원 ='순희'
connect by 직속상사 = prior 직원    직원      직급

결과

 같은 결과셋이 여러 번 만들어지는 것을 방지하기 위해서는 이러한 조건을 사용해서는 안 된다.

 

처음 쿼리의 예제에서 직원 ='개똥'이라는 조건을 사용했으며, 이는 회사의 가장 높은 사람을 의미하는 것으로 전체 직원에 대한 목록이 만들어 진다. 하지만 이러한 방법은 그다지 좋지 않다. 왜냐하면, 개똥이 테이블에서 빠져나간다면 새로운 쿼리를 작성하여 직속상사가 의 값이 NULL 인 직원으로 부터 루트 노드가 다시 시작되도록 해야할 것이다.

 

그러므로, 가능하면 보다 구체적인, 즉 결과셋의 양이 적은 조건을 사용하는 것이 바람직하다. 직원 테이블을 보면 개똥의 직속상사의 값이 NULL로 저장되어 있는데, 이는 개똥이라는 직원이 보고할 사람이 없음을, 즉 가장 최상의 간부임을 의미한다.

select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
  from 직원
start with 직속상사 is null

connect by 직속상사 = prior 직원

결과

* connect by prior

connect by 절은 각 행이 어떻게 연결되는지를 오라클에게 알려주는 역할을 한다. 

즉 계층 구조 내에서 각 행의 관계를 설정하는 것이다.

현재 행과 다른 행은 Prior라는 키워드를 통해 구별된다. Prior는 상위 행을 참조하는 것으로, 우리의 예제에서는 다음과 같이 사용되었다.

connect by 직속상사 = prior 직원

 이는 "방금 전 행의 직원 값이 현재 행의 직속상사 값인 모든 행을 찾아라"라는 의미이다.

쉽게 말하면, 방금전에 살펴본 직원이 현재 직원의 상사가 되는 방식으로 리턴하라는 것이다.

다음 예제 코드를 보면, prior 부분이 = 기호를 사이에 두고 반대편으로 건너갔는데,

결과는 다음과 같이 트리를 거슬러 내려가는 것이 아니라, 거슬러 올라가는 방식으로 리턴되었다.

select lpad(' ',(level-1)*2,' ')||직원 직원, 직급

  from 직원
start with 직원 ='철수'
connect by prior 직속상사 = 직원

결과

 이 쿼리에서는 철수가 루트 노드이며, 그의 상사가 오히려 아래에 표현되어 있다. 

그 이유는 " 방금 전 행의 직속상사 값이 현재 행의 직원 값인 모든 행을 찾아라"라고 선언했기 때문이다. 

이와 같이 prior 키워드를 등호의 반대편으로 넣어도 오류가 발생하지 않고, 전혀 다른 결과가 얻어짐을 알 수 있다.  

prior 키워드는 또한 이전 행의 열을 참조하기 위해 다음과 같이 select 절 내에서 사용 될 수도 있다.

select lpad(' ',(level-1)*2,' ')||직원 직원, prior 직원 상사, 직급

  from 직원
start with 직원 ='철수'
connect by prior 직속상사 = 직원

결과

* level

level은 오라클에서 실행되는 모든 쿼리 내에서 사용 가능한 가상-열로서, 트리 내에서 어떤 단계(level)에 있는지를 나타내는 정수값이다.

계층적인 쿼리가 아니라면 다음과 같이 모든 값이 0, 즉 같은 단계를 가질 것이다.

select 직원,level

  from 직원

결과

한편, 계층적 쿼리에서는 level의 값을 통해 트리에서의 위치를 확인할 수 있다. 루트 노드의 level 값이 1이다.

select lpad(' ',(level-1)*2,' ')||직원 직원,직급,level

  from 직원
start with 직속상사 is null
connect by prior 직원 = 직속상사

결과

 트리를 한 단계씩 거슬러 내려갈 때마다 값이 1씩 증가함을 알 수 있다. 

level은 여러 가지 면에서 아주 유용하다. 먼저, 다음과 같이 각 항목을 출력할 때 앞에 붙는 공백의 양을 조절하여 계층적인 형식을 한눈에 알아볼 수 있도록 하는 것이 가능하다.

select lpad(' ',(level-1)*2,' ')||직원 직원

또한, level 값이 3까지인 내용만을 출력하라. 등의 명령도 가능하다.

select lpad(' ',(level-1)*2,' ')||직원 직원,직급,level

  from 직원
start with 직속상사 is null
connect by prior 직원 = 직속상사 and level <=3

 

결과

철수와 길동의 경우는 level 값이 4이기 때문에 출력되지 않았다. 

level <=3 이라는 조건을 where 절이 아닌 connect by 절에 넣은 것에 주의해야한다.  

어떤 곳에 넣어도 결과는 같지만, where 절에 넣으면 전체 트리를 구성한 후에 다시 선택하는 반면, connect by 절에 넣으면 이 조건을 사용해서 트리를 구성하기 때문에 보다 효과적이라고 할 수 있다.

 

출처: https://vaert.tistory.com/m/166