(1) 조인 컬럼에 IS NOT NULL  조건 추가

select count(e.empno),count (d.dname)
from emp e, dept d
where d.deptno=e.deptno
and sal<=2900  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1036783580

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |    20 |     7  (15)| 00:00:01 |
|   1 |  SORT AGGREGATE               |         |     1 |    20 |            |          |
|   2 |   MERGE JOIN                  |         |     8 |   160 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                  |         |     8 |    56 |     5  (20)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL         | EMP     |     8 |    56 |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  5 - access("D"."DEPTNO"="E"."DEPTNO")
      filter("D"."DEPTNO"="E"."DEPTNO")
  6 - filter("SAL"<=2900)

• 조인컬럼 deptno 가 null 인 데이터는 조인 액세스가 불필요
• deptno is not null 조건을 추가 하면 불필요한 테이블 액세스및 조인 시도 감소 가능

select count(e.empno),count (d.dname)
from emp e, dept d
where d.deptno=e.deptno
and sal<=2900
and e.deptno is not null
and d.deptno is not null   ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1036783580

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |    20 |     7  (15)| 00:00:01 |
|   1 |  SORT AGGREGATE               |         |     1 |    20 |            |          |
|   2 |   MERGE JOIN                  |         |     8 |   160 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                  |         |     8 |    56 |     5  (20)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL         | EMP     |     8 |    56 |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  5 - access("D"."DEPTNO"="E"."DEPTNO")
      filter("D"."DEPTNO"="E"."DEPTNO")
  6 - filter("SAL"<=2900 AND "E"."DEPTNO" IS NOT NULL)

SQL>

• 실험
  

SQL> create table hwi_temp
as
select  *
from emp,(select rownum no from dual connect by level<=1000); 

Table created.


SQL> update hwi_temp set deptno=null;

14000 rows updated.


SQL> commit;

Commit complete.

select /*+ ordered use_nl(d) index(e hwi_temp_idx) index(d dept_pk) */
       count(e.empno),count (d.dname)
from hwi_temp e,scott.dept d
where d.deptno=e.deptno
and e.sal <= 2900

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.02          0         95          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.02          0         95          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
     1  SORT AGGREGATE (cr=95 pr=0 pw=0 time=0 us)
     0   NESTED LOOPS  (cr=95 pr=0 pw=0 time=0 us)
     0    NESTED LOOPS  (cr=95 pr=0 pw=0 time=0 us cost=10617 size=52 card=1)
 10000     TABLE ACCESS FULL HWI_TEMP (cr=95 pr=0 pw=0 time=15821 us cost=26 size=412932 card=10588)
     0     INDEX UNIQUE SCAN PK_DEPT (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 73180)
     0    TABLE ACCESS BY INDEX ROWID DEPT (cr=0 pr=0 pw=0 time=0 us cost=1 size=13 card=1)


※ hwi_temp table에서 10000번읽었지만 dept테이블과는 조인이 일어나지 않음

Predicate 정보 에도 별도로 추가된 필터 없음

* 실수로 hwi_temp_idx 생성 하지 않았음 ㅡㅡ;;

SQL> alter session set optimizer_features_enable='9.2.0.8';

Session altered.

SQL>
SQL> select /*+ ordered use_nl(d) index(e hwi_temp_idx) index(d dept_pk) full(d) */
       count(e.empno),count (d.dname)
from hwi_temp e,scott.dept d
where d.deptno=e.deptno
and e.sal <= 2900;

COUNT(E.EMPNO) COUNT(D.DNAME)
-------------- --------------
            0              0

SQL>

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.07       0.08          6      70095          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.07       0.09          6      70097          0           1

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
     1  SORT AGGREGATE (cr=70095 pr=6 pw=0 time=0 us)
     0   NESTED LOOPS  (cr=70095 pr=6 pw=0 time=0 us cost=9444 size=52 card=1)
 10000    TABLE ACCESS FULL HWI_TEMP (cr=95 pr=0 pw=0 time=7087 us cost=15 size=484497 card=12423)
     0    TABLE ACCESS FULL DEPT (cr=70000 pr=6 pw=0 time=0 us cost=1 size=13 card=1)


※ inner table을 full table scan으로 액세스 할때는 조인 액세스가 발생

11.2.0.0


select /*+ ordered use_nl(d) index(e hwi_temp_idx) index(d dept_pk) full(d) */
       count(e.empno),count (d.dname)
from hwi_temp e,scott.dept d
where d.deptno=e.deptno
and e.sal <= 2900;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01         86         72          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.02          6         95          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.03         92        167          0           1

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

Rows     Row Source Operation
-------  ---------------------------------------------------
     1  SORT AGGREGATE (cr=95 pr=6 pw=0 time=0 us)
     0   NESTED LOOPS  (cr=95 pr=6 pw=0 time=0 us)
     0    NESTED LOOPS  (cr=95 pr=6 pw=0 time=0 us cost=10617 size=52 card=1)
 10000     TABLE ACCESS FULL HWI_TEMP (cr=95 pr=6 pw=0 time=8100 us cost=26 size=412932 card=10588)
     0     INDEX UNIQUE SCAN PK_DEPT (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 73180)
     0    TABLE ACCESS BY INDEX ROWID DEPT (cr=0 pr=0 pw=0 time=0 us cost=1 size=13 card=1)

• 통계정보 존재유무

없을때

SQL> select /*+ ordered use_nl(d) index(e hwi_temp_idx) index(d dept_pk) full(d) */
       count(e.empno),count (d.dname)
from hwi_temp e,scott.dept d
where d.deptno=e.deptno
and e.sal <= 2900;

COUNT(E.EMPNO) COUNT(D.DNAME)
-------------- --------------
            0              0


Execution Plan
----------------------------------------------------------
Plan hash value: 1599336290

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |     1 |    52 | 10617   (1)| 00:02:08 |
|   1 |  SORT AGGREGATE               |          |     1 |    52 |            |          |
|   2 |   NESTED LOOPS                |          |       |       |            |          |
|   3 |    NESTED LOOPS               |          |     1 |    52 | 10617   (1)| 00:02:08 |
|*  4 |     TABLE ACCESS FULL         | HWI_TEMP | 10588 |   403K|    26   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    13 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  4 - filter("E"."SAL"<=2900)
  5 - access("D"."DEPTNO"="E"."DEPTNO")



begin
 dbms_stats.gather_table_stats(user,'hwi_temp',method_opt=>'for all columns', no_invalidate=>false);
end;
  /

PL/SQL procedure successfully completed.

SQL> select /*+ ordered use_nl(d) index(e hwi_temp_idx) index(d dept_pk) full(d) */
       count(e.empno),count (d.dname)
from hwi_temp e,scott.dept d
where d.deptno=e.deptno
and e.sal <= 2900;

COUNT(E.EMPNO) COUNT(D.DNAME)
-------------- --------------
            0              0


Execution Plan
----------------------------------------------------------
Plan hash value: 1599336290

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |     1 |    34 |    26   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |          |     1 |    34 |            |          |
|   2 |   NESTED LOOPS                |          |       |       |            |          |
|   3 |    NESTED LOOPS               |          |     1 |    34 |    26   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL         | HWI_TEMP |     1 |    21 |    26   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    13 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  4 - filter("E"."DEPTNO" IS NOT NULL AND "E"."SAL"<=2900)
  5 - access("D"."DEPTNO"="E"."DEPTNO")

※ 통계정보 수집시 deptno is not null 조건이 추가됨

• 조건
• 조인 컬럼의 null 값 비중이 5% 이상일때
  

• is not null 조건 추가시 이점
• 해시조인
• Build Input을 읽어 해시 맵을 만들때 더 적은 메모리 사용
• Probe Input를 읽을때 null값인 레코드를 제외함으로 해시 맵 탐색 횟수를 줄임
• 양쪽 모두 null값 비중이 클수록 효과가 큼
• 소트머지 조인
• 양쪽 테이블에서 조인 컬럼이 null 인 레코드를 제외한다면 소트 및 비교 연산 횟수가 줄어듬
  

• 결론
• is not null조건은 조인시 이점이 많음.
• 옵티마이져는 null값 비중이 5%이상일때만 쿼리 변환을 수행하므로 사용자가 직접추가
• 조인 컬럼에 null이 많을경우 default값으로 (0,’X’)등을 사용시 조인 성능이 떨어짐
  

(2) 필터 조건 추가

select * from emp
where sal between :mn and :mx

• 사용자가 :mx보다 :mn변수에 더 큰 값을 입력한다면 쿼리 결과는 공집합
• 8i 까지는 공집합임을 일수 있는데도 불구하고 실제 수행이 이루어짐
• 9i 부터 수행 방지를 위해 임의의 필터 조건식을 추가
  

스크립트 ch4_28.txt

변수조건 테스트

SQL> create table emp as select * from scott.emp;

Table created.

SQL>

variable mn number;
variable mx number;

SQL>
SQL> begin
 :mn := 5000;
 :mx := 100;
end;    /

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly;

11g

SQL> select * from emp
where  sal between :mn and :mx;
 
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3896240783

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |  1218 |     2   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |    14 |  1218 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER(:MN)<=TO_NUMBER(:MX))
   2 - filter("SAL">=TO_NUMBER(:MN) AND "SAL"<=TO_NUMBER(:MX))

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        695  bytes sent via SQL*Net to client
        408  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> alter session set optimizer_features_enable = '9.0.1';

Session altered.

통계정보없음

SQL> select * from emp
where  sal between :mn and :mx;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| EMP  |
----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("SAL"<=TO_NUMBER(:MX) AND "SAL">=TO_NUMBER(:MN))

Note
-----
  - rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
         1  consistent gets
         0  physical reads
         0  redo size
       695  bytes sent via SQL*Net to client
       408  bytes received via SQL*Net from client
         1  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         0  rows processed

SQL> analyze table emp compute statistics;

Table analyzed.

통계정보 생성후

SQL> select * from emp
where  sal between :mn and :mx; 

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3896240783

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    32 |     1 |
|*  1 |  FILTER            |      |       |       |       |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    32 |     1 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter(TO_NUMBER(:MN)<=TO_NUMBER(:MX))
  2 - filter("SAL">=TO_NUMBER(:MN) AND "SAL"<=TO_NUMBER(:MX))

Note
-----
  - cpu costing is off (consider enabling it)


Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
         0  consistent gets
         0  physical reads
         0  redo size
       695  bytes sent via SQL*Net to client
       408  bytes received via SQL*Net from client
         1  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         0  rows processed



SQL> alter session set optimizer_features_enable = '8.1.7';

Session altered.

SQL> select * from emp
where  sal between :mn and :mx;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    32 |     1 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    32 |     1 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("SAL">=TO_NUMBER(:MN) AND "SAL"<=TO_NUMBER(:MX))

Note
-----
  - cpu costing is off (consider enabling it)


Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
         1  consistent gets
         0  physical reads
         0  redo size
       695  bytes sent via SQL*Net to client
       408  bytes received via SQL*Net from client
         1  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         0  rows processed

SQL>

상수값으로 조회시

SQL> alter session set optimizer_features_enable = '11.2.0.1';

Session altered.

SQL> select * from emp
where  sal between &mn and &mx; 

Enter value for mn: 5000
Enter value for mx: 100

old   2: where  sal between &mn and &mx
new   2: where  sal between 5000 and 100

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3896240783

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    32 |     0   (0)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    32 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter(NULL IS NOT NULL)
  2 - filter("SAL"<=100 AND "SAL">=5000)


Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
         0  consistent gets
         0  physical reads
         0  redo size
       695  bytes sent via SQL*Net to client
       408  bytes received via SQL*Net from client
         1  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         0  rows processed

SQL> alter session set optimizer_features_enable = '9.0.1';

Session altered.

SQL> select * from emp
where  sal between &mn and &mx;  2
Enter value for mn: 5000
Enter value for mx: 100
old   2: where  sal between &mn and &mx
new   2: where  sal between 5000 and 100

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3896240783

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    32 |     1 |
|*  1 |  FILTER            |      |       |       |       |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    32 |     1 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter(5000<=100)
  2 - filter("SAL">=5000 AND "SAL"<=100)

Note
-----
  - cpu costing is off (consider enabling it)


Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
         0  consistent gets
         0  physical reads
         0  redo size
       695  bytes sent via SQL*Net to client
       408  bytes received via SQL*Net from client
         1  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         0  rows processed

SQL>

(3) 조건절 비교 순서

select * from t
where a=1
and b= 1000;

• 검색시 B 조건식을 먼저 평가하는것이 유리
• 대부분의 레코드가 B=1000조건을 만족하지 않아 A컬럼 비교 연산을 수행할 필요가 없음
• A=1 을 먼저 평가하면 B컬럼에 대한 비교연산까지 수행해야하므로 CPU사용량 증가

• 조건절 처리시 부등호(>)  조건을 먼저 평가하느냐 like조건을 먼저하느냐에 따라 일량 차이생김
• 옵티마이져는 테이블 전체를 스캔하거나
  인덱스를 수평적으로 스캔할때의 filter조건식을 평가할때
  선택도가 낮은 컬럼을 먼저 처리하도록 순서를 조정

• 옵티마이져 모드별 처리

create table t

nologging
as
select '1' a, level b from dual connect by level <=1000000; 

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'t',no_invalidate=>false);

PL/SQL procedure successfully completed.

SQL> set autot traceonly exp;
SQL> select * from t
 2  where a=1
 3  and b=1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |   458   (2)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     7 |   458   (2)| 00:00:06 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("B"=1000 AND TO_NUMBER("A")=1)

SQL> select * from t
 2  where b=1000
 3  and a=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |   458   (2)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     7 |   458   (2)| 00:00:06 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("B"=1000 AND TO_NUMBER("A")=1)

SQL>

• cpu costing 모드에서 조건절 비교 순서 제어

select /*+ ORDERED_PREDICATES */ * from t
where a=1
and b=1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |   463   (3)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     7 |   463   (3)| 00:00:06 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter(TO_NUMBER("A")=1 AND "B"=1000)

SQL>

• 9i
• 시스템 통계 삭제
• exec dbms_stats.delete_system_stats;

• 10g
  
• i/o 비용 모델로 전환
• alter session set “_optimizer_cost_model”=io;

• rbo 모드로 변환
• alter session set optimizer_mode=rule;
  

ordered_predicates 힌트의 또 다른 용도

• 10g
• or또는 in-list조건에 대한 or-expansion이 일어날대 실행 순서를 제어할 목적으로 사용
• cpu모델 하에서  계산된카디널리티가 낮은 쪽을 먼저 실행
• 9i
  
• i/o,cpu비용모델 불문하고 in-list,or-expansion방식 처리시에는 뒤쪽값을 우선실행
  

10g 에서 실험

SQL> select /*+ use_concat(@subq 1) qb_name(subq) index(e) */ *
from emp e
where deptno in (10,30); 

Execution Plan
----------------------------------------------------------
Plan hash value: 2959818684

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     9 |   333 |     4   (0)| 00:00:01 |
|   1 |  CONCATENATION               |          |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP      |     3 |   111 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_IDX1 |     3 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| EMP      |     6 |   222 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | EMP_IDX1 |     6 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("DEPTNO"=10)
  5 - access("DEPTNO"=30)

∴  30을 in-list뒤쪽에 기술했음에도, Predicate 정보상 카디널리티가 낮은 10이 위쪽에 올라감

SQL> select /*+ use_concat(@subq 1) qb_name(subq) index(e) ordered_predicates */ *
from emp e
where deptno in (10,30); 

Execution Plan
----------------------------------------------------------
Plan hash value: 2959818684

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     9 |   333 |     4   (0)| 00:00:01 |
|   1 |  CONCATENATION               |          |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP      |     6 |   222 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_IDX1 |     6 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| EMP      |     3 |   111 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | EMP_IDX1 |     3 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("DEPTNO"=30)
  5 - access("DEPTNO"=10)

∴ ordered_predicates 힌트를 사용하면 9i처럼 inlist 뒤쪽에 있는 값을 먼저 수행

SQL> select /*+ use_concat(@subq 1) qb_name(subq) index(e) no_cpu_costing */ *
from emp e
where deptno in (10,30); 

Execution Plan
----------------------------------------------------------
Plan hash value: 2959818684

-------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     9 |   333 |     4 |
|   1 |  CONCATENATION               |          |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP      |     6 |   222 |     2 |
|*  3 |    INDEX RANGE SCAN          | EMP_IDX1 |     6 |       |     1 |
|   4 |   TABLE ACCESS BY INDEX ROWID| EMP      |     3 |   111 |     2 |
|*  5 |    INDEX RANGE SCAN          | EMP_IDX1 |     3 |       |     1 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("DEPTNO"=30)
  5 - access("DEPTNO"=10)

Note
-----
  - cpu costing is off (consider enabling it)

SQL>

∴ no_cpu_costing 힌트를 사용해 IO비용모델로 변경해도 in-list 뒤쪽부터 실행

※ _optimizer_cost_model 파라미터를 ‘IO’ 로 설정

• 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
• 작성자: 남송휘 (tofriend)
• 최초작성일: 2011년 6월 7일
• 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
• 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^