Character of DUAL:
DUAL is owned by SYS. SYS owns the data dictionary, therefore DUAL is part of the data dictionary. You are not to modify the data dictionary via SQL ever.
Dual table and its purpose:
Dual is just a convenience table. You don't need to use it, you can use anything you want. The advantage to dual is the optimizer understands dual is a special one row, one column table -- when you use it in queries, it uses this knowledge when developing the plan.
The Dual table structure, field and record details are below.
SQL> desc dual;
Name Null? Type
-------------- -------- ----------------------------
DUMMY VARCHAR2(1)
SQL> select * from dual;
D
-
X
SQL> select count(*) from dual;
COUNT(*)
----------
1DUAL is owned by SYS:
The below scripts connect to DB as SYSTEM user and try to modify the DUAL table. The insert statement is not able to modify the table which is owned by SYS user.
SQL> conn system/password@oraprc
Connected.
SQL> INSERT INTO DUAL VALUES ('X');
INSERT INTO DUAL VALUES ('X')
*
ERROR at line 1:
ORA-01031: insufficient privileges
We change the connection to SYS user and try the same and it is able to insert the data.
SQL> conn sys/password@oraprc as sysdba
Connected.
SQL> INSERT INTO DUAL VALUES ('X');
1 row created.
SQL> select count(*) from dual;
COUNT(*)
----------
2
Special one row:
The definition has mentioned that DUAL is special one row, one column table. But we can insert the records in that table. So the count got changed in that.
SQL> INSERT INTO DUAL VALUES ('X');
1 row created.
SQL> select count(*) from dual;
COUNT(*)
---------
3
SQL> select * from dual;
D
-
X
The reason is that, the optimizer understands dual is a magic, special 1 row table. It stopped on the select * because there is to be one row in there. It’s just the way it works.
Delete operation on DUAL:
SQL> select count(*) from dual;
COUNT(*)
----------
3
SQL> delete from dual;
1 row deleted.
SQL> select count(*) from dual;
COUNT(*)
----------
2
SQL> delete from dual;
1 row deleted.
SQL> select count(*) from dual;
COUNT(*)
----------
1
The delete statement is deleting single record each time. The reason is that, the DUAL is special one row table.
Checking with FUCTION:
We create the function which return the value “1” and check the same function with DUAL and user created table.
SQL> select count(*) from dual;
COUNT(*)
----------
2
SQL> create or replace function foo return number
2 as
3 x number;
4 begin
5 x:=1;
6 return 1;
7 end;
8 /
Function created.
SQL> create table emp
2 ( num int);
Table created.
SQL> insert into emp values(1);
1 row created.
SQL> insert into emp values(2);
1 row created.
SQL> insert into emp values(3);
1 row created.
SQL> insert into emp values(4);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from emp;
NUM
----------
1
2
3
4
SQL> select foo from dual;
FOO
----------
1
SQL> select foo from emp;
FOO
----------
1
1
1
1
The EMP table is having the 4 records and it is returning the 4 records when do the select on that table. We have checked the same thing with the DUAL table. The count of the DUAL is 2 and it is returning one record.
