In DB2 V8 and later, volatile tables provide a way to prefer index access over table space scans or non-matching index scans for tables that have statistics that make them appear to be small. They are good for tables that shrink and grow, allowing matching index scans on tables that have grown larger without new RUNSTATS.
They also improve cluster table support. Cluster tables are tables that have groups or clusters of data that logically belong together. Within each group, rows should be accessed in the same sequence to avoid lock contention during concurrent access. The sequence of access is determined by the primary key, and if DB2 changes the access path lock, contention can occur. To best support cluster tables (volatile tables), DB2 uses index-only access when possible. This practice minimizes application contention on cluster tables by preserving the access sequence by primary key. You should ensure that indexes are available for single table access and joins.
You can specify the keyword VOLATILE on the CREATE TABLE or the ALTER TABLE statements. If specified, you are forcing an access path of index accessing and no list prefetch.
|
Copyright © 2013 CA Technologies.
All rights reserved.
|
|