쿼리 변환을 통해 Top-N 쿼리의 성능을 개선한 사례를 살펴보자.
아래와 같이 테이블을 생성하자.
아래는 t1, t2 테이블을 아우터 조인한 Top-N 쿼리다. t1, t2 테이블이 해시 조인되어 418개의 블록 I/O가 발생했다.
2번 쿼리는 아우터 조인한 t2 테이블의 c1 칼럼이 PK이므로 아래 쿼리처럼 Top-N 처리 후 t2 테이블을 조인해도 결과가 동일하다. t2 테이블의 NL 조인되어 블록 I/O가 229개로 감소했다.
아래와 같이 쿼리를 변경해보자. 인라인 뷰의 SELECT 목록을 t1 테이블만 조회했고, Top-N 처리 후에 t2 테이블을 아우터 조인했다. 중복 조인으로 인해 블록 I/O가 5번 쿼리보다 20개 증가했다. 전체 블록 I/O에 비해 크지 않은 수치로 볼 수 있다.
스칼라 서브 쿼리도 동일한 기법을 활용할 수 있다. 아래 쿼리는 동적 조건에 스칼라 서브 쿼리의 결과를 사용했다. 101,000개의 블록 I/O가 발생했다.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1, ROWNUM AS c2 FROM XMLTABLE ('1 to 100000');
CREATE TABLE t2 AS SELECT * FROM t1;
ALTER TABLE t2 ADD CONSTRAINT t2_pk PRIMARY KEY (c1);
-- 2
SELECT *
FROM (SELECT *
FROM t1 a
LEFT OUTER
JOIN t2 b
ON b.c1 = a.c2
WHERE a.c1 > 0
ORDER BY a.c1) a
WHERE ROWNUM <= 10;
--------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 418 |
|* 1 | COUNT STOPKEY | | 1 | 10 | 418 |
| 2 | VIEW | | 1 | 10 | 418 |
|* 3 | SORT ORDER BY STOPKEY| | 1 | 10 | 418 |
|* 4 | HASH JOIN OUTER | | 1 | 100K| 418 |
|* 5 | TABLE ACCESS FULL | T1 | 1 | 100K| 209 |
| 6 | TABLE ACCESS FULL | T2 | 1 | 100K| 209 |
--------------------------------------------------------------------
-- 3
SELECT *
FROM (SELECT *
FROM t1 a
WHERE a.c1 > 0
ORDER BY a.c1) a
LEFT OUTER
JOIN t2 b
ON b.c1 = a.c2
WHERE ROWNUM <= 10;
---------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 229 |
|* 1 | COUNT STOPKEY | | 1 | 10 | 229 |
| 2 | NESTED LOOPS OUTER | | 1 | 10 | 229 |
| 3 | VIEW | | 1 | 10 | 209 |
| 4 | SORT ORDER BY | | 1 | 10 | 209 |
|* 5 | TABLE ACCESS FULL | T1 | 1 | 100K| 209 |
| 6 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 10 | 20 |
|* 7 | INDEX UNIQUE SCAN | T2_PK | 10 | 10 | 10 |
---------------------------------------------------------------------------
아래와 같이 t2 테이블에 대한 동적 조건이 존재한다면 쿼리를 3번 쿼리처럼 변경할 수 없다. 동적 조건과 함께 아우터 조인까지 동적으로 처리하는 방식이 성능 측면에서 가장 효율적이지만 개발 난이도가 높아지는 단점이 있다.
-- 4
SELECT *
FROM (SELECT *
FROM t1 a
LEFT OUTER
JOIN t2 b
ON b.c1 = a.c2
WHERE a.c1 > 0
AND b.c2 > 0 -- 동적 조건
ORDER BY a.c1) a
WHERE ROWNUM <= 10;
--------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 418 |
|* 1 | COUNT STOPKEY | | 1 | 10 | 418 |
| 2 | VIEW | | 1 | 10 | 418 |
|* 3 | SORT ORDER BY STOPKEY| | 1 | 10 | 418 |
|* 4 | HASH JOIN | | 1 | 100K| 418 |
|* 5 | TABLE ACCESS FULL | T1 | 1 | 100K| 209 |
|* 6 | TABLE ACCESS FULL | T2 | 1 | 100K| 209 |
--------------------------------------------------------------------
-- 5
SELECT *
FROM (SELECT a.* -- 변경
FROM t1 a
LEFT OUTER
JOIN t2 b
ON b.c1 = a.c2
WHERE a.c1 > 0
AND b.c2 > 0 -- 동적 조건
ORDER BY a.c1) a
LEFT OUTER
JOIN t2 b
ON b.c1 = a.c2
WHERE ROWNUM <= 10;
---------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 438 |
|* 1 | COUNT STOPKEY | | 1 | 10 | 438 |
| 2 | NESTED LOOPS OUTER | | 1 | 10 | 438 |
| 3 | VIEW | | 1 | 10 | 418 |
| 4 | SORT ORDER BY | | 1 | 10 | 418 |
|* 5 | HASH JOIN | | 1 | 100K| 418 |
|* 6 | TABLE ACCESS FULL | T1 | 1 | 100K| 209 |
|* 7 | TABLE ACCESS FULL | T2 | 1 | 100K| 209 |
| 8 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 10 | 20 |
|* 9 | INDEX UNIQUE SCAN | T2_PK | 10 | 10 | 10 |
---------------------------------------------------------------------------
아래 쿼리는 동적 조건이 비활성화된 경우를 가정하여 동적 조건을 주석 처리했다. 쿼리 변환에 의해 인라인 뷰 내에서 t2 테이블의 조인이 제거되었다. 블록 I/O가 3번 쿼리와 동일하게 229개만 발생한다. 쿼리 변환을 통해 동적 조건이 비활성화된 경우 블록 I/O를 감소시키는 개선 방안이다.
-- 6
SELECT *
FROM (SELECT a.* -- 변경
FROM t1 a
LEFT OUTER
JOIN t2 b
ON b.c1 = a.c2
WHERE a.c1 > 0
-- AND b.c2 > 0 -- 동적 조건
ORDER BY a.c1) a
LEFT OUTER
JOIN t2 b
ON b.c1 = a.c2
WHERE ROWNUM <= 10;
---------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 229 |
|* 1 | COUNT STOPKEY | | 1 | 10 | 229 |
| 2 | NESTED LOOPS OUTER | | 1 | 10 | 229 |
| 3 | VIEW | | 1 | 10 | 209 |
| 4 | SORT ORDER BY | | 1 | 10 | 209 |
|* 5 | TABLE ACCESS FULL | T1 | 1 | 100K| 209 |
| 6 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 10 | 20 |
|* 7 | INDEX UNIQUE SCAN | T2_PK | 10 | 10 | 10 |
---------------------------------------------------------------------------
-- 7
SELECT a.*
FROM (SELECT a.*
, (SELECT MAX (x.c2) FROM t2 x WHERE x.c1 = a.c2) AS xc2
FROM t1 a
ORDER BY a.c1) a
WHERE ROWNUM <= 10
AND a.xc2 > 0 -- 동적 조건
;
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 101K|
| 1 | SORT AGGREGATE | | 100K| 100K| 101K|
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 100K| 100K| 101K|
|* 3 | INDEX UNIQUE SCAN | T2_PK | 100K| 100K| 1462 |
|* 4 | COUNT STOPKEY | | 1 | 10 | 101K|
|* 5 | VIEW | | 1 | 10 | 101K|
| 6 | SORT ORDER BY | | 1 | 10 | 101K|
| 7 | TABLE ACCESS FULL | T1 | 1 | 100K| 209 |
--------------------------------------------------------------------------
아래와 같이 쿼리를 변경해보자. 메인 쿼리의 SELECT 목록에서 t1 테이블만 조회했고, 스칼라 서브 쿼리를 중복 기술했다. 중복 스칼라 서브 쿼리로 인해 블록 I/O가 7번 쿼리보다 20개 증가했다.
-- 8
SELECT a.c1 -- 변경
, a.c2 -- 변경
, (SELECT MAX (x.c2) FROM t2 x WHERE x.c1 = a.c2) AS xc2
FROM (SELECT a.*
, (SELECT MAX (x.c2) FROM t2 x WHERE x.c1 = a.c2) AS xc2
FROM t1 a
ORDER BY a.c1) a
WHERE ROWNUM <= 10
AND a.xc2 > 0 -- 동적 조건
;
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 101K|
| 1 | SORT AGGREGATE | | 10 | 10 | 20 |
| 2 | TABLE ACCESS BY INDEX ROWID | T2 | 10 | 10 | 20 |
|* 3 | INDEX UNIQUE SCAN | T2_PK | 10 | 10 | 10 |
|* 4 | COUNT STOPKEY | | 1 | 10 | 101K|
|* 5 | VIEW | | 1 | 10 | 101K|
| 6 | SORT AGGREGATE | | 100K| 100K| 101K|
| 7 | TABLE ACCESS BY INDEX ROWID| T2 | 100K| 100K| 101K|
|* 8 | INDEX UNIQUE SCAN | T2_PK | 100K| 100K| 1462 |
| 9 | SORT ORDER BY | | 1 | 10 | 101K|
| 10 | TABLE ACCESS FULL | T1 | 1 | 100K| 209 |
----------------------------------------------------------------------------
아래 쿼리는 동적 조건을 주석 처리했다. 아우터 조인과 마찬가지로 쿼리 변환에 의해 스칼라 서브 쿼리가 제거되어 블록 I/O가 209개만 발생한다.
-- 9
SELECT a.c1 -- 변경
, a.c2 -- 변경
, (SELECT MAX (x.c2) FROM t2 x WHERE x.c1 = a.c2) AS c2x
FROM (SELECT a.*
, (SELECT MAX (x.c2) FROM t2 x WHERE x.c1 = a.c2) AS xc2
FROM t1 a
ORDER BY a.c1) a
WHERE ROWNUM <= 10
-- AND a.xc2 > 0 -- 동적 조건
;
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 209 |
| 1 | SORT AGGREGATE | | 10 | 10 | 20 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 10 | 20 |
|* 3 | INDEX UNIQUE SCAN | T2_PK | 10 | 10 | 10 |
|* 4 | COUNT STOPKEY | | 1 | 10 | 209 |
| 5 | VIEW | | 1 | 10 | 209 |
|* 6 | SORT ORDER BY STOPKEY | | 1 | 10 | 209 |
| 7 | TABLE ACCESS FULL | T1 | 1 | 100K| 209 |
--------------------------------------------------------------------------'Oracle > Tuning' 카테고리의 다른 글
| 여부 속성으로 인한 성능 저하 사례 #2 (0) | 2020.07.09 |
|---|---|
| 여부 속성으로 인한 성능 저하 사례 #1 (0) | 2020.07.08 |
| 뷰 병합이 동작하지 않는 사례 (0) | 2020.04.07 |
| 조인에 따른 사용자 함수의 동작 (0) | 2020.03.21 |
| 월 기준 이력 조회 (0) | 2020.03.16 |
