튜닝예제(2)

인덱스의 내형적 변형사항

아래 SQL은 하나의 테이블을 access합니다.

process_retailer_input : 약 1만건
- 인덱스 : PK : retailer_number
- 컬럼 : retailer_number varchar2(18)

- 튜닝전
select business_name, chain_number, terminal_type
from   process_retailer_input

where retailer_number = 11100003

call        count      cpu     elapsed     disk      query    current     rows
------------------------------------------------------------------
Parse         1        0.00       0.00          0          0             0            0
Execute     1         0.00       0.00          0          0             0            0
Fetch         2         0.17       0.51        2908       3620         4            1
------------------------------------------------------------------
total           4         0.17       0.51        2908       3620         4            1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 401

Rows     Row Source Operation
------------------------------------------------------------------
    1       TABLE ACCESS FULL PROCESS_RETAILER_INPUT
------------------------------------------------------------------

설명.

이는 컬럼의 내부적인 변형이 일어나게 됩니다. retailer_number = 11100003 로 작성을 하면 오라클은 to_number(retailer_number) = 11100003 로 내부적으로 변형을 일으킵니다. 즉, 문자와 숫자를 비교를 하면 숫자를 기준으로 문자가 변경되게 됩니다. 그래서 다음과 같이 작성을 합니다.

retailer_number = 11100003 -> retailer_number = '11100003

이렇게 하면 PK인덱스를 사용할 수 있습니다.
  
- 튜닝후
select business_name, chain_number, terminal_type
from   process_retailer_input

where retailer_number = '11100003          '

call        count      cpu     elapsed     disk      query    current     rows
------------------------------------------------------------------
Parse        1         0.00       0.00          0           0           0            0
Execute     1         0.00       0.00          0           0           0            0
Fetch        2         0.00       0.00          1           3           0            1
------------------------------------------------------------------
total          4         0.00       0.00          1           3           0            1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 401

Rows     Row Source Operation
------------------------------------------------------------------
  1    TABLE ACCESS BY INDEX ROWID PROCESS_RETAILER_INPUT
  1    INDEX UNIQUE SCAN (object id 265130)

결과 : 0.51초 -> 0.00초(0.00초이하)

참고.

오라클은 위와 같은 사항이 발생을 하면 숫자를 기준으로 문자의 변형을 일으킵니다. 그럼 다음과 같은 경우는 어떨까요?
num = '10' (num컬럼은 number이다.)
num = to_number('10')로 변경이 되어 변형이 일어나지 않아 num에 인덱스가 있을 경우 인덱스를 사용할 수 있습니다.
그럼, 다음과 같은 경우는요?
num like '10%'
쫌 어렵죠. ^^
위 이론대로라면 '10%'을 숫자로 변경을 해야 하지만 %의 문자값이 존재하여 num에 변형을 일으킵니다. 그럼, 인덱스를 사용할 수 없겠죠. 실무에서는 = 보다는 like을 빈번히 사용합니다. 그래서 인덱스 지정이 예상되는 컬럼은 문자타입으로 하는 것이 좋습니다.
물론, 일련번호와 같이 like로 사용할 필요가 없는 것은 숫자타입으로 지정하는 것이 당연하구요.