What is hash cluster in Oracle?

Answer 1

Storing a table in a hash cluster is an optional way to improve the performance of data retrieval. A hash cluster provides an alternative to a non-clustered table with an index or an index cluster. With an indexed table or index cluster, Oracle Database locates the rows in a table using key values that the database stores in a separate index. To use hashing, you create a hash cluster and load tables into it. The database physically stores the rows of a table in a hash cluster and retrieves them according to the results of a hash function.

A hash cluster is created using a CREATE CLUSTER statement, but you specify a HASHKEYS clause. The following example contains a statement to create a cluster named emp_cluster that stores the trial table, clustered by the empno column (the cluster key); and another statement creating a table in the cluster.

CREATE CLUSTER emp_cluster (empno NUMBER(5,0))
    TABLESPACE users
    STORAGE (INITIAL 250K     NEXT 50K
      MINEXTENTS 1     MAXEXTENTS 3
      PCTINCREASE 0)
    HASH IS empno HASHKEYS 150;

CREATE TABLE emp (
    empno NUMBER(5,0) PRIMARY KEY,
    ...)
    CLUSTER emp_cluster (empno);