Sparse Join Index Defined on One Partition
A partitioned primary index can be defined on a join index as long as the index is not row compressed. You can also define a sparse join index on only one partition of a partitioned base table by expressing sparseness-defining criteria that match the borders of the partition.
Building sparse join indexes on partitions of partitioned tables that support single‑AMP access is frequently useful for situations in which tactical queries always have both the sparse-defining column (in this example, a date range that matches one partition) and the primary index value (in this example, the store identifier) of the sparse join index. Of course, the tactical queries also need to be accessing a similar subset of columns from the base table: the ones carried in the sparse join index.
A different sparse join index could be built independently on several different partitions of the same partitioned table. As long as each query specifies a constraint that matches the sparse-defining columns for one of those sparse join indexes, the Optimizer can choose the appropriate one to use for the query.
The appropriate primary index for a sparse join index depends on what values the tactical queries specify when they are submitted.