Oracle-L: RE: Cache on sysdate? --From 9i performance planning manual

From: Richard Ji <richard.ji_at_mobilespring.com>
Date: Thu, 26 Dec 2002 12:59:17 -0800
Message-ID: <F001.00522919.20021226125917@fatcity.com>

That's because doing dt := sysdate is more optimized, it's still select sysdate into dt from dual but more optimized. select from dual doing 4 db block gets in 8i and 2 db block gets in 9i, but you can tune it to cut it down.

Richard Ji

-----Original Message-----
Sent: Thursday, December 26, 2002 2:45 PM To: Multiple recipients of list ORACLE-L

That's what I thought too. But the results of testing are somewhat different.
Maybe it's evaluated within the PL/SQL engine and does not require a context

switch to the SQL engine.

Platform : Sun Solaris 2.6
Oracle : 8.1.7.4 (32 bit)

DEV:43#14739-23049>@dual1
DEV:43#14739-23049>declare
  2 dt date;
  3 begin
  4

  5          for i in 1..10000
  6          loop
  7      select sysdate into dt from dual;

8 -- dt := sysdate;
9 end loop;
10 end;
11 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.97

DEV:43#14739-23049>@dual1
DEV:43#14739-23049>declare
  2 dt date;
  3 begin
  4

  5          for i in 1..10000
  6          loop
  7  --    select sysdate into dt from dual;
  8      dt := sysdate;

9 end loop;
10 end;
11 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.92

Regards,
Denny

Quoting K Gopalakrishnan <kaygopal_at_yahoo.com>:

> Raj:
>
> Both are same. It is internally translated as a select call to dual.
>
> KG
>
> --- "Jamadagni, Rajendra" <Rajendra.Jamadagni_at_espn.com> wrote:
> > Can someone please explain me why you have to use
> >
> > select sysdate
> > from dual
> > /
> >
> > when
> >
> > my_date_Var := sysdate;
> >
> > just works fine? Maybe I am clueless ... but I can take an
> > explanation
> >
> > Raj
>
> =====
> Have a nice day !!
> ------------------------------------------------------------
> Best Regards,
> K Gopalakrishnan,
> Bangalore, INDIA.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: groups_at_koovakattu.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Richard Ji
  INET: richard.ji_at_mobilespring.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Dec 26 2002 - 14:59:17 CST