Database Reference
In-Depth Information
Single-table hash clusters are dedicated to the implementation of lookup tables that are frequently (ideally,
always) accessed through a specific key. Basically, this is the same utilization you can get from index-organized tables.
However, there are some major differences between the two. Table 13-4 lists the main advantages and disadvantages
of single-table hash clusters compared to index-organized tables. The crucial disadvantage is that single-table hash
clusters need to be accurately sized to take advantage of them.
Table 13-4. Single-table Hash Clusters Compared to Index-organized Tables
Advantages
Disadvantages
Better performance (if accessed through
cluster key and sizing is done correctly)
Careful sizing needed to avoid hash collisions
and waste of space
Cluster key might be different from primary key
Partitioning not supported
LOB columns not supported
When a single-table hash cluster is accessed through the cluster key, the TABLE ACCESS HASH operation appears
in the execution plan. What it does is access the block(s) containing the required data directly through the cluster key.
The following excerpt of the output generated by the hash_cluster.sql script illustrates this:
SELECT * FROM t WHERE id = 6
--------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 1 |
|* 1 | TABLE ACCESS HASH| T | 1 | 1 | 1 |
--------------------------------------------------------------
1 - access("ID"=6)
In addition to the equality condition, the only other condition that enables access to data through the cluster key
is the IN condition. When it's specified, the operation that appears in the execution plan depends on the database
version. In fact, while up to and including version 11.1 the CONCATENATION operation is used, from version 11.2 onward
INLIST ITERATOR is used instead. Each child of both operations is executed once to get a particular cluster key. The
following execution plan was generated on version 11.1.0.7:
SELECT * FROM t WHERE id IN (6, 8, 19, 28)
---------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 4 |
| 1 | CONCATENATION | | 1 | 4 | 4 |
|* 2 | TABLE ACCESS HASH| T | 1 | 1 | 1 |
|* 3 | TABLE ACCESS HASH| T | 1 | 1 | 1 |
|* 4 | TABLE ACCESS HASH| T | 1 | 1 | 1 |
|* 5 | TABLE ACCESS HASH| T | 1 | 1 | 1 |
---------------------------------------------------------------
 
Search WWH ::




Custom Search