Sparse Join Indexes and Tactical Queries
Join indexes are particularly useful for tactical query applications. Chapter 10: “Join and Hash Indexes,” discusses join indexes more fully, but one interesting join index approach that is worth highlighting here for its relevance to tactical queries is the sparse join index.
Sparse join indexes include only a subset of a base table rows in their definition, using a WHERE clause to determine which base tables rows are retained and which are not (see “Sparse Join Indexes” on page 507). Sparse join indexes are quicker to build, faster to scan, and take up less disk space, depending on the degree of sparseness. Like all join indexes, sparse join indexes support single-AMP access when based on their primary index definition.