SUM聚合函数优化方法：
SUM列无NULL值 => SUM列增加索引 => INDEX FAST FULL SCAN
SUM列有NULL值 => SUM列增加复合索引(column,1) => INDEX FAST FULL SCAN 复合索引优化SUM聚合函数注意事项：
NULL值虽然不影响SUM结果，但是NULL值会影响索引的使用（因为NULL值列的索引值不会被存储），复合索引列值不全为NULL时，NULL值可以被存储，也就是这个时候可以使用索引
DOUDOU@doudou1> drop table test1 purge;
Table dropped.
DOUDOU@doudou1> create table test1 as select * from user_objects;
Table created.
DOUDOU@doudou1> insert into test1(object_id) values('');
1 row created.
--object_id is null values
DOUDOU@doudou1> select count(*) from test1 where object_id is null;
COUNT(*)
----------
20480
--object_id is not null values
DOUDOU@doudou1> select count(*) from test1 where object_id is not null;
COUNT(*)
----------
262144
DOUDOU@doudou1> select sum(object_id) from test1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3896847026
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 821 (1)| 00:00:10 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| TEST1 | 258K| 3281K| 821 (1)| 00:00:10 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
3036 consistent gets
1 physical reads
0 redo size
537 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
DOUDOU@doudou1> create index idx_doudou on test1 (object_id,1);
Index created.
DOUDOU@doudou1> set autot trace
DOUDOU@doudou1> select sum(object_id) from test1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2562788052
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 13 | 209 (1)| 00:00:
03 |
| 1 | SORT AGGREGATE | | 1 | 13 | |
|
| 2 | INDEX FAST FULL SCAN| IDX_DOUDOU | 258K| 3281K| 209 (1)| 00:00:
03 |
--------------------------------------------------------------------------------
----
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
820 consistent gets
741 physical reads
0 redo size
537 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed