Is DUAL a real table?

Is DUAL a real table or is the optimizer specially processing DUAL references?

Conclusion: It is a real table. See the following analysis.

At the same time, according to the database creation script $ ORACLE_HOME/RDBMS/ADMIN/dcore. bsq, the optimizer has special processing for this table. There is no detailed description on how to deal with this table.

1. Execution Plan

 
 

  
  
select * from dual;  
  
  
 
  
  
Plan hash value: 272002086  
  
  
   
  
  
--------------------------------------------------------------------------  
  
  
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  
  
--------------------------------------------------------------------------  
  
  
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |  
  
  
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 | 
 
 

2 rowid

 
 

  
  
select dbms_rowid.rowid_object(rowid) obj,  
  
  
       dbms_rowid.rowid_relative_fno(rowid) rfno,  
  
  
       dbms_rowid.rowid_block_number(rowid) bno,  
  
  
       dbms_rowid.rowid_row_number(rowid) rno from dual;  
  
  
        
  
  
OBJ RFNO BNO RNO  
  
  
258 1 2082 0        
  
  
 
  
  
select owner,object_name,object_id from dba_objects where object_id=258;  
  
  
OWNER OBJECT_NAME OBJECT_ID  
  
  
SYS DUAL 258 
 
 

3. dump block

 
 

  
  
alter system dump datafile 1 block 2082;  
  
  
--------------------------------------------------  
  
  
/u01/app/oracle/product/10.2.0/db_1/admin/bocnet/udump/bocnet_ora_557.trc  
  
  
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production  
  
  
With the Partitioning, OLAP and Data Mining options  
  
  
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1  
  
  
System name: Linux  
  
  
Node name: bocnet95  
  
  
Release: 2.6.18-164.el5xen  
  
  
Version: #1 SMP Thu Sep 3 04:47:32 EDT 2009  
  
  
Machine: i686  
  
  
Instance name: bocnet  
  
  
Redo thread mounted by this instance: 1  
  
  
Oracle process number: 50  
  
  
Unix process pid: 557, image:oracle@bocnet95(TNS V1-V3)  
  
  
 
  
  
*** SERVICE NAME:(SYS$USERS) 2011-05-29 04:54:44.485  
  
  
*** SESSION ID:(135.9121) 2011-05-29 04:54:44.485  
  
  
Start dump data blocks tsn: 0 file#: 1 minblk 2082 maxblk 2082  
  
  
buffer tsn: 0 rdba: 0x00400822 (1/2082)  
  
  
scn: 0x0006.c428013f seq: 0x01 flg: 0x04 tail: 0x013f0601  
  
  
frmt: 0x02 chkval: 0x89a3 type: 0x06=trans data  
  
  
Hex dump of block: st=0, typ_found=1  
  
  
Dump of memory from 0x0D6D0400 to 0x0D6D2400  
  
  
D6D0400 0000A206 00400822 C428013F 04010006  [....".@.?.(.....]  
  
  
D6D0410 000089A3 00000001 00000102 A514474A  [............JG..]  
  
  
D6D0420 00000001 00030002 00000000 00200009  [.............. .]  
  
  
D6D0430 00003DF2 00820CFC 00070E84 00018000  [.=..............]  
  
  
D6D0440 A41874A9 00230001 00004CA9 00800C2F  [.t....#..L../...]  
  
  
D6D0450 002A1137 0001A000 A513B6E3 00010100  [7.*.............]  
  
  
D6D0460 0014FFFF 1F831F9B 00001F83 1F9B0001  [................]  
  
  
D6D0470 00000000 00000000 00000000 00000000  [................]  
  
  
        Repeat 503 times  
  
  
D6D23F0 00000000 2C000000 58010100 013F0601  [.......,...X..?.]  
  
  
Block header dump:  0x00400822  
  
  
 Object id on Block? Y  
  
  
 seg/obj: 0x102  csc: 0x01.a514474a  itc: 2  flg: O  typ: 1 - DATA  
  
  
     fsl: 0  fnx: 0x0 ver: 0x01  
  
  
   
  
  
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc  
  
  
0x01   0x0009.020.00003df2  0x00820cfc.0e84.07  C---    0  scn 0x0001.a41874a9  
  
  
0x02   0x0001.023.00004ca9  0x00800c2f.1137.2a  C-U-    0  scn 0x0001.a513b6e3  
  
  
   
  
  
data_block_dump,data header at 0xd6d045c  
  
  
===============  
  
  
tsiz: 0x1fa0  
  
  
hsiz: 0x14  
  
  
pbl: 0x0d6d045c  
  
  
bdba: 0x00400822  
  
  
     76543210  
  
  
flag=--------  
  
  
ntab=1  
  
  
nrow=1  
  
  
frre=-1  
  
  
fsbo=0x14  
  
  
fseo=0x1f9b  
  
  
avsp=0x1f83  
  
  
tosp=0x1f83  
  
  
0xe:pti[0] nrow=1 offs=0  
  
  
0x12:pri[0] offs=0x1f9b  
  
  
block_row_dump:  
  
  
tab 0, row 0, @0x1f9b  
  
  
tl: 5 fb: --H-FL-- lb: 0x0  cc: 1  
  
  
col  0: [ 1]  58  
  
  
end_of_block_dump  
  
  
End dump data blocks tsn: 0 file#: 1 minblk 2082 maxblk 2082  
  
  
--------------------------------------------------- 
 
 

4. manual database creation script

 
 

  
  
$ORACLE_HOME/RDBMS/ADMIN/dcore.bsq  
  
  
--dual  
  
  
create table dual                   /* pl/sql's standard pckg requires dual. */  
  
  
  (dummy varchar2(1))    /* note, the optimizer knows sys.dual is single row */  
  
  
  storage (initial 1)  
  
  
/  
  
  
insert into dual values('X')  
  
  
/  
  
  
create public synonym dual for dual  
  
  
/  
 
 

Extended Extension: What is DUAL?

DUAL: Diffusing Update Algorithm, one of the dispersion Update Algorithm. Dual is a virtual table automatically created by Oracle and the data dictionary. It has only one column: DUMMY, whose data type is VARCHAR2 (1 ). There is only one row of data in dual: 'X '. Dual belongs to the sys mode, but all users can access it using the dual name. This table is often used to calculate the constant expression and pseudo column equivalence using the SELECT statement, because it only returns one row of data and uses other multiple data rows may be returned. It is used to query the content that does not belong to the actual table, and sometimes it is used to check the existence of records of a certain condition in a table.

 
 

  
  
For example, select sysdate from dual;
  
  
Select 3 + 3 from dual;
  
  
Equivalent to SQL Server's
  
  
Set @ Date = getdate ()