马上注册,结交更多数据大咖,获取更多知识干货,轻松玩转大数据
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
本帖最后由 dbadoudou 于 2014-8-25 19:51 编辑
复合索引优化(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时,复合索引也不会被使用,因为这行的索引值不会被存储)。
|