ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DUAL是真实的表吗

DUAL是真实的表吗

原创 Linux操作系统 作者:redhouser 时间:2011-05-30 09:28:58 0 删除 编辑

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
/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-696605/,如需转载,请注明出处,否则将追究法律责任。

上一篇: DB2锁问题
请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    826395