1.基本的索引概念
【索引的五种扫描类型】
INDEX UNIQUE SCAN
INDEX RANGE SCAN
INDEX FAST FULL SCAN
INDEX FULL SCAN
index full scan(max/min)--10g后出现的索引扫描方法
INDEX SKIP SCAN
BITMAP INDEX SINGLE VALUE
BITMAP INDEX FAST FULL SCAN
2.组合索引
组合索引的扫描类型：UNIQUE SCAN、RANGE SCAN。如果组合索引的首列包括在查询中，就可以使用这两种扫描类型。
组全索引的特殊扫描类型：FAST FULL SCAN、INDEX FULL SCAN（FFS完全可以代替IFS）、INDEX SKIP SCAN 。如果组合索引的首列不包含在查询中，可以考虑使用这几种扫描类型。
使用FAST FULL SCAN的条件，除了上一条限制，还有注意
（1）所有的查询列都包含在索引中，否则，执行FFS之外，还要再执行物理扫描查询不包含在索引中的列。这样执行效率会比全表扫描还要低
（2）FFS使用的索引如果包含了表的大部分列，他就有可以比表还大。效率反而比全表扫描还要低
INDEX SKIP SCAN：查询结果列不在索引列中，在结果集很小的情况下，INDEX SKIP SCAN应该也要比全表扫描快。
3.限制索引
3.1使用不等于运算符（<>,!=）
3.2关于大于(>)、小于( --deptno的最大值是43149
--使用索引的SQL
select * from DEPT d where d.deptno>40149;
select * from DEPT d where d.deptno<30;
--不使用索引的SQL
select * from DEPT d where d.deptno<40149;
select * from DEPT d where d.deptno>30;
小结：大于号与小于号是否使用索引取于结果集的大小？如果结果集比较大，优化器会认为走全表扫描的代价更小，就会放弃使用索引。
3.3使用IS NULL或IS NOT NULL
在WHERE子句中使用IS NULL或IS NOT NULL同样会限制索引的使用。
如果被索引的列在某些行中存在NULL值，在索引中就不会有相应的条目（例外情况是位图索引，这是位图索引对于NULL搜索通常较为快速的原因）。
3.4使用函数
如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。
select * from DEPT d where to_char(d.deptno)='600';
3.5比较不匹配的数据类型
--场景1：number类型的索引与varchar2类型比较
select * from DEPT d where d.deptno='600';--deptno是
--场景2：varchar2类型的索引与number类型比较
select * from DEPT d where d.dname=999; --dname是varchar2类型
--小结：不同类型的值比较，ORACLE会隐式地把VARCHAR2转换成NUMBER后，再比较。场景1是将'600'转换成了600后再比较，场景1使用了索引。
而场景2是将dname列转换成了number类型之后进行的比较，场景2没有使用索引。
4.选择性
oracle根据查询数据，提供了多种方法来判断使用索引的价值。第一个方法就是判断索引中的唯一键的数量。
select
i.INDEX_NAME,
i.index_type,
i.DISTINCT_KEYS,--索引：唯一键的数据
i.NUM_ROWS, --表：数据行数
i.NUM_ROWS/i.DISTINCT_KEYS --值为1，选择性最好
from user_indexes i;
【技巧】索引的选择性可以帮助基于成本的优化器来判断执行路径。索引的选择性越高，针对每个不同值返回的行数也越少。
5.集群因子（clustering factor）
select i.INDEX_NAME,i.TABLE_NAME,i.CLUSTERING_FACTOR,i.LEAF_BLOCKS,i.NUM_ROWS
from user_indexes i;
select * from user_ind_columns;
集群因子的计算简要介绍如下：
A.按顺序扫描索引
B.将当前索引值指向的ROWID的块部分与以前的索引值进行比较（*比较索引中的邻近行）
C.如果ROWID指向不同的TABLE块，则增加集群因子
--分析：为什么dname列上的集群因子高
select deptno,--主键，NUMBER
dname--此列的值是使用UPDATE更新的，值与deptno相同，但是数据类型是NUMBER
from DEPT d ;
--分析：因为数据是按deptno从小到大（数值型排序）产生并保证到数据块中的。
dname是字符型的，字符型的排序顺序与数值不一样，所以集群因子大
【注】也可以理解为，集群因子代表索引的排序顺序与物理上的排序顺序的一致程度。如果两者顺序相近，则集群因子与叶块的数量就会相近。
否则，集群因子有可能远远大于叶块数量。较高的集群因子，会产生较高IO。
集群因子对执行范围扫描的SQL语句有一定的影响。如果集群因子很低（相对于树叶块的数量），需要读取的表中块的数量就可以减少很多。一个较高
的集群因子（相对于树叶块的数量）会增加满足基于索引列的范围查询所需的数据块数目。
6.二元高度
索引是树形结构 2元高度 就是从 根出发 经过 树枝 到达 叶块 所以需要的块数就是2元高度。
那么数据块越大存放的信息越多 产生的分支就越少 树就越简单啊！
select i.INDEX_NAME,i.TABLE_NAME,i.CLUSTERING_FACTOR,i.LEAF_BLOCKS,i.NUM_ROWS
,i.blevel
from user_indexes i;
?
7.使用直方图（柱状图）：
【直方图作用】出现数据偏斜问题时，帮助优化器决定是否使用索引
【实验：测试直方图】
第一步：造数据
select deptno,loc from DEPT t order by deptno desc ;--43149
deptno的值从1至43149；
当deptno<=1000时，loc='aaaaa';
deptno>1000 and deptno<=10000 then loc='bbbbb'
deptno>10000 and deptno<=43000 then loc='ccccc'
deptno>43000时，loc=to_char(deptno)
－－－－－－－－－－－－－－－－
update dept set loc='aaaaa' where deptno<=1000;
update dept set loc='bbbbb' where deptno>1000 and deptno<=10000;
update dept set loc='ccccc' where deptno>10000 and deptno<=43000;
update dept set loc=to_char(deptno) where deptno>43000;
commit;
第二步：在loc上创建索引（不分析表）
第三步：测试索引的使用情况
-- select * from dept t where t.loc='aaaaa';--走索引
--select * from dept t where t.loc='bbbbb'--走索引
--select * from dept t where t.loc='ccccc'--走索引
--select * from dept t where t.loc='43123'--走索引
第四步：分析表后再按第三步测试
--4.1分析表（1秒）
begin
dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'dept');
end;
--4.2测试
--select * from dept t where t.loc='aaaaa'--走索引
--select * from dept t where t.loc='bbbbb'--全表扫描
--select * from dept t where t.loc='ccccc'--全表扫描
--select * from dept t where t.loc='43123'--走索引
第五步：创建直方图后按第三步测试
--5.1创建直方图
begin
dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'dept',method_opt => 'FOR columns size 20 loc');
end;
--5.2测试结果
--select * from dept t where t.loc='aaaaa'--全表扫描
--select * from dept t where t.loc='bbbbb'--全表扫描
--select * from dept t where t.loc='ccccc'--全表扫描
--select * from dept t where t.loc='43123'--全表扫描
--5.3创建直方图(75个桶)
begin
dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'dept',method_opt => 'FOR COLUMNS SIZE 75 LOC');--FOR ALL COLUMNS SIZE 75
end;
--5.4测试结果
--select * from dept t where t.loc='aaaaa'--走索引
--select * from dept t where t.loc='bbbbb'--全表扫描
--select * from dept t where t.loc='ccccc'--全表扫描
--select * from dept t where t.loc='43123'--走索引
第六步：按比例收集统计量信息
--6.1（0.6秒）
begin
dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'dept',estimate_percent =>0.1 );
end;
--6.2测试结果
--select * from dept t where t.loc='aaaaa'--全表扫描
--select * from dept t where t.loc='bbbbb'--全表扫描
--select * from dept t where t.loc='ccccc'--全表扫描
--select * from dept t where t.loc='43123'--全表扫描
【实验小结】
1.在收集表的统计信息之后，优化器成功识别了数据偏斜问题。从实验结果来后，4.1步的操作产生了直方图
2.5.1步对解决数据偏斜问题是无意的,5.3的结果是正确的。所以手工创建直方图，要使用足够的桶（75是ORACLE默认的）
3.按比例收集统计量信息，解决不了数据偏斜问题
4.收集表的统计信息（按100%的比例收集）即可解决数据偏斜问题
5.考虑大表效率问题：（1）可以按比例收集表的统计量信息；（2）对数据偏斜的列单独创建直方图
在分析表或索引时，直方图用于记录数据的分布。通过获得该信息，基于成本的优化器就可以决定使用返回少量行的索引，而避免使用基于限制条件返回许多行的索引。
构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规化：例如，如果一到两个值构成了表中的大部分数据（数据偏斜），相关的索引就可能无法帮助
减少满足查询所需的I/O数量。创建直方图可以让基于成本的优化器知道何时使用索引才最合适，或何时应该根据WHERE子句中的值返回表中80%的记录。
【创建直方图】
begin
dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'dept',method_opt => 'FOR columns size 20 loc');
end;
ORACLE的直方图是高度均衡的，而不是宽度均衡的。
直方图的使用不受索引的限制，可以在表的任何列上构建直方图。
8.快速全局扫描
【注意】ITPUB讨论的问题 http://www.itpub.net/thread-1744681-1-1.html
全局扫描和FFS需要在列上使用NOT NULL约束（如果是复合索引，至少的有一列为NOT NULL）或在WHERE 后加IS NOT NULL。
以前只知道where后面的 IS NOT NULL会限制索引的唯一值扫描和范围扫描。不知道IS NOT NULL可以帮优化器走全局扫描。学习了！
在索引的快速全局扫描期间，Oracle读取B树索引上的所有树叶块。这个索引可以按顺序读取，这样可以一次读取多个块。初始化文件中的DB_FILE_MULTIBLOCK_READ_COUNT
参数可以控制同时被读取的块的数目。相比于全表扫描，快速全局扫描通常需要较少的物理I/O，并且允许更快地处理查询。
如果表查询中的所有列都被包括在索引里，而索引的前置列并不在WHERE条件中，就可以使用快速全局扫描（必须使用INDEX_FFS）。作为选择，Oracle可能执行索引的跳跃
式扫描。
【技巧】如果索引相对于表的总体尺寸来说很小，快速全局扫描就可以使应用程序的性能陡增。如果表中有一个包含了大部分列的组合索引，索引可能要比真实的表要大，
这样快速全局扫描反而会降低性能。
【实验1】快速全局扫描与跳跃式扫描
create index INDEX4 on DEPT (LOC, DEPTNO);
select * from scott.dept t where t.deptno=600;
select /*+index_ffs(t index4) */ * from scott.dept t where t.deptno=600;--与下面的跟踪结果相同
--跟踪结果
----------------------------------------------------------------------------------
SQL> set autotrace on statistics explain; --set autotrace traceonly statistics explain;
SQL> select * from scott.dept t where t.deptno=600;
DEPTNO DNAME LOC
---------- -------------- -------------
600 600 aaaaa
Execution Plan
----------------------------------------------------------
Plan hash value: 1812805614
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 1 | 16 | 23 (0)| 00:0
0:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 16 | 23 (0)| 00:0
0:01 |
|* 2 | INDEX SKIP SCAN | INDEX4 | 1 | | 22 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."DEPTNO"=600)
filter("T"."DEPTNO"=600)
Statistics
----------------------------------------------------------
172 recursive calls
0 db block gets
49 consistent gets
0 physical reads
0 redo size
531 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
----------------------------------------------------------------------------------
【实验1结论】使用INDEX_FFS时，作为选择，Oracle可能执行索引的跳跃式扫描
【实验2】快速全局扫描与全表扫描比较
-------------------------------------------------------------------------------------
create index INDEX4 on DEPT (LOC, DEPTNO, DNAME);
SQL> select /*+ index_ffs(t index4)*/ deptno,dname from scott.dept t where t.deptno=600 and t.dname='600';
DEPTNO DNAME
---------- --------------
600 600
Execution Plan
----------------------------------------------------------
Plan hash value: 3621602317
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 35 (3)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| INDEX4 | 1 | 11 | 35 (3)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."DEPTNO"=600 AND "T"."DNAME"='600')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
174 consistent gets
166 physical reads
0 redo size
468 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ no_index(t)*/ deptno,dname from scott.dept t where t.deptno=600 and t.dname='600';
DEPTNO DNAME
---------- --------------
600 600
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 38 (6)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DEPT | 1 | 11 | 38 (6)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."DEPTNO"=600 AND "T"."DNAME"='600')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
185 consistent gets
0 physical reads
0 redo size
468 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-------------------------------------------------------------------------------------
【实验2结论】与全表扫描相比，FFS虽然总代价小一点，但产生了“166 physical reads”。为什么会产生大量的物理读？
【实验3】快速全局扫描与全局扫描的区别
create index IND_SCOTT on DEPT (LOC);
---------------------------------------------------------------------------------------------------
SQL> select /*+index(t ind_scott)*/ count(t.loc) from scott.dept t;
COUNT(T.LOC)
------------
43105
Execution Plan
----------------------------------------------------------
Plan hash value: 2803976732
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 115 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | INDEX FULL SCAN| IND_SCOTT | 43105 | 252K| 115 (1)| 00:00:02 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
204 recursive calls
0 db block gets
269 consistent gets
0 physical reads
0 redo size
417 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+index_ffs(t ind_scott)*/ count(t.loc) from scott.dept t;
COUNT(T.LOC)
------------
43105
Execution Plan
----------------------------------------------------------
Plan hash value: 4213602357
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
---
| 0 | SELECT STATEMENT | | 1 | 6 | 24 (5)| 00:00:0
1 |
| 1 | SORT AGGREGATE | | 1 | 6 | |
|
| 2 | INDEX FAST FULL SCAN| IND_SCOTT | 43105 | 252K| 24 (5)| 00:00:0
1 |
--------------------------------------------------------------------------------
---
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
250 consistent gets
0 physical reads
0 redo size
417 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
---------------------------------------------------------------------------------------------------
【结论】从实验结果后,(1)FFS的总成本要远远小于全局扫描；(2)FFS的一致读稍少一些，但差别不大。 (3)ffs使用了5%的CPU资源，而全局扫描只使用了1%。
试分析原因：FFS是（1）FFS效率高的最主要原因是可以一次读取多个块。
（2）FFS可以一次读取多个块是由他的读取方式决定的。
（2）FFS是按逻辑存储结构读取的块，即按索引段的结构并发地读取数据块；
IFS是（1）遍历索引的数据结构（例如，B树索引是树形结构）
（2）遍历顺序：root块，找到第一个branch块，再找第一个leaf块，根据第一个LEAF的双向链表地址读取下一下LEAF块。是串行访问。
因为是通过链表读取下一个块，所以不能一次读取多个块
9.跳跃式扫描
索引跳跃式扫描特性允许优化器使用组合索引，即便索引的第一列没有出现在WHERE子句中。索引跳跃式扫描比索引扫描快得多
【实验1】
create index INDEX4 on DEPT (LOC, DEPTNO);
--禁用deptno列直方图
begin
dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'dept',method_opt => 'FOR COLUMNS SIZE 1 deptno');--FOR ALL COLUMNS SIZE 75
end;
--结果下面的SQL仍然“跳跃式扫描”
select * from dept t where t.deptno=600
--执行计划
-----------------------------------------------
SELECT STATEMENT, GOAL = ALL_ROWS 4 1 16
TABLE ACCESS BY INDEX ROWID SCOTT DEPT 4 1 16
INDEX SKIP SCAN SCOTT INDEX4 3 1
------------------------------------------------
【8中的两个实验说明，有两个索引列的复合索引常用跳跃式索引扫描】
是否使用跳跃式扫描是由优化器决定的，没有HINT可以实现手工执行“跳跃式索引扫描”
【实验2】
create index INDDEP3 on DEPT (LOC, DNAME);
select /*+index_ffs(t inddep3)*/ count(*) from scott.dept t where t.dname='600'; ffs:cost 47；436 consistent gets
select /*+no_index(t inddep3)*/ count(*) from scott.dept t where t.dname='600'; table access full:cost 38；173 consistent gets
select /*skip index*/ count(*) from scott.dept t where t.dname='600'; index skip scan:cost 3 ; 10 consistent gets
【实验2结论】跳跃式扫描的I/O是最少的，总成本也是最小的。
【其他注意事项】
1.收集统计信息
2.min,max一起选无not null约束就不走索引
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7901922/viewspace-1060010/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/7901922/viewspace-1060010/
6443
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
