조인문을 처리할 때 조인 컬럼에 null인 데이터는 조인 엑세스가 불필요하다. 어차피 조인에 실패하기 때문이다.
따라서 조인컬럼에 is not null을 추가해 주면 불필요한 테이블 엑세스 및 조인 시도를 줄일 수 있어 쿼리 성능 향상에 도움이 된다.
조인컬럼 is not null 추가 테스트
-- t_emp 테이블 14000 row 생성
create table t_emp
as
select *
from scott.emp
,(select rownum no from dual connect by level <= 1000);
-- 나는 이미 null로 되어있다고 나옴 ,,,,
alter table t_emp modify deptno null;
-- t_emp테이블의 deptno컬럼을 모두 null값으로 변경
update t_emp set deptno = null;
commit;
-- 인덱스 생성
create index t_emp_idx on t_emp(sal);
| {code:title=통계정보 생성전 테스트 | borderStyle=solid} select /*+ ordered use_nl(d) index(e t_emp_idx) index(d pk_dept)*/ count(e.empno), count(d.dname) from t_emp e, scott.dept d where d.deptno = e. deptno and e.sal <= 2900; |
| Id | Operation | Name | E-Rows |
| 1 | SORT AGGREGATE | 1 | |
| 2 | NESTED LOOPS | 1 | |
| 3 | TABLE ACCESS BY INDEX ROWID | T_EMP | 8810 |
| INDEX RANGE SCAN | T_EMP_IDX | 8810 |
| 5 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 |
| INDEX UNIQUE SCAN | PK_DEPT | 1 |
Predicate Information (identified by operation id):
4 - access("E"."SAL"<=2900)
6 - access("D"."DEPTNO"="E"."DEPTNO")
Predicate 정보를 볼 때 아직 옵티마이저에 의해 추가된 필터 조건은 없다.
t_emp의 원본 테이블 emp에는 sal<= 2900인 사원 레코드가 10개, t_emp 테이블을 만들때 1,000번의 복제
즉, 10*1000=10,000 레코드 존재한다.
실제로 수행해 보면 아래와 같이 t_emp_idx에 1000번의 테이블 엑세스가 발생하는 것을 볼 수 있다.
Call Count CPU Time Elapsed Time Disk Query Current Rows
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: HNJTEST (ID=61)
Rows Row Source Operation
여기서 주목할 점은 10,000개의 레코드를 읽었지만 dept 테이블과의 조인 엑세스가 전혀 발생하지 않은 것(cr=0)이다.
즉, is null 조건을 따로 기술하지 않더라도 읽은 값이 null일 때는 조인 엑세스를 하지 않는다는 뜻이다.
그러나 아래와 같이 *Inner 테이블을 full table scan으로 엑세스* 할 때는 조인엑세스가 발생하며, 11g에서도 마찬가지라고 한다.
select /*+ ordered use_nl(d) index(e t_emp_idx) full(d)*/
count(e.empno), count(d.dname)
from t_emp e, scott.dept d
where d.deptno = e.deptno
and e.sal <= 2900;
Call Count CPU Time Elapsed Time Disk Query Current Rows
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: HNJTEST (ID=61)
Rows Row Source Operation
그러나 명시적으로 e.deptno에 is not null을 명시해주면 읽지 않는다.
select /*+ ordered use_nl(d) index(e t_emp_idx) full(d)*/
count(e.empno), count(d.dname)
from t_emp e, scott.dept d
where d.deptno = e.deptno
and e.sal <= 2900
and e.deptno is not null;
Call Count CPU Time Elapsed Time Disk Query Current Rows
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: HNJTEST (ID=61)
Rows Row Source Operation
|{code:title=통계정보 생성후 테스트|borderStyle=solid}
-- 통계정보생성
begin
dbms_stats.gather_table_stats(user, 't_emp', method_opt=>'for all columns',no_invalidate=>false);
end;
select /*+ ordered use_nl(d) index(e t_emp_idx) full(d)*/
count(e.empno), count(d.dname)
from t_emp e, scott.dept d
where d.deptno = e. deptno
and e.sal <= 2900;
------------------------------------------------------------
| Id | Operation | Name | E-Rows |
------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 |
| 2 | NESTED LOOPS | | 1 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T_EMP | 1 |
|* 4 | INDEX RANGE SCAN | T_EMP_IDX | 10001 |
|* 5 | TABLE ACCESS FULL | DEPT | 1 |
------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."DEPTNO" IS NOT NULL)
4 - access("E"."SAL"<=2900)
5 - filter("D"."DEPTNO"="E"."DEPTNO")
통계정보 생성 후 Predicate 정보를 보면 e. deptno is not null 조건이 추가 되었다.
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.016 0.006 0 841 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.016 0.007 0 841 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: HNJTEST (ID=61)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=841 pr=0 pw=0 time=5764 us)
0 NESTED LOOPS (cr=841 pr=0 pw=0 time=5747 us)
0 TABLE ACCESS BY INDEX ROWID T_EMP (cr=841 pr=0 pw=0 time=5737 us)
10000 INDEX RANGE SCAN T_EtMP_IDX (cr=22 pr=0 pw=0 time=20019 us)(Object ID 53095)
0 TABLE ACCESS FULL DEPT (cr=0 pr=0 pw=0 time=0 us)
10,000번 full scan하면서 발생하던 70000개의 블록I/O가 사라졌다.|
t_emp 테이블에 엑세스하면서 발생한 블록I/O는 통계정보를 수집하기 전과 똑같이 841개이다.
추가된 is not null 조건을 필터링하려면 어차피 테이블을 방문해야 하기 때문이다.
야래와 같이 t_emp_idx 인덱스에 deptno 컬럼을 추가하고 다시 수행하면 블록 I/O가 841에서 23으로 확연히 준다.
drop index t_emp_idx;
create index t_emp_idx on t_emp(sal, deptno);
select /*+ ordered use_nl(d) index(e t_emp_idx) */
count(e.empno), count(d.dname)
from t_emp e, scott.dept d
where d.deptno = e. deptno
and e.sal <= 2900;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.007 22 23 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.009 22 23 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: HNJTEST (ID=61)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=23 pr=22 pw=0 time=7131 us)
0 NESTED LOOPS (cr=23 pr=22 pw=0 time=7109 us)
0 TABLE ACCESS BY INDEX ROWID T_EMP (cr=23 pr=22 pw=0 time=7099 us)
0 INDEX RANGE SCAN T_EMP_IDX (cr=23 pr=22 pw=0 time=7088 us)(Object ID 53096)
0 TABLE ACCESS BY INDEX ROWID DEPT (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN PK_DEPT (cr=0 pr=0 pw=0 time=0 us)(Object ID 51250)
위 내용을 종합해 보면 다음과 같다.
대표적으로 between을 사용할때 옵티마이져가 임의로 조건식을 추가한다.
사전에 두 값을 비교해야 알 수 있음에도 쿼리를 실제 수행하고서야 공집합을 출력한다면 매우 비합리적이다. 이럴경우 8i까지는 사용자가 한참을 기다려야만 했다. 9i부터는 이를 방지하기 위해 옵티마이저가 임의로 필터 조건식을 추가한다.
| {code:title=옵티마이져가 필터 조건 추가 | borderStyle=solid} -- :mx보다 :mn 변수에 더 큰 값을 입력한다면 쿼리 결과는 공집합니다. select * from scott.emp where sal between :mx and :mn; {code} 아래는 :mx에 5000, :mn에 100을 입력하고 수행되었을때의 결과, 블록I/O가 전혀 발생하지 않는다. recursive calls 0 db block gets 0 consistent gets 0 --> 블록I/O가 0이다 physical reads 0 redo size 0 bytes sent via SQL*Net to client 669 bytes received via SQL*Net from client 297 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 0 rows processed 0 실행계획 상으로는 table full scan을 수행하고 나서 필터 처리가 일어나는 것 같지만 실제로는 table full scan 자체를 생략한 것이다. | {code:title=옵티마이져가 필터 조건 추가 확인 | borderStyle=solid} --- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
| FILTER | 1 | 0 | 00:00:00.01 | ||
| TABLE ACCESS FULL | EMP | 0 | 1 | 0 | 00:00:00.01 |
Predicate Information (identified by operation id):
1 - filter(TO_NUMBER(:MX)<=TO_NUMBER(:MN)) ---> 옵티마이저가 추가한 조건
2 - filter(("SAL"<=TO_NUMBER(:MN) AND "SAL">=TO_NUMBER(:MX)))
|
바인드 변수 대신 상수 값으로 조회할 때도 filter 조건이 추가되는데, 아래와 같은 9i와 10g에서 조금 다르게 처리하고 있다.
||오라클버젼||처리방식|| ||
|9i|filter(5000<=100)|통계정보가 없으면 RBO 모드로 작동해 위와 같은 쿼리변환이 일어나지 않는다.
|10g 이상|filter(NULL IS NOT NULL)| |
h2. 조건절 비교 순서
어떤 테이블에 아래와 같이 데이터가 있다.
!etc_01.jpg!
위 데이터를 아래 sql문으로 검색하면 B컬럼에 대한 조건식을 먼저 평가하는 것이 유리하다.
왜냐하면, 대부분의 레코드가 B=1000 조건을 만족하지 않아 A컬럼에 대한 비교 연산을 수행하지 않아도 되기 때문이다.
select *
from t
where a=1
and b=1000;
반대로 A=1 조건식을 먼저 평가한다면, A컬럼이 대부분 1이어서 B컬럼에 대한 비교 연산까지 그만큼 수행해야 하므로 CPU 사용량이 늘어날 것이다.
이에 옵티마이저는, 테이블 전체를 스캔하거나 인덱스를 수평적으로 스탠할 때의 Filter조건식(Filter Predicates)을 평가할 때 선택도가 낮은 컬럼을 먼저 처리하도록 순서를 조정한다.(인덱스 수직적 탐색을 위한 조건절에는 영향없음.)
이런 쿼리 변환이 작동하려면 9i, 10g를 불문하고 옵티마이저에게 시스템 통계를 제공함으로써 CPU Costing 모델을 활성화해야 한다.
옵티마이저 모드별 조건절 비교 순서
||옵티마이져 모드||조건절 비교 순서||
|RBO|where절에 기술된 반대 순서로|
|CBO (I/O Costing 모드)|where절에 기술된 순서대로|
|CBO (CPU Costing 모드)|비교 연산해야 할 일량을 고려해 옵티마이저가 결정. 선택도가 낮은 조건식부터 평가|
테스트를 해보자.
create table t
nologging
as
select 1 a, rownum b from dual connect by level <= 1000000;
select *
from t
where a=1
and b=1000;
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
| TABLE ACCESS FULL | T | 1 | 117 | 1 | 00:00:00.02 | 1804 | 1186 |
Predicate Information (identified by operation id):
select *
from t
where b=1000
and a=1;
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| TABLE ACCESS FULL | T | 1 | 117 | 1 | 00:00:00.01 | 1804 |
Predicate Information (identified by operation id):
흠... 내가 테스트 했을때는 조건식의 위치에 따라 filter조건이 변하였다... 왜그런지... 이유를 알았다.. 시스템 통계를 생성안했기 때문.
조건절의 어떤 조건을 먼저 읽느냐에 따라서도 성능의 차이가 나기 때문에, 조건절의 실행순서 제어해 주는 *ordered_predicates*를 사용한다.
ordered_predicates 힌트를 사용하면, 조건절의 순서대로 실행이 된다
h3. ordered_predicates 힌트의 또다른 용도
10g에서 OR 또는 IN-List 조건에 대한 OR-Expansion이 일어날 때 실행 순서를 제어할 목적으로 ordered_predicates 힌트를 사용할 수도 있다.
예를 들어, 9i 까지는 I/O비용모델, CPU비용모델을 불문하고 IN-List를 OR-Expansion(=Concatenation)방식으로 처리할 때 뒤쪽에 있는 값을 먼저 실행한다. 하지만 10g CPU 비용 모델 하에서는 계산된 카디널리티가 낮은 쪽을 먼저 실행한다.
실제 그런지 테스트해보자.
select /*+ use_concat(@subq 1) qb_name(subq) index(e) */ *
from scott.emp e
where deptno in (10,30) ;
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| TABLE ACCESS BY INDEX ROWID | EMP | 1 | 9 | 9 | 00:00:00.01 | 4 |
| 2 | INDEX FULL SCAN | PK_EMP | 1 | 14 | 14 | 00:00:00.01 | 2 |
Predicate Information (identified by operation id):
1 - filter(("DEPTNO"=10 OR "DEPTNO"=30))
흠........ 책에서처럼 concatenation이 안나온다... 이건 또 왜이럴까.... 모르겠다...
책에서는 10g에서 use_concat(@subq 1) qb_name(subq) 의 힌트를 주게 되면 concatenation으로 풀리고, ordered_predicates로 조건절의 순서를 제어할 수 있다고 되어있다. (페이지 557~558)
h2. 퀴즈풀이
쿼리변환에 대한 설명을 마치기 전에, 2절 뒷부분에 퀴즈로 제시했던 10개의 실행계획을 같이 재현해 보자.
퀴즈를 위해 사용한 두 테이블은 scott계정에 있는 dept와 emp테이블 이며, PK는 각각 아래와 같이 구성되어 있다.
{code:sql}
dept.dept_pk : deptno
emp.emp_pk : empno
<인덱스 구성>
dept_x01 : dname + deptno
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select /*+ index(d dept_x01) */ * from dept d
2 where dname = 'SALES'
3 and exists (
4 select /*+ no_unnest no_push_subq */ 'x'
5 from emp
6 where deptno = d.deptno
7 and job = 'SALESMAN'
8 and sal > 1000)
9 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3269917256
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 4 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_PUSH_SUBQ NO_UNNEST */ 0 FROM "EMP" "EMP"
WHERE "JOB"='SALESMAN' AND "DEPTNO"=:B1 AND "SAL">1000))
3 - access("DNAME"='SALES')
4 - filter("SAL">1000)
5 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')
메인쿼리에서 dname = 'SALES' 조건을 위해 사용된 dept_x01 인덱스에 deptno컬럼이 포함돼 있으므로 dept테이블을 액세스하기 전
인덱스 레벨에서 서브쿼리 필터링이 가능하다.
하지만 no_push_subq 힌트를 사용했기 때문에 테이블 액세스 이후에 필터링이 이루어지고 있다.
(dept_x01 인덱스에 deptno 컬럼이 없으며 no_push_subq 힌트 없이도 실행계획이 수립된다)
<인덱스 구성>
dept_x01 : dname + deptno
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select /*+ index(d dept_x01) */ * from dept d
2 where dname = 'SALES'
3 and exists (
4 select /*+ no_unnest no_push_subq */ 'x'
5 from emp
6 where deptno = d.deptno
7 and job = 'SALESMAN')
8 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2128684681
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 | 11 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_PUSH_SUBQ NO_UNNEST */ 0 FROM "EMP" "EMP"
WHERE "JOB"='SALESMAN' AND "DEPTNO"=:B1))
3 - access("DNAME"='SALES')
4 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')
인덱스 상황과 힌트, 모두 1번과 똑같지만 서브쿼리에서 sal > 1000 조건을 제거했기 때문에 테이블 액세스 없이 emp_x01인덱스만으로
서브쿼리 필터링이 이루어지고 있다. (dept_x01 인덱스에 deptno 컬럼이 없으면 no_push_subq 힌트 없이도 같은 실행계획이 수립된다)
<인덱스 구성>
dept_x01 : dname + deptno
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select /*+ index(d dept_x01) */ * from dept d
2 where dname = 'SALES'
3 and exists (
4 select /*+ no_unnest push_subq */ 'x'
5 from emp
6 where deptno = d.deptno
7 and job = 'SALESMAN'
8 and sal >= 1000)
9 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3410011884
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DNAME"='SALES')
filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 0 FROM "EMP" "EMP" WHERE
"JOB"='SALESMAN' AND "DEPTNO"=:B1 AND "SAL">=1000))
3 - filter("SAL">=1000)
4 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')
1번과 쿼리뿐만 아니라 인덱스 상황까지 동일하지만 push_subq 힌트를 사용했다.
따라서 가능한 빠른 단계에서 서브쿼리를 실행하기 위해 dept_x01 인덱스를 액세스(2번 단계)하고 나서 곧바로 서브쿼리르 필터링(3~4단계)하고 있다.
서브쿼리에서 필터링되지 않는 레코드만 dept테이블을 액세스(1번 단계) 한다.
실행계획 각 단계의 Indent를 주의 깊게 관찰하기 바란다.
emp 테이블 필터 처리가 dept_x01 인덱스의 자식 노드로 표현되고 있다.
<인덱스 구성>
dept_x01 : dname + deptno
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select /*+ index(d dept_x01) */ * from dept d
2 where dname = 'SALES'
3 and exists (
4 select /*+ no_unnest push_subq */ 'x'
5 from emp
6 where deptno = d.deptno
7 and job = 'SALESMAN')
8 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 166048891
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_X01 | 1 | 11 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DNAME"='SALES')
filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 0 FROM "EMP" "EMP"
WHERE "JOB"='SALESMAN' AND "DEPTNO"=:B1))
3 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')
3번과 인덱스 상황이 동일하고 힌트도 똑같지만 서브쿼리에서 sal > 1000 조건을 제거했기 때문
테이블 액세스없이 emp_x01 인덱스만으로 서브쿼리 필터링이 이루어지고 있습니다.
<인덱스 구성>
dept_x01 : dname
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select /*+ index(d dept_x01) */ * from dept d
2 where dname = 'SALES'
3 and exists (
4 select /*+ no_unnest push_subq */ 'x'
5 from emp
6 where deptno = d.deptno
7 and job = 'SALESMAN'
8 and sal > 1000)
9 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3234211606
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 0 FROM "EMP" "EMP"
WHERE "JOB"='SALESMAN' AND "DEPTNO"=:B1 AND "SAL">1000))
2 - access("DNAME"='SALES')
3 - filter("SAL">1000)
4 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')
1번과 3번 에서는 dept_x01인덱스에 deptno 컬럼이 포함돼 있으므로 dept테이블을 액세스 하기 이전
인덱스 레벨에서 서브쿼리 필터링이 가능했다.
하지만 여기서는 dept_x01인덱스가 dname컬럼만으로 구성했기 때문에 인덱스 레벨에서 서브쿼리 필터링이 불가능하다.
따라서 push_subq 힌트를 이용하지 않는다면 1번과 같은 실행계획이 수립됐을 것입니다.
그런데 필요없는 push_subq 힌트를 의도적으로 사용했기 때문에 1번과 다른 형태의 실행계획이 나오게 된 것이다.
하지만 이것은 표현상의 문제일 때 실제 처리과정은 1번 실행계획과 다르지 않다.
1번 실행계획보다 emp테이블에 대한 필터처리가 한 칸 더 안쪽으로 들어가면서 dept테이블에 대한 자식노드로 표현된 것에 주목하기 바란다.
테이블 레벨 필터링이 분명하다.
인덱스 레벨에서 필터링이 이루어지는 3번 실행계획에서는 서브쿼리 필터링이 dept_x01 인덱스의 자식노드로 표현된 것과도 대비해 보기 바란다.
같은 쿼리 과정에 대해 1번과 5번 두 가지 펴현 방식이 다 필요한 이유는, 메인쿼리에서 제 3의 테이블과 조인이 있을때 조인전에 서브쿼리 필터링이
먼저 이루어지는 경우를 표현하기 위해서 일것 이다. 아래 실행계획을 참조하기 바란다.
<인덱스 구성>
dept_x01 : dname
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select /*+ index(d dept_x01) */ * from dept d
2 where dname = 'SALES'
3 and exists (
4 select /*+ no_unnest push_subq */ 'x'
5 from emp
6 where deptno = d.deptno
7 and job = 'SALESMAN')
8 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3152071781
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DEPT_X01 | 1 | | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_X01 | 1 | 11 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 0 FROM "EMP" "EMP"
WHERE "JOB"='SALESMAN' AND "DEPTNO"=:B1))
2 - access("DNAME"='SALES')
3 - access("DEPTNO"=:B1 AND "JOB"='SALESMAN')
5번과 기본적으로 동일한 실행계힉이지만 서브쿼리 필터링을 위해 인덱스만 액세스하는 경우를 표현하기 위해 sal > 1000 조건을 제거 했다.
아래는 5번에서 예시한 것처럼 메인 쿼리가 또 다른 제3의 테이블과 조인이 있는 경우이며,
조인 전에 인덱스만으로 서브쿼리 필터링이 먼저 이루어지고 있다.
<인덱스 구성>
dept_x01 : dname
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select * from dept d
2 where deptno = (
3 select /*+ index(e emp_x01) */ deptno
4 from emp e
5 where sal >= 5000
6 and job = 'PRESIDENT')
7 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1561374394
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"= (SELECT /*+ INDEX ("E" "EMP_X01") */ "DEPTNO" FROM "EMP"
"E" WHERE "SAL" IS NOT NULL AND "SAL">=5000 AND "JOB"='PRESIDENT'))
3 - filter("JOB"='PRESIDENT')
4 - access("SAL">=5000 AND "SAL" IS NOT NULL)
where 조건절에 사용된 서브쿼리가
1) 메인쿼리와 상관관계에 있지 않으면서(Non-Correlated, 서브쿼리에서 메인 쿼리르 참조하지 않음)
2) 단일 로우를 리턴(single-row subquery)하는 경우로서
아래와 같은 형태에 속한다.
select * from tab1 where key1 = (select avg(col1) from tab2);
select * from tab1 where (key1, key2) =
( select col1, col2 from tab2 where col3 >= 5000 and rownum = 1);
2절 (2)항 보충설명 (p468 '서브쿼리의 또 다른 최적화 기법')에서 설명한 바와 같이,
위와 같은 형태의 서브쿼리를 만나면 옵티마이저는 서브쿼리를 Fetch가 아닌 Execute시점에 먼저 수행해
그 결과 값을 메인 쿼리에 상수로 제공하는, 아래와 같은 방식으로 처리한다.
select * from tab1 where key1 = :value1;
select * from tab1 where (key1, key2) = (:vlaue1, :value2);
실제 그렇게 처리되는지는 아래 SQL트래이스 결과를 보면 확인할 수 있다.
select * from dept d
where deptno = (
select /*+ index(e emp_x01) */ deptno
from emp e
where sal >= 5000
and job = 'PRESIDENT')
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 2 0 0
Fetch 2 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=0 pw=0 time=89 us)
1 INDEX UNIQUE SCAN DEPT_PK (cr=3 pr=0 pw=0 time=70 us)(object id 45128)
1 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=50 us)
1 INDEX RANGE SCAN EMP_X01 (cr=1 pr=0 pw=0 time=25 us)(object id 45139)
총 4블럭을 읽었는데, 그 중 2개 블록은 Execute 단계에서 읽었다.
emp테이블을 필터링하는 부분(실행계획 맨 아래쪽 두 단계)이 여기에 해당된다.
참고로 9i에서는 아래쪽 두 단계가 Row Source Operation에 표시되지 않는다.
조건절에서 서브쿼리를 'in'이 아닌 '='조건으로 비교한다는 것은 서브쿼리가 단일로우를 리턴하게 됨을 의미하고,
만약 이들 서브쿼리가 2개 이상의 로우를 리턴한다면 ORA-01427(single-row subquery returns more than one row)에러가 발생한다.
이를 방지하려고 아래처럼 서브쿼리에서 rownum = 1 같은 stopkey 조건절이 넣어주거나, select-list에 min, max, avg등 집계함수를
사용하는 경우가 일반적이다.
SQL> select * from dept d
2 where deptno = (
3 select /*+ index(e emp_x01) */ deptno
4 from emp e
5 where sal >= 5000
6 and job = 'PRESIDENT'
7 and rownum <= 1)
8 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3366355202
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"= (SELECT /*+ INDEX ("E" "EMP_X01") */ "DEPTNO" FROM "EMP"
"E" WHERE ROWNUM<=1 AND "SAL" IS NOT NULL AND "SAL">=5000 AND "JOB"='PRESIDENT'))
3 - filter(ROWNUM<=1)
4 - filter("JOB"='PRESIDENT')
5 - access("SAL">=5000 AND "SAL" IS NOT NULL)
7번 실행계획은 3번 Filter 실행계획과 서로 대비되도록 하려고 rownum =1 조건을 생략한 채 의도적으로 만들어 낸 것이다.
따라서 3번 실행계획과 헷갈리는 경우는 실제로 생기지 않을 것이다.
<인덱스 구성>
dept_x01 : deptno
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select * from dept d
2 where deptno = (
3 select /*+ index(e emp_x01) */ deptno
4 from emp e
5 where sal >= 5000 )
6 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2570959809
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_X01 | 1 | 7 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"= (SELECT /*+ INDEX ("E" "EMP_X01") */ "DEPTNO" FROM
"EMP" "E" WHERE "SAL" IS NOT NULL AND "SAL">=5000))
3 - access("SAL">=5000 AND "SAL" IS NOT NULL)
7번과 같은 상황이지만 서브쿼리에서 테이블 액세스가 발생하지 않는 경우를 보이고 있다.
4번 실행계획과 대비해보기 바란다.
7번과 마찬가지로 서브쿼리를 Fetch가 아닌 Execute 시점에 먼저 수행해 그 결과 값을 메인 쿼리애 상수로 제공하는 방식으로 처리한다.
실제 그렇게 처리되는지 아래 SQL트레이스 결과를 보면 알수 있다.
select * from dept d
where deptno = (
select /*+ index(e emp_x01) */ deptno
from emp e
where sal >= 5000 )
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 1 0 0
Fetch 2 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID DEPT (cr=3 pr=0 pw=0 time=66 us)
1 INDEX UNIQUE SCAN DEPT_PK (cr=2 pr=0 pw=0 time=46 us)(object id 45128)
1 INDEX RANGE SCAN EMP_X01 (cr=1 pr=0 pw=0 time=25 us)(object id 45139)
총 3블록을 읽었는데, 그 중 1개 블록은 Execute 단계에서 읽었다.
emp테이블을 필터링하는 부분(실행계획 맨 아래쪽 한 단계)이 여기에 해당된다.
참고로 9i에서는 아래쪽 두 단계가 Row Source Operation에 표시되지 않는다.
7번과 마찬가지로 아래처럼 stopkey조건이 붙는 경우가 더 일반적 이다.
SQL> select * from dept d
2 where deptno = (
3 select /*+ index(e emp_x01) */ deptno
4 from emp e
5 where sal >= 5000
6 and rownum <= 1)
7 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 692795560
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 | 7 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"= (SELECT /*+ INDEX ("E" "EMP_X01") */ "DEPTNO" FROM
"EMP" "E" WHERE ROWNUM<=1 AND "SAL" IS NOT NULL AND "SAL">=5000))
3 - filter(ROWNUM<=1)
4 - access("SAL">=5000 AND "SAL" IS NOT NULL)
<인덱스 구성>
dept_x01 : deptno
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select * from dept d
2 where deptno = (
3 select /*+ index(e emp_x01) */ max(deptno)
4 from emp e
5 where sal >= 5000
6 and job = 'PRESIDENT')
7 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 681784893
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 15 | | |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"= (SELECT /*+ INDEX ("E" "EMP_X01") */ MAX("DEPTNO") FROM
"EMP" "E" WHERE "SAL" IS NOT NULL AND "SAL">=5000 AND "JOB"='PRESIDENT'))
4 - filter("JOB"='PRESIDENT')
5 - access("SAL">=5000 AND "SAL" IS NOT NULL)
7번 처럼 서브쿼리에서 stopkey조건을 사용하지 않는다면 여기처럼 max등의 집계함수를 사용하는 경우가 일반적이다.
이 실행계획도 3번 실행계획과 대비해 보기 바란다.
여기서도 max값을 구하는 서브쿼리가 Execute 단계에서 실행되는지 확인해 보자.
select * from dept d
where deptno = (
select /*+ index(e emp_x01) */ max(deptno)
from emp e
where sal >= 5000
and job = 'PRESIDENT')
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 2 0 0
Fetch 2 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=0 pw=0 time=114 us)
1 INDEX UNIQUE SCAN DEPT_PK (cr=3 pr=0 pw=0 time=94 us)(object id 45128)
1 SORT AGGREGATE (cr=2 pr=0 pw=0 time=68 us)
1 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=46 us)
1 INDEX RANGE SCAN EMP_X01 (cr=1 pr=0 pw=0 time=25 us)(object id 45139)
9i에서는 아래쪽 세 단계가 Row Source Operation에 표시되지 않는다
<인덱스 구성>
dept_x01 : deptno
emp_x01 : deptno + job
<SQL과 실행계획>
SQL> select * from dept d
2 where deptno = (
3 select /*+ index(e emp_x01) */ max(deptno)
4 from emp e
5 where sal >= 5000 )
6 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1001032830
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 7 | | |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 | 7 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"= (SELECT /*+ INDEX ("E" "EMP_X01") */ MAX("DEPTNO")
FROM "EMP" "E" WHERE "SAL" IS NOT NULL AND "SAL">=5000))
4 - access("SAL">=5000 AND "SAL" IS NOT NULL)
9번과 같은 상황이지만 서브쿼리에서 테이블 액세스가 발생하지 않는 경우를 보이고 있다.
4번 실행계획과 대비해 보기 바란다.
9i에서는 SQL트레이스 Row Source Operation에서 아래 세 단계가 Row Source Operations에 표시되지 않는다.