by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

7. OR-Expansion




OR-Expansion 기본
OR 조건이 분리 가능하다면? OR-Expansion!
LNNVL
LNNVL(1=1)    : FALSE
LNNVL(1=2)    : TRUE
LNNVL(Null=1) : TRUE
준비

-- 준비
drop table emp;
drop table dept;

create table emp as select * from scott.emp;
create table dept as select * from scott.dept;

alter table emp add constraint emp_pk primary key (empno);
alter table dept add constraint dept_pk primary key (deptno);
alter table emp add constraint emp_fk_dept foreign key (deptno) references dept (deptno);

create index emp_n1 on emp (job);
create index emp_n2 on emp (deptno);

exec dbms_stats.gather_table_stats (ownname => 'uadmin', tabname => 'emp' , degree => 1, cascade => TRUE);
exec dbms_stats.gather_table_stats (ownname => 'uadmin', tabname => 'dept' , degree => 1, cascade => TRUE);
OR-Expansion 자동 OR-Expansion 수동
select /*+ gather_plan_statistics */ * from emp
 where job = 'CLERK' or deptno = 20;
select /*+ gather_plan_statistics */ * from emp
 where job = 'CLERK'
union all
select * from emp
 where deptno = 20
   and lnnvl(job='CLERK'); -- job='CLERK' 인 경우만 빼고 참

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |      7 |00:00:00.01 |       6 |
|   1 |  CONCATENATION               |        |      1 |        |      7 |00:00:00.01 |       6 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      3 |      4 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN          | EMP_N1 |      1 |      3 |      4 |00:00:00.01 |       2 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      4 |      3 |00:00:00.01 |       2 |
|*  5 |    INDEX RANGE SCAN          | EMP_N2 |      1 |      5 |      5 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$1_1")
      USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1) PREDICATE_REORDERS((3 2) (2 3)))
      OUTLINE_LEAF(@"SEL$1_2")
      OUTLINE(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1_1" "EMP"@"SEL$1" ("EMP"."JOB"))
      INDEX_RS_ASC(@"SEL$1_2" "EMP"@"SEL$1_2" ("EMP"."DEPTNO"))
      END_OUTLINE_DATA
  */

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

   3 - access("JOB"='CLERK')
   4 - filter(LNNVL("JOB"='CLERK'))
   5 - access("DEPTNO"=20)

-- OR-Expansion 은 비용 기반이다.
-- 위의 예에서 job='CLERK' 인 레코드와 deptno = 20 인 레코드의 중복이 많을경우는 OR-Expansion 출동 안함

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |      7 |00:00:00.01 |       6 |
|   1 |  UNION-ALL                   |        |      1 |        |      7 |00:00:00.01 |       6 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      3 |      4 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN          | EMP_N1 |      1 |      3 |      4 |00:00:00.01 |       2 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      4 |      3 |00:00:00.01 |       2 |
|*  5 |    INDEX RANGE SCAN          | EMP_N2 |      1 |      5 |      5 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SET$1")
      INDEX_RS_ASC(@"SEL$2" "EMP"@"SEL$2" ("EMP"."DEPTNO"))
      INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."JOB"))
      END_OUTLINE_DATA
  */

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

   3 - access("JOB"='CLERK')
   4 - filter(LNNVL("JOB"='CLERK'))
   5 - access("DEPTNO"=20)
OR-Expansion 제어 힌트 (use_concat) OR-Expansion 제어 힌트 (no_expand)
select /*+ gather_plan_statistics use_concat */ * from emp
 where job = 'CLERK' or deptno = 20;
select /*+ gather_plan_statistics no_expand */ * from emp
 where job = 'CLERK' or deptno = 20;

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |      7 |00:00:00.01 |       6 |
|   1 |  CONCATENATION               |        |      1 |        |      7 |00:00:00.01 |       6 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      3 |      4 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN          | EMP_N1 |      1 |      3 |      4 |00:00:00.01 |       2 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      4 |      3 |00:00:00.01 |       2 |
|*  5 |    INDEX RANGE SCAN          | EMP_N2 |      1 |      5 |      5 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$1_1")
      USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1) PREDICATE_REORDERS((3 2) (2 3)))
      OUTLINE_LEAF(@"SEL$1_2")
      OUTLINE(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1_1" "EMP"@"SEL$1" ("EMP"."JOB"))
      INDEX_RS_ASC(@"SEL$1_2" "EMP"@"SEL$1_2" ("EMP"."DEPTNO"))
      END_OUTLINE_DATA
  */

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

   3 - access("JOB"='CLERK')
   4 - filter(LNNVL("JOB"='CLERK'))
   5 - access("DEPTNO"=20)

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      7 |00:00:00.01 |       4 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      7 |      7 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "EMP"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   1 - filter(("DEPTNO"=20 OR "JOB"='CLERK'))
OR-Expansion 제어 파라미터 (_no_or_expansion = true) OR-Expansion 제어 파라미터 (_no_or_expansion = false)
alter session set "_no_or_expansion" = true;

select /*+ gather_plan_statistics */ * from emp
 where job = 'CLERK' or deptno = 20;
alter session set "_no_or_expansion" = false;

select /*+ gather_plan_statistics */ * from emp
 where job = 'CLERK' or deptno = 20;

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      7 |00:00:00.01 |       4 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      7 |      7 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "EMP"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   1 - filter(("DEPTNO"=20 OR "JOB"='CLERK'))

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |      7 |00:00:00.01 |       6 |
|   1 |  CONCATENATION               |        |      1 |        |      7 |00:00:00.01 |       6 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      3 |      4 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN          | EMP_N1 |      1 |      3 |      4 |00:00:00.01 |       2 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      4 |      3 |00:00:00.01 |       2 |
|*  5 |    INDEX RANGE SCAN          | EMP_N2 |      1 |      5 |      5 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$1_1")
      USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1) PREDICATE_REORDERS((3 2) (2 3)))
      OUTLINE_LEAF(@"SEL$1_2")
      OUTLINE(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1_1" "EMP"@"SEL$1" ("EMP"."JOB"))
      INDEX_RS_ASC(@"SEL$1_2" "EMP"@"SEL$1_2" ("EMP"."DEPTNO"))
      END_OUTLINE_DATA
  */

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

   3 - access("JOB"='CLERK')
   4 - filter(LNNVL("JOB"='CLERK'))
   5 - access("DEPTNO"=20)
OR-Expansion 브랜치별 조인 순서 최적화

-- 준비
create index emp_n3 on emp (sal)
create index dept_n1 on dept (loc)

exec dbms_stats.gather_index_stats (ownname => 'uadmin', indname => 'emp_n3' , degree => 1);
exec dbms_stats.gather_index_stats (ownname => 'uadmin', indname => 'dept_n1' , degree => 1);
OR-Expansion 안했을때 OR-Expansion 했을때
select /*+ gather_plan_statistics use_nl(e d) no_expand */ *
  from emp e, dept d
 where d.deptno = e.deptno
   and e.sal >= 2000
   and (e.job = 'SALESMAN' or d.loc = 'CHICAGO');      
select /*+ gather_plan_statistics use_nl(e d) use_concat */ *
  from emp e, dept d
 where d.deptno = e.deptno
   and e.sal >= 2000
   and (e.job = 'SALESMAN' or d.loc = 'CHICAGO');

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |      1 |00:00:00.01 |      14 |
|   1 |  NESTED LOOPS                 |         |      1 |        |      1 |00:00:00.01 |      14 |
|   2 |   NESTED LOOPS                |         |      1 |      4 |      6 |00:00:00.01 |       8 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP     |      1 |     11 |      6 |00:00:00.01 |       4 |
|*  4 |     INDEX RANGE SCAN          | EMP_N3  |      1 |     11 |      6 |00:00:00.01 |       2 |
|*  5 |    INDEX UNIQUE SCAN          | DEPT_PK |      6 |      1 |      6 |00:00:00.01 |       4 |
|*  6 |   TABLE ACCESS BY INDEX ROWID | DEPT    |      6 |      1 |      1 |00:00:00.01 |       6 |
---------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."SAL"))
      INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
      LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
      USE_NL(@"SEL$1" "D"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "D"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   4 - access("E"."SAL">=2000)
   5 - access("D"."DEPTNO"="E"."DEPTNO")
   6 - filter(("E"."JOB"='SALESMAN' OR "D"."LOC"='CHICAGO'))

----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |      1 |        |      1 |00:00:00.01 |       9 |
|   1 |  CONCATENATION                 |         |      1 |        |      1 |00:00:00.01 |       9 |
|   2 |   NESTED LOOPS                 |         |      1 |        |      1 |00:00:00.01 |       7 |
|   3 |    NESTED LOOPS                |         |      1 |      4 |      6 |00:00:00.01 |       5 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  5 |      INDEX RANGE SCAN          | DEPT_N1 |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  6 |     INDEX RANGE SCAN           | EMP_N2  |      1 |      5 |      6 |00:00:00.01 |       2 |
|*  7 |    TABLE ACCESS BY INDEX ROWID | EMP     |      6 |      4 |      1 |00:00:00.01 |       2 |
|   8 |   NESTED LOOPS                 |         |      1 |        |      0 |00:00:00.01 |       2 |
|   9 |    NESTED LOOPS                |         |      1 |      2 |      0 |00:00:00.01 |       2 |
|* 10 |     TABLE ACCESS BY INDEX ROWID| EMP     |      1 |      2 |      0 |00:00:00.01 |       2 |
|* 11 |      INDEX RANGE SCAN          | EMP_N1  |      1 |      3 |      4 |00:00:00.01 |       1 |
|* 12 |     INDEX UNIQUE SCAN          | DEPT_PK |      0 |      1 |      0 |00:00:00.01 |       0 |
|* 13 |    TABLE ACCESS BY INDEX ROWID | DEPT    |      0 |      1 |      0 |00:00:00.01 |       0 |
----------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$1_1")
      USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(3))
      OUTLINE_LEAF(@"SEL$1_2")
      OUTLINE(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1_1" "D"@"SEL$1" ("DEPT"."LOC"))
      INDEX(@"SEL$1_1" "E"@"SEL$1" ("EMP"."DEPTNO"))
      INDEX_RS_ASC(@"SEL$1_2" "E"@"SEL$1_2" ("EMP"."JOB"))
      INDEX(@"SEL$1_2" "D"@"SEL$1_2" ("DEPT"."DEPTNO"))
      LEADING(@"SEL$1_1" "D"@"SEL$1" "E"@"SEL$1")
      LEADING(@"SEL$1_2" "E"@"SEL$1_2" "D"@"SEL$1_2")
      USE_NL(@"SEL$1_1" "E"@"SEL$1")
      NLJ_BATCHING(@"SEL$1_1" "E"@"SEL$1")
      USE_NL(@"SEL$1_2" "D"@"SEL$1_2")
      NLJ_BATCHING(@"SEL$1_2" "D"@"SEL$1_2")
      END_OUTLINE_DATA
  */

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

   5 - access("D"."LOC"='CHICAGO')
   6 - access("D"."DEPTNO"="E"."DEPTNO")
   7 - filter("E"."SAL">=2000)
  10 - filter("E"."SAL">=2000)
  11 - access("E"."JOB"='SALESMAN')
  12 - access("D"."DEPTNO"="E"."DEPTNO")
  13 - filter(LNNVL("D"."LOC"='CHICAGO'))

-- 윗쪽 브렌치는 DEPT -> EMP 조인
-- 아래 브렌치는 EMP -> DEPT 조인
같은 컬럼에 대한 OR-Expansion (OR) 같은 컬럼에 대한 OR-Expansion (IN)
select /*+ gather_plan_statistics */ *
  from emp
 where (deptno = 10 or deptno = 30)
   and ename = 'CLARK';
select /*+ gather_plan_statistics */ *
  from emp
 where deptno in (10, 30)
   and ename = 'CLARK';

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |      1 |00:00:00.01 |       5 |
|   1 |  INLIST ITERATOR             |        |      1 |        |      1 |00:00:00.01 |       5 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP    |      2 |      1 |      1 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN          | EMP_N2 |      2 |      9 |      9 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."DEPTNO"))
      END_OUTLINE_DATA
  */

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

   2 - filter("ENAME"='CLARK')
   3 - access(("DEPTNO"=10 OR "DEPTNO"=30))

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |      1 |00:00:00.01 |       5 |
|   1 |  INLIST ITERATOR             |        |      1 |        |      1 |00:00:00.01 |       5 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP    |      2 |      1 |      1 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN          | EMP_N2 |      2 |      9 |      9 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."DEPTNO"))
      END_OUTLINE_DATA
  */

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

   2 - filter("ENAME"='CLARK')
   3 - access(("DEPTNO"=10 OR "DEPTNO"=30))
select /*+ gather_plan_statistics qb_name(MAIN) use_concat(@MAIN 1) */ *
  from emp e
 where (deptno = 10 or deptno = 30)
   and ename = 'CLARK';
select /*+ gather_plan_statistics qb_name(MAIN) use_concat(@MAIN 1) */ *
  from emp
 where deptno in (10, 30)
   and ename = 'CLARK';

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |      1 |00:00:00.01 |       6 |
|   1 |  CONCATENATION               |        |      1 |        |      1 |00:00:00.01 |       6 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN          | EMP_N2 |      1 |      3 |      3 |00:00:00.01 |       2 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |      0 |00:00:00.01 |       2 |
|*  5 |    INDEX RANGE SCAN          | EMP_N2 |      1 |      6 |      6 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"MAIN")
      OUTLINE_LEAF(@"MAIN_1")
      USE_CONCAT(@"MAIN" OR_PREDICATES(1) PREDICATE_REORDERS((3 2) (2 3)))
      OUTLINE_LEAF(@"MAIN_2")
      OUTLINE(@"MAIN")
      INDEX_RS_ASC(@"MAIN_1" "E"@"MAIN" ("EMP"."DEPTNO"))
      INDEX_RS_ASC(@"MAIN_2" "E"@"MAIN_2" ("EMP"."DEPTNO"))
      END_OUTLINE_DATA
  */

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

   2 - filter("ENAME"='CLARK')
   3 - access("DEPTNO"=10)
   4 - filter("ENAME"='CLARK')
   5 - access("DEPTNO"=30)

-- 9i 까지는 OR-Expansion 유도 되었을때 뒤쪽 값(30)이 먼저 출력 됨 (IO 비용 모델)
-- 10g 부터는 OR-Expansion 유도 되었을때 통계적으로 카디널리티가 작은 값이 먼저 출력 됨 (CPU 비용 모델)    

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |      1 |00:00:00.01 |       6 |
|   1 |  CONCATENATION               |        |      1 |        |      1 |00:00:00.01 |       6 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN          | EMP_N2 |      1 |      3 |      3 |00:00:00.01 |       2 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |      0 |00:00:00.01 |       2 |
|*  5 |    INDEX RANGE SCAN          | EMP_N2 |      1 |      6 |      6 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"MAIN")
      OUTLINE_LEAF(@"MAIN_1")
      USE_CONCAT(@"MAIN" OR_PREDICATES(1) PREDICATE_REORDERS((3 2) (2 3)))
      OUTLINE_LEAF(@"MAIN_2")
      OUTLINE(@"MAIN")
      INDEX_RS_ASC(@"MAIN_1" "EMP"@"MAIN" ("EMP"."DEPTNO"))
      INDEX_RS_ASC(@"MAIN_2" "EMP"@"MAIN_2" ("EMP"."DEPTNO"))
      END_OUTLINE_DATA
  */

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

   2 - filter("ENAME"='CLARK')
   3 - access("DEPTNO"=10)
   4 - filter("ENAME"='CLARK')
   5 - access("DEPTNO"=30)

-- 연산자가 "=" 가 아닐경우
select /*+ gather_plan_statistics */ *
  from emp
 where (deptno = 10 or deptno >= 30)
   and ename = 'CLARK';

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |      1 |00:00:00.01 |       6 |
|   1 |  CONCATENATION               |        |      1 |        |      1 |00:00:00.01 |       6 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN          | EMP_N2 |      1 |      3 |      3 |00:00:00.01 |       2 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |      0 |00:00:00.01 |       2 |
|*  5 |    INDEX RANGE SCAN          | EMP_N2 |      1 |      6 |      6 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$1_1")
      USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1) PREDICATE_REORDERS((3 2) (2 3)))
      OUTLINE_LEAF(@"SEL$1_2")
      OUTLINE(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1_1" "EMP"@"SEL$1" ("EMP"."DEPTNO"))
      INDEX_RS_ASC(@"SEL$1_2" "EMP"@"SEL$1_2" ("EMP"."DEPTNO"))
      END_OUTLINE_DATA
  */

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

   2 - filter("ENAME"='CLARK')
   3 - access("DEPTNO"=10)
   4 - filter("ENAME"='CLARK')
   5 - access("DEPTNO">=30)
       filter(LNNVL("DEPTNO"=10))
NVL/DECODE 조건식에 대한 OR-Expansion

-- 준비
create index emp_n4 on emp (ename);
exec dbms_stats.gather_index_stats (ownname => 'uadmin', indname => 'emp_n4' , degree => 1);
NVL/DECODE 조건식에 대한 OR-Expansion - NVL

-- emp.deptno 가 Nullable 인 경우 결과가 달라질 수 있다.
explain plan for
select *
  from emp
 where deptno = nvl(:deptno, deptno)
   and ename like :ename || '%';

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     3 |   114 |     2   (0)| 00:00:01 |
|   1 |  CONCATENATION                |        |       |       |            |          |
|*  2 |   FILTER                      |        |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP    |     2 |    76 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_N4 |     2 |       |     1   (0)| 00:00:01 |
|*  5 |   FILTER                      |        |       |       |            |          |
|*  6 |    TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | EMP_N2 |     5 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - filter(:DEPTNO IS NULL)
   3 - filter("DEPTNO" IS NOT NULL)
   4 - access("ENAME" LIKE :ENAME||'%')
       filter("ENAME" LIKE :ENAME||'%')
   5 - filter(:DEPTNO IS NOT NULL)
   6 - filter("ENAME" LIKE :ENAME||'%')
   7 - access("DEPTNO"=:DEPTNO)

-- 위쪽 브렌치는 EMP_N4(ENAME) 사용
-- 아래 브렌치는 EMP_N2(DEPTNO) 사용   
NVL/DECODE 조건식에 대한 OR-Expansion - 수동

-- 수동
explain plan for   
select * from emp
 where :deptno is null
   and deptno is not null
   and ename like :ename || '%'
 union all
select * from emp
 where :deptno is not null
   and deptno = :deptno
   and ename like :ename || '%';

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     3 |   114 |     2  (50)| 00:00:01 |
|   1 |  UNION-ALL                    |        |       |       |            |          |
|*  2 |   FILTER                      |        |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP    |     2 |    76 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_N4 |     2 |       |     1   (0)| 00:00:01 |
|*  5 |   FILTER                      |        |       |       |            |          |
|*  6 |    TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | EMP_N2 |     5 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - filter(:DEPTNO IS NULL)
   3 - filter("DEPTNO" IS NOT NULL)
   4 - access("ENAME" LIKE :ENAME||'%')
       filter("ENAME" LIKE :ENAME||'%')
   5 - filter(:DEPTNO IS NOT NULL)
   6 - filter("ENAME" LIKE :ENAME||'%')
   7 - access("DEPTNO"=TO_NUMBER(:DEPTNO))
NVL/DECODE 조건식에 대한 OR-Expansion - DECODE

-- DECODE
explain plan for    
select * from emp
 where deptno = decode(:deptno, null, deptno, :deptno)
   and ename like :ename || '%';   

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     3 |   114 |     2   (0)| 00:00:01 |
|   1 |  CONCATENATION                |        |       |       |            |          |
|*  2 |   FILTER                      |        |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP    |     2 |    76 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_N4 |     2 |       |     1   (0)| 00:00:01 |
|*  5 |   FILTER                      |        |       |       |            |          |
|*  6 |    TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | EMP_N2 |     5 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - filter(:DEPTNO IS NULL)
   3 - filter("DEPTNO" IS NOT NULL)
   4 - access("ENAME" LIKE :ENAME||'%')
       filter("ENAME" LIKE :ENAME||'%')
   5 - filter(:DEPTNO IS NOT NULL)
   6 - filter("ENAME" LIKE :ENAME||'%')
   7 - access("DEPTNO"=:DEPTNO)
NVL/DECODE 조건식에 대한 OR-Expansion - _or_expand_nvl_predicate

-- 기능제어 파라미터 : _or_expand_nvl_predicate
alter session set "_or_expand_nvl_predicate" = false;

explain plan for
select *
  from emp
 where deptno = nvl(:deptno, deptno)
   and ename like :ename || '%';

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_N4 |     2 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("DEPTNO"=NVL(:DEPTNO,"DEPTNO"))
   2 - access("ENAME" LIKE :ENAME||'%')
       filter("ENAME" LIKE :ENAME||'%')
NVL/DECODE 조건식에 대한 OR-Expansion - 여러 컬럼

-- 여러 컬럼에 적용시 변별력 좋은 컬럼만 동작 
explain plan for   
select *
  from emp
 where deptno = nvl(:deptno, deptno)
   and job = nvl(:job, job)
   and ename like :ename || '%';   

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     2 |    76 |     2   (0)| 00:00:01 |
|   1 |  CONCATENATION                |        |       |       |            |          |
|*  2 |   FILTER                      |        |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_N4 |     2 |       |     1   (0)| 00:00:01 |
|*  5 |   FILTER                      |        |       |       |            |          |
|*  6 |    TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | EMP_N1 |     3 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - filter(:JOB IS NULL)
   3 - filter("DEPTNO"=NVL(:DEPTNO,"DEPTNO") AND "JOB" IS NOT NULL)
   4 - access("ENAME" LIKE :ENAME||'%')
       filter("ENAME" LIKE :ENAME||'%')
   5 - filter(:JOB IS NOT NULL)
   6 - filter("ENAME" LIKE :ENAME||'%' AND "DEPTNO"=NVL(:DEPTNO,"DEPTNO"))
   7 - access("JOB"=:JOB)
USE_CONCAT(@MAIN 1) USE_CONCAT(@MAIN 8)

-- USE_CONCAT 의 두번째 인자가 1 일때 : 가능한 모두 분리
select /*+ gather_plan_statistics qb_name(main) use_concat(@main 1) */ *
  from emp
 where deptno in (10, 30)
   and job in ('CLERK', 'SALESMAN');  

-- USE_CONCAT 의 두번째 인자가 8 일때 : INLIST 불가능시 분리 (DEFAULT)
select /*+ gather_plan_statistics qb_name(main) use_concat(@main 8) */ *
  from emp
 where deptno in (10, 30)
   and job in ('CLERK', 'SALESMAN');

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |      6 |00:00:00.01 |       9 |
|   1 |  CONCATENATION               |        |      1 |        |      6 |00:00:00.01 |       9 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |      0 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN          | EMP_N2 |      1 |      3 |      3 |00:00:00.01 |       1 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  5 |    INDEX RANGE SCAN          | EMP_N2 |      1 |      3 |      3 |00:00:00.01 |       2 |
|*  6 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      4 |      4 |00:00:00.01 |       2 |
|*  7 |    INDEX RANGE SCAN          | EMP_N1 |      1 |      4 |      4 |00:00:00.01 |       1 |
|*  8 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  9 |    INDEX RANGE SCAN          | EMP_N1 |      1 |      4 |      4 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"MAIN")
      OUTLINE_LEAF(@"MAIN_1")
      USE_CONCAT(@"MAIN" OR_PREDICATES(1 8 16) PREDICATE_REORDERS((3 2) (2 3)))
      OUTLINE_LEAF(@"MAIN_2")
      OUTLINE_LEAF(@"MAIN_3")
      OUTLINE_LEAF(@"MAIN_4")
      OUTLINE(@"MAIN")
      INDEX_RS_ASC(@"MAIN_1" "EMP"@"MAIN" ("EMP"."DEPTNO"))
      INDEX_RS_ASC(@"MAIN_2" "EMP"@"MAIN_2" ("EMP"."DEPTNO"))
      INDEX_RS_ASC(@"MAIN_3" "EMP"@"MAIN_3" ("EMP"."JOB"))
      INDEX_RS_ASC(@"MAIN_4" "EMP"@"MAIN_4" ("EMP"."JOB"))
      END_OUTLINE_DATA
  */

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

   2 - filter("JOB"='SALESMAN')
   3 - access("DEPTNO"=10)
   4 - filter("JOB"='CLERK')
   5 - access("DEPTNO"=10)
   6 - filter("DEPTNO"=30)
   7 - access("JOB"='SALESMAN')
   8 - filter("DEPTNO"=30)
   9 - access("JOB"='CLERK')

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |      6 |00:00:00.01 |       5 |
|   1 |  INLIST ITERATOR             |        |      1 |        |      6 |00:00:00.01 |       5 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP    |      2 |      3 |      6 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN          | EMP_N1 |      2 |      8 |      8 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"MAIN")
      OUTLINE(@"MAIN")
      INDEX_RS_ASC(@"MAIN" "EMP"@"MAIN" ("EMP"."JOB"))
      END_OUTLINE_DATA
  */

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

   2 - filter(("DEPTNO"=10 OR "DEPTNO"=30))
   3 - access(("JOB"='CLERK' OR "JOB"='SALESMAN'))


이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.
이 문서의 내용은 (주)오픈메이드컨설팅에서 출간한 'THE LOGICAL OPTIMIZER'를 참고하였습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.