SUM/AVG的优化
drop table t purge;
create table t as select * from dba_objects;
create index idx1_object_id on t(object_id);
set autotrace on
set linesize 1000
set timing on
select sum(object_id) from t;
执行计划
—————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 13 | 49 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FAST FULL SCAN| IDX1_OBJECT_ID | 92407 | 1173K| 49 (0)| 00:00:01 |
—————————————————————————————-
统计信息
———————————————————-
0 recursive calls
0 db block gets
170 consistent gets
0 physical reads
0 redo size
432 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
–比较一下假如不走索引的代价，体会一下这个索引的重要性
select /*+full(t)*/ sum(object_id) from t;
SUM(OBJECT_ID)
————–
2732093100
执行计划
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 13 | 292 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| T | 92407 | 1173K| 292 (1)| 00:00:04 |
—————————————————————————
统计信息
———————————————————-
0 recursive calls
0 db block gets
1047 consistent gets
0 physical reads
0 redo size
432 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
–起来类似的比如AVG，和SUM是一样的，如下：
select avg(object_id) from t;
AVG(OBJECT_ID)
————–
37365.5338
执行计划
—————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 13 | 49 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FAST FULL SCAN| IDX1_OBJECT_ID | 92407 | 1173K| 49 (0)| 00:00:01 |
—————————————————————————————-
统计信息
———————————————————-
0 recursive calls
0 db block gets
170 consistent gets
0 physical reads
0 redo size
448 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
–不知大家注意到没，这里的试验已经告诉我们了，OBJECT_ID列是否为空，也不影响SUM/AVG等聚合的结果。
