AnySQL.net English

    Some of my friends write some articles of hash cluster table, about the lowest cost of the equal access. I want to tell you how to enhance the range access of the hash cluster key column. The demo scripts listed as following :

CREATE CLUSTER HASH_C_DEMO (OBJECT_ID NUMBER)
SIZE 50 SINGLE TABLE HASHKEYS 1000;
CREATE TABLE T_C_DEMO (OBJECT_ID NUMBER, OBJECT_NAME VARCHAR2(30))
CLUSTER HASH_C_DEMO (OBJECT_ID);
CREATE INDEX IDX_T_C_DEMO ON T_C_DEMO (OBJECT_ID);
INSERT INTO T_C_DEMO SELECT OBJECT_ID, OBJECT_NAME FROM USER_OBJECTS;

    Let's test the equal access :

SQL> SELECT OBJECT_NAME FROM T_C_DEMO WHERE OBJECT_ID=621150;

OBJECT_NAME
------------------------------
P_DEMO

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (HASH) OF 'T_C_DEMO'

    Let's test the range access :

SQL> SELECT OBJECT_NAME FROM T_C_DEMO WHERE OBJECT_ID BETWEEN 611851 AND 611853;

OBJECT_NAME
------------------------------
SYS_LOB0000609516C00004$$
T_IOT

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_C_DEMO'
   2    1     INDEX (RANGE SCAN) OF 'IDX_T_C_DEMO' (NON-UNIQUE)

    I will tell you how to choose the SIZE and HASHKEYS option in later articles.