索引概述

索引

索引结构及特点

两大类：B树索引，2）位图索引

1、B树索引结构

根节点，分支节点，叶子节点，以及表行，rowid，键值，双向链等概念。

        

1）叶块之间使用双向链连接，

2）删除表行时索引叶块也会更新，但只是逻辑更改，并不做物理的删除叶块。

3）索引叶块中不保存表行键值的null信息。

2、位图索引结构

位图索引适用于离散度较低的列，它的叶块中存放key, start rowid-end rowid,并应用一个函数把位图中相应key值置1。

建立位图索引：

SQL>create bitmap index job_bitmap on emp1(job);

 

3、B树索引和位图索引的适用环境

索引是与表相关的一个可选结构，在逻辑上和物理上都独立于表的数据，索引能优化查询，不能优化DML操作

由于是Oracle自动维护索引，所以频繁的DML操作反而会引起大量索引维护的开销。3）如果SQL语句仅访问被索引的列，那么数据库只需从索引中读取数据，而不用读取表，如果该语句同时还要访问除索引列之外的列，那么数据库会使用rowid来查找表中的行。

4、几种常用的B树索引创建方法：

1）唯一索引，指键值不重复。

SQL>create unique index empno_idx on emp1(empno);

2）非唯一索引（Unique or non_unique):

SQL>create index empno_idx on emp1(empno);

3）组合索引(Composite)：绑定了两个或更多列的索引。

SQL> create index job_deptno_idx on emp1(job,deptno);

4）反向键索引(Reverse)：将字节倒置后组织键值。当使用序列产生主键索引时，可以防止叶节点出现热块现象（考点）。缺点是无法提供索引范围扫描。

SQL>create index mgr_idx on emp1(mgr) reverse;

5）函数索引(Function base)：以索引列值的函数值为键值去组织索引

SQL> create index fun_idx on emp1(lower(ename));

6）压缩（Compress)：重复键值只存储一次，就是说重复的键值在叶块中就存一次，后跟所有与之匹配的rowid字符串。

SQL> create index comp_idx on emp1(sal) compress;

7）升序或降序（Ascending or descending)：叶节点中的键值排列默认是升序的。

SQL>create index deptno_job_idx on emp1(deptno desc, job asc);

5、索引的优化

查询优化器使用索引

1）索引唯一扫描(index unique scan)

通过唯一索引查找一个数值返回单个ROWID。对于唯一组合索引，要在where的谓词“=”后包含所有列的“布尔与”。

2）索引范围扫描(index range scan)

在非唯一索引上，可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。

使用index rang scan的3种情况：

(在唯一索引列上使用了range操作符(><<>>= <= between)

在唯一组合索引上，对组合索引使用部分列进行查询(含引导列），导致查询出多行

对非唯一索引列上进行的任何查询。不含‘布尔或’

3）索引全扫描(index full scan)

对整个index进行扫描，并且顺序的读取其中数据。

CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时，才进行全Oracle索引扫描，

4）索引快速扫描(index fast full scan)

扫描索引中的所有的数据块，fast full scan 在读取叶子块时的顺序完全由物理存储位置决定，并采取多块读，每次读取DB_FILE_MULTIBLOCK_READ_COUNT个块。

CBO能够索引全扫描和快速扫描的前提是：所要的数据必须能从索引中可以直接得到，因此不再需要查询基表。

6、索引的碎片问题

对基表做DML操作，便导致对索引表块的自动更改操作，尤其是基表的delete操作会引起index表的index entries的逻辑删除。

注意：只有当一个索引块中的全部index entry都被删除了，这个块才会被收回。

如果update基表索引列，则索引块会发生entry delete，再entry insert，这都些动作都可能产生索引碎片

测试：

第一步，搭建环境

SQL>create table t (id int);

SQL>create index ind_1 on t(id);

SQL>

begin

  for i in 1..1000000 loop

         insert into t values (i);

                   if mod(i, 100)=0 then

                   commit;

                   end if;

  end loop;

end;

/

第二步，收集统计信息，并查看视图

SQL>analyze index ind_1 validate structure;

SQL>select name,HEIGHT,PCT_USED,DEL_LF_ROWS/LF_ROWS from index_stats;

第三步，基表高强度更新，造成索引碎块

SQL>delete t where rownum<700000;

第四步，重复第二步，查看视图

7、重建索引

查询索引的两个动态视图

DBA_INDEXES

DBA_IND_COLUMNS

在Oracle文档里并没有清晰的给出索引碎片的量化标准。

Oracle建议通过Segment Advisor(段顾问）解决表和索引的碎片问题，如果你想自行解决，可以通过查看index_stats视图，当以下三种情形之一发生时，说明积累的碎片应该整理了。

1.HEIGHT >=4  

2 PCT_USED< 50% (相对值） 

3 DEL_LF_ROWS/LF_ROWS>0.2

SQL>alter index ind_1 rebuild online;

索引不可用（unusable）和不可见（invisible）

1）仅仅保存索引定义，不删除索引，也不更新索引。

SQL>alter index ind_1 unusable;    

索引被设定为unusable后，如再次使用需要做rebuild。

SQL>alter index ind_1 rebuild;        

2）在11g里，Oracle提供了一个新特性(Index Invisible)来降低直接删除索引或禁用索引的风险。我们可以在创建索引时指定invisible属性或者用alter语句来修改索引为invisible(visible)

SQL>alter index ind_1 invisible;

SQL> select index_name,status,VISIBILITY from user_indexes;

索引不可见其实是对优化器来说不可见，索引维护还是正常进行的。

 

创建本地索引

create index 索引名字 on 表名（列名）local tablespace 索引表空间名；

创建全局索引

create index 索引名字 on 表名(列名) global tablespace 索引表空间名；

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30100210/viewspace-2150493/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30100210/viewspace-2150493/