Pseudo columns and a DUAL table - what do they really mean?

Question

A double table is used to select pseudo columns. it has one row and one DUMMY column, which has an X value.

I have two questions

• What does a pseudo column really mean?

• How a double can give meaning, for example:

  select sysdate from dual 

  will result in the current time and time. How is this possible?

Answer 1

A pseudo-column is a function that returns a system value. sysdate is a function that returns the current datetime; rownum is a pseudo-column that returns the row number in the 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 the SELECT statement, like the columns of a table. Currently, we can write our own functions and use them in SQL operations without blinking, and so the phrase “pseudo-column” is a bit confusing.

The function that distinguishes the function from the pseudo-column is that the pseudo-column returns a different value for each row in the result set, while the function returns the same value (unless any column in the table is passed as a parameter to get value).

Dual is another venerable piece of Oracle history. This is a table containing one row and which, as you know, contains one row. So the select statement you are quoting just says “give me the current time”. It is functionally equivalent.

 select sysdate from emp where rownum = 1 / 

In PL / SQL, choosing from double is unmanageable. We can simply copy this:

 l_date := sysdate; 

One common use for DUAL is to get the next sequence value in a trigger. With 11g we can do ...

 :new.id := my_seq.nextval; 

Under the covers, select my_seq.nextval into :new.id from dual; is still running select my_seq.nextval into :new.id from dual;

Answer 2

2. How does choosing from DUAL give system time?

SQL has a number of built-in functions that do not require parentheses after they are called. One such feature in Oracle is SYSDATE.

Remember that if you have a table, a SELECT statement without a constraint condition (WHERE clause) usually returns one row of data for each row in the table. So, given the 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 expression as:

 SELECT 'ABC', Dummy, SYSDATE FROM Ex1; 

This will also return 3 lines:

• 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 the two lines and re-run the request, I get:

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

I get:

• 2010-03-03

Because there is only 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 cannot do this - I'm not sure if Oracle supports the RENAME TABLE statement, and this probably will not allow you to rename your table so that it can be confused with the built-in DUAL table. But conceptually, the Ex1 table with one row in it is isomorphic to DUAL.

1. What is a pseudo-column?

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

 SELECT ROWNUM, * FROM SomeTable 

The output column appears ROWNUM (ROWID in Informix, with which I am most familiar), but it is not stored directly in the DBMS. Different DBMSs have different pseudo-columns for different purposes.

Sometimes it is difficult to distinguish between a pseudocolonomer and a function.

Answer 3

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 pseudo-column. However, you cannot insert, update, or delete from a pseudo-call. Also note that pseudo-columns are allowed in SQL statements, but not in procedural instructions.

NOTE. - Pseudocolumns are allowed in SQL statements, but not in procedural instructions.

SQL> SELECT sysdate, systimestamp FROM dual; SYSDATE SYSTIMESTAMP

---

13-DEC-07 13-DEC-07 02/10/31.956842 AM +02: 00