따봉 구걸남's 블로그

데이터베이스 모델링에서 관계는 결국 컬럼으로 표현이 되는데 이 컬럼이 참조하는 테이블이 자기자신일 때가 있다. 

이를 자기 참조 모델 또는 순환 관계 모델이라고도 부르는데, 어떻게 사용하는지 알아보자.

-- 테이블 생성
CREATE TABLE EMPLOYEES (
    EMPLOYEE_ID NUMBER PRIMARY KEY,
    FIRST_NAME VARCHAR2(50),
    LAST_NAME VARCHAR2(50),
    EMAIL VARCHAR2(100),
    PHONE_NUMBER VARCHAR2(20),
    HIRE_DATE DATE,
    JOB_ID VARCHAR2(10),
    SALARY NUMBER(8,2),
    MANAGER_ID NUMBER,
    DEPARTMENT_ID NUMBER
);

-- 샘플 데이터 삽입
INSERT INTO EMPLOYEES VALUES (100, 'Steven', 'King', 'SKING', '515.123.4567', TO_DATE('17-JUN-2003', 'DD-MON-YYYY'), 'AD_PRES', 24000, NULL, 90);
INSERT INTO EMPLOYEES VALUES (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', TO_DATE('21-SEP-2005', 'DD-MON-YYYY'), 'AD_VP', 17000, 100, 90);
INSERT INTO EMPLOYEES VALUES (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', TO_DATE('13-JAN-2001', 'DD-MON-YYYY'), 'AD_VP', 17000, 100, 90);
INSERT INTO EMPLOYEES VALUES (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', TO_DATE('03-JAN-2006', 'DD-MON-YYYY'), 'IT_PROG', 9000, 102, 60);
INSERT INTO EMPLOYEES VALUES (104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', TO_DATE('21-MAY-2007', 'DD-MON-YYYY'), 'IT_PROG', 6000, 103, 60);
INSERT INTO EMPLOYEES VALUES (105, 'David', 'Austin', 'DAUSTIN', '590.423.4569', TO_DATE('25-JUN-2005', 'DD-MON-YYYY'), 'IT_PROG', 4800, 103, 60);
INSERT INTO EMPLOYEES VALUES (106, 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', TO_DATE('05-FEB-2006', 'DD-MON-YYYY'), 'IT_PROG', 4800, 103, 60);

SELECT LEVEL, 
       LPAD(' ', 4*(LEVEL-1)) || e.FIRST_NAME || ' ' || e.LAST_NAME AS "Employee",
       e.EMPLOYEE_ID,
       e.MANAGER_ID,
       e.JOB_ID,
       e.SALARY,
       e.DEPARTMENT_ID
FROM EMPLOYEES e
START WITH e.MANAGER_ID = 0
CONNECT BY PRIOR e.EMPLOYEE_ID = e.MANAGER_ID
WHERE e.DEPARTMENT_ID = 60
ORDER SIBLINGS BY e.SALARY DESC;

- WHERE 절은 계층 구조가 만들어진 후에 적용. => 상위 관리자가 다른 부서에 속해 있어도(ex. 90) 결과에 포함
- ORDER SIBLINGS BY : 이 구문은 같은 레벨의 형제 노드들을 정렬.
  여기서는 각 레벨에서 급여(SALARY)를 기준으로 내림차순 정렬합니다. 예를 들어, LEVEL 4의 Bruce, David, Valli는 모두 같은 상사(Alexander)를 가지고 있으며, 이들은 급여 순으로 정렬됨.
- 최상위노드의 manager_id 는 보통 null 이 된다. 그러나 검색 편의를 위해 특정 값을 넣거나 편의상 컬럼을 추가하기도한다. (ex. IS_ROOT )

- prior 에 명시된 컬럼이 상수로 바뀌고 이를 반대편 컬럼(manager_id) 에 넣고 찾아가게 되므로 해당 컬럼에 인덱스를 생성하는 것이 좋다. 역전개의 가능성이 있기에 반대쪽 컬럼에도 인덱스를 생성하는 것이 좋다.