shantanu_gg's Journal

all about dual
Source:http://asktom.oracle.com/pls/ask/f?p=4950:8:1201632583865455535::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1562813956388

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 -- wierd things can and will happen --
you are just demonstrating some of them. We can make many strange things happen
in Oracle by updating the data dictionary. It is neither recommend, supported
nor a very good idea.

1.What is the dual table, what is its purpose.

dual is just a convienence 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.

2.Why does it contain only one column with datatype varchar2, why not
number .

truly, why no. Why not a date you would ask then. The column, its name, its
datatype and even its value are NOT relevant. DUAL exists solely as a means to
have a 1 row table we can reliably select from. Thats all.

3.Does it contain one row by default.

yes, when we build the database, we build dual and put a single row in it.

4.why do we
usually SELECT USER FROM DUAL,
why cant I do it like
SQL> select USER FROM EMP WHERE ROWNUM<2;

truly, why can't you? is something preventing you from doing so?? You can if
you want. Me, I'll stick with "select user from dual". I know dual exists. I
know it has at least 1 and at most 1 row. I know the optimizer knows all about
dual and does the most efficient thing for me.

5) yes

6) 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. Its just the way it
works. Hopefully you reset dual back to 1 row after your testing or you just
totally broke your database!

7) like I said, duals magic, the optimizer knows all about what DUAL should be
and does things based on that.

8) dual = magic. dual is a one row table however having more then 1 or less
then one is dangerous. You are updating the data dictionary. You should
naturally expect very bad things to happen.

Ok, here is some trivia for you out there. How did I do this:

SVRMGR> select * from dual;
D
-
X
1 row selected.

SVRMGR> ????????????????????;
Statement processed.

SVRMGR> select * from dual;
ADDR INDX INST_ID D
-------- ---------- ---------- -
01680288 0 1 X
1 row selected.

What was that magic command?

Advertisement