Database Reference
In-Depth Information
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ------------------------------------------------------
1 1 1 TABLE ACCESS HASH T_HASHED (cr=1 pr=0 pw=0 time=25 us)
****************************************************************************************
SELECT * FROM T_HEAP WHERE OBJECT_ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 72105 0.87 0.84 0 0 0 0
Fetch 72105 0.70 0.71 0 216315 0 72105
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 144211 1.58 1.55 0 216315 0 72105
...
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- -------------------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID T_HEAP (cr=3 pr=0 pw=0 time=22...
1 1 1 INDEX UNIQUE SCAN T_HEAP_PK (cr=2 pr=0 pw=0 ...
Hash Clustered Tables Wrap-up
That is the nuts and bolts of a hash cluster. Hash clusters are similar in concept to index clusters, except a cluster index
is not used.
The data is the index
in this case. The cluster key is hashed into a block address and the data is expected to
be there. The important things to understand about hash clusters are as follows:
HASHKEYS/
trunc(blocksize/SIZE)
and allocate and format that space right away. As soon as the first
table is put in that cluster, any full scan will hit every allocated block. This is different from
every other table in this respect.
•
The hash cluster is allocated right from the beginning. Oracle will take your
•
The number of
HASHKEY
s in a hash cluster is a fixed size. You cannot change the size of the
hash table without a rebuild of the cluster. This does not in any way limit the amount of data
you can store in this cluster; it simply limits the number of unique hash keys that can be
generated for this cluster. This may affect performance due to unintended hash collisions if
the value was set too low.
•
Range scanning on the cluster key is not available. Predicates such as
WHERE cluster_key
BETWEEN 50 AND 60
cannot use the hashing algorithm. There are an infinite number of
possible values between
50
and
60
, and the server would have to generate them all to hash
each one and see if there was any data there. This is not possible. The cluster will be full
scanned if you use a range on a cluster key and have not indexed it using a conventional index.
