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

分区表索引

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:28SYS@test1>createindexpart_id_indonpart_t1(object_id)local;Indexcreated.11:49:23SYS@test1>selectindex_name,partitioning_type,partition_countfromuser_part_indexes11:49:532whereindex_name='PART_ID_IND';INDEX_NAMEPARTITIONPARTITION_COUNT------------------------------------------------------PART_ID_INDRANGE511:53:55SYS@test1>selectindex_name,PARTITION_NAME,STATUS,TABLESPACE_NAMEfromuser_ind_partitions11:54:472whereindex_name='PART_ID_IND';INDEX_NAMEPARTITION_NAMESTATUSTABLESPACE_NAME--------------------------------------------------------------------------------------------------PART_ID_INDP1USABLETBS1PART_ID_INDP2USABLETBS2PART_ID_INDP3USABLETBS3PART_ID_INDP4USABLESYSTEMPART_ID_INDP5USABLESYSTEM11:54:59SYS@test1>selecttable_name,PARTITION_NAME,PARTITION_POSITION,TABLESPACE_NAMEfromdba_tab_partitions11:55:412wheretable_name='PART_T1';TABLE_NAMEPARTITION_NAMEPARTITION_POSITIONTABLESPACE_NAME------------------------------------------------------------------------------------------------------------PART_T1P11TBS1PART_T1P22TBS2PART_T1P33TBS3PART_T1P44SYSTEMPART_T1P55SYSTEM11:56:18SYS@test1>altertablepart_t1movepartitionp4tablespacetbs4;Tablealtered.11:56:29SYS@test1>altertablepart_t1movepartitionp5tablespacetbs4;Tablealtered.11:56:43SYS@test1>selecttable_name,PARTITION_NAME,PARTITION_POSITION,TABLESPACE_NAMEfromdba_tab_partitions11:56:512wheretable_name='PART_T1';TABLE_NAMEPARTITION_NAMEPARTITION_POSITIONTABLESPACE_NAME------------------------------------------------------------------------------------------------------------PART_T1P11TBS1PART_T1P22TBS2PART_T1P33TBS3PART_T1P44TBS4PART_T1P55TBS411:56:55SYS@test1>selectindex_name,PARTITION_NAME,STATUS,TABLESPACE_NAMEfromuser_ind_partitions11:57:042whereindex_name='PART_ID_IND';INDEX_NAMEPARTITION_NAMESTATUSTABLESPACE_NAME--------------------------------------------------------------------------------------------------PART_ID_INDP1USABLETBS1PART_ID_INDP2USABLETBS2PART_ID_INDP3USABLETBS3PART_ID_INDP4UNUSABLESYSTEMPART_ID_INDP5UNUSABLESYSTEM11:58:31SYS@test1>alterindexPART_ID_INDrebuildpartitionp4online;Indexaltered.12:03:52SYS@test1>selectindex_name,PARTITION_NAME,STATUS,TABLESPACE_NAMEfromuser_ind_partitions12:03:592whereindex_name='PART_ID_IND';INDEX_NAMEPARTITION_NAMESTATUSTABLESPACE_NAME--------------------------------------------------------------------------------------------------PART_ID_INDP1USABLETBS1PART_ID_INDP2USABLETBS2PART_ID_INDP3USABLETBS3PART_ID_INDP4USABLESYSTEMPART_ID_INDP5UNUSABLESYSTEM12:04:08SYS@test1>alterindexPART_ID_INDrebuildpartitionp4onlinetablespacetbs4;Indexaltered.12:04:22SYS@test1>alterindexPART_ID_INDrebuildpartitionp5onlinetablespacetbs4;Indexaltered.12:04:33SYS@test1>selectindex_name,PARTITION_NAME,STATUS,TABLESPACE_NAMEfromuser_ind_partitions12:04:392whereindex_name='PART_ID_IND';INDEX_NAMEPARTITION_NAMESTATUSTABLESPACE_NAME--------------------------------------------------------------------------------------------------PART_ID_INDP1USABLETBS1PART_ID_INDP2USABLETBS2PART_ID_INDP3USABLETBS3PART_ID_INDP4USABLETBS4PART_ID_INDP5USABLETBS4

2、局部非前缀索引

13:26:27SYS@test1>createindexpart_name_indonpart_t1(object_name)local;Indexcreated.13:27:13SYS@test1>selectindex_name,PARTITION_NAME,STATUS,TABLESPACE_NAMEfromuser_ind_partitions13:27:232whereindex_name='PART_NAME_IND';INDEX_NAMEPARTITION_NAMESTATUSTABLESPACE_NAME--------------------------------------------------------------------------------------------------PART_NAME_INDP1USABLETBS1PART_NAME_INDP2USABLETBS2PART_NAME_INDP3USABLETBS3PART_NAME_INDP4USABLETBS4PART_NAME_INDP5USABLETBS413:29:00SYS@test1>select*frompart_t1whereobject_name='EMP';OWNER------------------------------OBJECT_NAME------------------------------------------------------------------------------------------------------------------------OBJECT_IDOBJECT_TYPETIMESTAMPSTATUS-------------------------------------------------------SCOTTEMP14741TABLE2013-11-18:15:07:49VALIDElapsed:00:00:00.01ExecutionPlan----------------------------------------------------------Planhashvalue:2894019794--------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Pstart|Pstop|--------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|123|7(0)|00:00:01|||1|PARTITIONRANGEALL||1|123|7(0)|00:00:01|15||2|TABLEACCESSBYLOCALINDEXROWID|PART_T1|1|123|7(0)|00:00:01|15||*3|INDEXRANGESCAN|PART_NAME_IND|1||6(0)|00:00:01|15|--------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------3-access("OBJECT_NAME"='EMP')Note------dynamicsamplingusedforthisstatement(level=2)Statistics----------------------------------------------------------0recursivecalls0dbblockgets12consistentgets0physicalreads0redosize779bytessentviaSQL*Nettoclient419bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessed13:29:07SYS@test1>13:29:07SYS@test1>SELECT*FROMPART_T1whereobject_id=14741;OWNER------------------------------OBJECT_NAME------------------------------------------------------------------------------------------------------------------------OBJECT_IDOBJECT_TYPETIMESTAMPSTATUS-------------------------------------------------------SCOTTEMP14741TABLE2013-11-18:15:07:49VALIDElapsed:00:00:00.00ExecutionPlan----------------------------------------------------------Planhashvalue:3145656835------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Pstart|Pstop|------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|123|2(0)|00:00:01||||1|PARTITIONRANGESINGLE||1|123|2(0)|00:00:01|5|5||2|TABLEACCESSBYLOCALINDEXROWID|PART_T1|1|123|2(0)|00:00:01|5|5||*3|INDEXRANGESCAN|PART_ID_IND|1||1(0)|00:00:01|5|5|------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------3-access("OBJECT_ID"=14741)Note------dynamicsamplingusedforthisstatement(level=2)Statistics----------------------------------------------------------0recursivecalls0dbblockgets3consistentgets0physicalreads0redosize779bytessentviaSQL*Nettoclient419bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessed

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

13:37:50SYS@test1>createindexpart_name_gindonpart_t1(object_name)global；13:37:54SYS@test1>select*frompart_t1whereobject_name='EMP';OWNER------------------------------OBJECT_NAME------------------------------------------------------------------------------------------------------------------------OBJECT_IDOBJECT_TYPETIMESTAMPSTATUS-------------------------------------------------------SCOTTEMP14741TABLE2013-11-18:15:07:49VALIDElapsed:00:00:00.00ExecutionPlan----------------------------------------------------------Planhashvalue:2017751627---------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Pstart|Pstop|---------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|123|2(0)|00:00:01||||1|TABLEACCESSBYGLOBALINDEXROWID|PART_T1|1|123|2(0)|00:00:01|ROWID|ROWID||*2|INDEXRANGESCAN|PART_NAME_GIND|1||1(0)|00:00:01|||---------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-access("OBJECT_NAME"='EMP')Note------dynamicsamplingusedforthisstatement(level=2)Statistics----------------------------------------------------------44recursivecalls0dbblockgets77consistentgets1physicalreads0redosize783bytessentviaSQL*Nettoclient419bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessed13:38:04SYS@test1>/OWNER------------------------------OBJECT_NAME------------------------------------------------------------------------------------------------------------------------OBJECT_IDOBJECT_TYPETIMESTAMPSTATUS-------------------------------------------------------SCOTTEMP14741TABLE2013-11-18:15:07:49VALIDElapsed:00:00:00.00ExecutionPlan----------------------------------------------------------Planhashvalue:2017751627---------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Pstart|Pstop|---------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|123|2(0)|00:00:01||||1|TABLEACCESSBYGLOBALINDEXROWID|PART_T1|1|123|2(0)|00:00:01|ROWID|ROWID||*2|INDEXRANGESCAN|PART_NAME_GIND|1||1(0)|00:00:01|||---------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-access("OBJECT_NAME"='EMP')Note------dynamicsamplingusedforthisstatement(level=2)Statistics----------------------------------------------------------0recursivecalls0dbblockgets4consistentgets0physicalreads0redosize783bytessentviaSQL*Nettoclient419bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessed13:38:24SYS@test1>13:40:01SYS@test1>selectindex_name,PARTITION_NAME,STATUS,TABLESPACE_NAMEfromuser_ind_partitions13:40:032whereindex_name='PART_NAME_GIND';norowsselected13:40:47SYS@test1>SELECTINDEX_NAME,TABLESPACE_NAME,INDEX_TYPEFROMUSER_INDEXES13:41:022whereindex_name='PART_NAME_GIND';INDEX_NAMETABLESPACE_NAMEINDEX_TYPE---------------------------------------------------------------------------------------PART_NAME_GINDINDXNORMAL

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

13:43:36SYS@test1>createindexpart_name_gindonpart_t1(object_name)global13:44:152partitionbyhash(object_name)13:44:193partitions413:44:234storein(tbs1,tbs2,tbs3,tbs4);Indexcreated.13:44:38SYS@test1>selectindex_name,PARTITION_NAME,STATUS,TABLESPACE_NAMEfromuser_ind_partitions13:45:312whereindex_name='PART_NAME_GIND';INDEX_NAMEPARTITION_NAMESTATUSTABLESPACE_NAME--------------------------------------------------------------------------------------------------PART_NAME_GINDSYS_P61USABLETBS1PART_NAME_GINDSYS_P62USABLETBS2PART_NAME_GINDSYS_P63USABLETBS3PART_NAME_GINDSYS_P64USABLETBS413:45:41SYS@test1>setautotraceon13:47:12SYS@test1>select*frompart_t1whereobject_name='EMP';OWNER------------------------------OBJECT_NAME------------------------------------------------------------------------------------------------------------------------OBJECT_IDOBJECT_TYPETIMESTAMPSTATUS-------------------------------------------------------SCOTTEMP14741TABLE2013-11-18:15:07:49VALIDElapsed:00:00:00.00ExecutionPlan----------------------------------------------------------Planhashvalue:2733506516----------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Pstart|Pstop|----------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|123|2(0)|00:00:01||||1|PARTITIONHASHSINGLE||1|123|2(0)|00:00:01|1|1||2|TABLEACCESSBYGLOBALINDEXROWID|PART_T1|1|123|2(0)|00:00:01|ROWID|ROWID||*3|INDEXRANGESCAN|PART_NAME_GIND|1||1(0)|00:00:01|1|1|----------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------3-access("OBJECT_NAME"='EMP')Note------dynamicsamplingusedforthisstatement(level=2)Statistics----------------------------------------------------------44recursivecalls0dbblockgets80consistentgets1physicalreads0redosize779bytessentviaSQL*Nettoclient419bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessed13:47:29SYS@test1>/OWNER------------------------------OBJECT_NAME------------------------------------------------------------------------------------------------------------------------OBJECT_IDOBJECT_TYPETIMESTAMPSTATUS-------------------------------------------------------SCOTTEMP14741TABLE2013-11-18:15:07:49VALIDElapsed:00:00:00.00ExecutionPlan----------------------------------------------------------Planhashvalue:2733506516----------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Pstart|Pstop|----------------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|123|2(0)|00:00:01||||1|PARTITIONHASHSINGLE||1|123|2(0)|00:00:01|1|1||2|TABLEACCESSBYGLOBALINDEXROWID|PART_T1|1|123|2(0)|00:00:01|ROWID|ROWID||*3|INDEXRANGESCAN|PART_NAME_GIND|1||1(0)|00:00:01|1|1|----------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------3-access("OBJECT_NAME"='EMP')Note------dynamicsamplingusedforthisstatement(level=2)Statistics----------------------------------------------------------0recursivecalls0dbblockgets4consistentgets0physicalreads0redosize779bytessentviaSQL*Nettoclient419bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessed

分区表索引维护

全局索引维护：

当对一个分区进行维护时，全局索引都会受到影响，必须重建13:50:18SYS@test1>altertablepart_t1movepartitionp1tablespacetbs2;13:51:17SYS@test1>selectindex_name,PARTITION_NAME,STATUS,TABLESPACE_NAMEfromuser_ind_partitions13:51:232whereindex_name='PART_NAME_GIND'13:51:283/INDEX_NAMEPARTITION_NAMESTATUSTABLESPACE_NAME--------------------------------------------------------------------------------------------------PART_NAME_GINDSYS_P61UNUSABLETBS1PART_NAME_GINDSYS_P62UNUSABLETBS2PART_NAME_GINDSYS_P63UNUSABLETBS3PART_NAME_GINDSYS_P64UNUSABLETBS413:51:31SYS@test1>altertablepart_t1movepartitionp1tablespacetbs1;Tablealtered.13:52:30SYS@test1>selectindex_name,PARTITION_NAME,STATUS,TABLESPACE_NAMEfromuser_ind_partitions13:52:352whereindex_name='PART_NAME_GIND';INDEX_NAMEPARTITION_NAMESTATUSTABLESPACE_NAME--------------------------------------------------------------------------------------------------PART_NAME_GINDSYS_P61UNUSABLETBS1PART_NAME_GINDSYS_P62UNUSABLETBS2PART_NAME_GINDSYS_P63UNUSABLETBS3PART_NAME_GINDSYS_P64UNUSABLETBS4Elapsed:00:00:00.0013:52:40SYS@test1>13:52:40SYS@test1>alterindexPART_NAME_GINDrebuild;alterindexPART_NAME_GINDrebuild*ERRORatline1:ORA-14086:apartitionedindexmaynotberebuiltasawholeElapsed:00:00:00.0313:53:31SYS@test1>alterindexPART_NAME_GINDrebuildpartitionsys_p61;Indexaltered.Elapsed:00:00:00.9513:53:53SYS@test1>alterindexPART_NAME_GINDrebuildpartitionsys_p62;Indexaltered.Elapsed:00:00:00.4213:54:01SYS@test1>alterindexPART_NAME_GINDrebuildpartitionsys_p63;Indexaltered.Elapsed:00:00:00.4913:54:07SYS@test1>alterindexPART_NAME_GINDrebuildpartitionsys_p64;Indexaltered.Elapsed:00:00:00.4213:54:11SYS@test1>selectindex_name,PARTITION_NAME,STATUS,TABLESPACE_NAMEfromuser_ind_partitions13:54:182whereindex_name='PART_NAME_GIND';INDEX_NAMEPARTITION_NAMESTATUSTABLESPACE_NAME--------------------------------------------------------------------------------------------------PART_NAME_GINDSYS_P61USABLETBS1PART_NAME_GINDSYS_P62USABLETBS2PART_NAME_GINDSYS_P63USABLETBS3PART_NAME_GINDSYS_P64USABLETBS4Elapsed:00:00:00.00

局部分区维护（Local）：

当对一个分区进行维护时，local索引，只是对应的分区索引受到影响13:56:08SYS@test1>selectindex_name,PARTITION_NAME,STATUS,TABLESPACE_NAMEfromuser_ind_partitions13:56:172whereindex_name='PART_ID_IND';INDEX_NAMEPARTITION_NAMESTATUSTABLESPACE_NAME--------------------------------------------------------------------------------------------------PART_ID_INDP1UNUSABLETBS1PART_ID_INDP2USABLETBS2PART_ID_INDP3USABLETBS3PART_ID_INDP4USABLETBS4PART_ID_INDP5USABLETBS413:56:35SYS@test1>ALTERINDEXPART_ID_INDrebuildpartitionp1;Indexaltered.Elapsed:00:00:00.5313:56:59SYS@test1>selectindex_name,PARTITION_NAME,STATUS,TABLESPACE_NAMEfromuser_ind_partitions13:57:042whereindex_name='PART_ID_IND';INDEX_NAMEPARTITION_NAMESTATUSTABLESPACE_NAME--------------------------------------------------------------------------------------------------PART_ID_INDP1USABLETBS1PART_ID_INDP2USABLETBS2PART_ID_INDP3USABLETBS3PART_ID_INDP4USABLETBS4PART_ID_INDP5USABLETBS4

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

14:04:25SYS@test1>selectindex_name,PARTITION_NAME,STATUS,TABLESPACE_NAMEfromuser_ind_partitions14:04:392whereindex_name='PART_NAME_GIND';INDEX_NAMEPARTITION_NAMESTATUSTABLESPACE_NAME--------------------------------------------------------------------------------------------------PART_NAME_GINDSYS_P61USABLETBS1PART_NAME_GINDSYS_P62USABLETBS2PART_NAME_GINDSYS_P63USABLETBS3PART_NAME_GINDSYS_P64USABLETBS4Elapsed:00:00:00.0014:04:46SYS@test1>altertablepart_t1movepartitionp1tablespacetbs1updateglobalindexes;Tablealtered.Elapsed:00:00:00.8014:05:04SYS@test1>selectindex_name,PARTITION_NAME,STATUS,TABLESPACE_NAMEfromuser_ind_partitions14:05:092whereindex_name='PART_NAME_GIND';INDEX_NAMEPARTITION_NAMESTATUSTABLESPACE_NAME--------------------------------------------------------------------------------------------------PART_NAME_GINDSYS_P61USABLETBS1PART_NAME_GINDSYS_P62USABLETBS2PART_NAME_GINDSYS_P63USABLETBS3PART_NAME_GINDSYS_P64USABLETBS4

local 索引 需要手工rebuild

14:06:25SYS@test1>alterindexPART_ID_INDrebuildpartitionp1;Indexaltered.Elapsed:00:00:00.9014:06:42SYS@test1>selectindex_name,PARTITION_NAME,STATUS,TABLESPACE_NAMEfromuser_ind_partitions14:06:462whereindex_name='PART_ID_IND';INDEX_NAMEPARTITION_NAMESTATUSTABLESPACE_NAME--------------------------------------------------------------------------------------------------PART_ID_INDP1USABLETBS1PART_ID_INDP2USABLETBS2PART_ID_INDP3USABLETBS3PART_ID_INDP4USABLETBS4PART_ID_INDP5USABLETBS4

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

访问分区表：sql>createtablepart_t2PARTITIONBYRANGE(object_id)(partitionp1valueslessthan(4000)tablespacetbs1,partitionp2valueslessthan(8000)tablespacetbs2,partitionp3valueslessthan(12000)tablespacetbs3,partitionp4valueslessthan(maxvalue)tablespacetbs4)asselectowner,object_name,object_id,object_type,TIMESTAMP,statusfromdba_objects;12:47:40SYS@test1>setautotractrace12:48:49SYS@test1>select*frompart_t2whereobject_id<4000;3931rowsselected.Elapsed:00:00:00.04ExecutionPlan----------------------------------------------------------Planhashvalue:1733007488--------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Pstart|Pstop|--------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||3931|472K|12(0)|00:00:01||||1|PARTITIONRANGESINGLE||3931|472K|12(0)|00:00:01|1|1||2|TABLEACCESSFULL|PART_T2|3931|472K|12(0)|00:00:01|1|1|--------------------------------------------------------------------------------------------------Note------dynamicsamplingusedforthisstatement(level=2)Statistics----------------------------------------------------------132recursivecalls0dbblockgets361consistentgets6physicalreads0redosize192675bytessentviaSQL*Nettoclient3301bytesreceivedviaSQL*Netfromclient264SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)3931rowsprocessed访问非分区表：12:50:29SYS@test1>setautotracetrace12:51:06SYS@test1>select*fromdba_objectswhereobject_id<4000;3931rowsselected.Elapsed:00:00:00.09ExecutionPlan----------------------------------------------------------Planhashvalue:1409114634----------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|----------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||3099|626K|49(3)|00:00:01||1|VIEW|DBA_OBJECTS|3099|626K|49(3)|00:00:01||2|UNION-ALL|||||||*3|TABLEACCESSBYINDEXROWID|SUM$|1|26|0(0)|00:00:01||*4|INDEXUNIQUESCAN|I_SUM$_1|1||0(0)|00:00:01||5|TABLEACCESSBYINDEXROWID|OBJ$|1|24|3(0)|00:00:01||*6|INDEXRANGESCAN|I_OBJ1|1||2(0)|00:00:01||*7|FILTER|||||||*8|HASHJOIN||3486|391K|49(3)|00:00:01||9|TABLEACCESSFULL|USER$|41|697|3(0)|00:00:01||*10|HASHJOIN||3486|333K|46(3)|00:00:01||11|INDEXFULLSCAN|I_USER2|41|861|1(0)|00:00:01||*12|TABLEACCESSFULL|OBJ$|3486|262K|44(0)|00:00:01||*13|TABLEACCESSBYINDEXROWID|IND$|1|8|2(0)|00:00:01||*14|INDEXUNIQUESCAN|I_IND1|1||1(0)|00:00:01||15|NESTEDLOOPS||1|28|2(0)|00:00:01||*16|INDEXFULLSCAN|I_USER2|1|19|1(0)|00:00:01||*17|INDEXRANGESCAN|I_OBJ4|1|9|1(0)|00:00:01||*18|FILTER|||||||19|NESTEDLOOPS||1|105|3(0)|00:00:01||20|TABLEACCESSFULL|LINK$|1|88|2(0)|00:00:01||21|TABLEACCESSCLUSTER|USER$|1|17|1(0)|00:00:01||*22|INDEXUNIQUESCAN|I_USER#|1||0(0)|00:00:01|----------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------3-filter(BITAND("S"."XPFLAGS",8388608)=8388608)4-access("S"."OBJ#"=:B1)6-access("EO"."OBJ#"=:B1)7-filter(("O"."TYPE#"<>1AND"O"."TYPE#"<>10OR"O"."TYPE#"=1AND(SELECT1FROM"SYS"."IND$""I"WHERE"I"."OBJ#"=:B1AND("I"."TYPE#"=1OR"I"."TYPE#"=2OR"I"."TYPE#"=3OR"I"."TYPE#"=4OR"I"."TYPE#"=6OR"I"."TYPE#"=7OR"I"."TYPE#"=9))=1)AND("O"."TYPE#"<>4AND"O"."TYPE#"<>5AND"O"."TYPE#"<>7AND"O"."TYPE#"<>8AND"O"."TYPE#"<>9AND"O"."TYPE#"<>10AND"O"."TYPE#"<>11AND"O"."TYPE#"<>12AND"O"."TYPE#"<>13AND"O"."TYPE#"<>14AND"O"."TYPE#"<>22AND"O"."TYPE#"<>87AND"O"."TYPE#"<>88ORBITAND("U"."SPARE1",16)=0OR("O"."TYPE#"=4OR"O"."TYPE#"=5OR"O"."TYPE#"=7OR"O"."TYPE#"=8OR"O"."TYPE#"=9OR"O"."TYPE#"=10OR"O"."TYPE#"=11OR"O"."TYPE#"=12OR"O"."TYPE#"=13OR"O"."TYPE#"=14OR"O"."TYPE#"=22OR"O"."TYPE#"=87)AND(SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE'AND"U"."TYPE#"<>2OR"U"."TYPE#"=2AND"U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))OREXISTS(SELECT0FROMSYS."USER$""U2",SYS."OBJ$""O2"WHERE"O2"."OWNER#"="U2"."USER#"AND"O2"."TYPE#"=88AND"O2"."DATAOBJ#"=:B2AND"U2"."TYPE#"=2AND"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#"<4000AND"O"."NAME"<>'_NEXT_OBJECT'AND"O"."NAME"<>'_default_auditing_options_'AND"O"."LINKNAME"ISNULLANDBITAND("O"."FLAGS",128)=0)13-filter("I"."TYPE#"=1OR"I"."TYPE#"=2OR"I"."TYPE#"=3OR"I"."TYPE#"=4OR"I"."TYPE#"=6OR"I"."TYPE#"=7OR"I"."TYPE#"=9)14-access("I"."OBJ#"=:B1)16-access("U2"."TYPE#"=2AND"U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))filter("U2"."TYPE#"=2AND"U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))17-access("O2"."DATAOBJ#"=:B1AND"O2"."TYPE#"=88AND"O2"."OWNER#"="U2"."USER#")18-filter(NULLISNOTNULL)22-access("L"."OWNER#"="U"."USER#")Statistics----------------------------------------------------------38recursivecalls0dbblockgets670consistentgets3physicalreads0redosize228642bytessentviaSQL*Nettoclient3301bytesreceivedviaSQL*Netfromclient264SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)3931rowsprocessed12:51:26SYS@test1>/3931rowsselected.Elapsed:00:00:00.07ExecutionPlan----------------------------------------------------------Planhashvalue:1409114634----------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|----------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||3099|626K|49(3)|00:00:01||1|VIEW|DBA_OBJECTS|3099|626K|49(3)|00:00:01||2|UNION-ALL|||||||*3|TABLEACCESSBYINDEXROWID|SUM$|1|26|0(0)|00:00:01||*4|INDEXUNIQUESCAN|I_SUM$_1|1||0(0)|00:00:01||5|TABLEACCESSBYINDEXROWID|OBJ$|1|24|3(0)|00:00:01||*6|INDEXRANGESCAN|I_OBJ1|1||2(0)|00:00:01||*7|FILTER|||||||*8|HASHJOIN||3486|391K|49(3)|00:00:01||9|TABLEACCESSFULL|USER$|41|697|3(0)|00:00:01||*10|HASHJOIN||3486|333K|46(3)|00:00:01||11|INDEXFULLSCAN|I_USER2|41|861|1(0)|00:00:01||*12|TABLEACCESSFULL|OBJ$|3486|262K|44(0)|00:00:01||*13|TABLEACCESSBYINDEXROWID|IND$|1|8|2(0)|00:00:01||*14|INDEXUNIQUESCAN|I_IND1|1||1(0)|00:00:01||15|NESTEDLOOPS||1|28|2(0)|00:00:01||*16|INDEXFULLSCAN|I_USER2|1|19|1(0)|00:00:01||*17|INDEXRANGESCAN|I_OBJ4|1|9|1(0)|00:00:01||*18|FILTER|||||||19|NESTEDLOOPS||1|105|3(0)|00:00:01||20|TABLEACCESSFULL|LINK$|1|88|2(0)|00:00:01||21|TABLEACCESSCLUSTER|USER$|1|17|1(0)|00:00:01||*22|INDEXUNIQUESCAN|I_USER#|1||0(0)|00:00:01|----------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------3-filter(BITAND("S"."XPFLAGS",8388608)=8388608)4-access("S"."OBJ#"=:B1)6-access("EO"."OBJ#"=:B1)7-filter(("O"."TYPE#"<>1AND"O"."TYPE#"<>10OR"O"."TYPE#"=1AND(SELECT1FROM"SYS"."IND$""I"WHERE"I"."OBJ#"=:B1AND("I"."TYPE#"=1OR"I"."TYPE#"=2OR"I"."TYPE#"=3OR"I"."TYPE#"=4OR"I"."TYPE#"=6OR"I"."TYPE#"=7OR"I"."TYPE#"=9))=1)AND("O"."TYPE#"<>4AND"O"."TYPE#"<>5AND"O"."TYPE#"<>7AND"O"."TYPE#"<>8AND"O"."TYPE#"<>9AND"O"."TYPE#"<>10AND"O"."TYPE#"<>11AND"O"."TYPE#"<>12AND"O"."TYPE#"<>13AND"O"."TYPE#"<>14AND"O"."TYPE#"<>22AND"O"."TYPE#"<>87AND"O"."TYPE#"<>88ORBITAND("U"."SPARE1",16)=0OR("O"."TYPE#"=4OR"O"."TYPE#"=5OR"O"."TYPE#"=7OR"O"."TYPE#"=8OR"O"."TYPE#"=9OR"O"."TYPE#"=10OR"O"."TYPE#"=11OR"O"."TYPE#"=12OR"O"."TYPE#"=13OR"O"."TYPE#"=14OR"O"."TYPE#"=22OR"O"."TYPE#"=87)AND(SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE'AND"U"."TYPE#"<>2OR"U"."TYPE#"=2AND"U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))OREXISTS(SELECT0FROMSYS."USER$""U2",SYS."OBJ$""O2"WHERE"O2"."OWNER#"="U2"."USER#"AND"O2"."TYPE#"=88AND"O2"."DATAOBJ#"=:B2AND"U2"."TYPE#"=2AND"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#"<4000AND"O"."NAME"<>'_NEXT_OBJECT'AND"O"."NAME"<>'_default_auditing_options_'AND"O"."LINKNAME"ISNULLANDBITAND("O"."FLAGS",128)=0)13-filter("I"."TYPE#"=1OR"I"."TYPE#"=2OR"I"."TYPE#"=3OR"I"."TYPE#"=4OR"I"."TYPE#"=6OR"I"."TYPE#"=7OR"I"."TYPE#"=9)14-access("I"."OBJ#"=:B1)16-access("U2"."TYPE#"=2AND"U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))filter("U2"."TYPE#"=2AND"U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))17-access("O2"."DATAOBJ#"=:B1AND"O2"."TYPE#"=88AND"O2"."OWNER#"="U2"."USER#")18-filter(NULLISNOTNULL)22-access("L"."OWNER#"="U"."USER#")Statistics----------------------------------------------------------0recursivecalls0dbblockgets663consistentgets0physicalreads0redosize228642bytessentviaSQL*Nettoclient3301bytesreceivedviaSQL*Netfromclient264SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)3931rowsprocessed