-- 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