oracle 索引介绍（二）

      索引也可以像表分区结构那样进行分区。有多种方法可以对索引进行分区。在分区表上可以创建局部或全局索引。并且，有多种分区方案可选，例如范围分区、散列分区、列表分区以及混合分区方案。自ORACLE数据库10g版本以来，也可以在非分区表上建立分区索引。

 

• 局部索引（TABLE ACCESS BY LOACL INDEX ROWID）

 

     局部分区索引使用LOCAL关键字来建立，其分区边界与表相同。简单来说，与每个表分区相联结的有一个索引分区。因为维护操作可以在独立分区级进行，表的可用性更好。对索引分区的维护操作仅需要锁定相应的表分区而不是整张表。尽管表的可用性很重要，你仍然应该考虑另外一点：如果谓语没有声明分区键列，那么在局部索引中必须访问所有索引分区以识别候选的数据行。如果分区数非常多，达到几千的量级的话，这有可能会导致性能问题的现出。即使这样，你也需要衡量创建局部索引而不是全局索引所带来的影响。

 

• 全局索引(TABLE ACCESS BY GLOBAL INDEX ROWID)

 

      全局索引通过关键字GLOBAL来创建。在全局索引中，索引的分区边界与表的分区边界不一定要匹配，并且表和索引的分区键也可以不一样。对全局索引的任何维护都将需要获得表的较高层级的锁，从而降低了应用的可用性。相反，对于局部索引的维护可以只在分区级上完成，只会影响相应的表分区。

 

• 散列分区与范围分区

 

     在散列分区方案中，分区键列的值使用散列算法进行散列化来确定存储数据行的分区。这种类型的分区方案适合于分区列使用人造键值填充的情况，例如分区列由顺序生成的值填充。如果列值的分布是均匀的，那么每个分区将会存储几乎相等数目的数据行。散列分区方案还有几个额外的优点，范围分区方案有一些管理开支，因为在将来要想放入新的数据行就需要定期加入新的分区。例如，如果分区键是ORDER_DATE列，那么就必须增加新分区来放放将来日期的数据。而在散列分区方案中，这个开支就避免了，因为数据行在使用散列算法进行分区的各个分区上是均匀分布的。如果列值是均匀分布的，则所有分区将保存近似相等数量的数据行，也就没有理由定期增加更多新的分区了。

 

      散列分区表和索引在应对由唯一索引和主键索引所引起的与并发性相关的性能问题时是非常有效的。典型的主键列可能会使用生成的顺序序列值来填充。因为索引按照排序后的顺序存储列值，新数据行的列值将会进入到索引最右侧的叶子块。在该叶子块满了以后，接下来插入的数据行就会进入新的最右侧叶子块中，资源争夺点也就从一个叶子块转移到了另一个叶子块。随着表的插入并发性增加，会话将会大幅修改索引的最右侧块。基本上，索引的当前最右侧叶子块将会是最主要的资源争夺点。你将会看到会话等待事件，例如缓冲区繁忙等待。在RAC中，由于全局缓存通信成本导致了这个问题的放大，GC缓冲器繁忙将会是排在第一位的等待事件。这种类型的最右侧索引的快速增长被称为"右侧增长索引"。与右侧增长索引相关的并发性问题可以通过将索引散列分区到多个分区表中来消除。例如，如果索引被散列化到32个分区中，那么插入操作将会被有效地分散到32个最右侧叶子块中，因为有32个索引树。使用散列分区方案来对表进行分区，然后再在分区后的表上创建局部索引也将具有同样的效果。

 

查看散列分区分布：

select dbms_rowid.rowid_object(rowid) obj_id, count(*)
  from test
 group by dbms_rowid.rowid_object(rowid);

 

      从本质上来说，通过对表进行分区并将右侧增长索引创建为局部索引可以增加并发性。如果表不能进行分区，那么也可以单独对索引使用散列分区方案进行分区来解决性能问题。

 

• 压缩索引

 

      压缩索引是常规B-树索引的变体。这种类型的索引更适合于引导列中具有重复值的列。通过将引导列中的重复值在索引叶子块中仅保存一次来实现压缩。数据行区的指针指向这些前置行，避免在数据行显式存储这些重复值。如果列具有很多重复值的话，与常规的索引相比压缩索引可能小很多。在处理压缩索引的时候CPU使用率可能会略有上升，这可以很安全地忽略。

 

select 'compressed index size(MB) :' || trunc(bytes / 1024 / 1024, 2)
  from user_segments
 where segment_name = 'T';
 
 create index t_ind_bill_month on t(bill_month) compress 1;
 
 select 'compressed index size(MB) :' || trunc(bytes / 1024 / 1024, 2)
  from user_segments
 where segment_name = 'T';

 

很明显，数据自身的特点对压缩比起着非常重要的作用。如果列值重复的次数越高，则索引压缩就能得到越多的益处。如果没有重复数据，则压缩索引可能比常规索引还要大。因此，压缩索引适合于引导列具有较少唯一值的索引。dba_indexes/user_indexes视图中的compression和prefix_length列显示了索引的压缩属性。

 

最优压缩列数:

analyze index t_ind_bill_month validate structure;
select opt_cmpr_count, opt_cmpr_pctsave
  from index_stats
 where name = upper('t_ind_bill_month');

 注意：analyze index validate structure语句需要对表的共享级锁，可能会引起应用停机。

 

• 基于函数的索引

 

       如果一个谓语在索引列上应用了函数，则优化器不会选用该列上的索引。例如，对谓语TO_CHAR(ID)='100'，不会选用ID列上的索引，因为在索引列上应用了TO_CHAR函数。这个限制可以通过在表在式TO_CHAR(ID)上创建基于函数的索引来克服。基于函数的索引预存函数的结果。谓语中所声明的表达式必须与基于函数的索引所声明的表达式相匹配。基于函数的索引也可以建立在用户自定义的函数上，但这个函数必须定义为确定性函数，也就是说对这个函数的每一次执行必须返回一致的值。不遵守这一规则的用户自定义函数不能用来创建基于函数的索引。

 

虚拟列与基于函数的索引
select data_default, hidden_column, virtual_column
  from dba_tab_cols
 where table_name = 'T'
   and virtual_column = 'YES';

select index_name, column_name
  from dba_ind_columns
 where index_name = 'T_IND_BILLNO';

 

在增加了基于函的索引后收集表的统计信息是很重要的。如果不收集，新的虚拟列就没有统计信息，这有可能会导致性能异常。

分析脚本：
SQL> begin
  2  dbms_stats.gather_table_stats(
  3  ownname => user,
  4  tabname => 'T',
  5  estimate_percent => 30,
  6  cascade => true );
  7  end;
  8  /

 

• 反转键索引

 

      反转键索引是解决右侧增长索引相关性能问题的另一个选项。在反转键索引中，列值按照逐个字符的反向顺序存储。例如，列值12345在索引中存储为54321。因为列值是按照反向顺序存储的，连续的列值将会存储在不同的索引叶子块中，从而避免了右侧增长索引所带来的资源争夺问题。但是，在表数据块中，这些列值还是存储为12345的。

 

反转键索引有两个问题：

     1.反转键索引的范围扫描不能使用范围运算符，如BETWEEN,<,>等。这是可以理解的，因为索引范围扫描的基本假设就是列值按照逻辑键升序或降序来存储。反转键索引由于列值按照反转顺序存储，没有按照逻辑的顺序来维护违反了这个假设。因此索引范围扫描不适用于反转键索引。

     2.反转键索引可能会人为地增加物理读取的次数，因为列值被存储在很多个叶子块中，而这些叶子块可能需要读取到缓冲区缓存中来修改块。但是，这个I/O成本的增加需要与右侧增长索引所引起的并发性问题相对照一衡量。

 

create unique index t_ind_billmonth on t(bill_month) global reverse;

      在RAC环境中，右侧增长索引可能会引起不容忽视的性能问题。反转键索引被引入来解决性能问题。但有时候你可能应该考虑散列分区索引而不是反转键索引。

 

• 降序索引

 

     索引默认按照升序存储列值，但可以通过使用降序索引来切换为降序存储。如果你的应用按照特定的顺序来获取数据，则在数据行被发送给应用之前需要进行排序。通过降序索引可以避免这个排序。如果应用按照某个特定的顺序上百万次地获取数据，则这类索引是非常有用的。

 

create index t_ind on t(a desc,b desc,c desc) ;

 

• 不可见索引

 

      在某些场景下，你可能需要增加一个索引来对SQL语句的性能进行调优，但你不太确定索引所带来的负面影响。不可见索引在以较小的风险来衡量新索引所带来的影响方面非常有用。一个索引可以加入到数据库中并被标记为不可见，这样优化器就不会选用这个索引。可以在确定某个索引没有负面影响或对执行计划没有负面影响后将它标记为可见。在数据库中加入索引以后，你可以在会话中将optimizer_use_invisible_indexes参数设置为true,这样不会影响应用性能。

alter index t_ind invisible ;
alter session set optimizer_use_invisible_indexes = true;

 不可见索引还有另一个应用场景。这种索引有助于在删除不使用的索引时用来降低风险。

 

 

• 虚拟索引

 

      虚拟索引对于查看索引的有效性是很有用的。虚拟索引不会分配存储空间，因此可以很快建立。虚拟索引与不可见索引的不同之处在于不可见索引是有与之相关的存储的，只是优化器不能选择它们。而虚拟索引没有与之关联的存储空间。由于这个原因，虚拟索引也被称为无段索引。会话可修改的一个下划线参数_use_nosegment_indexes控制了优化器是否可以考虑选择虚拟索引。这个参数的默认值是false，应用不会选择虚拟索引。

 

create index t_ind on t(a desc,b desc,c desc) nosegment ;

alter session set '_use_nosegment_indexes' = true; 

      虚拟索引没有与之相关的存储，因此这些索引不需要进行维护。但你可以像常规索引那样收集这些索引的统计信息。虚拟索引可以用来改进谓语的基数估计而不增加与常规索引相关的存储成本。

 

• 位图联结索引

      位图联结索引对于数据仓库应用中物化事实表和维度表之间的联结是很有用的。在数据仓库中，一般来说，事实表比维度表要大得多，并且维度和事实表使用主键进行联结 ，在它们之间存在外键关系。这种联结的成本由于事实表很大而更高。如果能够预先存储联结结果则这些查询的性能就会得到提高。物化视图是预先计算联结结果的可选这一，位图联结索引是另一个可选项。

create bitmap index sales_bji1 on sales(p.prod_name,c.cust_first_name,ch.channel_desc) from 
sales s,products p,customers c,channel ch 
where s.prod_id=p.prod_id and s.cust_id=c.cust_id and s.channel_id=ch.channel_id;