Data access methods: overview

The following table provides a summary of data management methods that are discussed in this book.

Table 1. Summary of data access methods

Access Method	Selection Process	Good When	Not Good When	Selected When	Advantages
Table scan access method	Reads all rows. Selection criteria applied to data in dataspace.	> 20% rows selected.	< 20% rows selected.	No ordering, grouping, or joining and > 20% rows selected.	Minimizes page I/O through pre-fetching.
Parallel table prefetch access method	Data retrieved from auxiliary storage in parallel streams. Reads all rows. Selection criteria applied to data in dataspace.	> 20% rows selected. Adequate active memory available. Query would otherwise be I/O bound. Data spread across multiple disk units.	< 20% rows selected. Query is CPU bound.	No ordering, grouping, or joining and > 20% rows selected.	Minimizes wait time for page I/O through parallel table prefetching.
Parallel table scan method (available only when the DB2 UDB Symmetric Multiprocessing feature is installed)	Data read and selected in parallel tasks.	> 10% rows selected, large table. Adequate active memory available. Data spread across multiple disk units. DB2 UDB Symmetric Multiprocessing installed. Multi-processor system.	< 10% rows selected. Query is CPU bound on a uniprocessor system.	DB2 UDB Symmetric Multiprocessing installed. I/O bound or running on a multi-processor system.	Significant performance especially on multiprocessors.
Index scan-key selection access method	Selection criteria applied to index.	Ordering, grouping, and joining.	Large number of rows selected.	Index is required and cannot use index scan-key positioning method.	Dataspace accessed only for rows matching index scan-key selection criteria.
Parallel index scan-key selection access method (available only when the DB2 UDB Symmetric Multiprocessing feature is installed)	Selection criteria applied to index in parallel tasks.	Size of index is much less than the dataspace. DB2 UDB Symmetric Multiprocessing must be installed.	Large number of rows selected.	When ordering of results not required.	Better I/O overlap because parallel tasks perform the I/O. Can fully utilize multiprocessor systems.
Index scan-key positioning access method	Selection criteria applied to range of index entries. Commonly used option.	< 20% rows selected.	> 20% rows selected.	Selection columns match left-most keys and < 20% rows selected.	Index and dataspace accessed only for rows matching selection criteria.
Parallel index scan-key positioning access method (available only when the DB2 UDB Symmetric Multiprocessing feature is installed)	Selection criteria applied to range of index entries in parallel tasks.	< 20% rows selected. DB2 UDB Symmetric Multiprocessing must be installed.	Large number of rows selected.	When ordering of results not required. Selection columns match left-most keys and < 20% rows selected.	Index and dataspace accessed only for rows matching selection criteria. Better I/O overlap because parallel tasks perform the I/O. Can fully utilize a multiprocessor systems.
Index-from-index access method	Key row positioning on permanent index. Builds temporary index over selected index entries.	Ordering, grouping and joining.	> 20% rows selected.	No existing index to satisfy ordering but existing index does satisfy selection and selecting < 20% rows.	Index and dataspace accessed only for rows matching selection criteria.
Sort access method	Order data read using table scan processing or index scan-key positioning.	> 20% rows selected or large result set of rows.	< 20% rows selected or small result set of rows.	Ordering specified; either no index exists to satisfy the ordering or a large result set is expected.	See table scan and index scan-key positioning in this table.
Index Only Access Method	Done in combination with any of the other index access methods	All columns used in the query exist as key columns. DB2 UDB Symmetric Multiprocessing must be installed.	< 20% rows selected or small result set of rows.	All columns used in the query exist as key columns and DB2 UDB Symmetric Multiprocessing is installed.	Reduced I/O to the dataspace.
Parallel table or index based preload access method	Index or table data loaded in parallel to avoid random access.	Excessive random activity would otherwise occur against the object and active memory is available to hold the entire object.	Active memory is already over-committed.	Excessive random activity would result from processing the query and active memory is available which can hold the entire object.	Random page I/O is avoided which can improve I/O bound queries.
Hashing access method(Parallel or non-parallel)	Rows with common values are grouped together.	Longer running grouping and join queries.	Short running queries.	Join or grouping specified.	Reduce random I/O when compared to index methods. If DB2 UDB Symmetric Multiprocessing is installed, possible exploitation of SMP parallelism.
Bitmap processing method	Key position/index scan-key selection used to build bitmap. Bitmap used to avoid touching rows in table.	Selection can be applied to index and either >5% or <25% rows selected or an OR operator is involved in selection that precludes the use of only one index.	>25% rows selected.	Indexes match selection criteria.	Reduces page I/O to the data space. Allows multiple indexes per table.