Database Reference
In-Depth Information
EODA@ORA12CR1> exec show_space( 'HASH_CLUSTER', user, 'CLUSTER' )
Free Blocks............................. 0
Total Blocks............................ 1,024
Total Bytes............................. 8,388,608
Total MBytes............................ 8
Unused Blocks........................... 14
Unused Bytes............................ 114,688
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 1,024
Last Used Block......................... 114
PL/SQL procedure successfully completed.
We can see that the total number of blocks allocated to the table is 1,024. Fourteen of these blocks are unused
(free). One block goes to table overhead to manage the extents. Therefore, 1,009 blocks are under the HWM of this
object, and these are used by the cluster. The prime 1,009 just happens to be the next largest prime over 1,000, and
since the block size is 8KB, we can see that Oracle did in fact allocate and format 1009 blocks for us. The figure is a
little higher than this due to the way extents are rounded and/or by using locally managed tablespaces with uniformly
sized extents.
This example points out one of the issues with hash clusters you need to be aware of. Normally, if we create an
empty table, the number of blocks under the HWM for that table is 0. If we full scan it, it reaches the HWM and stops.
With a hash cluster, the tables will start out big and will take longer to create, as Oracle must initialize each block, an
action that normally takes place as data is added to the table. They have the potential to have data in their first block
and their last block, with nothing in between. Full scanning a virtually empty hash cluster will take as long as full
scanning a full hash cluster. This is not necessarily a bad thing; we built the hash cluster to have very fast access to the
data by a hash key lookup. We did not build it to full scan it frequently.
Now we can start placing tables into the hash cluster in the same fashion we did with index clusters:
EODA@ORA12CR1> create table hashed_table
2 ( x number, data1 varchar2(4000), data2 varchar2(4000) )
3 cluster hash_cluster(x);
Table created.
To see the difference a hash cluster can make, I set up a small test. I created a hash cluster, loaded some data in it,
copied this data to a regular table with a conventional index on it, and then did random reads on each table (the same
“random” reads on each). Using runstats,
SQL_TRACE
, and
TKPROF
, I was able to determine the characteristics of each.
The following is the setup I performed, followed by the analysis:
EODA@ORA12CR1> create cluster hash_cluster
2 ( hash_key number )
3 hashkeys 75000
4 size 150
5 /
Cluster created.
EODA@ORA12CR1> create table t_hashed
2 cluster hash_cluster(object_id)
3 as
4 select *
5 from all_objects
6 /
Table created.
