Local local partition Index for Oracle

Environment: Oracle 12.2.0.1

Note: It is not determined if the 10g,11g has these features. Now basically not 10g, mainly with 12c,11g.

This feature is undoubtedly important for DBAs or implementers, especially if your database is primarily used in OLAP or DW (data Warehouse) environments.

Many times, if you need to create a new index on a huge table, such as this represents a base station performance data, there may be 3.5 billion of the entire table.

If you follow the previous operation, it is obviously a very scary thing:

• Consume a huge amount of time
• Not necessarily successful
• May affect the conduct of the business

Then, the local local partition index is different.

The following is my test!

1 Create TableT_tab_columns (2Ownervarchar2( -) not NULL, 3table_namevarchar2( -) not NULL, 4column_namevarchar2( -) not NULL, 5Data_typevarchar2( -), 6Data_type_modvarchar2(3),   7Data_type_ownervarchar2( -), 8Data_length Number,        9Data_precision Number,        TenData_scale Number,         OneNullablevarchar2(1),    Acolumn_id Number,         -Default_length Number,               -Num_distinct Number,         theCollationvarchar2( -)   - ) -Partition byList (owner) - ( +Partition P_sysValues('SYS','SYSTEM') , -Partition P_sys_otherValues('Mdsys','Ctxsys','Dvsys','Wmsys','Lbacsys') indexingoff, +Partition P_usersValues('LZF','Emcuser') A ); at  - --- - INSERT  intoT_tab_columns - Select  - owner, - table_name, in column_name, - Data_type, to Data_type_mod, + Data_type_owner, - Data_length, the Data_precision, * Data_scale, $ Nullable,Panax Notoginseng column_id, - Default_length, the num_distinct, + Collation A  fromDba_tab_columns the WHEREOWNERinch('SYS','SYSTEM','Mdsys','Ctxsys','Dvsys','Wmsys','Lbacsys','LZF','Emcuser'); + Commit; - ---2.1 Creating an index $ Create IndexIdx_tab_columns_colname ont_tab_columns (column_name) local indexing partial $ --Validation - SelectSegment_name,partition_name fromUser_segmentswhereSegment_name='Idx_tab_columns_colname'; - segment_name partition_name the idx_tab_columns_colname P_sys - idx_tab_columns_colname p_usersWuyi --* * * * Proof: There is a role the --2.2 Creating an index without indexing partial - Create IndexIdx_tab_columns_dt ont_tab_columns (data_type) local; Wu --Validation - SelectSegment_name,partition_name fromUser_segmentswhereSegment_name='Idx_tab_columns_dt'; About segment_name partition_name $ Idx_tab_columns_dt P_sys - Idx_tab_columns_dt P_sys_other - Idx_tab_columns_dt p_users - --* * * * proof: Creating an index is entirely dependent on the indexing partial sentence.  A --2.3 What happens if I change the index to be non-indexed?  + Alter TableT_tab_columns Modify Partition P_users indexingoff; the --Validation - SelectSegment_name,partition_name fromUser_segmentswhereSegment_name='Idx_tab_columns_colname'; $ segment_name partition_name the idx_tab_columns_colname P_sys the  the SelectSegment_name,partition_name fromUser_segmentswhereSegment_name='Idx_tab_columns_dt'; the segment_name partition_name - Idx_tab_columns_dt P_sys in Idx_tab_columns_dt P_sys_other the Idx_tab_columns_dt p_users the --* * * * Proof: Only the index with indexing partial is affected, and the index of the corresponding partition is deleted.  About  the --2.4 What happens if you change the index to be indexed?  the Alter TableT_tab_columns Modify Partition P_users indexing on; the --Validation + SelectSegment_name,partition_name fromUser_segmentswhereSegment_name='Idx_tab_columns_colname'; - segment_name partition_name the idx_tab_columns_colname P_sysBayi idx_tab_columns_colname p_users the SelectSegment_name,partition_name fromUser_segmentswhereSegment_name='Idx_tab_columns_dt'; the results slightly.  - --* * * * proof: Modifying the Indexing property of a table partition only affects the index that uses the indexing partial, rebuilding the corresponding partition's index. 

By modifying the indexing property of all partitions (off), then creating a local index of the indexing partial pair, and finally modifying the corresponding partition to indexing on with a timed task, you can incrementally create an index of each partition.

Local local partition Index for Oracle