有一张表T
SQL> desc t
名称 是否为空? 类型
----------------------------------------- -------- ------------------
ID NOT NULL NUMBER
D1 DATE
N1 NUMBER
N2 NUMBER
N3 NUMBER
N4 NUMBER
N5 NUMBER
N6 NUMBER
C1 VARCHAR2(20)
C2 VARCHAR2(20)
PAD VARCHAR2(4000)
id为主键,索引名为t_pk
SQL> select count(*) from t;
COUNT(*)
----------
10000
下面通过两个过程来查找min(id)、max(id),第一个在一个select语句选出两个值,第二个每个值分别用一个select语句选出。
SQL> alter session set sql_trace=true;
会话已更改。
SQL> declare
2 l_max number;
3 l_min number;
4 begin
5 for i in 1 .. 10000 loop
6 select max(id), min(id) into l_max, l_min from t ;
7 end loop;
8 end;
9 /
PL/SQL 过程已成功完成。
下面是TKPROF的内容:
declare
l_max number;
l_min number;
begin
for i in 1 .. 10000 loop
select max(id), min(id) into l_max, l_min from t ;
end loop;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.00 0 0 0 0
Execute 2 3.26 3.60 0 0 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 3.27 3.61 0 0 0 2
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 80
********************************************************************************
SELECT MAX(ID), MIN(ID)
FROM
T
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.00 0 0 0 0
Execute 20000 0.84 0.81 0 0 0 0
Fetch 20000 104.45 104.55 0 780000 0 20000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 40002 105.31 105.37 0 780000 0 20000 --发现cpu和query的值都很大
Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: 80 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
10000 SORT AGGREGATE (cr=390000 pr=0 pw=0 time=50670073 us)
100000000 INDEX FAST FULL SCAN T_PK (cr=390000 pr=0 pw=0 time=376003502 us)(object id 54587)
第二个过程:
SQL> declare
2 l_max number;
3 l_min number;
4 begin
5 for i in 1 .. 10000 loop
6 select max(id) into l_max from t ;
7 select min(id) into l_min from t ;
8 end loop;
9 end;
10 /
PL/SQL 过程已成功完成。
TKPROF
declare
l_max number;
l_min number;
begin
for i in 1 .. 10000 loop
select max(id) into l_max from t ;
select min(id) into l_min from t ;
end loop;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 3.18 3.38 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 3.18 3.39 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 80
********************************************************************************
SELECT MAX(ID)
FROM
T
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 0.45 0.38 0 0 0 0
Fetch 10000 0.48 0.42 0 20000 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20001 0.93 0.81 0 20000 0 10000
Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: 80 (recursive depth: 1)
********************************************************************************
SELECT MIN(ID)
FROM
T
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 0.40 0.38 0 0 0 0
Fetch 10000 0.43 0.42 0 20000 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20001 0.84 0.81 0 20000 0 10000 --cpu和query的值很小
Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: 80 (recursive depth: 1)
第三个过程:
1 declare
2 l_max number;
3 l_min number;
4 begin
5 for i in 1 .. 10000 loop
6 select a, b into l_max, l_min from (select (select max(id) from t) a ,(sele
ct min(id) from t) b from dual);
7 end loop;
8* end;
SQL> /
PL/SQL 过程已成功完成。
SELECT A, B
FROM
(SELECT (SELECT MAX(ID) FROM T) A ,(SELECT MIN(ID) FROM T) B FROM DUAL)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 0.31 0.23 0 0 0 0
Fetch 10000 1.21 1.23 0 40000 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20001 1.52 1.47 0 40000 0 10000 --比分开写时候快了一点点
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 80 (recursive depth: 1)
执行计划
----------------------------------------------------------
Plan hash value: 2832703409
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:0
1 |
| 1 | SORT AGGREGATE | | 1 | 4 | |
|
| 2 | INDEX FULL SCAN (MIN/MAX)| T_PK | 10000 | 40000 | 2 (0)| 00:00:0
1 |
| 3 | SORT AGGREGATE | | 1 | 4 | |
|
| 4 | INDEX FULL SCAN (MIN/MAX)| T_PK | 10000 | 40000 | 2 (0)| 00:00:0
1 |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:0
1 |
统计信息
----------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
466 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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25361369/viewspace-711621/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25361369/viewspace-711621/