dual table

Hi!

DUAL is a table automatically created by Oracle 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.


Columnt length doesn't affect on value you choose from table.
You can select from any your table any constant value.

For example:
--------
create table test( a varchar(1))
/

select sysdate from test;
select user from test
etc ....

--------


Best regards!
Yaroslav.

1) dual table is a sysnonym or double for all imagined tables.

2) Usually we select from dual to see the effect of functions or to use the value(s) selected for column(s) of other table(s).

3) Since data of any type can be used in quotes as varchar2 type and on concatination everything turns into a varchar2 type.

4) One value X represents that any imaginary table of any symmetry/dimensions can be expanded from this.

5) As kretzschmar says it is a imaginary table with a dummy column and value which can represent any number of real tables with any number of columns and rows.

desc dual;

Name     Null?    Type
-------- ------ -----------
DUMMY           VARCHAR2(1)

select * from dual;

D
-
X

6) We can insert, update, delete from dual and we can alter and drop dual. But all these are not recommended since it is part of data dictionary.

7) there will be no effect to it's functionality as long as it exists i.e. table dual in sys with dummy varchar2(1) as a minimum.

8) So it's functionality is to act as a virtual table and change shape like a chameleon so that select query's work against it as required.

All the best!

--- k_murli_krishna

Yaroslav, a comment would have been better. But I must accept that you are a bigger expert and have helped me once in a while.

--- k_murli_krishna

Yaroslav, a comment would have been better. But I must accept that you are a bigger expert and have helped me once in a while.

--- k_murli_krishna

bhombal,
   I'm not sure what more you're looking for.  DUAL is a table in SYS (as you noted) and there is a public synonym created when the database catalog is built.  It's no different from any other table - there's no magic or internal mechanism associated with it.

   If anyone is doubtful, try creating a table called FRED with a single column called BARNEY varchar2(1) and insert a single record into it "insert into fred value ('?');".  Then try any "DUAL" sql statement you have with FRED instead of DUAL.  You'll see it works the same.

   DUAL was created to have a standard name for solving a problem with relational/SQL implementations - the handling of a single value constant, or anything that didn't require processing against a table.  For example, SQL can calculate the equation "132.33/44+5", but unless there's a table you can select from, there's no records returned.  DUAL was created to be a single-row table (the column is never referenced and is unimportant) that would give you the record you need.

   One of the most maddening debugging problems for early Oracle programmers was finding out why they got results that were double or got multiple-rows returned or twice the result set.  The problem was that someone had done a full import and added a second record to the DUAL table.

   You should also note that some newer SQL implementations have solved this by not requiring a FROM clause when the value is a constant, or non-table based single value.

Good luck!

There isn't any fancy mechanism for it.

I could just as easily select SYSDATE from emp, customer, whatever. However is do SELECT SYSDATE from EMP, I will get one record of sysdate for each record in the table.

The importance is that there is ALWAYS one and only one record in the table.  

It's main purpose is to allow the selection of functions (such as SYSDATE, sequence_name.nextval, sequence_name.curval, etc.) without specifying a "real" table.  (Dual IS a real table, but I mean a non-dummy table).

If you used:

select sysdate
from customer
where rownum = 1; in one instance, and

select sysdate
from emp
where rownum = 1 in another instance,

you'd be getting SYSDATE for both (though at different times!)...but the usage might cause the casual observer to make incorrect assumptions about the nature of the data in either of these tables.

So don't go thinking that DUAL is some really clever structure that is capable of deriving dates, and complex functions...because all it really is is a dummy table.

But the use of DUAL indicates that these activities are taking place.  

...

but now that I look at the other experts comments (oops) I realise that I am simply restating what they've already said...

regards,
JT

Actually, there is one common misconception about DUAL regarding its origin. It was not created as a convenient single-row table to generate derived, non-static data.  It was originally created as an internal-only table to build data dictionary views on.  The programmer (can't remember his name) was looking for a way to create twice the number of rows in a table by doing a simple join on DUAL.  The results were something similar to this:

SELECT *
FROM emp
UNION ALL
SELECT *
FROM emp;

This was way back in the very early versions of Oracle, so they were trying to find a way to do something simple when the functionlity to do it easier ways wasn't there yet.  It turned out to be a handy table, so they kept it and created a public synonym for it to open the use up to everybody.


Andrew

Nice one Andrew!
Regards,
JT

PS Was it always in the SYS schema?  I was once told that one should not select from SYS.DUAL, rather select simply from DUAL.

I believe the reasoning was that there was a good chance that the DUAL table was going to be moved to another schema -- and since the synonym and grants would be suitably applied, using SYS.DUAL was dangerous.

Good point or load of malarkey?

JT,
<.02>
  Oracle has always warned against referencing SYS or SYSTEM objects specifically.  I never took it too seriously because I could always create one.  A more credible warning was that they might change the structure of the table, so we should never reference the column (DUMMY) or assume we knew the value of that one row.

  Also, in version 3 (when I started using Oracle), the recursive sql required by synonyms was considered wasteful (and on a 4Mhz VAX, you needed to be efficient).  It was generally accepted practice to create your own DUAL table (although DUMMY and ANSWER were more common names) so that you could be sure that it existed.  By version 5, this practice had fallen out of favor (DUAL had stayed and the duplicate-record problem I mentioned had bitten a lot of people).

</.02>

Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
points to be given to asimkovsky
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

anand_2000v
EE Cleanup Volunteer