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

TR = 1
TS = 1 million

TR = 1
TS = 5 million

P,(C)

P, C

CNO PNO CNO,...

TR = 1
TS = 1 million

TR = 5 million

CUST POLICY

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

LRT = 5 million 10 ms + 7 million 0.01 ms + 5 million 0.1 ms = 14h

× × ×

Figure 11.10 Massive batch job slow already after reorganization.

10 ms + 5 , 000 , 000 × 0 . 01 ms). To avoid such frequent reorganizations of the
POLICY table, columns should be added to index (CNO, ... ) to prevent table
touches, or the table rows should be stored in that index if the DBMS supports
the option. More about this later.

Table Disorganization (Without Clustering Index Starting
with CNO)

Figure 11.10 shows what would happen if the POLICY table rows are not in
CNO sequence; either PNO is the clustering index as shown or perhaps there is
no clustering index, and the table rows have not been sorted in CNO sequence
at reload time.
The 12 million touches now take 14 h even before the marketing campaign.
Now, making the foreign key index (CNO, ... ) fat for the batch program makes
a huge difference. In general, the order of the table rows does not matter as long
as the access path is index only.

TABLE ROWS STORED IN LEAF PAGES

Real table pages never split, but table rows stored in leaf pages do move when
the leaf pages split. Do we need to worry about this?

SQL Server

In SQL Server 2000, the table rows are stored in the leaf pages of the clustered
index if a table has a clustered index. Let us assume that the primary key index