ORACLE SUBQUERY FACTORING
from 실행 계획으로 배우는 고성능 데이터베이스 튜닝 | 비팬북스 | 권순용
1. ORACLE SUBQUERY FACTORING
이름은 SUBQUERY 이지만 INLINE VIEW 와 유사하다. 추가로 뷰머징이 불가능한 INLINE VIEW 와 유사하다. SUBQUERY FACTORING 은 별도의 공간에 임시테이블을 생성한다.
(이 기능은 9i 부터 사용가능하다.)
임시테이블은 SQL 종료까지 메모리에 존재한다. 수행되는 방법에는 두가지가 있다.
-서브쿼리 팩토링이 독립적으로 수행됨 => 임시테이블을 생성
-서브쿼리 팩토링이 메인 쿼리에 삽입되어 수행 => 쿼리길이를 단축한 효과
(단순히 SHELL 의 ALIAS 와 비슷하다 생각해도 될 듯)
<메인쿼리에 삽입>
SQL> get sqf1
1 with samp as (select * from emp)
2* select ename from samp
SQL> @sqf1
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 90 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 15 | 90 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
위 쿼리는 select ename from (select * from emp) 와 동일하다.
(뷰머징이 안되었다고 가정)
<독립적인 수행>
SQL> get sqf1
1 with samp as (select /*+ materialize */ * from emp)
2* select ename from samp
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 1389430303
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 105 | 5 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | TABLE ACCESS FULL | EMP | 15 | 555 | 3 (0)| 00:00:01 |
| 4 | VIEW | | 15 | 105 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6617_311152 | 15 | 555 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
SUBQUERY FACTORING 이 독립적으로 사용된 경우의 실행계획이다. 5번에 보면 시스템이 가상의 테이블을 생성한 걸 알 수 있다.
2. SUBQUERY FACTORING 의 사용의의
반복적인 엑세스가 일어나는 테이블이 있는 경우 디스크에서 읽어들여 메모리에 캐싱하고
CPU 시간을 소모하는 일련의 사이클을 거쳐 DB BUFFER에서 AGING OUT 되는 경우가 있는데 (특히 FULL TABLE SCAN 시) AGING OUT이 의미하는 바는 디스크에서 메모리로 다시 읽어들여야 한다는 뜻이다. 반복사용한다는 것은 SQL 수행타임 중 많이 사용된다는 이야기며 SUBQUERY FACTORING을 이용하면 SQL 수명동안 임시테이블로 존재하기 때문에 디스크 I/O를 줄일 수 있다.
/*+ 예제 추가 */
3. SUBQUERY FACTORING 사용시 주의점
SQL> get sqf1
1 with samp as (select /*+ materialize */ * from emp)
2 select ename from samp
3* where empno = 7788
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 1463519103
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 300 | 5 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | TABLE ACCESS FULL | EMP | 15 | 555 | 3 (0)| 00:00:01 |
|* 4 | VIEW | | 15 | 300 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661B_311152 | 15 | 555 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
위에서 눈여겨 볼 점은 WHERE 절이다. SUBQUERY FACTORING 은 메모리 공간에 생성된 임시테이블이다. (VIEW) 달리 말해 새롭게 생성된 집합을 의미하며 이는 곧 인덱싱이 안되어 있음을 의미한다. 때문에 메인 쿼리에서 WHERE 절 을 주면 FULL TABLE SCAN 을 하게 된다. 이는 NL JOIN 에서 성능이슈가 될 수 있는데 DRIVING TABLE 로 오는 경우 INNER TABLE 에 대한 반복적인 엑세스로 INNER TABLE 로 오는 경우 DRIVING 결과 건수만큼 FULL TABLE SCAN 을 할 위험성이 있다.
SQL> get sqf1
1 with samp as (select /*+ materialize index(e,emp_deptno_ix) */ * from emp e where deptno = 20)
2 select ename from samp
3* where empno = 7788
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 2129565714
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 100 | 4 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 185 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPTNO_IX | 5 | | 1 (0)| 00:00:01 |
|* 5 | VIEW | | 5 | 100 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6623_311152 | 5 | 185 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
앞서본 쿼리와 달리 SUBQUERY FACTORING 절에 조건절을 주었다. 하지만 위의 실행계획을 보면 VIEW 를 FULL TABLE ACCESS 한 것으로 나와있다. 조건절이 없어 FULL SCAN 을 한 것 처럼 보이지만 ROWS 와 BYTES 를 보면 WHERE 절로 FILTERING 된 결과임을 알 수 있다. FILTERING 된 결과는 3,4 를 보면 알 수 있다. 다시말해 WITH 절에서 처리범위를 줄일 수 있다면 줄이는게 낫다는 이야기다.