Hash Partitioned Global Index
作者 ：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/05/hash_partitioned_global_index.html
链接：http://www.dbaroad.me/archives/2009/05/hash_partitioned_global_index.html
Hash partitioned global index是10G的新特性，Oracle对哪种情况下适合使用该索引，也有一点说明：
Hash partitioned global indexes can improve the performance of indexes
where a small number of leaf blocks in the index have high contention
in multiuser OLTP environments. Queries involving the equality and IN
predicates on the index partitioning key can efficiently use hash-partitioned global indexes.
散列分区索引对完全相等性查询（equality and IN）能达到较好的分区消除效果:
SQL> create table test as 2 select owner,object_name,object_id from dba_objects; Table created. SQL> create index ind_hash_01 on test (owner) global 2 partition by hash (owner) partitions 6; Index created. SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TEST',cascade=>true); PL/SQL procedure successfully completed. SQL> select * from test where owner='M1'; OWNER OBJECT_NAME OBJECT_ID --------------- --------------- ---------- M1 DEPT 88520 M1 PK_DEPT 88521 M1 EMP 88522 M1 CK_LOG 88526 M1 IND_EMP 88527 SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------ SQL_ID aaq4r433g1qu2, child number 0 ------------------------------------- select * from test where owner='M1' Plan hash value: 1034188634 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------ | 1 | PARTITION HASH SINGLE | | 1 | 1 | 5 |00:00:00.01 | 5 | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 1 | 5 |00:00:00.01 | 5 | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------ |* 3 | INDEX RANGE SCAN | IND_HASH_01 | 1 | 1 | 5 |00:00:00.01 | 3 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("OWNER"='M1') 19 rows selected. SQL> select * from test where owner in ('M1','OUTLN'); OWNER OBJECT_NAME OBJECT_ID ---------- ------------------------------ ---------- M1 DEPT 88520 M1 PK_DEPT 88521 M1 EMP 88522 M1 CK_LOG 88526 M1 IND_EMP 88527 OUTLN OL$ 452 OUTLN OL$HINTS 453 OUTLN SYS_LOB0000000453C00021$$ 454 OUTLN OL$NODES 456 OUTLN OL$NAME 457 OUTLN OL$SIGNATURE 458 OUTLN OL$HNT_NUM 459 OUTLN ORA$GRANT_SYS_SELECT 5493 13 rows selected. SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------ SQL_ID a9rjz12jc5kvc, child number 0 ------------------------------------- select * from test where owner in ('M1','OUTLN') Plan hash value: 1839128208 ------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------- | 1 | INLIST ITERATOR | | 1 | | 13 |00:00:00.01 | 9 | | 2 | PARTITION HASH ITERATOR | | 2 | 1 | 13 |00:00:00.01 | 9 | | 3 | TABLE ACCESS BY INDEX ROWID| TEST | 2 | 1 | 13 |00:00:00.01 | 9 | |* 4 | INDEX RANGE SCAN | IND_HASH_01 | 2 | 1 | 13 |00:00:00.01 | 5 | ------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access(("OWNER"='M1' OR "OWNER"='OUTLN')) 20 rows selected. |
而对一些范围查询，比如：like,between and,>等，则要返回所有分区:
SQL> select * from test where owner like 'M1%'; OWNER OBJECT_NAME OBJECT_ID --------------- --------------- ---------- M1 DEPT 88520 M1 PK_DEPT 88521 M1 EMP 88522 M1 CK_LOG 88526 M1 IND_EMP 88527 SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------ SQL_ID atj964wcsnnbs, child number 0 ------------------------------------- select * from test where owner like 'M1%' Plan hash value: 1551804055 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------ | 1 | PARTITION HASH ALL | | 1 | 1 | 5 |00:00:00.01 | 13 | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 6 | 1 | 5 |00:00:00.01 | 13 | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------ |* 3 | INDEX RANGE SCAN | IND_HASH_01 | 6 | 1 | 5 |00:00:00.01 | 11 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("OWNER" LIKE 'M1%') filter("OWNER" LIKE 'M1%') 20 rows selected. |
— The End —
关键字: 基础知识
站内搜索