Table and Index Organization - Relational Database Index Design and the Optimizers - page 12

of the topic, a glossary is provided that summarizes all the terms used throughout
the text.

Index and Table Pages

Index and table rows are grouped together in pages ; these are often 4K in size,
this being a rather convenient size to use for most purposes, but other page sizes
may be used. Fortunately, as far as index design is concerned, this is not an
important consideration other than that the page size will determine the number
of index and table rows in each page and the number of pages involved. To
cater for new rows being added to tables and indexes, a certain proportion of
each page may be left free when they are loaded or reorganized. This will be
considered later.
Buffer pools and I/O activity (discussed later) are based on pages; for
example, an entire page will be read from disk into a buffer pool. This means
that several rows , not just one, are read into the buffer pool with a single I/O.
We will also see that several pages may be read into the pool by just one I/O.

INDEX ROWS

An index row is a useful concept when evaluating access paths. For a unique
index, such as the primary key index CNO on table CUST, it is equivalent to an
index entry in the leaf page (see Fig. 2.1); the column values are copied from the
table to the index, and a pointer to the table row added. Usually, the table page
number forms a part of this pointer, something that should be kept in mind for
a later time. For a nonunique index, such as the index CITY on table CUST, the
index rows for a particular index value should be visualized as individual index
entries, each having the same CITY value, but followed by a different pointer
value. What is actually stored in a nonunique index is, in most cases, one CITY
value followed by several pointers. The reason why it is useful to visualize these
as individual index entries will become clear later.

1
3
7

8

7
20
39

12

Continue
until last level
single page

20

21

33

39

Figure 2.1 Ve r y sma l l
index.

Nonleaf pages

Leaf pages