(See bottom for update)
A very common application requirement is to stamp rows as they are created and/or modified with the date/time that the modification occurred. Typically this is done with a simple trigger:
create or replace trigger STAMP_RBI before INSERT on STAMP for each row begin :new.date_col := sysdate; end; /
This works fine except that calls to SYSDATE implicitly invoke a recursive SQL, namely 'select sysdate from dual'. This means a context switch for every row that is created.
In my simple example above, creating 50,000 rows using
insert into STAMP (x) select rownum from sys.source$ where rownum < 50000;
took 26 seconds.
However, PL/SQL offers a routine called DBMS_UTILITY.GET_TIME which is gives 100th's of a second from some arbitrary epoch. Most texts will state that this is not useful for timestamps, but it CAN be used with a little arithmetic. We can then build our own sysdate function, called "SYSDA.TE" in the source below.
create or replace package sysda is function te return date; end; / create or replace package body sysda is v_dte date; v_csec number; function te return date is begin return v_dte+(dbms_utility.get_time-v_csec)/8640000; end; begin select sysdate,dbms_utility.get_time into v_dte, v_csec from dual; end; /
This simply stores the SYSDATE and the DBMS_UTILITY.GET_TIME at the same instance on the first instantiation of the package, and then calculates deltas to return the current date. The trigger now needs a slight change
create or replace trigger STAMP_RBI before INSERT on STAMP for each row begin :new.date_col := sysda.te; end; /
Using the same test as above of 50,000 rows, the new code completed the test in only 11 seconds.
Important Note: I haven't confirmed with 8i, but for older versions, DBMS_UTILITY.GET_TIME does wrap around, so being a 32-bit integer, this solution is only appropriate for databases that come down before it wraps (that is, they get bounced at least once every 240 days or so)
From about 8.1.7, Oracle have resolved this problem. A quick look at the contents in standard.sql (and its subordindate files) shows the following snippet:
function pessdt return DATE; pragma interface (c,pessdt); -- Bug 1287775: back to calling ICD. -- Special: if the ICD returns NULL, that means we should do the old -- 'SELECT SYSDATE FROM DUAL;' thing. This allows us to do the SELECT from -- PL/SQL rather than having to do it from C (within the ICD.) function sysdate return date is d date; begin d := pessdt; if (d IS NULL) then select sysdate into d from sys.dual; end if; return d; end;
So it looks like that Oracle will attempt to get the date from a system call, and if that does not work for some reason (???), then it will fallback to 'select sysdate from dual'.