bhombal
asked on
dual table
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.
Please explain in detail.
kretzschmar
dual is a virtual 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.
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
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
--- 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
--- 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!
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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>
<.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 **
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
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
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