索引概述
索引概述
11.1 索引结构及特点
11.1.1 B树索引结构（图），介绍根节点，分支节点，叶子节点，以及表行，rowid，键值，双向链等概念。
考点：
1）叶块之间使用双向链连接，
2）删除表行时索引叶块也会更新，但只是逻辑更改，并不做物理的删除叶块。
3）索引叶块中不保存表行键值的null信息。
11.1.2 位图索引结构：
SQL>create bitmap index job_bitmap on emp1(job);
值/行 1 2 3 4 5 6 7 8 9 10 11 12 13 14
---------------------------------------------------------------
ANALYST 0 0 0 0 0 0 0 1 0 1 0 0 1 0
CLERK 1 0 0 0 0 0 0 0 0 0 1 1 0 1
MANAGER 0 0 0 1 0 1 1 0 0 0 0 0 0 0
PRESIDENT 0 0 0 0 0 0 0 0 1 0 0 0 0 0
SALESMAN 0 1 1 0 1 0 0 0 0 0 0 0 0 0
SQL>select count(*) from emp1 where job = 'CLERK' or job = 'MANAGER';
值/行 1 2 3 4 5 6 7 8 9 10 11 12 13 14
---------------------------------------------------------------
CLERK 1 0 0 0 0 0 0 0 0 0 1 1 0 1
MANAGER 0 0 0 1 0 1 1 0 0 0 0 0 0 0
---------------------------------------------------------------
or的结果 1 0 0 1 0 1 1 0 0 0 1 1 0 1
以上操作使用autotrace可以看到优化器使用了bitmap，在此 Oracle 会应用一个函数把位图中的第 i 位转换为一个 rowid，从而可用于访问表。B树索引要比位图索引应用更广泛，下面我们重点关注B树索引。
索引是与表相关的一个可选结构，在逻辑上和物理上都独立于表的数据，索引能优化查询，不能优化DML操作，Oracle自动维护索引，频繁的DML操作反而会引起大量的索引维护。
如果SQL语句仅访问被索引的列，那么数据库只需从索引中读取数据，而不用读取表，如果该语句同时还要访问除索引列之外的列，那么，数据库会使用rowid来查找表中的行，通常，为检索表数据，数据库以交替方式先读取索引块，然后读取相应的表块。
11.2 B树索引适用那些情况
适合情况 不适合情况
--------------------------------------------------------------------------------------------
经常用于WHERE子句或作为连接条件的列 表很小
所含数据值范围比较的列 列很少在查询中作为条件
含有大量空值的列 多数情况下查询出大于总记录的5%的表
总是使用两个或多个组合列查询 频繁更新的表（也适用bitmap)
多数情况下只查询总记录小于5%的表
11.3 索引的类型与选项：
1）B树索引，2）位图索引
常用的B树索引类型：
唯一或非唯一索引（Unique or non_unique): 唯一索引指键值不重复。
SQL> create unique index empno_idx on emp1(empno);
或
SQL> create index empno_idx on emp1(empno);
组合索引(Composite)：绑定了两个或更多列的索引。
SQL> create index job_deptno_idx on emp1(job,deptno);
反向键索引(Reverse)：将字节倒置后组织键值。当使用序列产生主键索引时，可以防止叶节点出现热块现象（考点）。
SQL> create index mgr_idx on emp1(mgr) reverse;
函数索引(Function base)：以索引列值的函数值为键值去组织索引
SQL> create index fun_idx on emp1(lower(ename));
压缩（Compress)：重复键值只存储一次，就是说重复的键值在叶块中就存一次，后跟所有与之匹配的rowid字符串。
SQL> create index comp_idx on emp1(sal) compress;
升序或降序（Ascending or descending)：叶节点中的键值排列默认是升序的。
SQL> create index deptno_job_idx on emp1(deptno desc, job asc);
索引相关的数据字典
USER_INDEXES //索引主要信息
USER_IND_CULUMNS //索引列的信息
11.4 索引扫描方式
Oracle的执行计划常见的四种索引扫描方式：
1）索引唯一扫描(index unique scan)
通过唯一索引查找一个数值返回单个ROWID。对于唯一组合索引，要在where的谓词“=”后包含所有列的“布尔与”。
2）索引范围扫描(index range scan)
在非唯一索引上，可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。
使用index rang scan的3种情况：
(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)
(b) 在唯一组合索引上，对组合索引使用部分列进行查询(含引导列），导致查询出多行
(c) 对非唯一索引列上进行的任何查询。不含‘布尔或’
3）索引全扫描(index full scan)
对整个index进行扫描，并且顺序的读取其中数据。
全Oracle索引扫描只在CBO模式下才有效。 CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时，才进行全Oracle索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。
4）索引快速扫描(index fast full scan)
扫描索引中的所有的数据块，与 index full scan很类似，但是一个显著的区别是full scan是根据叶子块的双向列表顺序读取，读取的块是有顺序的，也是经过排序的，所以返回的列表也是排序的。而fast full scan 在读取叶子块时的顺序完全由物理存储位置决定，并采取多块读，每次读取DB_FILE_MULTIBLOCK_READ_COUNT个块。
//分析器是根据要访问的数据量和索引的聚簇因子等属性判断使用RANG SCAN或FULL INDEX SCAN
举例这四种索引扫描方式：
如果你的scott不能使用autotrace，做一下几步。
SQL> conn / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce
SQL> grant plustrace to scott,hr;
SQL>
试试scott下能否使用autotrace
SQL> create table emp1 as select * from emp;
SQL> set autotrace traceonly explain;
SQL> select empno from emp1;
执行计划
----------------------------------------------------------
Plan hash value: 2226897347
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 182 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP1 | 14 | 182 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
例：索引唯一扫描(index unique scan)
SQL> create unique index emp1_idx on emp1(empno);
索引已创建。
SQL> select empno from emp1 where empno=7788;
执行计划
----------------------------------------------------------
Plan hash value: 1995401140
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| EMP1_IDX | 1 | 13 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
SQL> drop index emp1_idx;
例：索引范围扫描(index range scan)
SQL> create index emp1_idx on emp1(empno);
SQL> select empno from emp1 where empno=7788;
执行计划
----------------------------------------------------------
Plan hash value: 253836959
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| EMP1_IDX | 1 | 13 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
3）索引全扫描(index full scan)
SQL> select count(empno) from emp1;
执行计划
----------------------------------------------------------
Plan hash value: 1026340756
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN| EMP1_IDX | 14 | 182 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
当行数较少时，使用聚合函数,（函数是索引列）CBO会使用index full scan
另一种情况是，当你要查询出的数据全部可以从索引中直接得到，也就是说仅读索引块而不需要读表块，这时会选择index (fast) full scan
SQL> alter table emp1 modify (empno not null); --因索引的叶子块不存空值，使empno字段非空。
SQL> select empno from emp1; --数据库仅访问索引本身的数据。而无需访问表。
显示结果同上
例：索引快速扫描(index fast full scan)
SQL> insert into emp1 select * from emp1;
已创建14行。
SQL> /
.....
已创建14336行。
SQL> select count(empno) from emp1;
执行计划
----------------------------------------------------------
Plan hash value: 593157948
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 27 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INDEX FAST FULL SCAN| EMP1_IDX | 28672 | 86016 | 27 (0)| 00:00:01 |
----------------------------------------------------------------------------------
对比index full scan，当行数较多时，会使用index fast full scan
行数较多时index fast full scan 比index full scan计划成本要低的多，所以CBO优化器会优选 index fast full scan
两项再比较一下：
SQL> select empno from emp1;
执行计划
----------------------------------------------------------
Plan hash value: 1892048637
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28033 | 355K| 29 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| EMP1_IDX | 28033 | 355K| 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
可以加一行hint,强制oracle使用index full scan的执行计划，得到cost是100.
SQL> select /*+ index(emp1 emp1_idx) */ empno from emp1;
执行计划
----------------------------------------------------------
Plan hash value: 4252953140
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28033 | 355K| 100 (0)| 00:00:02 |
| 1 | INDEX FULL SCAN | EMP1_IDX | 28033 | 355K| 100 (0)| 00:00:02 |
-----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
可以看出：CBO在满足一定条件时会先选index fast full scan,而不是index full scan，因为前者的cost是29，比后者100低许多。
CBO优化器何时决定INDEX FULL SCAN 与 INDEX FAST FULL SCAN：
共性：当仅从索引表中就可以得到所要的查询结果，省去了第二步扫描表块。
个性：INDEX FAST FULL SCAN可以使用多块读，多块读由参数db_file_multiblock_read_count指定，适用于表行多时IO效率更高，而对于索引 列上order by之类的操作又几乎总是使用INDEX FULL SCAN。
SQL>
11.5 索引的碎片问题
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;
SQL> alter index ind_1 rebuild [online];
在Oracle文档里并没有清晰的给出索引碎片的量化标准,Oracle建议通过Segment Advisor(段顾问）解决表和索引的碎片问题（053课程会涉及），如果你想自行解决，可以通过查看index_stats视图，当以下三种情形之一发生时，说明积累的碎片应该整理了（仅供参考）。
1.HEIGHT >=4
2 PCT_USED< 50%
3 DEL_LF_ROWS/LF_ROWS>0.2
联机重建索引通常比删除后再重建要更实用，Oracle9i和10g一直提供联机索引重建功能--rebuild online,但由于涉及到使用表的排他锁，通常一段时间内其他用户的DML操作需要等待。
在Oracle11g中有了改进，实现了最终意义上的联机索引重建(rebuild index online)的特性，它重新设计了锁的模式，因此容许DML操作不受干扰。
11.6 索引不可见（invisible),11g新特性
在11g里， Oracle提供了一个新特性来降低直接删除索引或禁用索引的风险，那就是索引不可见（Index Invisible)。我们可以在创建索引时指定invisible属性或者用alter语句来修改索引为invisible(visible)（考点）
SQL> create table test (id int,name char(10));
SQL> create index test_idx on test(id)
SQL> alter index test_idx invisible;
SQL> select index_name,status from user_indexes;
注意 索引被设定为invisible后，视图user_indexes中status字段仍然是VALID，实际上就是指该索引对于优化器不可见，而索引的正常更新仍然会由有Oracle自动完成的。对比11g前的unusable，（保存索引定义，不删除索引，也不更新索引）。
SQL> alter index test_idx unusable;
SQL> select index_name,status from user_indexes;
索引被设定为 unusable后，视图user_indexes中status字段是unusable(考点）
查询索引的两个动态视图：
select * from dba_indexes;
select * from dba_ind_columns
SQL>
