11.2 버전부터 재귀 서브 쿼리 팩토링(reqursive subquery factoring) 기능을 사용할 수 있다. 재귀 서브 쿼리 팩토링은 ANSI 표준 SQL 문법이다. 계층 쿼리 절보다 복잡하지만 다양한 기능을 사용할 수 있다.

(기본적으로 계층 쿼리 절을 사용하고, 특별한 루프 처리나 상위 노드에 대한 누적 집계 등이 필요할 경우 선택적으로 재귀 서브 쿼리 팩토링 기능을 사용하는 것이 일반적이다.)

 

[기본 문법]

재귀 서브 쿼리 팩토링은 WITH 절을 사용한다.

재귀 서브 쿼리 팩토링의 WITH 절은 서브 쿼리, SEARCH 절, CYCLE 절로 구성된다.

 

WITH QUERY_NAME ([C_ALIAS [, C_ALIAS] ...]) AS (SUBQUERY) [SEARCH_CLAUSE] [CYCLE_CLAUSE]

 

서브 쿼리는 UNION ALL 연산자로 구성된다. UNION ALL 연산자의 상단 쿼리가 START WITH 절, 하단 쿼리가 CONNECT BY 절의 역할을 수행한다. WITH 절에 정의한 서브 쿼리를 하단 쿼리와 조인함으로써 재귀적으로 조인이 수행되는 방식으로 동작한다.

 

아래 쿼리는 순방향으로 계층을 전개한다.

현재 노드의 MGR이 부모 노드의 EMPNO인 행을 조회한다.

 

WITH W1 (EMPNO, ENAME, MGR, LV) AS (

SELECT EMPNO, ENAME, MGR, 1 AS LV

FROM EMP

WHERE MGR IS NULL -- START WITH 절

UNION ALL

SELECT C.EMPNO, C.ENAME, C.MGR, P.LV + 1 AS LV

FROM W1 P, EMP C

WHERE C.MGR = P.EMPNO -- CONNECT BY 절

)

SELECT LV, EMPNO, LPAD (' ', LV - 1, ' ') || ENAME AS ENAME, MGR FROM W1;

 

아래 쿼리는 역방향으로 계층을 전개한다.

현재 노드의 EMPNO가 자식 노드의 MGR인 행을 조회한다.

 

WITH W1 (EMPNO, ENAME, MGR, LV) AS (

SELECT EMPNO, ENAME, MGR, 1 AS LV

FROM EMP

WHERE ENAME = 'ADAMS' -- START WITH 절

UNION ALL

SELECT C.EMPNO, C.ENAME, C.MGR, P.LV + 1 AS LV

FROM W1 P, EMP C

WHERE C.EMPNO = P.MGR -- CONNECT BY 절

)

SELECT LV, EMPNO, LPAD(' ', LV - 1, ' ') || ENAME AS ENAME, MGR FROM W1;

 

재귀 서브 쿼리 팩토링은 계층 정보를 조회하기 위한 연산자와 슈도 칼럼을 제공하지 않는다.

아래 쿼리의 표현식으로 계층 정보를 조회할 수 있다.

 

WITH W1 (EMPNO, ENAME, MGR, LV, EMPNO_P, RT, PT) AS (

SELECT EMPNO, ENAME, MGR

, 1 AS LV -- LEVEL

, NULL AS EMPNO_P -- PRIOR

, ENAME AS RT -- CONNECT_BY_ROOT

, ENAME AS PT -- SYS_CONNECT_BY_PATH

FROM EMP

WHERE MGR IS NULL

UNION ALL

SELECT C.EMPNO, C.ENAME, C.MGR

, P.LV + 1 AS LV -- LEVEL

, P.EMPNO AS EMPNO_P -- PRIOR

, P.RT -- CONNECT_BY_ROOT

, P.RT || ',' || C.ENAME AS PT -- SYS_CONNECT_BY_PATH

FROM W1 P, EMP C

WHERE C.MGR = P.EMPNO)

SEARCH DEPTH FIRST BY EMPNO SET SO

SELECT LV, EMPNO, LPAD (' ', LV - 1, ' ') || ENAME AS ENAME, MGR, EMPNO_P, RT, PT

, CASE WHEN LV - LEAD (LV) OVER (ORDER BY SO) < 0

THEN 0

ELSE 1

END AS LF -- CONNECT_BY_ISLEAF

FROM W1

ORDER BY SO;

 

[계층 정렬]

재귀 서브 쿼리 팩토링은 계층을 정렬하기 위해 SEARCH 절을 제공한다.

BREADTH 방식과 DEPTH 방식을 사용할 수 있으며, FIRST BY 뒤에 기술된 C_ALIAS에 따라 행이 정렬된다.

ORDERING_COLUMN은 정렬 순번이 반환될 열을 지정한다.

 

SEARCH {DEPTH | BREADTH} FIRST BY C_ALIAS [, C_ALIAS]... SET ORDERING_COLUMN

 

방식 - 설명

BREADTH - 자식 행을 반환하기 전에 형제 행을 반환 (기본값)

DEPTH - 형제 행을 반환하기 전에 자식 행을 반환 (= 계층 쿼리 절)

 

아래는 BREADTH 방식을 사용한 쿼리다.

BREADTH 방식은 너비(BREADTH)를 기준으로 계층을 탐색한다.

2레벨이 모두 반환된 후 3레벨이 반환된다.

 

WITH W1 (EMPNO, ENAME, MGR, LV) AS (

SELECT EMPNO, ENAME, MGR, 1 AS LV

FROM EMP

WHERE MGR IS NULL

UNION ALL

SELECT C.EMPNO, C.ENAME, C.MGR, P.LV + 1 AS LV

FROM W1 P, EMP C

WHERE C.MGR = P.EMPNO)

SEARCH BREADTH FIRST BY EMPNO SET SO

SELECT LV, EMPNO, LPAD (' ', LV - 1, ' ') || ENAME AS ENAME, MGR, SO

FROM W1

ORDER BY SO;

 

아래는 DEPTH 방식을 사용한 쿼리다.

DEPTH 방식은 깊이(DEPTH)를 기준으로 계층을 탐색한다.

계층 쿼리 절과 결과가 동일하다.

 

WITH W1 (EMPNO, ENAME, MGR, LV) AS (

SELECT EMPNO, ENAME, MGR, 1 AS LV

FROM EMP

WHERE MGR IS NULL

UNION ALL

SELECT C.EMPNO, C.ENAME, C.MGR, P.LV + 1 AS LV

FROM W1 P, EMP C

WHERE C.MGR = P.EMPNO)

SEARCH DEPTH FIRST BY EMPNO SET SO

SELECT LV, EMPNO, LPAD (' ', LV - 1, ' ') || ENAME AS ENAME, MGR, SO

FROM W1

ORDER BY SO;

 

[루프 처리]

재귀 서브 쿼리 팩토링은 루프를 처리하기 위해 CYCLE 절을 제공한다.

CYCLE 절은 상위 노드에 동일한 C_ALIAS 값이 존재하면 루프가 발생한 것으로 인식한다.

 

CYCLE C_ALIAS [, C_ALIAS] ... SET CYCLE_MARK_C_ALIAS TO CYCLE_VALUE DEFAULT NO_CYCLE_VALUE

 

항목 - 설명

C_ALIAS [, C_ALIAS] - 루프 여부를 확인할 열

CYCLE_MARK_C_ALIAS - 루프 여부를 반환할 열

CYCLE_VALUE - 루프가 발생한 경우 반환할 값

NO_CYCLE_VALUE - 루프가 발생하지 않은 경우 반환할 값

 

아래는 CYCLE 절을 사용한 쿼리다. 상위 노드 (LV = 1)에 현재 노드 (LV = 4)의 EMPNO인 7839와 동일한 EMPNO가 존재하기 때문에 루프가 발생한 것으로 인식한다.

계층 쿼리 절과 달리 루프가 발생한 노드까지 반환된다.

IC 열이 1인 행이 루프가 발생한 행이다.

 

WITH W1 (EMPNO, ENAME, MGR, LV) AS (

SELECT EMPNO, ENAME, MGR, 1 AS LV

FROM EMP_L

WHERE EMPNO = 7839

UNION ALL

SELECT C.EMPNO, C.ENAME, C.MGR, P.LV + 1 AS LV

FROM W1 P, EMP_L C

WHERE C.MGR = P.EMPNO)

SEARCH DEPTH FIRST BY EMPNO SET SO

CYCLE EMPNO SET IC TO '1' DEFAULT '0'

SELECT LV, EMPNO, LPAD (' ', LV - 1, ' ') || ENAME AS ENAME, MGR, IC

FROM W1

ORDER BY SO;

 

재귀 서브 쿼리 팩토링은 계층 전개와 무관한 열로도 루프 여부를 확인할 수 있다.

아래 쿼리는 DEPTNO 열로 루프 여부를 확인한다.

SCOTT과 FORD는 상위 노드인 JONES의 DEPTNO가 20이므로 둘 다 루프가 발생한 것으로 인식된다.

 

WITH W1 (EMPNO, ENAME, MGR, DEPTNO, LV) AS (

SELECT EMPNO, ENAME, MGR, DEPTNO, 1 AS LV

FROM EMP

WHERE MGR IS NULL

UNION ALL

SELECT C.EMPNO, C.ENAME, C.MGR, C.DEPTNO, P.LV + 1 AS LV

FROM W1 P, EMP C

WHERE C.MGR = P.EMPNO)

SEARCH DEPTH FIRST BY EMPNO SET SO

CYCLE DEPTNO SET IC TO '1' DEFAULT '0'

SELECT LV, EMPNO, LPAD (' ', LV - 1, ' ') || ENAME AS ENAME, MGR, DPETNO, IC

FROM W1

ORDER BY SO;

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

 

 

계층 쿼리 절(hierarchical query clause)은 오라클 데이터베이스의 전통적인 계층 쿼리 구문이다.

​

[기본 문법]

계층 쿼리 절은 WHERE 절 다음에 기술하며, FROM 절이 수행된 후 수행된다.

START WITH 절과 CONNECT BY 절로 구성되며, START WITH 절이 수행된 후 CONNECT BY 절이 수행된다.

START WITH 절은 생략이 가능하다.

​

[START WITH condition] CONNECT BY [NOCYCLE] condition

​

절 - 설명

START WITH 절 - 루트 노드를 생성하며 1번만 수행

CONNECT BY 절 - 루트 노드의 하위 노드를 생성하며 조회 결과가 없을 때까지 반복 수행

​

계층 쿼리 절에서 사용할 수 있는 연산자, 슈도 칼럼, 함수다.

​

유형 - 항목 - 설명

연산자 - PRIOR - 직전 상위 노드의 값을 반환

            - CONNECT_BY_ROOT - 루트 노드의 값을 반환

슈도 칼럼 - LEVEL - 현재 레벨을 반환

                - CONNECT_BY_ISLEAF - 리프 노드인 경우 1, 아니면 0을 반환

                - CONNECT_BY_ISCYCLE - 루프가 발생한 경우 1, 아니면 0을 반환

함수 - SYS_CONNECT_BY_PATH - 루트 노드에서 현재 노드까지의 경로를 반환

​

아래는 계층 쿼리 절을 사용한 쿼리다.

START WITH 절로 MGR이 존재하지 않는 행을 조회하고, CONNECT BY 절로 현재 노드의 MGR이 직전 상위 노드의 EMPNO인 행을 반복해서 조회한다.

ENAME 열은 LEVEL 슈도 칼럼과 LPAD 함수를 사용하여 계층의 레벨에 따라 값을 들여쓰기 했다.

​

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

            , PRIOR EMPNO AS EMPNO_P

  FROM EMP

START WITH MGR IS NULL       -- MGR이 존재하지 않는 행

CONNECT BY MGR = PRIOR EMPNO -- MGR이 부모 노드의 EMPNO인 행

;

​

[SYS_CONNECT_BY_PATH 함수]

SYS_CONNECT_BY_PATH 함수는 루트 노드에서 현재 노드까지의 COLUMN을 CHAR로 구분하여 연결한 값을 반환한다. COLUMN 값에 CHAR가 포함되어 있으면 ORA-30004 에러, 연결한 값의 길이가 4000보다 길면 ORA-01489 에러가 발생한다.

​

SYS_CONNECT_BY_PATH (COLUMN, CHAR)

​

에러 - 설명

ORA-30004 - ... 구분 기호를 열 값의 일부로 사용할 수 없습니다.

ORA-01489 - 문자열 연결의 결과가 너무 깁니다.

​

아래는 CONNECT_BY_ROOT 연산자, CONNECT_BY_ISLEAF 슈도 칼럼, SYS_CONNNECT_BY_PATH 함수를 사용한 쿼리다.

​

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

            , CONNECT_BY_ISLEAF AS LF

            , CONNECT_BY_ROOT ENAME AS RT

            , SYS_CONNECT_BY_PATH (ENAME, ',') AS PT

  FROM EMP

START WITH MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO;

​

[동작 원리]

계층 쿼리 절은 START WITH 절로 루트 노드를 생성한 후, 결과가 없을 때까지 CONNECT 절을 반복 수행하여 하위 노드를 생성한다.

(계층 쿼리 절은 다양한 방식으로 수행될 수 있다. 수행 방식은 옵티마이저에 의해 결정된다.)

​

[1단계 (LEVEL = 1)]

START WITH 절을 수행하여 루트 노드를 생성한다. START WITH 절은 WHERE 절과 유사하게 동작한다.

​

SELECT 1 AS LV, EMPNO, ENAME, MGR

  FROM EMP

WHERE MGR IS NULL -- START WITH MGR IS NULL

;

​

-- 다음 예제를 위해 1단계 수행 결과를 임시 테이블에 저장하자.

DROP TABLE T1 PURGE;

​

CREATE TABLE T1 AS SELECT 1 AS LV, EMPNO, ENAME, MGR FROM EMP WHERE MGR IS NULL;

​

[2단계 (LEVEL = 2)]

CONNECT BY 절로 임시 테이블에 저장된 1단계 결과와 EMP 테이블을 조인한다.

CONNECT BY 절도 WHERE 절과 유사하게 동작한다.

​

SELECT 2 AS LV, C.EMPNO, C.ENAME, C.MGR

  FROM T1 P, EMP C

WHERE P.LV = 1

     AND C.MGR = P.EMPNO -- CONNECT BY MGR = PRIOR EMPNO

;

​

다음 예제를 위해 2단계 수행 결과를 임시 테이블에 저장하자.

INSERT INTO T1

SELECT 2, C.EMPNO, C.ENAME, C.MGR FROM T1 P, EMP C

WHERE P.LV = 1 AND C.MGR = P.EMPNO;

​

[3단계 (LEVEL = 3)]

CONNECT BY 절로 임시 테이블에 저장된 2단계 결과와 EMP 테이블을 조인한다.

​

SELECT 3 AS LV, C.EMPNO, C.ENAME, C.MGR

  FROM T1 P, EMP C

WHERE P.LV = 2

     AND C.MGR = P.EMPNO;

​

다음 예제를 위해 3단계 수행 결과를 임시 테이블에 저장하자.

INSERT INTO T1

SELECT 3, C.EMPNO, C.ENAME, C.MGR FROM T1 P, EMP C 

WHERE P.LV = 2 AND C.MGR = P.EMPNO;

​

[4단계 (LEVEL = 4)]

CONNECT BY 절로 임시 테이블에 저장된 3단계 결과와 EMP 테이블을 조인한다.

​

SLEECT 4 AS LV, C.EMPNO, C.ENAME, C.MGR

  FROM T1 P, EMP C

WHERE P.LV = 3

     AND C.MGR = P.EMPNO;

​

다음 예제를 위해 4단계 수행 결과를 임시 테이블에 저장하자.

INSERT INTO T1

SELECT 4, C.EMPNO, C.ENAME, C.MGR FROM T1 P, EMP C

WHERE P.LV = 3

     AND C.MGR = P.EMPNO;

​

[5단계 (LEVEL = 5)]

CONNECT BY 절로 임시 테이블에 저장된 4단계 결과와 EMP 테이블을 조인한다.

​

SELECT 5 AS LV, C.EMPNO, C.ENAME, C.MGR

  FROM T1 P, EMP C

WHERE P.LV = 4

     AND C.MGR = P.EMPNO;

​

결과가 반환되지 않으면 CONNECT BY 절의 수행을 멈추고 수행 결과가 저장된 임시 테이블을 조회하여 결과를 반환한다.

​

SELECT * FROM T1;

​

[전개 방향]

순환 관계는 순방향 또는 역방향으로 전개할 수 있다.

순방향 전개와 역방향 전개는 데이터 모델 상의 전개 방향이 반대일 뿐 동작 원리는 동일하다.

​

                           전개 방향             -    START WITH 절             -           CONNECT BY 절

순방향 전개        부모 -> 자식        - 부모 노드 조회                   -         PK에 PRIOR 기술

역방향 전개        자식 -> 부모        - 자식 노드 조회                   -          FK에 PRIOR 기술

​

아래 쿼리는 순방향으로 계층을 전개한다.

순방향 전개는 START WITH 절로 부모 노드를 조회하고, CONNECT BY 절을 통해 자식 노드로 계층을 전개한다.

PK(EMPNO)에 PRIOR 연산자를 기술하여 현재 노드의 MGR이 부모 노드의 EMPNO인 행을 조회한다.

​

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

  FROM EMP

START WITH MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO;

​

아래 쿼리는 역방향으로 계층을 전개한다.

역방향 전개는 START WITH 절로 자식 노드를 조회하고, CONNECT BY 절을 통해 부모 노드로 계층을 전개한다.

FK(MGR)에 PRIOR 연산자를 기술하여 현재 노드의 EMPNO가 자식 노드의 MGR인 행을 조회한다.

​

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

  FROM EMP

START WITH ENAME = 'ADAMS'

CONNECT BY EMPNO = PRIOR MGR;

​

[계층 정렬]

계층 쿼리 절은 형제 노드의 행을 정렬하기 위해 SIBLINGS 키워드를 제공한다.

​

계층 쿼리 절에 ORDER BY 절을 사용하면 계층 구조와 무관하게 행이 정렬된다.

​

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR, SAL

  FROM EMP

START WITH MGR IS NULL

CONNECT BY MGR = PRIRO EMPNO

ORDER BY SAL;

​

ORDER BY 절에 SIBLINGS 키워드를 사용하면 형제 노드 내에서만 행이 정렬되기 때문에 계층 구조를 유지한 상태로 행을 정렬할 수 있다.

CLARK, BLAKE, JONES는 형제 노드다. SAL 순서로 행이 정렬된다.

BLAKE의 하위 노드인 JAMES ~ ALLEN도 SAL 순서로 행이 정렬된다.

​

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR, SAL

  FROM EMP

START WITH MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO

ORDER SIBLINGS BY SAL;

​

[루프 처리]

부모 노드가 현재 노드의 자식 노드로 연결되면 루프(loop)가 발생한다.

계층 쿼리 절은 루프를 처리하기 위해 NOCYCLE 키워드와 CONNECT_BY_ISCYCLE 슈도 칼럼을 제공한다.

​

예제를 위해 아래와 같이 테이블을 생성하자.

NVL 함수로 MGR이 널인 KING의 MGR을 SCOTT의 EMPNO인 7788로 변경했다.

​

DROP TABLE EMP_L PURGE;

CREATE TABLE EMP_L AS SELECT EMPNO, ENAME, NVL (MGR, 7788) AS MGR FROM EMP;

​

아래는 EMP_L 테이블을 조회한 결과이다.

KING -> JONES -> SCOTT -> KING 구간에서 루프가 발생한다.

​

SELECT EMPNO, ENAME, MGR FROM EMP_L;

​

계층 전개 시 루프가 발생하면 에러가 발생한다.

​

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

  FROM EMP_L

START WITH EMPNO = 7839

CONNECT BY MGR = PRIOR EMPNO;

​

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

​

아래 쿼리는 CONNECT BY 절에 NOCYCLE 키워드를 기술했다.

CONNECT BY 절에 NOCYCLE 키워드를 기술하면 루프가 발생한 노드를 전개하지 않는다.

IC 열이 1인 행이 루프가 발생한 행이다.

​

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || EMPNO AS ENAME, MGR

            , CONNECT_BY_ISCYCLE AS IC

  FROM EMP_L

START WITH EMPNO = 7839

CONNECT BY NOCYCLE MGR = PRIOR EMPNO;

 

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

 

계층 쿼리를 사용하면 순환 관계를 가진 데이터를 조회할 수 있다.

 

순환 관계를 가진 데이터는 아래와 같은 계층 구조로 표현할 수 있다.

박스로 표시된 부분을 노드(node)라고 한다.

순환 관계는 노드와 노드의 관계를 통해 만들어진다.

 

[계층 구조]

LEVEL = 1 - [root] - parent

LEVEL = 2 - [branch], [branch]

LEVEL = 3 - [branch], [leaf], [leaf], [leaf] - child

LEVEL = 4 - [leaf]

 

현재 노드는 부모 노드와 자식 노드를 가질 수 있다.

일반적으로 하나의 부모 노드는 다수의 자식 노드를 가질 수 있고,

하나의 자식 노드는 하나의 부모 노드만 가질 수 있다.

(이번 장에서 살펴볼 순환 관계는 1:M 관계를 가진다. M:M 순환 관계는 BOM(Bill Of Materials) 구조로 설계 된다. 이 책은 BOM 구조를 다루지 않는다.)

 

노드 - 설명

부모 노드 - 현재 노드의 직전 상위 노드

자식 노드 - 현재 노드의 직후 하위 노드

 

노드는 부모 노드와 자식 노드의 존재 여부에 따라 루트 노드, 브랜치 노드, 리프 노드로 구분할 수 있다.

 

노드 - 설명

루트 노드 - 부모 노드가 존재하지 않는 노드

브랜치 노드 - 부모 노드와 자식 노드가 존재하는 노드

리프 노드 - 자식 노드가 존재하지 않는 노드

 

순환 관계는 계층의 깊이에 따라 레벨(level)이 부여된다.

루트 노드는 레벨이 1이고 계층이 전개될수록 레벨이 증가한다.

 

emp 테이블의 empno 열과 mgr 열은 순환 관계를 가진다.

(emp 테이블의 루트 노드는 KING이다. 부모 노드가 없으므로 mgr 열이 널이다. 성능상의 이유로 널이 아닌 기본값을 저장하기도 한다.)

 

아래 쿼리에서 JONES의 부모 노드는 EMPNO가 JONES의 MGR인 KING, 자식 노드는 MGR이 JONES의 EMPNO인 SCOTT과 FORD다.

 

SELECT EMPNO, ENAME, MGR FROM EMP;

 

KING은 JONES, BREAK, CLARK을 자식 노드로 가지고, JONES는 SCOTT, FORD를 자식 노드로 가지며, SOCTT과 FORD는 각각 ADAMS와 SMITH를 자식 노드로 가진다.

 

순환 관계를 가진 테이블은 셀프 조인을 통해 부모 노드나 자식 노드를 조회할 수 있다.

 

아래 쿼리는 JONES의 자식 노드를 조회한다.

MGR이 JONES의 EMPNO인 행을 조회하면 된다.

 

SELECT B.EMPNO, B.ENAME, B.MGR

FROM EMP A, EMP B

WHERE A.ENAME = 'JONES' -- EMPNO = 7566

AND B.MGR = A.EMPNO;

 

아래 쿼리는 JONES의 자식 노드의 자식 노드를 조회한다.

MGR이 JONES의 자식 노드인 SCOTT과 FORD의 EMPNO인 행을 조회하면 된다.

 

SELECT C.EMPNO, C.ENAME, C.MGR

FROM EMP A, EMP B, EMP C

WHERE A.ENAME = 'JONES'

AND B.MGR = A.EMPNO

AND C.MGR = B.EMPNO;

 

아래 쿼리는 SMITH의 부모 노드를 조회한다.

EMPNO가 SMITH의 MGR인 행을 조회하면 된다.

 

SELECT B.EMPNO, B.ENAME, B.MGR

FROM EMP A, EMP B

WHERE A.ENAME = 'SMITH'

AND B.EMPNO = A.MGR;

 

아래 쿼리는 SMITH의 부모 노드의 부모 노드를 조회한다.

EMPNO가 SMITH의 부모 노드인 FORD의 MGR인 행을 조회하면 된다.

 

SELECT C.EMPNO, C.ENAME, C.MGR

FROM EMP A, EMP B, EMP C

WHERE A.ENAME = 'SMITH'

AND B.EMPNO = A.MGR

AND C.EMPNO = B.MGR;

 

깊은 레벨의 노드를 조회하기 위해서는 셀프 조인을 반복해야 한다.

오라클 데이터베이스는 순환 관계를 가진 데이터를 조회할 수 있는 계층 쿼리 절과 재귀 서브 쿼리 팩토링 기능을 제공한다.

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

 

 

[Top-N 쿼리와 조인]

Top-N 쿼리와 조인은 성능과 관련된 주제지만 중요한 내용이다.

​

아래 쿼리는 조인 후 Top-N 처리를 수행한다.

emp 테이블이 14건이므로 조인을 14번 수행하고 2건의 결과를 반환한다.

emp 테이블과 dept 테이블은 조인 차수가 M:1이고, 

아우터 조인으로 조인했기 때문에 emp 테이블을 Top-N 처리한 후 dept 테이블을 조인해도 동일한 결과를 얻을 수 있다.

(emp 테이블이 dept 테이블에 대해 필수 관계를 가지므로 이너 조인으로 조인해도 결과가 동일하다.)

​

SELECT EMPNO, SAL, DEPTNO, DNAME

  FROM (SELECT A.EMPNO, A.SAL, A.DEPTNO, B.DNAME

                  FROM EMP A, DEPT B

                WHERE B.DEPTNO (+) = A.DEPTNO

                ORDER BY A.SAL, A.EMPNO)

WHERE ROWNUM <= 2;

​

아래 쿼리는 위 쿼리와 결과가 동일하다.

인라인 뷰에서 Top-N 처리한 결과 집합으로 DEPT 테이블을 아우터 조인한다.

조인은 2번만 수행된다.

​

SELECT A.EMPNO, A.SAL, A.DEPTNO, B.DNAME

  FROM (SELECT *

                   FROM (SELECT EMPNO, SAL, DEPTNO

                                   FROM EMP

                                  ORDER BY SAL, EMPNO)

            , DEPT B

WHERE B.DEPTNO (+) = A.DEPTNO

ORDER BY A.SAL, A.EMPNO;

​

1개의 열만 조회할 경우 Top-N 처리 후 스칼라 서브 쿼리를 사용할 수 있다.

SELECT A.EMPNO, A.SAL, A.DEPTNO

            , (SELECT X.DNAME FROM DEPT X WHERE X.DEPTNO = A.DEPTNO) AS DNAME

  FROM (SELECT EMPNO, SAL, DEPTNO FROM EMP ORDER BY SAL, EMPNO) A

WHERE ROWNUM <= 2;

​

아래 쿼리는 Top-N 처리 후 조인하는 방식으로 변경할 수 없다.

emp 테이블이 dept 테이블에 대해 필수 관계를 가지더라도 b.loc = 'DALLAS' 조건에 의해 인라인 뷰의 결과 집합이 달라질 수 있기 때문이다.

​

SELECT EMPNO, SAL, DEPTNO, DNAME

  FROM (SELECT A.EMPNO, A.SAL, A.DEPTNO, B.DNAME

                  FROM EMP A, DEPT B

                WHERE B.DEPTNO = A.DEPTNO

                     AND B.LOC = 'DALLAS'

                ORDER BY A.SAL, A.EMPNO)

WHERE ROWNUM <= 2;

​

아래 쿼리에서 결과가 달라진 것을 확인할 수 있다.

SELECT A.EMPNO, A.SAL, A.DEPTNO, B.DNAME

  FROM (SELECT *

                  FROM (SELECT EMPNO, SAL, DEPTNO FROM EMP ORDER BY SAL, EMPNO)

               WHERE ROWNUM <= 2) A

WHERE B.DEPTNO (+) = A.DEPTNO

     AND B.LOC (+) = 'DALLAS'

ORDER BY A.SAL, A.EMPNO;

​

[Top-N 쿼리와 UNION ALL 연산자]

Top-N 쿼리와 UNION ALL 연산자도 성능과 관련된 주제지만 중요한 내용이다.

​

아래 쿼리는 dept 테이블과 emp 테이블을 UNION ALL 연산자로 연결한 결과 집합에 Top-N 처리를 수행한다.

결과 집합을 정렬해야 하므로 소트 부하가 발생할 수 있다.

​

SELECT *

  FROM (SELECT 1 AS TP, DEPTNO AS NO, DNAME AS NAME FROM DEPT 

                UNION ALL

                SELECT 2 AS TP, EMPNO AS NO, ENAME AS NAME FORM EMP

                ORDER BY TP, NO)

WHERE ROWNUM <= 3;

​

아래 쿼리처럼 데이터 집합 별로 Top-N 처리를 수행하면 소트 부하를 경감시킬 수 있다.

UNION ALL 연산자는 순차적으로 수행된다.

아래 쿼리는 emp 테이블을 읽지 않고 결과를 반환한다.

​

SELECT *

  FROM (SELECT *

                  FROM (SELECT 1 AS TP, DEPTNO AS NO, DNAME AS NAME FROM DEPT ORDER BY NO)

                WHERE ROWNUM <= 3

                UNION ALL

                SELECT *

                  FROM (SELECT 2 AS TP, EMPNO AS NO, ENAME AS NAME FROM EMP ORDER BY NO)

                WHERE ROWNUM <= 3)

WHERE ROWNUM <= 3;

 

 

CREATE TABLE 부서 (
    부서번호    NUMBER        NOT NULL
  , 부서명      VARCHAR2(10)  NOT NULL
  , 상위부서번호 NUMBER        NOT NULL
  , CONSTRAINTS 부서_PK PRIMARY KEY (부서번호)
  , CONSTRAINTS 부서_F1 FOREIGN KEY (상위부서번호) REFERENCES 부서 (부서번호)
);
CREATE TABLE 사원 (
    사원번호    NUMBER       NOT NULL
  , 사원명      VARCHAR2(10) NOT NULL
  , 급여       NUMBER       NOT NULL
  , 소속부서코드 NUMBER       NOT NULL
  , CONSTRAINTS 사원_PK PRIMARY KEY (사원번호)
  , CONSTRAINTS 사원_F1 FOREIGN KEY (소속부서코드) REFERENCES 부서 (부서번호)
);
오라클 데이터베이스(Oracle Database)는 오라클 사에서 개발한 ORDBMS 제품이다.
오라클 데이터베이스의 명칭은 오라클 사의 공동 창업자인 래리 엘리슨(Larry Ellison)이 Ampex 사에 재직할 당시 수행한 CIA 프로젝트의 코드 네임에서 유래했다.
오라클의 사전적 의미는 예언자다.

오라클 데이터베이스는 아래의 네 가지 에디션으로 판매된다.

에디션        사용           파티셔닝       Flashback 기술
Enterprise  대형 시스템      Y            Y
Standard    중소 규모 시스템  N            Flashback Query
Express     소형 솔루션      Y            Y
Personal    개인 사용자      Y            Y


[사용자]
    사용자(user)는 데이터베이스에 로그인할 수 있는 계정이다.
    데이터베이스를 생성하면 관리자 계정이 함께 생성된다.
    관리자 계정은 DBA가 사용한다.
    개발자는 DBA가 생성한 일반 계정을 사용한다.

    SYS 사용자는 데이터베이스의 슈퍼 유저(super user) 계정으로 모든 권한을 가지고 있다.
    SYSTEM 사용자는 관리자 계정으로 모든 권한을 가지고 있지만 데이터베이스를 생성할 수 없다.

[오브젝트]
    오브젝트(object)는 논리적인 데이터 구조다.
    오브젝트는 사용자(user)에게 종속될 수 있다.
    사용자에게 종속된 오브젝트의 논리적 집합을 스키마(schema)라고 한다.
    스키마는 오브젝트를 소유한 사용자와 동일한 이름을 가진다.
    
    사용자에 종속된 오브젝트를 스키마 오브젝트, 종속되지 않는 오브젝트를 비 스키마(nonschema) 오브젝트라고 한다.

    스키마 오브젝트    - 테이블, 클러스터, 인덱스, 뷰, 시퀀스, 시노님, 오브젝트 타입
    비 스키마 오브젝트 - 사용자, 롤, 디렉터리

    오브젝트는 데이터 저장 여부에 따라 세그먼트 오브젝트와 비 세그먼트 오브젝트로 구분할 수 있다.
    저장 공간이 필요한 오브젝트를 세그먼트(segment)라고 한다.

    세그먼트 오브젝트 - 테이블, 클러스터, 인덱스
    비 세그먼트 오브젝트 - 뷰, 시퀀스, 시노님, 오브젝트 타입, 사용자, 롤, 디렉터리

[테이블]
    테이블(table)은 데이터를 구성하는 기본 단위다.
    행(row)과 열(column)로 구성된다.

[데이터 타입]
    열은 데이터 타입(data type)을 지정할 수 있다.

    문자 - CHAR, VARCHAR2, CLOB, LONG, NCHR, NVARCHAR2, NCLOB
    숫자 - NUMBER, BINARY_FLOAT, BINARY_DOUBLE
    날짜 - DATE, TIMESTAMP, INTERVAL
    이진 - BLOB, BFILE, LONG RAW, RAW
    기타 - ROWID, UROWID

[데이터 무결성]
    데이터 무결성(Data Integrity)은 데이터의 정확성과 일관성이 유지되고 있는 상태를 의미한다.
    데이터 무결성은 개체 무결성(Entity integrity), 참조 무결성(referential integrity),
    범위 무결성(domain integrity), 사용자 정의 무결성(user defined integrity)으로 구분된다.

    개체 무결성 - 엔티티의 인스턴스가 속성이나 속성의 조합으로 식별되어야 함
    참조 무결성 - 자식 엔티티의 외래 식별자가 부모 엔티티의 기본 식별자에 존재해야 함
    범위 무결성 - 속성값이 지정한 범위에 유효해야 함
    사용자 정의 무결성 - 개체 무결성, 참조 무결성, 범위 무결성에 속하지 않는 무결성

    데이터 무결성은 DBMS가 제공하는 기능을 통해 보장할 수 있다.

    개체 무결성 - PF 제약 조건, UNIQUE 제약 조건, NOT NULL 제약 조건
    참조 무결성 - FK 제약 조건, 트리거
    범위 무결성 - 데이터 타입, 기본값, CHECK 제약 조건
    사용자 정의 무결성 - 트리거

 

+ Recent posts