AnySQL.net

    最近几个人都写Blog来关注Single Table Cluster, 我也来重复一下, 要说明的是可以在Hash表上加普通的索引, 以支持按范围的访问, 演示的角本如下:

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;

    接下来测试一下等于查询的情况:

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'

    接下来测试一下范围查询的情况:

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)

    关于Hash表SIZE参数和逻辑读的效率, 可以看玉面飞龙和汪海的文章, 我就不重复测了.