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
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)
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.
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>
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.
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
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
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
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
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
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?
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
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
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,
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
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 ?
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 !
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.
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!
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.
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.
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.
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!
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.
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 ...)?
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?
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?
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
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.
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.
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?
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
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
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
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
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.
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.
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
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
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
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.
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?.
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
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
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??
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
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
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
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 ?
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
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?
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
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.
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.
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.
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
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?
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!
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 ?
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
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
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.
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.
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
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.
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
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
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
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>
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?
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?
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
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:"?
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?
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
:-)
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 ?
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.
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...
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***
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.
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
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
&#931;&#969;&#954;&#961;&#940;&#964;&#951;&#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
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.
;-)
&#931;&#969;&#954;&#961;&#940;&#964;&#951;&#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
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.
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
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
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;
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
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
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 ?
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 ?
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
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
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
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.
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
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
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
?
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
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
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.
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'
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
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
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.
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.
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
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?
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.
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...
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>
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
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?
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?
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?
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
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
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';
Any practical example of DUAL?
Anubhav Apurva, September 26, 2021 - 11:25 am UTC
Jumping here after almost 20 years :)
I understand DUAL is used to select properties like date and evaluate expressions without a need of a physical table.
But are there any more instances where Dual is involved in more complex scenario or query?
Any real life examples?
September 27, 2021 - 7:06 am UTC
1) Multiple inserts with a single call
insert all
into table1 values (1,2,3)
into table1 values (4,5,6)
into table1 values (7,8,9)
...
...
select * from dual
2) Multi-column scalars
select
( select col from table1 where ... ) as c1,
( select col from table3 where ... ) as c2,
( select col6 from table1 where ... ) as c3,
( select col12 from table2 where ... ) as c4,
...
...
from dual
(Yes could also be done without dual)