출처 : http://ukja.tistory.com/131
날짜를 표현하는 데이터 타입으로 Date를 사용해야 하는가? 혹은 Number나 Varchar2를 사용해야하는가?라는 해묵은 논쟁이 있다.
"너무나 간단하지 않은가?"라고 반문하겠지만, 의의로 적용되는 상황을 보면 그리 쉬운 질문은 아닌거 같다. 여러가지 판단 기준이 있겠지만, 가장 중요한 기준은 이것이어야 한다.
"어느 쪽이 CBO가 더 이해하기 쉬운가?"
이 기준으로 생각하면 선택은 언제나 Date 타입이어야 한다. 거의 반론의 여지가 없다고 본다. 간단한 테스트를 통해 확인해보자.
drop table t1 purge;
create table t1 (
c_date date,
c_char varchar2(8),
c_numb number
);
-- 1,000일간의 데이터를 생성한다.
-- 동일한 데이터를 Date, Varchar2, Number 타입으로 생성한다.
insert into t1
select
sysdate - level,
to_char(sysdate-level, 'yyyymmdd'),
to_number(to_char(sysdate-level,'yyyymmdd'))
from
dual
connect by level <= 1000;
-- Date와 Number 기준으로 2007년 10월 1일 ~ 2008년 5월 1일까지의 범위를 구한다.
with v as (
select
max(c_date) as max_date, min(c_date) as min_date,
max(c_char) as max_char, min(c_char) as min_char,
max(c_numb) as max_numb, min(c_numb) as min_numb,
count(*) as total_rows
from
t1
)
select
total_rows *
(to_date('20080501','yyyymmdd')-to_date('20071001','yyyymmdd'))
/(max_date - min_date) as diff_d,
total_rows * (20080501-20071001) / (max_numb-min_numb) as diff_n
from
v
;
DIFF_D DIFF_N
---------- ----------
213.213213 319.919178 <-- 날짜는 213일이지만, 숫자값으로는 320만큼 차이가 난다.
이제 다음과 같이 각 데이터 타입별로 2007년 10월 1일 ~ 2008년 5월 1일까지의 날수를 누가 더 정확하게 표현하는지 확인해본다.
-- Date 타입
explain plan for
select *
from t1
where
c_date between to_date('20071001','yyyymmdd') and
to_date('20080501', 'yyyymmdd')
;
@plan
-- Varchar2 타입
explain plan for
select *
from t1
where
c_char between '20071001' and
'20080501'
;
@plan
-- Number 타입
explain plan for
select *
from t1
where
c_numb between 20071001 and
20080501
;
@plan
결과는 다음과 같다(Cardinality의 차이에 주의하자)
-- Date 타입인 경우
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ({83832c2e9762d7d59158bb8e184331dad143c7b73eb69f86229ee6e276689139}CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 215 | 4945 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 215 | 4945 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
-- Varchar2 타입인 경우
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ({83832c2e9762d7d59158bb8e184331dad143c7b73eb69f86229ee6e276689139}CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 321 | 7383 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 321 | 7383 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
-- Number 타입인 경우
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ({83832c2e9762d7d59158bb8e184331dad143c7b73eb69f86229ee6e276689139}CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 321 | 7383 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 321 | 7383 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
위의 결과를 보면 오직 Date 타입을 사용한 경우에만 실제 데이터 분포를 합리적으로 해석하는 것을 알 수 있다. 이유는 간단하다. CBO가 "날짜"를 "날짜"로 해석할 수 있기 때문이다.
(Oracle이 예측한 Cardinality가 우리가 Min/Max를 이용해 구한 값과 거의 일치하는 것에 유의하자)
반면 Number나 Vachar2 타입을 사용한 경우에는 "날짜"로 해석할수 없기 때문에 단순히 범위만을 고려한 값을 예측하게 된다. 이런 차이들이 실행 계획의 합리성을 결정하기 때문에 그 효과는 하늘과 땅 차이라 하겠다.
하지만, 여기서 다시 한번 이런 복잡한 질문을 던질 수 있다.
"지난 몇 년 동안 날짜를 표현하는데 Varchar2나 Number를 사용해왔지만, 아무런 문제도 없었다. 이것은 어떻게 해석할 것인가?"
답변은 운이 좋았거나, Bind 변수를 사용했기 때문이라는 것이다.
Oracle은 다음과 같이 Between Range 조건을 Bind 변수로 사용한 경우에는 0.25{83832c2e9762d7d59158bb8e184331dad143c7b73eb69f86229ee6e276689139}라는 고정된 값을 Selectivity(선택도)로 사용한다.
(이것은 단일 Range 조건이 5{83832c2e9762d7d59158bb8e184331dad143c7b73eb69f86229ee6e276689139}의 선택도를 사용하기 때문이다. Between Range 조건(And 조건의 일종)은 5{83832c2e9762d7d59158bb8e184331dad143c7b73eb69f86229ee6e276689139}*5{83832c2e9762d7d59158bb8e184331dad143c7b73eb69f86229ee6e276689139} = 0.25{83832c2e9762d7d59158bb8e184331dad143c7b73eb69f86229ee6e276689139}가 된다)
explain plan for
select *
from t1
where
c_numb between :b1 and
:b2
;
@plan
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ({83832c2e9762d7d59158bb8e184331dad143c7b73eb69f86229ee6e276689139}CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 69 | 3 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 3 | 69 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
전체 Row수가 1,000개 이므로 1,000*0.0025 = 2.5 = 3이 되는 것을 알 수 있다.
아마 예외없이 Bind 변수를 사용함으로써 항상 고정된 Cardinality가 나왔을 것이고, 이로 인해 잘못된 데이터 타입으로 인한 부작용을 확인할 길이 없었을 것이다.
Range Predicate는 Histogram과 더불어 CBO에게 가장 골치아픈 문제 중 하나이다. 오라클을 오라클답게 사용하려면 선택의 기준을 항상 "CBO에게" 맞추는 습관이 필요할 것이다.
