说明：之前使用range分区做出来的效果不明显，这次使用hash分区。
1、准备工作：
----创建两张一样的hash分区表，jacks_part和echos_part------------------ 1 SQL> create table jacks_part (owner varchar2(30),object_id number,object_name varchar2(128)) 2 2 partition by hash(object_id) 3 3 partitions 30; 4 5 Table created. 6 7 SQL> create table echos_part (owner varchar2(30),object_id number,object_name varchar2(128)) 8 2 partition by hash(object_id) 9 3 partitions 30; 10 11 Table created.12 ----分别向两张表插入一些记录----------------- 13 SQL> insert into jacks_part select owner,object_id,object_name from dba_objects;14 15 72196 rows created.16 17 SQL> insert into echos_part select owner,object_id,object_name from jacks_part;18 19 72196 rows created.20 21 SQL> commit;22 23 Commit complete.24 ----分别创建global索引和local索引--------------- 25 SQL> create index globals_ind on jacks_part(object_id)26 2 global partition by hash(object_id);27 28 Index created.29 30 SQL> create index locals_ind on echos_part(object_id) local;31 32 Index created.33 ----查询索引是否正确-------------------------- 34 SQL> select index_name,table_name,locality from user_part_indexes;35 36 INDEX_NAME TABLE_NAME LOCALI37 ------------------ ------------------------------ ------38 LOCALS_IND ECHOS_PART LOCAL39 GLOBALS_IND JACKS_PART GLOBAL
2、分区索引性能优于全局索引的例子：
1 SQL> set linesize 200; 2 SQL> set autotrace traceonly; 3 SQL> select /*+ index(echos_part,locals_ind) */ * from echos_part where object_id>100; 4 5 72097 rows selected. 6 7 8 Execution Plan 9 ----------------------------------------------------------10 Plan hash value: 309281521111 12 -----------------------------------------------------------------------------------------------------------------13 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |14 -----------------------------------------------------------------------------------------------------------------15 | 0 | SELECT STATEMENT | | 4228 | 396K| 89 (0)| 00:00:02 | | |16 | 1 | PARTITION HASH ALL | | 4228 | 396K| 89 (0)| 00:00:02 | 1 | 30 |17 | 2 | TABLE ACCESS BY LOCAL INDEX ROWID| ECHOS_PART | 4228 | 396K| 89 (0)| 00:00:02 | 1 | 30 |18 |* 3 | INDEX RANGE SCAN | LOCALS_IND | 4228 | | 25 (0)| 00:00:01 | 1 | 30 |19 -----------------------------------------------------------------------------------------------------------------20 21 Predicate Information (identified by operation id):22 ---------------------------------------------------23 24 3 - access("OBJECT_ID">100)25 26 Note27 -----28 - dynamic sampling used for this statement (level=2)29 30 31 Statistics32 ----------------------------------------------------------33 0 recursive calls34 0 db block gets35 10562 consistent gets36 0 physical reads37 0 redo size38 3128267 bytes sent via SQL*Net to client39 53285 bytes received via SQL*Net from client40 4808 SQL*Net roundtrips to/from client41 0 sorts (memory)42 0 sorts (disk)43 72097 rows processed44 45 SQL> select /*+ index(jacks_part,globals_ind) */ * from jacks_part where object_id>100;46 47 72097 rows selected.48 49 50 Execution Plan51 ----------------------------------------------------------52 Plan hash value: 250144835253 54 -------------------------------------------------------------------------------------------------------------------55 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |56 -------------------------------------------------------------------------------------------------------------------57 | 0 | SELECT STATEMENT | | 2500 | 234K| 4639 (1)| 00:00:56 | | |58 | 1 | PARTITION HASH SINGLE | | 2500 | 234K| 4639 (1)| 00:00:56 | 1 | 1 |59 | 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| JACKS_PART | 2500 | 234K| 4639 (1)| 00:00:56 | ROWID | ROWID |60 |* 3 | INDEX RANGE SCAN | GLOBALS_IND | 2500 | | 15 (0)| 00:00:01 | 1 | 1 |61 -------------------------------------------------------------------------------------------------------------------62 63 Predicate Information (identified by operation id):64 ---------------------------------------------------65 66 3 - access("OBJECT_ID">100)67 68 Note69 -----70 - dynamic sampling used for this statement (level=2)71 72 73 Statistics74 ----------------------------------------------------------75 0 recursive calls76 0 db block gets77 74718 consistent gets78 0 physical reads79 0 redo size80 3077218 bytes sent via SQL*Net to client81 53285 bytes received via SQL*Net from client82 4808 SQL*Net roundtrips to/from client83 0 sorts (memory)84 0 sorts (disk)85 72097 rows processed
3、分区索引性能低于全局索引的例子1：
1 SQL> select /*+ index(echos_part,locals_ind) */ count(*) from echos_part where object_id>100; 2 3 4 Execution Plan 5 ---------------------------------------------------------- 6 Plan hash value: 2317569636 7 8 -------------------------------------------------------------------------------------------------- 9 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |10 --------------------------------------------------------------------------------------------------11 | 0 | SELECT STATEMENT | | 1 | 13 | 25 (0)| 00:00:01 | | |12 | 1 | SORT AGGREGATE | | 1 | 13 | | | | |13 | 2 | PARTITION HASH ALL| | 4228 | 54964 | 25 (0)| 00:00:01 | 1 | 30 |14 |* 3 | INDEX RANGE SCAN | LOCALS_IND | 4228 | 54964 | 25 (0)| 00:00:01 | 1 | 30 |15 --------------------------------------------------------------------------------------------------16 17 Predicate Information (identified by operation id):18 ---------------------------------------------------19 20 3 - access("OBJECT_ID">100)21 22 Note23 -----24 - dynamic sampling used for this statement (level=2)25 26 27 Statistics28 ----------------------------------------------------------29 0 recursive calls30 0 db block gets31 205 consistent gets32 0 physical reads33 0 redo size34 424 bytes sent via SQL*Net to client35 419 bytes received via SQL*Net from client36 2 SQL*Net roundtrips to/from client37 0 sorts (memory)38 0 sorts (disk)39 1 rows processed40 41 SQL> select /*+ index(jacks_part,globals_ind) */ count(*) from jacks_part where object_id>100;42 43 44 Execution Plan45 ----------------------------------------------------------46 Plan hash value: 247812913747 48 ------------------------------------------------------------------------------------------------------49 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |50 ------------------------------------------------------------------------------------------------------51 | 0 | SELECT STATEMENT | | 1 | 13 | 15 (0)| 00:00:01 | | |52 | 1 | SORT AGGREGATE | | 1 | 13 | | | | |53 | 2 | PARTITION HASH SINGLE| | 2500 | 32500 | 15 (0)| 00:00:01 | 1 | 1 |54 |* 3 | INDEX RANGE SCAN | GLOBALS_IND | 2500 | 32500 | 15 (0)| 00:00:01 | 1 | 1 |55 ------------------------------------------------------------------------------------------------------56 57 Predicate Information (identified by operation id):58 ---------------------------------------------------59 60 3 - access("OBJECT_ID">100)61 62 Note63 -----64 - dynamic sampling used for this statement (level=2)65 66 67 Statistics68 ----------------------------------------------------------69 0 recursive calls70 0 db block gets71 201 consistent gets72 0 physical reads73 0 redo size74 424 bytes sent via SQL*Net to client75 419 bytes received via SQL*Net from client76 2 SQL*Net roundtrips to/from client77 0 sorts (memory)78 0 sorts (disk)79 1 rows processed 分区索引性能低于全局索引的例子2：
1 SQL> drop index globals_ind; 2 3 Index dropped. 4 5 SQL> create index global_indexs on jacks_part(object_id) global; 6 7 Index created. 8 9 SQL> select /*+ index(echos_part,locals_ind) */ count(*) from echos_part where object_id>100;10 11 12 13 Execution Plan14 ----------------------------------------------------------15 Plan hash value: 231756963616 17 --------------------------------------------------------------------------------------------------18 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |19 --------------------------------------------------------------------------------------------------20 | 0 | SELECT STATEMENT | | 1 | 5 | 175 (0)| 00:00:03 | | |21 | 1 | SORT AGGREGATE | | 1 | 5 | | | | |22 | 2 | PARTITION HASH ALL| | 72101 | 352K| 175 (0)| 00:00:03 | 1 | 30 |23 |* 3 | INDEX RANGE SCAN | LOCALS_IND | 72101 | 352K| 175 (0)| 00:00:03 | 1 | 30 |24 --------------------------------------------------------------------------------------------------25 26 Predicate Information (identified by operation id):27 ---------------------------------------------------28 29 3 - access("OBJECT_ID">100)30 31 32 Statistics33 ----------------------------------------------------------34 1704 recursive calls35 0 db block gets36 437 consistent gets37 206 physical reads38 0 redo size39 40 SQL> select /*+ index(jacks_part,global_indexs) */ count(*) from jacks_part where object_id>100;41 42 43 Execution Plan44 ----------------------------------------------------------45 Plan hash value: 101656623846 47 -----------------------------------------------------------------------------------48 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |49 -----------------------------------------------------------------------------------50 | 0 | SELECT STATEMENT | | 1 | 5 | 201 (0)| 00:00:03 |51 | 1 | SORT AGGREGATE | | 1 | 5 | | |52 |* 2 | INDEX RANGE SCAN| GLOBAL_INDEXS | 72101 | 352K| 201 (0)| 00:00:03 |53 -----------------------------------------------------------------------------------54 55 Predicate Information (identified by operation id):56 ---------------------------------------------------57 58 2 - access("OBJECT_ID">100)59 60 61 Statistics62 ----------------------------------------------------------63 1 recursive calls64 0 db block gets65 201 consistent gets66 200 physical reads67 0 redo size