Hash Clusters

Learnt something newly from oracle product documentation its about Hash clusters storage and data access mechanisms.

rajesh@11GR2> create cluster emp_dept_hash_clust (deptno number)
  2  size 8192 HASHKEYS 100;

Cluster created.

Elapsed: 00:00:00.04
rajesh@11GR2>
rajesh@11GR2> show parameter db_block_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

Elapsed: 00:00:00.14
rajesh@11GR2> exec show_space(user,'EMP_DEPT_HASH_CLUST','CLUSTER');
l_total_blocks****************  112
l_total_bytes*****************  917504
l_unused_blocks***************  2
l_unused_bytes****************  16384
l_last_used_extent_file_id****  4
l_last_used_extent_block_id***  632
l_last_used_block*************  6
l_unformatted_blocks**********  0
l_unformatted_bytes***********  0
l_fs1_blocks******************  0
l_fs1_bytes*******************  0
l_fs2_blocks******************  0
l_fs2_bytes*******************  0
l_fs3_blocks******************  0
l_fs3_bytes*******************  0
l_fs4_blocks******************  0
l_fs4_bytes*******************  0
l_full_blocks*****************  101
l_full_bytes******************  827392

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
rajesh@11GR2>
rajesh@11GR2> select ( 100* 8192)/8192 as blocks from dual;

    BLOCKS
----------
       100

Elapsed: 00:00:00.04
rajesh@11GR2>
rajesh@11GR2> create table emp_clust(
  2     empno,
  3     ename,
  4     sal,
  5     hiredate,
  6     deptno
  7  )
  8  cluster emp_dept_hash_clust (deptno)
  9  as
 10  select level, 'x',dbms_random.random,sysdate,case when mod(level,100) = 0 then 1 else mod(level,100) end as deptno
 11  from dual
 12  connect by level <= 1000000;

Table created.

Elapsed: 00:00:32.03
rajesh@11GR2>
rajesh@11GR2> exec dbms_stats.gather_table_stats(user,'EMP_CLUST');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.53
rajesh@11GR2>

select empno,deptno,ename,hiredate
from emp_clust
where deptno = 3

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.07          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       68      0.00       0.07          1        155          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       70      0.00       0.14          1        157          0       10000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 93

Rows     Row Source Operation
-------  ---------------------------------------------------
  10000  TABLE ACCESS HASH EMP_CLUST (cr=155 pr=1 pw=0 time=7178 us)

Oracle database applies the HASH function on Hash clustered key to locate the block that contains requested data. But be aware that Hash clusters are blind to Range scans.

select empno,deptno,ename,hiredate
from emp_clust
where deptno between 3 and 5

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      201      0.32       0.22         71       4679          0       30000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      203      0.32       0.22         71       4679          0       30000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 93 

Rows     Row Source Operation
-------  ---------------------------------------------------
  30000  TABLE ACCESS FULL EMP_CLUST (cr=4679 pr=71 pw=0 time=66023 us cost=1243 size=730980 card=40610)

Now we can see that oracle doesn't apply HASH function to locate the block, instead it full scan the Table to answer the queries. But in case of Indexed clusters oracle will still use clusters to answer queries involving Range scans.

rajesh@11GR2> create cluster emp_dept_clust (deptno number);

Cluster created.

Elapsed: 00:00:00.28
rajesh@11GR2>
rajesh@11GR2> create index idx_emp_dept_clu1 on cluster emp_dept_clust;

Index created.

Elapsed: 00:00:00.14
rajesh@11GR2> create table emp_clust(
  2     empno,
  3     ename,
  4     sal,
  5     hiredate,
  6     deptno
  7  )
  8  cluster emp_dept_clust (deptno)
  9  as
 10  select level, 'x',dbms_random.random,sysdate,case when mod(level,100) = 0 then 1 else mod(level,100) end as deptno
 11  from dual
 12  connect by level <= 1000000;

Table created.

Elapsed: 00:00:36.10
rajesh@11GR2> exec dbms_stats.gather_table_stats(user,'EMP_CLUST');

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.34

select empno,deptno,ename,hiredate
from emp_clust
where deptno between 3 and 5

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      201      0.01       0.02          0        324          0       30000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      203      0.01       0.02          0        324          0       30000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 93 

Rows     Row Source Operation
-------  ---------------------------------------------------
  30000  TABLE ACCESS CLUSTER EMP_CLUST (cr=324 pr=0 pw=0 time=18460 us cost=36 size=730980 card=40610)
      3   INDEX RANGE SCAN IDX_EMP_DEPT_CLU1 (cr=4 pr=0 pw=0 time=14 us cost=3 size=0 card=40610)(object id 76415)