关闭

oracle dual表

标签: oracledual
890人阅读 评论(0) 收藏 举报
分类:
 
  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|      (0)| 00:00:01 |
  1 | FASTDUAL             1|      (0)| 00:00:01|  
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
          recursive calls
          db block gets
          consistentgets
          physical reads
          redo size
       523  bytes sent via SQL*Net to client
       523  bytes received via SQL*Net from client
          SQL*Net roundtrips to/from client
          sorts (memory)
          sorts (disk)
          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|      (0)| 00:00:01 |
  1 | TABLE ACCESS FULL| DUAL|    1|    2|      (0)| 00:00:01 |
--------------------------------------------------------------------------


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

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:30795次
    • 积分:630
    • 等级:
    • 排名:千里之外
    • 原创:29篇
    • 转载:2篇
    • 译文:5篇
    • 评论:0条
    联系方式
    QQ:774622220 email:wxl1314520@gmail.com 新浪微博:北漂IT一卒
    文章分类