《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.
在这种情况下，索引记录实际上是反序读入的而避免需要单独的排序。