Oracle's Dual Table detailed

1, the use of dual table

Dual is an actual table in Oracle that can be read by any user and is often used in a SELECT statement block that does not have a target table

(1) View current connected users

Sql> Select User from dual;

USER

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

SYSTEM

(2) View the current date, time

Sql> select Sysdate from dual;

Sysdate

-----------

2007-1-24 1

Sql> Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

To_char (Sysdate, "YYYY-MM-DDHH2")

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

2007-01-24 15:02:47

(3) used as a calculator

Sql> select 1+2 from dual;

1+2

----------

3

(4) View sequence values

sql> Create sequence AAA increment by 1-start with 1;

Sql> select Aaa.nextval from dual;

Nextval

----------

1

Sql> select Aaa.currval from dual;

Currval

----------

1

2, on the dual table test and analysis

Dual is a row of tables, if you go into the INSERT, delete, truncate operation, will cause a lot of program problems. The results also vary from tools such as Sql*plus, Pl/sql Dev and so on.

--See what dual is Object

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

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

--View table structure, only one field dummy, for VARCHAR2 (1) type

Sql> DESC Dual

Name Type Nullable Default Comments

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

DUMMY VARCHAR2 (1) Y

Structure of the--dual table:

CREATE TABLE SYS. DUAL

（

DUMMY VARCHAR2 (1)

）

Tablespace SYSTEM

Pctfree 10

Pctused 40

Initrans 1

Maxtrans 255

Storage

（

Initial 16K

Next 16K

Minextents 1

Maxextents 505

Pctincrease 50

）；

/*

Is very confused, why Oracle to use varchar (1) type, with char (1) is not good? From this table structure, the dual table design is designed to be as simple as possible to reduce the cost of retrieval.

Also, the dual table is based on the system table space, the first is because the dual table is the SYS user built, the default table space is System; second, it is good for system performance to separate the tables and user tables that may be queried frequently.

It is not enough to create a table and create a synonym. Dual is under the SYS schema, so it is not possible to query this table with another user login, so you also need to authorize:

Grant SELECT on SYS. DUAL to public with GRANT option;

Grant SELECT permissions to the public.

Next look at the data in the dual table, in fact, the data in the dual table has a very important relationship to the Oracle database environment (Oracle will not be paralyzed, but many stored procedures and some queries will not be executed correctly).

*/

--Query number of rows

-After the database is created, a record is inserted in the dual table. Personally think: The value of the dummy field does not matter, it is important that the number of records in the dual table

Sql> Select COUNT (*) from dual;

COUNT (*)

----------

1

Sql> SELECT * from dual;

DUMMY

-----

X

--Insert data, query records, and return only one line of records

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

1 row created.

Sql> commit;

Commit complete.

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

1 row created.

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

1 row created.

Sql> commit;

Commit complete.

Sql> Select COUNT (*) from dual;

COUNT (*)

----------

4

Sql> SELECT * from dual;

DUMMY

-----

X

/*

--We insert a piece of data, dual table is not returned a row, but more than a row of records, what would be the result?

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

1 lines inserted

Sql> commit;

Submit Complete

Sql> SELECT * from dual;

DUMMY

-----

X

Y

Sql> select Sysdate from dual;

Sysdate

-----------

2004-12-15

2004-12-15

This is the time to return two records, which can also cause problems. In by using

Select Sysdate into v_sysdate from dual;

Oracle throws a Too_many_rows (ORA-01422) exception to a stored procedure that obtains time or other information.

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Therefore, it is necessary to ensure that there is only one record in the dual table. Of course, also can not put the dual table Update,insert,delete permission to free out, so for the system is very dangerous

*/

--Cut off the watch.

Sql> TRUNCATE TABLE dual;

Table truncated.

Sql> Select COUNT (*) from dual;

COUNT (*)

----------

0

Sql> SELECT * from dual;

No rows selected

Sql> select Sysdate from dual;

No rows selected

--Try to remove the data from the dual table and see what happens:

Sql> Delete from dual;

1 rows deleted

Sql> SELECT * from dual;

DUMMY

-----

Sql> select Sysdate from dual;

Sysdate

-----------

/*

We could not get the system date. Because, Sysdate is a function that is used for each data row. Now that there is no data, it is not possible to take out the system date.

This is for a lot of use

Select Sysdate into v_sysdate from dual;

This way it is fatal to take system time and other information stored procedures, because Oracle will immediately throw a No_data_found (ORA-01403) exception, even if the exception is caught, the stored procedure will not be able to complete the required action correctly.

*/

--For the delete operation, Oracle does some internal processing of the dual table operation to ensure that only one record is returned in the dual table. Of course, this internal operation is not visible.

--regardless of how many records are in the table (except for records), Oracle deletes only one piece of data for each delete operation.

Sql> Select COUNT (*) from dual;

COUNT (*)

----------

2

Sql> Delete from dual;

1 rows deleted

Sql> commit;

Submit Complete

Sql> Select COUNT (*) from dual;

COUNT (*)

----------

1

/*

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

There is internalized the code that makes this happen. Code checks that ensurethat a table scan of SYS. DUAL only returns one row. SVRMGRL behaviour is incorrect but it now obsolete product.