OR연산자나 IN연산자를 사용하였을 때 내부적으로 Concatnation실행계획으로 처리되어 마치 2개 쿼리로 나누어져서 실행되는 쿼리변환이다.
==============================================================
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)
==============================================================
==============================================================
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")
==============================================================
Be Careful
버전에 따른 정렬순서
선택적으로 입력하는 조건절에 대해 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 || '%' ;
==============================================================