通过案例学调优之--分区表索引

通过案例学调优之--分区表索引

分区表索引

Just like partitioned tables, partitioned indexes improve manageability, availability, perfORMance, and Scalability. They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method (local indexes). In general, you should use global indexes for OLTP applications and local indexes for data warehousing or DSS applications. Also, whenever possible, you should try to use local indexes because they are easier to manage. When deciding what kind of partitioned index to use, you should consider the following guidelines in order:

1. If the table partitioning column is a subset of the index keys, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 2.

2. If the index is unique, use a global index. If this is the case, you are finished. If this is not the case, continue to guideline 3.

3. If your priority is manageability, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 4.

4. If the application is an OLTP one and users need quick response times, use a global index. If the application is a DSS one and users are more interested in throughput, use a local index.

局部索引local index

1.        局部索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区说，一句话，局部索引的分区机制和表的分区机制一样。

2.        如果局部索引的索引列以分区键开头，则称为前缀局部索引。

3.        如果局部索引的列不是以分区键开头，或者不包含分区键列，则称为非前缀索引。

4.        前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。

5.        局部索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用局部索引去给表做唯一性约束，则约束中必须要包括分区键列。

6.        局部分区索引是对单个分区的，每个分区索引只指向一个表分区，全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区，对分区表中的某个分区做truncate或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，局部分区索引具有更高的可用性。

7.        位图索引只能为局部分区索引。

8.        局部索引多应用于数据仓库环境（OLAP)中。

全局索引global index

1.        全局索引的分区键和分区数和表的分区键和分区数可能都不相同，表和全局索引的分区机制不一样。

2.        全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前几列。

3.        全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只动，截断一个分区中的数据，都需要rebulid若干个分区甚至是整个索引。

4.        全局索引多应用于OLTP系统中。

5.        全局分区索引只按范围或者散列hash分区，hash分区是10g以后才支持。

6.        oracle9i以后对分区表做move或者truncate的时可以用update global indexes语句来同步更新全局分区索引，用消耗一定资源来换取高度的可用性。

7.        表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用b做全局分区索引

分区索引字典

DBA_PART_INDEXES   分区索引的概要统计信息，可以得知每个表上有哪些分区索引，分区索引的类新(local/global,)

Dba_ind_partitions    每个分区索引的分区级统计信息

Dba_indexesminusdba_part_indexes  可以得到每个表上有哪些非分区索引 

案例分析：

分区索引

1、局部前缀索引（Local Index）

11:48:28 SYS@ test1 >create index part_id_ind on part_t1(object_id) local;
Index created.

11:49:23 SYS@ test1 >select index_name, partitioning_type, partition_count from user_part_indexes
11:49:53   2   where index_name='PART_ID_IND';
INDEX_NAME                     PARTITION PARTITION_COUNT
------------------------------ --------- ---------------
PART_ID_IND                    RANGE                   5

11:53:55 SYS@ test1 >select index_name,PARTITION_NAME,STATUS ,TABLESPACE_NAME from user_ind_partitions
11:54:47   2   where index_name='PART_ID_IND';
INDEX_NAME                     PARTITION_NAME                 STATUS   TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
PART_ID_IND                    P1                             USABLE   TBS1
PART_ID_IND                    P2                             USABLE   TBS2
PART_ID_IND                    P3                             USABLE   TBS3
PART_ID_IND                    P4                             USABLE   SYSTEM
PART_ID_IND                    P5                             USABLE   SYSTEM

11:54:59 SYS@ test1 >select table_name,PARTITION_NAME ,PARTITION_POSITION,TABLESPACE_NAME from dba_tab_partitions
11:55:41   2  where table_name='PART_T1';
TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------------------
PART_T1                        P1                                              1 TBS1
PART_T1                        P2                                              2 TBS2
PART_T1                        P3                                              3 TBS3
PART_T1                        P4                                              4 SYSTEM
PART_T1                        P5                                              5 SYSTEM

11:56:18 SYS@ test1 >alter table part_t1 move partition p4 tablespace tbs4;
Table altered.

11:56:29 SYS@ test1 >alter table part_t1 move partition p5 tablespace tbs4;
Table altered.

11:56:43 SYS@ test1 >select table_name,PARTITION_NAME ,PARTITION_POSITION,TABLESPACE_NAME from dba_tab_partitions
11:56:51   2  where table_name='PART_T1';
TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------------------
PART_T1                        P1                                              1 TBS1
PART_T1                        P2                                              2 TBS2
PART_T1                        P3                                              3 TBS3
PART_T1                        P4                                              4 TBS4
PART_T1                        P5                                              5 TBS4

11:56:55 SYS@ test1 >select index_name,PARTITION_NAME,STATUS ,TABLESPACE_NAME from user_ind_partitions
11:57:04   2  where index_name='PART_ID_IND';
INDEX_NAME                     PARTITION_NAME                 STATUS   TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
PART_ID_IND                    P1                             USABLE   TBS1
PART_ID_IND                    P2                             USABLE   TBS2
PART_ID_IND                    P3                             USABLE   TBS3
PART_ID_IND                    P4                             UNUSABLE SYSTEM
PART_ID_IND                    P5                             UNUSABLE SYSTEM

11:58:31 SYS@ test1 >alter index PART_ID_IND rebuild partition p4 online;
Index altered.

12:03:52 SYS@ test1 >select index_name,PARTITION_NAME,STATUS ,TABLESPACE_NAME from user_ind_partitions
12:03:59   2  where index_name='PART_ID_IND';
INDEX_NAME                     PARTITION_NAME                 STATUS   TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
PART_ID_IND                    P1                             USABLE   TBS1
PART_ID_IND                    P2                             USABLE   TBS2
PART_ID_IND                    P3                             USABLE   TBS3
PART_ID_IND                    P4                             USABLE   SYSTEM
PART_ID_IND                    P5                             UNUSABLE SYSTEM

12:04:08 SYS@ test1 >alter index PART_ID_IND rebuild partition p4 online tablespace tbs4;
Index altered.

12:04:22 SYS@ test1 >alter index PART_ID_IND rebuild partition p5 online tablespace tbs4;
Index altered.

12:04:33 SYS@ test1 >select index_name,PARTITION_NAME,STATUS ,TABLESPACE_NAME from user_ind_partitions
12:04:39   2  where index_name='PART_ID_IND';
INDEX_NAME                     PARTITION_NAME                 STATUS   TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
PART_ID_IND                    P1                             USABLE   TBS1
PART_ID_IND                    P2                             USABLE   TBS2
PART_ID_IND                    P3                             USABLE   TBS3
PART_ID_IND                    P4                             USABLE   TBS4
PART_ID_IND                    P5                             USABLE   TBS4

 2、局部非前缀索引

13:26:27 SYS@ test1 >create index part_name_ind on part_t1(object_name) local;
Index created.

13:27:13 SYS@ test1 >select index_name,PARTITION_NAME,STATUS ,TABLESPACE_NAME from user_ind_partitions
13:27:23   2  where index_name='PART_NAME_IND';
INDEX_NAME                     PARTITION_NAME                 STATUS   TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
PART_NAME_IND                  P1                             USABLE   TBS1
PART_NAME_IND                  P2                             USABLE   TBS2
PART_NAME_IND                  P3                             USABLE   TBS3
PART_NAME_IND                  P4                             USABLE   TBS4
PART_NAME_IND                  P5                             USABLE   TBS4       
         
13:29:00 SYS@ test1 >select * from part_t1 where object_name='EMP';
OWNER
------------------------------
OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------
 OBJECT_ID OBJECT_TYPE         TIMESTAMP           STATUS
---------- ------------------- ------------------- -------
SCOTT
EMP
     14741 TABLE               2013-11-18:15:07:49 VALID
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2894019794
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |     1 |   123 |     7   (0)| 00:00:01 |          |
|   1 |  PARTITION RANGE ALL               |               |     1 |   123 |     7   (0)| 00:00:01 |     1  5 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PART_T1       |     1 |   123 |     7   (0)| 00:00:01 |     1  5 |
|*  3 |    INDEX RANGE SCAN                | PART_NAME_IND |     1 |       |     6   (0)| 00:00:01 |     1  5 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("OBJECT_NAME"='EMP')
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
        779  bytes sent via sql*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
13:29:07 SYS@ test1 >
13:29:07 SYS@ test1 >SELECT * FROM PART_T1 where object_id=14741;
OWNER
------------------------------
OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------
 OBJECT_ID OBJECT_TYPE         TIMESTAMP           STATUS
---------- ------------------- ------------------- -------
SCOTT
EMP
     14741 TABLE               2013-11-18:15:07:49 VALID
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3145656835
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |     1 |   123 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE            |             |     1 |   123 |     2   (0)| 00:00:01 |     5 |     5 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PART_T1     |     1 |   123 |     2   (0)| 00:00:01 |     5 |     5 |
|*  3 |    INDEX RANGE SCAN                | PART_ID_IND |     1 |       |     1   (0)| 00:00:01 |     5 |     5 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("OBJECT_ID"=14741)
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        779  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

3、全局非分区索引（Global Index）

13:37:50 SYS@ test1 >create index part_name_gind on part_t1(object_name) global；
13:37:54 SYS@ test1 >select * from part_t1 where object_name='EMP';
OWNER
------------------------------
OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------
 OBJECT_ID OBJECT_TYPE         TIMESTAMP           STATUS
---------- ------------------- ------------------- -------
SCOTT
EMP
     14741 TABLE               2013-11-18:15:07:49 VALID
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2017751627
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                |     1 |   123 |     2   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| PART_T1        |     1 |   123 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | PART_NAME_GIND |     1 |       |     1   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_NAME"='EMP')
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
         44  recursive calls
          0  db block gets
         77  consistent gets
          1  physical reads
          0  redo size
        783  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
13:38:04 SYS@ test1 >/
OWNER
------------------------------
OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------
 OBJECT_ID OBJECT_TYPE         TIMESTAMP           STATUS
---------- ------------------- ------------------- -------
SCOTT
EMP
     14741 TABLE               2013-11-18:15:07:49 VALID
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2017751627
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                |     1 |   123 |     2   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| PART_T1        |     1 |   123 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | PART_NAME_GIND |     1 |       |     1   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_NAME"='EMP')
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        783  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
13:38:24 SYS@ test1 >
13:40:01 SYS@ test1 >select index_name,PARTITION_NAME,STATUS ,TABLESPACE_NAME from user_ind_partitions
13:40:03   2  where index_name='PART_NAME_GIND';
no rows selected
13:40:47 SYS@ test1 >SELECT INDEX_NAME,TABLESPACE_NAME,INDEX_TYPE FROM USER_INDEXES
13:41:02   2   where index_name='PART_NAME_GIND';
INDEX_NAME                     TABLESPACE_NAME                INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
PART_NAME_GIND                 INDX                           NORMAL

4、全局分区索引（只能是前缀）

13:43:36 SYS@ test1 >create index part_name_gind on part_t1(object_name) global
13:44:15   2  partition by hash(object_name)
13:44:19   3  partitions 4
13:44:23   4  store in(tbs1,tbs2,tbs3,tbs4);
Index created.

13:44:38 SYS@ test1 >select index_name,PARTITION_NAME,STATUS ,TABLESPACE_NAME from user_ind_partitions
13:45:31   2  where index_name='PART_NAME_GIND';
INDEX_NAME                     PARTITION_NAME                 STATUS   TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
PART_NAME_GIND                 SYS_P61                        USABLE   TBS1
PART_NAME_GIND                 SYS_P62                        USABLE   TBS2
PART_NAME_GIND                 SYS_P63                        USABLE   TBS3
PART_NAME_GIND                 SYS_P64                        USABLE   TBS4

13:45:41 SYS@ test1 >set autotrace on
13:47:12 SYS@ test1 >select * from part_t1 where object_name='EMP';
OWNER
------------------------------
OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------
 OBJECT_ID OBJECT_TYPE         TIMESTAMP           STATUS
---------- ------------------- ------------------- -------
SCOTT
EMP
     14741 TABLE               2013-11-18:15:07:49 VALID
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2733506516
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     1 |   123 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION HASH SINGLE              |                |     1 |   123 |     2   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| PART_T1        |     1 |   123 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  3 |    INDEX RANGE SCAN                 | PART_NAME_GIND |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("OBJECT_NAME"='EMP')
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
         44  recursive calls
          0  db block gets
         80  consistent gets
          1  physical reads
          0  redo size
        779  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
13:47:29 SYS@ test1 >/
OWNER
------------------------------
OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------
 OBJECT_ID OBJECT_TYPE         TIMESTAMP           STATUS
---------- ------------------- ------------------- -------
SCOTT
EMP
     14741 TABLE               2013-11-18:15:07:49 VALID
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2733506516
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     1 |   123 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION HASH SINGLE              |                |     1 |   123 |     2   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| PART_T1        |     1 |   123 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  3 |    INDEX RANGE SCAN                 | PART_NAME_GIND |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("OBJECT_NAME"='EMP')
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        779  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 分区表索引维护

全局索引维护：

当对一个分区进行维护时，全局索引都会受到影响，必须重建
13:50:18 SYS@ test1 >alter table part_t1 move partition p1 tablespace tbs2;
13:51:17 SYS@ test1 >select index_name,PARTITION_NAME,STATUS ,TABLESPACE_NAME from user_ind_partitions
13:51:23   2  where index_name='PART_NAME_GIND'
13:51:28   3  /
INDEX_NAME                     PARTITION_NAME                 STATUS   TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
PART_NAME_GIND                 SYS_P61                        UNUSABLE TBS1
PART_NAME_GIND                 SYS_P62                        UNUSABLE TBS2
PART_NAME_GIND                 SYS_P63                        UNUSABLE TBS3
PART_NAME_GIND                 SYS_P64                        UNUSABLE TBS4

13:51:31 SYS@ test1 >alter table part_t1 move partition p1 tablespace tbs1;
Table altered.

13:52:30 SYS@ test1 >select index_name,PARTITION_NAME,STATUS ,TABLESPACE_NAME from user_ind_partitions
13:52:35   2  where index_name='PART_NAME_GIND';
INDEX_NAME                     PARTITION_NAME                 STATUS   TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
PART_NAME_GIND                 SYS_P61                        UNUSABLE TBS1
PART_NAME_GIND                 SYS_P62                        UNUSABLE TBS2
PART_NAME_GIND                 SYS_P63                        UNUSABLE TBS3
PART_NAME_GIND                 SYS_P64                        UNUSABLE TBS4
Elapsed: 00:00:00.00
13:52:40 SYS@ test1 >
13:52:40 SYS@ test1 >alter index PART_NAME_GIND rebuild;
alter index PART_NAME_GIND rebuild
            *
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole
Elapsed: 00:00:00.03
13:53:31 SYS@ test1 >alter index PART_NAME_GIND rebuild partition sys_p61;
Index altered.
Elapsed: 00:00:00.95
13:53:53 SYS@ test1 >alter index PART_NAME_GIND rebuild partition sys_p62;
Index altered.
Elapsed: 00:00:00.42
13:54:01 SYS@ test1 >alter index PART_NAME_GIND rebuild partition sys_p63;
Index altered.
Elapsed: 00:00:00.49
13:54:07 SYS@ test1 >alter index PART_NAME_GIND rebuild partition sys_p64;
Index altered.
Elapsed: 00:00:00.42
13:54:11 SYS@ test1 >select index_name,PARTITION_NAME,STATUS ,TABLESPACE_NAME from user_ind_partitions
13:54:18   2  where index_name='PART_NAME_GIND';
INDEX_NAME                     PARTITION_NAME                 STATUS   TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
PART_NAME_GIND                 SYS_P61                        USABLE   TBS1
PART_NAME_GIND                 SYS_P62                        USABLE   TBS2
PART_NAME_GIND                 SYS_P63                        USABLE   TBS3
PART_NAME_GIND                 SYS_P64                        USABLE   TBS4
Elapsed: 00:00:00.00

局部分区维护（Local）：

当对一个分区进行维护时，local 索引，只是对应的分区索引受到影响
13:56:08 SYS@ test1 >select index_name,PARTITION_NAME,STATUS ,TABLESPACE_NAME from user_ind_partitions
13:56:17   2  where index_name='PART_ID_IND';
INDEX_NAME                     PARTITION_NAME                 STATUS   TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
PART_ID_IND                    P1                             UNUSABLE TBS1
PART_ID_IND                    P2                             USABLE   TBS2
PART_ID_IND                    P3                             USABLE   TBS3
PART_ID_IND                    P4                             USABLE   TBS4
PART_ID_IND                    P5                             USABLE   TBS4
13:56:35 SYS@ test1 >ALTER INDEX PART_ID_IND rebuild partition p1;
Index altered.
Elapsed: 00:00:00.53
13:56:59 SYS@ test1 >select index_name,PARTITION_NAME,STATUS ,TABLESPACE_NAME from user_ind_partitions
13:57:04   2  where index_name='PART_ID_IND';
INDEX_NAME                     PARTITION_NAME                 STATUS   TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
PART_ID_IND                    P1                             USABLE   TBS1
PART_ID_IND                    P2                             USABLE   TBS2
PART_ID_IND                    P3                             USABLE   TBS3
PART_ID_IND                    P4                             USABLE   TBS4
PART_ID_IND                    P5                             USABLE   TBS4

 维护分区表时，更新全局索引

14:04:25 SYS@ test1 >select index_name,PARTITION_NAME,STATUS ,TABLESPACE_NAME from user_ind_partitions
14:04:39   2  where index_name='PART_NAME_GIND';
INDEX_NAME                     PARTITION_NAME                 STATUS   TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
PART_NAME_GIND                 SYS_P61                        USABLE   TBS1
PART_NAME_GIND                 SYS_P62                        USABLE   TBS2
PART_NAME_GIND                 SYS_P63                        USABLE   TBS3
PART_NAME_GIND                 SYS_P64                        USABLE   TBS4
Elapsed: 00:00:00.00
14:04:46 SYS@ test1 >alter table part_t1 move partition p1 tablespace tbs1 update global indexes;
Table altered.
Elapsed: 00:00:00.80
14:05:04 SYS@ test1 >select index_name,PARTITION_NAME,STATUS ,TABLESPACE_NAME from user_ind_partitions
14:05:09   2  where index_name='PART_NAME_GIND';
INDEX_NAME                     PARTITION_NAME                 STATUS   TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
PART_NAME_GIND                 SYS_P61                        USABLE   TBS1
PART_NAME_GIND                 SYS_P62                        USABLE   TBS2
PART_NAME_GIND                 SYS_P63                        USABLE   TBS3
PART_NAME_GIND                 SYS_P64                        USABLE   TBS4

local 索引 需要手工rebuild

14:06:25 SYS@ test1 >alter index PART_ID_IND rebuild partition p1;
Index altered.
Elapsed: 00:00:00.90
14:06:42 SYS@ test1 >select index_name,PARTITION_NAME,STATUS ,TABLESPACE_NAME from user_ind_partitions
14:06:46   2  where index_name='PART_ID_IND';
INDEX_NAME                     PARTITION_NAME                 STATUS   TABLESPACE_NAME
------------------------------ ------------------------------ -------- ------------------------------
PART_ID_IND                    P1                             USABLE   TBS1
PART_ID_IND                    P2                             USABLE   TBS2
PART_ID_IND                    P3                             USABLE   TBS3
PART_ID_IND                    P4                             USABLE   TBS4
PART_ID_IND                    P5                             USABLE   TBS4

分区表和非分区表访问对比

访问分区表：
sql>create table part_t2
     PARTITION BY RANGE (object_id)
    (partition p1 values less than (4000)  tablespace tbs1, 
    partition p2 values less than (8000)  tablespace tbs2,
    partition p3 values less than (12000) tablespace tbs3,
    partition p4 values less than (maxvalue) tablespace tbs4)
    as 
    select owner,object_name,object_id,object_type,TIMESTAMP,status from dba_objects;
    
12:47:40 SYS@ test1 >set autotrac trace
12:48:49 SYS@ test1 >select * from part_t2 where object_id < 4000;
3931 rows selected.
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 1733007488
--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |  3931 |   472K|    12   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|         |  3931 |   472K|    12   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS FULL    | PART_T2 |  3931 |   472K|    12   (0)| 00:00:01 |     1 |     1 |
--------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
        132  recursive calls
          0  db block gets
        361  consistent gets
          6  physical reads
          0  redo size
     192675  bytes sent via SQL*Net to client
       3301  bytes received via SQL*Net from client
        264  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3931  rows processed

访问非分区表：
12:50:29 SYS@ test1 >set autotrace trace
12:51:06 SYS@ test1 >select * from dba_objects where object_id <4000;
3931 rows selected.
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
Plan hash value: 1409114634
----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |  3099 |   626K|    49   (3)| 00:00:01 |
|   1 |  VIEW                          | DBA_OBJECTS |  3099 |   626K|    49   (3)| 00:00:01 |
|   2 |   UNION-ALL                    |             |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID | SUM$        |     1 |    26 |     0   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN          | I_SUM$_1    |     1 |       |     0   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID | OBJ$        |     1 |    24 |     3   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN           | I_OBJ1      |     1 |       |     2   (0)| 00:00:01 |
|*  7 |    FILTER                      |             |       |       |            |          |
|*  8 |     HASH JOIN                  |             |  3486 |   391K|    49   (3)| 00:00:01 |
|   9 |      TABLE ACCESS FULL         | USER$       |    41 |   697 |     3   (0)| 00:00:01 |
|* 10 |      HASH JOIN                 |             |  3486 |   333K|    46   (3)| 00:00:01 |
|  11 |       INDEX FULL SCAN          | I_USER2     |    41 |   861 |     1   (0)| 00:00:01 |
|* 12 |       TABLE ACCESS FULL        | OBJ$        |  3486 |   262K|    44   (0)| 00:00:01 |
|* 13 |     TABLE ACCESS BY INDEX ROWID| IND$        |     1 |     8 |     2   (0)| 00:00:01 |
|* 14 |      INDEX UNIQUE SCAN         | I_IND1      |     1 |       |     1   (0)| 00:00:01 |
|  15 |     NESTED LOOPS               |             |     1 |    28 |     2   (0)| 00:00:01 |
|* 16 |      INDEX FULL SCAN           | I_USER2     |     1 |    19 |     1   (0)| 00:00:01 |
|* 17 |      INDEX RANGE SCAN          | I_OBJ4      |     1 |     9 |     1   (0)| 00:00:01 |
|* 18 |    FILTER                      |             |       |       |            |          |
|  19 |     NESTED LOOPS               |             |     1 |   105 |     3   (0)| 00:00:01 |
|  20 |      TABLE ACCESS FULL         | LINK$       |     1 |    88 |     2   (0)| 00:00:01 |
|  21 |      TABLE ACCESS CLUSTER      | USER$       |     1 |    17 |     1   (0)| 00:00:01 |
|* 22 |       INDEX UNIQUE SCAN        | I_USER#     |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
   4 - access("S"."OBJ#"=:B1)
   6 - access("EO"."OBJ#"=:B1)
   7 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND  (SELECT 1
              FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
              "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
              "I"."TYPE#"=9))=1) AND ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND
              "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND
              "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND
              "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR
              "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR
              "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR
              "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND (SYS_CONTEXT('userenv','current_edition_name')='
              ORA$BASE' AND "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND
              "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR  EXISTS
              (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
              "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
   8 - access("O"."SPARE3"="U"."USER#")
  10 - access("O"."OWNER#"="U"."USER#")
  12 - filter("O"."OBJ#"<4000 AND "O"."NAME"<>'_NEXT_OBJECT' AND
              "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
              BITAND("O"."FLAGS",128)=0)
  13 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
  14 - access("I"."OBJ#"=:B1)
  16 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','curren
              t_edition_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','curren
              t_edition_id')))
  17 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  18 - filter(NULL IS NOT NULL)
  22 - access("L"."OWNER#"="U"."USER#")
Statistics
----------------------------------------------------------
         38  recursive calls
          0  db block gets
        670  consistent gets
          3  physical reads
          0  redo size
     228642  bytes sent via SQL*Net to client
       3301  bytes received via SQL*Net from client
        264  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3931  rows processed
12:51:26 SYS@ test1 >/
3931 rows selected.
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
Plan hash value: 1409114634
----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |  3099 |   626K|    49   (3)| 00:00:01 |
|   1 |  VIEW                          | DBA_OBJECTS |  3099 |   626K|    49   (3)| 00:00:01 |
|   2 |   UNION-ALL                    |             |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID | SUM$        |     1 |    26 |     0   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN          | I_SUM$_1    |     1 |       |     0   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID | OBJ$        |     1 |    24 |     3   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN           | I_OBJ1      |     1 |       |     2   (0)| 00:00:01 |
|*  7 |    FILTER                      |             |       |       |            |          |
|*  8 |     HASH JOIN                  |             |  3486 |   391K|    49   (3)| 00:00:01 |
|   9 |      TABLE ACCESS FULL         | USER$       |    41 |   697 |     3   (0)| 00:00:01 |
|* 10 |      HASH JOIN                 |             |  3486 |   333K|    46   (3)| 00:00:01 |
|  11 |       INDEX FULL SCAN          | I_USER2     |    41 |   861 |     1   (0)| 00:00:01 |
|* 12 |       TABLE ACCESS FULL        | OBJ$        |  3486 |   262K|    44   (0)| 00:00:01 |
|* 13 |     TABLE ACCESS BY INDEX ROWID| IND$        |     1 |     8 |     2   (0)| 00:00:01 |
|* 14 |      INDEX UNIQUE SCAN         | I_IND1      |     1 |       |     1   (0)| 00:00:01 |
|  15 |     NESTED LOOPS               |             |     1 |    28 |     2   (0)| 00:00:01 |
|* 16 |      INDEX FULL SCAN           | I_USER2     |     1 |    19 |     1   (0)| 00:00:01 |
|* 17 |      INDEX RANGE SCAN          | I_OBJ4      |     1 |     9 |     1   (0)| 00:00:01 |
|* 18 |    FILTER                      |             |       |       |            |          |
|  19 |     NESTED LOOPS               |             |     1 |   105 |     3   (0)| 00:00:01 |
|  20 |      TABLE ACCESS FULL         | LINK$       |     1 |    88 |     2   (0)| 00:00:01 |
|  21 |      TABLE ACCESS CLUSTER      | USER$       |     1 |    17 |     1   (0)| 00:00:01 |
|* 22 |       INDEX UNIQUE SCAN        | I_USER#     |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
   4 - access("S"."OBJ#"=:B1)
   6 - access("EO"."OBJ#"=:B1)
   7 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND  (SELECT 1
              FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
              "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
              "I"."TYPE#"=9))=1) AND ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND
              "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND
              "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND
              "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR
              "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR
              "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR
              "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND (SYS_CONTEXT('userenv','current_edition_name')='
              ORA$BASE' AND "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND
              "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR  EXISTS
              (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
              "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
   8 - access("O"."SPARE3"="U"."USER#")
  10 - access("O"."OWNER#"="U"."USER#")
  12 - filter("O"."OBJ#"<4000 AND "O"."NAME"<>'_NEXT_OBJECT' AND
              "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
              BITAND("O"."FLAGS",128)=0)
  13 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
  14 - access("I"."OBJ#"=:B1)
  16 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','curren
              t_edition_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','curren
              t_edition_id')))
  17 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  18 - filter(NULL IS NOT NULL)
  22 - access("L"."OWNER#"="U"."USER#")
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        663  consistent gets
          0  physical reads
          0  redo size
     228642  bytes sent via SQL*Net to client
       3301  bytes received via SQL*Net from client
        264  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3931  rows processed