Database Reference
In-Depth Information
TOTAL LENGTH OF THE INDEX COLUMNS
The total length of the columns that may be copied to an index has an upper
limit that depends on the DBMS. As fat indexes are becoming more popular,
this limit seems to be increasing in new versions. For instance, in DB2 for z/OS
V7 the upper limit was 255 bytes, but in V8, which became available in 2004,
the limit was increased to 2000 bytes. The next practical limit is the size of the
index pages. As discussed in Chapter 11, leaf page splits may start to happen
soon after reorganization if the free space per leaf page is not large enough for at
least two new index rows. If inserts do not go the end of the index, index rows
that are longer than 20% of leaf page size (800 bytes with 4K pages and 1600
bytes with 8K pages) may imply frequent reorganization.
VARIABLE-LENGTH COLUMNS
If the DBMS pads variable-length columns to the maximum length, index rows in
a fat index can easily become much too long. In DB2 for z/OS V7, for instance,
fat indexes with VARCHAR columns are not at all common for this reason, but
V8 has an option NOT PADDED in CREATE INDEX.
Many products use VARCHAR columns for nonnumeric columns; SAP is
a well-known example. The index tuning potential with such products is funda-
mentally increased if variable-length columns stay variable length when copied
to an index.
NUMBER OF INDEXES PER TABLE
Many products either have no upper limit or the limit is so high that it does
not matter. SQL Server 2000, for instance, allows up to 249 unique nonclustered
indexes or constraints per table and 1 clustered index. The time taken by the
access path selection process increases according to the number of indexes, but
this is significant only if the cost estimates are done at each execution.
MAXIMUM INDEX SIZE
Typical upper limits are several gigabytes, and these are being increased on a
continuing basis. Large indexes are normally partitioned, as are large tables, in
order to minimize the cost of running maintenance utilities and to spread the
index over several disk drives or RAID arrays.
INDEX LOCKING
If the DBMS locks an index page or a part of an index page, for example, a
subpage, from the time of the update to the commit point, the index page or
Search WWH ::
Custom Search