Database Reference
In-Depth Information
The second test is performed with row prefetching set to 100. Hence, ten calls to the database engine are enough
to retrieve all rows. Also in this case, this behavior is confirmed by looking at the number of logical reads: 13 on the
index and 87 (100 - 13) on the table. Basically, for each call, 100 rowids were extracted from the index block, and their
corresponding table rows were often found in the same data block, that was accessed only once:
SQL> set arraysize
100
SQL> SELECT /*+ index(t t_pk) */ * FROM t;
-------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1000 | 100 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 1000 |
100
|
| 2 | INDEX FULL SCAN | T_PK | 1 | 1000 |
13
|
-------------------------------------------------------------------------
Now let's perform the same test with a much higher clustering factor. To achieve this, an
ORDER BY
clause based
on a random value is added to the
INSERT
statement used to load data into the table. The only statistic that changes is
the clustering factor. Notice that it's close to the number of rows. In other words, it's very bad:
SQL> TRUNCATE TABLE t;
SQL> INSERT INTO t
2 SELECT rownum, dbms_random.value, dbms_random.string('p',500)
3 FROM dual
4 CONNECT BY level <= 1000
5 ORDER BY dbms_random.value;
SQL> SELECT blocks, num_rows
2 FROM user_tables
3 WHERE table_name = 'T';
BLOCKS NUM_ROWS
---------- ----------
80 1000
SQL> SELECT blevel, leaf_blocks, clustering_factor
2 FROM user_indexes
3 WHERE index_name = 'T_PK';
BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ----------- -----------------
1 2 990
The following are the numbers of logical reads for the two tests. On one hand, for both tests the number of logical
reads on the index has not changed. This makes sense because, in both cases, the index stores the same keys in exactly
the same order (only the corresponding rowids are different). On the other hand, for both tests the number of logical
