说明：之前使用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 LOCALI 37 ------------------ ------------------------------ ------ 38 LOCALS_IND ECHOS_PART LOCAL 39 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: 3092815211 11 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 Note 27 ----- 28 - dynamic sampling used for this statement (level=2) 29 30 31 Statistics 32 ---------------------------------------------------------- 33 0 recursive calls 34 0 db block gets 35 10562 consistent gets 36 0 physical reads 37 0 redo size 38 3128267 bytes sent via SQL*Net to client 39 53285 bytes received via SQL*Net from client 40 4808 SQL*Net roundtrips to/from client 41 0 sorts (memory) 42 0 sorts (disk) 43 72097 rows processed 44 45 SQL> select /*+ index(jacks_part,globals_ind) */ * from jacks_part where object_id>100; 46 47 72097 rows selected. 48 49 50 Execution Plan 51 ---------------------------------------------------------- 52 Plan hash value: 2501448352 53 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 Note 69 ----- 70 - dynamic sampling used for this statement (level=2) 71 72 73 Statistics 74 ---------------------------------------------------------- 75 0 recursive calls 76 0 db block gets 77 74718 consistent gets 78 0 physical reads 79 0 redo size 80 3077218 bytes sent via SQL*Net to client 81 53285 bytes received via SQL*Net from client 82 4808 SQL*Net roundtrips to/from client 83 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 Note 23 ----- 24 - dynamic sampling used for this statement (level=2) 25 26 27 Statistics 28 ---------------------------------------------------------- 29 0 recursive calls 30 0 db block gets 31 205 consistent gets 32 0 physical reads 33 0 redo size 34 424 bytes sent via SQL*Net to client 35 419 bytes received via SQL*Net from client 36 2 SQL*Net roundtrips to/from client 37 0 sorts (memory) 38 0 sorts (disk) 39 1 rows processed 40 41 SQL> select /*+ index(jacks_part,globals_ind) */ count(*) from jacks_part where object_id>100; 42 43 44 Execution Plan 45 ---------------------------------------------------------- 46 Plan hash value: 2478129137 47 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 Note 63 ----- 64 - dynamic sampling used for this statement (level=2) 65 66 67 Statistics 68 ---------------------------------------------------------- 69 0 recursive calls 70 0 db block gets 71 201 consistent gets 72 0 physical reads 73 0 redo size 74 424 bytes sent via SQL*Net to client 75 419 bytes received via SQL*Net from client 76 2 SQL*Net roundtrips to/from client 77 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 Plan 14 ---------------------------------------------------------- 15 Plan hash value: 2317569636 16 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 Statistics 33 ---------------------------------------------------------- 34 1704 recursive calls 35 0 db block gets 36 437 consistent gets 37 206 physical reads 38 0 redo size 39 40 SQL> select /*+ index(jacks_part,global_indexs) */ count(*) from jacks_part where object_id>100; 41 42 43 Execution Plan 44 ---------------------------------------------------------- 45 Plan hash value: 1016566238 46 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 Statistics 62 ---------------------------------------------------------- 63 1 recursive calls 64 0 db block gets 65 201 consistent gets 66 200 physical reads 67 0 redo size