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

CNAME

TR = 1
TS = 1 million

TR = 1
TS = 5 million

P,C

P

Fat PNO CNO,...

CNO

CUST

1,000,000 rows

HOT

POLICY

5,000,000 rows

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

Figure 11.12 Table rows in leaf pages—Oracle.

free space per leaf page but access to the columns in the overflow area
becomes slow.
Figure 11.12 shows how the CUST table could be implemented as an Index-
Organized Table. This saves one million touches in our batch program, and the
index is fat for any query with WHERE CNO = :CNO. The foreign key index on
table POLICY should be made fat for the batch program to prevent table touches
and very frequent organizations of the POLICY table. Because index (CNO, ... )
is not the primary key index, the table rows cannot be stored in it.
Let us assume 8K pages (blocks) and random inserts to the CUST table.
Without using an overflow area, 6 CUST rows could be stored per leaf page,
with free space for 2 new rows (X = 2). To keep LPSR below 1%, the CNO
index should then be reorganized when 20% of the free space has been used,
F = 20% in Figure 11.6. That point is reached when, on average, 0.4 new CUST
rows (F × X = 0 . 2 × 2) have been added per page, which corresponds to a 7%
growth (0.4/6) of the CUST table. More frequent reorganizations may be justified
to keep the success ratio of the direct pointer (the first guess) close to 100%.

COST OF INDEX REORGANIZATION

An index can be reorganized in many ways:
1. Full index scan (random touches and no sort or sequential touches and
sort)
2. Full table scan (like CREATE INDEX; sequential touches and a sort)