Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SELECT ... FROM DUAL ?

Re: SELECT ... FROM DUAL ?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 17 Sep 1999 07:30:37 -0400
Message-ID: <ESLiNz5bs8ncf9YmkWe6nWudW7Ah@4ax.com>


A copy of this was sent to pbolduc_at_my-deja.com (if that email address didn't require changing) On Fri, 17 Sep 1999 07:40:42 GMT, you wrote:

>I have seen PL/SQL code (see
>http://www.oracle.com/oramag/code/cod02189.html) such as:
>
> SELECT SUBSTR (vissuedt, 3, 3) INTO vmonth FROM dual;
>
>my question is: would not this be more efficient written as:
>
> vmonth := SUBSTR(vissuedt,3,3);
>
>Due to my understanding there is a small, but definate cost from
>switching from PL/SQL context to SQL context. I understand with things
>like sequences need to be selected from DUAL.
>

Well, i agree here. That should be an assignment, not a select from DUAL. Where I believe this behaviour comes from is a developer who has been doing Oracle development for a long time and remembers old forms 2.x triggers. They didn't really have full blown plsql -- to do a substr you had to select from dual.

SUBSTR and many other functions should be coded as assignments whenever possible. Some functions (decode) must be used in a SELECT.

>PL/SQL code I have seen and wanted to change right away:
>
> SELECT USER INTO v_username FROM dual;
>
> SELECT TRUNC(SYSDATE) INTO v_today FROM DUAL;
>

This code doesn't matter as much. For example, in Oracle8i release 8.1 i just executed:

alter session set sql_trace = true;

declare

        x       varchar2(25);
        y       date;
begin
        for i in 1 .. 100 loop
                x := user;
                y := trunc(sysdate);
        end loop;

end;
/
exit

My tkprof shows:

declare

    x varchar2(25);
    y date;
begin

    for i in 1 .. 100 loop

        x := user;
        y := trunc(sysdate);

    end loop;
end;

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.05       0.05          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.06       0.06          0          0          0           1

Misses in library cache during parse: 1 Optimizer goal: CHOOSE
Parsing user id: 13887 (TKYTE)


SELECT USER
FROM
 SYS.DUAL call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0
Execute    100      0.00       0.00          0          0          0           0
Fetch      100      0.01       0.01          0        100        400         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      201      0.01       0.01          0        100        400         100

Misses in library cache during parse: 0 Optimizer goal: CHOOSE
Parsing user id: 13887 (TKYTE) (recursive depth: 1)

Rows Row Source Operation

-------  ---------------------------------------------------

    100 TABLE ACCESS FULL DUAL Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
    100 TABLE ACCESS (FULL) OF 'DUAL'

SELECT SYSDATE
FROM
 SYS.DUAL call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0
Execute    100      0.01       0.01          0          0          0           0
Fetch      100      0.01       0.01          0        100        400         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      201      0.02       0.02          0        100        400         100

Misses in library cache during parse: 0 Optimizer goal: CHOOSE
Parsing user id: 13887 (TKYTE) (recursive depth: 1)

Rows Row Source Operation

-------  ---------------------------------------------------

    100 TABLE ACCESS FULL DUAL Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
    100 TABLE ACCESS (FULL) OF 'DUAL'

See -- it went to the database for that anyway (in different release levels of Oracle -- you may/may not see this really really recursive sql. for example in 8.0.3, the select user from dual shows but NOT the select sysdate from dual).

Not only that but it went 100 TIMES to the database!

This does bring up an optimization when using USER and SYSDATE. Instead of coding:

declare

        x       varchar2(25);
        y       date;
begin
        for i in 1 .. 100 loop
                x := user;
                y := trunc(sysdate);
                ...
        end loop;

end;
/

You should code:

declare

        x       varchar2(25) default USER;
        y       date default trunc(sysdate);
begin
        for i in 1 .. 100 loop 
                ...
        end loop;

end;
/

this can make a *huge* difference in some apps. beware tho: getting USER this way is OK since USER never changes. Getting sysdate this way is *probably* OK but it really depends on your application (what happens if this loop is executed near midnight? the value of y in the first loop might change whereas in the second, its more or less a constant).

when possible, its a good practice to:

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.

>Perhaps I am just being picky.
>

not at all in my opinion. You should see the speed up you can get from getting the DATE once per session as opposed to N times per session. Whats more important then choosing:

   x := trunc(sysdate);

over

   select trunc(sysdate) into x from dual;

is choosing on whether or not to reuse x as a constant global (since x:=trunc(sysdate) is going to run that query anyway)

>Phil Bolduc
>North Vancouver, Canada
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Sep 17 1999 - 06:30:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US