to_date and to_timestamp

I noticed the 9.2.0.7 and 10.2.0.1 CBO evaluates to_date and to_timestamp differently.
Here’s a test:

drop table t;
create table t (c timestamp(9));
— insert a whole year worth of timestamps, 5 minutes apart. Gather stats
begin
for i in 0..364 loop
for j in 0..12*24-1 loop
insert into t values (to_date( ‘2003-01-01 00:00:00′,’YYYY-MM-DD HH24:MI:SS’ ) + i + j/24/12);
end loop;
end loop;
dbms_stats.gather_table_stats(user, ‘T’,cascade=>true);
end;
/

— Now use to_date
explain plan for
select * from t where c between to_date( ‘2003-02-14 00:00:00′,’yyyy-mm-dd hh24:mi:ss’ )
and to_date( ‘2003-02-18 00:00:00′,’yyyy-mm-dd hh24:mi:ss’ );
And here’s the plan:
 
————————————————————————-
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
————————————————————————-
|   0 | SELECT STATEMENT     |             |  1154 | 12694 |    75  (59)|
|*  1 |  TABLE ACCESS FULL   | T           |  1154 | 12694 |    75  (59)|
————————————————————————-
 
Predicate Information (identified by operation id):
—————————————————
 
   1 – filter(“T”.”C”<=TIMESTAMP’2003-02-18 00:00:00′ AND
              “T”.”C”>=TIMESTAMP’2003-02-14 00:00:00′ )

So far so good. CBO converts to_date to timestamp constant and gets the correct cardinality. 4 days * 24 * 12 = 1152. 1154 is very close.

— now to_timestamp
explain plan for
select * from t where c between to_timestamp(‘2003-02-14 00:00:00.000′,’yyyy-mm-dd hh24:mi:ss.ff3’ )
and to_timestamp(‘2003-02-18 00:00:00.000′,’yyyy-mm-dd hh24:mi:ss.ff3’ );

This is the plan
 
————————————————————————-
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
————————————————————————-
|   0 | SELECT STATEMENT     |             |   263 |  2893 |   179  (83)|
|*  1 |  FILTER              |             |       |       |            |
|*  2 |   TABLE ACCESS FULL  | T           |   263 |  2893 |   179  (83)|
————————————————————————-
 
Predicate Information (identified by operation id):
—————————————————
 
   1 – filter(TO_TIMESTAMP(‘2003-02-14
              00:00:00.000′,:B1)<=TO_TIMESTAMP(‘2003-02-18 00:00:00.000’,:B2))
   2 – filter(“T”.”C”>=TO_TIMESTAMP(‘2003-02-14 00:00:00.000’,:B1) AND
              “T”.”C”<=TO_TIMESTAMP(‘2003-02-18 00:00:00.000’,:B2))

Notice how Oracle converts the format string to bind variables. That’s when the 0.25% rule kicks in and to_timestamp behaves almost like any PL/SQL function. 365*24*12*.05*.05=262.8. That’s where the cardinality 263 comes from.

This could result in disaster when the num_rows for T is small and T is joined to other tables. If CBO evaluates the cardinality down to 1…

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: