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

7. OR-Expansion




OR-Expansion

OR연산자나 IN연산자를 사용하였을 때 내부적으로 Concatnation실행계획으로 처리되어 마치 2개 쿼리로 나누어져서 실행되는 쿼리변환이다.

OR-Expansion 기본

==============================================================


create index emp_deptno_idx on emp(deptno);
create index emp_job_idx on emp(job);

orcl:WOONG >
  1  select * from emp
  2  where  job = 'CLERK' or deptno = 20;
경   과: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2584904590

---------------------------------------------------------------------------
| Id  | Operation                        | Name           | Rows  | Bytes |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                |     7 |   609 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | EMP            |     7 |   609 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |                |       |       |
|   3 |    BITMAP OR                     |                |       |       |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                |       |       |
|*  5 |      INDEX RANGE SCAN            | EMP_JOB_IDX    |       |       |
|   6 |     BITMAP CONVERSION FROM ROWIDS|                |       |       |
|*  7 |      INDEX RANGE SCAN            | EMP_DEPTNO_IDX |       |       |
---------------------------------------------------------------------------

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

   5 - access("JOB"='CLERK')
   7 - access("DEPTNO"=20)


orcl:WOONG >
1	alter session set "_b_tree_bitmap_plans" = false;

orcl:WOONG >
  1  select * from emp
  2  where  job = 'CLERK' or deptno = 20;
경   과: 00:00:00.00

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

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

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

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


orcl:WOONG >
  1  select /*+ USE_CONCAT */ * from emp
  2  where  job = 'CLERK' or deptno = 20;
경   과: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 951052261

-----------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     6 |   522 |
|   1 |  CONCATENATION               |                |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP            |     3 |   261 |
|*  3 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     1 |       |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP            |     3 |   261 |
|*  5 |    INDEX RANGE SCAN          | EMP_JOB_IDX    |     1 |       |
-----------------------------------------------------------------------

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

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


orcl:WOONG >
  1  select * from emp
  2  where  job = 'CLERK'
  3  union all
  4  select * from emp
  5  where  deptno = 20
  6  and    LNNVL(job='CLERK');
경   과: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3447806485

-----------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     7 |   609 |
|   1 |  UNION-ALL                   |                |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP            |     4 |   348 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_IDX    |     4 |       |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP            |     3 |   261 |
|*  5 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |
-----------------------------------------------------------------------

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

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



==============================================================
OR-Expansion 브랜치별 조인 순서 최적화
==============================================================
orcl:WOONG >
  1  select /*+ NO_EXPAND */ * from emp e, dept d
  2  where  d.deptno = e.deptno
  3  and    e.sal >= 2000
  4  and   (e.job = 'SALESMAN' or d.loc = 'CHICAGO');
경   과: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3581194715

----------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |     4 |   228 |
|   1 |  MERGE JOIN                    |             |     4 |   228 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | DEPT        |     4 |    80 |
|   3 |    INDEX FULL SCAN             | DEPT_PK     |     4 |       |
|*  4 |   FILTER                       |             |       |       |
|*  5 |    SORT JOIN                   |             |    11 |   407 |
|   6 |     TABLE ACCESS BY INDEX ROWID| EMP         |    11 |   407 |
|*  7 |      INDEX RANGE SCAN          | EMP_SAL_IDX |    11 |       |
----------------------------------------------------------------------

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

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

orcl:WOONG >
  1  select /*+ leading(e) use_nl(d)
  2             index(e emp_sal_idx)
  3             index(d dept_pk) */ * from emp e, dept d
  4  where  d.deptno = e.deptno
  5  and    e.sal >= 2000
  6  and   (e.job = 'SALESMAN' or d.loc = 'CHICAGO');
경   과: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3582342135

--------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     4 |   228 |
|   1 |  NESTED LOOPS                |             |     4 |   228 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP         |    11 |   407 |
|*  3 |    INDEX RANGE SCAN          | EMP_SAL_IDX |    11 |       |
|*  4 |   TABLE ACCESS BY INDEX ROWID| DEPT        |     1 |    20 |
|*  5 |    INDEX UNIQUE SCAN         | DEPT_PK     |     1 |       |
--------------------------------------------------------------------

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

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



orcl:WOONG >
  1  select /*+ USE_CONCAT */ * from emp e, dept d
  2  where  d.deptno = e.deptno
  3  and    e.sal >= 2000
  4  and   (e.job = 'SALESMAN'or d.loc = 'CHICAGO');
경   과: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1959184987

-------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |     6 |   342 |
|   1 |  CONCATENATION                 |                |       |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID  | EMP            |     4 |   148 |
|   3 |    NESTED LOOPS                |                |     4 |   228 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT           |     1 |    20 |
|*  5 |      INDEX RANGE SCAN          | DEPT_LOC_IDX   |     1 |       |
|*  6 |     INDEX RANGE SCAN           | EMP_DEPTNO_IDX |     5 |       |
|   7 |   NESTED LOOPS                 |                |     2 |   114 |
|*  8 |    TABLE ACCESS BY INDEX ROWID | EMP            |     2 |    74 |
|*  9 |     INDEX RANGE SCAN           | EMP_JOB_IDX    |     3 |       |
|* 10 |    TABLE ACCESS BY INDEX ROWID | DEPT           |     1 |    20 |
|* 11 |     INDEX UNIQUE SCAN          | DEPT_PK        |     1 |       |
-------------------------------------------------------------------------

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

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


==============================================================
같은 컬럼에 대한 OR-Expasioin
  • 9i까지는 OR조건이나 In-List도 OR-Expansion으로 작동할 수 있었지만,
  • 10g부터는 기본적으로 In-List Iterator방식으로 처리된다. OR-Expansion으로 유도할 수 있으나 In-List Iterator에 비해 나은 점이 없다.
Be Careful

버전에 따른 정렬순서

  • 9i까지는 OR-Expansion으로 유도하면 뒤쪽에 위치되어진 값이 먼저 출력되었다.
  • 10g부터는 통계정보를 참조하여 카디널리티가 작은 값이 먼저 출력된다.
NVL/DECODE 조건식에 대한 OR-Expansion

선택적으로 입력하는 조건절에 대해 nvl또는 Decode함수를 이용할 수 있는데 이때 OR-Expansion쿼리변환이 일어날 수 있다.
중요한 점은 입력변수에 따라 공집합이 되는 쿼리블록이 구분되어 실행되거나 또는 값에 따라 다른 인덱스를 사용할 수 있다는 점이다.
예전에는 튜너가 union all을 사용하여 수동으로 분기하였으나 옵티마이져가 스스로 이와 같은 처리를 하려고 노력한다. 하지만 조건이 복잡해질수록 sql작성자가 직접 개입해서 union all로 분기해주어야 한다.

==============================================================

SQL> select * from emp
  2  where  deptno = nvl(:deptno, deptno)
  3  and    ename  like :ename || '%' ;

Execution Plan
----------------------------------------------------------
Plan hash value: 2453271963

------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     3 |   111 |
|   1 |  CONCATENATION                |                |       |       |
|*  2 |   FILTER                      |                |       |       |
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP            |     2 |    74 |
|*  4 |     INDEX RANGE SCAN          | EMP_ENAME_IDX  |     2 |       |
|*  5 |   FILTER                      |                |       |       |
|*  6 |    TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    37 |
|*  7 |     INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |
------------------------------------------------------------------------

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)

=====================================================================================

SQL> select * from emp
  2  where  :deptno is null
  3  and    deptno is not null
  4  and    ename  like :ename || '%'
  5  union all
  6  select * from emp
  7  where  :deptno is not null
  8  and    deptno = :deptno
9  and    ename  like :ename || '%' ;

=====================================================================================
SQL> select * from emp
  2  where  deptno = decode(:deptno, null, deptno, :deptno)
  3  and    ename  like :ename || '%' ;
==============================================================

문서에 대하여

  • 최초작성자 : [이지웅]
  • 최초작성일 : 2010년 05월 14일
  • 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
  • 이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.

문서정보

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