Virtually hashed tables are available on IBM Linux pSeries
and Linux AMD64.
You can perform hash-based index scans using nonclustered indexes or clustered indexes on data-only-locked tables. During the scan, each worker process navigates the higher levels of the index and reads the leaf-level pages of the index. Each worker process then hashes on either the data page ID or the value in a separate hash table to determine which data pages or data rows to process.
A virtually hashed table can be an efficient way to organize a table because it does not require a separate hash table. Instead, it stores the rows so that, using the hash key, the query processor can determine the row ID (based on the row’s ordinal number) and the location of the data. Because it does not use a separate hash table to hold the information, it is called a “virtually” hashed table.
For systems that require efficient CPU usage, the virtually hashed table is a good option.
Clustered and nonclustered indexes are expensive for tables that are used for lookups, or for tables in which the row position does not change. With recent advancements in L2 and L3 CPU architectures, you must utilize the cache to take advantage of the real CPU computing power. If you do not utilize the cache, the CPU spends needless cycles waiting for available memory. For clustered or nonclustered indexes, the server misses rows every time it accesses the index-level search, which consumes many CPU cycles. Virtually hashed tables access row-location patterns by computing the hash-key value instead of performing a search.
