索引相关

索引的管理成本
 
1、     存储索引的磁盘空间 
2、     执行数据修改操作（INSERT、UPDATE、DELETE）产生的索引维护  
3、     在数据处理时回需额外的回退空间。
 
实际数据修改测试：
 
一个表有字段A、B、C，同时进行插入10000行记录测试 
在没有建索引时平均完成时间是2.9秒
在对A字段建索引后平均完成时间是6.7秒
在对A字段和B字段建索引后平均完成时间是10.3秒
在对A字段、B字段和C字段都建索引后平均完成时间是11.7秒
从以上测试结果可以明显看出索引对数据修改产生的影响
 
索引按存储方法分类 
1、B*树索引
B*树索引是最常用的索引，其存储结构类似书的索引结构，有分支和叶两种类型的存储数据块，分支块相当于书的大目录，叶块相当于索引到的具体的书页。一般索引及唯一约束索引都使用B*树索引。  
2、位图索引
位图索引储存主要用来节省空间，减少ORACLE对数据块的访问，它采用位图偏移方式来与表的行ID号对应，采用位图索引一般是重复值太多的表字段。位图索引在实际密集型OLTP（数据事务处理）中用得比较少，因为OLTP会对表进行大量的删除、修改、新建操作，ORACLE每次进行操作都会对要操作的数据块加锁，所以多人操作很容易产生数据块锁等待甚至死锁现象。在OLAP（数据分析处理）中应用位图有优势，因为OLAP中大部分是对数据库的查询操作，而且一般采用数据仓库技术，所以大量数据采用位图索引节省空间比较明显。
 
索引按功能分类  
1、唯一索引 
唯一索引有两个作用，一个是数据约束，一个是数据索引，其中数据约束主要用来保证数据的完整性，唯一索引产生的索引记录中每一条记录都对应一个唯一的ROWID。
 
2、主关键字索引
主关键字索引产生的索引同唯一索引，只不过它是在数据库建立主关键字时系统自动建立的。
 
3、一般索引
一般索引不产生数据约束作用，其功能主要是对字段建立索引表，以提高数据查询速度。
 

索引按索引对象分类
 
单列索引（表单个字段的索引）
多列索引（表多个字段的索引）
函数索引（对字段进行函数运算的索引）
 
建立函数索引的方法：
create   index   收费日期索引   on   GC_DFSS(trunc(sk_rq))
create   index   完全客户编号索引   on   yhzl(qc_bh||kh_bh)
 
在对函数进行了索引后，如果当前会话要引用应设置当前会话的query_rewrite_enabled为TRUE。
alter   session   set   query_rewrite_enabled=true
 
注：如果对用户函数进行索引的话，那用户函数应加上   deterministic参数，意思是函数在输入值固定的情况下返回值也固定。例：
create   or   replace   function   trunc_add（input_date   date）return   date   deterministic  
as  
begin
        return   trunc(input_date+1); 
end   trunc_add;
 
应用索引的扫描分类  
INDEX   UNIQUE   SCAN（按索引唯一值扫描） 
select   *   from   zl_yhjbqk   where   hbs_bh= '5420016000 '
 
INDEX   RANGE   SCAN（按索引值范围扫描）  
select   *   from   zl_yhjbqk   where   hbs_bh> '5420016000 ' 
select   *   from   zl_yhjbqk   where   qc_bh> '7001 '
 
INDEX   FAST   FULL   SCAN（按索引值快速全部扫描） 
select   hbs_bh   from   zl_yhjbqk   order   by   hbs_bh  
select   count(*)   from   zl_yhjbqk  
select   qc_bh   from   zl_yhjbqk   group   by   qc_bh
 
什么情况下应该建立索引
 
表的主关键字
 
自动建立唯一索引  
如zl_yhjbqk（用户基本情况）中的hbs_bh（户标识编号）
 
表的字段唯一约束
 
ORACLE利用索引来保证数据的完整性
如lc_hj（流程环节）中的lc_bh+hj_sx（流程编号+环节顺序）
 
直接条件查询的字段
 
在SQL中用于条件约束的字段
如zl_yhjbqk（用户基本情况）中的qc_bh（区册编号） 
select   *   from   zl_yhjbqk   where   qc_bh=’7001’
 
查询中与其它表关联的字段
 
字段常常建立了外键关系
如zl_ydcf（用电成份）中的jldb_bh（计量点表编号）  
select   *   from   zl_ydcf   a,zl_yhdb   b   where   a.jldb_bh=b.jldb_bh   and   b.jldb_bh=’540100214511’  

查询中排序的字段  
排序的字段如果通过索引去访问那将大大提高排序速度 
select   *   from   zl_yhjbqk   order   by   qc_bh（建立qc_bh索引）
select   *   from   zl_yhjbqk   where   qc_bh= '7001 '   order   by   cb_sx（建立qc_bh+cb_sx索引，注：只是一个索引，其中包括qc_bh和cb_sx字段）
 
查询中统计或分组统计的字段
select   max(hbs_bh)   from   zl_yhjbqk
select   qc_bh,count(*)   from   zl_yhjbqk   group   by   qc_bh
 
什么情况下应不建或少建索引  
表记录太少
 
如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块，这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出，处理速度显然会比用索引快。
 
如表zl_sybm（使用部门）一般只有几条记录，除了主关键字外对任何一个字段建索引都不会产生性能优化，实际上如果
 
对这个表进行了统计分析后ORACLE也不会用你建的索引，而是自动执行全表访问。如： 
select   *   from   zl_sybm   where   sydw_bh= '5401 '（对sydw_bh建立索引不会产生性能优化）
 
经常插入、删除、修改的表 
对一些经常处理的业务表应在查询允许的情况下尽量减少索引，如zl_yhbm，gc_dfss，gc_dfys，gc_fpdy等业务表。
 
数据重复且分布平均的表字段  
假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
 
经常和主字段一块查询但主字段索引值比较多的表字段
如gc_dfss（电费实收）表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作标志来具体查询某一笔收款的情况，如果将所有的字段都建在一个索引里那将会增加数据的修改、插入、删除时间，从实际上分析一笔收款如果按收费序号索引就已经将记录减少到只有几条，如果再按后面的几个字段索引查询将对性能不产生太大的影响。
 
如何只通过索引返回结果
一个索引一般包括单个或多个字段，如果能不访问表直接应用索引就返回结果那将大大提高数据库查询的性能。对比以下三个SQL，其中对表zl_yhjbqk的hbs_bh和qc_bh字段建立了索引：  
1   select   hbs_bh,qc_bh,xh_bz   from   zl_yhjbqk   where   qc_bh=’7001’  
执行路径: 
SELECT   STATEMENT,   GOAL   =   CHOOSE                   11     265   5565  
  TABLE   ACCESS   BY   INDEX   ROWID         DLYX         ZL_YHJBQK       11     265   5565  
  INDEX   RANGE   SCAN     DLYX         区册索引         1       265  
平均执行时间（0.078秒）
 
2   select   hbs_bh,qc_bh   from   zl_yhjbqk   where   qc_bh=’7001’  
执行路径:  
SELECT   STATEMENT,   GOAL   =   CHOOSE                   11     265   3710
TABLE   ACCESS   BY   INDEX   ROWID         DLYX         ZL_YHJBQK       11     265   3710
INDEX   RANGE   SCAN     DLYX         区册索引         1       265    
平均执行时间（0.078秒）
 
3   select   qc_bh   from   zl_yhjbqk   where   qc_bh=’7001’  
执行路径:  
SELECT   STATEMENT,   GOAL   =   CHOOSE                   1       265   1060
INDEX   RANGE   SCAN       DLYX         区册索引         1       265   1060
平均执行时间（0.062秒）
 

从执行结果可以看出第三条SQL的效率最高。执行路径可以看出第1、2条SQL都多执行了TABLE   ACCESS   BY   INDEX   ROWID（通过ROWID访问表）这个步骤，因为返回的结果列中包括当前使用索引(qc_bh)中未索引的列（hbs_bh,xh_bz），而第3条SQL直接通过QC_BH返回了结果，这就是通过索引直接返回结果的方法。

如何重建索引
alter   index   表电量结果表主键   rebuild

如何快速新建大数据量表的索引 
如果一个表的记录达到100万以上的话，要对其中一个字段建索引可能要花很长的时间，甚至导致服务器数据库死机，因为在建索引的时候ORACLE要将索引字段所有的内容取出并进行全面排序，数据量大的话可能导致服务器排序内存不足而引用磁盘交换空间进行，这将严重影响服务器数据库的工作。解决方法是增大数据库启动初始化中的排序内存参数，如果要进行大量的索引修改可以设置10M以上的排序内存（ORACLE缺省大小为64K），在索引建立完成后应将参数修改回来，因为在实际OLTP数据库应用中一般不会用到这么大的排序内存。

================================================================================================

oracle中DateTime类型的字段，建立索引后，查寻时索引如何生效？请高手指教
 在oracle的表里面有一个dateTime的字段，记录精确到秒，我给此字段建立索引之后，为什么在查询区间的时候只有在12个小时范围内，索引生效，超出即不生效
如下面代码
select * from  act  where id=2001
and cstart_time >= to_date('2011-7-1 02:00:00','yyyy-MM-dd hh24:mi:ss')
and cstart_time < to_date('2011-7-1 18:00:00','yyyy-MM-dd hh24:mi:ss')
若是这样，索引并不生效。
但如果把最后一句改成
and cstart_time < to_date('2011-7-1 14:00:00','yyyy-MM-dd hh24:mi:ss')
索引即生效。请高手指教，是不是我建立的索引有问题，还是在建立索引时对
datetime类型有限制？
     
这就是ORACLE  CBO优化器的聪明之处了。
在ORACLE 的官方文档有这样的话：
Create an index if you frequently want to retrieve less than about 15% of the
rows in a large table.
意思就是如果你检索的数据占整个表数据的15%以下的时候建索引。
也就是说一般情况下如果你查询的数据占表数据的15%以下的时候，用索引会比全表扫描效率高。如果超过15%的话可能就是全表扫描的效率更好！当然这个15%也不是固定的，也要根据不同情况来分！
而ORACLE 的CBO优化器会自动根据表的统计数据和你要检索的数据，去分析是用索引的效率更高还是全表扫描的效率更高。
如果一定要用索引的话，那就加个hints吧!