博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
ORACLE FAST FULL INDEX SCAN vs. INDEX RANGE SCANS
阅读量:6835 次
发布时间:2019-06-26

本文共 3359 字,大约阅读时间需要 11 分钟。

  Today ,My colleague give me a question.

      Actually,he had wrote a effective sql .But,There is a small mistake in this sql.So I write a different sql using clause exists.Duing about 400 seconds,it can run out a result. A then we correct the mistake   which there was in that effective sql.

      so ,I compared  effective sql with ineffective sql -:)

      Look this effective sql:

     
select bt.num

from uin,  bt

where    bt.num = uin.phnum(+)

and uin.usin_phnum is null

and bt.num is not null


     In the plan of this sql,we found Fast full index scans.In the document of oracle official(10g):

Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least .e column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.


You can specify fast full index scans with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.


A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.
   

      But ,the plan of my ineffective sql uses index range scans.The de.ion of index range scan:

 
An index range scan is a common operation for accessing selective data. It can be bounded (bounded . both sides) or unbounded (on .e or both sides). Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.


If data must be sorted by order, then use the ORDER BY clause, and do not rely . an index. If an index can be used to satisfy an ORDER BY clause, then the optimizer uses this option and avoids a sort.


        Through those de.ions ,We can catch . the merits and shortcomings in ffs and irs respectively.In fact ,that effective sql in the where clause:bt.num = uin.phnum(+),the coloumn "phumn" is  null and there is index . it .Maybe ,Someone will ask this question:"Why did the ORACLE Optimizer brake the rule that ' at least .e column in the index key has the NOT NULL constraint'?"

        In my guess ,outer join is the real reason.Because,the optimizer convert the outer join into two phase,first is not null and then union all the "null" operation.

        All above, There is a huge benefit to not reading the table rows, but there are some requirements for Oracle to invoke the fast full-index scan.

     
1.All of the columns required must be specified in the index. That is, all columns in the select and where clauses must exist in the index.

2.The query returns more than 10 percent of the rows within the index. This 10 percent figure depends . the degree of multi-block reads and the degree of parallelism.

3.You are counting the number of rows in a table that meet a specific criterion. The fast full-index scan is almost always used for count(*) operations.

       By the way,the outer join need also think over.

       -----------------------------

       Oh,finish! it took my about two hours for this blog. You konw,We need experiment many times -:)

本文转自Be the miracle!博客51CTO博客,原文链接http://blog.51cto.com/miracle/126365如需转载请自行联系原作者

Larry.Yue

你可能感兴趣的文章
设计模式6大原则:里氏置换原则
查看>>
hbase0.94.14伪分布式安装
查看>>
Debug记录:vCenter6.5突然不能访问并报错“503 Service Unavailable”
查看>>
自动导出oracle的数据
查看>>
顺序表实现的源码
查看>>
我的友情链接
查看>>
iOS调用系统摄像头和相册
查看>>
mysql文件导入办法(直接copy数据库文件)
查看>>
善用WIN四把利剑提升服务器稳定性
查看>>
Hadoop学习笔记《关于HDFS》
查看>>
【二叉树】线索化二叉树
查看>>
Office365混合部署之用户权限(角色)分配
查看>>
logback配置
查看>>
rhel6配置多用户tiger vnc server
查看>>
Mac环境下svn的使用
查看>>
Pig读写HBase数据
查看>>
NumPy基础(一)
查看>>
菜鸟学Linux 第104篇笔记 varnish
查看>>
ATEN宏正盛装出席Infocomm China 2016
查看>>
并发数/QPS/PV/ 服务器响应时间公示
查看>>