HASH is verify effective for locate one row (for equal access). Oracle have very famious hash join method, and hash cluster table to get higher performance. The B*Tree index on some busy system may not be effective enough, we have hit availability problems caused by index root block split or branch block split, and the cost of get one row is still too high for B*Tree index by navigating from root-branch-leaf-data blocks.
Oracle have support the hash cluster table, so hash cluster index is not a problem at all, because index is also a table contains the key columns and the ROWID value of the table rows. We could eliminate the root block split and branch block split, and get the cost of single row with 2 logical gets. Why not hash cluster table directory, because table is hard to move to reorganize the storage, while index can be rebuild online to reorganize the storage for better performance. So hash cluster index is worthy of it in some cases.
Now we can write our own context index to simulate it, but I never write my own context index, any one have a sample?
In Oracle, the hash cluster column type are not limited to number only, so it not so hard to support it? It's likely a specially shadow table of the master table, we can use trigger to simulate it currently.
SQL> create cluster c_hashidx (col1 varchar2(30)) hashkeys 128;
Cluster created.
Is there other DBMS system which support this kind of index? In IBM DB2, when creating an index you can specify some including columns for access filter, but not affect the row order of the index, if oracle can add this feature too, it will be another great thing.