[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초로 바뀌는 쾌거를 이루었다!!! 