oracle dual表

浏览： ℃

字体：大中小

发布时间：2013-12-11 11:02:57

来源：

dual表是一个小表，属于sys但是可以被所有用户使用。用户编写的程序可以引用来保证一个已知的结果。当一个值仅仅返回一次，dual表非常有用,例如,查询当前的日期和时间。
dual表只有一列和一行数据，数据为"X",如下：
SQL> desc dual
Name Null? Type
------------------------------------------------- ----------------------------
DUMMY VARCHAR2(1)

SQL> select * from dual;

D
-
X
下面使用dual执行一个算数操作：
SQL> select 1+2*5 from dual;

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

注意：
从Oracle Database 10g Release1,当计算表达式不包括DUMMY列时，在dual表不会执行逻辑I/O。如果查询DUMMY列，则会产生逻辑I/O.
例如：

SQL> set linesize 132
SQL> set autotrace traceonly
SQL> select 1+2*5 from dual; ---计算表达式，执行计划为FAST DUAL，无逻辑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; ---查询包含DUMMY，执行计划为TABLE ACCESSFULL，有逻辑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

>更多相关文章