Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sam.

Asked: October 06, 2001 - 7:47 pm UTC

Last updated: December 04, 2017 - 10:19 am UTC

Version: 8.1.7

Viewed 100K+ times! This question is

You Asked

Hi Tom

Feels good to post a question after quite some time.


Look at the following :


SQL> desc dual
Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)

SQL> select * from dual;

D
-
X

SQL> select count(*) from dual;

COUNT(*)
----------
1

SQL> select 'sdfsdfsdfsdfsdfsdf' from dual;

'SDFSDFSDFSDFSDFSD
------------------
sdfsdfsdfsdfsdfsdf

SQL> select 3434334 from dual;

3434334
----------
3434334

SQL> truncate table dual;
truncate table dual
*
ERROR at line 1:
ORA-04020: deadlock detected while trying to lock object
3510531527724648449742036


SQL> delete from dual;
delete from dual
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> connect system/manager
Connected.
SQL> /

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from dua;
select * from dua
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from dual;

no rows selected



1.What is the dual table, what is its purpose.
2.Why does it contain only one column with datatype varchar2, why not
number .
3.Does it contain one row by default.
4.why do we
usually SELECT USER FROM DUAL,
why cant I do it like
SQL> select USER FROM EMP WHERE ROWNUM<2;

USER
------------------------------
SCOTT


5.Does this mean that we are using the dual table only for the convenience that it has only one row, and it will return only one row back , when we give queries like

SELECT USER FROM DUAL

6.SQL> SELECT COUNT(*) FROM DUAL;

COUNT(*)
----------
2

SQL> INSERT INTO DUAL VALUES ('X');

1 row created.

SQL> INSERT INTO DUAL VALUES ('X');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM DUAL;

COUNT(*)
----------
4

SQL> SELECT * FROM DUAL;

D
-
X


Why is it returning only one row , it has to return 4 rows , when it has allowed me to insert 4 rows.

7.SQL> delete from dual;

1 row deleted.

SQL> select * from dual;

D
-
X

SQL> select count(*) from dual;

COUNT(*)
----------
3


I want to delete all the rows , I do a delete from dual, and oooo mama
look at it , it deletes only one row, why?

8.SQL> delete from dual;

1 row deleted.

SQL> delete from dual;

1 row deleted.

SQL> select count(*) from dual;

COUNT(*)
----------
1

NOw we have only one row in dual.

lets create a example function.

SQL> create or replace function foo return number
2 as
3 x number;
4 begin
5 x:=1;
6 return 1;
7 end;
8 /

Function created.

SQL> select foo from dual;

FOO
----------
1

SQL> insert into dual values ('X');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM DUAL;

COUNT(*)
----------
2

SQL> select foo from dual;

FOO
----------
1

shouldnt it return 2 rows of value 1?


because , look below , I have got 14 rows back , because I had 14 rows in the table.

SQL> SELECT FOO FROM EMP;

FOO
----------
1
1
1
1
1
1
1
1
1
1
1

FOO
----------
1
1
1

14 rows selected.

SQL>

Kindly elucidate the concept of dual table, and if possible illustrate it.

Thank you

and we said...

Let me just start by saying -- DUAL is owned by SYS. SYS owns the data dictionary, therefore DUAL is part of the data dictionary. You are not to modify the data dictionary via SQL ever -- wierd things can and will happen -- you are just demonstrating some of them. We can make many strange things happen in Oracle by updating the data dictionary. It is neither recommend, supported nor a very good idea.

1.What is the dual table, what is its purpose.

dual is just a convienence table. You don't need to use it, you can use anything you want. The advantage to dual is the optimizer understands dual is a special one row, one column table -- when you use it in queries, it uses this knowledge when developing the plan.


2.Why does it contain only one column with datatype varchar2, why not
number .

truly, why not. Why not a date you would ask then. The column, its name, its datatype and even its value are NOT relevant. DUAL exists solely as a means to have a 1 row table we can reliably select from. Thats all.


3.Does it contain one row by default.

yes, when we build the database, we build dual and put a single row in it.

4.why do we
usually SELECT USER FROM DUAL,
why cant I do it like
SQL> select USER FROM EMP WHERE ROWNUM<2;


truly, why can't you? is something preventing you from doing so?? You can if you want. Me, I'll stick with "select user from dual". I know dual exists. I know it has at least 1 and at most 1 row. I know the optimizer knows all about dual and does the most efficient thing for me.

5) yes

6) the optimizer understands dual is a magic, special 1 row table. It stopped on the select * because there is to be one row in there. Its just the way it works. Hopefully you reset dual back to 1 row after your testing or you just totally broke your database!

7) like I said, duals magic, the optimizer knows all about what DUAL should be and does things based on that.

8) dual = magic. dual is a one row table however having more then 1 or less then one is dangerous. You are updating the data dictionary. You should naturally expect very bad things to happen.


Ok, here is some trivia for you out there. How did I do this:

SVRMGR> select * from dual;
D
-
X
1 row selected.

SVRMGR> ????????????????????;
Statement processed.

SVRMGR> select * from dual;
ADDR INDX INST_ID D
-------- ---------- ---------- -
01680288 0 1 X
1 row selected.


What was that magic command?

Rating

  (171 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

was that some synonym you created?

A reader, October 07, 2001 - 11:11 pm UTC

"each GV$ view contains an extra column named
INST_ID of datatype INTEGER" ..

Tom, this is the second time...

Mark A. Williams, October 07, 2001 - 11:29 pm UTC

Tom:

Drum roll please...

SVRMGR> alter database close;
Statement processed.
SVRMGR> select * from dual;
ADDR INDX INST_ID D
-------- ---------- ---------- -
00FA6E50 0 1 X
1 row selected.
SVRMGR>

That's 2 now... :) (the other was the "cluster" challenge)

- Mark

Weekly Challenge

Mark A. Williams, October 08, 2001 - 9:32 am UTC

Tom:

Not that you don't already have enough to do... but, maybe you could come up with some kind of a weekly challenge? I really enjoy figuring out your challenges. I certainly didn't know the answer to this one until you posed it...

Anyway, thanks,

- Mark

Tom Kyte
October 08, 2001 - 1:34 pm UTC

Thats a cool idea -- we'll see if we can put one together (gotta write the software for it ;)

Why Oracle would keep performance from us ?

Connor, October 08, 2001 - 10:15 am UTC

SQL> conn system/manager (or any other standard user)
Connected.
SQL> set autotrace on statistics
SQL> select * from dual;

D
-
X


Statistics
--------------------------------
          4  db block gets
          1  consistent gets

SQL> conn / as sysdba
SQL> set autotrace on statistics
SQL> select * from dual;

D
-
X


Statistics
--------------------------------
          0  db block gets
          0  consistent gets

I'd like to know why Oracle hasn't exposed the "fast dual access" to us mere mortals.  For example, if you create your own dual table (as an IOT) you get down to 1 get - maybe our good friends at Oracle could do this for us?  (Especially since they give us a Designer product which generates triggers which have 'x := sysdate, y := user' in alarming numbers throughout - and poor ol' dual take a hammering) 

Tom Kyte
October 08, 2001 - 1:36 pm UTC

SYSDBA, SYSOPER, "internal" and sys in general shouldn't be used for anything other then admin.  Things work differently (eg: no read only transactions), some things don't work (eg: triggers), some things appear to work but don't really (eg: set autotrace).

According to this, it takes no work for SYSDBA to do anything -- its not just DUAL:


ops$tkyte@ORA817DEV.US.ORACLE.COM> connect / as sysdba
Connected.
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace on
ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from scott.emp;

  COUNT(*)
----------
        14


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'EMP_PK' (UNIQUE) (Cost=1 Card=14)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from all_objects;

  COUNT(*)
----------
     16913


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     FILTER
   3    2       NESTED LOOPS
   4    3         TABLE ACCESS (FULL) OF 'USER$'
   5    3         TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
   6    5           INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
   7    2       TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
   8    7         INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
   9    2       NESTED LOOPS
  10    9         FIXED TABLE (FULL) OF 'X$KZSRO'
  11    9         TABLE ACCESS (BY INDEX ROWID) OF 'OBJAUTH$'
  12   11           INDEX (RANGE SCAN) OF 'I_OBJAUTH2' (NON-UNIQUE)
  13    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  14    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  15    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  16    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  17    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  18    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  19    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  20    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  21    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  22    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  23    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  24    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  25    2       FIXED TABLE (FULL) OF 'X$KZSPR'
  26    2       FIXED TABLE (FULL) OF 'X$KZSPR'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


 

But why 5 gets...?

Connor, October 08, 2001 - 5:18 pm UTC

I take your point, but as I said, an IOT dual gets you down to 1 get. Surely they could give us that? Yes, I know, I can just go off any make my a MY_DUAL iot and stop bugging Oracle and you :-), but it would be nice if they took advantage of the very features they keep plugging for us.

Tom Kyte
October 08, 2001 - 9:17 pm UTC

demonstrate for me:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table my_dual ( x int primary key ) organization index;
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into my_dual values ( 1 );
1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select sysdate from my_dual;

SYSDATE
---------
08-OCT-01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
   1    0   INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_19186' (UNIQUE) (Cost=1 Card=1)




Statistics
----------------------------------------------------------
          0  recursive calls<b>
          4  db block gets
          2  consistent gets</b>
          0  physical reads
          0  redo size
        371  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> select sysdate from dual;

SYSDATE
---------
08-OCT-01


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DUAL'




Statistics
----------------------------------------------------------
          0  recursive calls<b>
          4  db block gets
          1  consistent gets</b>
          0  physical reads
          0  redo size
        371  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed 

Test Results

Mark A. Williams, October 08, 2001 - 11:10 pm UTC

Well, not that anyone is asking, but this is what I got:

SQL> connect scott
Enter password:
Connected.
SQL> create table my_dual (dummy varchar2(1) primary key) organization index;

Table created.

SQL> insert into my_dual values('X');

1 row created.

SQL> commit;

Commit complete.

SQL> analyze table my_dual compute statistics
  2  for table
  3  for all indexes
  4  for all indexed columns;

Table analyzed.

SQL> set autotrace on
SQL> select sysdate from my_dual;

SYSDATE
---------
08-OCT-01

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
   1    0   INDEX (FULL SCAN) OF 'SYS_IOT_TOP_31083' (UNIQUE) (Cost=1
          Card=1)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        387  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select sysdate from dual;

SYSDATE
---------
08-OCT-01

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DUAL'




Statistics
----------------------------------------------------------
          0  recursive calls
          2  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        387  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 

Tom Kyte
October 09, 2001 - 8:02 am UTC

Yes, you are seeing the effect of an INDEX scan instead of a FULL scan. See the followup to connor's comment below for details....

no one has quite answered the trivia yet

Doug, October 08, 2001 - 11:17 pm UTC

I vauguely remember this from a year or two ago - but..
No one has explained
svrmgrl> ????????????????????
It's not a procedure with a normal name.. it's not a synonym that closes the database so we can get are gv columns. Still a piece missing I think.


Tom Kyte
October 09, 2001 - 8:00 am UTC

Nope, Mark Williams (second comment) got it right with:

SVRMGR> alter database close;

closing the database makes this special dual table come into play (there for
RMAN to have a dual table to select from even when the database isn't fully up)



x$dual

Nick Josephson, October 09, 2001 - 12:18 am UTC

Tom,

My guess is that ????... denotes some sort of help
command and Oracle returns the row from x$dual.

However when I tried on W2K, Oracle 8.1.7, the command
does not return the expected result

-Nick

Tom Kyte
October 09, 2001 - 7:59 am UTC

Nope, Mark Williams (second comment) got it right with:

SVRMGR> alter database close;

closing the database makes this special dual table come into play (there for RMAN to have a dual table to select from even when the database isn't fully up)

Demo as requested

Connor, October 09, 2001 - 4:11 am UTC

(I've cut out the stats that are not important)

mcdonac@db1> create table xdual ( dummy varchar2(1) primary key ) organization index;

Table created.

mcdonac@db1> insert into xdual values ('X');

1 row created.

mcdonac@db1> commit;

Commit complete.

mcdonac@db1> set autotrace on statistics;
mcdonac@db1> select * from dual; <= NORMAL DUAL

D
-
X


Statistics
----------------------------------------------------------
4 db block gets
1 consistent gets

mcdonac@db1> select * from xdual; <= MY VERSION (NOT ANALYZED)

D
-
X


Statistics
----------------------------------------------------------
4 db block gets
2 consistent gets


mcdonac@db1> analyze table xdual compute statistics;

Table analyzed.

mcdonac@db1> select * from xdual; <= MY VERSION (ANALYZED)

D
-
X


Statistics
----------------------------------------------------------
0 db block gets
1 consistent gets

mcdonac@db1> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.1.1 - Production
PL/SQL Release 8.1.7.1.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.1.0 - Production
NLSRTL Version 3.4.1.0.0 - Production


Tom Kyte
October 09, 2001 - 8:08 am UTC

Ok, here you go -- my 2 cents worth.

you are seeing the difference between a FULL scan and an INDEX scan. In order for the index scan to happen, you need to use CBO with stats.

Billions of apps rely on DUAL. It is safe to say that EVERY Oracle application in the world relies on DUAL.

Many sites do not yet fully use the CBO, they do not gather stats.

If we took the above approach, all sites would be flung immediately into CBO mode as soon as they hit DUAL. The effects would be enormous, people would be really mad.

We told people for I don't know how many years "internal -- going away, svrmgrl -- going away". they are still in a state of shock when the install 9i and internal is actually gone, svrmgrl actually doesn't exist. Can you imagine if all of a sudden the CBO was the default for many (but not all) queries?

Here is the classic example of backwards compatibility getting in the way of new advances. Fortunately dual being so small and frequently access, all is cached.

Reader

Reader, October 09, 2001 - 9:07 am UTC


What is the breakdown of db block gets anyway.
Does it comprise of data block as well as
data dictionary blocks

Tom Kyte
October 09, 2001 - 9:18 am UTC

Its the reading of the segment header to perform the full scan.

Reader

Reader, October 09, 2001 - 9:41 am UTC


The segment header from dba_segments is one block.
Then why there are 4 dba block gets. obj$ does show
that DUAL is owned by PERFSTAT, SYS, PUBLIC (synonym) ...
Could this contribute to more than 1 db block gets

Thanks

Tom Kyte
October 09, 2001 - 10:09 am UTC

They get it 4 times for whatever reason.  They used to get it two times:

ops$ora734@ORA734.WORLD> select * from dual;

D
-
X


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DUAL'

Statistics
----------------------------------------------------------
          0  recursive calls
          2  db block gets

now 4.

PERFSTAT should not have DUAL -- we didn't create that, someone on your team must have.  There should be only:

ops$tkyte@ORA8I.WORLD> select object_name, owner, object_type from dba_objects where object_name = 'DUAL';

OBJECT_NAME                    OWNER                          OBJECT_TYPE
------------------------------ ------------------------------ ------------------
DUAL                           SYS                            TABLE
DUAL                           PUBLIC                         SYNONYM

Even so, it is not the reason for the >1 db block gets.  They are just getting the block 4 times.

If you are querying obj$ and the type number = 10, thats a "NON-EXISTENT" trick object we set up when you reference a synonym in your code.  we use it to track dependencies.

Get in the habit of ignore the obj$ table and all SYS.tables -- in 9i, we make it really hard to query them by default and the views are what you should use day to day.

 

much ado about nothing

Mikito Harakiri, October 10, 2001 - 12:31 am UTC

4 block reads or 1, who cares?

Logically, 'dual' is a unit relation. Unit relation have no columns, but we can't have tables without columns in oracle. Another important relation is the one that has no rows, but we don't need that in practice.

BTW, the original poster didn't even care deleting "select 1 from dua" with trivial error messages and everybody is reading that nonsence. There must be posting guidelines!


Dual for variables

Meyer, December 10, 2002 - 4:32 pm UTC

Tom,

I have seen people do this in PL/SQL:

select 'ANY_VALUE'
into LOCAL_VARIABLE
from dual;

when they could just do this:

LOCAL_VARIABLE := 'ANY_VALUE' ;

Can you explain any overhead/performance or behind-the-scene-difference between the way they are handled.

Thanks,
Meyer


Tom Kyte
December 10, 2002 - 8:51 pm UTC

there is huge overhead for that -- hugely huge overhead.

I think the people that wrote that code have been using Oracle for a long long long time though. I remember in version 5, with iag/iap - precursor to forms - you did that cause you didn't HAVE plsql!

orasiva

Siva, December 10, 2002 - 9:46 pm UTC

Always great answers...

If dropped Dual table?

Sikandar Hayat Awan, December 20, 2002 - 6:53 am UTC

One of my friend asked me that he has dropped dual table so now how to bring it back?

Note: Dear TOM how can I enable cookies to this screen so Name, Email and Where is the world are you? should come automatically as this is my personal machine?

Tom Kyte
December 20, 2002 - 8:11 am UTC

step 1: revoke all privileges except for CREATE SESSION from this friend.
step 2: connect as sysdba
step 3:

create table dual
(dummy varchar2(1))
storage (initial 1)
/
insert into dual values('X')
/
create public synonym dual for dual
/
grant select on dual to public with grant option
/



Everything You Ever Wanted To Know About Dual...

Jerry, December 20, 2002 - 6:46 pm UTC


Tom Kyte
December 20, 2002 - 8:23 pm UTC

but wait for 10i, when the rules all change ;)

Thanks

Sikandar Hayat Awan, December 20, 2002 - 10:54 pm UTC

The friend is not in my Domain so I can't. Thanks again.

Why this fails ...

raju, December 21, 2002 - 2:22 am UTC

Hi Tom,

There were no session connected to the database except for SYS, but the command failed. Any ideas where I am going wrong.

SYS@ORA92:MYCOMP> conn sys/change_on_install@ora92 as sysdba
Connected.
SYS@ORA92:MYCOMP> alter database close;
alter database close
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected


Also, I tried shutting down the database and then restarting it. The shutdown was successful but the startup failed.

SYS@ORA92:MYCOMP>
SYS@ORA92:MYCOMP> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORA92:MYCOMP> startup
ORACLE instance started.
ORA-12516: TNS:listener could not find available handler with matching protocol stack
SYS@ORA92:MYCOMP>


I am using 9.2.0.1 database on WindowsNT.

Thanks



Tom Kyte
December 21, 2002 - 9:01 am UTC

and this has to do with DUAL how?

Dear Raju

Sikandar Hayat Awan, December 21, 2002 - 10:58 am UTC

Dear Raju,
Here Mr. TOM means that this thread is dealing with DUAL while your question is different from it.


He tried SVRMGR>alter database close;

A reader, December 22, 2002 - 1:22 am UTC

Hi Tom,
actually Raju tried the magic command to query dual table after closing the database.



Dual

Som, April 09, 2003 - 7:06 am UTC

It is great. Thanks Tom. I am learning lot of things.

Any links to what's coming...

K K, April 11, 2003 - 8:37 pm UTC

>> but wait for 10i, when the rules all change ;)

Hi Tom,
Any links/pointers on what magic is coming our way in 10i?
Thanks,



Tom Kyte
April 13, 2003 - 8:05 pm UTC

http: //not.yet/

????????????????????; == alter database close; ?

robert, April 15, 2003 - 6:41 pm UTC

Tom, so
"SVRMGR> ????????????????????;"
is equivalent to
"SVRMGR> alter database close;" ?

Has to be the same length ?

thanks



Tom Kyte
April 16, 2003 - 10:03 am UTC

read upwards in the reviews -- yes, it was alter database close

select user into vs l_user:=user

A reader, July 15, 2003 - 7:54 am UTC

Hi

What´s the difference between

declare
l_user varchar2(30);
begin
l_user := user;
end;
/

declare
l_user varchar2(30);
begin
select user
into l_user
from dual;
end;
/

same ?

Tom Kyte
July 15, 2003 - 10:03 am UTC

the second is more code?

FGAC

Matt, September 05, 2003 - 4:56 am UTC

Tracing some of our code we have noticed now that now we have introduced Fine Grained Access Control quite a high proportion of our query io is on select sys_context() from dual.

Looking at the differences with dual and my_dual - can you think of anyway to drive all FGAC from my_dual. Considering the amount of times Dual is being hit this may result in some not so insignificant performance gains.

We don't really want to replace sys.dual as I suspect support wouldnt be happy bunnies :D !



Tom Kyte
September 05, 2003 - 3:41 pm UTC

can you show me how you are using sys_context?

in plsql, it is most performant to use plsql variables.
in sql, sys_context

perhaps you need to simply copy the sys_context values into plsql globals?

Function

Matt, September 08, 2003 - 3:54 am UTC

The function calls sys_context each time it is called - we could use a PL/SQL global - but the problem is at what point do we refresh it based on a context change within the application. We use Oracle Apps, and there would be limitations on what we can do with their modules.

Tom Kyte
September 08, 2003 - 11:14 am UTC

reduce the number of calls to the bearest minimum.  if you have say 3 context items to look at, consider selecting them all from dual at once instead of individual assignments.   consider:

ops$tkyte@ORA920LAP> create or replace context my_ctx using p
  2  /

Context created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> create or replace procedure p ( p_name in varchar2, p_val in varchar2 )
  2  as
  3  begin
  4          dbms_session.set_context( 'my_ctx', p_name, p_val );
  5  end;
  6  /

Procedure created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> exec p( 'x1', 'a' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> exec p( 'x2', 'b' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> exec p( 'x3', 'c' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> create or replace procedure p1
  2  as
  3          x1 varchar2(5) default sys_context( 'my_ctx', 'x1' );
  4          x2 varchar2(5) default sys_context( 'my_ctx', 'x2' );
  5          x3 varchar2(5) default sys_context( 'my_ctx', 'x3' );
  6  begin
  7          null;
  8  end;
  9  /

Procedure created.

ops$tkyte@ORA920LAP> create or replace procedure p2
  2  as
  3          x1 varchar2(5);
  4          x2 varchar2(5);
  5          x3 varchar2(5);
  6  begin
  7          select sys_context( 'my_ctx', 'x1' ), sys_context( 'my_ctx', 'x2' ), sys_context( 'my_ctx', 'x3' )
  8            into x1, x2, x3
  9            from dual;
 10  end;
 11  /

Procedure created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> exec runStats_pkg.rs_start

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> exec begin for i in 1 .. 10000 loop p1; end loop; end;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> exec runStats_pkg.rs_middle

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> exec begin for i in 1 .. 10000 loop p2; end loop; end;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> exec runStats_pkg.rs_stop(10000)
Run1 ran in 342 hsecs
Run2 ran in 120 hsecs
run 1 ran in 285% of the time

Name                                  Run1        Run2        Diff
LATCH.shared pool                   30,145      10,186     -19,959
STAT...recursive calls              30,001      10,002     -19,999
STAT...buffer is not pinned co      30,000      10,000     -20,000
STAT...execute count                30,005      10,005     -20,000
STAT...table scans (short tabl      30,000      10,000     -20,000
STAT...table scan rows gotten       30,000      10,000     -20,000
STAT...table scan blocks gotte      30,000      10,000     -20,000
STAT...no work - consistent re      30,000      10,000     -20,000
LATCH.library cache                 60,148      20,177     -39,971
LATCH.library cache pin             60,077      20,080     -39,997
STAT...session logical reads        90,522      30,530     -59,992
STAT...consistent gets              90,003      30,005     -59,998
STAT...calls to get snapshot s      90,001      30,001     -60,000
STAT...session pga memory                0      65,536      65,536
LATCH.cache buffers chains         182,587      62,586    -120,001

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
333,798     113,727    -220,071    293.51%

PL/SQL procedure successfully completed.
 

Nice

Mary, December 28, 2003 - 2:38 am UTC

Dear sir,
well and wish the same from you.
I have a question regarding dual.
How can we use the dual table to print 'Hello world' for
10 times?Do you have a trick to do it?
Thanks in advance
Bye!

Tom Kyte
December 28, 2003 - 11:12 am UTC

  1  select *
  2  from (
  3  select 'hello world'
  4  from dual
  5  group by cube( 1,2,3,4 )
  6  )
  7* where rownum <= 10
ops$tkyte@ORA9IR2> /
 
'HELLOWORLD
-----------
hello world
hello world
hello world
hello world
hello world
hello world
hello world
hello world
hello world
hello world
 
10 rows selected.

(as offered by another reader on a different thread once...) 

OK

Mary, December 29, 2003 - 8:10 am UTC

Dear Sir,
Thanks for your response.But I don't have access to oracle
8i or above.Do you have a trick which works under oracle
7.3 or 8.0.Please do reply.
Thanks in advance.

Tom Kyte
December 29, 2003 - 10:40 am UTC

whats the business case -- what problem are you trying to solve, give me the original "question" you are trying to answer and perhaps we can give a solution.

10 rows from dual

Tarun, December 30, 2003 - 2:00 am UTC

Mary, You can do this for getting the rows in 7.3 or 8.0. Is this okey Tom?

select 'hello world' from dual
union all
select 'hello world' from dual
union all
select 'hello world' from dual
union all
select 'hello world' from dual
union all
select 'hello world' from dual
union all
select 'hello world' from dual
union all
select 'hello world' from dual
union all
select 'hello world' from dual
union all
select 'hello world' from dual
union all
select 'hello world' from dual
/

'HELLOWORLD
-----------
hello world
hello world
hello world
hello world
hello world
hello world
hello world
hello world
hello world
hello world

10 rows selected.


Tom Kyte
December 30, 2003 - 10:12 am UTC

it is, i'm just trying to figure out WHY the heck anyone would need to.

Why require SELECT FROM?

Jim Nasby, December 30, 2003 - 6:00 pm UTC

Since this is an Oracle trivia type of question...

Some databases support SELECT without a FROM clause; seems much easier than worrying about dual. Any idea why Oracle has never done this?

On the bright side, at least there is a dual... a certain other database suggests you SELECT blah FROM TABLE( VALUES(1) AS a) or something similar. Yuck.

Tom Kyte
December 30, 2003 - 6:25 pm UTC


I've never "worried about dual".

I've worried about people doing silly things to it, updating a dictionary table directly and breaking the system.

We can use := in the places they use select without a from!


their

select 5 into @x

is our

x := 5;

their select without a from is what we call "assignment"

You should not use dual as a method of assigning variable values.
You should use dual in queries (where they will force you to use FROM as well)

Ok

Mary, December 31, 2003 - 2:23 am UTC

Dear Tom and other reviewers,
I don't know why you people get Annoyed.If you can do,
Do it or find a way out.
Bye!


Tom Kyte
December 31, 2003 - 9:40 am UTC

huh???

who's annoyed? confused or curious (as in "why would you want to do this, what is the real world need -- maybe then I can give you a real world solution") maybe..

A reader, December 31, 2003 - 10:46 am UTC

Mary, we should be grateful to Tom for providing his expertise. He is doing a favor by providing solutions which you won't find on any websites.

Thanks.



Guess I need to work on being more clear...

Jim Nasby, December 31, 2003 - 1:58 pm UTC

:)

Yes, clearly in PL/SQL you'd use := and be done with it. Having support for SELECT without FROM is more handy for command-line stuff, and other things external to PL/SQL.

Tom Kyte
December 31, 2003 - 2:35 pm UTC

like what, give me a "for example" to work with -- where select without from would be useful?

(or do you mean like turning the database into a big calculator -- where you want to:

SQL> select 5+4

or something?) 

A reader, December 31, 2003 - 7:13 pm UTC

I just love your strong wit and timing. I think this is what sets you apart as a truly great author as much as your technical brilliance. Great work!!

Excellent Answers

Jean, January 15, 2004 - 10:33 am UTC

This kind of topics helps us to know a lot of the secrets behind Oracle. I've never imagine that DUAL, beeing such a little thing, can make so many people to talk about it.

Are autotrace stats on SYS.DUAL for real?

Doug, February 09, 2004 - 1:43 pm UTC

By default, BEA's WebLogic application server (6.1 sp1) uses the query

select count(*) from dual

to test a JDBC connection before making it available to users of its connection pool.  Unfortunately, it does this A LOT... like before and after every statement!  I'll leave the "wisdom" of this strategy as a discussion for another day.  In our OLTP application, the above query has accounted for at least 74 million buffer gets (33% of the total!) since the last instance restart a couple of weeks ago.

Execs       Buffer gets  % of total  Disk reads  Gets/exe  Disk/exe
74,792,695  373,813,512  33.35       21,757      5.00      0.00
select count(*) from dual

By making an index organized table as suggested in this thread like so:

SQL> create table dual_iot(dummy varchar2(1) primary key) organization index;
SQL> insert into dual_iot values('X');
SQL> analyze table dual_iot compute statistics;

I can bring the number of buffer gets per execution down from 3 to 1:

SQL> set autot on stat
SQL> select count(*) from dual;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        491  bytes sent via SQL*Net to client
        651  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(*) from dual_iot;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        491  bytes sent via SQL*Net to client
        651  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
PL/SQL Release 9.2.0.3.0 - Production
CORE    9.2.0.3.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production

My question... is the savings of two consistent gets here a "real" savings?  In other words, is there something magic going on with SYS.DUAL that makes the autotrace stats misleading?  Or did I just become a hero by saving the database a whole lot of unnecessary buffer gets (and latches, and cpu, and ...)?
 

Tom Kyte
February 09, 2004 - 2:25 pm UTC

I can get it to zero LIO's (10g does this sort of "magic" out of the box btw)

But yes, this can reduce your LIO's and more:

ops$tkyte@ORA920PC> /*
DOC>
DOC>create table dual_iot ( dummy primary key ) organization index
DOC>as
DOC>select * from dual;
DOC>analyze table dual_iot compute statistics
DOC>/
DOC>
DOC>@connect "/ as sysdba"
DOC>
DOC>create or replace view dual_view
DOC>as
DOC>select dummy from x$dual;
DOC>
DOC>grant select on dual_view to public
DOC>/
DOC>
DOC>create public synonym dual_view for dual_view
DOC>/
DOC>
DOC>@connect /
DOC>*/
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> exec runStats_pkg.rs_start
                                                                                                                
PL/SQL procedure successfully completed.
                                                                                                                
ops$tkyte@ORA920PC> declare
  2     l_cnt number;
  3  begin
  4     for i in 1 .. 50000
  5     loop
  6          select count(*) into l_cnt from dual;
  7     end loop;
  8  end;
  9  /
                                                                                                                
PL/SQL procedure successfully completed.
                                                                                                                
ops$tkyte@ORA920PC> exec runStats_pkg.rs_middle
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC> declare
  2     l_cnt number;
  3  begin
  4     for i in 1 .. 50000
  5     loop
  6          select count(*) into l_cnt from dual_iot;
  7     end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC> exec runStats_pkg.rs_stop(10000)
Run1 ran in 966 hsecs
Run2 ran in 721 hsecs
run 1 ran in 133.98% of the time
 
<b>faster by the wallclock and more importantly:</b>

Name                                  Run1        Run2        Diff
STAT...buffer is not pinned co      50,000           0     -50,000
STAT...index scans kdiixs1               0      50,000      50,000
STAT...no work - consistent re      50,000           0     -50,000
STAT...table scan blocks gotte      50,000           0     -50,000
STAT...table scans (short tabl      50,000           0     -50,000
STAT...table scan rows gotten       50,000           0     -50,000
STAT...shared hash latch upgra           0      50,000      50,000
STAT...session uga memory           65,464           0     -65,464
STAT...session uga memory max       65,464           0     -65,464
STAT...session pga memory           74,048           0     -74,048
STAT...consistent gets             150,002      50,002    -100,000
STAT...calls to get snapshot s     150,002      50,001    -100,001
STAT...session logical reads       150,540      50,536    -100,004
LATCH.cache buffers chains         302,633     102,637    -199,996
 
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
553,916     353,767    -200,149    156.58%
 
PL/SQL procedure successfully completed.

<b>latches are way down, now compare IOT to "view"</b>

ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> exec runStats_pkg.rs_start
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC> declare
  2     l_cnt number;
  3  begin
  4     for i in 1 .. 50000
  5     loop
  6          select count(*) into l_cnt from dual_iot;
  7     end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC> exec runStats_pkg.rs_middle
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC> declare
  2     l_cnt number;
  3  begin
  4     for i in 1 .. 50000
  5     loop
  6          select count(*) into l_cnt from dual_view;
  7     end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC> exec runStats_pkg.rs_stop(10000)
Run1 ran in 788 hsecs
Run2 ran in 493 hsecs
run 1 ran in 159.84% of the time
 
<b>wall clock faster still and...</b>

Name                                  Run1        Run2        Diff
STAT...session logical reads        50,523         531     -49,992
STAT...consistent gets              50,003           5     -49,998
STAT...index scans kdiixs1          50,000           0     -50,000
STAT...shared hash latch upgra      50,000           0     -50,000
LATCH.cache buffers chains         102,609       2,592    -100,017
 
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
353,890     253,740    -100,150    139.47%
 
PL/SQL procedure successfully completed.
 

<b>and even less latches...</b>


BUT -- changing DUAL like that globally = "potentially dangerous" and "definitely not something support would like to hear"


Best if you can set up a private synonym "dual" in the schema(s) you want to effect -- as few as possible.

If you use the IOT -- all queries involving DUAL will go CBO (if they are not already)

If you use the x$dual -- well, it's an X$ table and might have other unanticipate side effects I haven't thought about yet.

If you do it - make sure you can back it out. 

Nice

James Stephen, March 03, 2004 - 12:53 am UTC

Hi Tom,
I have a table as
sql>select * from t;
name
------
Joe
Jack
Can this table be joined with the DUAL table to produce
four rows?

Tom Kyte
March 03, 2004 - 9:43 am UTC

sort of, but we have to make dual return 2 rows first which we can do...

but -- what do you really need to do here? if you need two rows for each one, you must join to a table with 2 rows in it (cartesian product)

Unsatisfied

James Stephen, March 04, 2004 - 12:34 am UTC

Dear TOM,
Mr.Chuck Weiss,Creator of DUAL table said the following info.in one of the Oracle Mag.Issues"For every row in your
table ,DUAL Table is capable of producing a pair of rows from it using join".That's why I asked this question.Can it be done?


Tom Kyte
March 04, 2004 - 8:18 am UTC

He is wrong or you are mis-quoting him.

Dual is nothing special really, just a one row, one column table.

It cannot be done with a join -- maybe a UNION ALL, but not a join. (group by cube can be used as well but that still isn't a join).



well, hold on :)



in 9i I can do it as a join -- anyone want to guess HOW (clue: it is really a UNION ALL in disguise but it is a join, clue2: in "9i")

There you go -- make a game of it.


A join

vc, March 04, 2004 - 8:57 am UTC

You can

select * from dual a full join dual b on a.dummy='a'

-- result:

DUMMY DUMMY_1
X
X

VC

Tom Kyte
March 04, 2004 - 1:26 pm UTC

:)



here

Mac, March 04, 2004 - 9:05 am UTC

SQL> select t.*
  2  from t,(select*from dual union all select* from dual);

NAME
----------
Joe
Joe
Jack
Jack

4 rows selected. 

Tom Kyte
March 04, 2004 - 1:26 pm UTC

that uses a union -- not just a join. the full outer join is the right answer above...

Exellent

Shahinur Rahman, March 14, 2004 - 2:25 am UTC

Really very helpfull as usual.

Weekly challenge

Dave Thompson, March 26, 2004 - 9:44 am UTC

Hi Tom,

Whatever happened to the idea of a weekly challenge?

Setting everyone a tricky little SQL problem based on the new stuff like analytics would be a good way for us all to learn.

Regards,

Dave.

Tom Kyte
March 26, 2004 - 10:45 am UTC

too little time, too much to do :)

Fair Enough!

A reader, March 26, 2004 - 10:48 am UTC

No problem, I often wonder how you fit all this stuff into 24 hours!

Cheers.

Dave.

The name !

DaPi, March 26, 2004 - 11:23 am UTC

I can't resist: WHY was it called DUAL when the number of rows and columns are SINGULAR?

Tom Kyte
March 26, 2004 - 11:28 am UTC

predates me by many many years. no idea.

dual does have many definitions in math and science as well, might be geek humor for all i know.

RE: The Name?

Mark A. Williams, March 26, 2004 - 11:31 am UTC

Well, there are two of them... maybe that had an influence? Maybe Bruce Scott would know?

- Mark

The name

A reader, March 26, 2004 - 12:01 pm UTC

It seems chuck weiss is right :
</code> http://otn.oracle.com/oramag/oracle/02-jan/o12sendmail.html <code>

He says that he created a two rows table and called it dual because he wanted to duplicate easily one table.
Since this table has lost one row but kept its name.


Dual Table

Mark A. Williams, March 26, 2004 - 12:41 pm UTC

Well, there you go... Bob's your uncle and all that...

- Mark

select * from dual

Balasubramaniam.P, April 25, 2004 - 12:51 pm UTC

excellent


ORIGIN OF NAME DUAL

finnbar, April 30, 2004 - 7:50 am UTC

I was taught that DUAL is short for Dummy Alias, but I don't know how reliable that is.

fast dual

Reader, May 30, 2004 - 12:24 pm UTC

Can you tell us more about Fast dual in 10g .
Is it an IOT ?
How can it manage with 0 LIO's ?

Thanks

Tom Kyte
May 30, 2004 - 1:14 pm UTC

it is an optimization that turns an access against dual into a function call really.  It need not goto the database data *at all*.

If you don't select dummy that is.  If you select sysdate from dual -- fast dual is used.  If you select dummy, sysdate from dual -- dual the table is used.

ops$tkyte@ORA10G> select sysdate from dual;
 
SYSDATE
---------
30-MAY-04
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
   1    0   FAST DUAL (Cost=2 Card=1)
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
 
ops$tkyte@ORA10G> select dummy from dual;
 
D
-
X
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=2)
   1    0   TABLE ACCESS (FULL) OF 'DUAL' (TABLE) (Cost=2 Card=1 Bytes=2)
 
 
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
 
ops$tkyte@ORA10G> delete from dual;
 
1 row deleted.
 
 
Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
   1    0   DELETE OF 'DUAL'
   2    1     TABLE ACCESS (FULL) OF 'DUAL' (TABLE) (Cost=2 Card=1)
 
 
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
          3  consistent gets
          0  physical reads
 
ops$tkyte@ORA10G> select sysdate from dual;
 
SYSDATE
---------
30-MAY-04
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
   1    0   FAST DUAL (Cost=2 Card=1)
 
<b>see, got a row but</b>
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
 
ops$tkyte@ORA10G> select dummy from dual;
 
no rows selected

<b>no rows there</b> 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=2)
   1    0   TABLE ACCESS (FULL) OF 'DUAL' (TABLE) (Cost=2 Card=1 Bytes=2)
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
 
ops$tkyte@ORA10G> rollback ;
 
Rollback complete.
 
ops$tkyte@ORA10G> set autotrace off
ops$tkyte@ORA10G>


<b>NOTE: do not delete from dual on your real systems, it could be a disaster</b>
 

Fascinating...

Dan Kefford, May 31, 2004 - 12:27 pm UTC

It amazes me that after over 2 1/2 years, this thread is still going strong.

Tom... are you considering publishing a book just on the DUAL table?

Securing dual table

Hatem, June 16, 2004 - 12:49 pm UTC

Hi Tom
i know the importance of the dual table
could you give me some ideas on how to secure dual table?

Regards
Hatem

Tom Kyte
June 16, 2004 - 1:27 pm UTC

it is already secure.

assuming you have competent DBAS

and you have reasonable security policies (eg: you don't over grant privs to users)

Hatem, June 16, 2004 - 1:33 pm UTC

Hi Tom
are there a way to prvent dml,ddl on the dual table without affecting the application?
if update dual set dummy=1 where dummy=x will cause the database to hang,also if modifying it to number instead of varchar2
how to prevent so?
please advice

Tom Kyte
June 16, 2004 - 3:34 pm UTC

yes, just don't grant permission to do so!

by default, no one can (it is a sys owned object).

it takes someone with lots of privileges to modify it.

if your application can modify it -- take that priv away, you don't need it, you don't want it.

(and that update will *not* cause the database to hang)
(and you won't be able to modify it since the value "X" won't convert)
(and converting it would not cause any issues actually)




Locking down dual.

Bill, June 16, 2004 - 2:13 pm UTC

How about having a DML trigger on ALTER, DROP that would raise and error if the table referenced is SYS.DUAL and a before insert, update, and delete trigger on the DUAL table it's self that would raise a failure if triggered.

Tom Kyte
June 16, 2004 - 3:40 pm UTC

just don't grant in the first place.

just don't create things as sys.

just leave dual alone.

I agree totally

Bill, June 16, 2004 - 4:40 pm UTC

The user asked if the dual table could be locked down and I told him, however I agree totally with you. The ONLY one with rights to touch the SYS schema should be the DBA staff. No one else, without exception should be allowed to touch it. I have followed this advise for 15 years as a DBA and I have never had a problem with the data directory being trashed. Also never give out SYSDBA or the SYS or SYSTEM password. Also never give out the password to the system DBA account. (Normally Oracle). I have seen users do many silly things over the years and you have to make a database not only fool proof, but damn fool proof.

Tom Kyte
June 16, 2004 - 4:42 pm UTC

tell the user "the way to lock down a table is via privileges and we have done so. the table is locked down"

Dual Table

Hatem, June 17, 2004 - 3:05 am UTC

Hi Tom
my problem that i'm using oracle application 11.5.7
my problem that the user apps can update,insert,...etc with the sys.dual
what kind of privileges to revoke from apps user?

Please Advice


Tom Kyte
June 17, 2004 - 9:56 am UTC

the apps user wont do that.


y it is giving extra fetch????

pooja, June 17, 2004 - 9:03 am UTC

hai tom, its all excellent abt DUAL table.but little bit confusion is..
when i inserted 2 rows into the table and again selecting from dual, it is giving 1 row.but, when i do a pl/sql block like..
select count(*) from dual;
2
select * from dual
'X'
now i write the block as..
declare
var1 date;
begin
select sysdate into var1 from dual;
dbms_output.put_line(var1);
end;
it is giving error as extra fetch returns more than requested number of rows.y like that re, it is very confusing.
plz tell me the reason

Tom Kyte
June 17, 2004 - 10:21 am UTC

DON"T DO THAT.

just leave dual alone. dual is special, dual is magic, just select from it, don't touch it otherwise.

read the original answer, I showed that exactly myself.

Please help on this

reader, June 17, 2004 - 9:47 am UTC

Hi Tom,
In one of the posting you had mentioned that in pl/sql it's lot better to have
varname := sysdate;
rather than select sysdate into varname from dual; as it's better in performance and second approach is more of sql not pl/sql, while there's no disagreement to what you have said, I have a request and a question here

request:
could you please show by some e.g. how first approach is faster.

question:
while discussing the first approach within my team one of the seniors said query to dual anytime actually inserts a row and then removes it from it after data is given to you. I haven't read this before any where and I am not sure of it so will you please give your opinion on this and e.g to prove it right/wrong

Thanks a ton for all your help.
your fan

Tom Kyte
June 17, 2004 - 12:29 pm UTC

I'm hoping that after reading this site for a while, people will be able to "show how approach A is better than approach B" (spent quite a while on that topic, how to do that, in my book effective oracle by design as well).  It is the most important thing you can learn.

ops$tkyte@ORA9IR2> create or replace procedure p1
  2  as
  3          l_date date := sysdate;
  4          l_x    number;
  5  begin
  6          if ( l_date is not null )
  7          then
  8                  l_x := 1;
  9          end if;
 10  end;
 11  /
 
Procedure created.
 
ops$tkyte@ORA9IR2> create or replace procedure p2
  2  as
  3          l_date date;
  4          l_x    number;
  5  begin
  6          select sysdate into l_date from dual;
  7          if ( l_date is not null )
  8          then
  9                  l_x := 1;
 10          end if;
 11  end;
 12  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec runStats_pkg.rs_start;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec for i in 1 .. 10000 loop p1; end loop;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec runStats_pkg.rs_middle;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec for i in 1 .. 10000 loop p2; end loop;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec runStats_pkg.rs_stop;
Run1 ran in 3 hsecs
Run2 ran in 52 hsecs
run 1 ran in 5.77% of the time
 
Name                                  Run1        Run2        Diff
LATCH.cache buffers lru chain            4           5           1
STAT...active txn count during           3           4           1
STAT...calls to kcmgcs                   3           4           1
STAT...cleanout - number of kt           3           4           1
STAT...consistent gets - exami           3           4           1
STAT...opened cursors current            0           1           1
STAT...parse count (total)               4           5           1
STAT...parse count (hard)                1           2           1
STAT...opened cursors cumulati           4           5           1
LATCH.messages                           0           2           2
STAT...bytes received via SQL*         905         903          -2
STAT...OS System time used               0           2           2
STAT...db block gets                   520         522           2
STAT...db block changes              1,020       1,022           2
STAT...consistent changes              510         512           2
LATCH.child cursor hash table            8          14           6
LATCH.library cache pin alloca          29          35           6
LATCH.row cache enqueue latch            0           8           8
LATCH.row cache objects                  0           8           8
STAT...recursive cpu usage               2          38          36
STAT...CPU used by this sessio           7          44          37
STAT...CPU used when call star           7          44          37
STAT...OS User time used                 0          49          49
STAT...Elapsed Time                      5          55          50
STAT...OS Page faults                    0         227         227
STAT...buffer is not pinned co           0      10,000      10,000
STAT...table scans (short tabl           0      10,000      10,000
STAT...table scan rows gotten            0      10,000      10,000
STAT...execute count                     5      10,005      10,000
STAT...table scan blocks gotte           0      10,000      10,000
STAT...no work - consistent re           0      10,000      10,000
STAT...recursive calls                   1      10,002      10,001
LATCH.shared pool                      114      10,136      10,022
LATCH.library cache pin                 63      20,068      20,005
LATCH.library cache                    119      20,139      20,020
STAT...calls to get snapshot s           1      30,001      30,000
STAT...consistent gets                   3      30,004      30,001
STAT...session logical reads           523      30,526      30,003
LATCH.cache buffers chains           2,591      62,582      59,991
 
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
3,451     113,520     110,069      3.04%
 
PL/SQL procedure successfully completed.


<b>that "senior" person is "making stuff up", if you heard them correctly.  It would be "not smart" if that was the way things actually worked.  Dual is just a one row, one column physical table.</b>


Also, note that "things change", consider this from 10g:

ops$tkyte@ORA10G> exec runStats_pkg.rs_start;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec for i in 1 .. 10000 loop p1; end loop;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec runStats_pkg.rs_middle;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec for i in 1 .. 10000 loop p2; end loop;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec runStats_pkg.rs_stop;
Run1 ran in 6 hsecs
Run2 ran in 41 hsecs
run 1 ran in 14.63% of the time
 
Name                                  Run1        Run2        Diff
STAT...opened cursors current            1           2           1
STAT...active txn count during           2           1          -1
STAT...cleanout - number of kt           2           1          -1
STAT...parse time elapsed                1           0          -1
STAT...parse time cpu                    1           0          -1
LATCH.library cache pin alloca           7           8           1
LATCH.library cache lock alloc           4           5           1
LATCH.compile environment latc           2           1          -1
STAT...bytes received via SQL*         921         919          -2
LATCH.undo global data                   1           3           2
LATCH.library cache load lock            2           0          -2
LATCH.row cache objects                176         174          -2
LATCH.session idle bit                  15          13          -2
STAT...physical reads                    3           0          -3
LATCH.session allocation                75          72          -3
STAT...parse count (total)              14          11          -3
STAT...physical reads cache              3           0          -3
STAT...physical read IO reques           3           0          -3
STAT...free buffer requested            14          11          -3
STAT...enqueue releases                  6           2          -4
STAT...shared hash latch upgra           4           0          -4
STAT...hot buffers moved to he           9           5          -4
LATCH.enqueue hash chains                8           4          -4
LATCH.messages                           3           7           4
STAT...sorts (memory)                    5           1          -4
STAT...index scans kdiixs1               4           0          -4
STAT...enqueue requests                  7           2          -5
STAT...consistent gets - exami           6           1          -5
LATCH.cache buffers lru chain           16          11          -5
LATCH.enqueues                          11           5          -6
STAT...consistent changes              709         701          -8
STAT...consistent gets                  21          12          -9
STAT...consistent gets from ca          21          12          -9
STAT...db block changes              1,397       1,388          -9
LATCH.object queue header oper          32          22         -10
STAT...db block gets                   723         709         -14
STAT...db block gets from cach         723         709         -14
LATCH.library cache lock               135         120         -15
STAT...session logical reads           744         721         -23
STAT...recursive cpu usage               5          33          28
STAT...DB time                          18          48          30
STAT...Elapsed Time                     18          48          30
STAT...CPU used when call star           8          40          32
STAT...CPU used by this sessio           8          40          32
STAT...undo change vector size      46,316      46,276         -40
LATCH.cache buffers chains           3,577       3,531         -46
LATCH.simulator hash latch               1         131         130
STAT...redo size                    95,856      95,712        -144
STAT...recursive calls                  76      10,039       9,963
LATCH.shared pool                      200      10,173       9,973
STAT...calls to get snapshot s          11      10,006       9,995
STAT...execute count                    14      10,010       9,996
LATCH.library cache                    364      20,320      19,956
LATCH.library cache pin                159      20,133      19,974
STAT...session uga memory           65,464           0     -65,464
 
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
4,808      54,753      49,945      8.78%
 
PL/SQL procedure successfully completed.
 

#so, while still much faster wall clock wise to use assignment, going against DUAL is not the scalability factor it is in 9i and before -- there is a new "fast dual" in that release.  In 9i, the latching was much higher</b>
 

re: The Name

Connor, June 17, 2004 - 11:06 am UTC

I find it very odd that DUAL is supposedly named because it had "two rows" - any "oracle old timer" I've spoken to can not remember DUAL with anything except a single row.

If memory serves, and no, I've no intention of revealing how many years have passed since I did a maths degree :-), the dual of the rows of a matrix was the columns of a matrix, and thus the special case of where the dual of matrix was equal to the matrix itself was a 1x1 matrix...hence DUAL with its single row and column

Cheers
Connor

Name of DUAL

DaPi, June 17, 2004 - 12:25 pm UTC

Conner, that sounds suspicious to me. The dual (or transpose) of any diagonal matrix is equal to the original. Of course, it's only in the 1x1 case that the order of storage of the rows is irrelevant :-)

Dave, June 17, 2004 - 1:33 pm UTC

Ok, you guys are far too math-geeky. Stop it immediately.

A reader, June 21, 2004 - 5:44 pm UTC

Magic command.

select * from dual gets this result?

SVRMGR> select * from dual;
ADDR INDX INST_ID D
-------- ---------- ---------- -
01680288 0 1 X
1 row selected.


Tom Kyte
June 21, 2004 - 8:55 pm UTC

so -- whats your point? you didn't answer the question did you?

OH WAIT, someone already did, years ago ;)

all about dual table

chandra, July 18, 2004 - 3:06 pm UTC

Tom,

I have created a sequence and am generating sequence numbers using dual table

I am using this sequence to generate ids in an online application.

Here is the sequence:

scott@ORA9I$ create sequence seq1
2 start with 1
3 increment by 1
4 /

Sequence created.

I have created a function that would return a sequence number.

scott@ORA9I$ create or replace function fun_seq1
2 return number
3 is
4 l_seq1 number;
5 begin
6 execute immediate
7 'select seq1.nextval from dual'
8 into l_seq1;
9 return l_seq1;
10 end;
11 /

Function created.

scott@ORA9I$ alter session set timed_statistics=true;

Session altered.

scott@ORA9I$ alter session set sql_trace=true;

Session altered.

scott@ORA9I$ select seq1.nextval from dual;

NEXTVAL
----------
1

scott@ORA9I$ select fun_seq1 from dual;

FUN_SEQ1
----------
2

Now is it efficient to use sequence directly in the dual table or is it efficient to call the function that returns the sequence.

I have used tkprof and checked the values but am unable to
come to a conclusion on this matter.

here is the tkprof report

select seq1.nextval from dual <-- I have run this query 5 times

call count cpu elapsed disk query current rows
------- ----- ----- ------- ----- ----- ------- -----
Parse 10 0.00 0.05 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 15 0.01 0.01 0 10 21 10
------- ----- ----- ------- ------- ----- ------ ------
total 35 0.01 0.06 0 10 21 10

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 66

Rows Row Source Operation
----- ---------------------------------------------------
1 SEQUENCE
1 TABLE ACCESS FULL DUAL

Now I am call the function below
select fun_seq1 from dual <-- I have run this query 5 times

call count cpu elapsed disk query current rows
------- ----- ----- ------- ----- ----- ------- ----
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 10 0.07 0.11 0 5 10 5
------- ----- ----- ------- ----- ----- ------- -----
total 20 0.07 0.11 0 5 10 5

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 66

Rows Row Source Operation
----- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL


I need your advice on this. which is the better approach?.


Tom Kyte
July 18, 2004 - 3:48 pm UTC

I would definitely

a) NOT use dynamic sql when static sql could be used. why execute immediate????

b) NOT use a function to query dual to return a sequence

c) avoid using DUAL all together by putting s.nextval right into my insert:

insert into t ( pk, .... ) values ( s.nextval, .... );

and if I needed the value, use the returning clause:

insert into t ( pk, .... ) values (s.nextval, .... ) returning pk into l_pk;




all about dual table

chandra, July 18, 2004 - 4:12 pm UTC

Thanks tom

your advice was very helpful

thanks once again

All About DUAL table

Chandra, July 19, 2004 - 1:31 pm UTC

Hi tom,

Thanks for the help
You have mentioned to avoid the dual table
Can you please explain what happens if we use the dual table .Can you explain using tkprof or autotrace.

thanks in advance

Chandra


Tom Kyte
July 19, 2004 - 2:01 pm UTC

why goto the server to pull back a number that you are just going to turn around and send back to the server????????


just goto the server and put the number in the database and if you need the number in the client application, use the returning clause to get it.


think about it -- why do the extra, unnecessary work??

don't understand ORA-00918

Matthias Rogel, July 21, 2004 - 2:15 am UTC

Hallo Tom,

please look at this:

sqlplus> column dummy format a20

sqlplus> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for Linux: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

sqlplus> select * from dual, (select * from dual);

DUMMY DUMMY
-------------------- --------------------
X X

sqlplus> select * from dual, dual;
select * from dual, dual
*
FEHLER in Zeile 1:
ORA-00918: column ambiguously defined



I don't understand this ORA-00918, why do I get it ?
Why don't I get it in the first query ?

Thank you

Tom Kyte
July 21, 2004 - 7:48 am UTC

I'll hypothesize.....


select * from dual, dual; 

would be the same as:

select dual.dummy, dual.dummy from dual, dual;

where as:

select * from dual, (select * from dual)

is the same as:

select dual.dummy, <anonymous>.dummy from dual, (select * from dual);

where anonymous is like "null" - no anonymous equates to anyother anonymous.  by inlining the view -- you "renamed" dual much like:

ops$tkyte@ORA9IR2> select * from dual, dual d2;
 
D D
- -
X X


does.

 

please forget about it

Matthias Rogel, July 21, 2004 - 2:27 am UTC

didn't have my coffee yet

just had my coffee

Matthias Rogel, July 21, 2004 - 10:41 am UTC

and agree with yout hypothesis

sql and pl/sql

MadhavSingh Kshatriy, August 06, 2004 - 4:11 am UTC

sql and pl/sql handles dual table differently??

If i have 2 rows in dual table sql returns only 1 thru select query.

pl/sql returns multiple rows???

why so??

Tom Kyte
August 06, 2004 - 8:12 am UTC

please re-read the above again. "dual is dual" "dual is magic" "dual had BETTER HAVE A SINGLE ROW or wierd things will happen"



Cardinality of dual

A reader, August 16, 2004 - 1:21 pm UTC

For 'select * from dual', the plan is

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 164 | 2 |
| 1 | TABLE ACCESS FULL | DUAL | 82 | 164 | 2 |
--------------------------------------------------------------------

Note: cpu costing is off

1. Why is the cardinality so off? (should be 1 instead of 82)

2. If dual is a special, magic table, shouldnt the CBO know that it always has 1 row?

3. If CBO overestimates the cardinality of DUAL like this, what harm would it cause my overall system performance?

4. I guess if I gather system stats, it will get it right, is this recommended? (I am on 9iR2)

Thanks

Tom Kyte
August 16, 2004 - 7:47 pm UTC

82?  that is pretty good.  you are just showing what happens when you force the cbo onto unanalyzed objects.  while I cannot explain "82" -- I can show you what happens:

ops$tkyte@ORA9IR2> delete from plan_table;
 
9 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for select * from dual;
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  TABLE ACCESS FULL   | DUAL        |       |       |       |
--------------------------------------------------------------------
 
Note: rule based optimization
 
9 rows selected.

<b>by default, the rbo would be used</b>

ops$tkyte@ORA9IR2> alter session set optimizer_goal=first_rows;
 
Session altered.
 
ops$tkyte@ORA9IR2> delete from plan_table;
 
2 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for select * from dual;
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  8168 | 16336 |    11 |
|   1 |  TABLE ACCESS FULL   | DUAL        |  8168 | 16336 |    11 |
--------------------------------------------------------------------
 
Note: cpu costing is off
 
9 rows selected.

<b>when forced into cbo mode, it should default based on the block size. 8k = 8168 rows for dual</b>
 
ops$tkyte@ORA9IR2> alter session set optimizer_dynamic_sampling=10;
 
Session altered.
 
ops$tkyte@ORA9IR2> delete from plan_table;
 
2 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for select * from dual;
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     2 |    11 |
|   1 |  TABLE ACCESS FULL   | DUAL        |     1 |     2 |    11 |
--------------------------------------------------------------------
 
Note: cpu costing is off
 
9 rows selected.

<b>using dynamic sampling (new in 9i, on more aggresively by default in 10g), you can "fix things" on the fly.  </b>

I am NOT suggesting 10 -- 3 is probably more like it.


in 9i and before, dual is just a table -- really just a table.  In fact you can create your own table dual.  not recommended of course, but hey. 

Cardinality of dual

A reader, August 16, 2004 - 8:44 pm UTC

3. If CBO overestimates the cardinality of DUAL like this, what harm would it cause my overall system performance?

4. I guess if I gather system stats, it will get it right, is this recommended? (I am on 9iR2)

In general, is there any benefit or harm to gathering stats on the SYS schema in 9iR2?

Thanks a lot

Tom Kyte
August 17, 2004 - 7:14 am UTC

3) wrong possible plan

4) no, system stats are a measure of your disk speeds and cpu speeds. gathering stats on the dictionary or optimizer dynamic sampleing (in 10g, stats on the dictionary are done for you)


in 8i, i wouldn't do stats on sys but it is supported if you have tested it and found benefit

in 9i, do it in test and fully test it first

in 10g, it is standard.

A reader, August 17, 2004 - 7:38 am UTC

3) wrong possible plan

But isnt this a potential big issue? DUAL is used all over the place even without users/developers knowing it.

So, would it make sense to gather stats only on SYS.DUAL?

Yes, I meant 'stats on SYS schema', not 'system stats', sorry for the confusion.

"in 9i, do it in test and fully test it first"

Yes, I realize that but before doing anything you need to know the expected benefits. Thats what I am trying to get from you.

In your experience and knowledge, do you know that gathering stats on SYS has such-and-such benefits? (Of course, YMMV thats why we test). But what can I hope to get?

Thanks

Tom Kyte
August 17, 2004 - 8:17 am UTC

3) did i say otherwise?

dual is not used with other tables without users/developers knowing about it. a simple select sysdate from dual (what you alude to - dual being used without your knowledge) is going to be a full scan no matter what so that is not a big deal.

In your queries - dual would never be used unless you yourself used dual.


I find dual to be no used in "real queries" and when it is, it is in a construct like:

select * from t where x = ( select foo() from dual );

meaning, the cardinality of dual is not excessively relevant.


I would not get too uptight about this really. I mean really -- how often do you use dual in a complex multi-table query?

Anil Pant, December 31, 2004 - 8:56 am UTC

Hi,
From Java 1.3.1 using JDBC we are connecting to Oracle 9i Release 2.

public String CreateHeader(String FileType) throws SQLException, FileNotFoundException, IOException{
String qry = null;
qry = "SELECT 5";
qry += " , RPAD('" + FileType + "', 15, ' ')";
qry += " , LPAD('" + Config.getProperty("File_sequence_numb") + "', 10, '0')";
qry += " , TO_CHAR(SYSDATE, 'YYYYMMDD')";
qry += " , TO_CHAR(SYSDATE, 'HH24:MI:SS')";
qry += " , RPAD('CVACS', 5, ' ')";
qry += " , 'P'";
qry += " FROM dual";
return qry;
}

On execution its not recognising the DAUL. When I replace it with some other table its working. Im not familar with Java. Im trying to run an java application.

Also when we tried on Oracle 8i it would.

Does anything special to be done for a user to access DUAL from Java ? Or is there any change in Oracle 9i from Oracle 8i with respect to DUAL ?


Tom Kyte
December 31, 2004 - 11:16 am UTC

Nope, dual is there, you can log into sqlplus and see for yourself.

turn on sql_trace=true and trace it, in the trace file, you'll see the actual sql.

not a bind variable in site, whatever brilliant java coder wrote this knew nothing about databases. geez.


even more geez -- why is this even a QUERY!!!!!!!!!!!!!!!! why not just variable assignments in a java program, there is no way this should goto the database (unless they just really truly wanted to squash your shared pool with billions of literal SQL statements like:

select 5,
rpad( '<UNIQUE_STRING_HERE>', 15, ' ' ),
lpad( '<AND_HERE>', 10, '0' ),
to_char(sysdate, 'yyyymmdd' ),
to_char(sysdate, 'hh24:mi:ss' ),
rpad( 'CVACS', 5, ' ' ), <<<<== that one is really funny :)
'P'
from dual;


that is just too cool, not a bind in sight, and nothing you couldn't do straight up in the java code itself without even hitting the database!



Now I understand what is Dual table!!

soham, February 01, 2005 - 6:11 am UTC

HI!
This is a very good explanation of Dual table.
thanks

tom in my db

A reader, March 16, 2005 - 7:43 am UTC

Hi Tom

I my database i have seen that execution are very high for the query
select sysdate from sys.dual;

i am facing performance issues .so how can we tune this query if possible.
or there is any other way around.

thanks

Tom Kyte
March 16, 2005 - 8:33 am UTC

10g will make that infinitely more efficient as it will not hit dual at all.

I'd be asking "why is it being executed so much"? sysdate can (and should) be included in the insert/update/select statement - why does the client need to get it specifically over and over and over?


the best way to make something really fast? don't do it at all.

apache dbcp

A reader, March 16, 2005 - 1:29 pm UTC

I have a web based application that uses apache dbcp and has a validation query. At the moment it uses "select 1 from dual" to verify the connection is available. This happens 1 a minute. Is this query the least performance impacting query I can run on the db? Is there any other query that will be faster and less resource intensive?

Tom Kyte
March 17, 2005 - 7:32 am UTC

how about just execute

"begin null; end;"

instead? don't run a query at all.



select sysdate from dual

A reader, March 17, 2005 - 5:39 am UTC

Tom

can we pin it in keep pool.if yes please give me the exact syntax.

as i know procedures can be pinned. But how to pin sql ?.
i refered documents but couldn't find.

Thanks

Tom Kyte
March 17, 2005 - 8:49 am UTC

if you are doing it billions of times, it is already in the shared pool. pinning won't do a thing.



10g will not hit dual

A reader, March 17, 2005 - 5:42 am UTC

then will be approach in 10g to get sysdate.
can there be any other in 9i also.

Tom Kyte
March 17, 2005 - 8:50 am UTC

in 9i and 10g -- the best way to speed this up....

*don't do it*

why are you doing it lots? what is the point?

Validation query

Greg, March 17, 2005 - 10:08 am UTC

I'm not familiar with dbcp and validation queries, but are you trying to check whether a connection is available, or that the  database is actually up?  Because a 'select 1 from dual' works when the database isn't open.
e.g.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
SQL> select 1 from dual;

         1
----------
         1

SQL> connect gw/gw
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress

We found that problem when one of our Unix systems rebooted over night, and the database needed recovery, but the monitor script was just doing a 'select sysdate from dual;', so it thought that the db was still up.  We changed the script to 'select open_mode from v$database;' and checked for 'READ WRITE'.  That worked much better - but perhaps there's a better way?  
 

10g will not hit dual

A reader, March 22, 2005 - 1:59 am UTC

The select sysdate from dual is being sed for getting time when a window ( form ) is being opened by user on the net.


Tom Kyte
March 22, 2005 - 11:08 am UTC

ok, if you cannot avoid it, guess you cannot avoid it, one certainly hopes your code isn't:


select sysdate into x from dual;
insert into t values ( .... x );

though.

no

A reader, March 23, 2005 - 12:05 am UTC

it is like 'select sysdate from sys.dual' from another user.
any workaround as you mentioned somewhere creating my_dual in that users schema.

Tom Kyte
March 23, 2005 - 1:16 am UTC

NOT if you say sys.dual

doesn't matter HOW many duals you create

HISTORY OF DUAL

Bhavesh Patel, March 24, 2005 - 7:59 am UTC

hi tom,
After reading about dual i came to know that So many peoples are not aware about history of dual:
so here it is:::(BY Chuck Weiss,Oracle)

I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, seemed apt for the process of creating a pair of rows from just one.
Chuck Weiss, Oracle.



how to add to date and time

baskar, April 04, 2005 - 3:37 am UTC

i want add two date and time pls help me


Tom Kyte
April 04, 2005 - 7:11 am UTC

select sysdate + 1 from dual; -- add a day
select sysdate + 1/24 an hour
+1/24/60 a minute
+1/24/60/60 a second

or read about intervals in 9i and above if you like.

Alternative to DUAL Table

Richard, April 06, 2005 - 11:24 am UTC

There is a school of thought which says that by creating one's own DUAL table (called, for example, XDUAL as a one column, one row IOT, which is then analyzed), one can reduce execution time (in certain scenarios) of PL/SQL.

I believe the underlying theory is that by using such a table, fewer consistent gets are performed than when using SYS.DUAL. I imagine this may apply only to pre-10g Oracle.

Is this *alternative* DUAL table idea a good one, a bad one, or irrelevant?

Tom Kyte
April 06, 2005 - 1:40 pm UTC

yes, you can get the consistent gets to go from 3 or 4 to 1

if you hit dual billions of times, there could be reasons to go there.

but if you hit dual billions of times and will be changing the queries, maybe we can stop hitting dual billions of times.

eg

instead of:

select s.nextval into :x from dual;
insert into t values ( ...... , :x );

just code

insert into t values ( ....., s.nextval ) <returning id into :x if you need it>


Instead of:

.... user ...
.. user ...
..... user .....

referencing user all over the place, just set a global ONCE to user...

and so on.

Yet Another DUAL Thing

Richard, April 07, 2005 - 12:09 pm UTC

Thank you for your help with my previous question.

It occurred to me that because DUAL is, typically, used a lot by both the Database and its Users, would it be sane to *pin* DUAL in memory?

Incidentally, I have now run-out of DUAL-related questions!

Tom Kyte
April 07, 2005 - 12:25 pm UTC

caches are made to "if you use it, it should be there"

dual is a small table, it should be "cached" already (short tables don't age out fast like long tables during a full scan)

and dual cannot be moved into a keep or recycle pool

so, I'd just leave it be

Interesting!

Richard, April 07, 2005 - 3:07 pm UTC


another use of the dual table

A reader, April 28, 2005 - 7:23 am UTC

create table temp(i integer);
insert into temp values(1);

select * from temp where i(+)=2;
Es wurden keine Zeilen ausgewõhlt

but
select temp.* from temp,dual where i(+)=decode(dummy,dummy,2);

I
----------


1 Zeile wurde ausgewõhlt.

Why doesn't the first query return a row and the second one does ?

Tom Kyte
April 28, 2005 - 8:33 am UTC

because there is no row source generate ANY rows in the first query.

you have zero rows outer joined to "nothing" in the first query.

you have zero rows outer joined to the "single row in dual" in the second query.

If dual had two rows that query would return 2 records.

Since you outer join to "nothing" you get "nothing"

many thanks

your fan, April 29, 2005 - 4:33 am UTC

so many thanks Tom, i just wonder how do you spend your time answering all questions. This dual table forum really give me many knowledges.

thanks and thanks again...

Dual Table

Kamal, May 10, 2005 - 8:00 am UTC

Hi Tom

SQL > startup mount
ORACLE instance started.

Total System Global Area  188743680 bytes
Fixed Size                   778036 bytes
Variable Size             162537676 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.

SQL> select * from  dual;

ADDR           INDX    INST_ID D
-------- ---------- ---------- -
0B6837A8          0          1 X

SQL> alter database open;

Database altered.

SQL> select * from dual;

D
-
X

How is Oracle Instance able to access the DUAL Table when the database is not opened....does it mean that the DUAL what is getting displayed is coming from memory...

there is dictionary base table X$DUAL...is it the one which is used during that query..

thanks
kamal
 

Tom Kyte
May 10, 2005 - 9:17 am UTC

write it off as "magic"

rman needed dual, rman needed dual when the database was not open for queries, rman was provided with a magical dual.



No rows returned when selecting from dual.

ht, June 30, 2005 - 6:40 pm UTC

Hi Tom,
I'm running a query against dual in 9.2.0.5.

select * from dual, select sysdate from dual, select user from dual returns no recs in 1 particular schema that has resource, create session privs. This schema can select from user_users successfully.

Other users don't have this issue in the same db.

Any ideas on what could be going on?

Thanks,
ht

Tom Kyte
June 30, 2005 - 8:37 pm UTC

they created a table named dual and it has zero rows in it.

Right on target.

ht, June 30, 2005 - 9:06 pm UTC

Thanks for the help again. The vendor creates a table named "dual" in their install and it breaks all of their triggers that need to select from dual.

Tom Kyte
June 30, 2005 - 9:17 pm UTC

not too smart of vendor? they did not test this?

the table was created but no data inserted

ht, July 01, 2005 - 12:54 am UTC

Believe it or not, it's an upgrade script for their app. They go through the trouble of creating the table, don't insert any data into it, and their triggers break since they "select user from dual" and "select sysdate from dual".

First time I've seen this "logic".

just a thought

A reader, July 01, 2005 - 6:10 am UTC

I just wondering if Dual's magic is something like OO's polymorphism, just more flexible. I just don t understand how Oracle could implement that using a table.

about oracle 10G

A reader, July 05, 2005 - 4:27 am UTC

Hi tom,
In oracle 10G, if we insert one row in dual (say 'Y') and if i either select count(*) or select * from dual, i get only one row. I read this thread earlier that this kind of implementation may change with different version of oracle. can you pls explain why i dont see 2 records, even in PL/SQL Block.



Tom Kyte
July 05, 2005 - 7:35 am UTC

please just read the original answer ....

and then don't do that

slow select nexval from dual

DanielD, July 12, 2005 - 2:18 pm UTC

Tom,

In your answer to chandra from July 18, 2004, you had given an advice not to use select from dual for a sequence (nextval). You are recommending "insert into t ( pk, .... ) values ( s.nextval, .... );"

I have been asked to find out why

select DOC.INVOICESeq.nextVal from dual

takes between 00:00:00.78 and 00:00:04.18. I was thinking about creating IOT "my_dual" and use it with nextval selection. What should I check to find out why selecting a sequence number takes > 4 sec. sometimes? What approach is better - creating "my_dual" or "insert into t ( pk, .... ) values ( s.nextval, .... );"

Thank you very much for your time.

DanielD

Tom Kyte
July 13, 2005 - 10:41 am UTC

trace it with waits on and see what you are waiting on.

the only way to fix something is understand what is broken about it.

could be network, could be cache buffers chains, could be machine is overloaded cpu wise, could be the soft parse, could be a bad log file sync on recursive sql

only one of which might be affected by "my_dual"
and there are others that it could be

but one wonders why you have the select from dual :)

Why from dual

DanielD, July 13, 2005 - 12:18 pm UTC

Tom,

I've asked "why from dual" and I've got an answer - legacy. Now it would be costly to change all the code. Further more - I've been asked contra question - wouldn't we see the same issue when using "insert into t ( pk, .... ) values ( s.nextval, .... );"?

Tracing it with waits on -

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

rigth? :)

Thank you for your time.

Tom Kyte
July 13, 2005 - 1:08 pm UTC

you might well be seeing it with s.nextval, if it is related to the sequence.

but you are thinking of creating "my_dual", how will you use "my_dual" without modifying the legacy code.


yes, that is the way to trace it.

SQL trace with WAITS

DanielD, July 13, 2005 - 1:49 pm UTC

Tom,

I ran the select nextval from dual with this trace level:

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

And then with this one:

alter session set events '10046 trace name context forever, level 8';

And while running in lever 8, I had to wait once for nextval for 12 sec and once almost for 3 sec:

SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

Elapsed: 00:00:00.02
SQL> select DOC.INVOICESeq.nextVal from dual;

   NEXTVAL
----------
    310413

Elapsed: 00:00:00.08
SQL> select DOC.INVOICESeq.nextVal from dual;

   NEXTVAL
----------
    310414

Elapsed: 00:00:00.01
SQL> select DOC.INVOICESeq.nextVal from dual;

   NEXTVAL
----------
    310415

Elapsed: 00:00:00.00
SQL> select DOC.INVOICESeq.nextVal from dual;

   NEXTVAL
----------
    310416

Elapsed: 00:00:00.01
SQL> select DOC.INVOICESeq.nextVal from dual;

   NEXTVAL
----------
    310417

Elapsed: 00:00:00.01
SQL> select DOC.INVOICESeq.nextVal from dual;

   NEXTVAL
----------
    310418

Elapsed: 00:00:00.04
SQL> select DOC.INVOICESeq.nextVal from dual;

   NEXTVAL
----------
    310419

Elapsed: 00:00:00.01
SQL> select DOC.INVOICESeq.nextVal from dual;

   NEXTVAL
----------
    310420

Elapsed: 00:00:00.00
SQL> select DOC.INVOICESeq.nextVal from dual;

   NEXTVAL
----------
    310421

<b>Elapsed: 00:00:12.44</b>
SQL> select DOC.INVOICESeq.nextVal from dual;

   NEXTVAL
----------
    310422

Elapsed: 00:00:00.11
SQL> select DOC.INVOICESeq.nextVal from dual;

   NEXTVAL
----------
    310423

Elapsed: 00:00:00.00
SQL> select DOC.INVOICESeq.nextVal from dual;

   NEXTVAL
----------
    310424

Elapsed: 00:00:00.01
SQL> select DOC.INVOICESeq.nextVal from dual;

   NEXTVAL
----------
    310425

Elapsed: 00:00:00.00
SQL> select DOC.INVOICESeq.nextVal from dual;

   NEXTVAL
----------
    310426

Elapsed: 00:00:00.01
SQL> select DOC.INVOICESeq.nextVal from dual;

   NEXTVAL
----------
    310427

Elapsed: 00:00:00.01
SQL> select DOC.INVOICESeq.nextVal from dual;

   NEXTVAL
----------
    310428

<b>Elapsed: 00:00:02.93</b>
SQL> select DOC.INVOICESeq.nextVal from dual;

   NEXTVAL
----------
    310429

Elapsed: 00:00:00.00
SQL> select DOC.INVOICESeq.nextVal from dual;

   NEXTVAL
----------
    310430

Elapsed: 00:00:00.00
SQL> select DOC.INVOICESeq.nextVal from dual;

   NEXTVAL
----------
    310431

Elapsed: 00:00:00.00
SQL> ALTER SESSION SET SQL_TRACE=FALSE;

Session altered.

I’ve got 5 trace files, formatted them but I can’t find any waits in them (output from one of them):

TKPROF: Release 8.1.7.4.0 - Production on Wed Jul 13 11:25:31 2005

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Trace file: dev_s013_7142.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

select DOC.INVOICESeq.nextVal 
from
 dual


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        8      0.00       0.00          0          0          0           0
Execute      8      0.00       0.00          0          0          0           0
Fetch       15      0.00       0.00          0          8         32           8
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       31      0.00       0.00          0          8         32           8

Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user id: 39  (DOC)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SEQUENCE 
      1   TABLE ACCESS FULL DUAL 


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: FIRST_ROWS
      1   SEQUENCE OF 'INVOICESEQ'
      1    TABLE ACCESS (FULL) OF 'DUAL'




********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        8      0.00       0.00          0          0          0           0
Execute      8      0.00       0.00          0          0          0           0
Fetch       15      0.00       0.00          0          8         32           8
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       31      0.00       0.00          0          8         32           8

Misses in library cache during parse: 0


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

    8  user  SQL statements in session.
    0  internal SQL statements in session.
    8  SQL statements in session.
    1  statement EXPLAINed in this session.
********************************************************************************
Trace file: dev_s013_7142.trc
Trace file compatibility: 8.00.04
Sort options: default

      14  sessions in tracefile.
      68  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       8  SQL statements in trace file.
       1  unique SQL statements in trace file.
       1  SQL statements EXPLAINed using schema:
           DOC.prof$plan_table
             Default table was used.
             Table was created.
             Table was dropped.
     153  lines in trace file.

I am doing anything wrong while tracing?

Thank you for your time.

DanielD 

Tom Kyte
July 13, 2005 - 2:33 pm UTC

do you have timed statistics on?

SQL trace with WAITS

DanielD, July 13, 2005 - 2:38 pm UTC

Tom,

That was the first thing I've checked when I did not see any waits in trace files:

SQL> ALTER SESSION SET SQL_TRACE=FALSE;

Session altered.

Elapsed: 00:00:00.01
SQL> show parameter timed_statistics   

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
timed_statistics                     boolean TRUE

DanielD 

Tom Kyte
July 13, 2005 - 2:58 pm UTC

that would indicate then that the problem is *not* in the database - it is running lightspeed.

problem is between client and database - hows that network doing?

Another explanatin for DUAL

Ian Gallacher, July 14, 2005 - 4:36 am UTC

My input to why Dual exists is that because of restrictions in earlier Forms (2.3?) a work around had to be given to allow simple arithmetic to be performed and updating of database “outwith normal Commit”

i.e. add 1 to counter was not available as a function under forms 2.3
use DUAL :-

select :count + 1 into :count from dual;

Updating outwith Normal commit time

Create “dual_block” based on Dual in forms with one field dummy

Set dummy to ‘X’ and commit

Have one trigger post-update from which can update/insert/delete into/from as many tables as you like !!

Also, to get over bugs in pl/sql regardings dates
select to_date(to_char(to_date(:global.EVT_DT, 'dd-mon-rrrr'),'ddmmrrrr'),'ddmmrrrr') into:EVT_DT from dual;

Equivalent sql doesn’t work !

Hope this gives another slant on DUAL

Ian


DUAL with USER and SYS_CONTEXT

Michael, November 27, 2005 - 5:07 am UTC

Hi Tom,

we use a lot of statements like

BEGIN
IF USER = 'SCOTT' THEN
...
END IF;

or

BEGIN
IF SYS_CONTEXT ('mycontext', myname) = myvalue THEN
...
END IF;

wich are translated to

SELECT USER
FROM
SYS.DUAL

or

SELECT SYS_CONTEXT(:B2,:B1)
FROM
SYS.DUAL

in the tracefile.

Similar as one of the reviewers we suffer from high execs and high buffer gets only due to selects on dual.

How can we redirect queries on USER and SYS_CONTEXT to use
a selfbuild IOT-dual or maybe the x$dual as you mentioned above without rewriting all of our code as

IF (SELECT USER FROM MY_DUAL) = 'SCOTT' THEN ...

which would indeed work as we would like?

IF statements like above are used tremendously often in our code, so a rewrite is out of question.

Any hints/tips are greatly appreciated.

Michael

Tom Kyte
November 27, 2005 - 9:59 am UTC

You cannot - you CAN assign user to a global package variable once (it will not change in your session).

Same with the context variables - sys_context was designed to be used in SQL mostly (to "bind" fine grained access control predicates), not as replacements for variables.

waits on dual table

Sami, December 27, 2005 - 12:40 pm UTC

Dear Tom,

While I am investigating waits on some other segments I've noticed waits on dual table. Why I am seeing "row lock waits" and "buffer busy waits" on dual table.


SQL> select OBJECT_NAME,STATISTIC_NAME,value from v$segment_statistics where value >0 
and STATISTIC_NAME like '%wait%';
 
OBJECT_NAME                    STATISTIC_NAME             VALUE
------------------------------ --------------------- ----------
DUAL                           buffer busy waits             38
DUAL                           row lock waits               358
I_JOB_NEXT                     buffer busy waits             20
JOB$                           buffer busy waits             16
PROPS$                         buffer busy waits              1
UNDO$                          buffer busy waits             12
I_FILE#_BLOCK#                 buffer busy waits             12
SEG$                           buffer busy waits             38
TAB$                           buffer busy waits              5
LOB$                           buffer busy waits              6
DBMS_LOCK_ALLOCATED            buffer busy waits             24
AQ$_QUEUE_TABLE_AFFINITIES     buffer busy waits              1
AQ$_QUEUE_TABLES               buffer busy waits              1
DEF$_DESTINATION               buffer busy waits             14
DEF$_TRANORDER                 buffer busy waits              3
SYS_C001122                    buffer busy waits             24
DEF$_AQCALL                    buffer busy waits             15
PROFILEDUSER_PK                buffer busy waits             62
PROFILEDUSER_IX04              buffer busy waits             13
PROFILEDUSER                   buffer busy waits              8
PROFILEDUSER                   row lock waits                61
LOG_TABLE                      buffer busy waits              5
 
22 rows selected.
 
SQL> 


 

Tom Kyte
December 27, 2005 - 2:15 pm UTC

what version of sqlplus are you using - some of them used to issue:

SELECT NULL FROM DUAL FOR UPDATE NOWAIT;


lots of people using sqlplus - could have lots of "waits" for that row.


SELECT NULL FROM DUAL FOR UPDATE NOWAIT;

Sami, December 27, 2005 - 4:49 pm UTC

Tom, Thanks for your quick response.

We are using 9.2.0.6.

SQL*Plus: Release 9.2.0.6.0 - Production on Tue Dec 27 13:40:03 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining
options
JServer Release 9.2.0.6.0 - Production

My understanding is that "SELECT NULL FROM DUAL FOR UPDATE NOWAIT;" will issued from SQl*Plus utility when someone connects from SQL*Plus. Am I right?

Tom Kyte
December 27, 2005 - 5:17 pm UTC

correct - it was a way to see if the database was read only or not - since changed in later releases.

so connections to the database via some releases of sqlplus are "serialized"

problem in selecting dual

Alay, April 08, 2006 - 5:08 am UTC

Hi Tom,

I am facing on problem with dual.
When I am trying to describe dual table with my user id it shows the error "INVALID OBJECT FOR DESCRIBE".

SQL> conn shivkumar
Enter password:
ERROR:
ORA-28002: the password will expire within 4 days


SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.
Connected.

SQL> desc dual
ERROR:
ORA-24372: invalid object for describe

But when I use sys.dual it working fine.

SQL> desc sys.dual;
 Name    Null?    Type
 ----------------------
 DUMMY            VARCHAR2(1)


In my database dual is a PUBLIC synonyms.
Everyone else can use "desc dual".
Above problem is with one perticular user only.
What could be the solution so I can use only dual instead of sys.dual? 

Tom Kyte
April 08, 2006 - 9:50 am UTC

do this for us

select owner, object_name, object_type from all_objects where object_name = 'DUAL'

when logged in as that user.

problem in selecting dual

Alay, April 08, 2006 - 12:27 pm UTC

This is the output:

SQL> conn shivkumar
Enter password:

SQL> select owner, object_name, object_type from all_objects where object_name = 'DUAL';


OWNER          OBJECT_NAME         OBJECT_TYPE
------------------------------------------------
SYS               DUAL               TABLE
PUBLIC            DUAL              SYNONYM
SHIVKUMAR         DUAL              PACKAGE

 

Tom Kyte
April 08, 2006 - 1:00 pm UTC

okey dokey - do you see the problem NOW?


you are shivkumar
you, shivkumar, own a package named DUAL
YOUR schema objects override all public synonyms

hence desc dual for you is desc shivkumar.dual

and I'll bet your DUAL package is invalid.


Seriously bad bad name for a package don't you think?

DUAL trivia?

David Weigel, April 09, 2006 - 10:35 pm UTC

So, why is the table called "DUAL"? Was it some connection to Oracle's legacy on old VAX/VMS machines where disks often had "DUA" in the name, like "DUA1:"?

Tom Kyte
April 10, 2006 - 5:29 am UTC

Ken Jacobs told me that it once was a matrix with more than one row (two in fact), used to pivot. By the time it was "production", it was down to a single row, but the name stuck.

Whatever happened to

A reader, April 10, 2006 - 3:42 pm UTC

... truth in advertising?


;-)

Bhupesh Bothra

Bhupesh Bothra, April 17, 2006 - 9:02 am UTC

What will happen if i change something in Dual?

Tom Kyte
April 17, 2006 - 9:56 am UTC

I will never talk to you again.

Don't do it.

treble

A reader, April 17, 2006 - 12:17 pm UTC

just did (as sys)
rename dual to treble

and db is still up
:-)

Tom Kyte
April 17, 2006 - 8:06 pm UTC

... the sound of silence ....

RE: SELECT NULL FROM DUAL FOR UPDATE NOWAIT

John, June 12, 2006 - 4:37 pm UTC

Tom,
You mentioned that some versions of sql*plus issue this command automatically. Could you please elaborate on which versions this is not done and is there a way to disable this behavior ?

Tom Kyte
June 13, 2006 - 10:59 am UTC

not sure what versions do it specifically, no you cannot disable it.

I just tested 9ir2 sqlplus and it does (but 10gr2's did not)

What is the difference performance-wise

A reader, June 22, 2006 - 7:41 pm UTC

Hi Tom,

Can you please let me know which is the better way:

SELECT FND_PROFILE.VALUE('XYZ') FROM DUAL;

or

declare
l varchar2(100);
begin
l := fnd_profile.value('xyz');
end;

Thanks.

Tom Kyte
June 23, 2006 - 10:09 am UTC

number 2.

select f(x) from dual;

must - run sql, invoke plsql, get return value, return it to you.



begin ... end;

must - run plsql

select UDF from dual?

Chris, July 05, 2006 - 1:10 pm UTC

I have a query written slightly differently running on AIX5L - 9207. The first selects a UDF from dual whereas the second does not. The first form runs much faster and does a lot less work. Here are the queries along with the autotrace outpout.

Query Form 1
------------

select func1.displayAccount(account_filter_id), func1.displayRoleOrResource(ROLE_ID, RESOURCE_ID) from t1 where ACCOUNT_FILTER_ID = (select func1.firmAccount('123') from dual)

Execution Plan
----------------------------------------------------------
0 *
SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=7 Bytes=56)
1 0
TABLE ACCESS (FULL) OF 'T1' (Cost=64 Card=7 Bytes=56)
2 1
TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=1)

Statistics
----------------------------------------------------------
167 recursive calls
0 db block gets
11930 consistent gets
9862 physical reads
0 redo size
3055 bytes sent via SQL*Net to client
684 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
55 sorts (memory)
0 sorts (disk)

Query Form 2
------------

select func1.displayAccount(account_filter_id), t1 where ACCOUNT_FILTER_ID in (func1.firmAccount('123'))

Execution Plan
----------------------------------------------------------
0 * SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=7 Bytes=56) *
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=64 Card=7 *
Bytes=56)

Statistics
----------------------------------------------------------
18826 recursive calls
0 db block gets
2008440 consistent gets
1714989 physical reads
0 redo size
3055 bytes sent via SQL*Net to client
684 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
18714 sorts (memory)
0 sorts (disk)
54 rows processed


I can see that a lot less work is being done with the first form and differently (memory sorts) but I can't explain why.

Does the CBO consider the function to be deterministic because I'm selecting from dual or is PL/SQL invoked just once (Query 1) vs once for each row (Query 2)?

Could a coding standard using the first form be implied to be better?

I'd appreciate any insight...

Tom Kyte
July 08, 2006 - 8:31 am UTC

the cbo never considers a function to be deterministic unless you define it that way.


when you do this:


select func1.displayAccount(account_filter_id),
func1.displayRoleOrResource(ROLE_ID, RESOURCE_ID)
from t1
where ACCOUNT_FILTER_ID = (select func1.firmAccount('123') from dual);
^^^^^^^^^^^^^ scalar subquery ^^^^^^^^^^^^^


we can cache the scalar subquery - and that firmAccount function is called once (since the inputs to it are static)

</code> http://asktom.oracle.com/pls/ask/search?p_string=%22scalar+subquery+caching%22


when you did this:

where ACCOUNT_FILTER_ID in (func1.firmAccount('123'))

we do not have a scalar subquery and that function might be called over and over and over again... coding it as:

here ACCOUNT_FILTER_ID in 
( (select func1.firmAccount('123') from dual) )

would reintroduce the scalar subquery caching....


(in fact, you might consider using (select f(x) from dual) in most cases...)


Until 10gr2 - this caching is not done even for deterministic functions
http://dizwell.com/main/index.php?option=com_jd-wiki&Itemid=170&id=deterministicfunctions <code>





select UDF from dual

Chris, July 12, 2006 - 3:25 pm UTC

Thank you for the response. The 'subquery caching' feature does explain why the form (select f(x) from dual) is more efficient. Indeed, everything I've read/been told indicates that the function is only being called once but the output below from my scenario seems to indicate 54 calls to the function regardless of the form. Perhaps there is a problem with my approach unless it's possible that the function is being called once per row but that the result set from the SQL inside the function in only executed once (cached).

Also, sorry I don't understand the statement "Until 10gr2 - this caching is not done even for deterministic functions" as the more efficient more (select f(x) from dual) that I'm running in 9207 is much more efficient - so assume caching is done in version 9i too?


**BEGIN SPOOL FILE***
09:15:30 system@ORA9207>
09:15:34 system@ORA9207> exec dbms_application_info.set_client_info( 0 );

PL/SQL procedure successfully completed.

09:15:35 system@ORA9207> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
0

09:15:39 system@ORA9207> set autotrace traceonly;
09:15:42 system@ORA9207> select func1.displayAccount(account_filter_id),
09:15:47 2 func1.displayRoleOrResource(ROLE_ID, RESOURCE_ID)
09:15:47 3 from t1
09:15:47 4 where ACCOUNT_FILTER_ID = (select func1.firmAccount('123') from dual);

54 rows selected.


Execution Plan
----------------------------------------------------------
0 * SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=7 Bytes=56) *
1 0 TABLE ACCESS (FULL) OF 'SECURITY_CONTEXT' (Cost=64 Card=7 *
Bytes=56)

2 1 TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=1) *




Statistics
----------------------------------------------------------
167 recursive calls
0 db block gets
11930 consistent gets
9861 physical reads
0 redo size
3057 bytes sent via SQL*Net to client
684 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
55 sorts (memory)
0 sorts (disk)
54 rows processed

09:15:51 system@ORA9207> set autotrace off;
09:15:56 system@ORA9207> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
54

09:16:00 system@ORA9207> exec dbms_application_info.set_client_info( 0 );

PL/SQL procedure successfully completed.

09:16:04 system@ORA9207> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
0

09:16:14 system@ORA9207> set autotrace traceonly;
09:16:18 system@ORA9207> select func1.displayAccount(account_filter_id),
09:16:25 2 func1.displayRoleOrResource(ROLE_ID, RESOURCE_ID)
09:16:25 3 from t1
09:16:25 4 where ACCOUNT_FILTER_ID in (func1.firmAccount('123'));

54 rows selected.

Execution Plan
----------------------------------------------------------
0 * SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=7 Bytes=56) *
1 0 TABLE ACCESS (FULL) OF 'SECURITY_CONTEXT' (Cost=64 Card=7 *
Bytes=56)


Statistics
----------------------------------------------------------
18822 recursive calls
0 db block gets
2008440 consistent gets
1686000 physical reads
0 redo size
3057 bytes sent via SQL*Net to client
684 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
18714 sorts (memory)
0 sorts (disk)
54 rows processed

09:20:56 system@ORA9207> set autotrace off;
09:22:12 system@ORA9207> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
54

09:22:18 system@ORA9207> spool off

***END SPOOL FILE***




Tom Kyte
July 12, 2006 - 4:05 pm UTC

the 10gr2 was all about the fact that "select f(x) from ..." will use caching if F is defined deterministic - you don't need to do the "select f(x) from dual" to trick it (JPL's pointed to paper describes this)




Chris, July 13, 2006 - 3:06 pm UTC

I see - thank you. In reference to my first question, would you agree that the function is called once per row regardless of the form used? It appears it was called 54 times in my example regardless of whether a subquery was used.

Tom Kyte
July 13, 2006 - 5:19 pm UTC

the function in the SELECT list maybe, but it was never wrapped in the scalar subquery???

09:15:42 system@ORA9207> select func1.displayAccount(account_filter_id),
09:15:47 2 func1.displayRoleOrResource(ROLE_ID, RESOURCE_ID)
09:15:47 3 from t1
09:15:47 4 where ACCOUNT_FILTER_ID = (select func1.firmAccount('123') from
dual);

which FUNCTION did the counting here????

how many rows in T1???

Chris, July 14, 2006 - 10:48 am UTC

Holy tunnel vision! I was so caught-up thinking about the subquery caching feature that I overlooked the functions in the select statement. I made the adj and confirmed the firmAccount function is only being called once.

My bad!

Date generation

Neeraj, August 25, 2006 - 9:00 am UTC

Hi Tom,
I need to generate dates of a month through SQL query.Is it possible?
i,e,

1-jan-2006
2-jan-2006
3-jan-2006
.......
31-jan-2006


Tom Kyte
August 27, 2006 - 8:24 pm UTC

ops$tkyte%ORA10GR2> variable x varchar2(10)
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec :x := 'jan-2006';

PL/SQL procedure successfully completed.



ops$tkyte%ORA10GR2> with data
  2  as
  3  ( select level l
  4      from dual
  5    connect by level <= to_number(to_char(last_day(to_date(:x,'mon-yyyy')),'dd'))
  6  )
  7  select to_date(:x,'mon-yyyy')+l-1
  8    from data;

TO_DATE(:
---------
01-JAN-06
02-JAN-06
...
30-JAN-06
31-JAN-06

31 rows selected.

 

Date generatiion

Neeraj, August 28, 2006 - 12:49 am UTC

Tom, you are Simply gr8.


The IOT and view trick

Uwe M. Kuechler, September 22, 2006 - 1:38 pm UTC

Hi Tom,

I thought I'd take the "View Trick" from above a step further and create a view on an IOT table "mydual" to compare it to the above x$dual solution.
Just in case anybody was thinking the IOT solution would be the smartest:

View on x$dual (Run 1) vs. View on IOT DUAL (Run 2):

NAME RUN1 RUN2 DIFF
-------------------------------------------- --------- ---------- ----------
STAT...redo size 1888 11424 9536
STAT...index scans kdiixs1 0 50014 50014
STAT...shared hash latch upgrades - no wait 0 50014 50014
STAT...consistent gets 5 50044 50039
STAT...session logical reads 15 50103 50088
STAT...session pga memory max 0 65536 65536
LATCH.cache buffers chains 54 100348 100294

Now where could the additional redo of Run2 originate from? On my test instance, I was the only active User and not DML-ing anything during the test.
This might also be quite relevant because on one system I'm not only battling with > 1000 "select ... from dual" per second but also with high redo sizes.

Best regards,
Uwe

magic bug

&amp;#931;&amp;#969;&amp;#954;&amp;#961;&amp;#940;&amp;#964;&amp;#951;&amp;#962;, September 25, 2006 - 5:17 am UTC

SQL> startup mount
ORACLE instance started.

Total System Global Area  922746880 bytes
Fixed Size                  1222624 bytes
Variable Size             146802720 bytes
Database Buffers          767557632 bytes
Redo Buffers                7163904 bytes
Database mounted.
SQL> describe dual
ERROR:
ORA-04043: object dual does not exist


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced


 

Tom Kyte
September 25, 2006 - 8:02 am UTC

please utilize support, for the alter database open problem.

dual is not describable until the database is OPEN so that is OK:


idle> startup mount
ORACLE instance started.

Total System Global Area 235999648 bytes
Fixed Size 450976 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
idle> desc dual
ERROR:
ORA-04043: object dual does not exist


idle> select * from dual;

ADDR INDX INST_ID D
-------- ---------- ---------- -
0A8A90D8 0 1 X


ahh.... i see, never mind, the describe causes the subsequent open to fail. :)

got it.


;-)

&amp;#931;&amp;#969;&amp;#954;&amp;#961;&amp;#940;&amp;#964;&amp;#951;&amp;#962;, September 25, 2006 - 8:49 am UTC


Dual on RAC

Ian, September 25, 2006 - 12:24 pm UTC

Tom

On our Production RAC environment (9.2.0.6) if you look at v$segment_statistics for global cache cr blocks served and global cache current blocks served dual shows up as the top table. Leaving aside the fact that it makes no sense (to me at least) to ship dual back and forth across the interconnect - what I really can't figure out is why you would ever need a consistent read image of dual.

The figure for global cache cr blocks served for dual is 14% of all blocks served. Today we shipped dual accross the interconnect 674114 times in cr mode and 43578 times in cu mode.

Just curious really.

Regards

Ian

Tom Kyte
September 25, 2006 - 4:37 pm UTC

because dual is just a table in 9i and before

in 10g, it is magic - it turns into a function (for this very reason) if you do not select the actual column dummy from it.

so, 10g - dual traffic will disappear for you.

Dual Has so much to know about .....

gandla shekhar reddy, September 25, 2006 - 1:48 pm UTC

Tom,
You are great and i never thought there is so much about simple dual ...

gandla shekhar reddy
gandla shekhar reddy

it seems to me

A reader, September 25, 2006 - 2:31 pm UTC

that every day, we learn something new about dual

To Ian - DUAL and RAC

Roderick, September 25, 2006 - 3:41 pm UTC

In theory, the DUAL table should have no problem staying resident in each node's buffer cache and not need to be shipped over the interconnect. Plus no process should be updating DUAL so a CR version should be sufficient most of the time.
Therefore, what you may be witnessing is a side-effect of SQL*Plus behavior (select NULL from DUAL for update) in 9.2.0.6 (and other versions) as noted in Metalink DocID 4179282.8.
Also note that if you move up to 10g, many SQL statements that reference DUAL may be rewritten internally by Oracle to reference the X$DUAL in-memory table instead.

Tom Kyte
September 26, 2006 - 2:05 am UTC

(and don't forget applications themselves that select for update against dual, I've seen that as well - beyond the sqlplus issue)

Dual and RAC

Ian, September 26, 2006 - 7:46 am UTC

Roderick

Thanks for the info. But now I am a little confused between CU and CR mode. I thought CU was Current and CR was Consistent Read (i.e. Been updated somewhere so need an earlier image)?

Regards

Ian

Tom Kyte
September 26, 2006 - 3:51 pm UTC

CR is consistent read
CU is current

he was saying - if you just need a CR version, pinging should not be a big issue.

CR is a way to read - it doesn't matter if the block has been updated or not, you are making a "cr request"



resources for select from dual

Bonnie, November 03, 2006 - 1:23 pm UTC

Hi Tom,

I'm not sure how to start a new question, so I'm continuing on with this link as it pertains to the dual table.

We are experiencing some performance issues with our 8.1.7 database. Our DBA support indicated the DB is running at it's highest efficiency. So he ran a some statistics and have discovered that "select user from dual" is
both the most executed statement and largest accumulative statement for the database(note that sys selects are the top three for accumulative size and that "select user from dual" is over 5X larger than the next statement):>

Largest accumulative statements:
> >
> > Buffer Gets Executions Gets per Exec % Total Hash
> Value> --------------- ------------ -------------- ------- --
> ----------
> > 174,706,050 38,746,182 4.5 2.6
> 2016510618> SELECT USER FROM SYS.DUAL
> >
> > 32,567,116 600,521 54.2 0.5
> 313510536> select job from sys.job$ where next_date <
> sysdate and (field1
> > = :1 or (field1 = 0 and 'Y' = :2)) order by next_date, job
> >
> > 14,300,013 3,138,289 4.6 0.2
> 2620572366> SELECT SYS_CONTEXT(:b1,:b2) FROM SYS.DUAL
> >
> > 12,645,378 339,694 37.2 0.2
> 2904863199> SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDEX(A2
> "SYS_C00906099"> ) */ A2."COST_GL_CD"
> C0,TO_NUMBER(TO_CHAR(A1.C3,'YYYYMM')) C1,A1
> > .C0 C2,A1.C1 C3,A1.C2 C4,A1.C3 C5,A1.C4 C6,A1.C5 C7,A1.C6
> C8,A1.> C7 C9,A2.ROWID C10,A2."CENTRE_KEY"
> C11,A2."SERVICE_TYPE" C12 FROM
> > (SELECT /*+ ORDERED NO_EXPAND USE_NL(A4) INDEX
(A4 "SYS_C009060
> >
> > 8,923,786 1,662,068 5.4 0.1
> 975917520> SELECT CDR_KEY FROM RAD.IP_BASIC A WHERE
> A.CHART_NUMBER = :b1
> AND
> > A.SITE = :b2 AND A.ADMIT <= :b3 AND A.DISCHARGE >= :b4
> > AND A.MAIN_SERV != '89' AND A.DISCHARGE >= '01-APR-2005'
> AND A
> > .DISCHARGE < '01-APR-2006'
> >
> > Most executed statements:
> >
> > Executions Rows Processed Rows per Exec Hash Value
> > ------------ ---------------- ---------------- ------------
> > 38,746,182 34,941,340 0.9 2016510618
> > SELECT USER FROM SYS.DUAL
> >
> > 22,396,405 -12,873,230 -0.6 88648810
> > begin MESSAGE_QUEUE.getCurrentQueueState(:1,:2,:3); end;
> >
> > 22,396,391 -12,873,228 -0.6 3726987281
> > begin :1 := MESSAGE_QUEUE.isQueueProcRunning (:2,:3); end;
> >
> > 22,396,390 -17,346,773 -0.8 791573392
> > begin MESSAGE_QUEUE.QUEUE_LOADER(:1,:2); end;
> >
> > 22,175,535 -1,798,685 -0.1 655539640

I'm only one user of many in the DB, but in my processes, I use the trigger to populate both users and date using user, and sysdate at the same frequency. If the function user invokes the query "select user from sys.dual", then this should also be happening for sysdate, but that is not the case. Can you explain why we're seeing the difference, and if this query impacts performance, and how it can be tune to improve it?

Your help is greatly appreciated as always.

Thank you.
Bonnie

Tom Kyte
November 04, 2006 - 12:06 pm UTC

well, since the user never changes, why don't you change the references to "user" to be:

create package my_globals
as
g_username varchar2(30) default USER;
end;
/

my_globals.g_username


that'll take that right down.

Another way to generate range.

Neeti, November 06, 2006 - 5:02 pm UTC

select rownum from dual connect by rownum < 10;



Tom Kyte
November 07, 2006 - 4:23 pm UTC

demonstrated many times on this site, recommend you

with data
as
(select level L from dual connect by level <= 10)
select ...
from data, ....


instead of that you did - which won't work reliably in 9i

Got Problem about X$DUAL

BoMBaY, November 28, 2006 - 1:31 am UTC

Hi Tom,
Start With :
I connect to database with sys. and then
I create view dual_view that using select dummy from x$dual.
After that, I grant select on this view to public.
Then, I connect to database with another user.
I set autotrace on.
When I select * from sys.daul_view.
I got an error ORA-01039 in the explain plan section.
Thanks,
BoMBaY

Tom Kyte
November 28, 2006 - 7:16 am UTC

not a problem, I would not recommend what you are doing - but basically it is saying "sorry, you cannot explain the .." well - you already know the message.



Problem about X$DUAL (Continue)

BoMBaY, November 28, 2006 - 1:37 am UTC

SQL> set autot trace
SQL> select sysdate from sys.dual_view;
 
 
Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view
 
 
SP2-0612: Error generating AUTOTRACE EXPLAIN report
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
... ...
          1  rows processed
 

Tom Kyte
November 28, 2006 - 7:17 am UTC

yes, you do not have the ability to explain the x$ tables, I suggest you just use "dual"

X$ Table

BoMBaY, November 30, 2006 - 9:02 pm UTC

How can i get privilege to explain plan $X Table ?

Tom Kyte
November 30, 2006 - 9:15 pm UTC

you don't, just don't do this - only asking for trouble.

Why I can not use the DUAL in subquery ?

Kamlesh Gujarathi, December 21, 2006 - 2:34 pm UTC

Hello Tom,

This site is very useful for me. I saw you many seminars also on site.

I have one query.

Why I can not use the DUAL in subquery.

My query gives error.

INSERT INTO TABLENAME
(FILED1, FILED2, FIELD3)
VALUES
((SELECT SEQUENCENAME.NEXTVAL FROM DUAL),
FIELDVALUE2, FIELDVALUE3)

This dont works ?

Tom Kyte
December 22, 2006 - 6:08 am UTC

ops$tkyte%ORA10GR2> insert into t values ( (select 1 from dual), 1 );

1 row created.


now, the sequence might be a problem there, but dual sure works in a scalar subquery. 

Satyanarayana Reddy, January 22, 2007 - 9:51 am UTC

Very Interesting

Bill B, August 16, 2007 - 2:58 pm UTC

You used the insert

INSERT INTO TABLENAME
   (FILED1, FILED2, FIELD3)
VALUES 
   ((SELECT SEQUENCENAME.NEXTVAL FROM DUAL), 
     FIELDVALUE2, FIELDVALUE3);


Why didn't you simply do

INSERT INTO TABLENAME
   (FILED1, FILED2, FIELD3)
VALUES 
   (SEQUENCENAME.NEXTVAL,FIELDVALUE2, FIELDVALUE3);


Why do a sub select when there is absolutely no need. That is almost as bad as all those people who use execute immediate on simple selects.

Alias with DUAL

Raghu Raman, August 29, 2007 - 4:55 pm UTC

Tom,

Just curious to know the difference between the below 2 statements.

SQL> select user from dual;

USER
------------------------------
APPS

SQL> select d.user from dual d;
select d.user from dual d
         *
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification

Why isn't Alias working with Dual table?

Regards
Raghu

Tom Kyte
September 04, 2007 - 4:47 pm UTC

because USER is not a column in DUAL, user is a 'function'

USER is not a column, it doesn't come from dual.

Alias with Dual

Raghu Raman, August 29, 2007 - 5:17 pm UTC

Sorry, wrong question. I thought i was having the column name Dummy instead of User.

Raghu

Strange Dual Behaviour

Anupam Pandey, September 19, 2007 - 6:16 am UTC

Hi Tom, 
       Dual is one column one row special table .
SQL> desc dual;

 Name                            Null?    Type
 ------------------------------- -------- ----
 DUMMY                                    VARCHAR2(1)

SQL> select *
  2  from dual;

D
-
X

SQL> select 10,20
  2  from dual;

       10        20
--------- ---------
       10        20

But in the above query when i asked for two columns from dual table,its giving me two columns. What is the logic behind that . 

Thanks an advance 
 Anupam Pandey 

Tom Kyte
September 19, 2007 - 12:46 pm UTC

you asked for two literals to be retrieved from a single row table.


I don't understand why this would be preplexing? Not any different than selecting 10, 20 from all objects or any other table?

Questioner should check their definition of DUAL

stew, September 21, 2007 - 2:00 pm UTC

> Dual is one column one row special table .

Their original premise is wrong.

generate new values with model

Lee Lindley, April 28, 2008 - 2:05 am UTC

Thanks for the "connect by" technique for generating values.   And I see the other ones like selecting from all_objects or a PL/SQL table function.   I was hoping the MODEL clause FOR loop could provide a technique that could be more understandable from a procedural viewpoint without resorting to PL/SQL, and it does; however, it is less efficient. The "connect by" is brutally efficient.  Nevertheless, here is yet another way to do it.

var levels number;
exec :levels := &levels;
set timing on
set autotrace traceonly
WITH x AS (SELECT LEVEL AS lev FROM dual CONNECT BY LEVEL <= :levels)
SELECT lev, TO_DATE('19900101','YYYYMMDD')+lev-1 AS dt FROM x;
/* now for model */
SELECT lev, dt
FROM dual
MODEL
    DIMENSION BY (0 AS lev)
    MEASURES (TO_DATE('19990101','YYYYMMDD') AS dt)
    RULES (
        dt[FOR lev FROM 0 TO :levels-1 INCREMENT 1] = dt[0] + CV(lev)
    )
;

Then after running to make sure it was parsed, ran again.
SQL> @x.sql
Enter value for levels: 100000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

100000 rows selected.

Elapsed: 00:00:00.87

Execution Plan
----------------------------------------------------------
Plan hash value: 377677300

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

| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time
     |

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

|   0 | SELECT STATEMENT              |      |     1 |    13 |     2   (0)| 00:0
0:01 |

|   1 |  VIEW                         |      |     1 |    13 |     2   (0)| 00:0
0:01 |

|*  2 |   CONNECT BY WITHOUT FILTERING|      |       |       |            |
     |

|   3 |    FAST DUAL                  |      |     1 |       |     2   (0)| 00:0
0:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(LEVEL<=TO_NUMBER(:LEVELS))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
    2629441  bytes sent via SQL*Net to client
      73795  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     100000  rows processed


100000 rows selected.

Elapsed: 00:00:03.27

Execution Plan
----------------------------------------------------------
Plan hash value: 50209616

------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2   (0)| 00:00:01 |
|   1 |  SQL MODEL ORDERED|      |     1 |            |          |
|   2 |   FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
    2629440  bytes sent via SQL*Net to client
      73795  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed

Ambigous Column Error

Bhushan, July 17, 2008 - 12:29 pm UTC

Hi Thomas,
I ran this piece of code on 8.1.7.4 it works perfect.
Tried Runing it on 9.2.0.1.0 and 10.2.0.4.0 it gives me ORA-00918.
Is it the desired behaviour...?
CREATE TABLE test (x VARCHAR2(10),z VARCHAR2(10));

INSERT INTO test (x) (SELECT substr(object_name,1,10) FROM all_objects WHERE ROWNUM < 100);

COMMIT;

---Test Script----
-- Created on 7/17/2008 by 142037
declare
-- Local variables here

CURSOR tst IS
SELECT x,z FROM test;
begin
-- Test statements here
FOR i IN tst LOOP
INSERT INTO test (z,x) SELECT * FROM (SELECT x,i.x FROM test WHERE x=i.x GROUP BY x,z);
commit;
END LOOP;

end;
-------
Your Views please.

Regards,
Bhushan
Tom Kyte
July 17, 2008 - 12:41 pm UTC

looks like

a) a really bad idea (it took many minutes to figure out what the heck you were trying to do - what "x" was all over the place)

b) a bug in 8i that accidentally permitted it.

  5       INSERT INTO test (z,x)
  6       SELECT *
  7         FROM (SELECT x,i.x
  8                 FROM test
  9                WHERE x=i.x
 10                GROUP BY x,z);


note that the name of i.x would be "X"

ops$tkyte%ORA10GR2> variable x refcursor
ops$tkyte%ORA10GR2> declare
  2    CURSOR tst IS SELECT x,z FROM test;
  3  begin
  4          for i in (select x from test where rownum = 1 )
  5          loop
  6          open :x for
  7       SELECT *
  8         FROM (SELECT i.x
  9                 FROM test
 10                WHERE x=i.x
 11                GROUP BY x,z);
 12          end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print x

X
--------------------------------
ICOL$




I suggest you alias these columns to avoid the ambiguity - name them "well"

Great to know.

Bhushan, July 17, 2008 - 11:45 pm UTC

Yes i could'nt agree more about having the alias.That is how i actually resolved it.We are in middle of migrating the DB from older versions (7,8,8i) to 10g for all of our applications and i observed this, so wanted to know if it was a bug in 9i and 10g or a bug then in 8i.About the example yeah i know its confusing..i tried to simulate a part of the code that was present in a procedure which spaneed over 3k lines :( ...(will have to do something therentoo i guess)
Anyways...thanks for the clarification.
Cheers!!!
Bhushan

DUAL table results

Victor, July 25, 2008 - 4:28 am UTC

Hello:

I have the following problem with the DUAL table. I have two databases, that on having consulted on the DUAL return different results.

In the first DB.

Select 'HI' from dual;
'HI'
--------------------------------------------------------------------------------
HI

In the second DB.

Select 'HI' from dual;

'HI'
----
HI

It is possible to form the space count in white for the left side that they put when the literal one consults on DUAL.

Thanks, sorry for my English, is very bud.

Tom Kyte
July 29, 2008 - 10:13 am UTC

you have cursor_sharing set to force or similar on one database and exact on the other.

In one database we know 'HI' is what you are selecting.

On the other, we have no clue, you used cursor_sharing...

ops$tkyte%ORA10GR2> alter session set cursor_sharing=exact;

Session altered.

ops$tkyte%ORA10GR2> select 'hi' from dual d1;

'H
--
hi

ops$tkyte%ORA10GR2> alter session set cursor_sharing=force;

Session altered.

ops$tkyte%ORA10GR2> select 'hi' from dual d2;

'HI'
--------------------------------
hi

ops$tkyte%ORA10GR2> alter session set cursor_sharing=exact;

Session altered.

ops$tkyte%ORA10GR2> select sql_text from v$sql where sql_text like 'select % from dual d%';

SQL_TEXT
-------------------------------------------------------------------------------
select sql_text from v$sql where sql_text like 'select % from dual d%'
select :"SYS_B_0" from dual d2
select 'hi' from dual d1


Victor, August 01, 2008 - 7:01 am UTC

yes, in a database I have put cursor_sharing=EXACT, ISDEFAULT ='TRUE ' and in other one is SIMILAR, ISDEFAULT ='FALSE ', which is more correct?, How I can know if I use cursor_sharing, I do not deal, in order that they all still have the sesiones modified, I have modificate in DBA?. thanks
Tom Kyte
August 03, 2008 - 1:51 pm UTC

I would say that at the system level, cursor sharing should be exact.

individual applications that "need" this crutch while they fix the bug they have (not using binds) can enable it as they need.

But this is something you need to sit down and discuss with the person that set it, they must have had a good reason for doing so - and you'll need to understand why it is set to similar.

Victor, August 04, 2008 - 4:16 am UTC

Thank you very much, yes, I will speak it with the DBA to know the reason for this which put in SIMILAR instead of EXACT, still this way I continue without understanding that relation has this parameter with the blanks that it puts for the right.

DUAL Table is capable of producing a pair of rows from it using join

Nitin, May 14, 2010 - 8:42 am UTC

SQL>  SELECT NVL (a.dummy, 'X') X
  2     FROM    DUAL a
  3          FULL JOIN
  4             DUAL b
  5          ON a.dummy = 'Z'
  6  /

X
-
X
X

Tom Kyte
May 24, 2010 - 7:14 am UTC

ops$tkyte%ORA11GR2> select null from dual group by cube(1,2,3);

N
-









8 rows selected.

ops$tkyte%ORA11GR2> select * from dual connect by level <= 2;

D
-
X
X





Not sure the point - there are many ways to get as many rows as you want from any table, including an empty one...

ops$tkyte%ORA11GR2> create table t (x int);

Table created.

ops$tkyte%ORA11GR2> select * from t;

no rows selected

ops$tkyte%ORA11GR2> select count(*) from t;

  COUNT(*)
----------
         0

ops$tkyte%ORA11GR2> select 'x' from (select count(*) from t) group by cube(1,2);
'
-
x
x
x
x



but - important point here - you are NOT getting more than one row from dual, you are getting more than one row from your SET, your QUERY, your virtual table - which isn't dual.

lnnvl

Sokrates, June 28, 2010 - 9:54 am UTC

what's wrong with

11.2.0.1.0 > select * from dual where lnnvl(dummy = any( 'A' ));

DUM
---
X

11.2.0.1.0 > select * from dual where lnnvl(dummy = any( 'A', 'B' ));
select * from dual where lnnvl(dummy = any( 'A', 'B' ))
*
ERROR at line 1:
ORA-13207: incorrect use of the [LNNVL] operator

?
Tom Kyte
July 06, 2010 - 11:44 am UTC

I believe is it because

dummy=any('a')

is really
dummy='a'

whereas

dummy=any('a','b')

is really
dummy='a' OR dummy='b'

and the or isn't allowed in the lnnvl, you'd have to expand it yourself

"the or isn't allowed in the lnnvl"

Sokrates, July 07, 2010 - 12:57 am UTC

thank you,

I didn't know that, can't find it documented either nor a note about it, where is it ?
[
I use
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions089.htm#SQLRF06327
]


Tom Kyte
July 08, 2010 - 11:35 am UTC

only simple predicates are allow,

expresion <op> expression



http://docs.oracle.com/docs/cd/E11882_01/server.112/e10592/functions089.htm#SQLRF06327

Oracle Database sometimes uses the LNNVL function internally in this way to rewrite NOT IN conditions as NOT EXISTS conditions. In such cases, output from EXPLAIN PLAN shows this operation in the plan table output. The condition can evaluate any scalar values but cannot be a compound condition containing AND, OR, or BETWEEN.


It is a side effect of IN( expression, expression, ... ) being a syntactic short cut for a bunch of OR'ed equals.

Thanks

Sokrates, July 14, 2010 - 3:57 am UTC

And sorry for Not Reading the Doc carefully enough

Bug ?

Sokrates, August 16, 2010 - 2:41 am UTC

SQL > select * from dual where exists(select 1/0 from dual);

DUM
---
X


oops - this seems to be a bug to me.
Doku says, "Exists-Condition" evaluates to "TRUE if a subquery returns at least one row."

the subquery
select 1/0 from dual

however, returns no row
Tom Kyte
August 19, 2010 - 1:01 am UTC

where exists (select ANYTHING from anytable)


is really:


where exists ( select NULL from anytable)


we *never* need to actually project anything in a where exists query, you should ALWAYS just select NULL just to remind yourself of that fact.



Dual Supports More than a Row

Anver, August 19, 2010 - 7:54 am UTC

I read at the beginning that dual is meant for only one row. i think i have a different view on that. For Example the below query can give me 10 rows.

select * from dual connect by level <= 10

this can be modified for individual use say, to get a date range

select sysdate+rownum from dual connect by level <= 10.

a date range of sysdate+10.

Also i agree to the concept that dual is a magic. and congrats for the brain [whoever it is] who's idea dual was.

Tom Kyte
August 19, 2010 - 3:14 pm UTC

that does not mean dual has more than one row.

I can get as many rows as I want from a ZERO (empty) row table.


ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select level, t.*
  2    from (select max(x) from t) t
  3  connect by level <= 5;

     LEVEL     MAX(X)
---------- ----------
         1
         2
         3
         4
         5




that does not mean T has five rows - the query result set does, but not the table.

Previous versions?

Greg, August 25, 2010 - 9:12 am UTC

Anver: I'm not sure your point is valid. I may be wrong, (Tom?) but dual existed since the very beginning of Oracle, however, that "connect by level" trick didn't really work in older versions ..

Connect by level didn't function straight up in older versions, and I doubt it was deliberately created for this purpose.
"Hey guys, I got a great idea!" ...
dual is dual .. connect by level is something "special" (and as Tom showed, doesn't even need dual)

It's like saying apples are great because you like apple crisp ... apples are apples .. they're great because they're apples ... what you make using them is something completely different .. :)

Hidden column in dual

Harry Zhang, August 26, 2010 - 3:04 am UTC

Hi Tom,

Just curious about dual. Seems there are hidden columns. Are there other columns and what is the below used for? Look like the sql come from some system packages or jobs.

select dummy from dual where ora_dict_obj_type = 'SYNONYM' AND ora_dict_obj_owner = 'PUBLIC'
Tom Kyte
August 26, 2010 - 1:28 pm UTC

ops$tkyte%ORA11GR2> desc t
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 DT                                                DATE
 VAL                                               NUMBER

ops$tkyte%ORA11GR2> select * from t where ora_dict_obj_type is not null;

no rows selected



ora_dict_obj_type is a function - you are calling a function from sql.

delete from

zhang yongsen, November 07, 2010 - 6:38 am UTC

SQL> delete from dual;

1 row deleted.

SQL> delete from dual;

1 row deleted.

 this  is  wrong .    my  oracle 10g
when i execute  delete from dual;  ,the data all deleted!!!!!!!!!!!!!!!       why



Tom Kyte
November 08, 2010 - 7:38 am UTC

do not delete from dual - that would be "not smart". dual is special, dual is magic - don't touch it for you will break a billion other things that rely on it.


read this page, it is all about dual being "magic", leave SYS and SYSTEM things alone.

delete from dual ..

Sokrates, November 08, 2010 - 8:02 am UTC

should raise an
ORA-xxxx delete from dual not allowed

exception, because 1 billion other things rely on that

I wonder why it doesn't
Tom Kyte
November 08, 2010 - 8:47 am UTC

because sys and system are special, private, ours and you shouldn't be messing with them anyway.

You have to be logged in as sys to even attempt to do what this person was attempting to do (a DBA would get:

ops$tkyte%ORA11GR2> delete from dual;
delete from dual
            *
ERROR at line 1:
ORA-01031: insufficient privileges


so right from the get go, you are using magic (SYS) to work magic on a magic table.

A billion other things depend on sys.obj$ being "not mangled" too - but we have to allow for operations to take place on these tables - by SYS the magical guy - for upgrade, changes, whatever.


Just like root can do:

rm -rf *

from "/"


Sokrates, November 08, 2010 - 8:48 am UTC


Index on DUAL

Parthiban Nagarajan, January 07, 2011 - 4:50 am UTC

Hi Tom
In "Optimizing Oracle Performance" book, it is mentioned that creating an index on the DUAL table improved the performance of Oracle 8 (or 8i) system to a great extent.
Now we are in 11g. But still, I would like to know about your thoughts/experiences about this one.
(©) So, does it mean that those systems used CBO instead of RBO while accessing DUAL?
Thanks in advance, Tom.
Tom Kyte
January 07, 2011 - 9:25 am UTC

I cannot suggest nor sanctify creating an index on dual - never touch the SYS schema.

In 10g and above, dual is a magic table - if you select sysdate from dual, the plan would be:

ops$tkyte%ORA11GR2> select sysdate from dual;

SYSDATE
---------
07-JAN-11


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        426  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed



no IO whatsoever - a new "fast dual" is a magic table that doesn't do any IO.

Just don't test with "select * from dual" - if you select the actual data from dual - we'll actually read the table and incur IO. This is OK because no one actually selects dummy from dual in real life (and if they do - it is their own fault that there would be IO - you never need to do that)


RE: Index on DUAL

Parthiban Nagarajan, January 07, 2011 - 9:37 am UTC

Hi Tom

Some mis-interpretation. Sorry if I had framed my sentences incorrectly.
Yes, I know about FAST DUAL since 10g.
But, I am talking only about 8 (or 8i) Oracles, as mentioned in the book.
As per the book, they created the index on dual to improve the performance. So, does it mean that those 8 (or 8i) systems used CBO instead of RBO while accessing DUAL? And, how far it could have improved the performance.
Thanks for the reply, in advance.
Tom Kyte
January 07, 2011 - 9:52 am UTC

I will not condone nor discuss putting an index on dual - never.

Why FAST DUAL wants to be slow?

Paresh, June 12, 2011 - 1:06 am UTC

Hi Tom,

My apologies in advance for asking a question after reading every word of this loooo..ng thread on Dual :)

Since Oracle code knows that there is one and only one value (one row, one column) 'X' in dual why bother accessing the actual dual table even for

1) select * from dual
and
2) select dummy from dual?

Why not just return 'X' and avoid consistent gets altogether (which FAST DUAL doesn't do)?

Thanks,
Paresh
Tom Kyte
June 17, 2011 - 10:05 am UTC

because there is no reason for you to ever select DUMMY from dual - and DUMMY could always be updated to some other value.

If you access the column in dual, we go to the table.

If you do not, we do not access the table.


If you want 'X' - just select 'X' from dual, it'll be 'fast dual'

Indexing the dual table

A reader, July 13, 2011 - 11:49 am UTC

I was reading this Cary Millsap article on index, http://www.hotsos.com/e-library/abstract.php?id=5, and I was surprised to see this statement in the footnote on page 2.

-------------------------------------------------------------------------
In Oracle8i, we have found that creating and using an index on sys.dual significantly reduces the number of LIOs required to select its single row. We have observed about a 10:1 associated improvement in response time for queries of dual. If your application makes, for example, millions of LIO calls per day against one-row reference tables, then our research suggests that you can conserve millions of LIOs per day and use about 90% less CPU for those queries by indexing them.
-------------------------------------------------------------------------

Is this statement still true in Oracle 11g? Even in 8i, is it a good idea to index a sys table?

Tom Kyte
July 15, 2011 - 8:40 am UTC

It would not be true in 10g and above at all - since the introduction of FAST DUAL:

ops$tkyte%ORA11GR2> select sysdate from dual;

SYSDATE
---------
15-JUL-11


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        426  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte%ORA11GR2> 


I cannot support indexing DUAL in any release of Oracle however - I would not recommend it. If you want - you can create your OWN 'dual like table' (i'd call is "my_dual" or something) as an IOT (index organized table) and put one row in it and use that instead of DUAL.

query on DUAL table during nomount, mount and open stages

Mahendra, August 05, 2011 - 7:21 am UTC

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  369098752 bytes
Fixed Size                  1249056 bytes
Variable Size              71303392 bytes
Database Buffers          293601280 bytes
Redo Buffers                2945024 bytes
SQL> select * from dual;

ADDR           INDX    INST_ID D
-------- ---------- ---------- -
0366CD54          0          1 X

SQL> select rowid from dual;
select rowid from dual
                  *
ERROR at line 1:
ORA-02031: no ROWID for fixed tables or for external-organized tables


SQL> alter database mount;

Database altered.

SQL> select * from dual;

ADDR           INDX    INST_ID D
-------- ---------- ---------- -
0366CD54          0          1 X

SQL> select rowid from dual;
select rowid from dual
                  *
ERROR at line 1:
ORA-02031: no ROWID for fixed tables or for external-organized tables


SQL> alter database open;

Database altered.

SQL> select * from dual;

D
-
X


Q. Is dual a fixed or normal table ?
It looks that when database is not opened it is fixed table.. and when database is opened it is normal table...
where the fixed dual table(with 3 columns) goes after database is opened? Is it replaced by single column dual table?
Can you please throw some light on it.

Tom Kyte
August 05, 2011 - 8:27 am UTC

a) dual is a magic table.

most of the times, dual should just be "fast dual"


ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select sysdate from dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

ops$tkyte%ORA11GR2> select dummy from dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------



The fast dual (since 10g) does NO IO, it just returns from memory.

The non-fast dual actually queries the physical table.



The fixed table dual exists before the database is open for compatibility with tools that need it when the database isn't open (like RMAN). The public synonym for DUAL "the table" overrides it when we have the database open.


A quirk - but nothing to really care about, it shouldn't really affect you at all.

why select * from dual is blocking other sessions?

Raghuveer, September 08, 2012 - 8:44 am UTC

Hi TOM,

can you please tell me , why select * from dual is holding lock? as per my knowledge a pure select statement neither puts lock, nor worries about existing lock.

Thanks In Advance...
Tom Kyte
September 10, 2012 - 8:14 pm UTC

give me an example of select * from dual blocking anyone, show the numbers.

Lock on Dual

Jim, September 11, 2012 - 5:58 pm UTC

I don't see how a SELECT * FROM DUAL can be locking it.
Unless someone is doing something dodgy such as having sometthing like a FOR UPDATE included.

eg
SELECT * FROM DUAL
FOR UPDATE OF DUMMY;

This would be a very bad idea to do and
would cause a lock on the DUAL Table.
"SELECT *" is also considered not a good idea and should almost never be found in production code.



select * from dual generates redo and it changes

doug, September 12, 2012 - 1:59 am UTC

Hi Tom

I am using single user system

I am selecting from dual with autotrace on

1st thing I noticed was I had redo generated,
why would that be?

I ran it again and redo was the same, run it a 3rd time and redo reduces, run it a 4th time and redo is back at the 1st value

Could you explain whats happending please


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set autotrace on
SQL> select * from dual;

D
-
X


Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
        108  redo size
        485  bytes sent via SQL*Net to client
        437  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> /

D
-
X


Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
        108  redo size
        485  bytes sent via SQL*Net to client
        437  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> /

D
-
X


Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
         64  redo size
        485  bytes sent via SQL*Net to client
        437  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> /

D
-
X


Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
        108  redo size
        485  bytes sent via SQL*Net to client
        437  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 

Tom Kyte
September 14, 2012 - 4:38 pm UTC

do you have any auditing going on or anything like that?

that would be yes

A reader, September 16, 2012 - 7:31 pm UTC

Hi Tom,
I am some what embarassed to say I forgot to check for that since it is a development database in fact it is one of the prebuilt Oracle VM's Developer Day.

Thankyou!

why select * from dual is blocking other sessions?

raghuveer, October 03, 2012 - 1:56 pm UTC

Hi TOM,

i was posted question regarding this on SEP 08 , in this same thread. you asked me to give an Example..

I have been waiting for the perfect real time scenario because of that, i was delayed these many days. really sorry for the delay.

following was case occurred
------------------------
Holding Session
===============

SESS INST_ID ID1 ID2 LMODE REQUEST TY BLOCK
------------- ------- ---------- ---------- ---------- ---------- -- ----------
Holder: 771 1 9109544 16254 6 0 TX 1
Waiter: 1084 1 9109544 16254 0 6 TX 0


Session Details
===============
Enter value for sid: 771
Enter value for spid:
Enter value for clientpid:

Session Id.............................................: 771
Serial Num..............................................: 19440
User Name ..............................................: APPS
Session Status .........................................: INACTIVE
Client Process Id on Client Machine ....................: *67645*
Server Process ID ......................................: 26339
Sql_Address ............................................: 0000000355826028
Sql_hash_value .........................................: 4009093839
Schema Name ..... ......................................: APPS
Program ...............................................:
Module .................................................: GYYUR
Action .................................................: FRM: Customer
Terminal ...............................................:
Client Machine .........................................: xxrvulrp
LAST_CALL_ET ...........................................: 19208
S.LAST_CALL_ET/3600 ....................................: 5.33555555555555555555555555555555555556


SQL Text Details
=================
Enter value for hash: 4009093839
Enter value for addr: 0000000355826028

0 SELECT USERENV('LANG') FROM DUAL

Wait Event Details
==================
Enter value for sid: 771

771 SQL*Net message from client



Tom Kyte
October 09, 2012 - 11:57 am UTC

the holding session is inactive, it holds some other lock, the select from dual has nothing to do with anything, it just happens to be the last sql they issues.


i would suspect that if the holding session has been idle for over 5 hours - this is due to a bug in your application server bug.

Someone has leaked a connection and that connection has an incomplete (uncommitted) transaction associated with it.

this is not a database problem, this is a bug in the developed code.


why select * from dual is blocking other sessions?

A reader, October 14, 2012 - 3:19 am UTC

Hi TOM, i am very Thankful to you and Your answer.

So you mean select userenv('LANG') from dual was the last sql query in that abounded TX, and actual lock was made by some other sql statement in the same TX, which was fired before to that select statememt, right?
Tom Kyte
October 15, 2012 - 10:27 am UTC

yes

Invalid table name DUAL

LC, December 11, 2012 - 4:47 am UTC

Hi Tom,

Look at this, inserting rows with one request :

INSERT INTO tablename1 ( column1, colum2 ... )
SELECT alias1, alias2 ...
FROM ( SELECT 'value1' alias1, 'value2' alias2 ...
       FROM dual
       UNION
       SELECT 'value3', 'value4' ... FROM dual
       UNION
       SELECT 'value5', 'value6' ... FROM dual
       UNION
       ...
)
WHERE NOT EXISTS (
    SELECT alias1 FROM tablename1
    WHERE column1 = alias1 )


-- column1 is a primary key

The request works fine, but not on all environments (batch launched), I got an "ORA-00903: invalid table name". May the DUAL table be interpreting differently depending what is performing the request?
Tom Kyte
December 17, 2012 - 2:54 pm UTC

this is a horrible approach - you should use a single insert:

insert into tablename1( c1, c2, ... ) select :c1, :c2, :c3, ... ) from dual where not exists ( select null from tablename1 where c1 = :c1 );


and array bind against that, do not submit big literal sql with all of those constants in it.

please use UNION ALL as well whenever you can, union = Unique(a+b+c...), union all = (a+b+c... ) - there is a big old distinct operation on UNION.

you give insufficient data to answer your question since this syntax is "legal"

ops$tkyte%ORA11GR2> create table t ( c1 varchar2(10), c2 varchar2(10) );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t ( c1, c2 )
  2  select a1, a2
  3    from (select 'v1' a1, 'v2' a2 from dual union all
  4          select 'v2' a1, 'v4' a2 from dual
  5             )
  6   where not exists ( select null from t where c1 = a1 )
  7  /

2 rows created.




what is dual table

rohan, August 22, 2013 - 4:46 pm UTC

only one column why not 2 as the name suggests?
Tom Kyte
August 28, 2013 - 6:16 pm UTC

it is just a historical legacy name from version 2 of Oracle starting in 1979.

and it doesn't always have just one column :)


[tkyte@dellpe ~]$ sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 28 14:09:57 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

idle> startup mount;
ORACLE instance started.

Total System Global Area 5044088832 bytes
Fixed Size                  2237048 bytes
Variable Size            1023413640 bytes
Database Buffers         4009754624 bytes
Redo Buffers                8683520 bytes
Database mounted.
idle> select * from dual;

ADDR                   INDX    INST_ID D
---------------- ---------- ---------- -
000000000A0C9070          0          1 X

idle> alter database open;

Database altered.

idle> select * from dual;

D
-
X

idle>



it is just a magic one row table.

Hi tommy

tommy, May 15, 2014 - 8:20 am UTC

Hi tommy

type of dual table.

manoranjan, May 25, 2015 - 9:27 am UTC

hi Tom. I want to know type of DUAL table. In other words can you tell me which type of table is DUAL from all types of table in oracle?

Dual Table

Bharat, June 27, 2017 - 6:47 am UTC



Its too Good ans. for not only the single question but its given the ans of sub question..

Thank You,
Bharat

how dual table converts data types

bhavanapriyanka, October 27, 2017 - 9:03 am UTC

how dual table converts its datatypes. because it has column datatype as varchar(1).
Could you please elaborate dual table.
and how data will access dual table
Connor McDonald
October 27, 2017 - 2:32 pm UTC

DUAL does not convert anything. It is simply equivalent to a (fast) 1 row table

select 'STRING' from dual
select date '2010-01-01' from dual
select 1235 from dual

is just querying various datatypes from a one-row table

SE:LECT * FROM DUAL NOT WORKING ON 11g

A reader, December 02, 2017 - 2:36 am UTC

Hi Tom,
I found your article helpful but i am having trouble in selecting from dual.

SELECT * FROM DUAL is not working in oracle 11g, this is the error message
SQL Error: ORA-02201: sequence not allowed here
02201. 00000 - "sequence not allowed here"
*Cause: An attempt was made to reference a sequence in a from-list.
*Action: A sequence can only be referenced in a select-list.

hope you can answer me thanks
Chris Saxon
December 04, 2017 - 10:19 am UTC

Uh-oh, looks like someone has been messing around with your dual table...

What is the output of the following query on your database:

select owner, object_type 
from   dba_objects
where  object_name = 'DUAL';

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.