索引基本管理

1、创建索引时应该考虑什么呢？
1）、索引能够提高查询性能，也会降低DML的操作速度。
 判断表DML和查询频率，需要两个方面来判断：
应用设计人员在设计前期就已经知道表的业务特性，并判断是否创建索引，这是最好的方法。
9I性能管理器（10G也有EM的性能处理模块）
2）、将索引和表分离到不同的表空间，也不要放到有回滚段和临时段的表空间。如SYSTEM表空间。离散IO
选择5个块的倍数
最小区的倍数
只要可能，要尽量设置本地管理的统一分配区的方式（UNIFORM）
 它能达到碎片最小化. 这里除了一个特例:通常系统表空间是要设置成自动分配的.因为系统表空间对象都很小，但对象很多，为了设置统一的区太大，浪费空间，就设置成自动分配了。
3）、大型索引创建，可以按如下步骤:
设置NOLOGGING
设置单独的大临时表空间
然后设置成LOGGING
还原临时表空间
备份索引表空间
4）、设置索引的INITRANS比表中的INITRANS大一些。避免事务在块中挂起

2、何时创建索引
 索引不是建得越多越好，索引多并不意味着性能好，因为索引是oracle自动维护的，索引对于查询是有利的，但对DML是有弊的（索引很耗费资源的），下面的情况适合建索引：
1）、 一个字段包含一个较大范围的值,也就是说重复值比较小。
 比如: 日期 2000-1-1号到2009-2-19，这是一个大的范围，我们去搜索一个小的范围或者精确找一个值的时候索引就非常有效
2）、 一个列中包含有大量的NULL值。
 有人说NULL值不是不存放在索引里吗(CLUSTER除外)?那为什么要建索引？因为Null越多，速度越快，没值,查询的东西就少。（索引忽略null），如果你在这个列上经常查有值的语句时，有大量NULL值的索引效率非常高，因为索引不包括NULL值,如果大量是NULL值 ,意味着这个索引非常小。那去查询一个有效值效率非常高。
3）、一个或多个在WHERE子句或者JOIN条件中频繁出现的列。
 但是这个是有前提条件的，条件就是下面一点
4）、这个表非常大，大多数查询返回出来的结果集占总的结果集的2%-4% 。
 其实这个2%-4%只是一个评估值，有时10%也好,这需要了解索引访问成本的算法。

3、什么时候不适合创建索引呢?
1）、表很小时。因为很小表的时候全表访问比索引访问成本还低,有没有索引影响不大。
 比如: 就一列的表，有必要建索引吗?本来就一列，索引反而把它整成两列了，对吧?一个键值，一个rowid
2）、这个列虽然索引效果很好,但是你业务中很少去使用这个列去查询的，考虑不要建索引,以均衡索引的成本，我们说过，索引主要是查询比较快吧
3）、如果该列上返回的结果集大于总结果集的2-4%,
 谨慎考虑建立索引，这些还是需要CBO知识，数据仓库 90%以上是查询，OLTP 60-70%是查询，如果DML语句超过60%建立索引不宜太多，不过还是要结合实际的情况，通常的OLTP还是查询多。
4）、索引列参考了表达式。
 这通常用不到索引，所以要看是不是建立函数索引，否则没有意义
这里注意: WHERE column_name IS NULL 不会使用索引   WHERE column_name IS NOT NULL 会考虑索引

4、建立索引有两种方式：
 1）、主键约束和唯一约束是自动创建索引
 2）、手工创建
create index index_name on table (column[,column...])
  例子：create index emp_last_name_idx on employees(last_name);
创建位图索引：
  create bitmap index ...
 创建位图索引时有一参数create_bitmap_area_size 在手工管理时默认是8M，该值越大，创建位图索引时可能越快，这样可能不需要用到临时表空间
 还有其他多种索引：
  oracle为支持海量数据库,还有很多索引类型比如,BITMAP,REVERSE ,IOT等,默认是就是b tree索引。

 3）、创建索引时必须有权限创建：
  因为要起码能访问表的权限,如果是自己的表,那没有问题,如果是别的用户的表,你需要CREATE [ANY] INDEX 权限

5、查询索引相关的信息 
 我们可以通过数据字典来查看索引情况。这里列出了个：
  ⊙ DBA_INDEXES是索引的概要信息
  ⊙ DBA_IND_COLUMNS是索引列的详细信息
  ⊙ DBA_IND_EXPRESSIONS提供了函数索引的表达式
  ⊙ V$OBJECT_USAGE提供了索引的使用情况
  ⊙ USER_INDEXES
  群集因子 Clustering  Factor位于USER_INDEXES视图中。该列反映了数据相对于已建索引的列是否显得有序。如果Clustering Factor列的值接近于索引中的树叶块(leaf block)的数目，表中的数据就越有序。如果它的值接近于表中的行数，则表中的数据就不是很有序。
  ⊙ USER_IND_COLUMNS    当然还有些别的索引数据字典,比如: 索引统计等。
 例如：我们到hr用户下，想找一下employees表有哪些索引
  select * from USER_INDEXES where table_name=upper('employees');
SQL> col column_name for a20
SQL> Select ic.index_name,ic.column_name,ic.column_position col_pos,ix.uniqueness
  2  From user_indexes ix,user_ind_columns ic
  3  Where ic.index_name=ix.index_name
  4  And ic.table_name= 'EMPLOYEES';
INDEX_NAME                     COLUMN_NAME             COL_POS UNIQUENES
------------------------------ -------------------- ---------- ---------
EMP_EMAIL_UK                   EMAIL                         1 UNIQUE
EMP_EMP_ID_PK                  EMPLOYEE_ID                   1 UNIQUE
EMP_DEPARTMENT_IX              DEPARTMENT_ID                 1 NONUNIQUE
EMP_JOB_IX                     JOB_ID                        1 NONUNIQUE
  ........

6、改变索引存储参数
 alter index employees_last_name_idx storage (next 200k maxextents 100);

7、分配索引区&是否索引区
 alter index id_idx allocate extent (size 200k datafile '/disk6/indx01.dbf');
 alter index id_idx deallocate unused;
 分配索引区跟分配表区一样，分配索引区跟分配表区一样，释放从未使用的索引区也跟释放从未使用表区也一样

8、删除索引
 drop index index_name；

9、监控索引使用情况
 ALTER INDEX index MONITORING USAGE;
 ALTER INDEX index NOMONITORING USAGE;
 设置两个监控点，可以监控在这段时间内有没有使用索引，我们通过查看数据字典V$OBJECT_USAGE来获取该索引使用情况的信息
 select index_name,monitoring,used,start_monitoring,end_monitoring
 from v$object_usage;  
 ----USED=YES，表示该索引被使用过

10、大量数据装载对索引的影响和处理
 ⊙ 建议先将索引删除，然后一次性装载数据，最后创建索引
  这样不要每次插入一行，然后维护索引，可以提高效率 ，也不会造成索引的过度拆分, 产生索引的碎片
 ⊙ 创建索引时可以直接指定统计索引的信息，这样建索引是实时跟踪统计信息，比创建索 引后分析，效率得到提高
  create index emp_ename on emp(ename) compute statistics; 
  注意:10G后能实现自动统计,所以省略了COMPUTE STATISTICS。
 ⊙ 可以采用并行转载，由多个服务器进程分别建立各自的子索引部分，设置独立的分区， 最后进行合并
  比如:索引的INITIAL区是5M，并行度是12，那么有12个进程同时并行来创建索引，那创建期间至少要生成5 × 12 =60M的空间
 ⊙ 表和索引要分开到不同的表空间，以提高装载时IO性能的提高
 ⊙ 可以使用NOLOGGING的方式来创建，但是索引创建完后要还原成LOGGING，而且要做备份。
  所以在DML时必须LOGGING的，包括INSERT APPEND
 ⊙ 可以通过索引压缩前缀部分来减少IO的使用
  压缩索引有可能会降低B*树的高度,这样，就会减少IO。
 ⊙ 对于创建完后，如果有频繁插入中间值的语句发生，那么将PCTFREE设置大一点（防止块分裂）

11、 创建大的索引的注意事项: 
 ◎ 对于特别大的索引，在独立创建的时候需要大量的排序
  可能要超过当前临时表空间的容量，如果临时表空间自动扩展，势必会造成临时表空间的浪费，和影响效率，可以单独建一个临时表空间。
 ◎ 如果你刚按索引列顺序的值批量插入了大量数据，可以使用NOSORT选项，这样就很快了。
  CREATE INDEX ord_customer_ix_demo
ON orders (order_mode)
NOSORT
NOLOGGING parallel;

12、相关术语
高基数：简单理解就是表中列的不同值多。
低基数：建单理解就是表中的列的不同值少。
以删除的叶节点数量：指得是数据行的delete操作从逻辑上删除的索引节点 的数量，要记住oracle在删除数据行后，将 “ 死 “ 节点保留在索引中，这样做可以加快sql删除操作的速度，因此oracle删除数据行后可以不必重新平衡索引。
索引高度：索引高度是指由于数据行的插入操作而产生的索引层数，当表中添加大量数据时，oracle将生成索引的新层次以适应加入的数据行，因此，oracle索引可能有4层，但是这只会出现在索引数中产生大量插入操作的区域。Oracle索引的三层结构可以支持数百万的项目，而具备4层或是更多层的需要重建。
每次索引访问的读取数：是指利用索引读取一数据行时所需要的逻辑I/O操作数，逻辑读取不必是物理读取，因为索引的许多内容已经保存在数据缓冲区，然而，任何数据大于10的索引都需要重建。