`

《Pro Oracle SQL》Chapter3--3.2.4 Index range scan

阅读更多

Index range scan    索引范围扫描    (page 101)
    An index range scan is chosen when a predicate contains a condition that will return a range of data.  
The index can be unique or non-unique as it is the condition that determines whether or not multiple
rows will be returned or not.  The conditions specified can use operators such as <,  >,  LIKE,  BETWEEN  and even  =.  In order for a range scan to be selected, the range will need to be fairly selective.  The larger the range, the more likely a full scan operation will be chosen instead.   Listing 3-11 shows an example of a query that will produce an index range scan plan.
    当谓词包含条件将返回一范围内的数据时,则(可能)选择“索引范围扫描”。 索引可以是唯一或者不唯一的,因为条件确定了是或不是多行返回或者没有(返回空)。指定的条件可用操作符如<,>,LIKE,BETWEEN和甚至=。为了选中范围扫描,范围必须具有相当的选择性的。范围越大,越可能是选中了全扫描操作。 列表3-11展示了一个查询例子将生成一个索引范围扫描计划。
Listing 3-11. Index Range Scan  
SQL> set autotrace traceonly explain
SQL>
SQL> select * from hr.employees where department_id = 60 ;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2056577954
--------------------------------------------------------------------------------------------------------------------
|  Id   |   Operation                                       |  Name                             | Rows    |   Bytes  |   Cost(%CPU)|
--------------------------------------------------------------------------------------------------------------------
|    0 |   SELECT   STATEMENT                  |                                          |          5  |      340 |          2(0)     |
|    1 |     TABLE  ACCESSBY INDEX ROWID|  EMPLOYEES                   |          5  |      340 |          2(0)     |
|*   2  |       INDEX RANGE SCAN                 |  EMP_DEPARTMENT_I X  |          5  |             |          1(0)     |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-------------------------------------------------------------------
   2 - access("DEPARTMENT_ID"=60)

    A range scan wil traverse the index structrue from the root block to the first leaf block containing an entry matching the specified condition. From that starting point, a rowid will be retrieved from the index entry and the table data block will be retrieved (TABLE ACCESS BY INDEX ROWID). After the first row is retrieved, the index leaf block will be accessed again and the next entry will be read to retrieve the next rowid. This back-and-forth between the index leaf blocks and the data blocks will continue until all the matching index entries have been read. Therefore, the number of block accesses required will include the number of branch blocks in the index (this can be found using the blevel statistic for the index) plus the number of index entries that match the condition multiplied by two. You have to multiply by two because each retrieval of a single row in the table will require that the index leaf block be accessed to retrieve the rowid and then the table data block will be accessed using that rowid. Therefor, if the example returned 5 rows and the blevel was 3, the total block access required would (5 rows x 2) + 3 = 13.
    范围扫描遍历索引结构,从根块到第一个包含匹配指定条件记录的叶块。从那个起点开始,rowid将从索引记录中检索出再用于检索表数据块(TABLE ACCESS BY INDEX ROWID)。在第一行检索之后,将再访问索引叶块,读取下一条记录,检索下一个rowid。在索引叶块和数据块之间的不断的来回,直到所有匹配的索引记 录都读取完。因此,需要访问的块数包括索引中的分支块数(这可以通过索引的blevel统计找出)加上匹配条件的索引记录的数量乘以2。你必须乘以2, 因为每次对表中的单行检索都需要访问索引叶块获取rowid,再通过它去表数据块中检索。 因此,如果例子中返回5行且blevel是3,则需要访问的总块 数是(5 rows x 2) + 3 = 13 。
    If the range of entries matching the condition is large enough, it is likely that more than one leaf block will have to be accessed. When that is the case, the next leaf block needed can be read using a pointer stored in the current leaf block that leads to the next leaf blcok (there's also a pointer to the previous leaf block). Since these pointers exist, there is no need to go back up to ther branch block to determine where to go next.
    如果匹配条件的记录的范围足够的大,很可能需要访问多于一个叶块。这种情况下,可用存于当前叶块的指针指向到下一叶块,来访问所需的下一叶块(也有指向前一叶块的指针)。由于这些指针的存在,就不必回退到分支块,确定下一步如何走了。
    When an index range scan is chosen, the predicate information in the plan will show the condition used to access the index. In the example, step 2 in the plan has an asterisk beside it. This is an indicator that predicate information for that step is listed below the plan. In that section ,you see an entry showing that the index entry access was determined using the condition DEPRTMENT_ID = 60.
    当选中了索引范围扫描,计划中的谓词信息将展示用于访问索引的条件。 在例子中,计划中的步骤2有一个星号在旁边。这是一个标识,那一步的谓词信息列于计划下面。在那一段,你将看见一记录展示:索引记录访问确定使用条件DEPRTMENT_ID=60。
    There are cases when predicates that you might think should use index range scans do not. For example, if you use a LIKE operator with a condition that starts with a wild card such as '%abc', the optimizer will not choose a range scan on an index for that column because the condition is too broad.Another similar case is when you have a predicate the uses a column that isn't the leading column in a compound index. In that case, as I'll discuss shortly, it is more likely for an index skip scan to be chosen instead.
    有些情况当谓词并没有如你所料的使用索引范围扫描。例如,如果你使用LIKE操作符带有条件,由通配符开始如“%abc”,优化器将不选择那列上的索引范围扫描,因为条件太广了。另一个相似的情况是,当你有一谓词用的列不是复合索引中的前导列。 如果是那样,我将等会讨论,很可能是选中了索引跳跃扫描替代 了。
    One final nuance of an index range scan that I'd like to note is the ability of an ascending ordered index (the default) to return rows in descending sorted order. The optimizer may choose to use an index to access rows via an index even if a full scan might be warranted. This may occur when the query includes an ORDER BY clause on a column that is indexed. Since the index is stored in sorted order, reading rows using the index will mean the rows are retrieved in sorted order and  the need to do a separate sort step can be avoided. But, what if the ORDER BY clause is requested in descending order? Since the index is stored in ascending order, the index couldn't be used for a descending order request, could it ? Listing 3-12 shows an example of this behavior and the special range scan operation used to handle it.
    索引范围扫描最后的一个细微差别 ,我想要提出注意的能力是:(默认情况下)升序排列的索引可返回 呈降序排列 的行。 优化器可能使用索引访问行即使全扫描可能更合理的(保证的,担保的)。当查询包含在索引列上的ORDER BY子句,这种情况可能就会发生。由于索引是按序存储的,用索引读取行就意味着行是按序读取的,且单独的排序步骤就可以省略。 但是如果ORDER BY子句请求的是降序了?由于索引是按升序排列的,索引就不能用于降序的请求了,可以么?列表3-12展示了这个行为的一个例子而是用了特殊的范围扫描操作处理它。

Listing 3-12. An Index Range Scan Used to Avoid a Sort
SQL> set autotrace traceonly explain
SQL>
SQL> select * from hr.employees
  2   where department_id in (90, 100)
  3   order by department_id  desc;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3707994525
 
----------------------------------------------------------------------------------------
| Id  | Operation                                           | Name                            | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                                      |     9  |   612   |     2   (0)    |
|   1 |  INLIST ITERATOR                             |                                       |        |            |                  |
|   2 |   TABLE ACCESS BY INDEX ROWID   | EMPLOYEES                |     9  |   612    |     2   (0)    |
| 3 |    INDEX RANGE SCAN DESCENDING | EMP_DEPARTMENT_IX |     9  |            |     1   (0)     |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("DEPARTMENT_ID"=90 OR "DEPARTMENT_ID"=100)
       filter("DEPARTMENT_ID"=90 OR "DEPARTMENT_ID"=100)
 
In this case, the index entries are actually read in reverse order to avoid the need for a separate sort.
在这种情况下,索引记录实际上是反序读入的而避免需要单独的排序。

1
0
分享到:
评论

相关推荐

    spring 3.2.4.RELEASE jar包

    spring 3.2.4 Realease 的所有jar包: spring-context-3.2.4.RELEASE.jar spring-core-3.2.4.RELEASE.jar spring-beans-3.2.4.RELEASE.jar spring-test-3.2.4.RELEASE.jar spring-web-3.2.4.RELEASE.jar spring-aop-...

    spring3.2.4相关jar包

    java spring3.2.4 jar包 spring-aop-3.2.4.RELEASE spring-aspects-3.2.4.RELEASE spring-beans-3.2.4.RELEASE spring-build-src-3.2.4.RELEASE spring-context-3.2.4.RELEASE spring-context-support-3.2.4.RELEASE...

    spring-cglib-repack-3.2.4.jar和spring-objenesis-repack-2.5.1.jar

    在深入理解`spring-cglib-repack-3.2.4.jar`和`spring-objenesis-repack-2.5.1.jar`这两个jar包之前,我们先来了解一下Spring框架的核心概念。 Spring框架的核心包括依赖注入(Dependency Injection,DI)和面向切...

    wstx-asl-3.2.4.jar

    wstx-asl-3.2.4.jar wstx-asl-3.2.4.jar

    spring-orm-3.2.4.jar

    spring-orm-3.2.4.RELEASE.jar,ssh整合的时候用的上。

    mockito-core-3.2.4.jar

    mockito-core-3.2.4

    mongodb-win32-x86_64-2008plus-ssl-3.2.4-signed.msi

    8. **平台兼容性**:MongoDB支持多种操作系统,包括Windows,这里我们讨论的是适用于64位Windows 2008及更高版本的“mongodb-win32-x86_64-2008plus-ssl-3.2.4-signed.msi”安装包。 安装MongoDB的步骤通常包括: ...

    spring-context-3.2.4.RELEASE.jar

    支持springmvc框架,版本为3.2.4,Java技术开发使用

    MyBatisCodeHelper-Pro-3.2.4-SNAPSHOT-obfuscated.zip

    MyBatisCodeHelper-Pro-3.2.4-SNAPSHOT-obfuscated.zip

    spring-framework-3.2.4-RELEASE Maven Source Code

    《Spring Framework 3.2.4-RELEASE Maven Source Code 深度解析》 Spring Framework作为Java开发领域的重要支柱,其3.2.4版本在当时具有广泛的影响力。本源码包“spring-framework-3.2.4-RELEASE”不仅包含了Spring...

    mongodb-win32-i386-3.2.4-signed.zip

    这款"mongodb-win32-i386-3.2.4-signed.zip"压缩包是专为32位Windows操作系统设计的MongoDB安装资源,包含了MongoDB 3.2.4版本的安装程序。 MongoDB 3.2版是一个重要的里程碑,它引入了多项关键改进和新特性。以下...

    apache-cxf-3.2.4完整版

    - **API稳定**:3.x系列是CXF的稳定版本,3.2.4在3.2.x分支上,意味着API已经相当成熟,开发者可以放心使用。 - **bug修复**:每次新版本发布通常都会修复一些已知的问题,提高整体的软件质量。 - **新特性**:...

    cglib-nodep-3.2.4-API文档-中文版.zip

    赠送原API文档:cglib-nodep-3.2.4-javadoc.jar; 赠送源代码:cglib-nodep-3.2.4-sources.jar; 赠送Maven依赖信息文件:cglib-nodep-3.2.4.pom; 包含翻译后的API文档:cglib-nodep-3.2.4-javadoc-API文档-中文...

    开发工具 cglib-3.2.4

    开发工具 cglib-3.2.4开发工具 cglib-3.2.4开发工具 cglib-3.2.4开发工具 cglib-3.2.4开发工具 cglib-3.2.4开发工具 cglib-3.2.4开发工具 cglib-3.2.4开发工具 cglib-3.2.4开发工具 cglib-3.2.4开发工具 cglib-3.2.4...

    QLExpress-3.2.4.jar

    QLExpress-3.2.4.jarQLExpress-3.2.4.jarQLExpress-3.2.4.jarQLExpress-3.2.4.jarQLExpress-3.2.4.jarQLExpress-3.2.4.jarQLExpress-3.2.4.jarQLExpress-3.2.4.jarQLExpress-3.2.4.jarQLExpress-3.2.4.jarQLExpress-...

    ffmpeg-win32-v3.2.4.zip

    报错 Imageio: 'ffmpeg-win32-v3.2.4.exe' was not found on your computer; downloading it now. 因为代码会检测在C:\Users\Administrator\AppData\Local\imageio\ffmpeg 目录下有没有'fwmpeag-win32-v3.2.4.exe...

    activemq-web-3.2.4.jar.zip

    《ActiveMQ Web 3.2.4:Java消息服务与Web集成详解》 Apache ActiveMQ是业界广泛使用的开源消息代理,它遵循Java消息服务(JMS)标准,为分布式环境中的应用程序提供高效、可靠的异步通信。在本篇中,我们将深入...

    apache-cxf-3.2.4

    3. **CXF的功能**: - **代码生成**:CXF的Wsdl2Java工具可以将WSDL文件转换为Java类和服务接口,这样开发者只需要关注业务逻辑,而无需手动编写繁琐的Web服务代码。 - **绑定层**:CXF支持多种绑定,包括JAX-WS...

    ffmpeg-win32-v3.2.4.exe

    报错 Imageio: 'ffmpeg-win32-v3.2.4.exe' was not found on your computer; downloading it now. 因为代码会检测在C:\Users\Administrator\AppData\Local\imageio\ffmpeg 目录下有没有'fwmpeag-win32-v3.2.4....

    spring-framework-3.2.4.RELEASE-dist.rar

    Spring Framework 3.2.4.RELEASE 是一个重要的版本,它是Spring生态系统的里程碑,为开发者提供了全面的Java应用程序开发支持,特别是针对企业级应用。这个版本包含了完整的文档、jar包、以及源代码,使得开发者可以...

Global site tag (gtag.js) - Google Analytics