玉面飞龙的BLOG

看来我是太old了,今天才知道10g有这个新功能。全局索引也可以hash partition了。这就为VLDB的优化提供了很大余地。原先b-tree level是3,4或者更高的索引可以通过hash partition，成为height为2的索引。像where ind_column=:1这样的查询，则只需要较少的IO。且大大减少的索引的大小和在leaf block上的争用。

语法如下

CREATE TABLE mytable (
column_1 NUMBER(10),
column_2 VARCHAR2(10),
column_3 NUMBER(10),
column_4 VARCHAR2(50)
);

CREATE INDEX myindex ON mytable (column_1, column_2, column_3) GLOBAL
PARTITION BY HASH (column_1, column_2)
(PARTITION myindex_part_1 TABLESPACE users,
PARTITION myindex_part_2 TABLESPACE users,
PARTITION myindex_part_3 TABLESPACE users,
PARTITION myindex_part_4 TABLESPACE users);

– Add a new partition.
ALTER INDEX myindex ADD PARTITION mytable_part_5;

– Reduce the number of partitions by 1.
ALTER INDEX myindex COALESCE PARTITION;

– Rebuild partition.
ALTER INDEX myindex REBUILD PARTITION mytable_part_1 TABLESPACE users;

– Rename partition.
ALTER INDEX myindex RENAME PARTITION mytable_part_1 TO mytable_part_a;

– Clean up.
DROP INDEX myindex;
DROP TABLE mytable;

通过hash算法，oracle可以直接去查找某个index partition实现index partition elimination

SQL> select * from test_binzhang where ID IN (’a');
————————————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
————————————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 123 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION HASH SINGLE | | 1 | 123 | 1 (0)| 00:00:01 | 8 | 8 |
| 2 | TABLE ACCESS BY INDEX ROWID| test_binzhang | 1 | 123 | 1 (0)| 00:00:01 | | |
|* 3 | INDEX UNIQUE SCAN | test_binzhang_PK | 1 | | 1 (0)| 00:00:01 | 8 | 8 |
————————————————————————————————————————-

但这个功能需要10g的COMPATIBLE

SQL> CREATE INDEX test_binzhang ON test_binzhang (USER_ID) GLOBAL
2 PARTITION BY HASH (USER_ID) partitions 4
;
CREATE INDEX test_binzhang ON test_binzhang (USER_ID) GLOBAL
*
ERROR at line 1:
ORA-00406: COMPATIBLE parameter needs to be 10.0.0.0.0 or greater
ORA-00722: Feature "Hash Partitioned Global Index"


The major drawback with hash partitioned indexes is the fact that you will not experience
partition elimination for range type queries in general. For example, if you
HASH(last_name) in an index on last_name - the query:

where last_name = :bv

can use that index nicely and uses a single partition, but

where last_name like :bv
where last_name between :bv1 and :bv2
where last_name > :bv

will have to range scan each and every global hash partition - since the data it needs
could be in all of them (or none of them, or some of them)