Database Reference
In-Depth Information
4 pad VARCHAR2(4000),
5 CONSTRAINT t_pk PRIMARY KEY (id)
6 );
SQL> INSERT INTO t
2 SELECT rownum, dbms_random.value, dbms_random.string('p',500)
3 FROM dual
4 CONNECT BY level <= 1000;
Because the values of the
id
column are inserted in increasing order, the clustering factor of the index that supports
the primary key is close to the number of blocks in the table. In other words, it's very good:
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 77
It's now useful to look at the number of logical reads performed when the whole table is accessed through the
primary key (a hint is used to force such an execution plan). The first test is performed with row prefetching set to 2.
Hence, at least 500 calls to the database engine must be performed to retrieve the 1,000 rows. This behavior can be
confirmed by looking at the number of logical reads (
Buffers
column): 503 on the index and 539 (1,042 - 503) on the
table. Basically, for each call, two rowids were extracted from the index block, and their data was found in the same
data block almost every time, thanks to the good clustering factor:
SQL> set arraysize
2
SQL> SELECT /*+ index(t t_pk) */ * FROM t;
-------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1000 | 1042 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 1000 |
1042
|
| 2 | INDEX FULL SCAN | T_PK | 1 | 1000 |
503
|
-------------------------------------------------------------------------
