Re: SYSDATE

In article <01bd0959$4fe9bea0$a049100a_at_pc743-sode.seurope.ikea.com>, "cb" <Corinna.Becker_at_memo.ikea.com> wrote:

> Hello Erik,
> your DBA is right in so far as you can shorten the UPDATE statement the
> following way:
> update <table_name>
> set date_field=SYSDATE
> where <some condition>;

This is true, which means the DBA is *wrong*. The function sysdate is *not* evaluated "internally" by selecting it from dual. This can be seen if the trace file is examined for the above version of the statement: there are no recursive statements generated for a select from dual
>
> That means that you don't need a select statement for SYSDATE.
> As far as I could draw from my handbooks SYSDATE is a function.
>
> The description for a pseudo-column is that a pseudo-column is a column
> that does not exist in a table but that you can treat as if it would. That
> means you can select it (from DUAL), you can use it in where clauses etc.
> (Only exception: You can't update it.) I think this description makes
> SYSDATE a pseudo-column. Another example for a pseudo column is the ROWID.
> The ROWID makes a row unique for the system.
>
> DUAL is a dummy table. (Try "select * from dual;"!) As you can't call
> system functions like SYSDATE as you're normally used to (call function
> (...);) the function is "selected" from this dummy table. This is done
> automatically. Only if you want to have the SYSDATE without any other
> contents you can select it from the DUAL table.
>
> Hope these informations help you
> Best regards
> Corinna Becker
>
> Erik Bowe <ebowe_at_SPSU.edu> wrote in article
> <01bd077a$12f51ca0$9af445cf_at_ebowe>...
> >
> > Got a quick question on SYSDATE...
> >
> > I'm currently working on project where we are migrating some Pro*C, UNIX
> > processes to make them fully Oracle 7 compliant by removing "workarounds"
> > that were necessary in Oracle 6. One of the major changes is converting
 a
> > number of UPDATE statements that read as follows:
> >
> > UPDATE <tablename>
> > SET <date field 1> = (SELECT SYSDATE FROM DUAL),
> > <date field 2> = (SELECT SYSDATE FROM DUAL),
> > ....
> >
> > WHERE <some condition>
> >
> > ...to just using SYSDATE without the SELECT.
> >
> > Unfortunately, I'm being told by a DBA that by just putting SYSDATE, I
> > won't gain anything but readability in the code and that internally
 Oracle
> > 7 is SELECTing SYSDATE from the DUAL table when used in this context.
 They
> > go on further to say that SYSDATE is actually a pseudo-column.
> >
> > As I understand it, SYSDATE is a function and not a pseudo-column and by
> > using it WITHOUT the SELECT, it should run a little quicker
 (unfortunately
> > I haven't had time to test if that's the case).
> >
> > Can anyone tell me as to what's really going on with SYSDATE within the
> > context of UPDATE??? Is it a function? ...or is it being translated by
> > Oracle behind the scenes to a sub-query within the UPDATE?
> >
> > Any help would be appreciated!!!
> >
> > Thanks, Erik
> > ebowe_at_SPSU.edu
> >
 

-- 
To get random signatures put text files into a folder called ³Random
Signatures² into your Preferences folder.