The Oracle dual table

Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Dual is a small table, belonging to the sys but can be used by all users. The user program can refer to the guarantee of a known result. When a value return only once, the dual table is very useful, for example, query the current date and time.
The dual table has only one column and a row of data, data for the " X", as follows:
SQL> desc dual
Name Null? Type
------------------------------------------------- ----------------------------
DUMMY VARCHAR2(1)

SQL> select * from dual;

D
-
X
Following the use of dual to perform an arithmetic operation:
SQL> select 1+2*5 from dual;

1+2*5
----------
11

Be careful:
From the Oracle Database 10g Release1, when the formula does not include the DUMMY column in the dual table, not to perform the logic I/O. If the query DUMMY column, will produce logic I/O.
For example:

SQL> set linesize 132
SQL> set autotrace traceonly
SQL> select 1+2*5 from dual; - expressions, execution plans for FAST DUAL, no logic I/O

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id |Operation | Name | Rows | Cost (%CPU)|Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT| | 1| 2 (0)| 00:00:01 |
| 1 | FASTDUAL | | 1| 2 (0)| 00:00:01|
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistentgets
0 physical reads
0 redo size
523 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select * from dual; - contains a query execution plans for TABLE DUMMY, ACCESSFULL, logic I/O


Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
--------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 1| 2| 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL| 1| 2| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
522 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Eli at December 12, 2013 - 6:02 PM