꿈꾸는 개발자, DBA 커뮤니티 구루비

쿼리 변환이란?

쿼리 변환
- 쿼리 옵티마이저가 SQL을 분석해 의미적으로 동일(→ 같은 결과를 리턴)하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것
- 본격적으로 최적화를 하기에 앞서 사전 정지(整地)작업을 하는 것 → 논리적 최적화
- 비용기반 옵티마이저의 서브 엔진 중 Query Transformer가 담당 (옵티마이저→[03. 옵티마이저 원리 (01. 옵티마이저 ~ 05. 카디널리티)|03. 옵티마이저 원리 (01. 옵티마이저 ~ 05. 카디널리티)])

쿼리 변환의 종류

구분	쿼리 변환	비고
1	서브쿼리 Unnesting	H→C
2	뷰 Merging	H→C
3	조건절 Pushing	H
4	조건절 이행	H
5	공통 표현식 제거	H
6	Outer 조인을 Inner 조인으로 변환	H
7	실체화 뷰 쿼리로 재작성	C
8	Star 변화	C
9	Outer 조인 뷰에 대한 조인 조건 Pushdown	C
10	OR-expansion	C
11	조인제거	11g
12	집계 서브쿼리를 분석함수로 변환	11g
13	집합 연산을 조인으로 변환	11g

쿼리 변환 방식

휴리스틱(Heuristic) 쿼리 변환	결과만 보장된다면 무조건 쿼리 변환 수행 일종의 규칙 기반(Rule-based) 최적화 기법 경험적으로 항상 더 나은 성능을 보일 것이라는 옵티마이저 개발팀의 판단이 반영된 것
비용기반(Cost-based) 쿼리 변환	변환된 쿼리의 비용이 더 낮을 때만 그것을 사용 그렇지 않을 때는 원본 쿼리 그대로 두고 최적화를 수행

서브쿼리 Unnesting

서브쿼리: 하나의 SQL 문장 내에서 괄호로 묶인 별도의 쿼리 블록(Query Block) → 쿼리에 내장된 또 다른 쿼리
서브쿼리의 분류

인라인 뷰(Inline View)	from절에 나타나는 서브쿼리
중첩된 서브쿼리(Nested Subquery)	결과집합을 한정하기 위해 where절에 사용된 서브쿼리 서브쿼리가 메인쿼리에 있는 컬럼을 참조하는 형태 → 상관관계 있는(Correlated) 서브쿼리
스칼라 서브쿼리(Scalar Subquery)	한 레코드당 정확히 하나의 컬럼 값만을 리턴하는 것이 특징 주로 select-list에서 사용되지만 몇 가지 예외 사항을 뺀다면 컬럼이 올 수 있는 대부부 위치에서 사용 가능

서브쿼리 Unnesting의 의미
- 중첩된 서브쿼리를 풀어내는 것
- 풀어내지 않고 그대로 두는 것 → '서브쿼리 No-Unnesting'
- 중첩된 서브쿼리는 메인쿼리와 부모와 자식이라는 종속적이고 계층적인 관계가 존재

 
select * from emp a
where exists (
    select 'x' from dept
    where deptno = a.deptno
    )
and sal >
    (select avg(sal) from emp b
     where exists (
         select 'x' from salgrade
         where b.sal between losal and hisal
         and grad = 4)
    )

- 논리적인 관점에서 그 처리과정은 IN, Exists를 불문하고 필터 방식 → 메인 쿼리에서 읽히는 레코드마다 서브쿼리를 반복 수행하면서 조건에 맞지 않는 데이터를 골라내는 것
- 서브쿼리를 처리하는데 있어 필터 방식이 항상 최적의 수행속도를 보장하지 못하므로 옵티마이저는 아래 둘 중 하나를 선택

1	동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화 → 서브쿼리 Unnesting 일반 조인문처럼 다양한 최적화 기법을 사용 가능할 수 있음(옵티마이저는 서브쿼리 Unnestig을 선호)
2	서브쿼리를 Unnesting하지 않고 원래대로 둔 상태에서 최적화 메인쿼리와 서브쿼리를 별도의 서브플랜으로 구분해 각각 최적화를 수행하며, 이때 서브쿼리에 필터 오퍼레이션이 나타남

- 휴리스틱 쿼리 변환이었으나 10g부터 비용기반 쿼리 변환으로 변경
- 관련 힌트

unnest	서브쿼리를 Unnesting 함으로써 조인방식으로 최적화하도록 유도
no_unnest	서브쿼리를 그대로 둔 상태에서 필터 방식으로 최적화하도록 유도

서브쿼리 Unnesting 기본 예시
{CODE:SQL}
select * from emp
where deptno in (select /*+ NO_UNNEST */ deptno from dept)

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

0	SELECT STATEMENT		1		14	00:00:00.01	7
1	FILTER		1		14	00:00:00.01	7
2	TABLE ACCESS FULL	EMP	1	14	14	00:00:00.01	4
3	INDEX UNIQUE SCAN	DEPT_PK	3	1	3	00:00:00.01	3

Predicate Information (identified by operation id):

---

1 - filter( IS NOT NULL)
3 - access("DEPTNO"=:B1)

select * from emp
where deptno in (select /*+ UNNEST */ deptno from dept)

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

0	SELECT STATEMENT		1		14	00:00:00.01	8
1	NESTED LOOPS		1	14	14	00:00:00.01	8
2	TABLE ACCESS FULL	EMP	1	14	14	00:00:00.01	4
3	INDEX UNIQUE SCAN	DEPT_PK	14	1	14	00:00:00.01	4

Predicate Information (identified by operation id):

---

3 - access("DEPTNO"="DEPTNO")

{CODE}

Unnesting된 쿼리의 조인 순서 조정
{CODE:SQL}

SELECT /*+ LEADING(EMP) */ *
FROM EMP WHERE DEPTNO IN (SELECT /*+ UNNEST */ DEPTNO FROM DEPT)

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

0	SELECT STATEMENT		1		14	00:00:00.01	8
1	NESTED LOOPS		1	14	14	00:00:00.01	8
2	TABLE ACCESS FULL	EMP	1	14	14	00:00:00.01	4
3	INDEX UNIQUE SCAN	DEPT_PK	14	1	14	00:00:00.01	4

Predicate Information (identified by operation id):

---

3 - access("DEPTNO"="DEPTNO")

SELECT /*+ LEADING(DEPT) / * FROM EMP WHERE DEPTNO IN (SELECT /+
UNNEST */ DEPTNO FROM DEPT)

-

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

-

0	SELECT STATEMENT		1		14	00:00:00.01	7
1	NESTED LOOPS		1		14	00:00:00.01	7
2	NESTED LOOPS		1	14	14	00:00:00.01	5
3	INDEX FULL SCAN	DEPT_PK	1	4	4	00:00:00.01	2
4	INDEX RANGE SCAN	EMP_DEPTNO_IDX	4	5	14	00:00:00.01	3
5	TABLE ACCESS BY INDEX ROWID	EMP	14	4	14	00:00:00.01	2

-

Predicate Information (identified by operation id):

---

4 - access("DEPTNO"="DEPTNO")

SELECT /*+ ORDERED */ *
FROM EMP WHERE DEPTNO IN (SELECT /*+ UNNEST */ DEPTNO FROM DEPT)

Plan hash value: 482744827

-

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

-

0	SELECT STATEMENT		1		14	00:00:00.01	7
1	NESTED LOOPS		1		14	00:00:00.01	7
2	NESTED LOOPS		1	14	14	00:00:00.01	5
3	INDEX FULL SCAN	DEPT_PK	1	4	4	00:00:00.01	2
4	INDEX RANGE SCAN	EMP_DEPTNO_IDX	4	5	14	00:00:00.01	3
5	TABLE ACCESS BY INDEX ROWID	EMP	14	4	14	00:00:00.01	2

-

Predicate Information (identified by operation id):

---

4 - access("DEPTNO"="DEPTNO")

SELECT /*+ LEADING(DEPT@QB1) */ *
FROM EMP WHERE DEPTNO IN (SELECT /*+ UNNEST QB_NAME(QB1) */ DEPTNO FROM DEPT)

-

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

-

0	SELECT STATEMENT		1		14	00:00:00.01	7
1	NESTED LOOPS		1		14	00:00:00.01	7
2	NESTED LOOPS		1	14	14	00:00:00.01	5
3	INDEX FULL SCAN	DEPT_PK	1	4	4	00:00:00.01	2
4	INDEX RANGE SCAN	EMP_DEPTNO_IDX	4	5	14	00:00:00.01	3
5	TABLE ACCESS BY INDEX ROWID	EMP	14	4	14	00:00:00.01	2

-

Predicate Information (identified by operation id):

---

4 - access("DEPTNO"="DEPTNO")

{CODE}

서브쿼리가 M쪽 집합이거나 Nonuique 인덱스일 때

1	1쪽 집합임을 확실할 수 없는 서브쿼리 쪽 테이블이 드라이빙된다면, 먼저 sort unique 오퍼레이션을 수행함으로써 1쪽 집합으로 만든 다음에 조인
2	메인 쿼리 쪽 테이블이 드라이빙된다면 세미 조인(Semi Join)방식으로 조인한다.


alter table dept drop primary key;
create index dept_deptno_idx on dept(deptno);

select /*+ leading(dept) */ *   from emp 
where deptno in (select deptno from dept)
 
Plan hash value: 3327316407
 
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |     14 |00:00:00.01 |       6 |       |       |          |
|   1 |  NESTED LOOPS                |                 |      1 |        |     14 |00:00:00.01 |       6 |       |       |          |
|   2 |   NESTED LOOPS               |                 |      1 |     14 |     14 |00:00:00.01 |       4 |       |       |          |
|   3 |    SORT UNIQUE               |                 |      1 |      4 |      4 |00:00:00.01 |       1 |  2048 |  2048 | 2048  (0)|
|   4 |     INDEX FULL SCAN          | DEPT_DEPTNO_IDX |      1 |      4 |      4 |00:00:00.01 |       1 |       |       |          |
|*  5 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX  |      4 |      5 |     14 |00:00:00.01 |       3 |       |       |          |
|   6 |   TABLE ACCESS BY INDEX ROWID| EMP             |     14 |      4 |     14 |00:00:00.01 |       2 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("DEPTNO"="DEPTNO")
 
 
select *   from emp 
where deptno in (select deptno from dept)
 
------------------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |      1 |        |     14 |00:00:00.01 |       6 |
|   1 |  NESTED LOOPS SEMI |                 |      1 |     14 |     14 |00:00:00.01 |       6 |
|   2 |   TABLE ACCESS FULL| EMP             |      1 |     14 |     14 |00:00:00.01 |       4 |
|*  3 |   INDEX RANGE SCAN | DEPT_DEPTNO_IDX |      3 |      4 |      3 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("DEPTNO"="DEPTNO")

- NL 세미 조인 처리
  - Outer(=Driving) 테이블의 한 로우가 Inner 테이블의 한 로우와 조인에 성공하는 순간 진행을 멈추고 Outer 테이블의 다음 로우를 계속 처리

필터 오퍼레이션과 세미조인의 캐싱 효과
- 서브쿼리를 No Unnesting으로 처리 시 필터 최적화 기법: 서브쿼리 수행 결과 캐싱

구분	8i	9i	10g
캐싱 수	256	256	1024


********************************************************************************

select count(*) from t_emp t
where exists (select /*+ no_unnest */ 'x' from dept
    where deptno = t.deptno and loc is not null)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          3          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         18          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         21          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 92

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=18 pr=0 pw=0 time=0 us)
   1400   FILTER  (cr=18 pr=0 pw=0 time=4069 us)
   1400    TABLE ACCESS FULL T_EMP (cr=12 pr=0 pw=0 time=890 us cost=5 size=18200 card=1400)
      3    TABLE ACCESS BY INDEX ROWID DEPT (cr=6 pr=0 pw=0 time=0 us cost=2 size=11 card=1)
      3     INDEX RANGE SCAN DEPT_DEPTNO_IDX (cr=3 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 79145)

********************************************************************************

- NL 세미 조인 캐싱 효과(10g부터)


********************************************************************************

select count(*) from t_emp t
where exists (select /*+ unnest nl_sj */ 'x' from dept
    where deptno = t.deptno and loc is not null)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          2          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.00          0         30          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.00       0.00          0         32          0           2

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 92

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=15 pr=0 pw=0 time=0 us)
   1400   NESTED LOOPS SEMI (cr=15 pr=0 pw=0 time=2925 us cost=1405 size=33600 card=1400)
   1400    TABLE ACCESS FULL T_EMP (cr=12 pr=0 pw=0 time=1399 us cost=5 size=18200 card=1400)
      3    TABLE ACCESS BY INDEX ROWID DEPT (cr=3 pr=0 pw=0 time=0 us cost=1 size=44 card=4)
      3     INDEX RANGE SCAN DEPT_DEPTNO_IDX (cr=2 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 79145)




********************************************************************************

Anti 조인
- 서브쿼리 NO UNNESTING 시 필터방식으로 처리


select * from dept d 
where  not exists    (select /*+ no_unnest */ 'x' from emp where deptno = d.deptno)
 
-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |      1 |        |      1 |00:00:00.01 |       7 |
|*  1 |  FILTER            |                |      1 |        |      1 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS FULL| DEPT           |      1 |      4 |      4 |00:00:00.01 |       3 |
|*  3 |   INDEX RANGE SCAN | EMP_DEPTNO_IDX |      4 |      2 |      3 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( IS NULL)
   3 - access("DEPTNO"=:B1)

exists 필터	조인에 성공하는 (서브) 레코드를 만나는 순간 결과집합에 담고 다른 (메인) 레코드로 이동
not exists 필터	조인에 성공하는 (서브) 레코드를 만나는 순간 버리고 다음 (메인) 레코드로 이동 조인에 성공하는 (서브) 레코드가 하나도 없을 때만 결과집합에 수집

- 서브쿼리 UNNESTING 시 Anti 조인 방식으로 처리


select * from dept d where  not exists    (select /*+ unnest nl_aj 
*/'x' from emp where deptno = d.deptno)
 
-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |      1 |        |      1 |00:00:00.01 |       5 |
|   1 |  NESTED LOOPS ANTI |                |      1 |      1 |      1 |00:00:00.01 |       5 |
|   2 |   TABLE ACCESS FULL| DEPT           |      1 |      4 |      4 |00:00:00.01 |       3 |
|*  3 |   INDEX RANGE SCAN | EMP_DEPTNO_IDX |      4 |      9 |      3 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("DEPTNO"="D"."DEPTNO")


select * from dept d where  not exists    (select /*+ unnest merge_aj 
*/ 'x' from emp where deptno = d.deptno)

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                |      1 |        |      1 |00:00:00.01 |       4 |       |       |          |
|   1 |  MERGE JOIN ANTI    |                |      1 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
|   2 |   SORT JOIN         |                |      1 |      4 |      4 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   3 |    TABLE ACCESS FULL| DEPT           |      1 |      4 |      4 |00:00:00.01 |       3 |       |       |          |
|*  4 |   SORT UNIQUE       |                |      4 |     14 |      3 |00:00:00.01 |       1 |  2048 |  2048 | 2048  (0)|
|   5 |    INDEX FULL SCAN  | EMP_DEPTNO_IDX |      1 |     14 |     14 |00:00:00.01 |       1 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("DEPTNO"="D"."DEPTNO")
       filter("DEPTNO"="D"."DEPTNO")
 

select * from dept d where  not exists    (select /*+ unnest hash_aj */ 
'x' from emp where deptno = d.deptno)
 
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |      1 |        |      1 |00:00:00.01 |       4 |       |       |          |
|*  1 |  HASH JOIN ANTI    |                |      1 |      1 |      1 |00:00:00.01 |       4 |  1000K|  1000K|  752K (0)|
|   2 |   TABLE ACCESS FULL| DEPT           |      1 |      4 |      4 |00:00:00.01 |       3 |       |       |          |
|   3 |   INDEX FULL SCAN  | EMP_DEPTNO_IDX |      1 |     14 |     14 |00:00:00.01 |       1 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("DEPTNO"="D"."DEPTNO")

집계 서브쿼리 제거(10g부터)



select d.deptno, d.dname, e.empno, e.ename, e.sal from   dept d, emp e 
where  d.deptno = e.deptno and    e.sal = (select max(sal) from emp 
where deptno = d.deptno)
 
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |      4 |00:00:00.01 |       4 |       |       |          |
|*  1 |  VIEW                          | VW_WIF_1       |      1 |     14 |      4 |00:00:00.01 |       4 |       |       |          |
|   2 |   WINDOW BUFFER                |                |      1 |     14 |     14 |00:00:00.01 |       4 |  2048 |  2048 | 2048  (0)|
|   3 |    MERGE JOIN                  |                |      1 |     14 |     14 |00:00:00.01 |       4 |       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMP            |      1 |     14 |     14 |00:00:00.01 |       2 |       |       |          |
|   5 |      INDEX FULL SCAN           | EMP_DEPTNO_IDX |      1 |     14 |     14 |00:00:00.01 |       1 |       |       |          |
|*  6 |     SORT JOIN                  |                |     14 |      4 |     14 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|   7 |      TABLE ACCESS FULL         | DEPT           |      1 |      4 |      4 |00:00:00.01 |       2 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("VW_COL_6" IS NOT NULL)
   6 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")


select /*+ opt_param('_remove_aggr_subquery', 'false') */ d.deptno,         <<< 집계 서브쿼리 제거 기능 OFF
d.dname, e.empno, e.ename, e.sal from   dept d, emp e where  d.deptno = 
e.deptno and    e.sal = (select max(sal) from emp where deptno = 
d.deptno)

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |      4 |00:00:00.01 |      11 |       |       |          |
|   1 |  NESTED LOOPS                  |                |      1 |      1 |      4 |00:00:00.01 |      11 |       |       |          |
|   2 |   NESTED LOOPS                 |                |      1 |     15 |     14 |00:00:00.01 |       9 |       |       |          |
|   3 |    MERGE JOIN                  |                |      1 |      3 |      3 |00:00:00.01 |       6 |       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT           |      1 |      4 |      4 |00:00:00.01 |       4 |       |       |          |
|   5 |      INDEX FULL SCAN           | DEPT_PK        |      1 |      4 |      4 |00:00:00.01 |       2 |       |       |          |
|*  6 |     SORT JOIN                  |                |      4 |      3 |      3 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|   7 |      VIEW                      | VW_SQ_1        |      1 |      3 |      3 |00:00:00.01 |       2 |       |       |          |
|   8 |       HASH GROUP BY            |                |      1 |      3 |      3 |00:00:00.01 |       2 |  1200K|  1200K| 1264K (0)|
|   9 |        TABLE ACCESS FULL       | EMP            |      1 |     14 |     14 |00:00:00.01 |       2 |       |       |          |
|* 10 |    INDEX RANGE SCAN            | EMP_DEPTNO_IDX |      3 |      5 |     14 |00:00:00.01 |       3 |       |       |          |
|* 11 |   TABLE ACCESS BY INDEX ROWID  | EMP            |     14 |      1 |      4 |00:00:00.01 |       2 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("ITEM_1"="D"."DEPTNO")
       filter("ITEM_1"="D"."DEPTNO")
  10 - access("D"."DEPTNO"="E"."DEPTNO")
  11 - filter("E"."SAL"="MAX(SAL)")
 

alter session set "_remove_aggr_subquery" = false;                          <<< 집계 서브쿼리 제거 기능 OFF

select d.deptno, d.dname, e.empno, e.ename, e.sal from   dept d, emp e 
where  d.deptno = e.deptno and    e.sal = (select max(sal) from emp 
where deptno = d.deptno)
 
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |      4 |00:00:00.01 |      11 |       |       |          |
|   1 |  NESTED LOOPS                  |                |      1 |      1 |      4 |00:00:00.01 |      11 |       |       |          |
|   2 |   NESTED LOOPS                 |                |      1 |     15 |     14 |00:00:00.01 |       9 |       |       |          |
|   3 |    MERGE JOIN                  |                |      1 |      3 |      3 |00:00:00.01 |       6 |       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT           |      1 |      4 |      4 |00:00:00.01 |       4 |       |       |          |
|   5 |      INDEX FULL SCAN           | DEPT_PK        |      1 |      4 |      4 |00:00:00.01 |       2 |       |       |          |
|*  6 |     SORT JOIN                  |                |      4 |      3 |      3 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|   7 |      VIEW                      | VW_SQ_1        |      1 |      3 |      3 |00:00:00.01 |       2 |       |       |          |
|   8 |       HASH GROUP BY            |                |      1 |      3 |      3 |00:00:00.01 |       2 |  1200K|  1200K|  753K (0)|
|   9 |        TABLE ACCESS FULL       | EMP            |      1 |     14 |     14 |00:00:00.01 |       2 |       |       |          |
|* 10 |    INDEX RANGE SCAN            | EMP_DEPTNO_IDX |      3 |      5 |     14 |00:00:00.01 |       3 |       |       |          |
|* 11 |   TABLE ACCESS BY INDEX ROWID  | EMP            |     14 |      1 |      4 |00:00:00.01 |       2 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("ITEM_1"="D"."DEPTNO")
       filter("ITEM_1"="D"."DEPTNO")
  10 - access("D"."DEPTNO"="E"."DEPTNO")
  11 - filter("E"."SAL"="MAX(SAL)")

Pushig 서브쿼리
- 서브쿼리를 No Unnesting으로 처리할 때 실행계획 상 가능한 앞 단계에서 서브쿼리 필터링이 처리되도록 강제하는 것(옵티마이저 힌트: push_subq)
- Pushing 서브쿼리는 Unnesting 되지 않은 서브쿼리에만 작동: push_subq 힌트는 항상 no_unnest 힌트와 같이 기술
- 버전에 따른 힌트 위치 변경

구분	9i	10g
힌트 위치	메인 쿼리 select절	서브 쿼리 select절


create table dept as select * from scott.dept;

create table emp1 as
select * from scott.emp, (select rownum no from dual connect by level <= 1000);

create table emp2 as select * from emp1;

alter table emp1 add constraint emp1_pk primary key(no, empno);

alter table emp2 add constraint emp2_pk primary key(no, empno);

********************************************************************************

select /*+ leading(e1) use_nl(e2) */ sum(e1.sal), sum(e2.sal)
from   emp1 e1, emp2 e2
where  e1.no = e2.no
and    e1.empno = e2.empno
and    exists (select /*+ NO_UNNEST NO_PUSH_SUBQ */ 'x'
          from dept where deptno = e1.deptno
          and  loc = 'NEW YORK')

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        2      0.06       0.06          0      14351          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.06       0.06          0      14351          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 92

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=14351 pr=0 pw=0 time=0 us)
   3000   FILTER  (cr=14351 pr=0 pw=0 time=59980 us)
  14000    NESTED LOOPS  (cr=14342 pr=0 pw=0 time=70122 us)
  14000     NESTED LOOPS  (cr=342 pr=0 pw=0 time=37542 us cost=14033 size=378000 card=14000)
  14000      TABLE ACCESS FULL EMP1 (cr=95 pr=0 pw=0 time=12726 us cost=29 size=210000 card=14000)
  14000      INDEX UNIQUE SCAN EMP2_PK (cr=247 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 79155)
  14000     TABLE ACCESS BY INDEX ROWID EMP2 (cr=14000 pr=0 pw=0 time=0 us cost=1 size=12 card=1)
      1    TABLE ACCESS FULL DEPT (cr=9 pr=0 pw=0 time=0 us cost=3 size=11 card=1)

********************************************************************************

select /*+ leading(e1) use_nl(e2) */ sum(e1.sal), sum(e2.sal)
from   emp1 e1, emp2 e2
where  e1.no = e2.no
and    e1.empno = e2.empno
and    exists (select /*+ NO_UNNEST PUSH_SUBQ */ 'x'
          from dept where deptno = e1.deptno
          and  loc = 'NEW YORK')

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        2      0.01       0.01          0       3351          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.02          0       3351          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 92

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=3351 pr=0 pw=0 time=0 us)
   3000   NESTED LOOPS  (cr=3351 pr=0 pw=0 time=19993 us)
   3000    NESTED LOOPS  (cr=351 pr=0 pw=0 time=13870 us cost=729 size=18900 card=700)
   3000     TABLE ACCESS FULL EMP1 (cr=104 pr=0 pw=0 time=7997 us cost=29 size=10500 card=700)
      1      TABLE ACCESS FULL DEPT (cr=9 pr=0 pw=0 time=0 us cost=3 size=11 card=1)
   3000     INDEX UNIQUE SCAN EMP2_PK (cr=247 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 79155)
   3000    TABLE ACCESS BY INDEX ROWID EMP2 (cr=3000 pr=0 pw=0 time=0 us cost=1 size=12 card=1)




********************************************************************************

뷰 Merging 이란?
- 아래 <쿼리1>의 뷰 쿼리 블록을 액세스 쿼리 블록(뷰를 참조하는 쿼리 블록)과의 머지(merge)과정을 거쳐 <쿼리2)와 같이 형태로 변환하는 것


<쿼리1>

select *
from (select * from emp where job='SALESMAN') a
    , (select * from dept where loc='CHICAGO') b
where a.deptno = b.deptno

<쿼리2>
select *
from emp a, dept b
where a.deptno = b.deptno
and a.job='SALESMAN'
and b.loc='CHICAGO'

- 옵티마이저 힌트: merge, no_merge
- 뷰 Merging 종류

구분	정의	뷰 Merging 처리
단순 뷰(Simple View)	조건절과 조인문만을 포함	no_merge 힌트를 사용하지 않는 한 항상 Merging
복합 뷰(Complex View)	group by절이나 distinct 연산을 포함	파라미터 설정(_complex_view_merging = true) 또는 힌트 사용에 의해서만 뷰 Merging

- Merging 불가능 뷰(Non-mergeable Views): 집합(set) 연산자, connect by, rownum 등을 포함하는 복합 뷰

단순 뷰(Simple View) Mergeing


create or replace view emp_salesman
as
select empno, ename, job, mgr, hiredate, sal, comm, deptno
from   emp
where  job = 'SALESMAN' ;


select /*+ leading(e) */ e.empno, e.ename, e.job, e.mgr, e.sal, d.dname 
from   emp_salesman e, dept d where  d.deptno = e.deptno and    e.sal 
>= 1500
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |      1 |        |      2 |00:00:00.01 |       8 |
|   1 |  NESTED LOOPS                 |             |      1 |        |      2 |00:00:00.01 |       8 |
|   2 |   NESTED LOOPS                |             |      1 |      3 |      2 |00:00:00.01 |       6 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP         |      1 |      3 |      2 |00:00:00.01 |       4 |
|*  4 |     INDEX RANGE SCAN          | EMP_SAL_IDX |      1 |     13 |      8 |00:00:00.01 |       2 |
|*  5 |    INDEX UNIQUE SCAN          | DEPT_PK     |      2 |      1 |      2 |00:00:00.01 |       2 |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPT        |      2 |      1 |      2 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("JOB"='SALESMAN')
   4 - access("SAL">=1500)
   5 - access("D"."DEPTNO"="DEPTNO")


select /*+ leading(e) no_merge(e) */ e.empno, e.ename, e.job, e.mgr, 
e.sal, d.dname from   emp_salesman e, dept d where  d.deptno = e.deptno 
and    e.sal >= 1500
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |      1 |        |      2 |00:00:00.01 |       8 |
|   1 |  NESTED LOOPS                  |              |      1 |        |      2 |00:00:00.01 |       8 |
|   2 |   NESTED LOOPS                 |              |      1 |      3 |      2 |00:00:00.01 |       6 |
|   3 |    VIEW                        | EMP_SALESMAN |      1 |      3 |      2 |00:00:00.01 |       4 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| EMP          |      1 |      3 |      2 |00:00:00.01 |       4 |
|*  5 |      INDEX RANGE SCAN          | EMP_SAL_IDX  |      1 |     13 |      8 |00:00:00.01 |       2 |
|*  6 |    INDEX UNIQUE SCAN           | DEPT_PK      |      2 |      1 |      2 |00:00:00.01 |       2 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | DEPT         |      2 |      1 |      2 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter("JOB"='SALESMAN')
   5 - access("SAL">=1500)
   6 - access("D"."DEPTNO"="E"."DEPTNO")

복합 뷰(Complex View) Merging
- 아래 항목을 포함하는 복합 뷰는 _complex_view_merging 파라미터를 true로 설정할 때만 Merging
  1. group by 절
  2. select-list에 distinct 연산자 포함
- _complex_view_merging 초기 설정

구분	초기설정값	쿼리 변환
8i	false	휴리스틱 쿼리 변환
9i	true	휴리스틱 쿼리 변환
10g	true	비용기반 쿼리 변화

- Non-mergeable Views
  1. 집합(set) 연산자(union, union all, intersect, minus)
  2. connect by절
  3. ROWNUM pseudo 컬럼
  4. select-list에 집계 함수(avg, count, max, min, sum) 사용: group by 없이 전체를 집계하는 경우
  5. 분석 함수(Analytic Function)
- 복합 뷰 예시


select d.dname, avg_sal_dept 
  from dept d     
      ,(select deptno, avg(sal) avg_sal_dept       
         from emp       
        group by deptno) e 
where d.deptno = e.deptno and d.loc = 'CHICAGO'
 
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      1 |00:00:00.01 |       7 |       |       |          |
|   1 |  MERGE JOIN                  |         |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
|   3 |    INDEX FULL SCAN           | DEPT_PK |      1 |      4 |      4 |00:00:00.01 |       2 |       |       |          |
|*  4 |   SORT JOIN                  |         |      1 |      3 |      1 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   5 |    VIEW                      |         |      1 |      3 |      3 |00:00:00.01 |       3 |       |       |          |
|   6 |     HASH GROUP BY            |         |      1 |      3 |      3 |00:00:00.01 |       3 |   899K|   899K| 1227K (0)|
|   7 |      TABLE ACCESS FULL       | EMP     |      1 |     14 |     14 |00:00:00.01 |       3 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("D"."LOC"='CHICAGO')
   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
 
Note
-----
   - cardinality feedback used for this statement


select /*+ merge(e) */ d.dname, avg_sal_dept from dept d     ,(select 
deptno, avg(sal) avg_sal_dept       from emp       group by deptno) e 
where d.deptno = e.deptno and d.loc = 'CHICAGO'
 
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |      1 |00:00:00.01 |       5 |       |       |          |
|   1 |  HASH GROUP BY                |         |      1 |      3 |      1 |00:00:00.01 |       5 |   833K|   833K|  465K (0)|
|   2 |   MERGE JOIN                  |         |      1 |      5 |      6 |00:00:00.01 |       5 |       |       |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   4 |     INDEX FULL SCAN           | DEPT_PK |      1 |      4 |      4 |00:00:00.01 |       1 |       |       |          |
|*  5 |    SORT JOIN                  |         |      1 |     14 |      6 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   6 |     TABLE ACCESS FULL         | EMP     |      1 |     14 |     14 |00:00:00.01 |       3 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("D"."LOC"='CHICAGO')
   5 - access("D"."DEPTNO"="DEPTNO")
       filter("D"."DEPTNO"="DEPTNO")

비용기반 쿼리 변환의 필요성
- 뷰 Merging은 9i까지 휴리스틱 쿼리 변환이었으나 10g부터 비용기반 쿼리 변환으로 변경
- 뷰 Merging을 제어하기 위한 파라미터(10g부터): _optimizer_cost_based_transformation
- 비용기반 서브쿼리 Unnesting도 이 파라미터에 의해 제어
- 조건절(Predicate) Pushing: _optimizer_push_pred_cost_based
- connect by도 비용기반 하에 쿼리 변환: _optimizer_connect_by_cost_based(10.2.0.2부터)

Merging 되지 않은 뷰의 처리방식
- 뷰 Merging이 되지 않는 경우
  1. 뷰 Merging을 시행했을 때 비용이 더 증가한다고 판단했을 때
  2. 부정확한 결과집합이 만들어질 가능성이 있을 때
- 뷰 Merging이 되지 않을 때 2차적으로 조건절 Pushing을 시도
- 조건절 Pushing도 실패 시 뷰 쿼리 블록을 개별적으로 최적화하고, 거기서 생성된 서브플랜을 전체 실행계획을 생성하는 데 사용


********************************************************************************

select /*+ leading(e) use_nl(d) */ *
from   dept d
     ,(select * from emp) e
where  e.deptno = d.deptno

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        2      0.00       0.00          0         21          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         21          0          14

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        14         14         14  NESTED LOOPS  (cr=21 pr=0 pw=0 time=88 us cost=17 size=812 card=14)
        14         14         14   NESTED LOOPS  (cr=7 pr=0 pw=0 time=218 us cost=17 size=812 card=14)
        14         14         14    TABLE ACCESS FULL EMP (cr=3 pr=0 pw=0 time=113 us cost=3 size=532 card=14)
        14         14         14    INDEX UNIQUE SCAN DEPT_PK (cr=4 pr=0 pw=0 time=43 us cost=0 size=0 card=1)(object id 92029)
        14         14         14   TABLE ACCESS BY INDEX ROWID DEPT (cr=14 pr=0 pw=0 time=51 us cost=1 size=20 card=1)

********************************************************************************

select /*+ leading(e) use_nl(d) */ *
from   dept d
     ,(select /*+ NO_MERGE */ * from emp) e
where  e.deptno = d.deptno

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        2      0.00       0.00          0         21          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         21          0          14

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        14         14         14  NESTED LOOPS  (cr=21 pr=0 pw=0 time=91 us cost=17 size=1498 card=14)
        14         14         14   NESTED LOOPS  (cr=7 pr=0 pw=0 time=249 us cost=17 size=1498 card=14)
        14         14         14    VIEW  (cr=3 pr=0 pw=0 time=155 us cost=3 size=1218 card=14)
        14         14         14     TABLE ACCESS FULL EMP (cr=3 pr=0 pw=0 time=127 us cost=3 size=532 card=14)
        14         14         14    INDEX UNIQUE SCAN DEPT_PK (cr=4 pr=0 pw=0 time=40 us cost=0 size=0 card=1)(object id 92029)
        14         14         14   TABLE ACCESS BY INDEX ROWID DEPT (cr=14 pr=0 pw=0 time=40 us cost=1 size=20 card=1)

********************************************************************************

select /*+ leading(d) use_nl(e) */ *
from   dept d
     ,(select /*+ NO_MERGE */ * from emp) e
where  e.deptno = d.deptno

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        2      0.00       0.00          0         12          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         12          0          14

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        14         14         14  NESTED LOOPS  (cr=12 pr=0 pw=0 time=71 us cost=15 size=1498 card=14)
         4          4          4   TABLE ACCESS FULL DEPT (cr=3 pr=0 pw=0 time=54 us cost=3 size=80 card=4)
        14         14         14   VIEW  (cr=9 pr=0 pw=0 time=72 us cost=3 size=348 card=4)
        56         56         56    TABLE ACCESS FULL EMP (cr=9 pr=0 pw=0 time=66 us cost=3 size=532 card=14)

********************************************************************************

select /*+ leading(d) use_nl(e) */ *
from   dept d
     ,(select /*+ NO_MERGE */ * from emp ORDER BY ENAME) e
where  e.deptno = d.deptno

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        2      0.00       0.00          0          5          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          5          0          14

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        14         14         14  NESTED LOOPS  (cr=5 pr=0 pw=0 time=103 us cost=19 size=1498 card=14)
         4          4          4   TABLE ACCESS FULL DEPT (cr=3 pr=0 pw=0 time=59 us cost=3 size=80 card=4)
        14         14         14   VIEW  (cr=2 pr=0 pw=0 time=91 us cost=4 size=348 card=4)
        56         56         56    SORT ORDER BY (cr=2 pr=0 pw=0 time=63 us cost=4 size=532 card=14)
        14         14         14     TABLE ACCESS FULL EMP (cr=2 pr=0 pw=0 time=15 us cost=3size=532 card=14)

********************************************************************************

조건절 Pushing
- 조건절 Pushing: 뷰를 참조하는 쿼리 블록의 조건절을 뷰 쿼리 블록 안으로 Pusing하는 기능
- 뷰 Merging 실패 시 옵티마이저는 2차적으로 조건절 Pushing 시도
- 뷰 Merging 실패
  1. 복합 뷰(Complex View) Merging 기능이 비활성화
  2. 사용자가 no_merge 힌트 사용
  3. Non-mergeable View: 뷰 Merging 시행하면 부정확한 결과 가능성
  4. 비용기반 쿼리 변환이 작동해 No Merging 선택(10g 이후)
- 조건절 Pushing 종류

조건절(Predicate) Pushdown	쿼리 블록 밖에 있는 조건들을 쿼리 블록 안쪽으로 밀어 넣는 것
조건절(Predicate) Pullup	쿼리 블록 안에 있는 조건들을 쿼리 블록 밖으로 내오는 것 그리고 다시 다른 쿼리 블록에 Pushdown 하는데 사용(→Predicate Move Around)
조인 조건(Join Predicate) Pushdown	NL조인 수행 중에 드라이빙 테이블에서 읽은 값을 건건이 Inner쪽(right side) 뷰 쿼리 블록 안으로 밀어 놓는 것

- 관련 힌트와 파라미터
  - 조건절 Pushdown과 조건절 Pullup은 항상 더 나은 성능 보장하므로 별도의 힌트를 제공하는 않음
  - 조인 조건 Pushdown: push_pred, no_push_pred
  - 조건절(Predicate) Pushing 파라미터: _optimizer_push_pred_cost_based
- Non-pushable View
  1. ROWNUM pseudo 컬럼
  2. 분석 함수(Analytic Function)
- 조건절 Pushdown
  1. GROUP BY 절을 포함한 뷰에 대한 조건절 Pushdown


alter session set "_complex_view_merging"= false;

select deptno, avg_sal   from  (select deptno, avg(sal) avg_sal from 
emp group by deptno) a where  deptno = 30
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  VIEW                         |                |      1 |      1 |      1 |00:00:00.01 |       2 |
|   2 |   SORT GROUP BY NOSORT        |                |      1 |      1 |      1 |00:00:00.01 |       2 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP            |      1 |      5 |      6 |00:00:00.01 |       2 |
|*  4 |     INDEX RANGE SCAN          | EMP_DEPTNO_IDX |      1 |      5 |      6 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("DEPTNO"=30)


select /*+ no_merge(a) */         b.deptno, b.dname, a.avg_sal   from  
(select deptno, avg(sal) avg_sal from emp group by deptno) a      , 
dept b where  a.deptno = b.deptno              and    b.deptno = 30
 
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |      1 |00:00:00.01 |       4 |       |       |          |
|   1 |  NESTED LOOPS                  |                |      1 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID  | DEPT           |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  3 |    INDEX UNIQUE SCAN           | DEPT_PK        |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|   4 |   VIEW                         |                |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   5 |    SORT GROUP BY               |                |      1 |      1 |      1 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|   6 |     TABLE ACCESS BY INDEX ROWID| EMP            |      1 |      5 |      6 |00:00:00.01 |       2 |       |       |          |
|*  7 |      INDEX RANGE SCAN          | EMP_DEPTNO_IDX |      1 |      5 |      6 |00:00:00.01 |       1 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("B"."DEPTNO"=30)
   7 - access("DEPTNO"=30)

1. 1. UNION 집합 연산자를 포함함 뷰에 대한 조건절 Pushdown


create index emp_x1 on emp(deptno, job);

select * from  (select deptno, empno, ename, job, sal, sal * 1.1 sal2, 
hiredate         from emp        where  job = 'CLERK'        union all  
      select deptno, empno, ename, job, sal, sal * 1.2 sal2, hiredate   
      from emp        where  job = 'SALESMAN' ) v where  v.deptno = 30
 
Plan hash value: 3103675373
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |      1 |        |      5 |00:00:00.01 |       5 |
|   1 |  VIEW                         |        |      1 |      4 |      5 |00:00:00.01 |       5 |
|   2 |   UNION-ALL                   |        |      1 |        |      5 |00:00:00.01 |       5 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      2 |      1 |00:00:00.01 |       3 |
|*  4 |     INDEX RANGE SCAN          | EMP_X1 |      1 |      2 |      1 |00:00:00.01 |       2 |
|   5 |    TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      2 |      4 |00:00:00.01 |       2 |
|*  6 |     INDEX RANGE SCAN          | EMP_X1 |      1 |      2 |      4 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("DEPTNO"=30 AND "JOB"='CLERK')
   6 - access("DEPTNO"=30 AND "JOB"='SALESMAN')

select /*+ ordered use_nl(e) */ d.dname, e.* from   dept d      
,(select deptno, empno, ename, job, sal, sal * 1.1 sal2, hiredate from 
emp        where  job = 'CLERK'        union all        select deptno, 
empno, ename, job, sal, sal * 1.2 sal2, hiredate from emp        where  
job = 'SALESMAN' ) e where  e.deptno = d.deptno and    d.deptno = 30

 
----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |      1 |        |      5 |00:00:00.01 |       7 |
|   1 |  NESTED LOOPS                  |         |      1 |      4 |      5 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | DEPT    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX UNIQUE SCAN           | DEPT_PK |      1 |      1 |      1 |00:00:00.01 |       1 |
|   4 |   VIEW                         |         |      1 |      4 |      5 |00:00:00.01 |       5 |
|   5 |    UNION-ALL                   |         |      1 |        |      5 |00:00:00.01 |       5 |
|   6 |     TABLE ACCESS BY INDEX ROWID| EMP     |      1 |      2 |      1 |00:00:00.01 |       3 |
|*  7 |      INDEX RANGE SCAN          | EMP_X1  |      1 |      2 |      1 |00:00:00.01 |       2 |
|   8 |     TABLE ACCESS BY INDEX ROWID| EMP     |      1 |      2 |      4 |00:00:00.01 |       2 |
|*  9 |      INDEX RANGE SCAN          | EMP_X1  |      1 |      2 |      4 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("D"."DEPTNO"=30)
   7 - access("DEPTNO"=30 AND "JOB"='CLERK')
   9 - access("DEPTNO"=30 AND "JOB"='SALESMAN')

- 조건절 Pullup


select * from     (select deptno, avg(sal) from emp where deptno = 10 
group by deptno) e1  ,(select deptno, min(sal), max(sal) from emp group 
by deptno) e2 where  e1.deptno = e2.deptno
 
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |      1 |00:00:00.01 |       4 |       |       |          |
|*  1 |  HASH JOIN                     |                |      1 |      1 |      1 |00:00:00.01 |       4 |  1245K|  1245K|  413K (0)|
|   2 |   VIEW                         |                |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   3 |    HASH GROUP BY               |                |      1 |      1 |      1 |00:00:00.01 |       2 |  1116K|  1116K|  763K (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID| EMP            |      1 |      5 |      3 |00:00:00.01 |       2 |       |       |          |
|*  5 |      INDEX RANGE SCAN          | EMP_DEPTNO_IDX |      1 |      5 |      3 |00:00:00.01 |       1 |       |       |          |
|   6 |   VIEW                         |                |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   7 |    HASH GROUP BY               |                |      1 |      1 |      1 |00:00:00.01 |       2 |   993K|   993K|  766K (0)|
|   8 |     TABLE ACCESS BY INDEX ROWID| EMP            |      1 |      5 |      3 |00:00:00.01 |       2 |       |       |          |
|*  9 |      INDEX RANGE SCAN          | EMP_DEPTNO_IDX |      1 |      5 |      3 |00:00:00.01 |       1 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("E1"."DEPTNO"="E2"."DEPTNO")
   5 - access("DEPTNO"=10)
   9 - access("DEPTNO"=10)


select /*+ opt_param('_pred_move_around', 'false') */ * from     
(select deptno, avg(sal) from emp where deptno = 10 group by deptno) e1 
 ,(select deptno, min(sal), max(sal) avg_sal from emp group by deptno) 
e2 where  e1.deptno = e2.deptno
 
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                |      1 |        |      1 |00:00:00.01 |       4 |       |       |          |
|   1 |  NESTED LOOPS                   |                |      1 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
|   2 |   VIEW                          |                |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   3 |    HASH GROUP BY                |                |      1 |      1 |      1 |00:00:00.01 |       2 |  1116K|  1116K|  766K (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID | EMP            |      1 |      5 |      3 |00:00:00.01 |       2 |       |       |          |
|*  5 |      INDEX RANGE SCAN           | EMP_DEPTNO_IDX |      1 |      5 |      3 |00:00:00.01 |       1 |       |       |          |
|   6 |   VIEW PUSHED PREDICATE         |                |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  7 |    FILTER                       |                |      1 |        |      1 |00:00:00.01 |       2 |       |       |          |
|   8 |     SORT AGGREGATE              |                |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   9 |      TABLE ACCESS BY INDEX ROWID| EMP            |      1 |      5 |      3 |00:00:00.01 |       2 |       |       |          |
|* 10 |       INDEX RANGE SCAN          | EMP_DEPTNO_IDX |      1 |      5 |      3 |00:00:00.01 |       1 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("DEPTNO"=10)
   7 - filter(COUNT(*)>0)
  10 - access("DEPTNO"="E1"."DEPTNO")



select /*+ opt_param('_pred_move_around', 'false') no_push_pred(e2) */ 
* from     (select deptno, avg(sal) from emp where deptno = 10 group by 
deptno) e1  ,(select deptno, min(sal), max(sal) avg_sal from emp group 
by deptno) e2 where  e1.deptno = e2.deptno
 
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |      1 |00:00:00.01 |       4 |       |       |          |
|*  1 |  HASH JOIN                     |                |      1 |      1 |      1 |00:00:00.01 |       4 |  1245K|  1245K|  691K (0)|
|   2 |   VIEW                         |                |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   3 |    HASH GROUP BY               |                |      1 |      1 |      1 |00:00:00.01 |       2 |  1116K|  1116K|  481K (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID| EMP            |      1 |      5 |      3 |00:00:00.01 |       2 |       |       |          |
|*  5 |      INDEX RANGE SCAN          | EMP_DEPTNO_IDX |      1 |      5 |      3 |00:00:00.01 |       1 |       |       |          |
|   6 |   VIEW                         |                |      1 |      3 |      3 |00:00:00.01 |       2 |       |       |          |
|   7 |    HASH GROUP BY               |                |      1 |      3 |      3 |00:00:00.01 |       2 |   993K|   993K|  731K (0)|
|   8 |     TABLE ACCESS FULL          | EMP            |      1 |     14 |     14 |00:00:00.01 |       2 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("E1"."DEPTNO"="E2"."DEPTNO")
   5 - access("DEPTNO"=10)

- 조인 조건 Pushdown


select /*+ no_merge(e) push_pred(e) */ * 
  from  dept d
      , (select empno, ename, deptno from emp) e 
where e.deptno(+) = d.deptno 
    and   d.loc = 'CHICAGO'
 
Plan hash value: 468580690
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |      1 |        |      6 |00:00:00.01 |       7 |
|   1 |  NESTED LOOPS OUTER           |                |      1 |      4 |      6 |00:00:00.01 |       7 |
|*  2 |   TABLE ACCESS FULL           | DEPT           |      1 |      1 |      1 |00:00:00.01 |       3 |
|   3 |   VIEW PUSHED PREDICATE       |                |      1 |      1 |      6 |00:00:00.01 |       4 |
|   4 |    TABLE ACCESS BY INDEX ROWID| EMP            |      1 |      5 |      6 |00:00:00.01 |       4 |
|*  5 |     INDEX RANGE SCAN          | EMP_DEPTNO_IDX |      1 |      5 |      6 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("D"."LOC"='CHICAGO')
   5 - access("DEPTNO"="D"."DEPTNO")

- - 조인 조건 Pushdown을 제어하는 힌트와 파타미터

Hint	push_pred	조인 조건 Pushdown을 유도
Hint	no_push_pred	조인 조건 Pushdown을 방지
Parameter	_push_join_predicate	뷰 Merging에 실패한 뷰 안쪽으로 조인 조건을 Pushdown하는 기능을 활성화 union 또는 union all을 포함하는 Non-mergeable 뷰에 대해서는 별로 파라미터 제공
Parameter	_push_join_union_view	union all을 포함하는 Non-mergeable 뷰 안쪽으로 조인 조건을 Pushdown하는 기능을 활성화
Parameter(10g)	_push_join_union_view2	union을 포함하는 Non-mergeable 뷰 안쪽으로 조인 조건을 Pushdown하는 기능을 활성화

1. 1. GROUP BY 절을 포함한 뷰에 대한 조인 조건 Pushdown(11g부터)


select /*+ leading(d) use_nl(e) no_merge(e) push_pred(e) index(e (deptno)) */         d.deptno, d.dname, e.avg_sal  
  from   dept d      
      , (select deptno, avg(sal) avg_sal from emp group by deptno) e 
where  e.deptno(+) = d.deptno
 
Plan hash value: 4224228980
 
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |      4 |00:00:00.01 |       7 |       |       |          |
|   1 |  NESTED LOOPS OUTER            |                |      1 |      4 |      4 |00:00:00.01 |       7 |       |       |          |
|   2 |   TABLE ACCESS FULL            | DEPT           |      1 |      4 |      4 |00:00:00.01 |       3 |       |       |          |
|   3 |   VIEW PUSHED PREDICATE        |                |      4 |      1 |      3 |00:00:00.01 |       4 |       |       |          |
|   4 |    SORT GROUP BY               |                |      4 |      1 |      3 |00:00:00.01 |       4 |  2048 |  2048 | 2048  (0)|
|   5 |     TABLE ACCESS BY INDEX ROWID| EMP            |      4 |      5 |     14 |00:00:00.01 |       4 |       |       |          |
|*  6 |      INDEX RANGE SCAN          | EMP_DEPTNO_IDX |      4 |      5 |     14 |00:00:00.01 |       2 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("DEPTNO"="D"."DEPTNO")

1. 1. UNION 집합 연산을 포함한 뷰에 대한 조인 조건 Pushdown(10g부터)


create index dept_idx on dept(loc);
create index emp_idx on emp(deptno, job);

select /*+ push_pred(e) */ d.dname, e.* 
  from   dept d      
      ,(select deptno, empno, ename, job, sal, sal * 1.1 sal2, hiredate 
          from emp       
        where  job = 'CLERK'        
        union all        
        select deptno, empno, ename, job, sal, sal * 1.2 sal2, hiredate 
          from emp        
        where  job = 'SALESMAN' ) e 
where  e.deptno = d.deptno 
    and    d.loc = 'CHICAGO'
 
Plan hash value: 590722732
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      1 |        |      5 |00:00:00.01 |       8 |
|   1 |  NESTED LOOPS                  |          |      1 |      2 |      5 |00:00:00.01 |       8 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | DEPT     |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN            | DEPT_IDX |      1 |      1 |      1 |00:00:00.01 |       2 |
|   4 |   VIEW                         |          |      1 |      1 |      5 |00:00:00.01 |       5 |
|   5 |    UNION ALL PUSHED PREDICATE  |          |      1 |        |      5 |00:00:00.01 |       5 |
|   6 |     TABLE ACCESS BY INDEX ROWID| EMP      |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  7 |      INDEX RANGE SCAN          | EMP_IDX  |      1 |      2 |      1 |00:00:00.01 |       2 |
|   8 |     TABLE ACCESS BY INDEX ROWID| EMP      |      1 |      1 |      4 |00:00:00.01 |       2 |
|*  9 |      INDEX RANGE SCAN          | EMP_IDX  |      1 |      2 |      4 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("D"."LOC"='CHICAGO')
   7 - access("DEPTNO"="D"."DEPTNO" AND "JOB"='CLERK')
   9 - access("DEPTNO"="D"."DEPTNO" AND "JOB"='SALESMAN')

1. 1. Outer 조인 뷰에 대한 조인 조건 Pushdown
    - Outer 조인에서 Inner쪽 집합이 뷰 쿼리 블록일 때, 뷰 안에서 참조하는 테이블 개수에 따라 옵티마이저 처리방법

뷰 안에서 참조하는 테이블이 단 하나일 때	뷰 Merging을 시도
뷰 내에서 참조하는 테이블이 두 개 이상일 때	조인 조건식을 뷰 안쪽으로 Pushing하려고 시도


select /*+ push_pred(b) */         a.empno, a.ename, a.sal, a.hiredate, b.deptno, b.dname, b.loc, a.job 
  from   emp a      
      ,(select e.empno, d.deptno, d.dname, d.loc        
          from   emp e
              , dept d         
         where d.deptno = e.deptno         
             and    e.sal >= 1000         
             and    d.loc in ( 'CHICAGO', 'NEW YORK' ) 
        ) b 
where  b.empno(+) = a.empno 
    and    a.hiredate >= to_date('19810901', 'yyyymmdd')
 
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |      1 |        |      8 |00:00:00.01 |      16 |
|   1 |  NESTED LOOPS OUTER            |                  |      1 |     14 |      8 |00:00:00.01 |      16 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | EMP              |      1 |     14 |      8 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN            | EMP_HIREDATE_IDX |      1 |     14 |      8 |00:00:00.01 |       2 |
|   4 |   VIEW PUSHED PREDICATE        |                  |      8 |      1 |      4 |00:00:00.01 |      12 |
|   5 |    NESTED LOOPS                |                  |      8 |      1 |      4 |00:00:00.01 |      12 |
|*  6 |     TABLE ACCESS BY INDEX ROWID| EMP              |      8 |      1 |      7 |00:00:00.01 |       6 |
|*  7 |      INDEX UNIQUE SCAN         | EMP_PK           |      8 |      1 |      8 |00:00:00.01 |       4 |
|*  8 |     TABLE ACCESS BY INDEX ROWID| DEPT             |      7 |      1 |      4 |00:00:00.01 |       6 |
|*  9 |      INDEX UNIQUE SCAN         | DEPT_PK          |      7 |      1 |      7 |00:00:00.01 |       4 |
-------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("A"."HIREDATE">=TO_DATE(' 1981-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   6 - filter("E"."SAL">=1000)
   7 - access("E"."EMPNO"="A"."EMPNO")
   8 - filter(("D"."LOC"='CHICAGO' OR "D"."LOC"='NEW YORK'))
   9 - access("D"."DEPTNO"="E"."DEPTNO")

조건절 이행
- '(A=B)이고 (B=C)이면 (A=C)이다'는 추론을 통해 새로운 조건절을 내부적으로 생성해 주는 쿼리변환
- '(A>B)이고 (B>C)이면 (A>C)이다'와 같은 추론도 가능


select * from dept d, emp e where e.job = 'MANAGER' and   e.deptno = 10 
 and   d.deptno = e.deptno
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      1 |00:00:00.01 |       5 |
|   1 |  NESTED LOOPS                |         |      1 |      2 |      1 |00:00:00.01 |       5 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX UNIQUE SCAN         | DEPT_PK |      1 |      1 |      1 |00:00:00.01 |       1 |
|   4 |   TABLE ACCESS BY INDEX ROWID| EMP     |      1 |      2 |      1 |00:00:00.01 |       3 |
|*  5 |    INDEX RANGE SCAN          | EMP_IDX |      1 |      2 |      1 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("D"."DEPTNO"=10)
   5 - access("E"."DEPTNO"=10 AND "E"."JOB"='MANAGER')

- 만약 조건절 이행이 작용해 조인 조건이 사라지고 이로 인해 비용 잘못 계산되는 문제가 생긴다면 아래와 같이 가공


select * from dept d, emp e where e.job = 'MANAGER' and   d.deptno = 
e.deptno and   e.deptno = 10  and   d.deptno = 10
 
Plan hash value: 60938376
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      1 |00:00:00.01 |       5 |
|   1 |  NESTED LOOPS                |         |      1 |      2 |      1 |00:00:00.01 |       5 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX UNIQUE SCAN         | DEPT_PK |      1 |      1 |      1 |00:00:00.01 |       1 |
|   4 |   TABLE ACCESS BY INDEX ROWID| EMP     |      1 |      2 |      1 |00:00:00.01 |       3 |
|*  5 |    INDEX RANGE SCAN          | EMP_IDX |      1 |      2 |      1 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("D"."DEPTNO"=10)
   5 - access("E"."DEPTNO"=10 AND "E"."JOB"='MANAGER')
       filter("D"."DEPTNO"="E"."DEPTNO")
 

select * from dept d, emp e where e.job = 'MANAGER' and   e.deptno = 10 
 and   d.deptno = e.deptno + 0
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |      1 |00:00:00.01 |       5 |
|   1 |  NESTED LOOPS                 |         |      1 |      2 |      1 |00:00:00.01 |       5 |
|   2 |   NESTED LOOPS                |         |      1 |      2 |      1 |00:00:00.01 |       4 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP     |      1 |      2 |      1 |00:00:00.01 |       3 |
|*  4 |     INDEX RANGE SCAN          | EMP_IDX |      1 |      2 |      1 |00:00:00.01 |       2 |
|*  5 |    INDEX UNIQUE SCAN          | DEPT_PK |      1 |      1 |      1 |00:00:00.01 |       1 |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPT    |      1 |      1 |      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("E"."DEPTNO"=10 AND "E"."JOB"='MANAGER')
   5 - access("D"."DEPTNO"="E"."DEPTNO"+0)

조건절 이행이 효과를 발휘하는 사례


select *
from 상품이력 a, 주문 b
where b.거래일자 between '20090101' and '20090131'
and a.상품번호 = b.상품번호
and b.거래일자 between a.시작일자 and a.종료일자

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    44 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    44 |     5  (20)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| 상품 |     1 |    25 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| 주문 |     1 |    19 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."상품번호"="B"."상품번호")
       filter("B"."거래일자">="A"."시작일자" AND "B"."거래일자"<="A"."종료일자")
   2 - filter("A"."종료일자">='20090101' AND "A"."시작일자"<='20090131')
   3 - filter("B"."거래일자">='20090101' AND "B"."거래일자"<='20090131')

- 튜닝사례


/*
인덱스 정보
IP주소목록_PK: IP주소
IP주소목록_X01: 시작IP주소

바인드변수
 :strtIpAddr := '192.168.000.001'
 :endIpAddr  := '192.168.000.255'
*/

SELECT IP주소, IP연결일자, 시작IP주소, 종료IP주소
     , ISP명, IP등록일자, IP사용기관ID, IP사용기관명, IP사용시도명
     , 사용기관주소, 사용기관우편번호, IP책임자명, IP책임자전화번호
  FROM IP주소목록
 WHERE 시작IP주소 >= :strtIpAddr
   AND 종료IP주소 <= :endIpAddr

Call     Count  CPU Time Elapsed Time      Disk      Query    Current        Rows
------- ------  -------- ------------ ---------- ---------- ----------  ----------
Parse        1     0.000         0.00          0          0          0           0
Execute      1     0.000         0.00          0          0          0           0
Fetch        9    32.820     1922.797     341291    6940276          0         106
------- ------  -------- ------------ ---------- ---------- ----------  ----------
Total       11    32.820     1922.797     341291    6940276          0         106

Rows    Row Source Operation
------- ----------------------------------------------------
    106 TABLE ACCESS BY INDEX ROWID IP주소목록 (cr=6940276 pr=341291 pw=0 ... )
8362619  INDEX RANGE SCAN IP주소목록_X01 (cr=27980 pr=27968 pw=0 time=33450495 us)

/*
인덱스 정보
IP주소목록_PK: IP주소
IP주소목록_X01: 시작IP주소 → 시작IP주소, 종료IP주소

바인드변수
 :strtIpAddr := '192.168.000.001'
 :endIpAddr  := '192.168.000.255'
*/

SELECT IP주소, IP연결일자, 시작IP주소, 종료IP주소
     , ISP명, IP등록일자, IP사용기관ID, IP사용기관명, IP사용시도명
     , 사용기관주소, 사용기관우편번호, IP책임자명, IP책임자전화번호
  FROM IP주소목록
 WHERE 시작IP주소 >= :strtIpAddr  
   AND 종료IP주소 <= :endIpAddr   
   AND 시작IP주소 <= 종료IP주소

/*
:strtIpAddr <= 시작IP주소 <= 종료IP주소 <= :endIpAddr

 WHERE 시작IP주소 BETWEEN :strtIpAddr AND :endIpAddr
   AND 종료IP주소 BETWEEN :strtIpAddr AND :endIpAddr
*/   

Call     Count  CPU Time Elapsed Time      Disk      Query    Current        Rows
------- ------  -------- ------------ ---------- ---------- ----------  ----------
Parse        1     0.000        0.000          0          0          0           0
Execute      1     0.000        0.000          0          0          0           0
Fetch        9     0.000        0.001          0         55          0         106
------- ------  -------- ------------ ---------- ---------- ----------  ----------
Total       11     0.000        0.001          0         55          0         106

Rows    Row Source Operation
------- ----------------------------------------------------
      0 STATEMENT
    106  FILTER (cr=55 pr=0 pw=0 time=37 us)
    106   TABLE ACCESS BY INDEX ROWID IP주소목록 (cr=55 pr=0 pw=0 time=34 us)
    106    INDEX RANGE SCAN IP주소목록_X01 (cr=12 pr=0 pw=0 time=654 us)

조인 제거(10g부터)
- 1:M 관계인 두 테이블을 조인하는 쿼리문에서 조인문을 제외한 어디에서도 1쪽 테이블을 참조하지 않는다면, 쿼리 수행 시 1쪽 테이블은 결과집합에 영향을 미치지 않기 때문에 읽지 않아도 된다.
- 위 특성을 이용해 옵티마이저는 M쪽 테이블만 읽도록 쿼리를 변화하는데, 이를 '조인 제거(Join Elimination)' 또는 '테이블 제거(Table Elimination)'라고 한다.


alter table emp add constraint fk_deptno foreign key(deptno) references dept(deptno);

select e.empno, e.ename, e.deptno, e.sal, e.hiredate from   dept d, emp 
e where  d.deptno = e.deptno
 
------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     14 |00:00:00.01 |       3 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("E"."DEPTNO" IS NOT NULL)

- IN, EXISTS 서브쿼리 시 조인 제거


alter session set "_optimizer_join_elimination_enabled" = false;

select * from emp e where  deptno in (select /*+ eliminate_join(dept) 
*/ deptno from dept)

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     14 |00:00:00.01 |       3 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DEPTNO" IS NOT NULL)
   

select * from emp e where  deptno in (select deptno from dept)
 
------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |     14 |00:00:00.01 |       7 |
|   1 |  NESTED LOOPS      |           |      1 |     14 |     14 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS FULL| EMP       |      1 |     14 |     14 |00:00:00.01 |       3 |
|*  3 |   INDEX UNIQUE SCAN| DEPTNO_PK |     14 |      1 |     14 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("DEPTNO"="DEPTNO")

select * from emp e where  exists (select /*+ eliminate_join(dept) */ 
'x' from dept where deptno = e.deptno)
 
------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     14 |00:00:00.01 |       3 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("E"."DEPTNO" IS NOT NULL)
 

select * from emp e where  exists (select  'x' from dept where deptno = 
e.deptno)
 
------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |     14 |00:00:00.01 |       5 |
|   1 |  NESTED LOOPS SEMI |           |      1 |     14 |     14 |00:00:00.01 |       5 |
|   2 |   TABLE ACCESS FULL| EMP       |      1 |     14 |     14 |00:00:00.01 |       3 |
|*  3 |   INDEX UNIQUE SCAN| DEPTNO_PK |      3 |      4 |      3 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("DEPTNO"="E"."DEPTNO")

- Outer 조인 시 조인 제거(11g부터)


alter table emp drop constraint fk_deptno;

select e.empno, e.ename, e.sal, e.hiredate from emp e, dept d where 
d.deptno(+) = e.deptno
 
------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     14 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------

04. 쿼리 변환 (01. 쿼리 변환이란 ? ~ 06. 조건절 이행) (by jangsu84) [2016.07.19]

쿼리 변환이란?

서브쿼리 Unnesting