Similarities of Hash Indexes to Secondary Indexes
Hash indexes are file structures that can be used either to resolve queries by accessing the index instead of its underlying base table or to enhance access performance when they do not cover a query by providing a secondary access path to requested base table rows. They can either substitute for or point to base table rows.
Because of these properties, hash indexes are useful for queries where the index structure contains all the columns referenced by a query, thereby covering the query, and making it possible to retrieve the requested data from the index rather than accessing its underlying base tables. For obvious reasons, an index with this property is often referred to as a covering index.
Hash indexes put in double duty by also providing pointers to base table rows to facilitate their access in situations where the index does not cover the query. This application of hash indexes is similar to how the Optimizer uses secondary indexes when it creates its access plans.
Because the distribution of rows to AMPs of hash index rows is under user control through the specification of an explicit partition key in the CREATE HASH INDEX statement, rows can be distributed in various ways, depending on the requirements of an application. Distribution of secondary index rows, on the other hand, is not under user control. Because of this, they are less adaptable to the specific requirements of an individual application than hash indexes.
Both secondary and hash indexes provide access paths to base table rows and can be selected by the Optimizer to cover SQL queries. Both also share a similar DDL syntax that is very different from the syntax used to create single-table join indexes.
The key difference between hash and secondary indexes is that the hash index partition key is user-selectable, which often makes it more useful for processing queries.