PHP Oracle – Selecting from the DUAL Table

PHP Oracle – Selecting from the DUAL Table

DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the nameDUAL 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. Refer to “About SQL Functions” for many examples of selecting a constant value from DUAL.

Note:

Beginning with Oracle Database 10g Release 1, logical I/O is not performed on the DUAL table when computing an expression that does not include the DUMMY column. This optimization is listed as FAST DUAL in the execution plan. If you SELECT theDUMMY column from DUAL, then this optimization does not take place and logical I/O occurs.

The dual table “works” almost just the way any other table works: it is a table from which you can select records.

This means, for example, you can describe the table. Here, in SQL*Plus:

So, the table has one column, named dummy which is a varchar2(1).

The table has, by design, one record (at least if nobody fiddled with it):

So, in order to get the same behaviour with dual2 as you have with dual, you have to insert one record into dual. Better yet, create it with a create table as select (ctas):

Now, your query works:

Earlier, I said that dual almost works like any other table. So, when does it not work like any other table?

It behaves differently, if no value from the table itself is selected. Again, with your queries, I let Oracleexplain them …

… in order to see how the table is accessed:

It can be seen that the statement does a full table access on dual2.

Now, same thing with dual:

This is where the dual table behaves differently: the value of dummy is not needed, so a fast dualoperation is executed, in order for the instance not to read the actual value on the disk.

 

PHP Oracle Blog


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">