[Oracle] 오라클 SQL 튜닝, 최종 출력 건에 대해서만 JOIN, 스칼라 서브 쿼리

주문번호를 내림차순으로 최근 주문데이터 10건 출력하기

* 결과값

* 쿼리1

SELECT OI2.ORD_NO,

            O.BRANCH_CD,

            OI2.ORD_DT,

            OI2.ORD_HMS,

            OI2.ITEM_ID,

            I.ITEM_NM,

            OI2.ORD_ITEM_QTY

  FROM 

  (

    SELECT RID

      FROM (

        SELECT ROW_NUMBER() OVER(ORDER BY ORD_NO DESC) RNO,

                    ROWID RID

          FROM ORD_ITEM

      ) OI 

    WHERE OI.RNO <= 10

  ) OI

      LEFT OUTER JOIN ORD_ITEM OI2

        ON OI.RID = OI2.ROWID

      LEFT OUTER JOIN ORD O

        ON OI2.ORD_NO = O.ORD_NO

     LEFT OUTER JOIN ITEM I

       ON OI2.ITEM_ID = I.ITEM_ID

 ORDER BY OI2.ORD_NO DESC;

* 실행계획

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

| Id  | Operation                        | Name        | Starts | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT                 |             |      1 |     10 |00:00:09.45 |     340K|

|   1 |  SORT ORDER BY                   |             |      1 |     10 |00:00:09.45 |     340K|

|*  2 |   HASH JOIN RIGHT OUTER          |             |      1 |     10 |00:00:09.45 |     340K|

|   3 |    TABLE ACCESS FULL             | ITEM        |      1 |     92 |00:00:00.01 |       7 |

|*  4 |    HASH JOIN RIGHT OUTER         |             |      1 |     10 |00:00:09.45 |     340K|

|   5 |     TABLE ACCESS FULL            | ORD         |      1 |     15M|00:00:02.96 |     340K|

|   6 |     NESTED LOOPS OUTER           |             |      1 |     10 |00:00:00.01 |       4 |

|*  7 |      VIEW                        |             |      1 |     10 |00:00:00.01 |       3 |

|*  8 |       WINDOW NOSORT STOPKEY      |             |      1 |     10 |00:00:00.01 |       3 |

|   9 |        INDEX FULL SCAN DESCENDING| ORD_ITEM_PK |      1 |     11 |00:00:00.01 |       3 |

|  10 |      TABLE ACCESS BY USER ROWID  | ORD_ITEM    |     10 |     10 |00:00:00.01 |       1 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - access("OI2"."ITEM_ID"="I"."ITEM_ID")

   4 - access("OI2"."ORD_NO"="O"."ORD_NO")

   7 - filter("OI"."RNO"<=10)

   8 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORD_NO") DESC )<=10)

※ 1500만건에 달하는 ORD 테이블을 FULL SCAN 함으로써 불필요한 IO 발생

* 쿼리2

SELECT OI2.ORD_NO,

            (SELECT O.BRANCH_CD FROM ORD O WHERE O.ORD_NO = OI2.ORD_NO) BRANCH_CD,

            OI2.ORD_DT,

            OI2.ORD_HMS,

            OI2.ITEM_ID,

            I.ITEM_NM,

            OI2.ORD_ITEM_QTY

  FROM 

  (

    SELECT RID

      FROM (

        SELECT ROW_NUMBER() OVER(ORDER BY ORD_NO DESC) RNO,

                    ROWID RID

          FROM ORD_ITEM

      ) OI 

    WHERE OI.RNO <= 10

  ) OI

     LEFT OUTER JOIN ORD_ITEM OI2

       ON OI.RID = OI2.ROWID

     LEFT OUTER JOIN ITEM I

       ON OI2.ITEM_ID = I.ITEM_ID

 ORDER BY OI2.ORD_NO DESC;

* 실행계획

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

| Id  | Operation                       | Name        | Starts | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT                |             |      1 |     10 |00:00:00.01 |      28 |

|   1 |  TABLE ACCESS BY INDEX ROWID    | ORD         |      5 |      5 |00:00:00.01 |      17 |

|*  2 |   INDEX UNIQUE SCAN             | ORD_PK      |      5 |      5 |00:00:00.01 |      12 |

|   3 |  SORT ORDER BY                  |             |      1 |     10 |00:00:00.01 |      28 |

|*  4 |   HASH JOIN RIGHT OUTER         |             |      1 |     10 |00:00:00.01 |      11 |

|   5 |    TABLE ACCESS FULL            | ITEM        |      1 |     92 |00:00:00.01 |       7 |

|   6 |    NESTED LOOPS OUTER           |             |      1 |     10 |00:00:00.01 |       4 |

|*  7 |     VIEW                        |             |      1 |     10 |00:00:00.01 |       3 |

|*  8 |      WINDOW NOSORT STOPKEY      |             |      1 |     10 |00:00:00.01 |       3 |

|   9 |       INDEX FULL SCAN DESCENDING| ORD_ITEM_PK |      1 |     11 |00:00:00.01 |       3 |

|  10 |     TABLE ACCESS BY USER ROWID  | ORD_ITEM    |     10 |     10 |00:00:00.01 |       1 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - access("O"."ORD_NO"=:B1)

   4 - access("OI2"."ITEM_ID"="I"."ITEM_ID")

   7 - filter("OI"."RNO"<=10)

   8 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORD_NO") DESC )<=10) 

※ ORD 테이블의 PK 인덱스를 이용하여 스칼라 서브쿼리 JOIN, 같은 ORD_NO에 해당하는 결과 건수가 한 건 이상인 경우 캐싱 효과까지 이용할 수 있음

☑ 스칼라 서브쿼리 자세히 알아보기

오예~ 9초대였던 쿼리가 0.01초로 바뀌는 쾌거를 이루었다!!!