================
1、索引的作用
索引可以避免全表扫描，尽快的找到数据
在对数据进行select、delete、update的时候，正确使用索引都可以加快数据的选取并最终起到加速的作用
但在增加数据的时候，新增的数据需要被写在索引当中，所以会带来性能的负担
索引可以加快查询，减慢插入
==================
2、索引的种类
B-Tree
oracle默认的索引类型，采用二叉树结构，每个索引都有一个根节点(root)
对索引进行访问的时候都是从root节点开始，找到相应的分支节点(branch),再和分支节点的值进行对比，最终找到叶子节点(leaf)
在叶子节点上记录了对应值的数据的物理地址(rowid),根据rowid来访问数据，最终达到加速查询的作用。
对于唯一索引，一个索引叶子节点值对应一个rowid，进行查询的时候效率很高，但如果表中建立索引的重复字段太多，建立B-Tree索引的意义就不大了。
以NULL为条件的查询不能使用B-Tree索引
-------------------
Bitmap
和B-Tree索引不同，Bitmap索引适用于值重复比较多的情况。
位图索引对每一个键值分别索引，对一个键值，可能分成一到多个范围进行存储。
每一个键值的存储范围大致包括几个部分：
首先是索引的键值，接着当前范围的其始rowid和终止rowid，最后是这个键值在这个范围的位置编码
将十六位的位置编码转化成二进制后，1表示符合，0表示不符和。
Bitmap索引插入一次索引一次，所以对批量数据插入的效率要比B-Tree索引要高
<注意>：
基于规则的优化器不能使用这种索引，必须是基于消耗的优化器
并不是所有的数据库都支持位图索引，oracle在CBO模式下才支持位图索引
这种索引适合静态表，并且字段值重复比较多的情况，适合应用在数据仓库
可以识别以NULL为条件的查询使用索引
建立速度快
不能建立唯一索引
-------------------
FBI
函数索引：
当where条件中使用了函数的话，B-Tree索引不能使用，这时就要使用函数索引
<注意>：
使用函数索引有以下限制
a、必须有query rewrite或global query rewrite权限
b、鄙视使用基于成本的优化器，基于规则的优化器将被忽略
c、必须设置以下两个参数
query_rewrite_enabled=true
query_rewrite_integrity=trusted
====================
各种索引的建立方法
1、B-Tree
SQL> create index b_tree_index on test(id);
索引已创建。
2、反向索引
SQL> create index r_index on test(name) reverse;
索引已创建。
3、降序索引
SQL> create index desc_index on test(age desc);
索引已创建。
4、位图索引
SQL> create bitmap index bitmap_index on test(gread);
索引已创建。
====================
索引使用的场合
1、B-Tree索引
常规的索引，多用于OLTP系统，能够快速定位行，应建立在高唯一值的行上
2、反向索引
B-Tree索引的衍生产物，应用于特殊场合
在OPS环境加序列增加的列上建立，不适合做区域扫描
3、降序索引
B-Tree索引的衍生产物，应用于有降序搜索语句的环境中
4、位图索引
位图方式管理的索引，适合于OLAP和DSS系统
不能建立唯一索引，应建立在低唯一值的字段上
适合集中读取，集中插入
不适合分散插入和更新修改
和B-Tree索引相比更节省空间
5、函数索引
B-Tree索引的衍生产物，应用于查询语句条件列上包含函数的情况
索引中经过函数计算的索引码值，可以在不修改应用程序的基础上提高查询效率
====================
索引不被引用的情况
1、同一个表中两个字段相比较的时候，如果两个字段分别建立索引，查询时是不引用的
实验：
SQL> create table test as select 1 a,2 b from dual;
表已创建。
SQL> insert into test select 3,3 from dual;
已创建 1 行。
SQL> commit;
提交完成。
SQL> create index a on test(a);
索引已创建。
SQL> create index b on test(b);
索引已创建。
SQL> select * from test where a=b;
A B
---------- ----------
3 3
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
Id
Operation
Name
Rows
Bytes
Cost (%CPU)
Time
--------------------------------------------------------------------------
0
SELECT STATEMENT
1
26
3 (0)
00:00:01
* 1
TABLE ACCESS FULL
TEST
1
26
3 (0)
00:00:01
--------------------------------------------------------------------------
这时走的是全表扫描
SQL> create index ab on test(a,b);
索引已创建。
执行计划
----------------------------------------------------------
Plan hash value: 3360239467
-------------------------------------------------------------------------
Id
Operation
Name
Rows
Bytes
Cost (%CPU)
Time
-------------------------------------------------------------------------
0
SELECT STATEMENT
1
26
1 (0)
00:00:01
* 1
INDEX FULL SCAN
AB
1
26
1 (0)
00:00:01
-------------------------------------------------------------------------
这时走的才是索引
---------------------
2、NULL值
B-Tree索引是不能对NULL值进行索引的，所以条件中有null索引回失效
SQL> select * from test where b is null;
A B
---------- ----------
5
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
Id
Operation
Name
Rows
Bytes
Cost (%CPU)
Time
--------------------------------------------------------------------------
0
SELECT STATEMENT
1
26
3 (0)
00:00:01
* 1
TABLE ACCESS FULL
TEST
1
26
3 (0)
00:00:01
--------------------------------------------------------------------------
这种情况需要使用Bitmap索引
SQL> create bitmap index b on test(b);
索引已创建。
1* select * from test where b is null
A B
---------- ----------
5
执行计划
----------------------------------------------------------
Plan hash value: 3296254258
-------------------------------------------------------------------------------------
Id
Operation
Name
Rows
Bytes
Cost (%CPU)
Time
-------------------------------------------------------------------------------------
0
SELECT STATEMENT
1
26
2 (0)
00:00:01
1
TABLE ACCESS BY INDEX ROWID
TEST
1
26
2 (0)
00:00:01
2
BITMAP CONVERSION TO ROWIDS
* 3
BITMAP INDEX SINGLE VALUE
B
-------------------------------------------------------------------------------------
----------------
3、not 函数
当使用not in、not exist或者<>时不会使用索引
SQL> select * from test where a<>3;
A B
---------- ----------
1 2
5
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
Id
Operation
Name
Rows
Bytes
Cost (%CPU)
Time
--------------------------------------------------------------------------
0
SELECT STATEMENT
2
52
3 (0)
00:00:01
* 1
TABLE ACCESS FULL
TEST
2
52
3 (0)
00:00:01
--------------------------------------------------------------------------
SQL> select * from test where a not in(1,2);
A B
---------- ----------
5
3 3
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
Id
Operation
Name
Rows
Bytes
Cost (%CPU)
Time
--------------------------------------------------------------------------
0
SELECT STATEMENT
2
52
3 (0)
00:00:01
* 1
TABLE ACCESS FULL
TEST
2
52
3 (0)
00:00:01
--------------------------------------------------------------------------
SQL> select * from test where not exists(
2 select a from test where a<5);
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2945203544
---------------------------------------------------------------------------
Id
Operation
Name
Rows
Bytes
Cost (%CPU)
Time
---------------------------------------------------------------------------
0
SELECT STATEMENT
3
78
4 (0)
00:00:01
* 1
FILTER
2
TABLE ACCESS FULL
TEST
3
78
3 (0)
00:00:01
* 3
INDEX RANGE SCAN
A
2
26
1 (0)
00:00:01
---------------------------------------------------------------------------
所以这些情况能避免尽量避免
------------------
4、当使用single-row函数的时候，如nvl,to_char,lower的时候索引是不能被使用的
SQL> select * from test where to_char(a)='5';
A B
---------- ----------
5
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
Id
Operation
Name
Rows
Bytes
Cost (%CPU)
Time
--------------------------------------------------------------------------
0
SELECT STATEMENT
1
26
3 (0)
00:00:01
* 1
TABLE ACCESS FULL
TEST
1
26
3 (0)
00:00:01
--------------------------------------------------------------------------
============================
索引的重建
重建的索引基本上都是B-Tree索引
对于更新频繁的表，数据插入时，依附在相应PCTFREE List上面的索引块的后面。
如果这个块所包含的数据达到了PCTFREE的额度，就导致索引数据所在的块分裂成了两个，到了一定程度，B-Tree索引就会加深一层(split/move down to next level)
当表delete很多数据的时候，索引站用的空间却不能被释放，所以需要重新建立索引
当发生以下情况时需要重新建立索引
1、索引的层数超过4层
2、被删除数据超过索引数据总量的20%的时候
查看删除数据的百分比可以用下面的方法：
实验：
SQL> create table test as select table_name name from all_tables;
表已创建。
SQL> select count(*) from test;
COUNT(*)
----------
1216
SQL> create index test_index on test(name);
索引已创建。
SQL> insert into test select * from test;
已创建1216行。
SQL> /
已创建2432行。
SQL> /
已创建4864行。
SQL> /
已创建9728行。
SQL> /
已创建19456行。
SQL> commit;
提交完成。
SQL> select count(*) from test;
COUNT(*)
----------
38912
SQL> delete from test where rownum<30001;
已删除30000行。
SQL> commit;
提交完成。
SQL> analyze index test_index validate structure;
索引已分析
SQL> select (del_lf_rows_len/lf_rows_len)*100 as index_usage
2 from index_stats
3 where name='TEST_INDEX';
INDEX_USAGE
-----------
77.2432455
删除的数据占数据总量的77%以上，说明该索引需要重建
SQL> alter index test_index rebuild;
索引已更改。
SQL> analyze index test_index validate structure;
索引已分析
SQL> select (del_lf_rows_len/lf_rows_len)*100 as index_usage
2 from index_stats
3 where name='TEST_INDEX';
INDEX_USAGE
-----------
0
---------------------------
通常在生产环境中，索引很可能都建在同一个表空间中，如果这样的话，通过OEM来管理索引的话就很容易
可以使用
存储--->表空间--->索引表空间
右键点击索引表空间，选择tablespace map，
存在于该表空间的段就会显示出来，如果分析项是红色的就表示该索引需要重建
========================
索引的管理
当发现一个经常要做的查询消耗的时间很长，并且出现了全表扫描的等待的话，就要考虑在需要的字段上建立索引
对于索引过多的问题，一般是很难发现的，但可以通过
alter index index_name monitoring usage;
alter index index_name nomonitoring usage;
来监控，最终确定该索引是否需要
===============================
将索引移动表空间
alter index index_name rebuild tablespace tablespace_name;