pseudo columns & DUAL table - what do they actually mean?

Question

Dual table is used to select pseudo columns. it has one row and one column DUMMY which has a value X.

I have two questions

1. What actually does a pseudo column mean?

2. How is the dual able to give the value for example:

   select sysdate from dual
   

   will result in current datetime. How is this possible?

Answer 1

A pseudo-column is a function which returns a system generated value. sysdate is a function which returns the current datetime; rownum is a pseudo-column that returns the row number in a result set.

The nomenclature dates from the earlier days of Oracle, before we had PL/SQL. It just means that we can use these functions in the projection of a SELECT statement, just like the columns of a table. Nowadays we can write our own functions and use them in SQL statements without blinking, and so the phrase "pseudo-column" is a tad confusing.

The feature which distinguishes a function from a pseudo-column is that the pseudo-column returns a different value for each row in the resultset whereas a function returns the same value (unless some column in the table is passed as a parameter to derive the value).

Dual is another venerable slice of Oracle history. It is a table which contains one row, and which the database knows contains one row. So the select statement you quote is just saying "give me the current datetime". It is functionally equivalent to

select sysdate 
from emp
where rownum = 1
/

In PL/SQL the select from dual is nugatory. We can just code this:

l_date := sysdate;

One common use for DUAL used to be getting the next value of a sequence in a trigger. Since 11g we can do ...

:new.id := my_seq.nextval;

Under the covers this still executes select my_seq.nextval into :new.id from dual;

Answer 2

A pseudo-column is an Oracle assigned value (pseudo-field) but not stored on disk.

Pseudocolumns are not actual columns in a table but they behave like columns.

For example, you can select values from a pseudocolumn. However, you cannot insert into, update, or delete from a pseudocolumn. Also note that pseudocolumns are allowed in SQL statements, but not in procedural statements.

NOTE:- pseudocolumns are allowed in SQL statements, but not in procedural statements.

SQL> SELECT sysdate, systimestamp FROM dual; SYSDATE SYSTIMESTAMP

---

13-DEC-07 13-DEC-07 10.02.31.956842 AM +02:00

Answer 3

pseudo Columns : ROWID , ROWNUM , LEVEL

Answer 4

2. How does selecting from DUAL give the system time?

SQL has a number of built-in functions which don't need parentheses after them to invoke them. One such function in Oracle is SYSDATE.

Remember, if you have a table, a SELECT statement with no restriction condition (WHERE clause) normally returns one row of data for each row in the table. So, given a table:

CREATE TABLE Ex1(Dummy CHAR(10) NOT NULL);
INSERT INTO Ex1 VALUES('Abacus');
INSERT INTO Ex1 VALUES('Sedentary');
INSERT INTO Ex1 VALUES('Caucasus');

Running the SELECT statement:

SELECT Dummy FROM Ex1;

will return 3 rows. Now, suppose I write the statement as:

SELECT 'ABC', Dummy, SYSDATE FROM Ex1;

This will also return 3 rows:

• ABC, Abacus, 2010-03-03
• ABC, Sedentary, 2010-03-03
• ABC, Caucasus, 2010-03-03

If I omit the Dummy column:

SELECT 'ABC', SYSDATE FROM Ex1;

I get:

• ABC, 2010-03-03
• ABC, 2010-03-03
• ABC, 2010-03-03

And if I omit the string literal:

SELECT SYSDATE FROM Ex1;

I get:

• 2010-03-03
• 2010-03-03
• 2010-03-03

And I delete two rows and rerun the query, I get:

DELETE FROM Ex1 WHERE Dummy > 'B';
SELECT SYSDATE FROM Ex1;

I get:

• 2010-03-03

Because there's just the one row of data in the table Ex1.

Nominally, I could do:

 UPDATE Ex1 SET Dummy = 'X';
 RENAME TABLE Ex1 AS Dual;

Of course, you can't do that - I'm not sure whether Oracle supports a RENAME TABLE statement and it probably wouldn't let you rename your table so it could be confused with the built-in DUAL table. But conceptually, the table Ex1 with a single row in it is isomorphic with DUAL.

1. What is a Pseudo-Column?

Unless Oracle has a specific special meaning for the term, then a pseudo-column is a column that appears to be part of the table but that is not actually stored as data in the table. A classic example is a row number:

SELECT ROWNUM, * FROM SomeTable

The output appears to have a column ROWNUM (ROWID in Informix, with which I'm most familiar) but that is not directly stored in the DBMS. Different DBMS have other different pseudo-columns, for different purposes.

It is sometimes difficult to distinguish between a pseudo-column and a function.