选择和优化索引

选择和优化索引  ---读oracle database 11g 性能优化攻略
索引是一种用来改进sql查询性能的数据库对象，索引类似于书的目录
创建索引之前需要考虑
索引的类型
需要包含的列
应该使用单独的列和组合列
特殊的属性，例如并行、关闭日志、压缩、不可见索引等
唯一性
命名规范
表空间布局
初始化空间需求以及增长
对select语句性能的影响
对DML语句的影响
如果基表是分区表，那么使用全局索引还是本地索引

1.理解B树索引
B树索引是oracle默认的索引类型，对于大多数oltp类型应用，使用B树索引就足够了，之所以叫B树索引是因为rowid和相应的列被存储在一个类似于平衡树的结构中
创建索引之前可通过dbms_space.create_index_cost存储过程来估算索引所需的空间
2.选择需要建立索引的列
确定列创建索引的一般原则
为每张表定义一个主键，将会自动在主键列上创建索引
在需要保持唯一的非空列上创建一个唯一键约束，将会在唯一键约束列上创建一个索引
在外键列上显示创建索引
在经常作为谓词出现在执行频率很高的sql查询where字句上的列上创建索引
除非有非常充分的理由使用其他类型的索引，否则使用默认的B树索引
为索引创建独立的表空间
索引应该建在选择性高的字段上；
索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
频繁进行数据操作的表，不要建立太多的索引；
删除无用的索引，避免对执行计划造成负面影响；
3.创建主键索引
默认情况主键约束字段上自动创建索引
创建主键约束和索引有如下方法
首先创建索引，然后使用alter table ...add constraint（这种方法优点可以单独删除或禁用主键约束或者索引）
将约束声明在create table语句中（和相应的列放在一起）
将约束声明在create table语句中（不和相应列放在一起）
4.创建唯一索引
主键和唯一键的区别是主键每张表只有一个，唯一键可以有多个，唯一键可以为空值，而主键不能为空值
创建唯一约束时会自动创建一个唯一索引
5.为外键创建索引
6.有效使用组合索引
如果有几个列经常搭配在where字句中使用，考虑创建组合索引
7.通过压缩减少索引大小
压缩索引可以节省存储空间
在叶子块中可以存放很多的数据行，这样在访问压缩索引时能减少I/O
8.实现基于函数的索引
查看基于函数索引的定义查看试图user_ind_expressions
9.在虚拟列上创建索引
alter table cust add （upp_name generated always as (upper(fist_name)) virtual）;
create index cust_vidx1 on cust（upp_name）;
10.平衡索引IO
如果索引值都是近似的，从而导致对同一个数据块的多次插入，引起争用，如果要分散索引的插入更均匀的分布索引结构，可以考虑使用反转索引（reverse）
11.新增一个索引而不影响已有的应用
创建一个不可见索引来实现
create index inv_idx1 on inv(inv_id) invisible;
通过设置参数OPTIMIZER_USE_INVISIBLE_INDEXES为true来使优化器考虑不可见索引
12.创建支持星型架构的位图索引
位图索引适用在低基数列很少更新的表上一般用在数据仓库环境中，经常执行dml操作的oltp数据库中不应该适用位图索引
13.创建位图连接索引
14.创建索引组织表
索引组织表将表中数据行的所有内容存储在一个B树索引结构中，索引组织表为完全匹配和在一定范围内搜索主键的查询提供快速访问的方法
15.监控索引的使用情况
alter index ind_name monitoring usage;  
select * from v$object_usage;
alter index ind_name nomonitoring usage;  
注意：
v$object_usage视图是查询当前连接用户的信息where io.owner# = userenv('SCHEMAID')
create or replace view sys.v$object_usage
(index_name, table_name, monitoring, used, start_monitoring, end_monitoring)
as
select io.name, t.name,
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
       decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
       ou.start_monitoring,
       ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where io.owner# = userenv('SCHEMAID')
  and i.obj# = ou.obj#
  and io.obj# = ou.obj#
  and t.obj# = i.bo#;

通过如下查询所有被监控的索引
select io.name, t.name,
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
       decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
       ou.start_monitoring,
       ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where i.obj# = ou.obj#
  and io.obj# = ou.obj#
  and t.obj# = i.bo#;
16.索引创建速度的最大化
将重做关闭（nologging）
增加并行度（parallel）
17.回收未使用的索引空间
重建索引
收缩索引
alter index ind_name rebuilt;
alter index ind_name shrink space;