No, because queries are read consistent: they see the data as of the timestamp the query began. So SYSDATE and SYSTIMESTAMP will produce the date and timestamp at the time the query begins, right? You can see this in this little test:
rwijk@ORA11G> select sysdate
2 , systimestamp
3 from dual
4 /
SYSDATE SYSTIMESTAMP
------------------- -----------------------------------------
24-07-2008 22:35:05 24-07-08 22:35:05,046000 +02:00
1 rij is geselecteerd.
rwijk@ORA11G> select min(n)
2 , max(n)
3 , min(d)
4 , max(d)
5 , min(t)
6 , max(t)
7 from ( select level n
8 , sysdate d
9 , systimestamp t
10 from dual
11 connect by level <= 2000000
12 )
13 /
MIN(N) MAX(N) MIN(D) MAX(D)
---------- ---------- ------------------- -------------------
MIN(T)
-------------------------------------------------------------
MAX(T)
-------------------------------------------------------------
1 2000000 24-07-2008 22:35:05 24-07-2008 22:35:05
24-07-08 22:35:05,062000 +02:00
24-07-08 22:35:05,062000 +02:00
1 rij is geselecteerd.
rwijk@ORA11G> select sysdate
2 , systimestamp
3 from dual
4 /
SYSDATE SYSTIMESTAMP
------------------- -----------------------------------------
24-07-2008 22:35:10 24-07-08 22:35:10,031000 +02:00
1 rij is geselecteerd.
The query takes 5 seconds, but the reported minimum and maximum sysdates and systimestamps are the time the query began, as you can see.
But a comment by James Padfield in this OTN-thread convinced me that it is not really read consistency that takes care of this behaviour of sysdate and systimestamp. He says:
I don't believe SYSDATE is truly read-consistent, if it was then references to SYSDATE within a serializable (or read-only) transaction would return the same value, it is trivial to show that they do not.
However It appears that SYSDATE has been made to behave as if it is read-consistent at the statement level and this implementation appears to involve re-write as previously shown. Possibly (and this is pure speculation) it is persisted somewhere at the start of the statement and this value is referenced by SYSDATE@!. This might give benefits in terms of both performance and statement level read-consistent behaviour (you might see the latter as a side-effect). I notice a similar rewrite is used with the USER function.
Of course the implementation is largely irrelevant as long as we clearly understand the behaviour.
Let's check the first remark. In a serializable transaction they produce different values:
rwijk@ORA11G> set transaction isolation level serializable
2 /
Transactie is ingesteld.
rwijk@ORA11G> select sysdate
2 , systimestamp
3 from dual
4 /
SYSDATE SYSTIMESTAMP
------------------- --------------------------------------
24-07-2008 22:35:10 24-07-08 22:35:10,031000 +02:00
1 rij is geselecteerd.
rwijk@ORA11G> exec dbms_lock.sleep(2)
PL/SQL-procedure is geslaagd.
rwijk@ORA11G> select sysdate
2 , systimestamp
3 from dual
4 /
SYSDATE SYSTIMESTAMP
------------------- --------------------------------------
24-07-2008 22:35:12 24-07-08 22:35:12,031000 +02:00
1 rij is geselecteerd.
So, this blows up the read consistency theory. But the first test showed it behaved as statement level read consistent. And the SYSDATE@! internal variable/function (so no, I was not swearing in the title) can be looked up by querying the filter_predicates of the plan_table. I also included the USER and SYSTIMESTAMP pseudo columns:
rwijk@ORA11G> explain plan
2 for
3 select dummy
4 from dual
5 where sysdate > date '2008-01-01'
6 and systimestamp > timestamp '2008-01-01 00:00:00'
7 and user != 'A'
8 /
Uitleg is gegeven.
rwijk@ORA11G> select filter_predicates
2 from plan_table
3 where filter_predicates is not null
4 /
FILTER_PREDICATES
--------------------------------------------------------------------------------
USER@!<>'A' AND SYS_EXTRACT_UTC(SYSTIMESTAMP(6))>SYS_EXTRACT_UTC(TIMESTAMP' 2008
-01-01 00:00:00,000000000') AND SYSDATE@!>TO_DATE(' 2008-01-01 00:00:00', 'syyyy
-mm-dd hh24:mi:ss')
1 rij is geselecteerd.
James' story about SYSDATE sounds very plausible to me. But now I wonder why SYSTIMESTAMP is not converted to SYSTIMESTAMP@! but to SYS_EXTRACT_UTC(SYSTIMESTAMP(6)), especially since SYSTIMESTAMP also behaves as statement level read consistent...
rwijk@ORA11G> select sysdate@!
2 , user@!
3 from dual
4 /
SYSDATE@! USER@!
------------------- ------------------------------
24-07-2008 23:11:41 RWIJK
1 rij is geselecteerd.
rwijk@ORA11G> select systimestamp@!
2 from dual
3 /
select systimestamp@!
*
FOUT in regel 1:
.ORA-00923: FROM-sleutelwoord is niet gevonden waar verwacht.
I suspect it is evaluated once as the start of the query execution and after that behaves like a literal/bound variable. SYS_CONTEXT calls would work the same way too.
ReplyDeleteThis makes more sense when you think of it in the context of a WHERE clause rather than the SELECT clause. If you have a table scan that takes 1 minute, and you are selecting where a date is greater than SYSDATE, you don't want comparisons between sixty different sysdates, all 1 second apart.
And it couldn't be read consistency because it isn't stored anywhere, so there's no block to check for SCNs
In Oracle12c the SYSDATE is giving the date with time 00hour.
ReplyDeleteFor example: select * from table where insert_time <= SYSDATE order by insertime;
when i execute this query i expect SYSDATE should provide the time when query started.
It's the behavior in 11g.
But in 12c SYSDATE is giving 00hour. How can i get the 11g behavior?