溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

通過案例學(xué)調(diào)優(yōu)之--分區(qū)表索引

發(fā)布時(shí)間：2020-08-01 14:30:59 來源：網(wǎng)絡(luò) 閱讀：574 作者：客居天涯 欄目：關(guān)系型數(shù)據(jù)庫(kù)

通過案例學(xué)調(diào)優(yōu)之--分區(qū)表索引


分區(qū)表索引

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.        局部索引一定是分區(qū)索引，分區(qū)鍵等同于表的分區(qū)鍵，分區(qū)數(shù)等同于表的分區(qū)說，一句話，局部索引的分區(qū)機(jī)制和表的分區(qū)機(jī)制一樣。

2.        如果局部索引的索引列以分區(qū)鍵開頭，則稱為前綴局部索引。

3.        如果局部索引的列不是以分區(qū)鍵開頭，或者不包含分區(qū)鍵列，則稱為非前綴索引。

4.        前綴和非前綴索引都可以支持索引分區(qū)消除，前提是查詢的條件中包含索引分區(qū)鍵。

5.        局部索引只支持分區(qū)內(nèi)的唯一性，無法支持表上的唯一性，因此如果要用局部索引去給表做唯一性約束，則約束中必須要包括分區(qū)鍵列。

6.        局部分區(qū)索引是對(duì)單個(gè)分區(qū)的，每個(gè)分區(qū)索引只指向一個(gè)表分區(qū)，全局索引則不然，一個(gè)分區(qū)索引能指向n個(gè)表分區(qū)，同時(shí)，一個(gè)表分區(qū)，也可能指向n個(gè)索引分區(qū)，對(duì)分區(qū)表中的某個(gè)分區(qū)做truncate或者move，shrink等，可能會(huì)影響到n個(gè)全局索引分區(qū)，正因?yàn)檫@點(diǎn)，局部分區(qū)索引具有更高的可用性。

7.        位圖索引只能為局部分區(qū)索引。

8.        局部索引多應(yīng)用于數(shù)據(jù)倉(cāng)庫(kù)環(huán)境（OLAP)中。

全局索引global index

1.        全局索引的分區(qū)鍵和分區(qū)數(shù)和表的分區(qū)鍵和分區(qū)數(shù)可能都不相同，表和全局索引的分區(qū)機(jī)制不一樣。

2.        全局索引可以分區(qū)，也可以是不分區(qū)索引，全局索引必須是前綴索引，即全局索引的索引列必須是以索引分區(qū)鍵作為其前幾列。

3.        全局分區(qū)索引的索引條目可能指向若干個(gè)分區(qū)，因此，對(duì)于全局分區(qū)索引，即使只動(dòng)，截?cái)嘁粋€(gè)分區(qū)中的數(shù)據(jù)，都需要rebulid若干個(gè)分區(qū)甚至是整個(gè)索引。

4.        全局索引多應(yīng)用于OLTP系統(tǒng)中。

5.        全局分區(qū)索引只按范圍或者散列hash分區(qū)，hash分區(qū)是10g以后才支持。

6.        oracle9i以后對(duì)分區(qū)表做move或者truncate的時(shí)可以用update global indexes語(yǔ)句來同步更新全局分區(qū)索引，用消耗一定資源來?yè)Q取高度的可用性。

7.        表用a列作分區(qū)，索引用b做局部分區(qū)索引，若where條件中用b來查詢，那么oracle會(huì)掃描所有的表和索引的分區(qū)，成本會(huì)比分區(qū)更高，此時(shí)可以考慮用b做全局分區(qū)索引

分區(qū)索引字典

DBA_PART_INDEXES   分區(qū)索引的概要統(tǒng)計(jì)信息，可以得知每個(gè)表上有哪些分區(qū)索引，分區(qū)索引的類新(local/global,)

Dba_ind_partitions    每個(gè)分區(qū)索引的分區(qū)級(jí)統(tǒng)計(jì)信息

Dba_indexesminusdba_part_indexes  可以得到每個(gè)表上有哪些非分區(qū)索引 

案例分析：

分區(qū)索引

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、全局非分區(qū)索引（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、全局分區(qū)索引（只能是前綴）

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

 分區(qū)表索引維護(hù)

全局索引維護(hù)：

當(dāng)對(duì)一個(gè)分區(qū)進(jìn)行維護(hù)時(shí)，全局索引都會(huì)受到影響，必須重建
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

局部分區(qū)維護(hù)（Local）：

當(dāng)對(duì)一個(gè)分區(qū)進(jìn)行維護(hù)時(shí)，local 索引，只是對(duì)應(yīng)的分區(qū)索引受到影響
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

 維護(hù)分區(qū)表時(shí)，更新全局索引

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

分區(qū)表和非分區(qū)表訪問對(duì)比

訪問分區(qū)表：
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

訪問非分區(qū)表：
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




向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手