조인이 포함된 Top-N 쿼리를 살펴보자.
테스트를 위해 아래와 같이 테이블을 생성하자.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1, 'X' AS c2 FROM XMLTABLE ('1 to 20');
CREATE TABLE t2 AS SELECT ROWNUM + 10 AS c1, 'X' AS c2 FROM XMLTABLE ('1 to 5');
CREATE INDEX t1_x1 on t1 (c1);
CREATE INDEX t2_x1 on t2 (c1);
아래는 조인이 포함된 Top-N 쿼리다. 많이 사용되는 패턴이지만 NL 조인이 NLJ_BATCHING 방식으로 수행되거나, 테이블이 BATCH_TABLE_ACCESS_BY_ROWID 방식으로 액세스되는 경우 정렬이 보장되지 않을 수 있다.
-- 2
SELECT /*+ ORDERED USE_NL(B) */
*
FROM (SELECT a.*
FROM t1 a
WHERE a.c1 > 0
ORDER BY a.c1) a
, t2 b
WHERE ROWNUM <= 5
AND b.c1 = a.c1;
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 11 |
|* 1 | COUNT STOPKEY | | 1 | 5 | 11 |
| 2 | NESTED LOOPS | | 1 | 5 | 11 |
| 3 | NESTED LOOPS | | 1 | 5 | 9 |
| 4 | VIEW | | 1 | 15 | 4 |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 15 | 4 |
|* 6 | INDEX RANGE SCAN | T1_X1 | 1 | 15 | 2 |
|* 7 | INDEX RANGE SCAN | T2_X1 | 15 | 5 | 5 |
| 8 | TABLE ACCESS BY INDEX ROWID | T2 | 5 | 5 | 2 |
-----------------------------------------------------------------------------
정렬을 보장하기 위해 아래처럼 힌트를 추가할 수 있다.
-- 3
SELECT /*+ ORDERED USE_NL(B) NO_NLJ_BATCHING(B) NO_BATCH_TABLE_ACCESS_BY_ROWID(B) */
*
FROM (SELECT a.*
FROM t1 a
WHERE a.c1 > 0
ORDER BY a.c1) a
, t2 b
WHERE ROWNUM <= 5
AND b.c1 = a.c1;
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 11 |
|* 1 | COUNT STOPKEY | | 1 | 5 | 11 |
| 2 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 5 | 11 |
| 3 | NESTED LOOPS | | 1 | 20 | 9 |
| 4 | VIEW | | 1 | 15 | 4 |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 15 | 4 |
|* 6 | INDEX RANGE SCAN | T1_X1 | 1 | 15 | 2 |
|* 7 | INDEX RANGE SCAN | T2_X1 | 15 | 5 | 5 |
-----------------------------------------------------------------------------
좀 더 확실한 방법은 ORDER BY 절을 추가하는 것이다. ORDER BY 절을 추가했지만 소트가 발생하지 않은 것을 확인할 수 있다.
-- 4
SELECT *
FROM (SELECT a.*
FROM t1 a
WHERE a.c1 > 0
ORDER BY a.c1) a
, t2 b
WHERE ROWNUM <= 5
AND b.c1 = a.c1
ORDER BY a.c1;
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 11 |
|* 1 | COUNT STOPKEY | | 1 | 5 | 11 |
| 2 | NESTED LOOPS | | 1 | 5 | 11 |
| 3 | NESTED LOOPS | | 1 | 5 | 9 |
| 4 | VIEW | | 1 | 15 | 4 |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 15 | 4 |
|* 6 | INDEX RANGE SCAN | T1_X1 | 1 | 15 | 2 |
|* 7 | INDEX RANGE SCAN | T2_X1 | 15 | 5 | 5 |
| 8 | TABLE ACCESS BY INDEX ROWID | T2 | 5 | 5 | 2 |
-----------------------------------------------------------------------------
아래와 같이 쿼리를 작성하면 결과가 달라질 수 있다.
-- 5
SELECT /*+ ORDERED USE_NL(B) */
*
FROM (SELECT a.*
FROM (SELECT *
FROM t1 a
WHERE a.c1 > 0
ORDER BY a.c1) a
WHERE ROWNUM <= 5) a
, t2 b
WHERE b.c1 = a.c1;
------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 0 | 4 |
| 1 | NESTED LOOPS | | 1 | 0 | 4 |
| 2 | NESTED LOOPS | | 1 | 0 | 4 |
| 3 | VIEW | | 1 | 5 | 2 |
|* 4 | COUNT STOPKEY | | 1 | 5 | 2 |
| 5 | VIEW | | 1 | 5 | 2 |
| 6 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 5 | 2 |
|* 7 | INDEX RANGE SCAN | T1_X1 | 1 | 5 | 1 |
|* 8 | INDEX RANGE SCAN | T2_X1 | 5 | 0 | 2 |
| 9 | TABLE ACCESS BY INDEX ROWID | T2 | 0 | 0 | 0 |
------------------------------------------------------------------------------
t2 테이블에 대한 조인 횟수가 많은 경우 랜덤 액세스 감소를 위해 서브 쿼리를 추가하기도 한다. 인덱스만으로 필터링할 수 있을 경우 적용할 수 있는 기법이다.
-- 6
SELECT /*+ ORDERED USE_NL(B) */
*
FROM (SELECT a.*
FROM (SELECT a.*
FROM t1 a
WHERE a.c1 > 0
AND EXISTS (SELECT 1
FROM t2 x
WHERE x.c1 = a.c1)
ORDER BY c1) a
WHERE ROWNUM <= 5) a
, t2 b
WHERE b.c1 = a.c1;
-------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 13 |
| 1 | NESTED LOOPS | | 1 | 5 | 13 |
| 2 | NESTED LOOPS | | 1 | 5 | 11 |
| 3 | VIEW | | 1 | 5 | 8 |
|* 4 | COUNT STOPKEY | | 1 | 5 | 8 |
| 5 | VIEW | | 1 | 5 | 8 |
| 6 | NESTED LOOPS SEMI | | 1 | 5 | 8 |
| 7 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 15 | 4 |
|* 8 | INDEX RANGE SCAN | T1_X1 | 1 | 15 | 2 |
|* 9 | INDEX RANGE SCAN | T2_X1 | 15 | 5 | 4 |
|* 10 | INDEX RANGE SCAN | T2_X1 | 5 | 5 | 3 |
| 11 | TABLE ACCESS BY INDEX ROWID | T2 | 5 | 5 | 2 |
-------------------------------------------------------------------------------
t1과 t2의 조인차수는 1:1 또는 M:1이므로 서브 쿼리보다 조인 후 ROWID를 사용하는 편이 효율적이다.
-- 7
SELECT /*+ ORDERED USE_NL(B) */
*
FROM (SELECT a.*
FROM (SELECT a.*
, b.ROWID AS rid
FROM t1 a
, t2 b
WHERE a.c1 > 0
AND b.c1 = a.c1
ORDER BY a.c1) a
WHERE ROWNUM <= 5) a
, t2 b
WHERE b.ROWID = a.rid;
------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 11 |
| 1 | NESTED LOOPS | | 1 | 5 | 11 |
| 2 | VIEW | | 1 | 5 | 9 |
|* 3 | COUNT STOPKEY | | 1 | 5 | 9 |
| 4 | VIEW | | 1 | 5 | 9 |
| 5 | NESTED LOOPS | | 1 | 5 | 9 |
| 6 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 15 | 4 |
|* 7 | INDEX RANGE SCAN | T1_X1 | 1 | 15 | 2 |
|* 8 | INDEX RANGE SCAN | T2_X1 | 15 | 5 | 5 |
| 9 | TABLE ACCESS BY USER ROWID | T2 | 5 | 5 | 2 |
------------------------------------------------------------------------------
t2 테이블의 크기가 작다면 소트 머지 조인도 가능하다. t1 테이블을 15행만 액세스한 것을 확인할 수 있다.
-- 8
SELECT /*+ LEADING(A) USE_MERGE(B) */
*
FROM (SELECT a.*
FROM t1 a
WHERE a.c1 > 0
ORDER BY a.c1) a
, t2 b
WHERE ROWNUM <= 5
AND b.c1 = a.c1;
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 9 | | | |
|* 1 | COUNT STOPKEY | | 1 | 5 | 9 | | | |
| 2 | MERGE JOIN | | 1 | 5 | 9 | | | |
| 3 | VIEW | | 1 | 15 | 4 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 15 | 4 | | | |
|* 5 | INDEX RANGE SCAN | T1_X1 | 1 | 15 | 2 | | | |
|* 6 | SORT JOIN | | 15 | 5 | 5 | 2048 | 2048 | 2048 (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 10 | 5 | | | |
-------------------------------------------------------------------------------------------------------
당연히 해시 조인도 가능하다. 소트 머지 조인과 해시 조인은 PGA을 사용하므로 쿼리의 수행 빈도에 주의해야 한다.
-- 9
SELECT /*+ LEADING(B) USE_HASH(B) */
*
FROM (SELECT a.*
FROM t1 a
WHERE a.c1 > 0
ORDER BY a.c1) a
, t2 b
WHERE ROWNUM <= 5
AND b.c1 = a.c1;
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 9 | | | |
|* 1 | COUNT STOPKEY | | 1 | 5 | 9 | | | |
|* 2 | HASH JOIN | | 1 | 5 | 9 | 2171K| 2171K| 1468K (0)|
| 3 | TABLE ACCESS FULL | T2 | 1 | 10 | 5 | | | |
| 4 | VIEW | | 1 | 15 | 4 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 15 | 4 | | | |
|* 6 | INDEX RANGE SCAN | T1_X1 | 1 | 15 | 2 | | | |
-------------------------------------------------------------------------------------------------------
'Oracle > Tuning' 카테고리의 다른 글
| 다중 행 다중 열 스칼라 서브 쿼리의 부분 범위 처리 (0) | 2019.01.09 |
|---|---|
| 파티션 인덱스와 소트 (0) | 2019.01.08 |
| 고유 값 구하기 (0) | 2019.01.04 |
| UNION ALL 연산자의 Top-N 동작 (0) | 2018.12.31 |
| 조건 기술에 따른 MERGE 문의 성능 차이 (0) | 2018.12.26 |
