Indexes and Reorganization - Relational Database Index Design and the Optimizers - page 222

P

CNAME PNO

TR = 1
TS = 1 million

TR = 1
TS = 5 million

P ,C

C

CNO CNO,...

CUST POLICY

1,000,000 rows 5,000,000 rows

LRT = 2 10 ms + 6 million 0.01 ms + 5 million 0.1 ms = 9 min

× × ×

Figure 11.11 Table rows in leaf pages—SQL Server.

(CNO) is the clustered index of the CUST table and the foreign key index (CNO,
... ) is the clustered index of the POLICY table—refer to Figure 11.11. This

eliminates 6,000,000 touches—just like fat indexes for the batch program—and
saves disk space too. INSERTs to CUST and POLICY are about 10 ms faster
because there is no table page to update.
The nonclustered indexes point to the table rows using the clustered index
key . The indirect pointers add a small overhead (SELECT ... WHERE PNO =
:PNO, for instance, must go through two sets of nonleaf pages), but they prevent
a massive pointer update operation when a leaf page split moves table rows. The
leaf page splits in the clustered index have the same effect as with any index:
Index slice read time becomes longer. LPSR may grow steeply when table rows
are long—if the clustered index key is not ever increasing.

Oracle

In Oracle 9i, the implementation of storing table rows in leaf pages (Index-
Organized Table) differs in three ways:
1. Only the primary key index can be chosen for the table rows.
2. There are two pointers in the other indexes: a direct pointer and the
primary key. The direct pointer is not updated if a table row moves as
a result of a split. The direct pointer is used as a first guess; if the table
row is no longer at that address, the nonleaf index pages of the primary
key index are used to access the table row.
3. There is an option to store only N first bytes of the table rows in the leaf
page; the rest is stored in an overflow area. This reduces the need for