SUM優化（複合索引）

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

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

| 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
          
總結：
1.NULL值在ORACLE中是一個神奇的值。所以想使用索引，定要考慮此列的值是否為空。
2.複合索引在一定情況下是可以優化有NULL值的列（複合索引所有列值都為NULL時，複合索引也不會被使用，因為這行的索引值不會被儲存）。