Subquery Pushing으로 동작하는 비상관 단일 행 서브쿼리를 액세스 조건으로 사용하면서브 쿼리가 Execute Call 단계에서 수행됩니다. 이를 통해 쿼리의 성능을 개선한 사례를 살펴보겠습니다.
테스트를 위해 아래와 같이 테이블과 인덱스를 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 (c1, c2, c3)
AS
SELECT CEIL (ROWNUM / 100000), NVL (NULLIF (MOD (ROWNUM, 100000), 0), 100000), LPAD ('X', 100, 'X')
FROM XMLTABLE ('1 to 1000000');
CREATE TABLE t2 (c1, c2, c3)
AS
SELECT CEIL (ROWNUM / 100000), NVL (NULLIF (MOD (ROWNUM, 100000), 0), 100000), LPAD ('X', 100, 'X')
FROM XMLTABLE ('1 to 1000000');
CREATE INDEX t1_x1 ON t1 (c1);
CREATE INDEX t2_x1 ON t2 (c1, c2);
아래 예제는 서브 쿼리가 필터 조건으로 사용되어 1,785개의 블록 I/O가 발생합니다.
-- 2-1
VAR b1 NUMBER = 1
-- 2-2
SELECT *
FROM t1
WHERE c1 = :b1
AND c2 = (SELECT c2 FROM t2 WHERE c1 = :b1 AND c2 > 100000 AND ROWNUM = 1)
AND ROWNUM = 1;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 0 |00:00:00.03 | 1785 | -- (6)
|* 1 | COUNT STOPKEY | | 1 | 0 |00:00:00.03 | 1785 | -- (5)
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 0 |00:00:00.03 | 1785 | -- (2)
|* 3 | INDEX RANGE SCAN | T1_X1 | 1 | 100K|00:00:00.01 | 197 | -- (1)
|* 4 | COUNT STOPKEY | | 1 | 0 |00:00:00.01 | 3 | -- (4)
|* 5 | INDEX RANGE SCAN | T2_X1 | 1 | 0 |00:00:00.01 | 3 | -- (3)
-----------------------------------------------------------------------------------------------
Outline Data
-------------
PUSH_SUBQ(@"SEL$2")
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - filter("C2"=)
3 - access("C1"=:B1)
4 - filter(ROWNUM=1)
5 - access("C1"=:B1 AND "C2">100000 AND "C2" IS NOT NULL)
서브쿼리를 액세스 조건으로 사용할 수 있도록 t1_x2 인덱스를 생성하고 쿼리를 다시 수행하면 블록 I/O가 3으로 감소합니다.
-- 3-1
CREATE INDEX t1_x2 ON t1 (c1, c2);
-- 3-2
SELECT *
FROM t1
WHERE c1 = :b1
AND c2 = (SELECT c2 FROM t2 WHERE c1 = :b1 AND c2 > 100000 AND ROWNUM = 1)
AND ROWNUM = 1;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 0 |00:00:00.01 | 3 | -- (6)
|* 1 | COUNT STOPKEY | | 1 | 0 |00:00:00.01 | 3 | -- (5)
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 0 |00:00:00.01 | 3 | -- (4)
|* 3 | INDEX RANGE SCAN | T1_X2 | 1 | 0 |00:00:00.01 | 3 | -- (1)
|* 4 | COUNT STOPKEY | | 1 | 0 |00:00:00.01 | 3 | -- (3)
|* 5 | INDEX RANGE SCAN | T2_X1 | 1 | 0 |00:00:00.01 | 3 | -- (2)
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - access("C1"=:B1 AND "C2"=)
4 - filter(ROWNUM=1)
5 - access("C1"=:B1 AND "C2">100000 AND "C2" IS NOT NULL)
아래 10046 트레이스에서 필터 조건으로 사용된 서브쿼리는 Fetch Call 단계, 액세스 조건으로 사용된 서브쿼리는 Execute Call 단계에서 수행되는 것을 확인할 수 있습니다.
-- 2-2: 2
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 1 0.02 0.02 0 1785 0 0 -- !
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.03 0.02 0 1785 0 0
-- 3-2: 3
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 3 0 0 -- !
Fetch 1 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 0