About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc
全部博文（166）
分类： Oracle
2022-04-11 11:16:45
DUAL表是Oracle中特殊的数据字典表，它是由Oracle内部代码维护的，我们一般情况下看到的DUAL表单行单列，字段名为DUMMY，类型VARCHAR2(1)，这些都是表象，因为DUAL表在Oracle中的用途很广泛，使用很便利，所以Oracle考虑了多方面因素，才造了这么个神奇的“虚表”。你认为DUAL表真的是单行单列的吗？先看表象：
SYS>desc dual
名称 是否为空? 类型
---------- -------- --------------
DUMMY VARCHAR2(1)
SYS>select * from dual;
DU
--
x
已选择 1 行。
表面来看，的确是单行单列，TOM(TOM KYTE,Oracle VP,世界知名数据库专家)曾经说DUAL表之所以做成单行单列只是为了便利，当然DUAL表有更多的用途，比如一些系统操作，如DROP TABLE，一些系统包等都可能会用到DUAL表，你改变了DUAL表的属性，比如增强一行，那么可能会导致严重的问题，数据字典都是不能乱动的。DUAL表的数据类型和列名以及它的值并没有绝对的关系，这个要注意，DUAL表用途广泛，Oracle优化器看到DUAL表，它就知道DUAL表应该是单行单列的，而且会启用相关优化措施(这点会在后面的例子中有说明)。那么DUAL表真的是单行单列的吗？看TOM给的一点提示：
--关闭数据库后，神奇的DUAL不同了
SYS>alter database close;
数据库已更改。
SYS>desc dual
名称 是否为空? 类型
---------- -------- --------------
DUMMY VARCHAR2(1)
SYS>select * from dual;
ADDR INDX INST_ID DU
-------- ---------- ---------- -- ---------- ------
03674AD4 0 1 X
已选择 1 行。
DUAL表既然是属于SYS数据字典，那么普通用户怎么访问到DUAL呢，而且还不要加SCHEMA,那当然是利用PUBLIC同义词了，查看：
SYS> SELECT * FROM DBA_SYNONYMS WHERE TABLE_NAME='DUAL';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
-----------------------------------------------------------------------
PUBLIC DUAL SYS DUAL
已选择 1 行。
从上面可以看到，DUAL表并没有那么简单。它拥有很多让你amazing的特性，当然如果你动了它，它会让你更加地amazing。下面就开始DUAL表的探秘之旅吧。
1）在9i、10g下对DUAL进行DDL、DML操作的神奇表现
前面已经说了，DUAL表这种特殊的数据字典，你只可以使用同义词来进行查询操作(当然所有的数据字典都不应该乱动)，而不能进行其他的操作改变DUAL表的基本属性，如果DUAL表属性一旦发生变化，可能会造成很多意想不到的问题，下面就暴力改变下DUAL表的属性，看看DUAL表到底有哪些神奇之处(切记，不要在生产环境下实验，后果自负)。
先在9i下进行相关实验（增加插入一行，然后SELECT 'X' FROM DUAL看看有几行，增加游标迭代）：
--select * from dual和select dummy from dual在9i和10g下的表现一样，这个不用测试了
SQL>SHOW REL
release 902000400
SQL> select * from dual;
D
-
X
1 row selected.
--给DUAL表插入一行
SQL> insert into dual values('Y');
1 row created.
--查看全部DUAL表内容，so amazing,竟然只返回新插入的一行
SQL> select * from dual;
D
-
Y
1 row selected.
--用count查看全部记录数量，正确返回，与上面的对比，DUAL表在SQL*PLUS里的表现真是很让人迷惑
SQL> select count(*) from dual;
COUNT(*)
----------
2
1 row selected.
SQL> commit;
Commit complete.
已经演示了一些DUAL表的神奇表现，下面神奇继续上演：
--全部清空，再次amazing,明明两行，竟然只删除了一行后插入的，当然和上面的select不谋而合
SQL> delete from dual;
1 row deleted.
SQL> select * from dual;
D
-
X
1 row selected.
--继续删除，这下清净了
SQL> delete from dual;
1 row deleted.
SQL> select * from dual;
no rows selected
SQL> select count(*) from dual;
COUNT(*)
----------
0
1 row selected.
--不能乱删的，恢复吧
SQL> insert into dual values('X');
1 row created.
SQL> select * from dual;
D
-
X
1 row selected.
--我已经不相信select * from dual了，还是count一下保险点，正确
SQL> select count(*) from dual;
COUNT(*)
----------
1
1 row selected.
--对上面的演示继续，这次不用delete from dual了，用delete from dual where dummy='Y',直接删除掉即可
SQL> insert into dual values('Y');
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from dual;
COUNT(*)
----------
2
1 row selected.
SQL> delete from dual where dummy='Y';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from dual;
COUNT(*)
----------
1
1 row selected.
--TRUNCATE和UPDATE没有什么神奇的
--TRUNCATE操作，和DELETE FROM不同，TRUNCATE在9.2.4下能清楚全部DUAL数据
--到10g版本测试，TRUNCATE,UPDATE,DELETE FROM表现和9i一样，但是也有不一样的，请见10g版本测试
SQL> insert into dual values('Y');
1 row created.
SQL> commit;
Commit complete.
SQL> truncate table dual;
Table truncated.
SQL> select * from dual;
no rows selected
SQL> select count(*) from dual;
COUNT(*)
----------
0
--恢复，执行UPDATE，可以改变其值
SQL> insert into dual values('X');
1 row created.
Commit complete
SQL> update dual set dummy='Y';
1 row updated
SQL> commit;
Commit complete
SQL> select * from dual;
DUMMY
-----
Y
--恢复
SQL> update dual set dummy='X';
1 row updated
SQL> commit;
Commit complete
上面演示了在9.2.4下对DUAL的INSERT,DELETE,TRUNCATE,UPDATE操作，可以看出，DUAL表的确很神奇，当然在SQL*PLUS里新插入一上用SELECT * FROM DUAL是看不出到底有几行的，用COUNT函数可以，在测试中，我发现在PLSQL DEVELOPER工具中用SELECT * FROM DUAL是可以看出到底有多少行的，说明SQL*PLUS和DUAL还是紧密相关的。
DUAL表多插入了或清空了，那么有什么不利之处呢？不利之处太多了，可能使系统包失效，使一些DDL操作报错，使一些程序报错等。下面就继续在9.2.4下，用简单的存储过程测试下DUAL表多插入了或清空了导致的问题。
SQL> select * from dual;
D
-
X
已选择 1 行。
已用时间: 00: 00: 00.03
SQL> insert into dual values('Y');
已创建 1 行。
已用时间: 00: 00: 00.07
SQL> commit;
以上测试只是9.2.0.4的情况，下面回到本章10g版本，你会发现与9.2.0.4很多不同的地方。
SQL>show rel
release 1002000100
SQL>select * from dual;
DU
--
X
已选择 1 行。
SQL>insert into dual values('Y');
已创建 1 行。
SQL>commit;
提交完成。
SQL>select * from dual;
DU
--
Y
已选择 1 行。
--注意和9i测试的对比，10.2就算插入了一行记录，count(*)仍然不能正确显示行数，还是1行，9i的为2行
SQL>select count(*) from dual;
COUNT(*)
----------
1
已选择 1 行。
--这个在9i显示的是两行，但是10.2是1行，可以猜测10.2多插入了行，在PL/SQL里的select sysdate into .. from dual
--也不会出问题，这个应该属于10g的优化
SQL>select sysdate from dual;
SYSDATE
--------------
31-10月-10
已选择 1 行。
--从上面可以看出，多插入一行，对select xxx into variable from dual是没有影响的。这个在9i下是要报too_many_rows错误--的
DINGJUN123>declare
2 var_date date;
3 begin
4 select sysdate into var_date from dual;
5 dbms_output.put_line(var_date);
6 end;
7 /
31-10月-10
PL/SQL 过程已成功完成。
--DELETE FROM 和9i一样，只能删除一行
SQL>delete from dual;
已删除 1 行。
SQL>commit;
提交完成。
SQL>select * from dual;
DU
--
X
已选择 1 行。
SQL>delete from dual;
已删除 1 行。
SQL>commit;
提交完成。
--将DUAL表全清空了，COUNT还是只显示1行，DUAL表除了select * from dual或select dummy from dual无行显示外，--计算是正常的1行
--这个和上面的多插入类似，9i也不同
SQL>select count(*) from dual;
COUNT(*)
----------
1
已选择 1 行。
SQL>select * from dual;
未选定行
SYS>select dummy from dual;
未选定行
SQL>select sysdate from dual;
SYSDATE
--------------
31-10月-10
已选择 1 行。
--DUAL表没有数据了同样不影响select xxx into variable from dual，9i下测试是报no_data_found异常的
DINGJUN123>declare
2 var_date date;
3 begin
4 select sysdate into var_date from dual;
5 dbms_output.put_line(var_date);
6 end;
7 /
31-10月-10
PL/SQL 过程已成功完成。
--TRUNCATE正常截断，和9i一样，当然和上面一样，空DUAL表的COUNT和相关计算不受影响
SQL>insert into dual values('X');
已创建 1 行。
SQL>insert into dual values('Y');
已创建 1 行。
SQL>commit;
提交完成。
SQL>truncate table dual;
表被截断。
SQL>select * from dual;
未选定行
--恢复
SQL>insert into dual values('X');
已创建 1 行。
SQL>commit;
提交完成。
下面看看10g多插入表对一些DROP TABLE操作的影响，当然其他的也可能影响（比如DROP TABLESPACE），没有测试而已，测试的目的是说明，DUAL表不可欺。
--多插入一条数据语句省略
DINGJUN123>drop table t;
drop table t
*
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01422: 实际返回的行数超出请求的行数
返回ORA-00604和ORA-01422错误，当对表进行DROP的时候，看到这两个错误，就要想起是DUAL表里的数据多了的缘故。通过SQL TRACE可以看到：
select dummy
from
dual where ora_dict_obj_type = 'TABLE'
说明DUAL表和DROP TABLE操作密切相关，此外DROP TABLESPACE也类似，当然可能有其他的操作也相关，这里不测试了。
9i好检查DUAL表数据是不是多了，一个COUNT搞定，在10g下测试COUNT还是返回一条，那么怎么办呢？有很多方式可以看到DUAL表的真正记录数，如到PLSQL DEVELOPER工具下查看，或用PL/SQL的游标解决。如下：
1）PL/SQL DEVELOPER可以正确显示10g下的DUAL表记录数
2）采用游标迭代
为什么不直接在PL/SQL里select sysdate into..等方式查看呢?前面测试了，10g下的DUAL不管是数据多了还是数据少了，select sysdate into … from dual显示的都是1行，而select dummy into … from dual如果清空了则不返回数据，多了的话也只显示1行，所以用迭代的方式。
DINGJUN123>select count(*) from dual;
COUNT(*)
----------
1
已选择 1 行。
DINGJUN123>set serveroutput on
DINGJUN123>begin
2 for rec in (select dummy from dual) loop
3 dbms_output.put_line(rec.dummy);
4 end loop;
5 end;
6 /
X
Y
PL/SQL 过程已成功完成。
通过测试9i和10g相应版本的DUAL操作，可以看出，9i到10g变化还是挺大的，9i下多插入或清空了DUAL表数据影响SELECT COUNT(*) FROM DUAL以及类似于SELECT SYSDATE FROM DUAL之类的操作，多插入了不影响DROP TABLE等操作。但是在10.2的测试下可以看出，多插入或清空不影响上述COUNT和计算操作，但是多插入了，则影响DROP TABLE等操作(清空了不影响DROP TABLE操作)，当然清空了影响SELECT * FROM DUAL或SELECT DUMMY FROM DUAL，但是不影响类似于SELECT SYSDATE FROM DUAL。此外，10g下在PL/SQL里用隐式游标迭代查找DUAL表里的数据，和9i表现一样。
3）10g的FAST DUAL优化
9i下的DUAL表访问没有优化，一般逻辑读为3。10g的DUAL表如果不需要访问DUAL表里的内容,而是利用DUAL表的一些功能，比如select sysdate from dual，则会采用FAST DUAL方式来访问表，它不是真正的访问表，因此逻辑读为0，这样在一个高频DUAL表使用的系统中，会大量减少逻辑读。
9i下DUAL的访问方式是：
--不管是只访问dual表本身的内容，还是用于计算等，都是全表扫描DUAL表，返回逻辑读一般为3个，无FAST DUAL
SQL> set autotrace traceonly
SQL> select * from dual;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
487 bytes sent via SQL*Net to client
655 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 sysdate from dual;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
495 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
10g则不同，如果只是利用DUAL表查找比如SYSDATE,做些相关计算，比如1+1，那么Oracle优化器(CBO,RBO一样规则)采用FAST DUAL方式访问，它不需要真正访问DUAL表，因此逻辑读为0。
DINGJUN123>set autotrace traceonly
--访问DUAL表本身的数据，不能走FAST DUAL，因为它需要真正访问表了
DINGJUN123>select * from dual
2 ;
已选择 1 行。
执行计划
----------------------------------------------------------
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 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
409 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--用于取系统函数，计算，启用FAST DUAL,不需要真正访问表，逻辑读为0
DINGJUN123>select sysdate from dual;
已选择 1 行。
执行计划
----------------------------------------------------------
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
417 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--这个也是访问了DUAL表
DINGJUN123>select dummy,sysdate from dual;
已选择 1 行。
执行计划
----------------------------------------------------------
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 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
474 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--RBO虽然不用了，还是测试一下，规则与CBO一样
DINGJUN123>alter session set optimizer_mode=rule;
会话已更改。
DINGJUN123>select 1 from dual;
已选择 1 行。
执行计划
----------------------------------------------------------
Plan hash value: 1388734953
---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | SELECT STATEMENT | |
| 1 | FAST DUAL | |
---------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
406 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
DINGJUN123>select sysdate,sysdate,sysdate from dual;
已选择 1 行。
执行计划
----------------------------------------------------------
Plan hash value: 1388734953
---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | SELECT STATEMENT | |
| 1 | FAST DUAL | |
---------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
547 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
DINGJUN123>select dummy from dual;
已选择 1 行。
执行计划
----------------------------------------------------------
Plan hash value: 272002086
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| DUAL |
----------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
409 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
10g有了FAST DUAL优化，增强了DUAL表作为常用工具的功能，这也是Oracle10g新版本带来的众多新特性之一，值得注意。
