Oracle Database performs I/O by blocks. Therefore, the optimizer's decision to use full table scans is influenced by the percentage of blocks accessed, not

rows.
Oracle数据库执行I / O块。因此，优化的影响决定使用全表扫描的块访问，而不是行的百分比。

This is called the index clustering factor. If blocks contain single rows, then rows accessed and blocks accessed are the same.如果一个数据块保存了 单条记录

， 那么 访问 行 和 访问 数据块  是一样的。



However, most tables have multiple rows in each block. Consequently, the desired number of rows may be clustered in a few blocks or spread out over a larger

number of blocks.

那么 大多数表 的情况是： 有 多行 在 每个数据块里面 ， 因此， 查询所需要的行 可能 是 聚集在 少数量的 数据块里面 ， 也 可能 扩展 到 很多数据块里面。



Although the clustering factor is a property of the index, the clustering factor actually relates to the spread of similar indexed column values within data

blocks in the table. A lower clustering factor indicates that the individual rows are concentrated within fewer blocks in the table. Conversely, a high

clustering factor indicates that the individual rows are scattered more randomly across blocks in the table. Therefore, a high clustering factor means that

it costs more to use a range scan to fetch rows by rowid, because more blocks in the table need to be visited to return the data. Example 11-4 shows how the

clustering factor can affect cost.

clustering factor 是 索引的一个属性，clustering factor 与  相似的 被 索引的 值 存储在 表里面的数据块 有关 。 一个 低 的 clustering factor 表示 单独的 行 集中

在 表里面的 很少几个数据块里面。 想反的， 一个高 的  clustering factor 表示 了  单独 的 行 分散在 表里面的 更多 的 随机 数据块 里面 。 这样， 高的 clustering

factor 表示   如果使用 通过 rowid 的范围扫描，  ， 代价 更 高， 因为更多的 数据块 需要 被被访问 来 获得 数据。



Example 11-4 Effects of Clustering Factor on Cost

Assume the following situation:

    There is a table with 9 rows.

    There is a non-unique index on col1 for table.

    The c1 column currently stores the values A, B, and C.

    The table only has three data blocks.

Case 1: The index clustering factor is low for the rows as they are arranged in the following diagram.

Block 1       Block 2        Block 3
-------       -------        -------
A  A  A       B  B  B        C  C  C

This is because the rows that have the same indexed column values for c1 are located within the same physical blocks in the table. The cost of using a range

scan to return all rows that have the value A is low because only one block in the table must be read.

Case 2: If the same rows in the table are rearranged so that the index values are scattered across the table blocks (rather than collocated), then the index

clustering factor is higher.

Block 1       Block 2        Block 3
-------       -------        -------
A  B  C       A  B  C        A  B  C

This is because all three blocks in the table must be read in order to retrieve all rows with the value A in col1.








SQL>  select owner ,  index_name,blevel,leaf_blocks,clustering_factor,num_rows,distinct_keys from dba_indexes where index_name='IND_DEMO01_ID';

OWNER                          INDEX_NAME                         BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR   NUM_ROWS DISTINCT_KEYS
------------------------------ ------------------------------ ---------- ----------- ----------------- ---------- -------------
SCOTT                          IND_DEMO01_ID                           0           1                 1        101            99

SQL>

CLUSTERING_FACTOR   =  1

blocks =  5




QL> select owner ,  BLOCKS  from dba_tables where table_name = 'DEMO1' ;

OWNER                              BLOCKS
------------------------------ ----------
SCOTT                                   5