On the Oracle-l mailing list, Jared Still posted a message about a little experiment he did which concluded that Oracle (10gR1) always makes sure that DUAL returns one and only one row even if it contains more than one row.
I have repeated the experiment on my Oracle 10g XE database (Important: Do not do this dangerous stuff on your production database. Never insert/delete/update DUAL):
SQL*Plus: Release 10.2.0.1.0 - Beta on Tue Nov 8 19:21:20 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> insert into dual values('A');
1 row created.
SQL> insert into dual values('B');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dual;
D
-
X
Rows A and B are not returned, why?
SQL> disconnect Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Beta SQL> connect hr/hr Connected. SQL> select * from dual; D - X SQL> create table hr_dual as select * from dual; Table created. SQL> select * from hr_dual; D - X A B
Now, A and B are returned if I do not select from DUAL directly.
Now, I will delete rows A and B from DUAL:
SQL> disconn Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Beta SQL> connect sys as sysdba Enter password: Connected. SQL> delete dual where dummy = 'A'; 1 row deleted. SQL> delete dual where dummy = 'B'; 1 row deleted. SQL> commit; Commit complete.
I will make sure that the rows are deleted from DUAL:
SQL> disconn Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Beta SQL> connect hr/hr Connected. SQL> select * from dual; D - X SQL> drop table hr_dual; Table dropped. SQL> create table hr_dual as select * from dual; Table created. SQL> select * from hr_dual; D - X
The rows are deleted from DUAL. Interesting indeed.
Possibly related:
- More on Generating Strings
- Cool Undocumented SQL Function SYS_OP_MAP_NONNULL and Some Alternatives
- The Lazy Developer’s Way to Populate a Surrogate Key
- When Your Bind Variable is Invalid and Your Column is Weird
- Triggers and Constraints
Tagged 10g, dual | Post a Comment
Back in 9i days we got strange exceptions with ejbs in 9i. Finally we got figured it out what was the problem: there were two rows in table dual..
November 8th, 2005, at 10:18 pm #Aye, great way to at least temporarily cripple a database: add rows to dual, or remove all rows from dual. That and update all the VARCHAR2 columns in the database (including source code) so that:
November 9th, 2005, at 5:35 am #Eddie,
I believe the difference is that when you use DUAL in a DDL statement, the fast dual optimizer path is not used. For example, I inserted an extra row into DUAL as you did. Then:
Trace output show that the DDL statement resulted in this explain plan:
Rows Execution Plan ------- --------------------------------------------------- 0 CREATE TABLE STATEMENT MODE: ALL_ROWS 0 LOAD AS SELECT OF 'BAR' 0 TABLE ACCESS MODE: ANALYZED (FULL) OF 'DUAL' (TABLE)Note that full access of the regular DUAL table was used. “Normal” SELECTs against DUAL access use FAST DUAL these days (I believe since 9i):
Rows Row Source Operation ------- --------------------------------------------------- 1 FAST DUAL (cr=0 pr=0 pw=0 time=10 us)Sergio
November 9th, 2005, at 6:56 am #Starting with 10g, Oracle does not do a full table scan on DUAL if it does not need to. I have just published a post about it.
So, following up on Sergio’s comment, it looks like in 10g, when I “select * from DUAL”, the “FAST DUAL” takes over and returns what it wants to return, one row.
November 9th, 2005, at 7:35 am #Yes, dual is magic - see asktom [article](http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:40476301944675#40756986348091)
November 9th, 2005, at 8:31 am #i have a interesting question…
say i do
delete from dual;
1 row deleted;
commit;
commited;
Now,
Select * from dual;
No rows returned;
select sysdate from dual;
No rows returned;
Wont this impact a serious problem in all the process and applications server which uses dual table for one or other purpose….
say now if i restart my database… I cant imagine what will happen….
can anybody say if i’m wrong in any of the things here….
i havent tried this as i dont want to spoil my server if at all there would be a problem.
March 6th, 2006, at 6:34 am #Wont this impact a serious problem in all the process and applications server which uses dual table
Yes. So, my advice to you is to never insert/delete/update DUAL.
March 6th, 2006, at 9:01 pm #TOAD shows rows inserted in dual without:
September 28th, 2006, at 6:32 am #create table hr_dual asl select * from dual;
But I couldn’t get in in SQLPlus without this statement, not even with rownum > 0 as shown on asktom
One hopes that Oracle have marked dual as read-only in 11g now that we have the facility to make tables read-only!
February 22nd, 2008, at 3:33 am #