Internal Functionality of DUAL table?

Question

Will local database get disturb if we create DUAL table ?

Kindly Suggest me ?

create table DUAL
(
      x varchar2(1)
);

Answer 1

No you cannot create a dual table. DUAL table is owned by SYS and SYS owns the data dictionary so you can not create it.

See the wiki

The DUAL table is a special one-row table present by default in all Oracle database installations. It is suitable for use in selecting a pseudocolumn such as SYSDATE or USER. The table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'.

Even if you try to create a DUAL table then it will create problems for you as everytime the Oracle engine has to ensure that you are not calling the SYS dual table. You need to specify the database and schema as well. It may lead to too much of ambiguity problem for Oracle engine. The Oracle optimizer knows everything that DUAL does and what it should do and it then does things based on that.

SQL Reference:

DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table. Refer to "About SQL Functions" for many examples of selecting a constant value from DUAL.

Beginning with Oracle Database 10g Release 1, logical I/O is not performed on the DUAL table when computing an expression that does not include the DUMMY column. This optimization is listed as FAST DUAL in the execution plan. If you SELECT the DUMMY column from DUAL, then this optimization does not take place and logical I/O occurs.

Answer 2

Will local database get disturb if we create DUAL table ?

Yes, of course weird things can and will happen. 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.

And the first question is "How will you guarantee only one row in your own DUAL table"?

This goes back to the original article Self-Managing PL/SQL by Steven Feuerstein where he explains "Use Your Own DUAL Table". But, that was back then when DUAL table was prone to such things.

However, in the recent releases, the DUAL table structure has been made robust and you cannot have more than single row ever. Here is a proof:

SQL> conn sys@pdborcl as sysdba
Enter password:
Connected.
SQL> insert into dual select * from dual;

1 row created.

SQL> select * from dual;

D
-
X

I know, few would argue that we can handle one row with our own DUAL table using a trigger or ROWNUM =1, however, you will soon realize the cons. It is simply not necessary from 10g on wards, as the DUAL table is now a memory structure and you cannot add a row to it as demonstrated above.

Imagine a situation where you have created your own DUAL table, and you are using the call to DUAL table in your PL/SQL code to get the USER, SYSDATE, SYSTIMESTAMP etc.

This is the code taken from the stdbody.sql file delivered with Oracle Database:

1 FUNCTION USER
 2   RETURN VARCHAR2
 3 IS
 4   c VARCHAR2 (255);
 5 BEGIN
 6   SELECT USER
 7    INTO c
 8    FROM SYS.DUAL;
 9
10   RETURN c;
11 END;

If you ever have more than one row in your own DUAL table, every call to the USER function in your PL/SQL code will fail with TOO_MANY_ROWS error.

Bottomline : All the discussion about using your own DUAL table made sense back then before 10g days. The DUAL table is now a robust memory structure and doesn't allow to add a row to it. So, makes no sense to use your own DUAL table rather than the SYS.DUAL.