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