| |
 |
|
Oracle 8 Tips
by Burleson Consulting |
The Data Warehouse Development Life Cycle
Oracle Features for the Data Warehouse
Understanding Oracle indexes
Most programmers do not realize that database deadlocks occur
frequently within the database indexes. It is important to note that
a SELECT of a single row from the database may cause more than one
lock entry to be placed in the storage pool as all affected index
rows are also locked. In other words, the individual row receives a
lock, but each index node that contains the value for that row will
also have locks assigned (Figure 8.4). If the "last" entry in a
sorted index is retrieved, the database will lock all index nodes
that reference the indexed value, in case the user changes that
value. Since many indexing schemes always carry the high-order key
in multiple index nodes, an entire branch of the index tree can be
locked--all the way up to the root node of the index.
While each database's indexing scheme is different,
some relational database vendors recommend that tables with
ascending keys be loaded in descending order, so that the rows are
loaded from Z to A on an alphabetic key field. Other databases such
as Oracle recommend that the indexes be dropped and re-created after
the rows have been loaded into an empty table.
Figure 8.4 An overview of Oracle locking.
|
