계층 쿼리 절은 다양한 방식으로 수행될 수 있다. 관련 내용을 살펴보자.
테스트를 위해 아래와 같이 테이블을 생성하자.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1, ROWNUM + 1 AS c2, LPAD ('X', 100, 'X') AS c3 FROM XMLTABLE ('1 to 1000');
CREATE TABLE t2 AS SELECT * FROM t1;
CREATE INDEX t1_x1 ON t1 (c1);
EXEC DBMS_STATS.GATHER_TABLE_STATS ('SCOTT','T1');
EXEC DBMS_STATS.GATHER_TABLE_STATS ('SCOTT','T2');
9.0 이전 버전은 계층 쿼리 절이 CONNECT BY 방식으로 수행된다. _OLD_CONNECT_BY_ENABLED 파라미터를 TRUE로 하면 해당 방식으로 계층 쿼리 절을 수행할 수 있다. CONNECT BY 방식은 아래의 순서로 수행된다. 실행 계획 7번의 수행 횟수(1999회)에서 계층을 건건이 수직 탐색하는 방식으로 동작한다는 것을 유추할 수 있다.
START WITH 절에 해당하는 행을 조회하여 PGA에 저장 (4 -> 5 -> 3)
결과가 반환되지 않을 때까지 CONNECT BY 절을 반복 수행하며 결과를 반환 (7 -> 6 -> 2)
-- 2
ALTER SESSION SET "_OLD_CONNECT_BY_ENABLED" = TRUE;
SELECT COUNT (*) AS cn
FROM t1
START WITH c1 IN (1, 2)
CONNECT BY c1 = PRIOR c2;
---------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 4038 |
| 1 | SORT AGGREGATE | | 1 | 1 | 4038 |
| 2 | CONNECT BY | | 1 | 1999 | 4038 | -- (5)
| 3 | INLIST ITERATOR | | 1 | 2 | 4 | -- (3)
|* 4 | INDEX RANGE SCAN | T1_X1 | 2 | 2 | 4 | -- (1)
| 5 | TABLE ACCESS BY USER ROWID | T1 | 2 | 2 | 2 | -- (2)
| 6 | TABLE ACCESS BY INDEX ROWID| T1 | 1999 | 1997 | 4032 | -- (5) -> (3)
|* 7 | INDEX RANGE SCAN | T1_X1 | 1999 | 1997 | 2035 | -- (4)
---------------------------------------------------------------------------
9.0 버전부터 CONNECT BY 방식이 CONNECT BY WITH FILTERING 방식으로 대체되었다. 해당 방식은 아래의 순서로 수행된다. 실행 계획 6번의 수행 횟수(999회)에서 계층을 수평 탐색하는 방식으로 동작한 것을 유추할 수 있다.
START WITH 절에 해당하는 행을 조회하여 PUMP(PGA)에 저장 (5 -> 4 -> 3)
계층별로 CONNECT BY 절을 수행하여 결과를 PUMP에 저장하는 작업을 반복 수행 (7 -> 9 -> 8 -> 6)
반환되는 결과가 없으면 PUMP에 저장된 결과를 정렬하여 반환 (2)
-- 3-1 : 9.0
ALTER SESSION SET "_OLD_CONNECT_BY_ENABLED" = FALSE;
SELECT /*+ CONNECT_BY_FILTERING */
COUNT (*) AS cn
FROM t1
START WITH c1 IN (1, 2)
CONNECT BY c1 = PRIOR c2;
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 2022 |
| 1 | SORT AGGREGATE | | 1 | 1 | 2022 |
|* 2 | CONNECT BY WITH FILTERING | | 1 | 1999 | 2022 | -- (7)
| 3 | INLIST ITERATOR | | 1 | 2 | 5 | -- (3)
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 2 | 5 | -- (2)
|* 5 | INDEX RANGE SCAN | T1_X1 | 2 | 2 | 4 | -- (1)
| 6 | NESTED LOOPS | | 999 | 1000 | 2017 | -- (7) -> (4)
| 7 | CONNECT BY PUMP | | 999 | 1001 | 0 | -- (4)
| 8 | TABLE ACCESS BY INDEX ROWID| T1 | 1001 | 1000 | 2017 | -- (6)
|* 9 | INDEX RANGE SCAN | T1_X1 | 1001 | 1000 | 1019 | -- (5)
----------------------------------------------------------------------------
아래 쿼리는 CONNECT BY 절에 ROWNUM <= 10 조건을 기술했지만 쿼리 3-1과 블록 I/O가 동일하다. CONNECT BY WITH FILTERING 방식은 수행 결과를 PUMP에 저장하기 때문에 부분 범위 처리가 불가능하다.
-- 3-2
SELECT COUNT (*) AS cn
FROM t1
START WITH c1 IN (1, 2)
CONNECT BY c1 = PRIOR c2
AND ROWNUM <= 10;
------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 2022 |
| 1 | SORT AGGREGATE | | 1 | 1 | 2022 |
| 2 | COUNT | | 1 | 11 | 2022 |
|* 3 | CONNECT BY WITH FILTERING | | 1 | 11 | 2022 |
| 4 | INLIST ITERATOR | | 1 | 2 | 5 |
| 5 | TABLE ACCESS BY INDEX ROWID | T1 | 2 | 2 | 5 |
|* 6 | INDEX RANGE SCAN | T1_X1 | 2 | 2 | 4 |
|* 7 | COUNT STOPKEY | | 999 | 1000 | 2017 | -- !
| 8 | NESTED LOOPS | | 999 | 1000 | 2017 |
| 9 | CONNECT BY PUMP | | 999 | 1001 | 0 |
| 10 | TABLE ACCESS BY INDEX ROWID| T1 | 1001 | 1000 | 2017 |
|* 11 | INDEX RANGE SCAN | T1_X1 | 1001 | 1000 | 1019 |
------------------------------------------------------------------------------
CONNECT BY 방식은 계층을 건건이 수직 탐색하므로 부분 범위 처리가 가능하다.
-- 3-3
ALTER SESSION SET "_OLD_CONNECT_BY_ENABLED" = TRUE;
SELECT COUNT (*) AS cn
FROM t1
START WITH c1 IN (1, 2)
CONNECT BY c1 = PRIOR c2
AND ROWNUM <= 10;
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 20 |
| 1 | SORT AGGREGATE | | 1 | 1 | 20 |
|* 2 | COUNT STOPKEY | | 1 | 10 | 20 | -- !
| 3 | CONNECT BY | | 1 | 10 | 20 |
| 4 | INLIST ITERATOR | | 1 | 1 | 2 |
|* 5 | INDEX RANGE SCAN | T1_X1 | 1 | 1 | 2 |
| 6 | TABLE ACCESS BY USER ROWID | T1 | 1 | 1 | 1 |
| 7 | TABLE ACCESS BY INDEX ROWID| T1 | 9 | 9 | 17 |
|* 8 | INDEX RANGE SCAN | T1_X1 | 9 | 9 | 8 |
----------------------------------------------------------------------------
아래 쿼리는 CONNECT BY 절에서 사용할 수 있는 인덱스가 존재하지 않아 계층수만큼 FULL SCAN을 반복하여 과도한 블록 I/O가 발생했다.
-- 3-4
ALTER SESSION SET "_OLD_CONNECT_BY_ENABLED" = FALSE;
DROP INDEX t1_x1;
SELECT /*+ CONNECT_BY_FILTERING */
COUNT (*) AS cn
FROM t1
START WITH c1 IN (1, 2)
CONNECT BY c1 = PRIOR c2;
-----------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 19000 |
| 1 | SORT AGGREGATE | | 1 | 1 | 19000 |
|* 2 | CONNECT BY WITH FILTERING| | 1 | 1999 | 19000 | -- (5)
|* 3 | TABLE ACCESS FULL | T1 | 1 | 2 | 19 | -- (1)
|* 4 | HASH JOIN | | 999 | 1000 | 18981 | -- (4) -> (2)
| 5 | CONNECT BY PUMP | | 999 | 1001 | 0 | -- (2)
| 6 | TABLE ACCESS FULL | T1 | 999 | 999K| 18981 | -- (3)
-----------------------------------------------------------------------
이런 상황을 개선하기 위해 10.2.0.2 버전에서 CONNECT BY WITHOUT FILTERING 방식이 도입되었다. 해당 방식은 아래의 순서로 수행된다. 인덱스가 존재하지 않더라도 과도한 블록 I/O가 발생하지 않는다.
- START WITH 절에 해당하는 행을 조회하여 PGA에 저장 (3)
- t1 테이블을 조회하여 PGA에 데이터 구조를 생성 (4)
- 1단계의 결과로 루트 노드를 결정하고, CONNECT BY 절을 반복 수행하여 결과를 저장하고, 계층 전개가 완료되면 저장된 결과를 정렬하여 반환 (2)
-- 4 : 10.2.0.2
SELECT /*+ NO_CONNECT_BY_FILTERING NO_CONNECT_BY_COMBINE_SW */
COUNT (*) AS cn
FROM t1
START WITH c1 IN (1, 2)
CONNECT BY c1 = PRIOR c2;
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 38 |
| 1 | SORT AGGREGATE | | 1 | 1 | 38 |
|* 2 | CONNECT BY WITHOUT FILTERING| | 1 | 1999 | 38 | -- (3)
|* 3 | TABLE ACCESS FULL | T1 | 1 | 2 | 19 | -- (1)
| 4 | TABLE ACCESS FULL | T1 | 1 | 1000 | 19 | -- (2)
--------------------------------------------------------------------------
- t1 테이블을 조회하여 PGA에 데이터 구조를 생성, START WITH 절에 해당하는 행은 별도 저장 (1)
- 전체 행을 1단계에 저장한 START WITH 절에 해당하는 행과 건건히 비교하여 루트 노드를 결정하고, CONNECT BY 절을 반복 수행하여 결과를 저장, 계층 전개가 완료되면 저장된 결과를 정렬하여 반환 (2)
-- 5-1 : 10.2.0.4
SELECT /*+ NO_CONNECT_BY_FILTERING CONNECT_BY_COMBINE_SW */
COUNT (*) AS cn
FROM t1
START WITH c1 IN (1, 2)
CONNECT BY c1 = PRIOR c2;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 19 |
| 1 | SORT AGGREGATE | | 1 | 1 | 19 |
|* 2 | CONNECT BY NO FILTERING WITH START-WITH| | 1 | 1999 | 19 | -- (2)
| 3 | TABLE ACCESS FULL | T1 | 1 | 1000 | 19 | -- (1)
-------------------------------------------------------------------------------------
CONNECT BY WITHOUT FILTERING 방식은 전체 행을 START WITH 절에 해당하는 행과 건건히 비교하기 때문에 START WITH 절에 서브 쿼리를 사용했을 경우 과도한 블록 I/O가 발생할 수 있다. 아래 실행 계획에서 t2 테이블이 1000회 FULL SCAN된 것을 확인할 수 있다.
-- 5-2
SELECT /*+ NO_CONNECT_BY_FILTERING CONNECT_BY_COMBINE_SW */
COUNT (*) AS cn
FROM t1
START WITH c1 IN (SELECT c1 FROM t2 WHERE c2 IN (2, 3))
CONNECT BY c1 = PRIOR c2;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 18989 |
| 1 | SORT AGGREGATE | | 1 | 1 | 18989 |
|* 2 | CONNECT BY NO FILTERING WITH START-WITH| | 1 | 1999 | 18989 | -- (3)
| 3 | TABLE ACCESS FULL | T1 | 1 | 1000 | 19 | -- (1)
|* 4 | TABLE ACCESS FULL | T2 | 1000 | 2 | 18970 | -- (2)
-------------------------------------------------------------------------------------
-- 5-3
SELECT /*+ NO_CONNECT_BY_FILTERING NO_CONNECT_BY_COMBINE_SW */
COUNT (*) AS cn
FROM t1
START WITH c1 IN (SELECT c1 FROM t2 WHERE c2 IN (2, 3))
CONNECT BY c1 = PRIOR c2;
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 57 |
| 1 | SORT AGGREGATE | | 1 | 1 | 57 |
|* 2 | CONNECT BY WITHOUT FILTERING| | 1 | 1999 | 57 | -- (5)
|* 3 | HASH JOIN RIGHT SEMI | | 1 | 2 | 38 | -- (3)
|* 4 | TABLE ACCESS FULL | T2 | 1 | 2 | 19 | -- (1)
| 5 | TABLE ACCESS FULL | T1 | 1 | 1000 | 19 | -- (2)
| 6 | TABLE ACCESS FULL | T1 | 1 | 1000 | 19 | -- (4)
--------------------------------------------------------------------------'Oracle > Tuning' 카테고리의 다른 글
| 제약 조건과 인덱스 (0) | 2018.11.04 |
|---|---|
| CLUSTER_BY_ROWID 힌트 (0) | 2018.11.03 |
| JPPD(Join Predicate PushDown) 쿼리 변환이 동작하지 않는 사례 (0) | 2018.11.03 |
| Join Elimination 쿼리 변환이 동작하지 않는 사례 (0) | 2018.11.03 |
| 사용자 정의 함수의 동작 방식 (0) | 2018.10.27 |
