sql> desc dual Name Null? Type ----------------------------------------- -------- ---------------------------- DUMMY VARCHAR2(1) sql> select 4*5 from dual; 4*5 ---------- 20 sql>
我觉得很奇怪.如果在dual中没有名为4 * 5的列,那么select语句如何工作？
另外,为什么我在创建自己的双表时没有看到相同的行为？
sql> create table dual2(dummy varchar2(1)); Table created. sql> desc dual2 Name Null? Type ----------------------------------------- -------- ---------------------------- DUMMY VARCHAR2(1) sql> select 4*5 from dual2; no rows selected sql>
The DUAL table is a special one-row table present by default in all Oracle database installations. It is suitable for use in selecting a pseudocolumn such as SYSDATE or USER. The table has a single VARCHAR2(1) column called DUMMY that has a value of ‘X’.
因此,双表是一种对空的表而不是空表执行操作的方法.当一个人不关心表,但需要通过select语句执行操作时,这很有用.如果表具有多个行或列,则将返回多个结果(由于在执行操作时对整个元组集进行操作.)
它不应该在生产中使用,除非您特别需要通过sql调用某些过程.
4 * 5是数学运算,就像’Foo’是一个字符串一样.因此,正如可以从任何表中选择4 * 5一样,就像可以从任何表中选择“Foo”一样,DUAL是一种从已知良好的表中选择它的方式,该表永远不会有多个结果.
从documentation(概念)：
DUAL is a small table in the data dictionary that Oracle Database and user-written programs can reference to guarantee a known result. The dual table is useful when a value must be returned only once,for example,the current date and time. All database users have access to DUAL.
The DUAL table has one column called DUMMY and one row containing the value X.
DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. It has one column,DUMMY,defined to be VARCHAR2(1),and contains one row with a value X. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row,the constant is returned only once. Alternatively,you can select a constant,pseudocolumn,or expression from any table,but the value will be returned as many times as there are rows in the table. Refer to “About sql Functions” for many examples of selecting a constant value from DUAL.
Beginning with Oracle Database 10g Release 1,logical I/O is not performed on the DUAL table when computing an expression that does not include the DUMMY column. This optimization is listed as FAST DUAL in the execution plan. If you SELECT the DUMMY column from DUAL,then this optimization does not take place and logical I/O occurs.
