DATOR


최종값 식별방안-2 DBMS


ROWID조인을 이용하는방법

SELECT  /*+ORDERED USE_NL(B)*/

        A.EMPLOYEE_ID

      , A.FIRST_NAME

      , A.LAST_NAME

      , B.WORK_DATE

      , B.OVER_TIME

      , B.OVER_PAY

FROM    EMP A

      , EMP_OT_PAY B

WHERE  A.MANAGER_ID=100

AND    A.EMPLOYEE_ID=B.EMPLOYEE_ID

AND    B.ROWID = (SELECT  /*+INDEX_DESC(C EMP_OT_PAY_PK)*/

                                       C.ROWID

                            FROM   EMP_OT_PAY C

                            WHERE  C.EMPLOYEE_ID=A.EMPLOYEE_ID

                            AND     C.WORK_DATE <= '20030818'

                                AND     ROWNUM=1)

 

call         count         cpu    elapsed        disk       query    current        rows

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

Parse            1        0.00       0.00           0           0          0           0

Execute          1        0.00       0.00           0           0          0           0

Fetch            2        0.02       0.00           0          34          0           3

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

Total            4        0.02       0.00           0          34          0           3

 

 

Rows     Row Source Operation

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

      3  NESTED LOOPS  (cr=34 pr=0 pw=0 time=0 us cost=16 size=58800 card=1400)

     14   TABLE ACCESS BY INDEX ROWID EMP (cr=4 pr=0 pw=0 time=91 us cost=2 size=322 card=14)

     14    INDEX RANGE SCAN EMP_IDX01 (cr=2 pr=0 pw=0 time=26 us cost=1 size=0 card=14)(object id 77765)

      3   TABLE ACCESS BY USER ROWID EMP_OT_PAY (cr=30 pr=0 pw=0 time=0 us cost=1 size=1900 card=100)

      3    COUNT STOPKEY (cr=28 pr=0 pw=0 time=0 us)

      3     INDEX RANGE SCAN DESCENDING EMP_OT_PAY_PK (cr=28 pr=0 pw=0 time=0 us cost=2 size=19 card=1)(object id 77763)

 

 

SQL상에서는 EMP_OT_PAY 테이블을 두번 읽는 것처럼 보이지만 실행계획을 보면 EMP_OT_PAY테이블을 단 한번만 읽는 것을 확인 할 수 있다. 자세히 살펴보면 TABLE ACCESS BY USER ROWID 실행계획을 확인 할 수 있는데, 메인쿼리와 서브쿼리가 ROWID을 이용하여 조인하면서 나타난 실행계획이다. 또한, 서브쿼리 내부에서는 ROWID INDEX구성컬럼만 이용하였으므로 TABLE ACCESS없이 INDEX만 읽어도 가능하기 때문에 테이블을 한번읽는 효과를 발휘한 것이다.

(확실한 ROWID 조인을 실행하기 위해 ROWID(B)힌트를 추가 할 수 있다.)

 

아래의 SQL Select절에서 서브쿼리를 이용해서 ROWID를 구하고, 이를 다시 View로 감싼 후 EMP_OT_PAY테이블과 ROWID를 이용해서 조인하는 방법으로, Where절에서 서브쿼리를 이용하는 방법과 동일한 실행계획을 보여준다.

 

SELECT  /*+ORDERED USE_NL(B)*/

            A.EMPLOYEE_ID

          , A.FIRST_NAME

          , A.LAST_NAME

         , B.WORK_DATE

          , B.OVER_TIME

          , B.OVER_PAY

FROM   (SELECT  A.EMPLOYEE_ID

                      , A.FIRST_NAME

                      , A.LAST_NAME

                      ,(SELECT  /*+INDEX_DESC(B EMP_OT_PAY_PK)*/

                                   B.ROWID

                       FROM    EMP_OT_PAY B

                       WHERE  B.EMPLOYEE_ID=A.EMPLOYEE_ID

                       AND     B.WORK_DATE <= '20030818'

                       AND     ROWNUM=1) AS ROW_ID

             FROM  EMP A

             WHERE A.MANAGER_ID=100) A

           , EMP_OT_PAY B

WHERE   B.ROWID=A.ROW_ID

 

 

call         count         cpu    elapsed        disk       query    current        rows

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

Parse            1        0.00       0.00           0           0          0           0

Execute          1        0.00       0.00           0           0          0           0

Fetch            2        0.00       0.00           0          34          0           3

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

Total            4        0.00       0.00           0          34          0           3

 

 

Rows     Row Source Operation

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

      3  NESTED LOOPS  (cr=34 pr=0 pw=0 time=0 us cost=16 size=6291600 card=149800)

     14   TABLE ACCESS BY INDEX ROWID EMP (cr=4 pr=0 pw=0 time=143 us cost=2 size=322 card=14)

     14    INDEX RANGE SCAN EMP_IDX01 (cr=2 pr=0 pw=0 time=52 us cost=1 size=0 card=14)(object id 77765)

      3   TABLE ACCESS BY USER ROWID EMP_OT_PAY (cr=30 pr=0 pw=0 time=0 us cost=1 size=203300 card=10700)

      3    COUNT STOPKEY (cr=28 pr=0 pw=0 time=0 us)

      3     INDEX RANGE SCAN DESCENDING EMP_OT_PAY_PK (cr=28 pr=0 pw=0 time=0 us cost=2 size=19 card=1)(object id 77763)

 

이제부터는 INDEX_DESC힌트+ROWNUM=1 방법을 이용할 때 주의 할 점 한가지를 이야기 하고자 한다. 이 방법은 위에서 설명 했듯이 “INDEX를 구성하는 컬럼이 정렬되어 있다.”는 특성을 전제로 하기 때문에 INDEX가 삭제되거나 INDEX의 구성컬럼이 변경되었을 경우 최초 구성컬럼의 구조가 변질 되기 때문에 SQL 작성 시점의 의도했던 값을 가져 올 수 없는 경우가 발생한다. 그러므로, 운영중에 INDEX의 변경 및 삭제는 다각도의 영향도 파악이 필요하고, 매우 신중을 기해야 한다.

아래와 같이 SQL을 작성하면 INDEX의 변경 및 삭제 시 성능상 문제는 발생 할 수 있지만 INDEX변화에 관계없이 동일한 값을 가져올 수 있다.

 

 

분석함수를 이용하는 방법

 

만약, EMP테이블에서 조회되는 직원의 데이터가 다량인 경우에는 위에서 기술한 서브쿼리 방법으로는 Random Access가 증가하는 비효율이 발생 할 수 있다.  이런한 경우에는 분석함수(ROW_NUMBER())을 이용한 HASH조인 방법으로 SQL을 작성하는게 효율적이다. 다음은 분석함수(ROW_NUMBER())을 이용한 HASH조인 방법의 SQL이다.

 

SELECT  A.EMPLOYEE_ID

   , A.FIRST_NAME

   , A.LAST_NAME

   , B.WORK_DATE

   , B.OVER_TIME

      , B.OVER_PAY

FROM    EMP A

      ,(SELECT  EMPLOYEE_ID

                     , WORK_DATE

                     , OVER_TIME

                     , OVER_PAY

                     , ROW_NUMBER()OVER(PARTITION BY EMPLOYEE_ID

ORDER BY WORK_DATE DESC) AS RNUM

           FROM    EMP_OT_PAY

           WHERE   WORK_DATE <= '20030818') B

WHERE   A.MANAGER_ID=100

AND     A.EMPLOYEE_ID=B.EMPLOYEE_ID

AND     B.RNUM=1

 

call         count         cpu    elapsed        disk       query    current        rows

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

Parse            1        0.00       0.00           0           0          0           0

Execute          1        0.00       0.00           0           0          0           0

Fetch            2        0.00       0.01           0          41          0           3

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

Total            4        0.00       0.01           0          41          0           3

 

 

Rows     Row Source Operation

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

      3  HASH JOIN  (cr=41 pr=0 pw=0 time=0 us cost=17 size=11259 card=139)

     14   TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=221 us cost=2 size=322 card=14)

     14    INDEX RANGE SCAN EMP_IDX01 (cr=1 pr=0 pw=0 time=52 us cost=1 size=0 card=14)(object id 77765)

     12   VIEW  (cr=39 pr=0 pw=0 time=1408 us cost=14 size=61712 card=1064)

   1089    WINDOW SORT PUSHED RANK (cr=39 pr=0 pw=0 time=4956 us cost=14 size=20216 card=1064)

   1089     TABLE ACCESS FULL EMP_OT_PAY (cr=39 pr=0 pw=0 time=5440 us cost=13 size=20216 card=1064)

 

 

=====================================================================

<이후 추가기술영역>

최상위 값이 1개이상인 경우방법.

Max()over()를 이용하는 방식(문제점 기술)

SELECT  A.EMPLOYEE_ID

          , A.FIRST_NAME

          , A.LAST_NAME

          , B.WORK_DATE

          , B.OVER_TIME

          , B.OVER_PAY

FROM    EMP A

(SELECT   EMPLOYEE_ID

                     , WORK_DATE

                     , OVER_TIME

                    , OVER_PAY

                    , MAX(WORK_DATE)

OVER(PARTITION BY EMPLOYEE_ID) AS MAX_WORK_DATE

FROM    EMP_OT_PAY

WHERE   WORK_DATE <= '20030818') B

WHERE  A.MANAGER_ID=100

AND    A.EMPLOYEE_ID=B.EMPLOYEE_ID

AND    B.WORK_DATE=B.MAX_WORK_DATE

 

 

call         count         cpu    elapsed        disk       query    current        rows

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

Parse            1        0.00       0.00           0           0          0           0

Execute          1        0.00       0.00           0           0          0           0

Fetch            2        0.02       0.01           0          41          0           3

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

Total            4        0.02       0.01           0          41          0           3

 

Misses in library cache during parse: 0

Optimizer goal: All_Rows

Parsing user id: 92  (GAGAMEL)

 

Rows     Row Source Operation

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

      3  HASH JOIN  (cr=41 pr=0 pw=0 time=0 us cost=17 size=10286 card=139)

     14   TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=221 us cost=2 size=322 card=14)

     14    INDEX RANGE SCAN EMP_IDX01 (cr=1 pr=0 pw=0 time=52 us cost=1 size=0 card=14)(object id 77765)

     12   VIEW  (cr=39 pr=0 pw=0 time=2002 us cost=14 size=54264 card=1064)

   1089    WINDOW SORT (cr=39 pr=0 pw=0 time=5802 us cost=14 size=20216 card=1064)

   1089     TABLE ACCESS FULL EMP_OT_PAY (cr=39 pr=0 pw=0 time=5319 us cost=13 size=20216 card=1064)

 

Rank()over()를 이용하는 방식

SELECT  A.EMPLOYEE_ID

   , A.FIRST_NAME

   , A.LAST_NAME

   , B.WORK_DATE

   , B.OVER_TIME

      , B.OVER_PAY

FROM    EMP A

      ,(SELECT  EMPLOYEE_ID

                     , WORK_DATE

                     , OVER_TIME

                     , OVER_PAY

                     , RANK()OVER(PARTITION BY EMPLOYEE_ID

ORDER BY WORK_DATE DESC) AS RNUM

           FROM    EMP_OT_PAY

           WHERE   WORK_DATE <= '20030818') B

WHERE   A.MANAGER_ID=100

AND     A.EMPLOYEE_ID=B.EMPLOYEE_ID

AND     B.RNUM=1

 

 

call         count         cpu    elapsed        disk       query    current        rows

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

Parse            1        0.00       0.00           0           0          0           0

Execute          1        0.00       0.00           0           0          0           0

Fetch            2        0.00       0.00           0          41          0           3

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

Total            4        0.00       0.00           0          41          0           3

 

 

Rows     Row Source Operation

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

      3  HASH JOIN  (cr=41 pr=0 pw=0 time=0 us cost=17 size=11259 card=139)

     14   TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=130 us cost=2 size=322 card=14)

     14    INDEX RANGE SCAN EMP_IDX01 (cr=1 pr=0 pw=0 time=26 us cost=1 size=0 card=14)(object id 77765)

     12   VIEW  (cr=39 pr=0 pw=0 time=462 us cost=14 size=61712 card=1064)

   1089    WINDOW SORT PUSHED RANK (cr=39 pr=0 pw=0 time=2296 us cost=14 size=20216 card=1064)

   1089     TABLE ACCESS FULL EMP_OT_PAY (cr=39 pr=0 pw=0 time=2901 us cost=13 size=20216 card=1064)

Tag :

Leave Comments