funny test of dual table

原创 2004年07月13日 23:42:00
funny test of dual table

C:/>sqlplus "/ as sysdba"

SQL*Plus: Release 9.0.1.0.1 - Production on Thu Aug 8 09:20:57 2002

(c) Copyright 2001 Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> select count(*) from dual;

COUNT(*)
----------
1

SQL> select * from dual;

D
-
X

SQL> insert into dual values ('Y');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into dual values ('X');

1 row created.

SQL> insert into dual values ('Z');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from dual;

COUNT(*)
----------
4

SQL> select * from dual;

D
-
X

SQL> select sysdate from dual;

SYSDATE
---------
08-AUG-02

SQL> select count(*) from dual;

COUNT(*)
----------
4

SQL> truncate table dual;

Table truncated.

SQL> select count(*) from dual;

COUNT(*)
----------
0

SQL> select * from dual;

no rows selected

SQL> select sysdate from dual;

no rows selected

SQL> insert into dual values ('X');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dual;

D
-
X

SQL> select count(*) from dual;

COUNT(*)
----------
1

SQL> select sysdate from dual;

SYSDATE
---------
08-AUG-02

SQL>

***************************************

作者:4pal    时间:02-08-08 22:01

嘿嘿。snow知道dual到底是什么吗? 我还是不 清楚

作者:rejoice999    时间:02-08-08 23:55

DUAL就是个一行一列的表,如果你往里加一行,就会导致很多程序出问题。所以从ORACLE8开始,ORACLE加了代码对DUAL特殊照顾,才会有SNOW版主的FUNNY TEST RESULT。
http://www.itpub.net/showthread.php...&highlight=dual

quote: 最初由 4pal 发布
嘿嘿。snow知道dual到底是什么吗? 我还是不 清楚

作者:oldwain    时间:02-08-10 21:19

quote: 最初由 rejoice999 发布
所以从ORACLE8开始,ORACLE加了代码对DUAL特殊照顾,



NO! NO!

ORACLE没有对DUAL进行特殊照顾, 进行特殊照顾的是sql*plus.

使用其它工具(比如pl/sql dev等) 会发现select * from dual会得到真实的记录.

另外, 在sqlplus中执行create table .. as select * from dual , inset ..select * from dual的结果也是真实的.

个人认为sqlplus耍的这个小聪明, 是个很愚蠢的小聪明.

作者:Fenng    时间:02-08-10 23:24

跟着这个贴子看了看,随后到asktom上查了一下,发现还真有贴子关于它的.
You Asked
Hi Tom

Feels good to post a question after quite some time.
Look at the following :
SQL> desc dual
Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)

SQL> select * from dual;

D
-
X

SQL> select count(*) from dual;

COUNT(*)
----------
1

SQL> select 'sdfsdfsdfsdfsdfsdf' from dual;

'SDFSDFSDFSDFSDFSD
------------------
sdfsdfsdfsdfsdfsdf

SQL> select 3434334 from dual;

3434334
----------
3434334

SQL> truncate table dual;
truncate table dual
*
ERROR at line 1:
ORA-04020: deadlock detected while trying to lock object
3510531527724648449742036


SQL> delete from dual;
delete from dual
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> connect system/manager
Connected.
SQL> /

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from dua;
select * from dua
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from dual;

no rows selected



1.What is the dual table, what is its purpose.
2.Why does it contain only one column with datatype varchar2, why not
number .
3.Does it contain one row by default.
4.why do we
usually SELECT USER FROM DUAL,
why cant I do it like
SQL> select USER FROM EMP WHERE ROWNUM<2;

USER
------------------------------
SCOTT


5.Does this mean that we are using the dual table only for the convenience that
it has only one row, and it will return only one row back , when we give
queries like

SELECT USER FROM DUAL

6.SQL> SELECT COUNT(*) FROM DUAL;

COUNT(*)
----------
2

SQL> INSERT INTO DUAL VALUES ('X');

1 row created.

SQL> INSERT INTO DUAL VALUES ('X');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM DUAL;

COUNT(*)
----------
4

SQL> SELECT * FROM DUAL;

D
-
X


Why is it returning only one row , it has to return 4 rows , when it has allowed
me to insert 4 rows.

7.SQL> delete from dual;

1 row deleted.

SQL> select * from dual;

D
-
X

SQL> select count(*) from dual;

COUNT(*)
----------
3


I want to delete all the rows , I do a delete from dual, and oooo mama
look at it , it deletes only one row, why?

8.SQL> delete from dual;

1 row deleted.

SQL> delete from dual;

1 row deleted.

SQL> select count(*) from dual;

COUNT(*)
----------
1

NOw we have only one row in dual.

lets create a example function.

SQL> create or replace function foo return number
2 as
3 x number;
4 begin
5 x:=1;
6 return 1;
7 end;
8 /

Function created.

SQL> select foo from dual;

FOO
----------
1

SQL> insert into dual values ('X');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM DUAL;

COUNT(*)
----------
2

SQL> select foo from dual;

FOO
----------
1

shouldnt it return 2 rows of value 1?


because , look below , I have got 14 rows back , because I had 14 rows in the
table.

SQL> SELECT FOO FROM EMP;

FOO
----------
1
1
1
1
1
1
1
1
1
1
1

FOO
----------
1
1
1

14 rows selected.

SQL>

Kindly elucidate the concept of dual table, and if possible illustrate it.

Thank you

--------------------------------------------------------------------------------
and we said...

Let me just start by saying -- DUAL is owned by SYS. SYS owns the data dictionary, therefore DUAL is part of the data dictionary. You are not to modify the data dictionary via SQL ever -- wierd things can and will happen -- you are just demonstrating some of them. We can make many strange things happen in Oracle by updating the data dictionary. It is neither recommend, supported nor a very good idea.

1.What is the dual table, what is its purpose.

dual is just a convienence table. You don't need to use it, you can use anything you want. The advantage to dual is the optimizer understands dual is a special one row, one column table -- when you use it in queries, it uses this knowledge when developing the plan.


2.Why does it contain only one column with datatype varchar2, why not
number .

truly, why no. Why not a date you would ask then. The column, its name, its datatype and even its value are NOT relevant. DUAL exists solely as a means to have a 1 row table we can reliably select from. Thats all.


3.Does it contain one row by default.

yes, when we build the database, we build dual and put a single row in it.

4.why do we usually SELECT USER FROM DUAL,
why cant I do it like
SQL> select USER FROM EMP WHERE ROWNUM<2;


truly, why can't you? is something preventing you from doing so?? You can if you want. Me, I'll stick with "select user from dual". I know dual exists. I know it has at least 1 and at most 1 row. I know the optimizer knows all about dual and does the most efficient thing for me.

5) yes

6) the optimizer understands dual is a magic, special 1 row table. It stopped on the select * because there is to be one row in there. Its just the way it works. Hopefully you reset dual back to 1 row after your testing or you just totally broke your database!

7) like I said, duals magic, the optimizer knows all about what DUAL should be and does things based on that.

8) dual = magic. dual is a one row table however having more then 1 or less then one is dangerous. You are updating the data dictionary. You should naturally expect very bad things to happen.


Ok, here is some trivia for you out there. How did I do this:

SVRMGR> select * from dual;
D
-
X
1 row selected.

SVRMGR> ????????????????????;
Statement processed.

SVRMGR> select * from dual;
ADDR INDX INST_ID D
-------- ---------- ---------- -
01680288 0 1 X
1 row selected.


What was that magic command?

作者:Fenng    时间:02-08-10 23:26

http://asktom.oracle.com/pls/ask/f?...6388,%7Bdual%7D

这是Tom上的连接

作者:biti_rainy    时间:02-08-11 09:49

hehe

看了 关于定义者权限和调用者权限 部分,结合oracle的关于所谓“精细存取控制”
在使用 sys_context() 函数的基础上

自己都可以做这么一个表出来

并且可以做一个满足你的需要的比dual更让你满意的表

例解EIGRP DUAL算法网络收敛原理

EIGRP采用的是DUAL(Diffusing Update Algorithm,扩散更新算法)。这个算法可以确保在极短时间内无环路计算出路由结果,并且允许所有与拓扑改变相关的设备在同一时间进行同步更...
  • lycb_gz
  • lycb_gz
  • 2013年09月22日 08:53
  • 5800

【经典策略系列】之 Dual Thrust 交易策略

一、楔子 今天看到了Dual Thrust系统的介绍,具体参见详解程序化交易Dual Thrust策略-雪球。 改写了一下用于股票交易。 使用第n-1日(前天)以前N天的数据计算Range,第n...
  • IAlexanderI
  • IAlexanderI
  • 2017年05月01日 15:56
  • 596

John+hdu+尼姆博弈

John Time Limit: 5000/1000 MS (Java/Others)    Memory Limit: 65535/32768 K (Java/Others) Total Sub...
  • u012870383
  • u012870383
  • 2014年09月24日 23:05
  • 584

lightmapping方式的比较

是 1.single lightmaps   该类型是最简单的一种lightmmaping方式,对性能及空间的消耗相对较小,可以很好的表现大多数静态场景的光影效果,但是在作用在游戏...
  • u012487582
  • u012487582
  • 2013年12月23日 17:12
  • 1489

mysql如何导入csv格式数据

MYSQL LOAD DATA INFILE命令可以把csv平面文件中的数据导入到数据库中。   linux下: LOAD DATA INFILE '/home/test/dump/ip_l...
  • lihe460186709
  • lihe460186709
  • 2016年12月26日 11:25
  • 143

MyCat - 使用篇(5)

数据库路由中间件MyCat - 使用篇(5)配置MyCat4. 配置schema.xmlschema.xml里面管理着MyCat的逻辑库、表,每张表使用的分片规则、分布在哪个DataNode以及Dat...
  • zhxdick
  • zhxdick
  • 2016年02月24日 09:39
  • 6003

Funny In C++ 计算机基础

硬盘是存东西的,数据是存在硬盘中 内存中不能存东西,但是程序运行,会先放到内存中 CPU 还有寄存器。速度比内存更快。把内存的东西弄过来,然后再给运算器进行计算 ...
  • cpp_edu
  • cpp_edu
  • 2016年01月25日 16:54
  • 108

oracle:时区

时区:时区是当地时间与英格兰格林威治时间的时差。一:与时区有关的函数: 1.CURRENT_DATE:返回在为数据库会话设置的本地时区中的当前时间。select CURRENT_DATE from ...
  • u011008029
  • u011008029
  • 2017年11月06日 20:02
  • 79

Dual SVM (对偶支持向量机)

1. SVM 目标函数及约束条件SVM 的介绍及数学推导参考:我的CSDN,此处直接跳过,直接给出 SVM 的目标函数和约束条件:minw,b12wTws.t.yn(wTxn+b)≥1,n=1,..N...
  • llcchh012
  • llcchh012
  • 2015年07月12日 16:50
  • 1918

台湾国立大学机器学习技术.听课笔记(第二讲) :Dual Support Vector Machine

台湾国立大学机器学习技术.听课笔记(第二讲) :Dual Support Vector Machine 一,Motivate of Dual SVM(对偶SVM的动机) 上一讲我们知道要想...
  • huang1024rui
  • huang1024rui
  • 2015年09月08日 10:11
  • 598
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:funny test of dual table
举报原因:
原因补充:

(最多只允许输入30个字)