ORACLE OPTIONAL CONDITION v1.0

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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 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

저작자표시 비영리

Lee

ORACLE OPTIONAL CONDITION v1.0

티스토리툴바

ORACLE OPTIONAL CONDITION v1.0

'전체' Related Articles

티스토리툴바