Hi,
In one of the scripts in my company, I find that a function takes in a varchar2 parameter and compares it with a date in a query. Somewhat like the following (highly reduced):
create or replace function fn_get_biz_name (p_yearmon in varchar2)
return varchar2
as
l_name business.name%type;
begin
select name
into l_name
from business
where (p_yearmon is null or to_char(effective_date,'yyyymm') <= p_yearmon);
return l_name;
exception
when others then
raise;
end;
/
I know Tom Kyte has mentioned many a time to compare dates with dates and strings with strings, but what exactly is the reason for doing so ? How exactly the optimizer "fooled" ? I tested it out for a couple of cases, but the plans do not look drastically different...
test@ORA10G>
test@ORA10G> @ver
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
test@ORA10G>
test@ORA10G>
test@ORA10G> drop table t;
Table dropped.
test@ORA10G> create table t (x number, y varchar2(10), z date);
Table created.
test@ORA10G> insert into t (x,y,z)
2 select
3 rownum,
4 chr(mod(rownum-1,26)+97),
5 sysdate - mod(rownum,365)
6 from all_objects,
7 (select rownum r from all_objects where rownum<=10);
407120 rows created.
test@ORA10G> commit;
Commit complete.
test@ORA10G>
test@ORA10G> set timing on
test@ORA10G> set autotrace traceonly explain stat
test@ORA10G> select *
2 from t
3 where to_char(z,'yyyymm') between '200701' and '200710';
289941 rows selected.
Elapsed: 00:00:03.03
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 281K| 7985K| 302 (18)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 281K| 7985K| 302 (18)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_CHAR(INTERNAL_FUNCTION("Z"),'yyyymm')>='200701' AND
TO_CHAR(INTERNAL_FUNCTION("Z"),'yyyymm')<='200710')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
29 recursive calls
1 db block gets
20526 consistent gets
0 physical reads
176 redo size
7142427 bytes sent via SQL*Net to client
213000 bytes received via SQL*Net from client
19331 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
289941 rows processed
test@ORA10G>
test@ORA10G> select *
2 from t
3 where trunc(z,'mm') between to_date('20070101','yyyymmdd') and to_date('20071001','yyyymmdd');
289941 rows selected.
Elapsed: 00:00:02.79
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 281K| 7985K| 319 (23)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 281K| 7985K| 319 (23)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TRUNC(INTERNAL_FUNCTION("Z"),'fmmm')>=TO_DATE('2007-01-01
00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
TRUNC(INTERNAL_FUNCTION("Z"),'fmmm')<=TO_DATE('2007-10-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
20507 consistent gets
0 physical reads
0 redo size
7142427 bytes sent via SQL*Net to client
213000 bytes received via SQL*Net from client
19331 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
289941 rows processed
test@ORA10G>
test@ORA10G> -- ============================================================================================
test@ORA10G>
test@ORA10G> select *
2 from t
3 where to_char(z,'yyyymm') <= '200710';
289941 rows selected.
Elapsed: 00:00:02.73
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 281K| 7985K| 300 (18)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 281K| 7985K| 300 (18)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_CHAR(INTERNAL_FUNCTION("Z"),'yyyymm')<='200710')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
20507 consistent gets
0 physical reads
0 redo size
7142427 bytes sent via SQL*Net to client
213000 bytes received via SQL*Net from client
19331 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
289941 rows processed
test@ORA10G>
test@ORA10G> select *
2 from t
3 where trunc(z,'mm') <= to_date('20071001','yyyymmdd');
289941 rows selected.
Elapsed: 00:00:02.60
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 281K| 7985K| 317 (22)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 281K| 7985K| 317 (22)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TRUNC(INTERNAL_FUNCTION("Z"),'fmmm')<=TO_DATE('2007-10-01
00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
20507 consistent gets
0 physical reads
0 redo size
7142427 bytes sent via SQL*Net to client
213000 bytes received via SQL*Net from client
19331 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
289941 rows processed
test@ORA10G>
test@ORA10G> set timing off
test@ORA10G> set autotrace off
test@ORA10G>
Would it matter in a case like the above (the function script) wherein the comparison would work since the lengths are the same ?
'120' < '9' when compared as characters (which is false), but
'120' < '900' when compared as characters or numbers (which is true) since lengths are the same
Thanks,
pratz