Solved

dual table

Posted on 2002-04-19
14
23,773 Views
Last Modified: 2012-08-13
In oracle dual table is a table in the schema of SYS. with a column (dummy varchar2(1)). If the column length is 1 then how does it return a value whose length more than 1. What internal mecanism is used for dual table.
Please explain in detail.
0
Comment
Question by:bhombal
  • 3
  • 2
  • 2
  • +6
14 Comments
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
dual is a virtual table
0
 
LVL 3

Expert Comment

by:p_yaroslav
Comment Utility
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.
0
 
LVL 17

Expert Comment

by:k_murli_krishna
Comment Utility
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
0
 
LVL 17

Expert Comment

by:k_murli_krishna
Comment Utility
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
0
 
LVL 17

Expert Comment

by:k_murli_krishna
Comment Utility
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
0
 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
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!
0
 
LVL 4

Expert Comment

by:jtrifts
Comment Utility
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Expert Comment

by:asimkovsky
Comment Utility
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
0
 
LVL 4

Accepted Solution

by:
asimkovsky earned 30 total points
Comment Utility
Here's the excerpt from the February issue of Oracle Magazine (SENDMAIL section):

I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, seemed apt for the process of creating a pair of rows from just one.
Chuck Weiss, Oracle



Andrew
0
 
LVL 4

Expert Comment

by:jtrifts
Comment Utility
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?
0
 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
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>
0
 
LVL 6

Expert Comment

by:Mindphaser
Comment Utility
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 **
0
 
LVL 13

Expert Comment

by:anand_2000v
Comment Utility

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
0
 

Expert Comment

by:new_attitude
Comment Utility
Ok I have read all on this that I could find and I think I get it - with just one question:

i understand it is a way to solve a problem or figure out the best way to lay out a query for dates, mathematical or functions that return a single value.  However, I am somewhat confused and can't seem to come up with an example of utilizing it for a join.  I apologize for my ignorant question - I understand very clearly that it is a single row single column table with one value and completely undederstand for testing out a query but would love some samples of utilizing for a join and then group by (slightly confused about the way it assists for returning useful data in conjunction with another table)

Thank you in advance for your patience.

Susan
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now