从执行的性能上来讲，请问全局索引与分区索引各适合于什么场景？

Question

本帖最后由 xiaoyanglei 于 2013-01-07 10:43:58 编辑

Answer 1

自己演示了下，分区索引在跨分区查询后会分区消除。效率反而没有全表索引高
--创建分区表语句
CREATE TABLE BIG_TABLE2 
( ID NUMBER NOT NULL,
OWNER VARCHAR2(30) NOT NULL,
object_name VARCHAR2(30) NOT NULL,
 SUBOBJECT_NAME VARCHAR2(30),
object_id NUMBER NOT NULL,
data_object_id NUMBER,
object_type VARCHAR2(19),
created date   NOT NULL,
LAST_DDL_TIME DATE   NOT NULL,
TIMESTAMP VARCHAR2(19),
STATUS VARCHAR2(7),
TEMPORARY VARCHAR2(1),
GENERATED VARCHAR2(1),
SECONDARY VARCHAR2(1),
NAMESPACE NUMBER NOT NULL,
EDITION_NAME VARCHAR2(30)
)
PARTITION BY RANGE(ID)
(
PARTITION BIG_TABLE2_10 VALUES LESS THAN (100000)  ,
PARTITION BIG_TABLE2_20 VALUES LESS THAN (200000)  ,
PARTITION BIG_TABLE2_30 VALUES LESS THAN (300000)  ,
PARTITION BIG_TABLE2_40 VALUES LESS THAN (400000)  ,
PARTITION BIG_TABLE2_50 VALUES LESS THAN (500000)  ,
PARTITION BIG_TABLE2_60 VALUES LESS THAN (600000)  ,
PARTITION BIG_TABLE2_70 VALUES LESS THAN (700000)  ,
PARTITION BIG_TABLE2_80 VALUES LESS THAN (800000)  ,
PARTITION BIG_TABLE2_90 VALUES LESS THAN (900000)  ,
PARTITION BIG_TABLE2_100 VALUES LESS THAN (1000000)  
)
--创建分区本地索引
create index ind_BIG_TABLE2 on BIG_TABLE2(id) local
SQL> select count(*) from big_table2;  COUNT(*)
----------
    999999SQL> select count(*) from big_table3;  COUNT(*)
----------
999999
分区索引优于全局索引例子
SQL> select count(*) from big_table3 where id between 508722 and 940000;  COUNT(*)
----------
    431279
执行计划
----------------------------------------------------------
Plan hash value: 856390402----------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                |     1 |    13 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IND_BIG_TABLE3 |     1 |    13 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - filter("ID">=508722 AND "ID"<=940000)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2523  consistent gets
          0  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> select count(*) from big_table2 where id between 508722 and 940000;  COUNT(*)
----------
    431279
执行计划
----------------------------------------------------------
Plan hash value: 4144809290------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                |     1 |    13 |     1   (0)| 00:00:01 |       |    |
|   1 |  SORT AGGREGATE           |                |     1 |    13 |            |          |       |    |
|   2 |   PARTITION RANGE ITERATOR|                |     1 |    13 |     1   (0)| 00:00:01 |     6 | 10 |
|*  3 |    INDEX RANGE SCAN       | IND_BIG_TABLE2 |     1 |    13 |     1   (0)| 00:00:01 |     6 | 10 |
------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   3 - access("ID">=508722 AND "ID"<=940000)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1669  consistent gets
          0  physical reads
      63980  redo size
        425  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
1 rows processed
分区索引比全局索引在一致性读上面要小。
全局索引优于分区索引例子
Create index idx_big_table2_object_id on big_table2(object_id) local;
Create index idx_big_table3_object_id on big_table2(object_id) global;SQL> select count(*) from big_table2 where object_id=12345;  COUNT(*)
----------
        14
执行计划
----------------------------------------------------------
Plan hash value: 2921771068--------------------------------------------------------------------------------------------
| Id  | Operation            | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart|
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                          |     1 |    13 |    10   (0)| 00:00:01 |    |  |
|   1 |  SORT AGGREGATE      |                          |     1 |    13 |            |          |    |  |
|   2 |   PARTITION RANGE ALL|                          |     1 |    13 |    10   (0)| 00:00:01 |     1 |
|*  3 |    INDEX RANGE SCAN  | IDX_BIG_TABLE2_OBJECT_ID |     1 |    13 |    10   (0)| 00:00:01 |     1 |
----------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   3 - access("OBJECT_ID"=12345)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         20  consistent gets
         17  physical reads
          0  redo size
        423  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processedSQL> select count(*) from big_table3 where object_id=12345;  COUNT(*)
----------
        14
执行计划
----------------------------------------------------------
Plan hash value: 1427655262----------------------------------------------------------------------------------------------
| Id  | Operation         | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                          |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                          |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_BIG_TABLE3_OBJECT_ID |     1 |    13 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - access("OBJECT_ID"=12345)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          2  physical reads
          0  redo size
        423  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed