msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Insert into DUAL

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.


Filed in Oracle on 08 Nov 05 | Tags: ,


Reader's Comments

  1. |

    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..

  2. |

    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:

     the letter l is replaced with the number 1

  3. |

    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:

    SQL> create table bar as select * from dual
      2  ;
    
    Table created.
    
    SQL> select * from bar;
    
    D
    -
    X
    A
    
    

    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

  4. |

    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.

  5. |

    Yes, dual is magic – see asktom article

  6. |

    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.

  7. |

    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.

  8. |

    TOAD shows rows inserted in dual without: 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

  9. |

    One hopes that Oracle have marked dual as read-only in 11g now that we have the facility to make tables read-only!