Using hash clusters

In the previous recipe, we introduced index clusters and tested a particular way to load data in a cluster to optimize the storage of rows with the same cluster key value.

In this recipe, we introduce a different kind of cluster—hash clusters. The biggest difference between index clusters and hash clusters is in the waydata is accessed given a particular cluster key value.

How to do it...

The following steps will demonstrate the use of hash clusters:

1. Connect to the HR schema of the TESTDB database:

   CONNECT hr@TESTDB/hr
   

2. Create the cluster:

   CREATE CLUSTER EMP_DEPT_CLUSTER (deptid NUMBER(4))
   SIZE 8192 HASHKEYS 100;
   

3. Create the first table and populate it with data:

   CREATE TABLE CL_DEPARTMENTS CLUSTER EMP_DEPT_CLUSTER
   (department_id) AS SELECT * FROM DEPARTMENTS;
   

4. Create the second table, populate it with data, and gather statistics:

   CREATE TABLE CL_EMPLOYEES CLUSTER EMP_DEPT_CLUSTER
   (department_id) AS SELECT * FROM EMPLOYEES;
   EXEC DBMS_STATS.GATHER_TABLE_STATS(‹HR›, ‹CL_EMPLOYEES...