DBMS-Specific Indexing Restrictions - Relational Database Index Design and the Optimizers - page 231

subpage can easily become a bottleneck as the inserts will be serialized. SQL
Server 2000, for instance, does this, but it is probably not a problem if the size
of the lock acquired is a row.
DB2 for z/OS uses latches to ensure physical integrity of index pages. This
is essentially a bit set in the database buffer pool when a page in the pool is
latched and reset when it is unlatched. A page is latched only for the time taken
to read or modify it, a fraction of a microsecond with current processors. Data
integrity is ensured by locking the table page or the table row that the index
row is pointing to ( data only locking ). If the program is making a modification
to a table row or a table page, these locks are not released until the commit
point.
Several years ago DB2 locked the modified index page or subpage until the
commit point. To avoid the resulting long lock waits, it was common practice
to avoid ever-increasing keys, by changing a time stamp value from hh.mm.ss
to ss.mm.hh, for instance. With latches (or index key locking), inserts at the end
of an index are not likely to cause queuing unless the insert transaction rate is
several thousand per second.
Avoiding ever-increasing index keys is another myth that is fading away very
slowly. With current implementations, an index with an ever-increasing key is
actually good for performance: Often there will be no synchronous reads because
the last leaf page is likely to stay in the database buffer pool. Furthermore, if the
index rows do not move or grow, there is no need for any distributed free space
or a reorganization.
INDEX ROW SUPPRESSION
Oracle indexes do not always have an index row for each table row. Rows with
nulls in all key columns are not indexed. As this is a sensitive issue, we will
again quote from Gulutzan and Pelzer (1, p. 251):
The first and most notorious exception to the rule is that Oracle—and Oracle
alone—refuses to store NULLS. Oh, the DBMS will put Nulls in compound keys
if other columns are NOT NULL, but Oracle does not have much use for NULL
keys. So if you have a table (Table1), containing two columns
(column1.column2), and column 2 is indexed, then
ž Oracle can do:
INSERT INTO Table1 VALUES (5, NULL)
much more quickly, because the DBMS does not have to update the
index at all.
ž Oracle can do:
SELECT * FROM Table1 WHERE column2 < 5
a bit more quickly, because the index on column2 has fewer keys and
therefore might have fewer levels.
ž Oracle cannot do:
SELECT * FROM Table1 WHERE column2 IS NULL