Each data block within the Oracle index serves as a node in the index tree, with the
bottom nodes (leaf blocks) containing pairs of symbolic keys and ROWID values. To properly
manage the blocks, Oracle controls the allocation of pointers within each data block. As an
Oracle tree grows (by inserting rows into the table), Oracle fills the block, and when full, it
splits, creating new index nodes (data blocks) to manage the symbolic keys within the
index. Hence, an Oracle index block may contain two types of pointers:
1 Pointers to other index nodes (data blocks)
2 ROWID pointers to specific table rows
Oracle manages the allocation of pointers within index blocks, and this is the reason why we
are unable to specify a PCTUSED value (the freelist re-link threshold) for indexes. When we
examine an index block structure, we see that the number of entries within each index node
is a function of two values:
1 The length of the symbolic key
2 The blocksize for the index tablespace
Because the blocksize affects the number of keys within each index node, it follows that the
blocksize will have an effect on the structure of the index tree. All else being equal, large
32K blocksizes will have more keys, resulting in a flatter index than the same index created
in a 2K tablespace. A large blocksize will also reduce the number of consistent gets during
index access, improving performance for scattered reads access.
Each data block within the index contains nodes in the index tree, with the bottom nodes
(leaf blocks) containing pairs of symbolic keys and ROWID values. As an Oracle tree grows
(by inserting rows into the table), Oracle fills the block, and when the block is full, it splits,
creating new index nodes (data blocks) to manage the symbolic keys within the index.
Hence, an Oracle index block may contain pointers to other index nodes or
ROWID/Symbolic-key pairs.
The number of entries within each index data block is a function of two values:
1 The length of the symbolic key
2 The blocksize for the index tablespace
Because the blocksize affects the number of keys within each index block, it follows that the
blocksize will have an effect on the structure of the index tree. All else being equal, large
32K blocksizes will have more keys, resulting in a flatter index than the same index created
in a 2K tablespace.
According to an article by Christopher Foot (www.dbazine.com/foot3.html): A bigger block
size means more space for key storage in the branch nodes of B-tree indexes, which
reduces index height and improves the performance of indexed queries.
In any case, there appears to be evidence that block size affects the tree structure, which
supports the argument that the size of the data blocks affects the structure of the Oracle
index tree.
You can use the large (16K 32K) blocksize data caches to contain data from indexes or
tables that are the object of repeated large scans. Does this really help performance? A
small but revealing test can reveal the answer to that question. For the test, the following
query will be used against a 9i database that has a database block size of 8K, but also has
the 16K cache enabled along with a 16K tablespace:
select
count(*)
from
scott.hospital
where
patient_id between 1 and 40000;
The SCOTT.HOSPITAL table has 150,000 rows in it and has an index build on the
PATIENT_ID column. An EXPLAIN of the query reveals that it uses an index range scan to
produce the desired end result:
-------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 (Cost=41 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'HOSPITAL_PATIENT_ID'
(NON-UNIQUE) (Cost=41 Card=120002 Bytes=480008)
Executing the query (twice, to eliminate parse activity and to cache any data) with the index
residing in a standard 8K tablespace produces these runtime statistics:
------------------------------------------
0 recursive calls
0 db block gets
421 consistent gets
0 physical reads
0 redo size
371 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
To test the effectiveness of the new 16K cache and 16K tablespace, the index used by the
query will be rebuilt into the 16K tablespace that has the exact same characteristics as the
original 8K tablespace, except for the larger blocksize:
alter index
scott.hospital_patient_id
rebuild nologging noreverse tablespace indx_16k;
Once the index is nestled firmly into the 16K tablespace, the query is re-executed (again,
twice) with the following runtime statistics being produced:
Statistics
------------------------------------
0 recursive calls
0 db block gets
211 consistent gets
0 physical reads
0 redo size
371 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
As you can see, the amount of logical reads has been reduced by half simply by using the
new 16K tablespace and accompanying 16K data cache. Clearly, the benefits of properly
using the new data caches and multi-block tablespace feature of Oracle9i and later are
worth your investigation and trials in your own database.