DUAL是真实的表，还是优化器对DUAL的引用进行特别处理？
结论：是真实的表，见以下分析。
同时，根据建库脚本$ORACLE_HOME/RDBMS/ADMIN/dcore.bsq，优化器对该表有特殊处理，具体如何处理没有详细说明。
1 执行计划
- 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 手工建库脚本
- $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
- /
【延伸扩展】DUAL是什么？
DUAL：Diffusing Update Algorithm ,弥散更新算法.EIGRP组件之一。dual是Oracle与数据字典一起自动创建的一个虚拟表﹐它只有一列﹕DUMMY﹐其数据类型为﹕VARCHAR2(1)。dual中只有一行数据﹕ ‘X ‘。dual属于sys模式﹐但所有用户都可以使用dual名称访问它﹐用SELECT计算常量表达式﹑伪列等值时常用该表﹐因为它只返回一行数据﹐而使用其它表时可能返回多个数据行。用来查那些不属于实际表里的内容，有时也用来检查某表某条件的记录存在性。
- 如：select sysdate from dual;
- select 3+3 from dual;
- 相当与Sql Server的
- set @Date=getdate()
【编辑推荐】
- 告诉你一些DBA求职面试技巧
- 这些问题，你能回答多少
- 在牛人眼中 数据库有何差异化又该如何选型
- Oracle的安全标记算不算bug
- 如何抓住蝴蝶效应中的那只蝴蝶
文章来源网络，作者：运维，如若转载，请注明出处：https://shuyeidc.com/wp/227170.html<
