Single hash clustered table: 2

When specify a column with the HASH IS clause, the result hashed number is mod(column_value, hashkeys). If the column value is null, then it is mapped to hashkeys -1.

 

If a row is hashed to number X, then normally it will be stored in the block which is the number ceil(X/rows per block) block inside the segment. Number of rows per block is determined by the SIZE (when define the cluster) and the block size. But if that block is full, then oracle has to allocate a new block and insert it there. And there is a link points to this block from the block which will store this row if it is not full.

 

SQL> insert into t_hashed  select 1009*rownum+1,1 from dba_objects where rownum<30;

 

29 rows inserted                           ==>They are hashed to the same value.

 

SQL> commit;

 

Commit complete

 

SQL> set serveroutput on

SQL> exec show_space('T_HASHED');

 

Free Blocks.............................2

Total Blocks............................256

Total Bytes.............................2097152

Unused Blocks...........................81==> 4 more blocks allocated,  (see the previous article which shows there are 85 blocks)

Unused Bytes............................663552

Last Used Ext FileId....................6

Last Used Ext BlockId...................28169

Last Used Block.........................47

 

PL/SQL procedure successfully completed

 

 

SQL> select dbms_rowid.rowid_block_number(rowid) blockid, count(*)

  2    from t_hashed

  3   where mod(a, 1009) = 1

  4   group by dbms_rowid.rowid_block_number(rowid)

  5   order by 1;

 

   BLOCKID   COUNT(*)

---------- ----------

     28042          7

     28211          7     ==>

     28212          7     ==> Totally 4 new blocks allocated. They are chained

     28213          7     ==>

     28214          1     ==> 

SQL> select dbms_rowid.rowid_block_number(rowid) blockid, count(*)

  2    from t_hashed

  3   where mod(a, 1009) != 1

  4   group by dbms_rowid.rowid_block_number(rowid)

  5   order by 1;

 

   BLOCKID   COUNT(*)

---------- ----------

                                                           ==> All other blocks are empty.