Hash cluster table can get much performance improve for equal access, however proper SIZE and HASHKEYS option is required. In OLTP, we always have some tables, the query use equal condition and with unique or very effective index on it, but the execution frequency is really high, half of the total consistent gets is due to these simple queries.
I will first analyze the table and get the current blocks with rows :
SQL> SELECT BLOCKS FROM USER_TABLES WHERE TABLE_NAME='T_OBJECTS';
BLOCKS
----------
118
For this table, data distribution is even enough, so I set the SIZE option to data block size, and set the HASHKEYS a value larger than the data blocks, in this case it's 144.
SQL> CREATE CLUSTER C_T_OBJECTS (OBJECT_ID NUMBER(38,0))
2 SIZE 8192 SINGLE TABLE HASHKEYS 144;
Cluster created.
Now create a hash cluster table CT_OBJECTS with the same structure as T_OBJECTS, and insert the rows from T_OBJECTS. I run the following PL/SQL to access all the rows in hash cluster table to detect the performance improve.
SQL> DECLARE
2 TEMP NUMBER:=0;
3 BEGIN
4 FOR REC IN (SELECT OBJECT_ID FROM T_OBJECTS) LOOP
5 SELECT DATA_OBJECT_ID INTO TEMP FROM CT_OBJECTS
6 WHERE OBJECT_ID=REC.OBJECT_ID;
7 END LOOP;
8 END;
9 /
Find out the hash value of the simple query, and get the statistics from V$SQL view.
SQL> SELECT HASH_VALUE, BUFFER_GETS, EXECUTIONS
2 FROM V$SQL WHERE HASH_VALUE=3267226907;
HASH_VALUE BUFFER_GETS EXECUTIONS
---------- ----------- ----------
3267226907 9464 9462
You can see that the consistent gets for each row is almost one, the lowest cost.