select sysdate from dual在Oracle9i和10g中的区别
在9i环境中
SQL> select * from v$version;
BANNER
----------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for HPUX: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
可以看到读取dual表会产生3个逻辑读
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
而在Oracle 10g，对dual的读取降低到0个逻辑读
SQL> select * from v$version;
BANNER
----------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> select sysdate from dual;
Execution Plan
----------------------
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 |
-----------------------------
Statistics
----------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
415 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
如果应用中大量采用select sysdate from dual这种方法获取系统时间，Oracle的这一点点小小的改动，无疑会提高性能。但是值得一提的是select * from dual；执行计划和逻辑读并没有改变。
