1. 실행계획 분리하기

/*
Bind        Value
:B1          KR     <-- Case1
:B1          NULL   <-- Case2
*/

SQL> SELECT *
     FROM   T1
     WHERE  C3 LIKE :B1||'%';

• Case1)인 경우 C3 = :B1(:B1 IS NOT NULL)
• Case2)인 경우 C3 IS NOT NULL(:B1 IS NULL)
• 위 경우 처럼 바인드 변수(:B1)의 값에 따라 추출 결과가 달라져야 할 경우에는 일반적으로 NVL, DECODE, LIKE를 이용

• 어떤 성능 차이가 있는지 확인 해보자!!

참고

Case1> 인덱스 경유(X)
SQL> SELECT 1
     FROM   DUAL
     WHERE  INDEX_FILD LIKE '%DD%';

Case2> 인덱스 경유(O)
SQL> SELECT 1
     FROM   DUAL
     WHERE  INDEX_FILD LIKE 'DD%';

Case3> 인덱스 경유(X)
SQL> SELECT 1
     FROM   DUAL
     WHERE  INDEX_FILD LIKE '%DD';

1). LIKE를 이용한 SQL 작성 시 성능

:B1 = '2011/09/19'

SQL> SELECT *
     FROM   NULL_T
     WHERE  C3 LIKE :B1 || '%';

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.007          0         73          0          0
Fetch        2    0.000        0.001          2          6          0          3
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.000        0.008          2         79          0          3

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      3   TABLE ACCESS BY INDEX ROWID NULL_T (cr=6 pr=2 pw=0 time=739 us cost=31 size=1386 card=33)
      3    INDEX RANGE SCAN NULL_T_IDX_03 (cr=3 pr=2 pw=0 time=719 us cost=2 size=0 card=33)(Object ID 127363930)

- NULL_T_IDX_03 인덱스 경유

:B1 = NULL

SQL> SELECT *
     FROM   NULL_T
     WHERE  C3 LIKE :B1 ||'%';
     
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.010        0.011          0         73          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch     9001    0.030        0.265        369       9429          0      90000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total     9003    0.040        0.276        369       9502          0      90000

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
  90000   TABLE ACCESS FULL NULL_T (cr=9429 pr=369 pw=0 time=47205 us cost=122 size=3885462 card=92511)

-- FULL Table Scan(전체 Rows 가져 옴)
-- Join시 Drivig으로 잡히게 되면 심각한 성능 부하

2). NVL를 이용한 SQL 작성 시 성능

-- :B1 IS NULL
SQL> SELECT *
     FROM   (SELECT *
             FROM   NULL_T
             WHERE  C3 = NVL(:B1,C3));

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.022          0        219          0          0
Fetch     9001    0.010        0.239          0       9429          0      90000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total     9003    0.010        0.261          0       9648          0      90000

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
  90000   CONCATENATION  (cr=9429 pr=0 pw=0 time=91791 us)
  90000    FILTER  (cr=9429 pr=0 pw=0 time=63108 us)
  90000     TABLE ACCESS FULL NULL_T (cr=9429 pr=0 pw=0 time=29691 us cost=120 size=3885462 card=92511)
      0    FILTER  (cr=0 pr=0 pw=0 time=4 us)
      0     TABLE ACCESS BY INDEX ROWID NULL_T (cr=0 pr=0 pw=0 time=0 us cost=118 size=43176 card=1028)
      0      INDEX RANGE SCAN NULL_T_IDX_03 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=141)(Object ID 127363930)
                         
-- 옵티마이져는 두개의 실행계획으로 풀게 된다.(C3 IS NOT NULL, C3 = :B1)

3). DECODE를 이용한 SQL 작성 시 성능

-- :B1 IS NULL
SQL> SELECT *
     FROM   NULL_T
     WHERE  C3 = DECODE(:B1, NULL, C3, :B1);

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.010        0.017          0        219          0          0
Fetch     9001    0.030        0.272        265       9429          0      90000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total     9003    0.040        0.289        265       9648          0      90000

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
  90000   CONCATENATION  (cr=9429 pr=265 pw=0 time=88368 us)
  90000    FILTER  (cr=9429 pr=265 pw=0 time=60199 us)
  90000     TABLE ACCESS FULL NULL_T (cr=9429 pr=265 pw=0 time=29599 us cost=120 size=3885462 card=92511)
      0    FILTER  (cr=0 pr=0 pw=0 time=4 us)
      0     TABLE ACCESS BY INDEX ROWID NULL_T (cr=0 pr=0 pw=0 time=0 us cost=118 size=43176 card=1028)
      0      INDEX RANGE SCAN NULL_T_IDX_03 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=141)(Object ID 127363930)

-- 옵티마이져는 두개의 실행계획으로 풀게 된다.(C3 IS NOT NULL, C3 = :B1)

정리

• NVL, DECODE는 :B1의 NULL 여부에 따라서 옵티마이져가 실행계획을 2개로 가져간다.(CONCATENATION)
• LIKE은 실행계획은 1개로 가져가지만, 만일 :B1의 값이 NULL일경우 Table Full Scan하기 때문에 빅데이타의 집합일 경우
  읽는데만 많은 성능을 부하 시키며, 만일 그 집합을 Driving으로 잡고 Join을 시도 할 경우, 해당 건수 만큼 Join이 일어나기 때문에 Oh My God!!

2. IS NULL조회 개선하기

예제>

 
SQL> SELECT *
     FROM   NULL_T
     WHERE  C3 IS NULL;

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.010        0.010          0         73          2          0
Execute      1    0.000        0.000          0          0          0          0
Fetch     1001    0.010        0.063        194       1435          0      10000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total     1003    0.020        0.073        194       1508          2      10000

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
  10000   TABLE ACCESS FULL NULL_T (cr=1435 pr=194 pw=0 time=192850 us cost=121 size=432138 card=10289)

문제점

• 인덱스는 NULL값을 취급하지 않기 때문에 C3컬럼이 인덱스 컬럼인데도 불구하고 IS NULL 로 인해 Full Table Scan이 일어났다.

해결방법-1

• Oracle에서 제공하는 Function Based Index(FBI)를 활용
• FBI의 장점
  :인덱스를 사용해야 되는데 조인 형태의 데이타 타입이 달라서 내장형 함수를 사용해야 될 경우, NULL값이 꼭 있어야 하는 필드
  같은 경우에 FBI를 쓰게되면 효율적으로 데이타 처리 가능
• FBI의 단점
  :인덱스는 Table, View와 같은 Object이기 때문에 인덱스 또한 메모리에 올려 놓고 쓰이게 되는데 만일 Drop를 했을 경우, 메모리에서 Object가 죽게 된다.
  일반 Normal한 인덱스를 Drop을 하면 컬럼을 대상으로 인덱스를 생성했기 때문에 인덱스를 참조하고 있던 프로그램들은 하드파싱 한번 만 일어나면 다시
  메모리에 올려서 사용할 수 가 있지만, FBI는 함수기반이기 때문에 메모리에서 Object가 죽게 되면 DB가 다운 될 확률이 크다.
  또한, Drop을 하지 않더라도 다른 세션에서 FBI를 컴파일 하게 되면 Object가 변경 되었기 때문에 FBI를 바로보고 있는 모든 Object들이 죽게 된다.

해결방법-2

• NVL이용
  Ex>

교재참조