zt http://www.xifenfei.com/1290.html
1、查看数据库版本
SQL> select * from v$version where
rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle
Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
2、创建表和索引
create table t_a as select * from
dba_objects;
create index t_a_ind on t_a(object_id);
3、查询最大值
SQL> select max(object_id) from
t_a;
执行计划
———————————————————-
Plan hash value: 3226265922
————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| T_A_IND | 1 | 13 | 2 (0)| 00:00:01 |
————————————————————————————–
Note—— dynamic sampling used for this statement (level=2)
统计信息
———————————————————-
0 recursive calls
0 db block gets
2
consistent gets
0 physical reads
0 redo size
431 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
4、查询最小值
SQL> select min(object_id) from
t_a;
执行计划
———————————————————-
Plan hash value: 3226265922
————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | |
|
| 2 | INDEX FULL SCAN (MIN/MAX)| T_A_IND | 1 | 13 | 2 (0)| 00:00:01 |
————————————————————————————–
Note—— dynamic sampling used for this statement (level=2)
统计信息
———————————————————-
0 recursive calls
0 db block gets
2
consistent gets
0 physical reads
0 redo size
429 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
5、查询最大值和最小值
SQL> select max(object_id),min(object_id)
from t_a;
执行计划
———————————————————-
Plan hash value: 2127980459
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 13
| 293 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE
ACCESS FULL| T_A | 78093 | 991K| 293 (1)| 00:00:04
|
—————————————————————————
Note—— dynamic sampling used for this statement (level=2)
统计信息
———————————————————-
4 recursive calls
0 db block gets
1119
consistent gets
1044 physical reads
0 redo size
502 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
6、查询最大值和最小值（排除null）
SQL> select
max(object_id),min(object_id) from t_a where object_id is not
null;
执行计划
———————————————————-
Plan hash value: 1214261695
———————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
———————————————————————————
| 0 | SELECT STATEMENT | | 1 | 13 | 50 (2)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | 13 | |
|
|* 2 | INDEX FAST FULL SCAN| T_A_IND | 78093 | 991K| 50 (2)|
00:00:01
|
———————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – filter(“OBJECT_ID” IS NOT NULL)
Note—— dynamic sampling used for this statement (level=2)
统计信息
———————————————————-
4 recursive calls
0 db block gets
242
consistent gets
0 physical reads
0 redo size
502 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
7、分别查询最大值和最小值
SQL> select (select max(object_id) from
t_a) max,(select min(object_id) from t_a) min from
dual;
执行计划
———————————————————-
Plan hash value: 312201770
————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | |
| 2 | INDEX FULL SCAN (MIN/MAX)| T_A_IND | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 13 | |
| 4 | INDEX FULL SCAN (MIN/MAX)| T_A_IND | 1 | 13 | 2 (0)| 00:00:01 |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
————————————————————————————–
Note—— dynamic sampling used for this statement (level=2)
统计信息
———————————————————-
0 recursive calls
0 db block gets
4
consistent gets
0 physical reads
0 redo size
480 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
说明：
1、单查询最大值或者最小值，sql会自动走index
2、如果同时查询最大值和最小值，sql会使用全表扫描，而不是我们想象的索引快速扫描
3、加上where
排除掉null的情况，sql使用索引快速扫描，原因是：在不能确定索引列不为null（或者没有排除掉null）的情况下，不会使用索引快速扫描，而sql为了保证正确而采用了全表扫描
4、INDEX
FULL SCAN (MIN/MAX)扫描效率很搞，所以把最大值，最小值分开查询，提高执行效率
5、其他写法
SQL> select
(select /*+ index_asc(t_a t_a_ind) */ object_id from t_a where rownu
m=1) min
,(select /*+ index_desc(t_a t_a_ind) */ object_id from t_a where
rownum=1)
max from dual;
执行计划
———————————————————-
Plan hash value:
674626822
—————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:
00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | INDEX FULL SCAN | T_A_IND | 78093 | 991K| 2 (0)| 00:
00:01 |
|* 3 | COUNT STOPKEY | | | | |
| 4 | INDEX FULL SCAN DESCENDING| T_A_IND | 78093 | 991K| 2 (0)| 00:
00:01 |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
—————————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – filter(ROWNUM=1)
3 – filter(ROWNUM=1)
Note—— dynamic sampling used for this statement (level=2)
统计信息
———————————————————-
0 recursive calls
0 db block gets
4
consistent gets
0 physical reads
0 redo size
480 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
注意：
1）这个要正确执行，需要一个前提条件object_id这列要为not
null限制条件，不然会hint提示无效
2）从执行计划的统计信息上看，这个和INDEX FULL SCAN
(MIN/MAX)方式的执行效率一样