ORACLE OPTIONAL CONDITION
from 오라클 성능 고도화 원리와 해법 1 | 비투엔
1.
선택적
검색 조건
쿼리 조건절의 값은 경우에 따라 생략되기도 한다. 이는 구글의
상세 검색에서도 볼 수 있다. ‘특정 키워드를 포함’, ‘특정
키워드를 미포함’ 과 같이 말이다. 이들에 대한 입력은 어디까지나
사용자의 선택이며 공란이 되어도 전혀 문제가 없다.
여기서 조건절에 대한 입력이 생략이 되어도 쿼리가 에러없이 수행되기 위해 다음과 같은 키워드를 사용한다.
OR, LIKE, NVL, DECODE, UNION
2.
선택적
검색 예제
A.
OR
Query 를 에러 없이 수행하기 위해 조건절을 다음과 같이 사용할 수 있다.
( a = :a or a = a )
이와 같이 조건을 사용하면 항상 full
scan 이 일어난다.
해당 컬럼에 인덱스가 있고 이를 이용할 생각이었다면 위와 같이 조건절을 사용해서는
안된다.
<Test>
SQL> create table empp
2 as select ename,sal from emp;
테스트를 위해 emp 를 사용해 empp 테이블을 생성했다.
테이블이 생성되었습니다.
SQL> select * from empp;
ENAME SAL
-------------------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 4000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
14 개의 행이 선택되었습니다.
Salary 가 null 값인 empno 가 ‘TOMS’인 row
를 하나 입력했다.
SQL> insert into empp
values('TOMS',null);
1 개의 행이 만들어졌습니다.
SQL> select * from empp;
ENAME SAL
-------------------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 4000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
TOMS
15 개의 행이 선택되었습니다.
Index Scan 여부를 판단하기 위해 empp.sal 에 Index 를 생성했다.
SQL> create index empp_ix1 on
empp(sal);
인덱스가 생성되었습니다.
SQL> set autot traceonly explain
SQL> select * from empp
2
where sal = 1300 or sal is null;
Execution Plan
----------------------------------------------------------
Plan hash value: 2722874243
--------------------------------------------------------------------------
| Id
| Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|
0 | SELECT STATEMENT | |
2 | 40 | 3
(0)| 00:00:01 |
|* 1 |
TABLE ACCESS FULL| EMPP | 2
| 40 | 3
(0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by
operation id):
---------------------------------------------------
1 - filter("SAL" IS NULL OR "SAL"=1300)
Note
-----
- dynamic sampling used for this statement
null 조건의 위치를 바꿔도 기존의 plan 이 동일하다.
SQL> select * from empp
2
where sal is null or sal = 1300;
Execution Plan
----------------------------------------------------------
Plan hash value: 2722874243
--------------------------------------------------------------------------
| Id
| Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|
0 | SELECT STATEMENT | |
2 | 40 | 3
(0)| 00:00:01 |
|* 1 |
TABLE ACCESS FULL| EMPP | 2
| 40 | 3
(0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by
operation id):
---------------------------------------------------
1 - filter("SAL" IS NULL OR "SAL"=1300)
Note
-----
- dynamic sampling used for this statement
참고로 emp.empno 에 대해 plan 을 조회하면 Index Scan 을 한다.
그 이유는 empno 은 Primary Key 로 Not Null 제약 조건이 있기 때문이다.
<Test>
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id
| Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
| | 1 |
37 | 1 (0)| 00:00:01 |
|
1 | TABLE ACCESS BY INDEX ROWID|
EMP | 1 |
37 | 1 (0)| 00:00:01 |
|*
2 | INDEX UNIQUE SCAN | PK_EMP | 1 |
| 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by
operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)
B.
LIKE
LIKE 의 경우 Query 에러를 피하기 위해 다음과 같이 사용하는 경우가 있다.
a = :a è a = :a||’%’
전자의 경우 :a is null 이면 해당 row 는 결과집합에서 제외된다. 하지만 후자의 경우는 :a is null 인 경우 null 인 row 가 결과 집합에 포함된다. 다시 말해 바인드 입력 값이 없어도
전체 rows 에 아무 영향이 없다.
<Test>
SQL> @empp
ENAME SAL
-------------------- ----------
ADAMS 1100
ALLEN 1600
BLAKE 2850
CLARK 2450
FORD 3000
JAMES 950
JONES 2975
KING 5000
MARTIN 1250
MILLER 1300
SCOTT 4000
SMITH 800
TOMS
TURNER 1500
WARD 1250
15 개의 행이 선택되었습니다.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats
last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID
73mx7vzyrzhhk, child number 0
-------------------------------------
select /*+ gather_plan_statistics */*
from empp where ename like :ename||'%'
Plan hash value: 1700919851
--------------------------------------------------------------------------------------------------
| Id
| Operation |
Name | Starts | E-Rows | A-Rows
| A-Time | Buffers |
--------------------------------------------------------------------------------------------------
|
1 | TABLE ACCESS BY INDEX ROWID|
EMPP | 1 |
2 | 15 |00:00:00.01 | 6 |
|*
2 | INDEX RANGE SCAN | EMPP_IX2 | 1 |
2 | 15 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------
위 플랜을 보면 Index Scan 후
Table 에 Random Access 를 한다. 현재 Bind 값에 실값이 없는 상태이다. 이는 A-Rows (실제 결과 수) 를 보면 알 수 있다. 이것이 의미하는 것은 조건이 생략 되는 경우 Index 를 Full Scan 후 Table 전체 범위에 대해 Random Access 가 일어난다는 것이다.
한가지 신기한 부분은 Index 를 통해 Table 값을 가져왔음에도 불구하고 sal is null 인 ‘TOMS’ 결과가 포함되었다는 것이다.
Predicate Information (identified by
operation id):
---------------------------------------------------
2 - access("ENAME" LIKE :ENAME||'%')
filter("ENAME" LIKE :ENAME||'%')
19 개의 행이 선택되었습니다.
SQL> exec :ename := 'S';
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> @empp
ENAME SAL
-------------------- ----------
SCOTT 4000
SMITH 800
SQL> select * from
table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------SQL_ID 73mx7vzyrzhhk, child number 0
-------------------------------------
select /*+ gather_plan_statistics */*
from empp where ename like :ename||'%'
Plan hash value: 1700919851
--------------------------------------------------------------------------------------------------
| Id
| Operation |
Name | Starts | E-Rows | A-Rows
| A-Time | Buffers |
--------------------------------------------------------------------------------------------------
|
1 | TABLE ACCESS BY INDEX ROWID|
EMPP | 1 |
2 | 2 |00:00:00.01 | 4 |
|*
2 | INDEX RANGE SCAN | EMPP_IX2 | 1 |
2 | 2 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------
조건이 들어간 경우 2건을 Index 에서
걸러내 2건에 대해 Table 에 Random Access 하였다. 아까와 같은 (Bind 가 비어있는 경우) 비효율은 없다.
Predicate Information (identified by
operation id):
---------------------------------------------------
2 - access("ENAME" LIKE
:ENAME||'%')
filter("ENAME" LIKE :ENAME||'%')
19 개의 행이 선택되었습니다.
C.
NVL
NVL 조건은 COLUMN = NVL(:BIND, COLUMN) 과 같이 사용한다. 주의할
점은 해당 Column 에 Not Null 제약이 없는 경우, 즉 Null 값이 있는 경우 건수가 달라질 수 있다. 그것은 Null = Null 비교가 불가능 (결과셋에서 제외됨) 하기 때문이다.
<Test>
SQL> select /*+
gather_plan_statistics */* from empp
2 where ename like :ename||'%'
3 and sal = nvl(:sal, sal)
4 /
Salary
에 Nvl 함수를 사용하였다.
ENAME SAL
-------------------- ----------
ADAMS 1100
ALLEN 1600
BLAKE 2850
CLARK 2450
FORD 3000
JAMES 950
JONES 2975
KING 5000
MARTIN 1250
MILLER 1300
SCOTT 4000
SMITH 800
TURNER 1500
WARD 1250
14 개의 행이 선택되었습니다.
Salary
가 Null 인 값이 Filtering 된 것을 알 수 있다. (15건에서 14건이 됨)
SQL> select * from
table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
SQL_ID
c2c6mfrn521u1, child number 0
-------------------------------------
select /*+ gather_plan_statistics */*
from empp where ename like :ename||'%' and sal =
nvl(:sal, sal)
Plan hash value: 1126477346
----------------------------------------------------------------------------------------------------
| Id
| Operation |
Name | Starts | E-Rows | A-Rows
| A-Time | Buffers |
----------------------------------------------------------------------------------------------------
|
1 | CONCATENATION | |
1 | | 14 |00:00:00.01 | 6 |
|*
2 | FILTER | |
1 | | 14 |00:00:00.01 | 6 |
|*
3 | TABLE ACCESS BY INDEX
ROWID| EMPP | 1 |
2 | 14 |00:00:00.01 | 6 |
|*
4 | INDEX RANGE SCAN | EMPP_IX2 | 1 |
2 | 15 |00:00:00.01 | 2 |
|*
5 | FILTER | |
1 | | 0 |00:00:00.01 | 0 |
|*
6 | TABLE ACCESS BY INDEX
ROWID| EMPP | 0 |
1 | 0 |00:00:00.01 | 0 |
|*
7 | INDEX RANGE SCAN | EMPP_IX1 | 0 |
1 | 0 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by
operation id):
---------------------------------------------------
2 - filter(:SAL IS NULL)
3 - filter("SAL" IS NOT NULL)
4 - access("ENAME" LIKE :ENAME||'%')
filter("ENAME" LIKE :ENAME||'%')
5 - filter(:SAL IS NOT NULL)
6 - filter("ENAME" LIKE :ENAME||'%')
7 - access("SAL"=:SAL)
30 개의 행이 선택되었습니다.
실행플랜은 Salary 가 null 이냐
null 이 아니냐에 따라 흐름이 달라진다. 위의 플랜은
OR EXPANSION /*+ Expand */ 결과와 동일하며 사실상 Union All 로
Plan 을 분기한 것과 동일하다.
참고로 Expansion 을 막았더니 다음과 같이 변형되었다. (/*+
no_expand */ 사용)
SQL> select /*+
gather_plan_statistics no_expand */* from empp
2 where ename like :ename||'%'
3 and sal = nvl(:sal, sal)
4 /
~~결과생략~~
14 개의 행이 선택되었습니다.
SQL> select * from
table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID
79phrpwdj6kfy, child number 0
-------------------------------------
select /*+ gather_plan_statistics
no_expand */* from empp where ename like :ename||'%'
and sal = nvl(:sal, sal)
Plan hash value: 1700919851
--------------------------------------------------------------------------------------------------
| Id
| Operation |
Name | Starts | E-Rows | A-Rows
| A-Time | Buffers |
--------------------------------------------------------------------------------------------------
|*
1 | TABLE ACCESS BY INDEX ROWID|
EMPP | 1 |
1 | 14 |00:00:00.01 | 6 |
|*
2 | INDEX RANGE SCAN | EMPP_IX2 | 1 |
2 | 15 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------
Salary
값의 유/무 에 따라 분기되었던 부분이 사라졌다. 위의 쿼리는 Salary 값에 상관없이 항상 ename 에 생성된 Index 로
Access 한다.
Predicate Information (identified by
operation id):
---------------------------------------------------
1 - filter("SAL"=NVL(:SAL,"SAL"))
2 - access("ENAME" LIKE :ENAME||'%')
filter("ENAME" LIKE :ENAME||'%')
21 개의 행이 선택되었습니다.
D.
DECODE
아래의 경우 Nvl 의 사용과 동일하게 Concaternation 이 일어난다.
<Test>
SQL> select /*+
gather_plan_statistics */* from empp
2 where ename like :ename||'%'
3 and sal = decode(:sal, null,
sal, :sal)
4 /
~~결과생략~~
14 개의 행이 선택되었습니다.
SQL> select * from
table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID
5mfy5pr8gy494, child number 0
-------------------------------------
select /*+ gather_plan_statistics */*
from empp where ename like :ename||'%' and sal =
decode(:sal, null, sal, :sal)
Plan hash value: 1126477346
----------------------------------------------------------------------------------------------------
| Id
| Operation |
Name | Starts | E-Rows | A-Rows
| A-Time | Buffers |
----------------------------------------------------------------------------------------------------
|
1 | CONCATENATION | |
1 | | 14 |00:00:00.01 | 6 |
|*
2 | FILTER | |
1 | | 14 |00:00:00.01 | 6 |
|*
3 | TABLE ACCESS BY INDEX
ROWID| EMPP | 1 |
2 | 14 |00:00:00.01 | 6 |
|*
4 | INDEX RANGE SCAN | EMPP_IX2 | 1 |
2 | 15 |00:00:00.01 | 2 |
|*
5 | FILTER | |
1 | | 0 |00:00:00.01 | 0 |
|*
6 | TABLE ACCESS BY INDEX
ROWID| EMPP | 0 |
1 | 0 |00:00:00.01 | 0 |
|*
7 | INDEX RANGE SCAN | EMPP_IX1 | 0 |
1 | 0 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by
operation id):
---------------------------------------------------
2 - filter(TO_CHAR(:SAL) IS NULL)
3 - filter("SAL" IS NOT NULL)
4 - access("ENAME" LIKE :ENAME||'%')
filter("ENAME" LIKE :ENAME||'%')
5 - filter(TO_CHAR(:SAL) IS NOT NULL)
6 - filter("ENAME" LIKE :ENAME||'%')
7 - access("SAL"=:SAL)
E.
UNION
앞서 사용한 OR 절에 대해 UNION ALL 을 사용해 분기할 수 있다.
SQL> select * from empp
2 where sal is null
3 union all
4 select * from empp
5 where sal = 1300;
Execution Plan
----------------------------------------------------------
Plan hash value: 2649685553
-----------------------------------------------------------------------------------------
| Id
| Operation |
Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
| | 2 |
40 | 5 (40)| 00:00:01 |
|
1 | UNION-ALL | | |
| | |
|*
2 | TABLE ACCESS FULL | EMPP |
1 | 20 | 3
(0)| 00:00:01 |
|
3 | TABLE ACCESS BY INDEX ROWID|
EMPP | 1 |
20 | 2 (0)| 00:00:01 |
|*
4 | INDEX RANGE SCAN | EMPP_IX1 | 1 |
| 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Salary
값 유/무에 따라 다른 Plan 을 이용한다.
Predicate Information (identified by
operation id):
---------------------------------------------------
2 - filter("SAL" IS NULL)
4 - access("SAL"=1300)
Note
-----
- dynamic sampling used for this statement