Oracle最轻量的SQL是什么？

显示全部楼层 · 2015-4-24 16:04:24

最轻量的意义是逻辑读为0，CPU Time尽可能低。

千问 · 2015-4-24 16:04:24

比如：
select 1 from dual
select sysdate from dual
...

千问 · 2015-4-24 16:04:24

哈哈

千问 · 2015-4-24 16:04:24

先看一下select 1 from dual和select sysdate from dual的区别。
10G:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE10.2.0.1.0Production
TNS for HPUX: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> set serveroutput on
SQL> declare
2i pls_integer;
3b pls_integer;
4e pls_integer;
5c pls_integer;
6d date;
7begin
8select value
9into b
10from v$mystat t, v$statname x
11 where t.STATISTIC# = x.STATISTIC#
12 and x.NAME = 'CPU used by this session';
13
14for i in 1 .. 100000 loop
15select 1 into c from dual;
16end loop;
17
18select value
19into e
20from v$mystat t, v$statname x
21 where t.STATISTIC# = x.STATISTIC#
22 and x.NAME = 'CPU used by this session';
23dbms_output.put_line(e-b);
24end;
25/
236
PL/SQL 过程已成功完成。
SQL> declare
2i pls_integer;
3b pls_integer;
4e pls_integer;
5c pls_integer;
6d date;
7begin
8select value
9into b
10from v$mystat t, v$statname x
11 where t.STATISTIC# = x.STATISTIC#
12 and x.NAME = 'CPU used by this session';
13
14for i in 1 .. 100000 loop
15select sysdate into d from dual;
16end loop;
17
18select value
19into e
20from v$mystat t, v$statname x
21 where t.STATISTIC# = x.STATISTIC#
22 and x.NAME = 'CPU used by this session';
23dbms_output.put_line(e-b);
24end;
25/
276
PL/SQL 过程已成功完成。
11G:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE11.2.0.2.0Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> set serveroutput on
SQL> declare
2i pls_integer;
3b pls_integer;
4e pls_integer;
5c pls_integer;
6d date;
7begin
8select value
9into b
10from v$mystat t, v$statname x
11 where t.STATISTIC# = x.STATISTIC#
12 and x.NAME = 'CPU used by this session';
13
14for i in 1 .. 100000 loop
15select 1 into c from dual;
16end loop;
17
18select value
19into e
20from v$mystat t, v$statname x
21 where t.STATISTIC# = x.STATISTIC#
22 and x.NAME = 'CPU used by this session';
23dbms_output.put_line(e-b);
24end;
25/
155
PL/SQL 过程已成功完成。
SQL> declare
2i pls_integer;
3b pls_integer;
4e pls_integer;
5c pls_integer;
6d date;
7begin
8select value
9into b
10from v$mystat t, v$statname x
11 where t.STATISTIC# = x.STATISTIC#
12 and x.NAME = 'CPU used by this session';
13
14for i in 1 .. 100000 loop
15select sysdate into d from dual;
16end loop;
17
18select value
19into e
20from v$mystat t, v$statname x
21 where t.STATISTIC# = x.STATISTIC#
22 and x.NAME = 'CPU used by this session';
23dbms_output.put_line(e-b);
24end;
25/
168
PL/SQL 过程已成功完成。

千问 · 2015-4-24 16:04:24

学习！

千问 · 2015-4-24 16:04:24

Kamus 发表于 2011-11-8 10:39
想也知道sysdate比1要慢一些了，至少还要取一次内存结构中的数据，如果你这个测试不时11g，我猜测sysdate消 ...
10G和11G都有结果。
这两个SQL只是个例子，其实可比性不大。只是希望了解有没有更轻量的。

千问 · 2015-4-24 16:04:24

千问 · 2015-4-24 16:04:24

SYS@ncdb>select * from x$dual;
ADDR
INDX
INST_ID D
---------------- ---------------- ---------------- -
0000000110174098
0
1 X
Elapsed: 00:00:00.03
SYS@ncdb>select * from x$dual;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3957808902
---------------------------------------------------------------------------
| Id| Operation| Name | Rows| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT || 1 |16 | 0 (0)| 00:00:01 |
| 1 |FIXED TABLE FULL| X$DUAL | 1 |16 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------

Statistics
----------------------------------------------------------

0recursive calls

0db block gets

0consistent gets

0physical reads

0redo size
301bytes sent via SQL*Net to client
247bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed
SYS@ncdb>select * from dual;
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3543395131
--------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
24recursive calls

0db block gets

7consistent gets

4physical reads

0redo size
221bytes sent via SQL*Net to client
247bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed
SYS@ncdb>select * from dual;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3543395131
--------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------

0recursive calls

0db block gets

3consistent gets

0physical reads

0redo size
221bytes sent via SQL*Net to client
247bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed
SYS@ncdb>

千问 · 2015-4-24 16:04:24

select 1 from dual; 10g里的fast dual,应该也是没有逻辑读的

千问 · 2015-4-24 16:04:24

如果PL/SQL 也算的话，那么是begin
null;
end;
/复制代码