Oracle中利用函数索引处理数据倾斜案例
http://blog.itpub.net/17203031/viewspace-681311/
关于B-Tree、Bitmap、函数索引的相关内容请参考另一篇博文：
Oracle中B-Tree、Bitmap和函数索引使用案例总结
通常来说，索引选取的数据列最好为分散度高、选择性好。从索引树结构的角度看，列值都是分布在叶节点位置。这样，通过树结构搜索得到的叶节点数量效率比较高。
实际中，我们常常遇到数据列值倾斜的情况。就是说，整个列数据取值有限。但是大部分数据值都集中在少数一两个取值里，其他取值比例极少。比如：一个数据列值有“N”、“B”、“M”、“P”、“Q”几个取值，其中55%数据行取值为“N”，40%数据行取值为“B”，剩下的取值分布在5%的数据行中。对于这种结构的数据列加索引，是存在一些问题的。
首先，默认数据库是会为所有的列值（非空）建立索引结构。也就意味着无论是高频度取值，还是低频度取值，都会在索引结构的叶节点上出现。当然，这样的大部分叶节点都是这些重复值。
其次，在CBO（基于成本优化器）的作用下，对高频度取值的搜索一般都不会选择索引作为搜索路径，因为进行全表扫描可能效率更高。我们为数据列建立了索引，但高频词的查询永远不会走到索引路径（下面的实验会证明这一点）。
最后，建立的索引空间和时间消耗比较大。建立的索引涵盖所有取值，对海量数据表而言，占有的空间势必较大。同时，在进行小频度数据查询的时候，虽然会去走索引路径，但是引起的逻辑物理读也是有一些损耗。
下面引入一个解决方法
思路：既然高频度值在查询的时候不会走到索引路径，可以考虑将其剔出构建索引的过程，只为那些低频度数据值建立索引结构。这样，建立的索引树结构相对较小，而且索引查询的效率也能提升。
具体的方法是使用decode函数。decode(a,b,c,d,e…f)含义：如果a=b，则返回c,等于d,返回e,最后没有匹配的情况下，返回f。针对上面的例子，可以使用decode(列名，‘N’, null,‘B’,null,列名)，含义是，如果该列取值为N或者B，直接设置为null，否则才返回列值，并且以此建立函数索引。
这样做借助了Oracle两个功能：1、对null值不生成索引；2、函数索引；
下面通过实验来证明该方法：
1、构建实验测试环境
--创建测试数据表tb_wjq
SEIANG@seiang11g>create table tb_wjq as select * from dba_objects where owner
in ('SEIANG','PUBLIC','HR','SYSMAN','XDB','BI','SYS');
Table created.
--使用脚本插入大量数据
begin
for i in 1..8 loop
insert /*+ append */ into tb_wjq select * from tb_wjq;
commit;
end loop;
end;
/
SEIANG@seiang11g>select count(*) from tb_wjq;
COUNT(*)
----------
9804160
Elapsed: 00:00:01.54
--用于实验的数据量分布情况
SEIANG@seiang11g>select owner,count(*) from tb_wjq group by owner;
OWNER COUNT(*)
------------------------------ ----------
SEIANG 3072
PUBLIC 4352256
HR 4352
SYSMAN 454912
XDB 149760
BI 1024
SYS 4838784
7 rows selected.
Elapsed: 00:00:02.44
可以看到，九万多条数据，绝大部分数据集中到了PUBLIC、SYSMAN、SYS上，其他数据取值频数较小。数据倾斜趋势明显。
2、建索引
--分别对owner列建立常规、函数索引。
SEIANG@seiang11g>create index idx_tb_wjq_owner_normal on tb_wjq(owner);
Index created.
Elapsed: 00:00:24.72
SEIANG@seiang11g>create index func_idx_tb_wjq_owner on tb_wjq(decode
(owner,'PUBLIC',null,'SYSMAN',null,'SYS',null,owner));
Index created.
Elapsed: 00:00:12.34
索引func_idx_tb_wjq_owner将PUBLIC、SYSMAN、SYS值转化为null，剔出了建立索引的过程。不仅可以从上面创建索引所用的时间可以看出，而且从下面的索引段信息看，两个索引所占的空间差异比较大，也证明了这点。
SEIANG@seiang11g>select
owner,segment_name,segment_type,bytes/1024/1024,blocks,extents from
dba_segments where segment_name='IDX_TB_WJQ_OWNER_NORMAL';
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024 BLOCKS
EXTENTS
--------------- ------------------------------ ------------------
--------------- ---------- ----------
SEIANG IDX_TB_WJQ_OWNER_NORMAL INDEX 184 23552 94
Elapsed: 00:00:00.01
SEIANG@seiang11g>select
owner,segment_name,segment_type,bytes/1024/1024,blocks,extents from
dba_segments where segment_name='FUNC_IDX_TB_WJQ_OWNER';
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024 BLOCKS
EXTENTS
--------------- ------------------------------ ------------------
--------------- ---------- ----------
SEIANG
FUNC_IDX_TB_WJQ_OWNER INDEX 3 384 18
由上可以看出，同样是对一个数据列加索引。普通索引类型IDX_TB_WJQ_OWNER_NORMAL占据94个区，23552个数据块，空间约占184M。而函数索引FUNC_IDX_TB_WJQ_OWNER的空间只用了初始分配的18个区，384个数据块，空间约占3M。由此，空间优势直观体现！
--收集统计数据，由于是实验性质，而且数据量大，采用高采样率收集统计信息。
SEIANG@seiang11g>exec dbms_stats.gather_table_stats('SEIANG', 'TB_WJQ',
cascade => true, estimate_percent => 100,method_opt => 'for all
indexed columns');
PL/SQL procedure successfully completed.
Elapsed: 00:00:49.67
3、检索效率分析
针对owner数据量149760的XDB取值进行分析。
--直接索引搜索：
SEIANG@seiang11g>select * from tb_wjq where owner='XDB';
149760 rows selected.
Elapsed: 00:00:01.89
Execution Plan
----------------------------------------------------------
Plan hash value: 3735191644
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 149K|
13M| 4708 (1)| 00:00:57 |
| 1 |
TABLE ACCESS BY INDEX ROWID| TB_WJQ | 149K|
13M| 4708 (1)| 00:00:57 |
|* 2 | INDEX RANGE SCAN | IDX_TB_WJQ_OWNER_NORMAL | 149K| |
348 (1)| 00:00:05 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='XDB')
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
26000
consistent gets
6349 physical reads
0 redo size
16482673 bytes sent via SQL*Net to client
110336 bytes received via SQL*Net from client
9985 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
149760 rows processed
发现采用BI作为搜索值时，是进行了索引搜索。下面是用函数索引搜索进行对比。
SEIANG@seiang11g>select * from tb_wjq where decode
(owner,'PUBLIC',null,'SYSMAN',null,'SYS',null,owner)='XDB';
149760 rows selected.
Elapsed: 00:00:01.54
Execution Plan
----------------------------------------------------------
Plan hash value: 3652333940
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 149K|
13M| 7091 (1)| 00:01:26 |
| 1 |
TABLE ACCESS BY INDEX ROWID| TB_WJQ | 149K|
13M| 7091 (1)| 00:01:26 |
|* 2 | INDEX
RANGE SCAN |
FUNC_IDX_TB_WJQ_OWNER |
149K| | 316
(1)| 00:00:04 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access(DECODE("OWNER",'PUBLIC',NULL,'SYSMAN',NULL,'SYS',NULL,"OWNER")='XDB')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
25998
consistent gets
0 physical reads
0 redo size
9017261 bytes sent via SQL*Net to client
110336 bytes received via SQL*Net from client
9985 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
149760 rows processed
对比后，我们可以发现，使用函数索引的方法，在执行时间、物理逻辑读、CPU使用上有一定差异。
|
|
普通索引 |
函数索引 |
|
执行时间 |
00: 00: 01.89 |
00: 00: 01.54 |
|
CPU使用 |
4708 |
7091 |
|
consistent gets |
26000 |
25998 |
|
physical reads |
6349 |
0 |
结论：使用函数索引处理偏值方法，在一定长度上优化查询效率和索引结构。上表的数据表明，会使逻辑物理读的消耗很大程度的减少（索引结构简化），同时连带影响执行时间的缩小。因为使用函数要进行计算，CPU使用率相对较高，在可以接受的范围内。
但是，这种方法是存在一些限制的，应用前一定要仔细规划。
首先，数据表数据要保证较大。因为毕竟函数索引的建立和搜索较普通索引消耗大，如果数据表小，带来的优化程度不能弥补消耗的成本，结果可能得不偿失。笔者进行的一系列实验中，也发现在数据量中等偏小时，这种性能优势不能凸显。
其次，列值倾斜趋势明显。通过开篇的讨论我们不难发现，列值倾斜的程度越高，使用函数索引剔出的数据量也就越大，生成的索引树结构也就越小越优化。这一点是本方法的核心！
最后，使用函数索引搜索时，搜索的取值频数越高，优化效果越好。在本例中，取值XDB的列有149760行，可以看出明显的性能优化。但是当我们选择值有1024条数据的BI值时，这种优化趋势可以看到，但是明显程度降低（实验结果如下所示）。这里的原因可能是数据量小时，两种方法逻辑物理读的差异度缩小。
--直接索引
SEIANG@seiang11g>select * from tb_wjq where owner='BI';
1024 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3735191644
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1024 |
98K| 35 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_WJQ | 1024 |
98K| 35 (0)| 00:00:01 |
|* 2 | INDEX
RANGE SCAN |
IDX_TB_WJQ_OWNER_NORMAL |
1024 | | 5
(0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='BI')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
280 consistent gets
151 physical reads
0 redo size
98579 bytes sent via SQL*Net to client
1271 bytes received via SQL*Net from client
70 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1024 rows processed
--函数索引
SEIANG@seiang11g>select * from tb_wjq where decode
(owner,'PUBLIC',null,'SYSMAN',null,'SYS',null,owner)='BI';
1024 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3652333940
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1024 |
98K| 50 (0)| 00:00:01 |
| 1 |
TABLE ACCESS BY INDEX ROWID| TB_WJQ | 1024 |
98K| 50 (0)| 00:00:01 |
|* 2 | INDEX
RANGE SCAN |
FUNC_IDX_TB_WJQ_OWNER | 1024
| | 3
(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access(DECODE("OWNER",'PUBLIC',NULL,'SYSMAN',NULL,'SYS',NULL,"OWNER")='BI')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
279
consistent gets
3 physical reads
0 redo size
33969 bytes sent via SQL*Net to client
1271 bytes received via SQL*Net from client
70 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1024 rows processed
但是如果是对SYS、PUBLIC或SYSMAN进行查询时，将会跳过所有的索引，直径进行全表扫描。
SEIANG@seiang11g>select * from tb_wjq where owner='SYS';
4838784 rows selected.
Elapsed: 00:00:45.85
Execution Plan
----------------------------------------------------------
Plan hash value: 1501781665
----------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 4838K| 452M| 39893
(1)| 00:07:59 |
|* 1 | TABLE
ACCESS FULL| TB_WJQ |
4838K| 452M| 39893 (1)| 00:07:59 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
457638 consistent gets
139684 physical reads
0 redo size
255169095 bytes sent via SQL*Net to client
3548958 bytes received via SQL*Net from client
322587 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4838784 rows processed
作者：SEian.G（苦练七十二变，笑对八十一难）