通过案例学调优之--分区表索引分区表索引Just like partitioned tables, partitioned indexes improve manageability, availabili
通过案例学调优之--分区表索引
分区表索引
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 TBS42、局部非前缀索引
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 TBS4local 索引 需要手工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--结束END--
本文标题: 通过案例学调优之--分区表索引
本文链接: https://www.lsjlt.com/news/44588.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑，方便收藏和打印～
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0