《Pro Oracle SQL》Chapter3--3.2.2 Index Scan Types
Index Scan Types 索引扫描类型 (page 97)There are several different types of index scans but each share some common ground in how they must traverse the index structure to access the leaf block entries that match the values being searched. First, the root block of the index is accessed with a single block read. The next step is to read a branch block. Depending on the height of the index, one or more branch blocks may need to be read. Each read is for a separate single block. Finally, the first index leaf block that contains the start of the index entries
needed is read. If the height of an index is 4, to get to the leaf block needed, 4 single block reads will be
performed. At this point, the rowid for the first matching index value in the leaf block is read and used
to make a single block read call to retrieve the table block where the entire row resides. Therefore, in
this example, to retrieve a single row from a table using an index, Oracle would have to read 5 blocks: 4
index blocks and 1 table block.
有好几种类型的索引扫描，但是都享有一些共同点，它们如何必须遍历索引结构获取访问匹配搜索值的叶块记录。首先，用一次单块读访问索引的根块。下一步是读一个分支块。依赖于索引的高度，可能需要读取一个或多个分支块。对每个独立的单块都要读取一次。最后，第一个索引叶块，包含所需索引记录的开端(的那个块)，被读入。如果索引的高度是4，要获取所需的叶块，就要执行4次单块读。从这点起，在叶块中第一次匹配的索引值的rowid被读入，且用于一单块读调用检索整个行驻留的表块。因此，在本例中，用一索引从表中检索一单行，Oracle必须读5个块：4个索引块和一个表块。
The various index scan types you will review are index range scan, index unique scan, index full
scan, index skip scan, and index fast full scan. An index fast full scan is actually more like a full table
scan, but since they are scans against an index structure I’ll cover them in this section.
你将学到各种索引扫描类型：索引范围扫描，索引唯一扫描，索引全扫描，索引跳跃扫描，索引快速全扫描。索引快速全扫描实际上更像全表扫描，但是他是扫描索引结构，我将在本节中讨论它。
Before I review the different scan types, I want to point out a very important index statistic called
clustering factor. The clustering factor statistic of an index helps the optimizer generate the cost of
using the index and is a measure of how well ordered the table data is as related to the indexed values.
Recall that index entries are stored in sorted order while table data is stored in random order. Unless
an effort has been made to specifically load data into a table in a specific order, you are not
guaranteed where individual rows of data will end up. For example, rows from the orders table that
share the same order_date may not all reside in the same blocks. They are likely to be scattered
randomly across the blocks in the table.
在我讨论不同的扫描类型之前，我要指出一个非常重要的索引统计信息称为“(聚)簇因子”。一个索引的簇因子统计帮助优化器产生索引使用的成本而且是相关索引值的表数据如何有序排列的度量。回想一下，索引记录是按顺序存储的而表数据是随机顺序存储的。除非故意以特定的顺序向表装载数据，你不能保证行之间的数据会结束。例如，orders表的行共享相同的order_date列，可能不会驻留在相同的块中。它们可能随机的、跨越的分布的在表的多个块中。
The clustering factor of an index indicates to the optimizer if data rows containing the same
indexed values will be located in the same or a small set of contiguous blocks, or if rows will be
scattered across numerous table blocks. Figure 3-5 shows how the rows might be stored physically in
the table’s data blocks.
一索引的簇因子向优化器指出是否包含相同的索引值的数据行将位于相同的或一小段连续的块中，或者行是否跨越了很多表块。图3-5展示了行是如何可能物理的存入表的数据块中。
In the diagram showing table T1, you see how rows containing the value 2 were loaded into the
same block. But, in table T2, rows with a value of 2 are not loaded in contiguous blocks. In this
example, an index on this column for table T1 would have a lower clustering factor. Lower numbers
that are closer to the number of table blocks are used to indicate highly ordered, or clustered, rows of
data based on the indexed value. The clustering factor for this column in table T2, however, would be
higher and typically closer to the number of rows in the table. Listing 3-8 shows the clustering factor
statistic for each of these two tables.
页:
[1]