[SQL]계층 구조 쿼리의 이해[3회]

마농의 SQL 백문백답: 단순하고 쉽게 작성하는

계층 구조 쿼리의 이해(3)

단순하고 쉽게 작성하는 SQL 노하우를 공유하자!

SQL은 필자에게 있어서 참으로 이해하기 쉬운 프로그램 언어였다. 간단한 기본 구문(SELECT, FROM, WHERE, GROUP BY, HAVING)과 조인을 통해 안 되는 게 없을 정도였기 때문이다. 물론 기본 구문 외에 다양한 함수들은 따로 배워야 하겠지만 말이다. 필자가 Q&A 게시판에 올라오는 수많은 질문에 답하면서 느낀 점은 ‘질문을 올린 사람은 SQL을 어렵게 생각하고 있었다’는 것이었다. 앞서 언급한 기본 구문들은 정말 너무나도 간단한데, 이 간단한 구문을 복잡하게 사용하고 있었던 것이다. 필자가 다루는 SQL은 전혀 복잡하지 않다. 필자는 SQL을 단순하고 쉽게 작성하는 노하우를 이 페이지를 통해 독자들과 공유하고자 한다. 자주 올라오는 SQL 질문들, 독자들이 어려워했던 질문들의 해법을 제시하고자 한다.

지난 회에는 계층구조 기본 구문에 이어 응용문제를 풀어보았다. 이번 회에도 계층쿼리 응용문제를 계속해서 살펴보겠다. 단, 이번 시간에는 계층쿼리와는 전혀 상관이 없을 것 같은 문제를 소개한다. 하지만 이 문제들을 계층쿼리를 이용해 풀어낼 것이다. 이번 시간은 계층쿼리의 사용범위가 무궁무진함을 보여주는 시간이 될 것이다.

[질문 13] 원하는 수만큼의 임의 데이터를 생성하는 방법이 있을까요?

이 질문은 얼핏 보기에 계층쿼리와는 무관해 보인다. 하지만 이 문제도 계층 쿼리를 이용해 풀어낼 수 있다. 우선은 간단한 예를 통해 문제에 접근해 보겠다. 예를 들어 1부터 10까지 숫자를 조회하는 SQL 을 만들어야 한다고 생각해 보자.

쉽게 생각할 수 있는 방법은 원하는 결과를 이어 붙이는 것이다.

SELECT 1 n FROM dual
UNION ALL SELECT  2 FROM dual
UNION ALL SELECT  3 FROM dual
UNION ALL SELECT  4 FROM dual
UNION ALL SELECT  5 FROM dual
UNION ALL SELECT  6 FROM dual
UNION ALL SELECT  7 FROM dual
UNION ALL SELECT  8 FROM dual
UNION ALL SELECT  9 FROM dual
UNION ALL SELECT 10 FROM dual
;

UNION ALL을 이용해 결과를 도출했다. 참으로 단순 명료한 구문으로 누구나 이해하기 쉬운 구문이다. 하지만 10이 아닌 100, 1000 또는 그 이상의 수를 구할 때는 이 방법을 사용할 수는 없을 것이다.

이번에는 기존 테이블을 이용해 보자.

SELECT ROWNUM n
  FROM emp
 WHERE ROWNUM <= 10
;

ROWNUM을 이용했다. ROWNUM의 특성을 아주 잘 살린 구문이다. UNION ALL로 이어 붙이 구문보다 훨씬 더 깔끔하다. 이 구문엔 문제는 없을까? 이 구분은 다음과 같은 문제를 갖고 있다.

첫째, 해당 테이블의 데이터가 10건 미만이라면? 원하는 결과를 얻을 수도 없다.
둘째, 특정 테이블을 지정해 사용해야 하는 문제가 있어서 범용적으로 적용하기 어렵다.
모든 사용자가 공통으로 갖고 있는 Dictionary 정보를 이용할 수도 있긴 하다.

SELECT ROWNUM n
  FROM user_tab_columns
 WHERE ROWNUM <= 10
;

하지만 이런 Dictionary 정보는 내부적으로 또 다른 정보들의 조합으로 이루어지므로 성능상 권장할 만한 방법은 아니다.

CREATE TABLE copy_t AS
SELECT ROWNUM n FROM user_tab_columns
;
SELECT n
  FROM copy_t
 WHERE n <= 10
;

이번에는 숫자가 저장된 테이블을 미리 생성해 놓고 사용하는 방법이다. 이는 다음 방법이 생기기 전까지(Oracle 9i 이전 버전) 매우 유용하게 사용된 기법이다.

SELECT LEVEL n
  FROM dual
 CONNECT BY LEVEL <= 10
;

이 구문이 바로 이 질문에 대한 답이다. dual 테이블에 계층 구문인 CONNECT BY 구문을 이용했다. 구문도 매우 간단하며, 숫자의 제약도 없고, dual을 이용해 성능도 뛰어나다. 원리는 이렇다.

dual 은 데이터 1건의 테이블이고, 여기에 계층 쿼리 조건을 적용 시켰는데, 그 조건이 일반적인 계층쿼리의 조건인 PRIOR를 이용한 상하위 관계 조건이 아니다. PRIOR 조건이 없이 그저 계층의 깊이를 제한하는 구문만 있을 뿐이다.

1건의 자료가 부모가 되고, 그 자료가 다시 또 그 자식이 되고, 이를 반복하게 되는 구문이다. 여기에 LEVEL <= 10 조건이 없다면 무한루프에 빠질 것이다. 이 구문은 앞으로 계속 유용하게 소개될 구문이므로 반드시 익혀두길 바란다. 이 구문을 응용한 문제풀이는 다음 시간에 다시 자세히 다루게 될 것이다.

[질문 14] CONNECT BY LEVEL <= n 의 결과가 이상해요.

SELECT LEVEL lv
  FROM emp
 WHERE empno = 7788
 CONNECT BY LEVEL <= 3
;

앞서 배운 행 생성 구문(CONNECT BY LEVEL <= n)을 잘못 사용한 결과다. 이 구문을 사용한 의도는 7788 사원의 자료 1건을 3건으로 복제하기 위함일 것이다. 예상한 결과는 1,2,3이라는 3건이 나와야 하지만 실행 결과는 211건이 나온다. 이는 CONNECT BY 절과 WHERE 절의 처리순서 때문이다.

WHERE 절이 먼저 처리되고 CONNECT BY 절이 처리되었다면 원하는 결과가 나왔을 것이다. 그러나 실제는 그렇지 않다. CONNECT BY 절이 먼저 수행되고 WHERE 절이 나중에 수행된다. 처리 과정을 살펴보면 START WITH 구문이 없으므로 emp 테이블의 모든 자료 14건이 루트가 된다.

또한 CONNECT BY PRIOR 조건이 없으므로 14건이 모두 자식이 된다.
LEVEL 1의 자료 14건
LEVEL 1의 자료마다 14건의 자식이 붙게 된다.
LEVEL 2의 자료는 14 * 14 건이 된다.
마찬가지로 LEVEL 3의 자료는 14 * 14 * 14 건이 된다.

계 층 쿼리로 인해 생성된 행은 모두 14 + 14*14 + 14*14*14 = 2954건이 되며, 마지막으로 WHERE 절이 수행되어 7788 사원만 뽑은 것이 211건이 되는 것이다. 한순간의 실수가 엄청난 결과를 초래하게 된 것이며, 만약 LEVEL <= 3이 아닌 LEVEL <= 10으로 실행했다면?

모래시계를 만나 실행 결과를 끝내 보지 못할 것이다. 총 건수 = 14 + 14^2 + 14^3 + 14^4 + 14^5 + 14^6 + 14^7 + 14^8 + 14^9 + 14^10
= 311,505,013,050 건

(CONNECT BY LEVEL <= n)을 이용한 행 생성 구문은 반드시 dual과 같이 1건의 자료에 대해서만 사용해야 한다.

[질문 15] 조합 가능한 모든 경우의 수를 구하고 싶어요.

코드가 저장돼 있는 테이블로부터 저장된 코드의 조합으로 만들 수 있는 모든 경우의 수를 구하는 문제다. 예를 들어 아래와 같이 3개의 코드 집합으로 만들 수 있는 조합의 결과는 모두 7가지다.

 

경우의 수를 구하기 위해 먼저 Cross Join을 이용해 보자. Cross Join은 두 집합 간에 연결고리 없이 모든 경우의 수로 조인을 하는 것을 말한다. 이렇게 연결된 집합을 Cartesian Product이라고도 한다. Cross Join을 이용하여 3개 코드를 이용한 두 개 조합을 만들어 보겠다.

SELECT t1.code code1
     , t2.code code2
  FROM test t1
     , test t2
;

 

3 건의 자료를 Self Cross Join하여 위의 결과를 얻었다.
3건 * 3건 = 9건

앞 선 질문에서 사용한 계층쿼리 결과와의 유사점이 보이는가? 14건의 자료에 계층 전개하여 2 LEVEL에서 14*14 건의 자료가 생성됐다. 여기서도 마찬가지로 3*3건의 자료가 생성됐다. 계층 쿼리 사용 가능성이 보이는 것이다.

우선 위 데이터 중 CODE1과 CODE2가 같은 자료일 경우 필요 없다. 또한 A-B와 B-A는 동일한 조합으로 보고 B-A는 필요 없다.이 두 조건을 추가해 보자.

SELECT t1.code code1
     , t2.code code2
  FROM test t1
     , test t2
 WHERE t1.code < t2.code
;

 

2개 코드로 만들 수 있는 조합이 완성됐다. 나머지 1개와 3개 코드를 추가해 쿼리를 완성해 보자

SELECT code
  FROM test
 UNION ALL
SELECT t1.code || '-' || t2.code
  FROM test t1
     , test t2
 WHERE t1.code < t2.code
 UNION ALL
SELECT t1.code || '-' || t2.code || '-' || t3.code
  FROM test t1
     , test t2
     , test t3
 WHERE t1.code < t2.code
   AND t2.code < t3.code
;

셀프 조인을 이용하여 경우의 수 결과가 나오긴 했다. 하지만 쿼리가 복잡하고 코드 개수가 늘어난다면? 그 때마다 UNION ALL 쿼리가 추가돼야 하는 부담이 있을 것이다.

위 에서 계층 쿼리 사용 가능성에 대해서 언급했다. 아무 조건 없이 조인한 결과는 아무 조건 없이 계층쿼리를 전개한 결과와 건수가 일치하였다. 위에서 사용한 조건(t1.code < t2.code)을 계층 전개 조건으로 하여 계층쿼리를 만들어 보자.

SELECT code
     , LEVEL lv
     , SYS_CONNECT_BY_PATH(code,'-') path
  FROM test
 CONNECT BY PRIOR code < code
;

 

t1.code 가 PRIOR code로 대체되고 t2.code가 code로 대체돼 CONNECT BY의 조건으로 주어졌다. 이 조건에 의해 A에서 시작하여 B, C로 이어지는 계층 경로가 생겼고, 또 B에서 시작하여 C로 연결되는 경로가 생겼다. C에서 시작한 코드는 이어지는 코드 없이 끝이 났음을 보여주는 결과다.

SYS_CONNECT_BY_PATH를 이용해 구한 이 경로들이 바로 이 문제의 정답이다. 쿼리를 다듬어 최종 결과를 완성해 보자.

SELECT SUBSTR(SYS_CONNECT_BY_PATH(code,'-'), 2) code
  FROM test
 CONNECT BY PRIOR code < code
 ORDER BY LEVEL, code
;

계층 쿼리를 이용해 간단하게 조합 가능한 모든 경우의 수를 구해 냈다. 완성된 계층 쿼리를 이용하게 되면 앞선 Self Cross Join 기법에서와는 다르게 코드의 개수가 늘어나더라도 쿼리를 변경하지 않아도 된다. 훨씬 더 코드가 간결하지 않은가?

[질문 16] 순서까지 고려한 경우의 수를 구하고 싶어요.

위 [질문 15]에서 우리는 B-A의 조합을 A-B 조합과 동일시하고 결과에서 제외시켰다. 이번에는 순서가 다른 조합에 대해 다른 조합으로 보고 결과에 포함시키는 문제다. 결과는 다음과 같이 나와야 한다.

 

[질문 15]에서 코드가 커지는 방향(A-B)으로 계층 전개를 시켰다.

CONNECT BY PRIOR code < code

하지만 이번에는 코드가 작아지는 방향(B-A)으로도 계층 전개를 시켜야 한다. 다만 (A-A)와 같이 코드가 같은 경우는 없다. 다시 말하면 코드가 같지 않다는 조건으로 계층 전개를 하면 될 것이다. 즉 상위 코드와 하위 코드가 같지 않다는 조건으로 계층쿼리를 작성해 보자.

SELECT SUBSTR(SYS_CONNECT_BY_PATH(code,'-'), 2) code
  FROM test
 CONNECT BY PRIOR code != code
 ORDER BY LEVEL, code
;

[ORA-01436: CONNECT BY의 루프가 발생되었습니다]

실행결과는 안타깝게도 [ORA-01436: CONNECT BY의 루프가 발생되었습니다]라는 오류가 발생하였다.

PRIOR code != code의 조건으로 연결하게 되면, (A-A)와 같은 자료는 나오지 않을 것이다. 그러나 (A-B-A)와 같은 자료는 가능하게 된다. 바로 직접 연결된 상하위 코드끼리는 같지 않은 걸로 체크되지만, 한 단계 건너뛰게 되면 (A-B-A)와 같이 앞서 나왔던 코드가 다시 나올 수 있게 된다

이렇게 되면 (A-B-A-B-A ...) 형태와 같이 코드들이 무한 반복될 수 있기 때문에 무한 루프 방지 차원에서 에러를 발생하게 된다. 하지만 우리는 첫 번째 기본 구?다.

SELECT SUBSTR(SYS_CONNECT_BY_PATH(code,'-'), 2) code
  FROM test
 CONNECT BY NOCYCLE PRIOR code != code
 ORDER BY LEVEL, code
;

NOCYCLE을 이용하여 간단하게 문제를 해결하고 결과도 도출하였다.

정리하며

지난시간에 이어 계속해서 계층 쿼리 응용과정을 알아보았다.

1. 원하는 수만큼의 ROW 생성 쿼리
2. 조합 가능한 모든 경우의 수 구하기

위 두 가지 문제를 계층 쿼리를 이용해 풀어보았다. 복잡할 것만 같았던 문제들을 몇 줄짜리 간단한 구문으로 해결하였다. 문제 풀이 결과 SQL만 본다면 허무하다 싶을 정도로 간단하다. 하지만 간단한 구문 속에 담긴 의미는 결코 간단하지만은 않을 것이다. (다음 회에 계속)

[출처 : http://www.dbguide.net/knowledge.db?cmd=view&boardUid=186615&boardConfigUid=19&boardStep=&categoryUid=205]