SQL을 작성할 때 특정한 순서대로 TABLE이 액세스 되기를 원하여

그 순서대로 인라인뷰로 묶어준다고 해도 실제 수행시 옵티마이저는 인라인뷰로 묶인 순서대로 수행하는 것이 아니라 SQL을 REWIRTE 하고 최적이라고 판단되는 순서로 수행을 합니다.

(물론 ROWNUM 사용이나 ORDERED, NO_MERGE 등 여러 힌트를 사용하여 원하는 순서대로 강제할 수도 있습니다)

질문하신 경우는 아마도 근본적으로 인라인뷰의 문제라기 보다는

CONNECT BY 쿼리의 문제라고 보여집니다.

일반적인 SQL 에서 WHERE 절에 있는 상수조건이 드라이빙 조건이 되지만

CONNECT BY 쿼리에서는 조금 다르게 수행이 됩니다.

※ CONNECT BY 쿼리는 아래와 같은 순서로 수행이 됩니다.

SELECT  *

FROM    ORGANIZATION

WHERE   ALIVE_DATE >= SYSDATE          --> 3. 필터링 조건 (드라이빙 조건이 아님)

START WITH PARENT_DEPARTMENT_ID IS NULL                  --> 1. 드라이빙 조건

CONNECT BY PRIOR DEPARTMENT_ID = PARENT_DEPARTMENT_ID;   --> 2. 조인 조건

그런데 경우에 따라 CONNECT BY 쿼리의 수행결과가 원하는 결과와

다른 경우가 발생할 수 있습니다.

예를 들어 보겠습니다.

CREATE TABLE ORGANIZATION

(

 DEPARTMENT_ID        NUMBER,

 ALIVE_DATE           DATE,

 DEPARTMENT_NAME      VARCHAR2(30),

 PARENT_DEPARTMENT_ID NUMBER

);

ALTER TABLE ORGANIZATION

ADD CONSTRAINTS ORGANIZATION_PK PRIMARY KEY (DEPARTMENT_ID, ALIVE_DATE)

USING INDEX NOLOGGING;

INSERT INTO ORGANIZATION

VALUES (1, TO_DATE('99991231','YYYYMMDD'), '엔코아컨설팅', NULL);

INSERT INTO ORGANIZATION

VALUES (2, TO_DATE('20091231','YYYYMMDD'), '컨설팅본부(OLD)', 1);

INSERT INTO ORGANIZATION

VALUES (3, TO_DATE('99991231','YYYYMMDD'), '1사업부(OLD)', 2);

INSERT INTO ORGANIZATION

VALUES (4, TO_DATE('99991231','YYYYMMDD'), '2사업부(OLD)', 2);

INSERT INTO ORGANIZATION

VALUES (5, TO_DATE('99991231','YYYYMMDD'), '컨설팅사업부(NEW)', 1);

INSERT INTO ORGANIZATION

VALUES (6, TO_DATE('99991231','YYYYMMDD'), '1사업부(NEW)', 5);

INSERT INTO ORGANIZATION

VALUES (7, TO_DATE('99991231','YYYYMMDD'), '2사업부(NEW)', 5);

엔코아컨설팅이란 회사에서 2010년에 조직개편으로 인해 기존 조직을

INVALID 처리하고 새로운 조직코드를 생성했습니다.

-- OLD 조직 구성

(1) 엔코아컨설팅 ---> (2) 컨설팅본부(OLD) ---> (3) 1사업부(OLD)

                                          |

                                          ---> (4) 2사업부(OLD)

-- NEW 조직 구성

(1) 엔코아컨설팅 ---> (5) 컨설팅본부(NEW) ---> (6) 1사업부(NEW)

                                          |

                                          ---> (7) 2사업부(NEW)

억지로(?) 상황을 만들기 위해서 OLD 조직인

"1사업부(OLD)", "2사업부(OLD)" 의 ALIVE_DATE 를 '20091231' 로 설정하지 않고

상위 조직인 "컨설팅본부(OLD)" 만 '20091231' 로 설정하여 INVALID 처리를

했습니다.

1. SQL 테스트 1

SELECT  *

FROM    ORGANIZATION

WHERE   ALIVE_DATE >= SYSDATE

START WITH PARENT_DEPARTMENT_ID IS NULL

CONNECT BY PRIOR DEPARTMENT_ID = PARENT_DEPARTMENT_ID;

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);

-------------------------------------------------------------------------------------------

| Id  | Operation                  | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |              |     1 |    47 |     2   (0)| 00:00:01 |

|*  1 |  FILTER                    |              |       |       |            |          |

|*  2 |   CONNECT BY WITH FILTERING|              |       |       |            |          |

|*  3 |    FILTER                  |              |       |       |            |          |

|   4 |     TABLE ACCESS FULL      | ORGANIZATION |     1 |    47 |     2   (0)| 00:00:01 |

|*  5 |    HASH JOIN               |              |       |       |            |          |

|   6 |     CONNECT BY PUMP        |              |       |       |            |          |

|   7 |     TABLE ACCESS FULL      | ORGANIZATION |     1 |    47 |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("ALIVE_DATE">=SYSDATE@!)

   2 - access("PARENT_DEPARTMENT_ID"=PRIOR "DEPARTMENT_ID")

   3 - filter("PARENT_DEPARTMENT_ID" IS NULL)

   5 - access("PARENT_DEPARTMENT_ID"=PRIOR "DEPARTMENT_ID")

-- 수행결과

Department_id   Alive_date    Department_name     Parent_department_id

----------------------------------------------------------------------

1               12/31/9999    엔코아컨설팅        

3               12/31/9999    1사업부(OLD)        2

4               12/31/9999    2사업부(OLD)        2

5               12/31/9999    컨설팅사업부(NEW)   1

6               12/31/9999    1사업부(NEW)        5

7               12/31/9999    2사업부(NEW)        5

1번 SQL의 실행계획을 보면, "PARENT_DEPARTMENT_ID IS NULL" 조건으로

드라이빙 되어 CONNECT BY 처리가 모두 이루어진 후에 마지막에

"ALIVE_DATE >= SYSDATE" 가 적용된 것을 알 수 있습니다.  

이 부분 확인  -->  1 - filter("ALIVE_DATE">=SYSDATE@!)

이렇게 처리되다 보니 원하지 않는 "1사업부(OLD)", "2사업부(OLD)"

조직까지 SELECT 가 되고 있습니다.

2. SQL 테스트 2

SELECT  *

FROM   (SELECT  *

        FROM    ORGANIZATION

        WHERE   ALIVE_DATE >= SYSDATE

       )

START WITH PARENT_DEPARTMENT_ID IS NULL

CONNECT BY PRIOR DEPARTMENT_ID = PARENT_DEPARTMENT_ID;

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);

------------------------------------------------------------------------------------------

| Id  | Operation                 | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |              |     1 |    47 |     2   (0)| 00:00:01 |

|*  1 |  CONNECT BY WITH FILTERING|              |       |       |            |          |

|*  2 |   FILTER                  |              |       |       |            |          |

|*  3 |    TABLE ACCESS FULL      | ORGANIZATION |     1 |    47 |     2   (0)| 00:00:01 |

|*  4 |   HASH JOIN               |              |       |       |            |          |

|   5 |    CONNECT BY PUMP        |              |       |       |            |          |

|*  6 |    TABLE ACCESS FULL      | ORGANIZATION |     1 |    47 |     2   (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("ORGANIZATION"."PARENT_DEPARTMENT_ID"=PRIOR

              "ORGANIZATION"."DEPARTMENT_ID")

   2 - filter("ORGANIZATION"."PARENT_DEPARTMENT_ID" IS NULL)

   3 - filter("ALIVE_DATE">=SYSDATE@!)

   4 - access("ORGANIZATION"."PARENT_DEPARTMENT_ID"=PRIOR

              "ORGANIZATION"."DEPARTMENT_ID")

   6 - filter("ALIVE_DATE">=SYSDATE@!)

-- 수행결과

Department_id   Alive_date    Department_name     Parent_department_id

----------------------------------------------------------------------

1               12/31/9999    엔코아컨설팅        

5               12/31/9999    컨설팅사업부(NEW)   1

6               12/31/9999    1사업부(NEW)        5

7               12/31/9999    2사업부(NEW)        5

2번 SQL의 실행계획을 보면,

CONNECT BY 처리를 하기 전에 먼저 "ALIVE_DATE >= SYSDATE" 조건에 맞는 데이터를 걸러 낸 후에 그 결과 내에서 CONNECT BY 가 이루졌습니다.

이 부분 확인  -->  3 - filter("ALIVE_DATE">=SYSDATE@!)

그리하여 "1사업부(OLD)", "2사업부(OLD)" 조직이 빠지고 원하는 새로운 조직만 SELECT 가 되었습니다.

PREDICATE 정보를 포함한 정확한 실행계획이나 데이터 샘플이 없어서

남택승님께서 겪으신 상황을 정확히 알 수 없지만

혹시 위와 같이 CONNECT BY 쿼리의 문제가 아닌지 한번 확인해 보시기 바랍니다.