1. HOME
  2. [종료]대용량 데이터베이스 스터디
  3. 2010년 하반기 대용량데이터베이스 스터디
  4. 7. OR-Expansion

7. OR-Expansion (by openlsw) [2010.10.14]

OR-Expansion 기본
{tip}OR 조건이 분리 가능하다면? OR-Expansion!{tip}
LNNVL
{code:sql}
LNNVL(1=1) : FALSE
LNNVL(1=2) : TRUE
LNNVL(Null=1) : TRUE
{code}
준비
{code:sql}
-- 준비
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 수동||
|{code:sql}
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' 인 경우만 빼고 참

|

{code:sql}
-		IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

-

0SELECT STATEMENT1700:00:00.016
1CONCATENATION1700:00:00.016
2TABLE ACCESS BY INDEX ROWIDEMP13400:00:00.014
  • 3
INDEX RANGE SCANEMP_N113400:00:00.012
  • 4
TABLE ACCESS BY INDEX ROWIDEMP14300:00:00.012
  • 5
INDEX RANGE SCANEMP_N215500:00:00.011

-

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 출동 안함

|{code:sql}
-------------------------------------------------------------------------------------------------
| 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)
{code:sql}
select /*+ gather_plan_statistics use_concat */ * from emp
where job = 'CLERK' or deptno = 20;
{code}		{code:sql}
select /*+ gather_plan_statistics no_expand */ * from emp
where job = 'CLERK' or deptno = 20;
{code}
{code:sql}
-		IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

-

0SELECT STATEMENT1700:00:00.016
1CONCATENATION1700:00:00.016
2TABLE ACCESS BY INDEX ROWIDEMP13400:00:00.014
  • 3
INDEX RANGE SCANEMP_N113400:00:00.012
  • 4
TABLE ACCESS BY INDEX ROWIDEMP14300:00:00.012
  • 5
INDEX RANGE SCANEMP_N215500:00:00.011

-

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)

|{code:sql}
------------------------------------------------------------------------------------
| 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)
{code:sql}
alter session set "_no_or_expansion" = true;

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

|{code:sql}
alter session set "_no_or_expansion" = false;

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

|

{code:sql}
IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

0SELECT STATEMENT1700:00:00.014
  • 1
TABLE ACCESS FULLEMP17700:00:00.014

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'))

|{code:sql}
-------------------------------------------------------------------------------------------------
| 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 브랜치별 조인 순서 최적화
{code:sql}
-- 준비
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 했을때||
|{code:sql}
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');

|

{code:sql}
---		IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

---

0SELECT STATEMENT1100:00:00.0114
1NESTED LOOPS1100:00:00.0114
2NESTED LOOPS14600:00:00.018
3TABLE ACCESS BY INDEX ROWIDEMP111600:00:00.014
  • 4
INDEX RANGE SCANEMP_N3111600:00:00.012
  • 5
INDEX UNIQUE SCANDEPT_PK61600:00:00.014
  • 6
TABLE ACCESS BY INDEX ROWIDDEPT61100:00:00.016

---

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'))

|{code:sql}
----------------------------------------------------------------------------------------------------
| 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)
{code:sql}
select /*+ gather_plan_statistics */ *
from emp
where (deptno = 10 or deptno = 30)
and ename = 'CLARK';
{code}		{code:sql}
select /*+ gather_plan_statistics */ *
from emp
where deptno in (10, 30)
and ename = 'CLARK';
{code}
{code:sql}
-		IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

-

0SELECT STATEMENT1100:00:00.015
1INLIST ITERATOR1100:00:00.015
  • 2
TABLE ACCESS BY INDEX ROWIDEMP21100:00:00.015
  • 3
INDEX RANGE SCANEMP_N229900:00:00.013

-

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))

|{code:sql}
-------------------------------------------------------------------------------------------------
| 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))

|

{code:sql}
select /*+ gather_plan_statistics qb_name(MAIN) use_concat(@MAIN 1) */ *
from emp e
where (deptno = 10 or deptno = 30)
and ename = 'CLARK';
{code}		{code:sql}
select /*+ gather_plan_statistics qb_name(MAIN) use_concat(@MAIN 1) */ *
from emp
where deptno in (10, 30)
and ename = 'CLARK';
{code}
{code:sql}
-		IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

-

0SELECT STATEMENT1100:00:00.016
1CONCATENATION1100:00:00.016
  • 2
TABLE ACCESS BY INDEX ROWIDEMP11100:00:00.014
  • 3
INDEX RANGE SCANEMP_N213300:00:00.012
  • 4
TABLE ACCESS BY INDEX ROWIDEMP11000:00:00.012
  • 5
INDEX RANGE SCANEMP_N216600:00:00.011

-

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 비용 모델) 

|{code:sql}
-------------------------------------------------------------------------------------------------
| 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)

|

{code:sql}
-- 연산자가 "=" 가 아닐경우
select /*+ gather_plan_statistics */ *
from emp
where (deptno = 10 or deptno >= 30)
and ename = 'CLARK';
{code}
{code:sql}
-		IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

-

0SELECT STATEMENT1100:00:00.016
1CONCATENATION1100:00:00.016
  • 2
TABLE ACCESS BY INDEX ROWIDEMP11100:00:00.014
  • 3
INDEX RANGE SCANEMP_N213300:00:00.012
  • 4
TABLE ACCESS BY INDEX ROWIDEMP11000:00:00.012
  • 5
INDEX RANGE SCANEMP_N216600:00:00.011

-

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||
|{code:sql}
-- 준비
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
{code:sql}
– emp.deptno 가 Nullable 인 경우 결과가 달라질 수 있다.
explain plan for
select *
from emp
where deptno = nvl(:deptno, deptno)
and ename like :ename		'%';
{code}
{code:sql}
IdOperationNameRowsBytesCost (%CPU)Time

0SELECT STATEMENT31142 (0)00:00:01
1CONCATENATION
  • 2
FILTER
  • 3
TABLE ACCESS BY INDEX ROWIDEMP2761 (0)00:00:01
  • 4
INDEX RANGE SCANEMP_N421 (0)00:00:01
  • 5
FILTER
  • 6
TABLE ACCESS BY INDEX ROWIDEMP1381 (0)00:00:01
  • 7
INDEX RANGE SCANEMP_N251 (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 - 수동||
|{code:sql}
-- 수동
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 || '%';

|

{code:sql}
IdOperationNameRowsBytesCost (%CPU)Time

0SELECT STATEMENT31142 (50)00:00:01
1UNION-ALL
  • 2
FILTER
  • 3
TABLE ACCESS BY INDEX ROWIDEMP2761 (0)00:00:01
  • 4
INDEX RANGE SCANEMP_N421 (0)00:00:01
  • 5
FILTER
  • 6
TABLE ACCESS BY INDEX ROWIDEMP1381 (0)00:00:01
  • 7
INDEX RANGE SCANEMP_N251 (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||
|{code:sql}
-- DECODE
explain plan for    
select * from emp
 where deptno = decode(:deptno, null, deptno, :deptno)
   and ename like :ename || '%';

|

{code:sql}
IdOperationNameRowsBytesCost (%CPU)Time

0SELECT STATEMENT31142 (0)00:00:01
1CONCATENATION
  • 2
FILTER
  • 3
TABLE ACCESS BY INDEX ROWIDEMP2761 (0)00:00:01
  • 4
INDEX RANGE SCANEMP_N421 (0)00:00:01
  • 5
FILTER
  • 6
TABLE ACCESS BY INDEX ROWIDEMP1381 (0)00:00:01
  • 7
INDEX RANGE SCANEMP_N251 (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||
|{code:sql}
-- 기능제어 파라미터 : _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 || '%';

|

{code:sql}
--		IdOperationNameRowsBytesCost (%CPU)Time

--

0SELECT STATEMENT1381 (0)00:00:01
  • 1
TABLE ACCESS BY INDEX ROWIDEMP1381 (0)00:00:01
  • 2
INDEX RANGE SCANEMP_N421 (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 - 여러 컬럼||
|{code:sql}
-- 여러 컬럼에 적용시 변별력 좋은 컬럼만 동작 
explain plan for   
select *
  from emp
 where deptno = nvl(:deptno, deptno)
   and job = nvl(:job, job)
   and ename like :ename || '%';

|

{code:sql}
IdOperationNameRowsBytesCost (%CPU)Time

0SELECT STATEMENT2762 (0)00:00:01
1CONCATENATION
  • 2
FILTER
  • 3
TABLE ACCESS BY INDEX ROWIDEMP1381 (0)00:00:01
  • 4
INDEX RANGE SCANEMP_N421 (0)00:00:01
  • 5
FILTER
  • 6
TABLE ACCESS BY INDEX ROWIDEMP1381 (0)00:00:01
  • 7
INDEX RANGE SCANEMP_N131 (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)||
|{code:sql}
-- 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');

|

{code:sql}
-		IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

-

0SELECT STATEMENT1600:00:00.019
1CONCATENATION1600:00:00.019
  • 2
TABLE ACCESS BY INDEX ROWIDEMP11000:00:00.012
  • 3
INDEX RANGE SCANEMP_N213300:00:00.011
  • 4
TABLE ACCESS BY INDEX ROWIDEMP11100:00:00.013
  • 5
INDEX RANGE SCANEMP_N213300:00:00.012
  • 6
TABLE ACCESS BY INDEX ROWIDEMP14400:00:00.012
  • 7
INDEX RANGE SCANEMP_N114400:00:00.011
  • 8
TABLE ACCESS BY INDEX ROWIDEMP11100:00:00.012
  • 9
INDEX RANGE SCANEMP_N114400:00:00.011

-

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')

|{code:sql}
-------------------------------------------------------------------------------------------------
| 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'를 참고하였습니다.

  1. HOME
  2. [종료]대용량 데이터베이스 스터디
  3. 2010년 하반기 대용량데이터베이스 스터디
  4. 7. OR-Expansion