[Oracle] 索引(续)

古城微笑少年丶 2022-07-10 23:59 142阅读 0赞

Oracle其它类型的索引

上一篇已经了解过了B树索引和位图索引。

下面了解下oracle中其它的索引。

HASH索引 (**Hash聚集索引**)

一个二元高度为4的B树索引, Oracle通过执行一个HASH函数将一个确定的值进行转化,然后匹配HASH列.

**在“散列聚簇表（create cluster）”或分区表（**参见“\[Oralce\] 分区表和索引”**）中才能创建。**

<table style="table-layout: fixed; width: 95%; border: #cccccc 1px dotted;"> 
 <tbody> 
  <tr> 
   <td style="WORD-WRAP: break-word"> <p>SQL&gt;&nbsp;create cluster hash_cluster<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(hash_key number)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;hashkeys 1000<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;size 8192;</p> <p>SQL&gt;&nbsp;create&nbsp;index idx_hash_cluster on cluster hash_cluster;</p> </td> 
  </tr> 
 </tbody> 
</table>

HASH索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。集群键上不同值的数目必须在创建HASH集群之前就要知道。需要在创建HASH集群的时候指定这个值。低估了集群键的不同值的数字可能会造成集群的冲突(两个集群的键值拥有相同的HASH值)。这种冲突是非常消耗资源的。冲突会造成用来存储额外行的缓冲溢出，然后造成额外的I/O。如果不同HASH值的数目已经被低估，您就必须在重建这个集群之后改变这个值。ALTER CLUSTER命令不能改变HASH键的数目。

HASH集群还可能浪费空间。如果无法确定需要多少空间来维护某个集群键上的所有行，就可能造成空间的浪费。如果不能为集群的未来增长分配好附加的空间，HASH集群可能就不是最好的选择。

如果应用程序经常在集群表上进行全表扫描，HASH集群可能也不是最好的选择。由于需要为未来的增长分配好集群的剩余空间量，全表扫描可能非常消耗资源。

**索引组织表(index organized table)**

索引组织表会把表的存储结构改成B树结构，以表的主键进行排序。这种特殊的表和其他类型的表一样， 可以在表上执行所有的DML和DDL语句。由于表的特殊结构，ROWID并没有被关联到表的行上。

对于一些涉及精确匹配和范围搜索的语句，索引组织表提供了一种基于键的快速数据访问机制。基于主键值的UPDATE和DELETE语句的性能也同样得以提高，这是因为行在物理上有序。由于键列的值在表和索引中都没有重复，存储所需要的空间也随之减少。

如果不会频繁地根据主键列查询数据，则需要在索引组织表中的其他列上创建二级索引。不会频繁根据主键查询表的应用程序不会了解到使用索引组织表的全部优点。对于总是通过对主键的精确匹配或范围扫描进行访问的表，就需要考虑使用索引组织表。

**反转键索引**

当载入一些有序数据时，索引肯定会碰到与I/O相关的一些瓶颈。在数据载入期间，某部分索引和磁盘肯定会比其他部分使用频繁得多。为了解决这个问题，可以把索引表空间存放在能够把文件物理分割在多个磁盘上的磁盘体系结构上。

为了解决这个问题，Oracle还提供了一种反转键索引的方法。如果数据以反转键索引存储，这些数据的值就会与原先存储的数值相反。这样，数据1234、1235和1236就被存储成4321、5321和6321。结果就是索引会为每次新插入的行更新不同的索引块。

不能对位图索引和索引组织表进行反转键处理。

技巧：

如果您的磁盘容量有限，同时还要执行大量的有序载入，就可以使用反转键索引。

不可以将反转键索引与位图索引或索引组织表结合使用。

**基于函数的索引**

可以在表中创建基于函数的索引。如果没有基于函数的索引，任何在列上执行了函数的查询都不能使用这个列的索引。

<table style="table-layout: fixed; width: 95%; border: #cccccc 1px dotted;"> 
 <tbody> 
  <tr> 
   <td style="WORD-WRAP: break-word"> <p>SQL&gt;&nbsp;select count(*)&nbsp; from&nbsp;sample&nbsp; where&nbsp;&nbsp;ratio(balance,limit) &gt;.5;&nbsp;&nbsp; -- sample表中有100万条记录</p> <p>Elapsed time: 20.1 minutes</p> <pre>SQL&gt;&nbsp;create index ratio_idx1 on sample (ratio(balance, limit));</pre> <pre>SQL&gt;&nbsp;select&nbsp;&nbsp;count(*) from&nbsp;&nbsp;sample where&nbsp;&nbsp;ratio(balance,limit) &gt;.5;&nbsp;<p>Elapsed time: 7 seconds!!!</p></pre> </td> 
  </tr> 
 </tbody> 
</table>

尽管基于函数的索引非常有用，但在建立它们之前必须先考虑下面一些问题：

能限制在这个列上使用的函数吗？如果能，能限制所有在这个列上执行的所有函数吗？

是否有足够应付额外索引的存储空间？

在每列上增加的索引数量会对针对该表执行的DML语句的性能带来何种影响？

基于函数的索引非常有用，但在实现时必须小心。在表上创建的索引越多，INSERT、UPDATE和DELETE语句的执行就会花费越多的时间。

注意：

对于优化器所使用的基于函数的索引来说，必须把初始参数QUERY \_REWRITE \_ ENABLED设定为TRUE。

**分区索引 **

参见“\[Oralce\] 分区表和索引”.  
**本地和全局索引 **

参见“\[Oralce\] 分区表和索引”.

**位图连接索引**

位图连接索引是基于两个表的连接的位图索引，在数据仓库环境中使用这种索引改进连接维度表和事实表的查询的性能。创建位图连接索引时，标准方法是连接索引中常用的维度表和事实表。当用户在一次查询中结合查询事实表和维度表时，就不需要执行连接，因为在位图连接索引中已经有可用的连接结果。通过压缩位图连接索引中的ROWID进一步改进性能，并且减少访问数据所需的I/O数量。

创建位图连接索引时，指定涉及的两个表。相应的语法应该遵循如下模式：

<table style="width: 400px;"> 
 <tbody> 
  <tr> 
   <td> <pre>create&nbsp;&nbsp; &nbsp;bitmap&nbsp;&nbsp; index&nbsp;&nbsp; FACT_DIM_COL_IDX<br>on&nbsp;&nbsp;&nbsp; &nbsp;FACT(DIM.Descr_Col)<br>from&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;FACT, DIM<br>where&nbsp; &nbsp;FACT.JoinCol = DIM.JoinCol;</pre> </td> 
  </tr> 
 </tbody> 
</table>

位图连接的语法比较特别，其中包含FROM子句和WHERE子句，并且引用两个单独的表。索引列通常是维度表中的描述列-- 就是说，如果维度是CUSTOMER，并且它的主键是CUSTOMER\_ID，则通常索引Customer\_Name这样的列。如果事实表名为SALES，可以使用如下的命令创建索引：

<table style="width: 400px;"> 
 <tbody> 
  <tr> 
   <td> <pre><pre>create&nbsp;&nbsp; &nbsp;&nbsp;bitmap&nbsp; index&nbsp;&nbsp; SALES_CUST_NAME_IDX<br>on&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;SALES(CUSTOMER.Customer_Name)<br>from&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;SALES, CUSTOMER<br>where&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;SALES.Customer_ID=CUSTOMER.Customer_ID;</pre>
</pre> </td> 
  </tr> 
 </tbody> 
</table>

如果用户接下来使用指定Customer\_Name列值的WHERE子句查询SALES和CUSTOMER表，优化器就可以使用位图连接索引快速返回匹配连接条件和Customer\_Name条件的行。

位图连接索引的使用一般会受到限制：只可以索引维度表中的列。用于连接的列必须是维度表中的主键或唯一约束；如果是复合主键，则必须使用连接中的每一列。不可以对索引组织表创建位图连接索引，并且适用于常规位图索引的限制也适用于位图连接索引。

**快速重建索引**

执行ALTER INDEX语句中的REBUILD选项，可以使用已有索引而不是整个表快速重建索引：

<table style="width: 400px;"> 
 <tbody> 
  <tr> 
   <td> <pre>alter&nbsp;&nbsp; index&nbsp;&nbsp; cust_idx1 rebuild parallel<br>tablespace&nbsp;&nbsp;&nbsp; cust_tblspc1<br>storage (pctincrease 0);<br>Index altered.</pre> </td> 
  </tr> 
 </tbody> 
</table>

我们可以同时修改STORAGE子句，并且可以使用parallel选项。

技巧：

利用ALTER INDEX语句中的REBUILD选项，可以使用已有索引而不是表来快速重建索引。在执行这个操作时必须要有足够的空间来保存所有的索引。

技巧：

还可以在重建索引时使用REBUILD ONLINE选项，以允许对表或分区进行DML操作。但不能对位图索引或那些强制参照完整性约束的索引指定REBUILD ONLINE。