Using Hash Clusters
Unix + Oracle =
Ixora
Steve Adams steve.adams@ixora.com.au
Cluster Blocks Table Directory
Row Directory
• Table directory • partitions the row directory • row numbers are relative to their table • cluster keys are stored as table 0
Unix + Oracle =
Ixora
0 1 0 1 2 3 4 0 1 2
Steve Adams steve.adams@ixora.com.au
Cluster Key Changes • Performed by recursive/service transactions • commit independent of parent/user transaction • improves concurrency
• Single threaded • to prevent incompatible key changes • always use ITL slot 1 • slot 1 is reserved - user transactions never use it
• wait in shared mode for TX lock if slot is busy Unix + Oracle =
Ixora
Steve Adams steve.adams@ixora.com.au
Index Cluster Inserts • Key can be inserted to block if • number of keys < (data area size - 14) / SIZE • available space > SIZE - 27 • key and data row pieces fit below PCTFREE
• Row inserts • • • •
lookup key in cluster index insert to (last) data block if space permits try up to 3 previous blocks in the key chain link a freelist block into the key chain & insert
Unix + Oracle =
Ixora
Steve Adams steve.adams@ixora.com.au
Index Cluster Deletes • Data row piece headers have a key index • Keys have current & pending row counts • both incremented on insert • pending decremented on delete • current decremented on stub cleanout
• Flushable keys • current count was, or pending count is zero • when inserting a new key, try to clean out all committed stubs and flush keys if possible Unix + Oracle =
Ixora
Steve Adams steve.adams@ixora.com.au
Cluster Key Scans • Purpose • to fetch all rows for a clustered table with a known cluster key value
• Buffer gets • visit all blocks in the key chain (reverse order) • scan row directory entries for the table and check cluster key index for all rows Unix + Oracle =
Ixora
Steve Adams steve.adams@ixora.com.au
Assessing Cluster Key Chaining • Detection • compare cluster key scan block gets to the cluster key scans statistic • maybe DBA_CLUSTERS . AVG_BLOCKS_PER_KEY
• Fix • rename existing tables and create new cluster • use PL/SQL iterate over the cluster keys and copy all rows for each key in turn Unix + Oracle =
Ixora
Steve Adams steve.adams@ixora.com.au
Hash Clusters • Fixed blocks • formatted during cluster creation • hash key value implied by location • keys never flushed; blocks never freed • no data row counts, or previous key rowid needed
• Chained blocks • just like index cluster blocks • key data is hash key value Unix + Oracle =
Ixora
Steve Adams steve.adams@ixora.com.au
Single Table Hash Clusters • Better “single row per hash key” • Reserved slots • one row slot per key • cluster key scans only inspect that slot iff no keys have more than one row • otherwise scan all row slots for table
• “A” flag in data layer flag byte • no more than one row per key Unix + Oracle =
Ixora
Steve Adams steve.adams@ixora.com.au
Hash Functions • Internal hash function • returns 31-bit positive integer • good distribution, but not perfect • mod(hash value, HASHKEYS)
• HASH IS column_name • must be integer column • saves hash computation • no false collisions Unix + Oracle =
Ixora
Steve Adams steve.adams@ixora.com.au
Hash Cluster Data Density • Use SINGLE TABLE syntax if possible • Otherwise • • • •
2K tablespace block size no SIZE parameter HASHKEYS is number of fixed blocks needed primary key columns first in row
• Benefits • minimize CPU cost of cluster key scans • maximize data density - only one key per block Unix + Oracle =
Ixora
Steve Adams steve.adams@ixora.com.au