神奇的DUAL表总结_PART1

★★★★★★★★★★★本文档版权归个人所有，未经允许，禁止用于商业用途，违者必究★★★★★★★★★★★

背景介绍：Oracle DUAL表是一个很神奇的东西，为什么说它神奇呢？DUAL表属于SYS用户，SYS用户是专门管理Oracle数据字典的用户，DUAL表因此属于数据字典的一个组成部分（而且DUAL表是个特殊实现的数据字典表）。一个Oracle使用人员应该知道SYS里的东西只能查询，不可乱做修改的，否则会造成意想不到的、甚至毁灭性的灾难。既然DUAL表属于数据字典，归SYS用户管理，那么其他的用户是如何访问的呢？答案是通过一个叫DUAL的PUBLIC SYNONYM来访问的。

DUAL表是建立数据库的时候随数据字典创建而建立的，它是单行单列的一个表，一般称为“哑表、虚表等”，列名DUMMY，类型VARCHAR2(1)，值为'X'，为什么叫虚表，因为它有比物理表更多神奇内涵。这就是DUAL表的全部基本内容，对于这个基本内容要确保是固定的，不可修改，有很多莫名的问题产生就是因为动了这张表。DUAL表的基本内容虽然简单，但背后隐藏了很多神奇的内容，在下面的内容中会详细介绍。

DUAL表的主要作用是什么呢？有Oracle使用经验的都知道，我们常使用DUAL表来计算常量型表达式，比如用DUAL表访问，SELECT里可以是1、USER、SYSDATE、'abc'、TRUNC(SYSDATE,'DD')、SEQ.NEXTVAL等这些常量型表达式。因为DUAL表就一行，用于计算就很方便了，当然可能有人会问，DUAL表计算方便，那么我建立一个只有一行的表(多行当然经常不是我们需要的结果)，不照样计算方便吗？那是的，当然计算方便，但是DUAL表和Oracle优化器有着更紧密的关联，优化器知道DUAL表只有一行，而且很特殊，还有一些优化访问手段，优化器遇到一些计算使用的是DUAL或只有一行的普通表，那么优化器的反应很可能是不同的，DUAL表的效率要优于那个普通表。另外DUAL表常用于结合CONNECT BY子句进行数据构造。

DUAL表是数据字典表，不能乱动，不可对它进行任何DDL、DML等操作，否则会造成意想不到的效果，比如给DUAL插入一条数据，那么执行DROP TABLE命令可能会报错，这些都在后面详细讲解，因为它很特殊，你只可对DUAL表使用SELECT权限，其他权限一律禁止。

内容简介：

1) DUAL表常规用途。

主要介绍DUAL表有哪些主要用途，比如计算，构造数据等。

2) DUAL表神秘之处

介绍在9i和10g下对DUAL进行DDL和DML操作的神奇表现、10g的FAST DUAL优化、在9i和10g下使用DUAL和CONNECT BY构造数据的一些问题和方法探讨以及其他DUAL神秘之处。

3) PL/SQL中使用DUAL赋值和直接变量赋值的性能区别。

通过测试说明SELECT ..INTO .. FROM DUAL与variable := value的性能区别。

4) 误删DUAL表的恢复过程。

误删DUAL表的相关方法介绍。

说明：本章内容脚本请参考dual_sample.sql，未做说明一律在当前版本的SQL*PLUS下完成脚本测试，未做说明所有脚本都是在下列版本下执行的：

DINGJUN123>show rel

release 1002000100

DUAL表的常规用途

DUAL是单行单列的，这个很特殊，我们常使用它来获取一些常量型值或做一些计算功能，当然DUAL还有个重要的作用就是用于数据构造。下面举例说明下DUAL表的常见功能。

1）获取一些系统函数的值

这个用的最多的就是获取SYSDATE函数的值，从而返回当前时间了，此外还可以获取USER等函数值。如：

DINGJUN123>SELECT SYSDATE FROM DUAL;

SYSDATE

--------------

20-10月-10

已选择 1 行。

DINGJUN123>SELECT USER FROM DUAL;

USER

---------------

DINGJUN123

已选择 1 行。

2）做相关运算

比如用DUAL做些数学运算，一些函数测试，获取sequence等。如：

--数学运算，充当计算器

DINGJUN123>SELECT 2*5-10 num FROM DUAL;

NUM

----------

0

已选择 1 行。

--函数测试，有时候一函数搞不清楚用法，用DUAL测试很方便

DINGJUN123>SELECT REPLACE('abcdefg','f','$') rp FROM DUAL;

RP

--------------

abcde$g

已选择 1 行。

--查找序列，序列是必须要通过SQL来查找的，所以用DUAL很简单，如果用一个多行的物理表，会有递增的序列出现

DINGJUN123>SELECT seq.nextval FROM DUAL;

NEXTVAL

----------

301

已选择 1 行。

--用普通物理表，有多少行，就会计算多少次，一般我们不需要这种功能

DINGJUN123>SELECT COUNT(*) FROM t;

COUNT(*)

----------

2

已选择 1 行。

DINGJUN123>SELECT seq.nextval FROM t;

NEXTVAL

----------

302

303

已选择2行。

3）使用DUAL表构造数据

使用DUAL表构造数据，常结合CONNECT BY层次查询来构造，比如现在要构造1到100之间所有偶数，如下：

DINGJUN123>SELECT LEVEL lv

2 FROM DUAL

3 WHERE MOD(LEVEL,2) = 0 --注意WHERE是在CONNECT BY之后执行的，过滤CONNECT BY的结果

4 CONNECT BY LEVEL <= 100;

LV

-------------------

2

4

6

---中间结果省略

96

98

100

已选择50行。

上面用DUAL和CONNECT BY实现常见的数据构造，注意这是在10g下完成的，如果是9i不一定成功，而且9i的不同版本还有不同的表现，这个会在下一节详细讲解。

再来看一个例子，有时候，我们需要建立些简单的表来进行测试，那当然需要给表插入些测试数据了，使用DUAL表，可以很简单地构造些测试数据：

DINGJUN123>DROP TABLE emp_t;

表已删除。

DINGJUN123>CREATE TABLE emp_t

2 AS

3 SELECT 1 id,'liming' name, DATE'2010-10-20' hire_date FROM DUAL UNION ALL

4 SELECT 1 ,'jack',DATE'2010-10-21' FROM DUAL;

表已创建。

使用DUAL表很简单吧，用于测试，构造些数据(还可以与DBMS_RANDOM包配合构造随机数据)，的确很方便，当然有很多方法可以实现数据的构造，比如数据直接放csv文件里，通过sql*loader来导入，或用外部表，也可以通过plsql developer等工具提供的快速导入功能（比如select * from table for update然后粘贴），但是少量数据还是用DUAL简单，它可以保存测试的脚本。下面做一个比较复杂点的DUAL表构造随机测试数据：