SUM优化（复合索引）

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

--------------------------------------------------------------------------------
----

--------------------------------------------------------------------------------
----

| 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

总结：
1.NULL值在ORACLE中是一个神奇的值。所以想使用索引，定要考虑此列的值是否为空。
2.复合索引在一定情况下是可以优化有NULL值的列（复合索引所有列值都为NULL时，复合索引也不会被使用，因为这行的索引值不会被存储）。

ITPub博客

Oracle 优化之SQL（复合索引）

最新文章