Hash indexes are designed to improve query performance like join indexes, especially single table join indexes, and in addition, they enable you to avoid accessing the base table. The syntax for the hash index is as follows:
/*Hash index syntax*/ CREATE HASH INDEX <hash-index-name> [, <fallback-option>] (<column-name-list1>) ON <base-table> [BY (<partition-column-name-list2>)] [ORDER BY <index-sort-spec>] ;
The partition column name list acts as the primary index for the hash index. This is comparable to having a secondary index defined as the partition column name list, but having additional data columns defined together with the index columns. The hash index is similar to a NUSI, in that it allows referring back to the primary data row. The hash index has the additional capability of doing index covering where a query can be satisfied by the columns of the hash index, plus the capability of doing partial index...