同时取最大最小值
--环境准备
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
alter table t add constraint pk_object_id primary key (OBJECT_ID);
set autotrace on
set linesize 1000
--看看执行计划是什么:
set linesize 1000
set autotrace on
select max(object_id),min(object_id) from t;
/*
执行计划
----------------------------------------------------------
Plan hash value: 1265209789
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 40 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FAST FULL SCAN| PK_OBJECT_ID | 54956 | 697K| 40 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
145 recursive calls
0 db block gets
224 consistent gets
142 physical reads
0 redo size
494 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
*/
/*
小结: 执行计划并没有走高效的index full scan (min/max)扫描方式,而是走index fast full scan模式。
这是因为oracle不能同时在索引 相同的两段寻找最大值 和最小值 。
以上语句修改成以下利用笛卡尔积的查询方式完成.
*/
--同时取最大最小值的语句的改造写法
select max, min
from (select max(object_id) max from t ) a,
(select min(object_id) min from t ) b;
/*
执行计划
----------------------------------------------------------
Plan hash value: 3319831621
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 26 | 4 (0)| 00:00:01 |
| 2 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 13 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| PK_OBJECT_ID | 54956 | 697K| 2 (0)| 00:00:01 |
| 5 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 13 | | |
| 7 | INDEX FULL SCAN (MIN/MAX)| PK_OBJECT_ID | 54956 | 697K| 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
7 recursive calls
0 db block gets
118 consistent gets
0 physical reads
0 redo size
472 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
*/
--性能在 consistent gets上得到了提升.