第十四章 使用索引和簇

一、使用索引

1.调整逻辑结构
尽管查询优化器可以避免在查询时使用选择性不高的索引（通过成本比较剔除），但sql engine必须继续维护所有的索引，不管是否充分的利用了。
在write-intensive型应用系统中，索引的维护需要很多cpu和i/o资源。因此除非必要，不要建立索引。

使用alter index monitoring usage进行一段时间的监测，如果索引没用到，就删除掉。

有时候，应用中的索引可能不是立即显现出来。比如父表上的外键，其避免了在子表上共享锁。

如果正在考虑创建索引来提高性能，可以先用explain plan语句查看优化器是否选择了索引进行运行。
在本地创建索引后，原有的执行计划生效，再次执行语句时会编译并使用新生成的执行计划；
而如果是在远程表上创建了索引，则会在语句再次解析后才有可能用到该索引。

由于新建的索引可能被其他的语句使用，创建后需要多检查几次。

2.使用sqlaccess advisor进行索引调优

使用sql access advisor可以手工指定使用哪些索引。此建议器在OEM企业管理器或者dbms_advisor包API上推荐了一系列的索引。使用负载或者假定的负载进行推荐。

3.为索引选择列和表达式
建索引需要注意：
在where子句中经常出现的列上考虑建索引
在表连接条件频繁使用的地方建索引
在高选择性的地方建索引
  在选择性低的列上，如果数据倾斜，一两个值的查询也是高效的
（在主键和唯一键完整性约束的列上，oracle自动建索引）
在distinct值很少的列上不要使用b树索引。
（Do not use standard B-tree indexes on keys or expressions with few distinct values. Such keys or expressions usually have poor selectivity and therefore do not optimize performance unless the frequently selected key values appear less frequently than the other key values. You can use bitmap indexes effectively in such cases, unless the index is modified frequently, as in a high concurrency OLTP application.）
不在频繁修改的列上使用索引
不要再只出现在where子句函数或操作中的列使用索引，除非是函数索引
考虑在外键约束列上建立索引，以便在主表进行大量dml操作时，子表不共享锁
考虑加索引会在查询时提高性能，但dml操作更慢、占用了更多空间，这样是否值得

373页

4.多列（混合）索引
相比于普通索引，有如下优点：
提高了可选择性：几个选择性不高的列放在一起可能组成一个选择性高的索引
减少i/o：如果待查询的列都能在索引上找到，就不需要再到表中查找了
对索引的前导列进行查询时也可以使用到该索引
多列索引的关键字选择：
当在where子句中经常对两个列使用and进行连接，得到结果时，尤其是其组合的可选择性比任意单个列选择性都好时；
如果一些查询语句中都用到了表中多列作为条件，可以考虑创建多列索引

多列索引的列顺序
将where子句中重要的条件作为键值；
对使用最频繁的列上创建索引；
如果数据在物理上是根据某列划分的，优先对其创建索引

5.书写用得上索引的语句

6.书写用不到索引的语句
方法：
在语句中加hint：no_index
在语句中加hint：full
在语句中加hint：index或index_combine来提示使用某一或几个索引，而不是用其他的

实验：
SQL> select /*+FULL*/* from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 2303402015

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |  1169K|    23M|  1455   (1)| 00:00:18 |
|   1 |  INDEX FAST FULL SCAN| MUT_IDX |  1169K|    23M|  1455   (1)| 00:00:18 |
--------------------------------------------------------------------------------

SQL> select /*+NO_INDEX(test)*/* from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1169K|    23M|  3575   (1)| 00:00:43 |
|   1 |  TABLE ACCESS FULL| TEST |  1169K|    23M|  3575   (1)| 00:00:43 |
--------------------------------------------------------------------------

正确的用法是/*+no_index(table_name)*/、/*+FULL(TEST)*/

使用索引并行执行是很高效的，但如果索引的选择性很高，对索引进行串行查询可能比并行的全表扫描要高效。

7.索引的重建
由于空间上的考虑，可能会考虑索引重建。当重建索引或者建立子索引时，oracle可能会用到已经存在的索引，而不是基表来提高创建时的性能。
但是，也会有反例。比如一个大表进行了大量的dml操作，当前索引块中只有50%左右在用，这时可能用基表为基础创建新索引更加高效。
重建语句：
alter index …… rebuild
其中可以包括storage、tablespace、initrans等来执行其属性。

重建索引比删除后再创建有优势，节省i/o、不会影响索引的正常使用

8.比较索引
通过alter index …… coalesce可以合并索引的叶子块。可以将索引的叶子节点合并重用。也可以在线重建索引。

9.对非唯一索引进行强制唯一

10.使用生效的不合法约束
Placing a constraint in the enabled novalidated state signifies that any new data entered into the table must conform. to the constraint. Existing data is not checked.By placing a constraint in the enabled novalidated state, you enable the constraint without locking the table.

使用如下的方法创建包含约束和索引的表：
1）创建表时指定约束为disabled状态
2）加载旧数据
3）创建所有的索引，包括需要约束的索引
4）enable novalidate all constraints。先主键、后外键
5）允许用户查询和修改数据
6）用alter table语句validate all constraints。先主键后外键

example：
CREATE TABLE t (a NUMBER CONSTRAINT apk PRIMARY KEY DISABLE,
b NUMBER NOT NULL);
CREATE TABLE x (c NUMBER CONSTRAINT afk REFERENCES t DISABLE);
Now load data into table t.
CREATE UNIQUE INDEX tai ON t (a);
CREATE INDEX tci ON x (c);
ALTER TABLE t MODIFY CONSTRAINT apk ENABLE NOVALIDATE;
ALTER TABLE x MODIFY CONSTRAINT afk ENABLE NOVALIDATE;
At this point, users can start performing INSERT, UPDATE, DELETE, and SELECT operations on table t.
ALTER TABLE t ENABLE CONSTRAINT apk;
ALTER TABLE x ENABLE CONSTRAINT afk;
Now the constraints are enabled and validated.

二、基于函数的索引
函数索引中包含对列的运算或处理。用于在索引中存放计算量比较大的值。
oracle将递减索引作为函数索引来处理。

三、使用分区索引
类似分区表，分区索引也是为了提高可管理性、可用性、性能和可测量性。既可以独立分区（全局索引），也可以自动链接到表的分区上（本地索引）
oracle提供了范围分区全局索引和hash分区全局索引（range and hash partitioned globalindexes）。
在hash分区全局索引中，每个分区中的值都决定于oracle的hash函数。
hash算法在OLTP系统中产生热块时能够显著提高性能（The hash method can improve performance of indexes where a small number leaf blocks in the index have high contention in multiuser OLTP environment）
通过hash算法，可以将索引根据分区键分到不同的分区，减少特定分区上的争用，提高性能。
在分区索引上包含等号或in谓词的查询可以有效利用全局hash分区索引来提高查询效率。

四、使用索引组织表
与不同表不同，索引表中，表的数据包含在其相关的索引上。对表的dml操作会造成对索引的操作。
与堆表不同在于数据有序。

五、使用位图索引
对有如下特点的查询，位图索引显著提高性能：
where子句中包含多个low-cardinality columns or medium-cardinality columns
这些条件中单个谓词可以取到很多行数据
在这些列上创建了位图索引
表中包含很多列

六、使用位图连接索引（bitmap join indexes）
除了可以在单表上创建位图索引，还可以在多个表上创建位图联合索引。
位图联合索引在存储方面比物化连接视图更高效。因为物化视图不压缩实际表的rowid

七、域索引
用用户定义的索引类型创建。

create indextype

八、表簇
表簇是一个或多个表的集合，这些表物理上存储在一起，由于共享相同的行、经常在sql语句中一起出现。
由于数据库将相关的行存在一起，扫描磁盘的时间优化了。
可以通过create cluster语句创建簇。
根据如下几点决定是否创建簇表：
簇表经常在应用的join语句中一起访问；
Do not cluster tables if the application joins them only occasionally or modifies their common column values frequently.
Do not cluster tables if the application often performs full table scans of only one of the tables.
Cluster master-detail tables if you often select a master record and then the corresponding detail records.
Store a detail table alone in a cluster if you often select many detail records of the same master.
Do not cluster tables if the data from all tables with the same cluster key value exceeds more than one or two data blocks.
Do not cluster tables when the number of rows for each cluster key value varies significantly.

九、使用hash簇
Hash clusters group table data by applying a hash function to each row's cluster key value. All rows with the same cluster key value are stored together on disk.

如何决定是否选择：
Use hash clusters to store tables accessed frequently by SQL statements with WHERE clauses, if the WHERE clauses contain equality conditions that use the same column or combination of columns.
Store a table in a hash cluster if you can determine how much space is required to hold all rows with a given cluster key value, including rows to be inserted immediately and rows to be inserted in the future.
Use sorted hash clusters, where rows corresponding to each value of the hash function are sorted on a specific columns in ascending order, when the database can improve response time on operations with this sorted clustered data.
Do not store a table in a hash cluster if the application often performs full table scans and if you must allocate a great deal of space to the hash cluster in anticipation of the table growing
Do not store a table in a hash cluster if the application frequently modifies the cluster key values.