溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
通過案例學調優之--分區表索引
分區表索引
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:
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.
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.
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.
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 processed3、全局非分區索引（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 NORMAL4、全局分區索引（只能是前綴）
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免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
