全局索引与本地分区索引的一个重要区别

       全局索引和本地分区索引的概念很简单，全局索引，把索引建在一个段中，本地分区索引，按照每个分区来建索引，分区有多少个，分区索引就有多少个。DDL会使全局索引失效，而本地分区索引不受影响，这个对数据的维护意义重大。下面来做一个实验：

SQL> create table TEST
  2  (
  3    OWNER          VARCHAR2(30) not null,
  4    OBJECT_NAME    VARCHAR2(30) not null,
  5    SUBOBJECT_NAME VARCHAR2(30),
  6    OBJECT_ID      NUMBER not null,
  7    DATA_OBJECT_ID NUMBER,
  8    OBJECT_TYPE    VARCHAR2(19),
  9    CREATED        DATE not null,
 10    LAST_DDL_TIME  DATE not null,
 11    TIMESTAMP      VARCHAR2(19),
 12    STATUS         VARCHAR2(7),
 13    TEMPORARY      VARCHAR2(1),
 14    GENERATED      VARCHAR2(1),
 15    SECONDARY      VARCHAR2(1)
 16  )
 17  partition by list (OWNER)
 18  (
 19    partition PART_OWNER1 values ('SYS'),
 20    partition PART_OWNER2 values ('PUBLIC'),
 21    partition PART_OWNER3 values ('GG'),
 22    partition PART_OWNER4 values ('GG_ARCHIVE'),
 23    partition PART_OWNER5 values ('GG_QUERY'),
 24    partition PART_OWNER6 values ('DFWMS'),
 25    partition PART_OWNER7 values ('ORDSYS'),
 26    partition PART_OWNER8 values ('SYSMAN'),
 27    partition PART_OWNER9 values ('MDSYS'),
 28    partition PART_OWNER10 values ('OLAPSYS'),
 29    partition PART_OTHER values (default)
 30  );

表已创建。

SQL> insert into test select * from dba_objects where object_id is not null;
已创建89039行。

SQL> commit;
提交完成。

SQL> create index ind_g_object_id on test(object_id);   ----建立全局索引
索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'test',cascade => true);
PL/SQL 过程已成功完成。

SQL> set autotrace trace exp
SQL> select * from test where object_id = 17689;
执行计划
----------------------------------------------------------
Plan hash value: 2926561466

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows| Bytes | Cost (%CPU)| Time    | Pstart| Psto
p |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |   1 |    93 |     2   (0)| 00:00:01|       ||
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TEST            |   1 |    93 |     2   (0)| 00:00:01| ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | IND_G_OBJECT_ID |   1 |       |     1   (0)| 00:00:01|       ||   ---索引扫描
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=17689)

SQL> set autotrace off
SQL> alter table test drop partition PART_OWNER9;      ---删除一个分区
表已更改。

SQL> set autotrace trace exp
SQL> select * from test where object_id = 76154;

执行计划
----------------------------------------------------------
Plan hash value: 4170016888
-------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    93 |   318   (1)| 00:00:04 |       |       |
|   1 |  PARTITION LIST ALL|      |     1 |    93 |   318   (1)| 00:00:04 |     1 |    10 |
|*  2 |   TABLE ACCESS FULL| TEST |     1 |    93 |   318   (1)| 00:00:04 |     1 |    10 |     ---索引失效，变为了全表扫描
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID"=76154)

SQL> drop index ind_g_object_id;
索引已删除。

SQL> create index ind_l_object_id on test(object_id) local; --- 建立本地分区索引
索引已创建。

SQL> select * from test where object_id = 76154;

执行计划
----------------------------------------------------------
Plan hash value: 2133679295
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
---
|   0 | SELECT STATEMENT                   |                 |    1 |    93 |    12   (0)| 00:00:01 |       ||
|   1 |  PARTITION LIST ALL                |                 |    1 |    93 |    12   (0)| 00:00:01 |     1 |    10 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TEST            |    1 |    93 |    12   (0)| 00:00:01 |     1 |    10 |  --- 索引扫描
|*  3 |    INDEX RANGE SCAN                | IND_L_OBJECT_ID |    1 |       |    11   (0)| 00:00:01 |     1 |    10 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("OBJECT_ID"=76154)

SQL> alter table test drop partition PART_OWNER5; ----删除一个分区

表已更改。

SQL> select * from test where object_id = 76154;
执行计划
----------------------------------------------------------
Plan hash value: 2133679295
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |     1 |    93 |    11   (0)| 00:00:01 |       ||
|   1 |  PARTITION LIST ALL                |                 |     1 |    93 |    11   (0)| 00:00:01 |     1 |9 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TEST            |     1 |    93 |    11   (0)| 00:00:01 |     1 |9 |   --- 依然索引扫描
|*  3 |    INDEX RANGE SCAN                | IND_L_OBJECT_ID |     1 |       |    10   (0)| 00:00:01 |     1 |9 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("OBJECT_ID"=76154)