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

Index Rows
The maximum number of columns in an index varies across the current DBMSs:
SQL Server 16, Oracle 32, DB2 for z/OS 64, and DB2 for LUW 16 (refer to
Chapter 12 for more detail).
Indexing variable-length columns have limitations in some products. If only
fixed-length index rows are supported, the DBMS may pad an index column to
the maximum length. As variable-length columns are becoming more common
(because of JAVA, for instance)—even in environments in which they were rarely
used in the past—support for variable-length index columns (and index rows) is
now the norm in the latest releases. DB2 for z/OS, for instance, has full support
for variable-length index columns in V8.
Normally, all columns copied to an index form the index key, which deter-
mines the order of the index entries. In unique indexes, an index entry is the same
as an index row. With nonunique indexes, there is an entry for each distinct value
of the index key together with a pointer for each of the duplicate table rows; this
pointer chain is normally ordered by the address of the table row. DB2 for LUW,
for instance, allows nonkey columns at the end of an index row. In addition to the
above, each index entry requires a certain amount of control information, used,
for example, to chain the entries in key sequence; throughout this topic, this
control information will be assumed, for the purpose of determining the number
of index rows per page, to be about 10 bytes in length.
Table Rows
We have already seen that some DBMSs, for instance, DB2 for z/OS, DB2
for LUW, Informix, and Ingres, support a clustering index, which affects the
placement of inserted table rows. The objective is to keep the order of the table
rows as close as possible to the order of the rows in the clustering index. If there
is no clustering index, the inserted table rows are placed in the last page of the
table or to any table page that has enough free space.
Some DBMSs, for example, Oracle and SQL Server, do not support a clus-
tering index that influences the choice of table page for an inserted table row.
However, with any DBMS, the table rows can be maintained in the required order
by reorganizing the table frequently; by reading the rows via a particular index
(the index that determines the required order) before the reload or by sorting the
unloaded rows before the reload.
Oracle and SQL Server provide an option for storing the table rows in the
index as shown in the next section. More information is provided in Chapter 12.
Index-Only Tables
If the rows in a table are not too long, it may be desirable to copy all the columns
into an index to make SELECTs faster. The table is then somewhat redundant.