网站建设微信端,简洁型网页,中国十大it外包公司,网站解析查询关于B-Tree、Bitmap、函数索引的相关内容请参考另一篇博文#xff1a;Oracle中B-Tree、Bitmap和函数索引使用案例总结通常来说#xff0c;索引选取的数据列最好为分散度高、选择性好。从索引树结构的角度看#xff0c;列值都是分布在叶节点位置。这样#xff0c;通过树结构…关于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)含义如果ab则返回c,等于d,返回e,最后没有匹配的情况下返回f。针对上面的例子可以使用decode(列名‘N’, null,‘B’,null,列名)含义是如果该列取值为N或者B直接设置为null否则才返回列值并且以此建立函数索引。这样做借助了Oracle两个功能1、对null值不生成索引2、函数索引下面通过实验来证明该方法1、构建实验测试环境--创建测试数据表tb_wjqSEIANGseiang11gcreate table tb_wjq as select * from dba_objects where ownerin (SEIANG,PUBLIC,HR,SYSMAN,XDB,BI,SYS);Table created.--使用脚本插入大量数据beginfor i in 1..8 loopinsert /* append */ into tb_wjq select * from tb_wjq;commit;end loop;end;/SEIANGseiang11gselect count(*) from tb_wjq;COUNT(*)----------9804160Elapsed: 00:00:01.54--用于实验的数据量分布情况SEIANGseiang11gselect owner,count(*) from tb_wjq group by owner;OWNER COUNT(*)------------------------------ ----------SEIANG 3072PUBLIC 4352256HR 4352SYSMAN 454912XDB 149760BI 1024SYS 48387847 rows selected.Elapsed: 00:00:02.44可以看到九万多条数据绝大部分数据集中到了PUBLIC、SYSMAN、SYS上其他数据取值频数较小。数据倾斜趋势明显。2、建索引--分别对owner列建立常规、函数索引。SEIANGseiang11gcreate index idx_tb_wjq_owner_normal on tb_wjq(owner);Index created.Elapsed: 00:00:24.72SEIANGseiang11gcreate 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剔出了建立索引的过程。不仅可以从上面创建索引所用的时间可以看出而且从下面的索引段信息看两个索引所占的空间差异比较大也证明了这点。SEIANGseiang11gselectowner,segment_name,segment_type,bytes/1024/1024,blocks,extents fromdba_segments where segment_nameIDX_TB_WJQ_OWNER_NORMAL;OWNER SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024 BLOCKSEXTENTS--------------- ------------------------------ --------------------------------- ---------- ----------SEIANG IDX_TB_WJQ_OWNER_NORMAL INDEX 184 23552 94Elapsed: 00:00:00.01SEIANGseiang11gselectowner,segment_name,segment_type,bytes/1024/1024,blocks,extents fromdba_segments where segment_nameFUNC_IDX_TB_WJQ_OWNER;OWNER SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024 BLOCKSEXTENTS--------------- ------------------------------ --------------------------------- ---------- ----------SEIANGFUNC_IDX_TB_WJQ_OWNER INDEX 3 384 18由上可以看出同样是对一个数据列加索引。普通索引类型IDX_TB_WJQ_OWNER_NORMAL占据94个区23552个数据块空间约占184M。而函数索引FUNC_IDX_TB_WJQ_OWNER的空间只用了初始分配的18个区384个数据块空间约占3M。由此空间优势直观体现--收集统计数据由于是实验性质而且数据量大采用高采样率收集统计信息。SEIANGseiang11gexec dbms_stats.gather_table_stats(SEIANG, TB_WJQ,cascade true, estimate_percent 100,method_opt for allindexed columns);PL/SQL procedure successfully completed.Elapsed: 00:00:49.673、检索效率分析针对owner数据量149760的XDB取值进行分析。--直接索引搜索SEIANGseiang11gselect * from tb_wjq where ownerXDB;149760 rows selected.Elapsed: 00:00:01.89Execution 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(OWNERXDB)Statistics----------------------------------------------------------8 recursive calls0 db block gets26000consistent gets6349 physical reads0 redo size16482673 bytes sent via SQL*Net to client110336 bytes received via SQL*Net from client9985 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)149760 rows processed发现采用BI作为搜索值时是进行了索引搜索。下面是用函数索引搜索进行对比。SEIANGseiang11gselect * from tb_wjq where decode(owner,PUBLIC,null,SYSMAN,null,SYS,null,owner)XDB;149760 rows selected.Elapsed: 00:00:01.54Execution 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 |INDEXRANGE 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 calls0 db block gets25998consistent gets0 physical reads0 redo size9017261 bytes sent via SQL*Net to client110336 bytes received via SQL*Net from client9985 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)149760 rows processed对比后我们可以发现使用函数索引的方法在执行时间、物理逻辑读、CPU使用上有一定差异。普通索引函数索引执行时间00: 00: 01.8900: 00: 01.54CPU使用47087091consistent gets2600025998physical reads63490结论使用函数索引处理偏值方法在一定长度上优化查询效率和索引结构。上表的数据表明会使逻辑物理读的消耗很大程度的减少(索引结构简化)同时连带影响执行时间的缩小。因为使用函数要进行计算CPU使用率相对较高在可以接受的范围内。但是这种方法是存在一些限制的应用前一定要仔细规划。首先数据表数据要保证较大。因为毕竟函数索引的建立和搜索较普通索引消耗大如果数据表小带来的优化程度不能弥补消耗的成本结果可能得不偿失。笔者进行的一系列实验中也发现在数据量中等偏小时这种性能优势不能凸显。其次列值倾斜趋势明显。通过开篇的讨论我们不难发现列值倾斜的程度越高使用函数索引剔出的数据量也就越大生成的索引树结构也就越小越优化。这一点是本方法的核心最后使用函数索引搜索时搜索的取值频数越高优化效果越好。在本例中取值XDB的列有149760行可以看出明显的性能优化。但是当我们选择值有1024条数据的BI值时这种优化趋势可以看到但是明显程度降低(实验结果如下所示)。这里的原因可能是数据量小时两种方法逻辑物理读的差异度缩小。--直接索引SEIANGseiang11gselect * from tb_wjq where ownerBI;1024 rows selected.Elapsed: 00:00:00.03Execution 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 |INDEXRANGE SCAN |IDX_TB_WJQ_OWNER_NORMAL|1024 | | 5(0)| 00:00:01 |-------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access(OWNERBI)Statistics----------------------------------------------------------0 recursive calls0 db block gets280 consistent gets151 physical reads0 redo size98579 bytes sent via SQL*Net to client1271 bytes received via SQL*Net from client70 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1024 rows processed--函数索引SEIANGseiang11gselect * from tb_wjq where decode(owner,PUBLIC,null,SYSMAN,null,SYS,null,owner)BI;1024 rows selected.Elapsed: 00:00:00.01Execution 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 |INDEXRANGE 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 calls0 db block gets279consistent gets3 physical reads0 redo size33969 bytes sent via SQL*Net to client1271 bytes received via SQL*Net from client70 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1024 rows processed但是如果是对SYS、PUBLIC或SYSMAN进行查询时将会跳过所有的索引直径进行全表扫描。SEIANGseiang11gselect * from tb_wjq where ownerSYS;4838784 rows selected.Elapsed: 00:00:45.85Execution Plan----------------------------------------------------------Plan hash value: 1501781665----------------------------------------------------------------------------| Id | Operation | Name | Rows| Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT || 4838K| 452M| 39893(1)| 00:07:59 ||* 1 |TABLEACCESS FULL| TB_WJQ|4838K| 452M| 39893 (1)| 00:07:59 |----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter(OWNERSYS)Statistics----------------------------------------------------------8 recursive calls0 db block gets457638 consistent gets139684 physical reads0 redo size255169095 bytes sent via SQL*Net to client3548958 bytes received via SQL*Net from client322587 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)4838784 rows processed作者SEian.G(苦练七十二变笑对八十一难)
