Dual table in Oracle

I often use dual and forget what it means. I will try again.

Dual? What's mysterious? When you want to get the Oracle system time, simply type a line of SQL

Isn't it? To learn more ....

SQL> select sysdate from dual;

Sysdate

---

28-sep-03

Haha, it is indeed very convenient to use dual. But do you know what the dual is and what special behavior it has? Let's take a look.

First, we need to 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 in SYS schema and is then used by other database users in public synonym mode.

Let's look at its structure:

SQL> DESC dual

Name null? Type

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

Dummy varchar2 (1)

SQL>

There is only one empty column named dummy.

Query the data in the table:

SQL> select dummy from dual;

Dummy

----

X


Oh, there is only one record. The dummy value is 'x'. It's normal. It's no surprise. Well, there's something amazing coming up!

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 works. But when we query records again, something strange happened.

SQL> select * from dual;

Dummy

----

X

The record just inserted is not displayed! The dual table has two records, but only one record is displayed!

Try to delete the file. Click "delete" to delete all files!

SQL> Delete from dual;/* try to delete all records without restrictions */

1 row deleted.

SQL> commit;

Commit complete.

Haha, only one record is deleted,

SQL> select * from dual;

Dummy

----

Y

Why? Does SQL syntax not work for dual? With this question, I found some official Oracle documents. oracle performed some internal operations on the dual table to ensure that only one record is returned in the dual table. of course, this write operation is invisible.

It seems that Oracle is an infinite mystery!

Appendix: Oracle interpretation of the unusual features of dual tables

There is internalized 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 is now an obsolete product.

The base issue you shoshould always remember and keep is: Dual table shoshould 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 that use functions like sysdate or other prebuilt or application functions. if dual has no rows at all some applications (that use dual) 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 shoshould always have 1 and only 1 row