Re: SELECT ... FROM DUAL ?

From: Frank Hubeny <fhubeny_at_ntsource.com>
Date: Fri, 17 Sep 1999 22:39:28 -0500
Message-ID: <37E30970.566B8758@ntsource.com>

I was impressed by the thoroughness of the analysis which Thomas Kyte provided about the use of sysdate and user -- especially with the actual display of tkprof output showing that a reference to sysdate in a plsql program was the same as a select from dual.

A slight variation on his method of avoiding multiple calls to sysdate by using a global constant would be to use a global variable returned via a function call as follows:

create or replace package body my_globals as

    g_user date;

    function getdate return date
    is
    begin

        if g_user is null then
            g_user := sysdate;
        end if;
        return g_user;

    end;
end;

This way the call to sysdate is made only if required and is only made once.

For web applications where the user's connection is brief, this might be a desirable way to handle calls to sysdate.

Frank Hubeny

Thomas Kyte wrote:

> create or replace package my_globals
> as
> g_user constant varchar2(30) default USER;
> g_date constant date default SYSDATE;
> g_sessionid constant number default userenv( 'sessionid' );
> end;
> /
>
> and then refer to my_globals.g_user, my_globals.g_date, etc in your code as they
> are plsql variables and won't go back to the kernel for values each time they
> are referenced.
>
> the *downside* to this approach is that if your procedure only needs the USER
> psuedo column -- it'll get the date and sessionid from the database anyway (you
> either get ALL of them or none of them in a session). In a large program that
> uses all of them, this won't matter. In a small program, since its done but
> once -- its probably not even noticable.
>
Received on Fri Sep 17 1999 - 22:39:28 CDT