Adams - Hash Clusters Oracle

Using Hash Clusters

Unix + Oracle =

Ixora

Steve Adams [email protected]

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 [email protected]

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 [email protected]

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 [email protected]

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 [email protected]

Demonstration • Flushable keys • Key insert restrictions

Unix + Oracle =

Ixora

Steve Adams [email protected]

Demonstration • Index clusters row inserts

Unix + Oracle =

Ixora

Steve Adams [email protected]

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 [email protected]

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 [email protected]

Hash Theory • TBD

Unix + Oracle =

Ixora

Steve Adams [email protected]

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 [email protected]

Demonstration • Hash cluster

Unix + Oracle =

Ixora

Steve Adams [email protected]

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 [email protected]

Demonstration • Single table hash cluster

Unix + Oracle =

Ixora

Steve Adams [email protected]

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 [email protected]

Demonstration • Single table hash cluster without HASH IS

Unix + Oracle =

Ixora

Steve Adams [email protected]

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 [email protected]