ORACLE FLATTEN INLINE VIEW #1
from 실행계획으로 배우는 고성능 데이터베이스 튜닝 | 비팬북스
1. INLINE VIEW 의 정의
QUERY 의 FROM 절에 사용한 SELECT 절을 INLINE VIEW 라 한다.
간단히 표현하면 SELECT ABC FROM (SELECT DEF FROM GHI) 를 보았을 때
FROM 절에 다시 정의된 괄호안의 SELECT 구문이 INLINE VIEW 에 해당한다.
2. FLATTEN VIEW and UNFLATTEN INLINE VIEW
INLINE VIEW 는 FLATTEN (병합된) UNFLATTEN (병합되지않은) 두가지 타입으로 나뉜다.
병합된은 병합가능한을 말함이고 병합되지않은은 병합이 불가능한을 말한다. 오라클은
사용자가 사용한 쿼리를 내부적으로 변형시키는데 이 과정에서 INLINE VIEW 는 병합될 수 있다.
3. FLATTEN VIEW 의 종류
먼저 주쿼리라는 단어에 대해 간단히 설명하고자 한다.
SELECT ABC FROM (SELECT DEF FROM GHI) 가 있을 때 주쿼리는 SELECT ABC FROM
을 가리킨다.
병합은 두가지 경우로 나타나는데
하나는 인라인뷰가 없어지고 주쿼리와 조인하는 형태로 변형되는 경우와
두번째는 주쿼리의 조건절(예를들면 WHERE절) 이 INLINE VIEW 로 파고드는 경우이다.
4. INLINE VIEW 예제
<<INLINE VIEW>>
SQL> get inline1
1 select d.department_name,e.member
2 from departments d,
3 (select
4 count(employee_id) member,department_id
5 from employees
6 group by department_id ) e
7 where e.department_id = d.department_id
8* and d.department_name like 'IT%'
inline view 를 사용한 쿼리이다. employees 를 department_id 를 그룹핑 조건으로 사용해 sum 한 결과와
departments 테이블과 조인한다.
SQL> /
DEPARTMENT_NAME MEMBER
------------------------------ ----------
IT 5
SQL> @disp
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 09rxqc5qsf4uy, child number 0
-------------------------------------
select d.department_name,e.member from departments d, (select count(employee_id)
member,department_id from employees group by department_id ) e where e.department_id =
d.department_id and d.department_name like 'IT%'
Plan hash value: 1435548253
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | HASH GROUP BY | | 8 | 184 | 3 (34)| 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 230 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | EMP_DEPARTMENT_IX | 107 | 321 | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 20 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
4 - filter("D"."DEPARTMENT_NAME" LIKE 'IT%')
5 - access("DEPARTMENT_ID"="D"."DEPARTMENT_ID")
25 개의 행이 선택되었습니다.
위의 실행계획을 해석하면 아래와 같다.
departments 와 employees 는 NESTED LOOP JOIN 으로 풀렸고 DRIVING TABLE 은 EMP TABLE 이다. INNER TABLE 은 DEPARTMENTS 이다.
EMP_DEPARTMENT_IX 는 EMP.DEPARTMENT_ID 를 인덱싱하고 있다. 이 인덱스를 읽어 상수값을 얻으면 DEPARTMENTS.DEPARTMENT_ID 에 이 값을 넘겨 조인하게 된다. DEPARTMENTS.DEPARTMENT_ID 는 UNIQUE 한 값으로 RANGE SCAN 의 필요가 없다. 조인에 성공하면 DEPARTMENTS 에 엑세스해 나머지 값을 가지고 오게 된다.
이에 대해 이야기하기에 앞서 EMPLOYEES 의 경우 INDEX SCAN 만으로 끝난 것을 확인할 수 있다. 다시 말해 INDEX SCAN 뒤 TABLE SACN 이 별도로 일어나지 않았는데 이 이유는 GROUPING 조건이 DEPARTMENT_ID 이고 이를 토대로 카운트하기 때문이다. 쿼리에는 COUNT(EMPLOYEE_ID) 로 사용했지만 이 경우는 COUNT(DEPARTMENT_ID) 와 동일하다. 사실 HR.EMPLOYEES 에는 부서에 할당받지 못한 EMPLOYEE 가 있지만 이 경우 그룹핑하여 DEPARTMENT_ID 로 연결한다고 했을 때 DEPARTMENT_ID 자체가 NULL 이기 때문에 조인이 불가능하다. 결과적으로 인덱스에 있는 값만으로 수행해도 아무런 문제가 없다.
반면 DEPARTMENTS 에 대해
|* 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 20 | 1 (0)| 00:00:01 |
가 일어난 이유는 DEPT_ID_PK 가 DEPARTMENT_ID 정보만 가지고 있기 때문이다. SELECT 항목을 살펴보면 DEPARTMENT_NAME 이 있는 걸 확인할 수 있다. 해당정보는 인덱스에 없기 때문에 결과적으로 테이블에 엑세스해 얻는 방법밖에 없다.
이야기가 상당히 빗나갔는데 다시 간결히 이야기 하자면 EMPLOYEES 와 DEPARTMENTS 를 NESTED LOOP 로 조인한 후 이를 그룹핑했다.
유저쿼리만 보면 인라인쿼리가 먼저 수행된 후 주쿼리가 수행되어야 할 것으로 보인다. 풀어이야기 하면 EMPLOYEES 에 엑세스해 그룹핑한 결과를 가지고 DEPARTMENTS 와 조인한다 라는 흐름이다.
하지만 실질적으로 실행플랜을 봤을 때 쿼리는 이런 방법으로 풀리지 않았다.
<<JOIN>>
SQL> get inline2
1 select d.department_name,count(e.employee_id) member
2 from departments d,employees e
3 where e.department_id = d.department_id
4 and d.department_name like 'IT%'
5* group by d.department_id,d.department_name
SQL> /
DEPARTMENT_NAME MEMBER
------------------------------ ----------
IT 5
SQL> @disp
~~ 중략 ~~
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | HASH GROUP BY | | 1 | 19 | 3 (34)| 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 190 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | EMP_DEPARTMENT_IX | 107 | 321 | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| |
---------------------------------------------------------------------------------------------------
수행 쿼리를 inline 을 없애고 join 으로 변형하여 수행한 모습이다. inline view 를 사용한 경우와 동일하게 풀린 것을 알 수 있다. 위 쿼리를 보면 아까 풀어 이야기한 실행플랜과 일치시키기 더 쉽다. 이처럼 오라클은 유저쿼리를 내부적으로 변형하는 경우가 있다. 그리고 이 변형은 단순화의 방향을 띤다.
<<UNFLATTEN VIEW>>
SQL> get inline3
1 select /*+ no_merge(e) */ d.department_name,e.member
2 from departments d,
3 (select sum(rownum),
4 count(employee_id) member,department_id
5 from employees
6 group by department_id ) e
7 where e.department_id = d.department_id
8* and d.department_name like 'IT%'
SQL> /
DEPARTMENT_NAME MEMBER
------------------------------ ----------
IT 5
SQL> @disp
~~ 중략 ~~
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | NESTED LOOPS | | 1 | 32 | 5 (20)| 00:00:01 |
| 2 | VIEW | | 11 | 176 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 11 | 33 | 4 (25)| 00:00:01 |
| 4 | COUNT | | | | | |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 321 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| |
--------------------------------------------------------------------------------------------
쿼리는 처음 inline view 의 경우와 동일하다. 이에 힌트하나를 추가하였다. 힌트는 no_merge 를 사용하였다. /*+ no_merge (table_alias) */ 형태로 사용하며 의미는 inline view 의 병합을 막는다는 뜻이다.
실행플랜을 보면 VIEW 항목을 확인할 수 있다. 이 단어는 병합불가능 상태를 의미한다. 추가적으로 이는 저장공간을 따로 사용하고 있음을 의미한다. 3~5 를 보면 EMPLOYEES 테이블을 FULL SCAN 해 DEPARTMENT_ID 로 그룹핑 해 COUNT 한 것을 알 수 있다. 그리고 이를(VIEW) 가지고 DEPARTMENTS 와 JOIN을 수행했다.
5. 성능비교
<PLAN 1>
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | HASH GROUP BY | | 1 | 19 | 3 (34)| 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 190 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | EMP_DEPARTMENT_IX | 107 | 321 | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| |
---------------------------------------------------------------------------------------------------
<PLAN 2>
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | NESTED LOOPS | | 1 | 32 | 5 (20)| 00:00:01 |
| 2 | VIEW | | 11 | 176 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 11 | 33 | 4 (25)| 00:00:01 |
| 4 | COUNT | | | | | |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 321 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| |
--------------------------------------------------------------------------------------------
PLAN1 과 PLAN2 를 비교해 보겠다.
PLAN1 은 EMP 의 INDEX 를 FULL SCAN 하였다. EMP 는 이 실행계획에서 DRIVING TABLE 역할을 하며 DEPARTMENTS 를 반복적으로 엑세스하게 된다. 달리 표현하면 EMPLOYEES 의 ROW 수만큼 (이 경우 107건) DEPARTMENTS 를 ACCESS 하게 된다. 이 ACCESS 는 조인을 하기 위함이며 랜덤엑세스가 일어난다.
PLAN2 는 11건에 대해 DEPARTMENTS 에 대해 조인을 위한 랜덤엑세스가 일어난다.
위 경우 조인으로 인한 랜덤엑세스양은 NL JOIN 의 경우 DRIVING TABLE 의 ROW 수에 비례한다. 위나 아래나 조인을 위한 랜덤엑세스량은 DRIVING TABLE의 ROW 수에 비례하기 때문에 동일한 양이 발생한다 할 수 있다. 하지만 PLAN2 의 경우 VIEW 계획이 만들어진 걸 볼 수 있고 이는 별도의 공간을 사용한다. 이는 DRIVING TABLE이 커지면 커질수록 상당한 자원을 소모하는 작업으로 불리한 실행계획이다.