Oracle中的dual

Let me just start by saying -- 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.