索引扫描(一)-聚簇因子

最新推荐文章于 2022-08-11 18:31:56 发布

转载最新推荐文章于 2022-08-11 18:31:56 发布 · 100 阅读

假设一个表中有9行记录，分布在3个block上，按照其中列col1的分布情况，分为以下两种情况：

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

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

第一种情况，相同的值分布在同一个block里，第二种情况，相同的三个值分布在3个block里，在列col1上创建一个索引。
显然，当进行如下查询时：

select * from t where col1='A';

oracle首先进行索引范围扫描，获取相应的列col1='A’的rowid，之后根据rowid从表中获取数据。对于第一种情况，只需要扫描block 1，即可获得想要的结果，而对于第二种情况，需要扫描block 1，block 2，block 3共3个block。显然，第二种情况下的需要进行的IO次数大于第一种情况。

以下例子，首先根据第一种情况构造一个测试表test1：

SQL> create table test1 (a char(2000),b char(100));

Table created.

SQL> insert into test1 values('A','X');

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> COMMIT;

Commit complete.

SQL> select dbms_rowid.rowid_block_number(rowid) from test1;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                3442
                                3442
                                3442

可见，三行均位于同一个block里。

SQL> insert into test1 values('B','X');

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_rowid.rowid_block_number(rowid) from test1;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                3442
                                3442
                                3442
                                3443
                                3443
                                3443

6 rows selected.

SQL> insert into test1 values('C','X');

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_rowid.rowid_block_number(rowid) from test1;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                3442
                                3442
                                3442
                                3443
                                3443
                                3443
                                3444
                                3444
                                3444

9 rows selected.

以上构造出第一种情况，即值为'A'，'B','C'的列分别各自位于同一个block里。再构造第二种情况：

SQL> create table test2(a char(2000),b char(100));

Table created.

SQL> insert into test2 values('A','X');

1 row created.

SQL> insert into test2 values('B','X');

1 row created.

SQL> insert into test2 values('C','X');

1 row created.

SQL> commit;

Commit complete.
SQL> select dbms_rowid.rowid_block_number(rowid) from test2;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                3450
                                3450
                                3450
这样，'A','B','C'这三个不同值的列均位于同一个block里了。然后再构造2个类似的block：

SQL> insert into test2 select * from test2 where rownum<=3;

3 rows created.

SQL> /

3 rows created.

SQL> select dbms_rowid.rowid_block_number(rowid) from test2;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                3450
                                3450
                                3450
                                3451
                                3451
                                3451
                                3452
                                3452
                                3452

接下来执行多次的insert into test1 select * from test1和 insert into test2 select * from test2，对这两个表各自构造出大量数据。

SQL> select count(*) from test1;

COUNT(*)
----------
4608

SQL> select count(*) from test2;

COUNT(*)
----------
4608

分别在test1和test2上创建索引：

SQL> create index idx_test1 on test1(a);

Index created.

SQL> create index idx_test2 on test2(a);

Index created.

执行索引范围扫描：

SQL> set autot traceonly statistics
SQL> select /*+ index(test1,idx_test1) */ * from test1 where a like 'A%';

1536 rows selected.

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       1237 consistent gets
          0 physical reads
          0 redo size
      23223 bytes sent via SQL*Net to client
       1517 bytes received via SQL*Net from client
        104 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       1536 rows processed

SQL> select /*+ index(test2,idx_test2) */ * from test2 where a like 'A%';

1536 rows selected.

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       2158 consistent gets
          0 physical reads
          0 redo size
      23223 bytes sent via SQL*Net to client
       1517 bytes received via SQL*Net from client
        104 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       1536 rows processed

显然，对test1的逻辑读要小于对test2的逻辑读，再看两个索引的聚簇因子以及两张表占用的block数量：

SQL> select index_name,CLUSTERING_FACTOR from user_Indexes where index_name in('IDX_TEST1','IDX_TEST2');

INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ -----------------
IDX_TEST1                                   1536
IDX_TEST2                                   4608

SQL> select count(distinct(dbms_rowid.rowid_block_number(rowid))) from test1;

COUNT(DISTINCT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)))
-----------------------------------------------------
                                                 1536

SQL> select count(distinct(dbms_rowid.rowid_block_number(rowid))) from test2;

COUNT(DISTINCT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)))
-----------------------------------------------------
                                                 1536

SQL> select count(*) from test1;

COUNT(*)
----------
      4608

SQL> select count(*) from test2;

COUNT(*)
----------
      4608