玉面飞龙的BLOG

Secondary index on IOT

October 8th, 2008 | Categories: Boring | Tags: IOT, Oracle

Index Organized Table上的secondary index的cluster factoring不像普通B-TREE索引那么有用。

创建一个iot表和非主键索引。
CREATE TABLE binzhang_test ( "USER_ID" NUMBER(18, 0) NOT NULL ENABLE, "ITEM_ID" NUMBER(38, 0) NOT NULL ENABLE, "CREATION_DATE" DATE, "pktest" PRIMARY KEY ( "USER_ID" , "ITEM_ID" ) ENABLE ) ORGANIZATION INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 104857600 FREELISTS 11 FREELIST GROUPS 1) TABLESPACE cr_data LOGGING NOCOMPRESS PCTTHRESHOLD 20 OVERFLOW ;


create index binzhang_test_ix1 on binzhang_test(CREATION_DATE);
analyze table binzhang_test compute statistics;

从统计信息来看，这个IOT的primary key index有840个leaf block,且secondary index的CLUSTERING_FACTOR为1123，是个很好的数字了。
OWNER U PAR TYPE Index Name Column Name --------------- - --- ------ ------------------------------- ------------------------- --- CASH_USER N NO BINZHANG_TEST_IX1 CREATION_DATE 1 CASH_USER Y NO IOT - pktest USER_ID 1 CASH_USER NO ITEM_ID 2


OWNER           TABLE_NAME           PAR        NUM_ROWS          BLOCKS          SAMPLE LAST_ANALYZED

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

CASH_USER       BINZHANG_TEST        NO           104160                               1 2008/10/08 01:45:41

OWNER INDEX_NAME LEAF_BLOCKS BLEVEL DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR SAMPLE --------------- ----------------------------- --------------- --------------- --------------- ----------------- --------------- CASH_USER pktest 840 2 104160 104160 0 1 CASH_USER BINZHANG_TEST_IX1 468 1 101088 101088 1123 1
测试基于secondary index的查询，看consistent gets需要多少。
select /*+ index(binzhang_test BINZHANG_TEST_IX1 ) */* from binzhang_test where creation_date between sysdate and sysdate+1 and rownum<=100; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=RULE (Cost=15051 Card=1 Bytes=62) 1 0 COUNT (STOPKEY) 2 1 FILTER 3 2 INDEX (UNIQUE SCAN) OF 'pktest' (UNIQUE) (Cost=25 Card=1 Bytes=62) 4 3 INDEX (RANGE SCAN) OF 'BINZHANG_TEST_IX1' (NON-UNIQUE) (Cost=25 Card=5208)

Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 103 consistent gets 1 physical reads 0 redo size 6274 bytes sent via SQL*Net to client 656 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 100 rows processed
返回100行，需要103次一致读。效率不是很好。这说明基于secondary index的IOT表查询，最低IO cost 为

rows_returned_from_secondary_index + secondary index blocks scanned

每次从secondary index的leaf block中读到一个index entry，都要去访问IOT table block一次。

如果表结构为heap普通表，且在CREATION_DATE的b-tree索引的cluster_facting比较好的情况下，针对这个例子，同样的查询会消耗更少的logical read.