关于index fast full scan

之前在工作的时候遇到过这样一个问题，表A_HEAD和表B_ATTR关联需要通过表A_LINE，但是A_LINE是一个10亿大的明细表，有100多个字段，而我实际SQL关联只用到了其中的2个字段
或者这种情况：查询某表中一共多少数据，展示出来，导致每次count(*)，或者count(ID)，同样的表很大，每次全表扫描非常慢。

通常情况如果我们只用到一个表中的一个字段（展示或者关联），这时候index fast full scan（后简称iffs）就派上用场了。

举个栗子！

还是那个表chenxu_table（详见http://blog.itpub.net/26224278/viewspace-1793672/）
表有700w+的数据量
如果我只用里面的object_id怎么办，比如我要求这个表有多少数据，或者使用object_id关联的时候，
SELECT T.OBJECT_ID
FROM TABLE_CHENXU T

网上有些资料是这样的：在object_id上建立normal索引就会走iffs。
其实这句话不完全正确的， 今天老头子来告诉你是怎么回事。

实验版本：Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

在 TABLE_CHENXU建立索引：

点击(此处)折叠或打开

1. create index INDEX_CHENXU_N1 on TABLE_CHENXU (OBJECT_ID)

查看执行计划（由于我之前实验已经在 TABLE_CHENXU上建立了索引，建索引时间实在太长我就偷个懒新建了个表 TABLE_CHENXU_TEST ）

点击(此处)折叠或打开

1. explain plan for
   
2. SELECT T.OBJECT_ID
   
3. FROM TABLE_CHENXU_TEST T;
   
4. select * from table(dbms_xplan.display())
   
5. 
   
6. Plan hash value: 2236262023
   
7.  
   
8. ---------------------------------------------------------------------------------------
   
9. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
   
10. ---------------------------------------------------------------------------------------
    
11. | 0 | SELECT STATEMENT | | 68924 | 336K| 284 (1)| 00:00:04 |
    
12. | 1 | TABLE ACCESS FULL| TABLE_CHENXU_TEST | 68924 | 336K| 284 (1)| 00:00:04 |
    

那么我们如何才能让这个SQL走iffs呢？
如下两种方法都可以：
1. 在建立索引的列上加上非空约束
2. 在建立索引的时候加上常量

先看第一种： 在建立索引的列上加上非空约束

点击(此处)折叠或打开

1. ALTER TABLE TABLE_CHENXU_TEST MODIFY object_id not null;

点击(此处)折叠或打开

1. explain plan for
   
2. SELECT T.OBJECT_ID
   
3. FROM TABLE_CHENXU_TEST T;
   
4. select * from table(dbms_xplan.display());
   
5. 
   
6. Plan hash value: 34473781
   
7.  
   
8. ---------------------------------------------------------------------------------------------------
   
9. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
   
10. ---------------------------------------------------------------------------------------------------
    
11. | 0 | SELECT STATEMENT | | 68924 | 336K| 43 (0)| 00:00:01 |
    
12. | 1 | INDEX FAST FULL SCAN| INDEX_TABLE_CHENXU_TEST_N1 | 68924 | 336K| 43 (0)| 00:00:01 |
    
13. ---------------------------------------------------------------------------------------------------

第二种：在建立索引的时候加上常量

点击(此处)折叠或打开

1. ALTER TABLE TABLE_CHENXU_TEST MODIFY OBJECT_ID NULL;
   
2. CREATE INDEX INDEX_CHENXU_N3 ON TABLE_CHENXU_TEST (OBJECT_ID,0);

点击(此处)折叠或打开

1. explain plan for
   
2. SELECT T.OBJECT_ID
   
3. FROM TABLE_CHENXU_TEST T;
   
4. select * from table(dbms_xplan.display());
   
5. 
   
6. Plan hash value: 3621541654
   
7.  
   
8. ----------------------------------------------------------------------------------------
   
9. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
   
10. ----------------------------------------------------------------------------------------
    
11. | 0 | SELECT STATEMENT | | 68924 | 336K| 48 (0)| 00:00:01 |
    
12. | 1 | INDEX FAST FULL SCAN| INDEX_CHENXU_N3 | 68924 | 336K| 48 (0)| 00:00:01 |
    

所以先直接在此字段上建立索引，Oracle是不会走iffs，依旧会走全表扫描，只有当CBO确定此列上无NULL值的时候，才会走IFFS。

结论：
INDEX FAST FULL SCAN 索引快速全扫描（把索引当表用）。多块读 。
当SQL要查询的数据能够完全从索引中获得，那么 Oracle就不会走全表扫描了，就会走索引快速全 扫描。索引快速全扫描类似全表扫描，它可以多块 读，并且可以并行扫描。
等待事件：db file scattered read
HINT：INDEX_FFS(表名/别名 索引名)

这里标黄色的部分需要加上一句话注释：且这个字段有非空约束，或这个索引有常量的时候，才会走iffs。
初步考虑到的原因是：索引中不存放空值，如果表中没有非空约束，CBO会进行全表扫描查看表中是否会有空值，所以不会走IFFS

不过我也做了实验对表进行直方图的收集，结果是即便进行了直方图统计也不会走iffs，关于这一点为什么，我还在研究，后续有时间追踪一下日志看下具体原理是怎样的，如有知道的大神求留言告知。

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

转载于:http://blog.itpub.net/26224278/viewspace-1793902/