Data types


Even if you've hard-coded a simple TO_DATE command around some literal text, it still costs you CPU to do that conversion. Better perhaps to store that literal in a variable. Consider the example below:

declare
  ld_date   date := TO_DATE('01-JAN-1980','DD-MON-YYYY');
BEGIN
for i in 1 .. 1000000 loop
   if sysdate+i/10000 > TO_DATE('01-JAN-1980','DD-MON-YYYY') then
       null;
   end if;
end loop;
end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1     17.11      16.85          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     17.12      16.86          0          0          0           1

whereas if store that TO_DATE in a variable, the CPU cost drops right down.

declare
  ld_date   date := TO_DATE('01-JAN-1980','DD-MON-YYYY');
BEGIN
for i in 1 .. 1000000 loop
   if sysdate+i/10000 > ld_date then
       null;
   end if;
end loop;
end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      6.28       6.16          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      6.29       6.17          0          0          0           1

So, if you're calling a routine frequently, and that routine contains date constants, then store them in variables.