Databases Reference
In-Depth Information
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 way data 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›,
estimate_percent => 100,
method_opt => ‹for all columns size 1›);
5.
Verify the execution plan for the regular table:
SET AUTOT TRACE EXP
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 20;
