Mysterious dual------black.

Title of the Mysterious dual Black_snail (original)

ORACLE DUAL Keywords



DUAL? What's so mysterious about it? When you want Oracle system time, simply hit a line of SQL

Oh, no. It's so mysterious ....

Sql> select Sysdate from dual;

Sysdate

---------

28-sep-03



Haha, indeed dual is very convenient to use. But you know what dual is the object of the bottom, does it have any special behavior? Come, let's have a look.



First figure out what dual is:

Sql> Connect System/manager

Connected.

Sql> Select owner, object_name, object_type from dba_objects where object_name like '%dual% ';



OWNER object_name object_type

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

SYS DUAL TABLE

Public DUAL synonym



The original dual is a table that belongs to the SYS schema and is then used by public synonym for use by other database users.

And look at the structure of it:

Sql> DESC Dual

Name Null? Type

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

DUMMY VARCHAR2 (1)



Sql>



There is only one character column named dummy.



Then check the data in the table:

Sql> select dummy from dual;

DUMMY

----------

X



Well, there's only one record, dummy's value is ' X '. It's normal, nothing strange. Well, there's something wonderful going on down there!

Insert a record:

Sql> Connect SYS as SYSDBA

Connected.

Sql> INSERT into dual values (' Y ');

1 row created.

Sql> commit;

Commit complete.

Sql> Select COUNT (*) from dual;

COUNT (*)

----------

2

So far, everything is fine. However, when we queried the record again, something strange happened.

Sql> SELECT * from dual;

DUMMY

----------

X

The record just inserted didn't show up! Obviously there are two records in the dual table, but only one is shown!

Try again to delete, ruthless little, all delete the light!

Sql> Delete from dual; /* Note There are no qualifying conditions, try to delete all records * *

1 row deleted.

Sql> commit;

Commit complete.



Haha, only one record has been deleted,

Sql> SELECT * from dual;

DUMMY

----------

Y



Why is that? Does the SQL syntax have no effect on dual? With this in mind, I have queried some Oracle official information. Oracle has done some internal processing of the dual table operation to ensure that only one record is returned in the dual table. Of course, the internal operation is not visible.

It seems that Oracle is a great mystery!



Appendix: Oracle's explanation of the unusual characteristics of the dual table

There is internalized the code that makes this happen. Code checks that ensure

That a table scan of SYS. DUAL only returns one row. SVRMGRL behaviour is

Incorrect but this are now obsolete product.

The base issue you should always remember and keep is:dual table should always

Have 1 ROW. Dual is a normal table with one dummy column of VARCHAR2 (1).

This is basically used from several applications as a pseudo table for

Getting results from a SELECT statement this use functions like sysdate or other

prebuilt or application functions. If DUAL has no rows at all some applications

(that use DUAL) the may fail with no_data_found exception. If DUAL has more than 1

Row then applications (that use DUAL) may fail with too_many_rows exception.

So DUAL should ALWAYS have 1 and only 1 row