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: “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:

Execution Plan
----------------------------------------------------------
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:

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.

--

Donald K. Burleson is one of the world’s top Oracle Database experts with more than 20 years of full-time DBA experience. He specializes in creating database architectures for very large online databases and he has worked with some of the world’s most powerful and complex systems. A former Adjunct Professor, Don Burleson has written 15 books, published more than 100 articles in national magazines, serves as Editor-in-Chief of Oracle Internals and edits for Rampant TechPress. Don is a popular lecturer and teacher and is a frequent speaker at Oracle Openworld and other international database conferences. Don's Web sites include DBA-Oracle, Remote-DBA, Oracle-training, remote support and remote DBA.