Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id gBR0xXH14756 for ; Thu, 26 Dec 2002 18:59:33 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id gBR0xXc14751 for ; Thu, 26 Dec 2002 18:59:33 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id NAA58868; Thu, 26 Dec 2002 13:41:48 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 00522919; Thu, 26 Dec 2002 12:59:17 -0800 Message-ID: Date: Thu, 26 Dec 2002 12:59:17 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Richard Ji Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Richard Ji Subject: RE: Cache on sysdate? --From 9i performance planning manual Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit 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 : > Raj: > > Both are same. It is internally translated as a select call to dual. > > KG > > --- "Jamadagni, Rajendra" 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@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@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@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@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).