고정 헤더 영역
상세 컨텐츠
본문
-- TEST50
CREATE TABLE TEST50 (MCODE VARCHAR2(15),PCODE VARCHAR2(15));
CREATE INDEX TEST50_I ON TEST50(MCODE);
-- TEST50 DATA
INSERT INTO TEST50 VALUES ( '000','');
INSERT INTO TEST50 VALUES ( '222','220');
INSERT INTO TEST50 VALUES ( '200','000');
INSERT INTO TEST50 VALUES ( '210','200');
INSERT INTO TEST50 VALUES ( '212','210');
INSERT INTO TEST50 VALUES ( '110','100');
INSERT INTO TEST50 VALUES ( '220','200');
INSERT INTO TEST50 VALUES ( '221','220');
INSERT INTO TEST50 VALUES ( '100','000');
INSERT INTO TEST50 VALUES ( '111','110');
INSERT INTO TEST50 VALUES ( '120','100');
INSERT INTO TEST50 VALUES ( '121','120');
INSERT INTO TEST50 VALUES ( '122','120');
INSERT INTO TEST50 VALUES ( '112','110');
INSERT INTO TEST50 VALUES ( '211','210');
INSERT INTO TEST50 VALUES ( '999','100');
-- TEST51
CREATE TABLE TEST51 (MCODE VARCHAR2(15),MDESC VARCHAR2(30));
CREATE INDEX TEST51_I ON TEST51(MCODE);
-- TEST51 DATA
INSERT INTO TEST51 VALUES ( '999','상위는 100입니다');
INSERT INTO TEST51 VALUES ('000','최상위 입니다');
INSERT INTO TEST51 VALUES ( '222','상위는 220입니다');
INSERT INTO TEST51 VALUES ( '200','상위는 000입니다');
INSERT INTO TEST51 VALUES ( '210','상위는 200입니다');
INSERT INTO TEST51 VALUES ( '212','상위는 210입니다');
INSERT INTO TEST51 VALUES ( '110','상위는 100입니다');
INSERT INTO TEST51 VALUES ( '220','상위는 200입니다');
INSERT INTO TEST51 VALUES ( '221','상위는 220입니다');
INSERT INTO TEST51 VALUES ( '100','상위는 000입니다');
INSERT INTO TEST51 VALUES ( '111','상위는 110입니다');
INSERT INTO TEST51 VALUES ( '120','상위는 100입니다');
INSERT INTO TEST51 VALUES ( '121','상위는 120입니다');
INSERT INTO TEST51 VALUES ( '122','상위는 120입니다');
INSERT INTO TEST51 VALUES ( '112','상위는 110입니다');
INSERT INTO TEST51 VALUES ( '211','상위는 210입니다');
--
CONNECT BY 의 SORT 와 JOIN
조건.
TEST50
MCODE PCODE
--------------- ---------------
222 220
200 000
210 200
212 210
110 100
220 200
221 220
100 000
111 110
120 100
121 120
122 120
112 110
211 210
000
999 100
16 행이 선택되었습니다
TEST51
MCODE MDESC
--------------- ------------------------------
222 상위는 220입니다
200 상위는 000입니다
210 상위는 200입니다
212 상위는 210입니다
110 상위는 100입니다
220 상위는 200입니다
221 상위는 220입니다
100 상위는 000입니다
111 상위는 110입니다
120 상위는 100입니다
121 상위는 120입니다
122 상위는 120입니다
112 상위는 110입니다
211 상위는 210입니다
000 최상위 입니다
999 상위는 100입니다
두개 테이블에 각각 MCODE에 대해 INDEX가 존재한다.
주어진바와같이 입력되어있는 순서는 SORT가 되어있지 않다.
문제.
주어진 조건처럼
TEST50 TABLE은 각 코드별 자신의 상위코드를 관리하며
TEST51 TABLE은 각 코드별 명칭을 관리한다.
위의 DATA를 이용해서
LEVEL별 전개를 하여
다음과 같은 결과를 만들어내고 싶다.
LEV MCODE MDESC PCODE MDESC
---- ------ ------------------- ------ -----------------
1 100 상위는 000입니다 000 최상위 입니다
2 110 상위는 100입니다 100 상위는 000입니다
3 111 상위는 110입니다 110 상위는 100입니다
3 112 상위는 110입니다 110 상위는 100입니다
2 120 상위는 100입니다 100 상위는 000입니다
3 121 상위는 120입니다 120 상위는 100입니다
3 122 상위는 120입니다 120 상위는 100입니다
2 999 상위는 100입니다 100 상위는 000입니다
1 200 상위는 000입니다 000 최상위 입니다
2 210 상위는 200입니다 200 상위는 000입니다
3 211 상위는 210입니다 210 상위는 200입니다
3 212 상위는 210입니다 210 상위는 200입니다
2 220 상위는 200입니다 200 상위는 000입니다
3 221 상위는 220입니다 220 상위는 200입니다
3 222 상위는 220입니다 220 상위는 200입니다
즉,CONNECT BY 를 이용한 전개를 하는데 어떻게 INDEX를 사용
할 것인가 라는 문제와
다른 테이블과의 JOIN이 필요한 경우 어떤방식으로 해결
할 것인가를 동시에 묻는 문제이다.
생각.
문제에서 요구하는 것은 CONNECT BY 시의 SORT 와 JOIN 이다.
그런데 CONNECT BY 가 들어있는 QUERY 는 JOIN이 안된다.
실제로 아래와 같은 문장을 사용하면
에러가 난다.
SELECT *
FROM TEST50 A, TEST51 B
START WITH A.PCODE = '000' AND A.MCODE > ' '
CONNECT BY PRIOR A.MCODE = PCODE AND A.MCODE > ' '
ORA-01437: 결합은 CONNECT BY와 함께 지정할 수 없습니다
그렇다면 먼저 코드를 이용해 전개를 한후에
그 결과를 DATASET으로하여 JOIN을 해야 할 것이다.
즉, INLINE VIEW 안으로 CONNECT BY 가 들어가는것은 허용이
된다는 뜻이다.
INLINE VIEW 안에서 전개를 한후에 그결과 (LEVEL 까지 포함) 를
이용해 명칭을 읽어오기위한 JOIN 을 한다는 것이다.
그렇다면 코드를 이용한 전개만 제대로 한다면 명칭을 읽어오는데
있어서의 어려움은 없을 듯하다.
문제는 단순 전개를 한 후의 정렬이다.
아래와 같이 단순 전개를 하면
SELECT LEVEL,MCODE,PCODE
FROM TEST50
START WITH PCODE = '000'
CONNECT BY PRIOR MCODE = PCODE
결과가 나오는데 아래와 같이 정렬이 안된 상태이다.
LEVEL MCODE PCODE
---------- --------------- ---------------
1 200 000
2 210 200
3 212 210
3 211 210
2 220 200
3 222 220
3 221 220
1 100 000
2 110 100
3 111 110
3 112 110
2 120 100
3 121 120
3 122 120
2 999 100
ORDER BY 를 하면 될듯 싶지만 그렇게 쉽지않다.
LEVEL별로 ORDER BY를 해도 원하는 결과는 아니고,
MCODE별로 ORDER BY를 해도 원하는 결과는 아니다.
문제에서 요구하는 결과는 아래와 같지만 어떤식으로 ORDER BY를 해도
아래와 같은 결과는 나오지 않는다.
LEV MCODE PCODE
---------- --------------- ---------------
1 100 000
2 110 100
3 111 110
3 112 110
2 120 100
3 121 120
3 122 120
2 999 100
1 200 000
2 210 200
3 211 210
3 212 210
2 220 200
3 221 220
3 222 220
결국 DATA를 읽어오는 시점에서 INDEX를 이용해 SORT를 해야 한다는
결론이 나온다.
이렇게 두가지 문제만을 해결하면 원하는 결과를 도출할 수 있다.
해법.
단계1.START WITH ....CONNECT BY 의 문법은 생략하고 시작하겠다.
이미 주어진 문제를 통하여 MCODE에 INDEX 가 존재한다는 것을
알고 있다.
위에서 사용한
정렬이 안되는 전개에서부터 시작하자.
SELECT LEVEL,MCODE,PCODE
FROM TEST50
START WITH PCODE = '000'
CONNECT BY PRIOR MCODE = PCODE
전개는 되는데 정렬이 안되는 문장이다.
EXECUTION PLAN을 보면 아래와 같이 FULL TABLE SCAN을 한다.
SELECT STATEMENTCost Estimate:
CONNECT BY
TABLE ACCESSFULL:SCOTT,,TEST50(2)
TABLE ACCESSBY USER ROWID:SCOTT,,TEST50(1)
TABLE ACCESSFULL:SCOTT,,TEST50(1)
일반적으로 RULE BASE OPTIMIZER MODE 에서는
WHERE 절에 INDEX COLUMN의 조건을 주어주면 INDEX SCAN을
하게 된다.
여기서도 RULE BASE OPTIMIZER 를 가정하고 시작한다.
그렇다면 위의문장 FROM 절 다음에
WHERE MCODE > ' ' 를 추가하면 원하는 결과가 나올까?.
그렇지 않다.
INDEX SCAN을 하도록 하기위해
MCODE > ' ' 를 추가하는것은 맞는다.
하지만 그 위치가 어디인가 하는 것이다.
이번 단계에서는 START WITH 에
MCODE > ' ' 를 추가해 보자. 아래와 같은 문장을
만들어보자는 것이다.
SELECT LEVEL,MCODE,PCODE
FROM TEST50
START WITH PCODE = '000' AND MCODE > ' '
CONNECT BY PRIOR MCODE = PCODE
실행시켜 결과를 보자.
LEVEL MCODE PCODE
---------- --------------- ---------------
1 100 000
2 110 100
3 111 110
3 112 110
2 120 100
3 121 120
3 122 120
2 999 100
1 200 000
2 210 200
3 212 210
3 211 210
2 220 200
3 222 220
3 221 220
처음 결과와 달라진 부분이 있을 것이다.
일단 1레벨의 SORT 에 성공했다.
START WITH PCODE = '000' 에서 PCODE 가 '000' 인 두개의
DATA '100' 과 '200' 이 INDEX SCAN 으로 읽혀 왔다는 것을
알 수 있다.
PLAN을 보자.
SELECT STATEMENTCost Estimate:
CONNECT BY
TABLE ACCESSBY INDEX ROWID:SCOTT,,TEST50(2)
INDEXRANGE SCAN:SCOTT,,,,TEST50_I
TABLE ACCESSBY USER ROWID:SCOTT,,TEST50(1)
TABLE ACCESSFULL:SCOTT,,TEST50(1)
문제는 1레벨 이외의 레벨이다.
단계2.CONNECT BY 에 동일한 조건을 추가해보자.
QUERY:
SELECT LEVEL,MCODE,PCODE
FROM TEST50
START WITH PCODE = '000'
CONNECT BY PRIOR MCODE = PCODE AND MCODE > ' '
OUTPUT:
LEVEL MCODE PCODE
---------- --------------- ---------------
1 200 000
2 210 200
3 211 210
3 212 210
2 220 200
3 221 220
3 222 220
1 100 000
2 110 100
3 111 110
3 112 110
2 120 100
3 121 120
3 122 120
2 999 100
PLAN:
SELECT STATEMENTCost Estimate:
CONNECT BY
TABLE ACCESSFULL:SCOTT,,TEST50(2)
TABLE ACCESSBY USER ROWID:SCOTT,,TEST50(1)
TABLE ACCESSBY INDEX ROWID:SCOTT,,TEST50(1)
INDEXRANGE SCAN:SCOTT,,,,TEST50_I
결과는 단계1의 결과와 상호보완적이라는 것을 알 수 있다.
즉 START WITH 와 CONNECT BY 절에 각각 MCODE > ' '를
추가하여 INDEX SCAN을 하도록 도와야 한다는 것이다.
QUERY:
SELECT LEVEL,MCODE,PCODE
FROM TEST50
START WITH PCODE = '000' AND MCODE > ' '
CONNECT BY PRIOR MCODE = PCODE AND MCODE > ' '
OUTPUT:
LEVEL MCODE PCODE
---------- --------------- ---------------
1 100 000
2 110 100
3 111 110
3 112 110
2 120 100
3 121 120
3 122 120
2 999 100
1 200 000
2 210 200
3 211 210
3 212 210
2 220 200
3 221 220
3 222 220
PLAN:
SELECT STATEMENTCost Estimate:
CONNECT BY
TABLE ACCESSBY INDEX ROWID:SCOTT,,TEST50(2)
INDEXRANGE SCAN:SCOTT,,,,TEST50_I
TABLE ACCESSBY USER ROWID:SCOTT,,TEST50(1)
TABLE ACCESSBY INDEX ROWID:SCOTT,,TEST50(1)
INDEXRANGE SCAN:SCOTT,,,,TEST50_I
단계3.이제 전개된 CODE 를 이용해 명칭을 가져오자.
이때 INLINE VIEW 안에서 사용된 LEVEL에 ALIAS 를 주어서
ERROR를 방지하는 부분만 주의하면 평이한 JOIN 이 될 것이다.
QUERY:
SELECT A.LEV,
A.MCODE,B.MDESC,
A.PCODE,C.MDESC
FROM (SELECT LEVEL LEV,MCODE,PCODE
FROM TEST50
START WITH PCODE = '000' AND MCODE > ' '
CONNECT BY PRIOR MCODE=PCODE AND MCODE > ' ') A,
TEST51 B,
TEST51 C
WHERE B.MCODE = A.MCODE
AND C.MCODE = A.PCODE
OUTPUT:
LEV MCODE MDESC PCODE MDESC
---- ------ ------------------- ------ -----------------
1 100 상위는 000입니다 000 최상위 입니다
2 110 상위는 100입니다 100 상위는 000입니다
3 111 상위는 110입니다 110 상위는 100입니다
3 112 상위는 110입니다 110 상위는 100입니다
2 120 상위는 100입니다 100 상위는 000입니다
3 121 상위는 120입니다 120 상위는 100입니다
3 122 상위는 120입니다 120 상위는 100입니다
2 999 상위는 100입니다 100 상위는 000입니다
1 200 상위는 000입니다 000 최상위 입니다
2 210 상위는 200입니다 200 상위는 000입니다
3 211 상위는 210입니다 210 상위는 200입니다
3 212 상위는 210입니다 210 상위는 200입니다
2 220 상위는 200입니다 200 상위는 000입니다
3 221 상위는 220입니다 220 상위는 200입니다
3 222 상위는 220입니다 220 상위는 200입니다
PLAN:
SELECT STATEMENTCost Estimate:
NESTED LOOPS
NESTED LOOPS
VIEW(1)
CONNECT BY
TABLE ACCESSBY INDEX ROWID:SCOTT,,TEST50(3)
INDEXRANGE SCAN:SCOTT,,,,TEST50_I
TABLE ACCESSBY USER ROWID:SCOTT,,TEST50(2)
TABLE ACCESSBY INDEX ROWID:SCOTT,,TEST50(2)
INDEXRANGE SCAN:SCOTT,,,,TEST50_I
TABLE ACCESSBY INDEX ROWID:SCOTT,,TEST51(5)
INDEXRANGE SCAN:SCOTT,,,,TEST51_I
TABLE ACCESSBY INDEX ROWID:SCOTT,,TEST51(4)
INDEXRANGE SCAN:SCOTT,,,,TEST51_I
