데이터 타입 테스트 케이스

create table test_data1
(
int_data int,
char_date varchar(8) default to_char(sysdate,'YYYYMMDD') not null
);
CREATE INDEX ON test_data1 (char_date);

create table test_data2
(
int_data int,
char_date date default sysdate not null
);
CREATE INDEX ON test_data2 (char_date);
create table test_data3
(
int_data int,
char_date timestamp default systimestamp not null
);
CREATE INDEX ON test_data3 (char_date);

insert into test_data1
SELECT a.rnum
     , case when a.moddata = 1 then '20101201'
            when a.moddata = 2 then '20101202'
     when a.moddata = 3 then '20101203'
            when a.moddata = 4 then '20101204'
     when a.moddata = 5 then '20101205'
            when a.moddata = 6 then '20101206'
     when a.moddata = 7 then '20101207'
            when a.moddata = 8 then '20101208'
     when a.moddata = 9 then '20101209'
            when a.moddata = 10 then '20101210'
 else '20101211'
 end
  from (
SELECT rownum rnum , mod(rownum,10) moddata
FROM table({0,1,2,3,4,5,6,7,8,9}) t0(a),
table({0,1,2,3,4,5,6,7,8,9}) t1(a), -- 100
table({0,1,2,3,4,5,6,7,8,9}) t2(a), -- 1,000
table({0,1,2,3,4,5,6,7,8,9}) t3(a) -- 10,000
) a
;x
insert into test_data2
SELECT a.rnum
     , case when a.moddata = 1 then to_date('20101201','YYYYMMDD')
            when a.moddata = 2 then to_date('20101202','YYYYMMDD')
     when a.moddata = 3 then to_date('20101203','YYYYMMDD')
            when a.moddata = 4 then to_date('20101204','YYYYMMDD')
     when a.moddata = 5 then to_date('20101205','YYYYMMDD')
            when a.moddata = 6 then to_date('20101206','YYYYMMDD')
     when a.moddata = 7 then to_date('20101207','YYYYMMDD')
            when a.moddata = 8 then to_date('20101208','YYYYMMDD')
     when a.moddata = 9 then to_date('20101209','YYYYMMDD')
            when a.moddata = 10 then to_date('20101210','YYYYMMDD')
 else to_date('20101211','YYYYMMDD')
 end
  from (
SELECT rownum rnum , mod(rownum,10) moddata
FROM table({0,1,2,3,4,5,6,7,8,9}) t0(a),
table({0,1,2,3,4,5,6,7,8,9}) t1(a), -- 100
table({0,1,2,3,4,5,6,7,8,9}) t2(a), -- 1,000
table({0,1,2,3,4,5,6,7,8,9}) t3(a) -- 10,000
) a
;x
insert into test_data3
SELECT a.rnum
     , case when a.moddata = 1 then to_timestamp('20101201','YYYYMMDD')
            when a.moddata = 2 then to_timestamp('20101202','YYYYMMDD')
     when a.moddata = 3 then to_timestamp('20101203','YYYYMMDD')
            when a.moddata = 4 then to_timestamp('20101204','YYYYMMDD')
     when a.moddata = 5 then to_timestamp('20101205','YYYYMMDD')
            when a.moddata = 6 then to_timestamp('20101206','YYYYMMDD')
     when a.moddata = 7 then to_timestamp('20101207','YYYYMMDD')
            when a.moddata = 8 then to_timestamp('20101208','YYYYMMDD')
     when a.moddata = 9 then to_timestamp('20101209','YYYYMMDD')
            when a.moddata = 10 then to_timestamp('20101210','YYYYMMDD')
 else to_timestamp('20101211','YYYYMMDD')
 end
  from (
SELECT rownum rnum , mod(rownum,10) moddata
FROM table({0,1,2,3,4,5,6,7,8,9}) t0(a),
table({0,1,2,3,4,5,6,7,8,9}) t1(a), -- 100
table({0,1,2,3,4,5,6,7,8,9}) t2(a), -- 1,000
table({0,1,2,3,4,5,6,7,8,9}) t3(a) -- 10,000
) a
;x


테스트 케이스

select count(*) from test_data1 where char_date = '20101201'

select count(*) from test_data2 where char_date = to_date('20101201','YYYYMMDD')

select count(*) from test_data3 where char_date = to_timestamp('20101201','YYYYMMDD')

select count(*) from test_data1 where char_date between '20101201' and '20101203'

select count(*) from test_data2 where char_date between to_date('20101201','YYYYMMDD') and to_date('20101203','YYYYMMDD')

select count(*) from test_data3 where char_date between to_timestamp('20101201','YYYYMMDD') and to_timestamp('20101203','YYYYMMDD')

select count(*) from test_data1 where char_date in( '20101201','20101202','20101203')

select count(*) from test_data2 where char_date in( to_date('20101201','YYYYMMDD'),to_date('20101202','YYYYMMDD'),to_date('20101203','YYYYMMDD'))

select count(*) from test_data3 where char_date in( to_timestamp('20101201','YYYYMMDD'),to_timestamp('20101202','YYYYMMDD'),to_timestamp('20101203','YYYYMMDD'))

select count(*) from test_data1 where char_date = '20101212'

select count(*) from test_data2 where char_date = to_date('20101212','YYYYMMDD')

select count(*) from test_data2 where char_date = to_timestamp('20101212','YYYYMMDD')