Oracle 12C new features table partition with asynchronous Global index asynchronous maintenance (once add, truncate, drop, spilt, merge multiple partitions)

Experiment Preparation:
--Create an experiment table
CREATE TABLE P_andy
(ID number (), NAME varchar2 (40))
PARTITION by RANGE (ID)
(PARTITION p1 VALUES less THAN (10),
PARTITION P2 VALUES less THAN (20)
);
Table created.
--View the current table partition:
Sql>
Col table_name for A25
Col Partition_name for A25
Select Table_name,partition_name,partition_position,tablespace_name,high_value from user_tab_partitions where table _name= ' P_andy ';

table_name Partition_name partition_position Tablespace_name High_value
------------------------- ------------------------- ------------------ ------------------------------ ------------- -
P_andy P1 1 USERS 10
P_andy P2 2 USERS 20
一、一次 Add multiple partitions

Sql>

ALTER TABLE P_andy ADD
PARTITION P3 VALUES less THAN (30),
PARTITION P4 VALUES less THAN (40);
Table altered.
--View the partition after add
Sql>
Select Table_name,partition_name,partition_position,tablespace_name,high_value from user_tab_partitions where table _name= ' P_andy ';
table_name Partition_name partition_position Tablespace_name High_value
------------------------- ------------------------- ------------------ ------------------------------ -----------
P_andy P1 1 USERS 10
P_andy P2 2 USERS 20
P_andy P3 3 USERS 30
P_andy P4 4 USERS 40
--Inserting data
Sql>
Begin
For I in 1.. The Loop
INSERT into P_andy values (i, ' andyi ');
End Loop;
Commit
End
/
PL/SQL procedure successfully completed.

二、一次 truncate or drop multiple partitions

(This shows that only the truncate,drop can be used to replace the keyword.)
Mode one: Truncate without update index, if the table has a global index, then truncate partition will fail the global index.
--Create a global non-partitioned index
Sql> CREATE index idx_pandy_id on P_andy (ID);
Index created.
--View index name
Sql>
Col column_name for A40
SELECT index_name, column_name, descend from user_ind_columns WHERE table_name = ' p_andy ';
Index_name column_name DESC
------------------------- ---------------------------------------- ----
idx_pandy_id ID ASC
--View index status
Sql>
Col index_name for A25
Select Table_name,index_name,status,blevel,leaf_blocks from user_indexes where index_name = ' idx_pandy_id ';
table_name index_name STATUS Blevel leaf_blocks
------------------------- ------------------------- -------- ---------- -----------
P_andy idx_pandy_id VALID 0 1
--Truncate multiple partitions, without update index
sql> ALTER TABLE P_andy TRUNCATE partition P3,P4;
Table truncated.
--View index status
Sql> Select Table_name,index_name,status,blevel,leaf_blocks from user_indexes where index_name = ' IDX_PANDY_ID ';
table_name index_name STATUS Blevel leaf_blocks
------------------------- ------------------------- -------- ---------- -----------
P_andy idx_pandy_id unusable 0 1
Mode two: Truncate with update index, if the table has a global index, then truncate partition does not invalidate the global index.
--Inserting data
Sql>
Begin
For I in 20.. The Loop
INSERT into P_andy values (i, ' andyi ');
End Loop;
Commit
End
/
PL/SQL procedure successfully completed.
--View index status
Sql> Select Table_name,index_name,status,blevel,leaf_blocks from user_indexes where index_name = ' IDX_PANDY_ID ';
table_name index_name STATUS Blevel leaf_blocks
------------------------- ------------------------- -------- ---------- -----------
P_andy idx_pandy_id unusable 0 1
--Rebuilding the index
sql> ALTER index IDX_PANDY_ID rebuild;
Index altered.
--View index status
Sql> Select Table_name,index_name,status,blevel,leaf_blocks from user_indexes where index_name = ' IDX_PANDY_ID ';
table_name index_name STATUS Blevel leaf_blocks
------------------------- ------------------------- -------- ---------- -----------
P_andy idx_pandy_id VALID 0 1
--Truncate multiple partitions with update index
sql> ALTER TABLE p_andy TRUNCATE partition p3,p4 UPDATE GLOBAL INDEXES;
Table truncated.
Description
1, Oracle 12c can achieve asynchronous global index asynchronous maintenance function, after partition maintenance operations, such as DROP or TRUNCATE, is still the VALID state, index
does not expire, but the state of the index is contained OBSOLETE data, and when the maintenance operation is complete, the index state resumes.
2, 12c data dictionary dba/all/user_indexes OR dba/all/user_ind_partitions added column orphaned_entries, indicating whether the current global index is insured with expired entries (indexed records, The actual data in the table has been drop or truncate).
3, column orphaned_entries three values
There may be 3 values for this column:
? YES: The index has an orphaned (expired free) entry
? No: Orphaned (Expired free) entry does not exist for this index
? N/A: Not applicable types such as non-partitioned table indexes or local indexes
4, for the index exists orphaned, we can manually clear (method recommended two kinds)
Law one: ALTER INDEX xxx REBUILD;
Method two:sql> alter session force parallel DDL parallel 8; Use parallelism for manual maintenance, accelerating maintenance
EXEC dbms_part. Cleanup_gidx (' [SCHEMA] ', ' [TABLE NAME] ');
--View index status Orphaned_entries
Sql> Select Table_name,index_name,status,blevel,leaf_blocks,orphaned_entries from user_indexes where index_name = ' Idx_pandy_id ';
table_name index_name STATUS blevel leaf_blocks ORP
------------------------- ------------------------- -------- ---------- ----------- ---
P_andy idx_pandy_id VALID 0 1 YES
三、一次 Spilt multiple partitions
The newly enhanced split PARTITION statement in 12c allows you to split a specific partition or sub-partition into multiple new partitions using only a single command.
--Inserting data
Sql>
Begin
For I in 20.. The Loop
INSERT into P_andy values (i, ' andyi ');
End Loop;
Commit
End
/
--View partition status
Sql>
Select Table_name,partition_name,partition_position,tablespace_name,high_value from user_tab_partitions where table _name= ' P_andy ';
table_name Partition_name partition_position Tablespace_name High_value
------------------------- ------------------------- ------------------ ------------------------------ -----------
P_andy P1 1 USERS 10
P_andy P2 2 USERS 20
P_andy P3 3 USERS 30
P_andy P4 4 USERS 40
Sql>
ALTER TABLE P_andy SPLIT PARTITION P4 into
(PARTITION P5 VALUES less THAN (33),
PARTITION P6 VALUES less THAN ($), PARTITION P4) UPDATE GLOBAL INDEXES;
Table altered.
--View partition after split
Sql>
Select Table_name,partition_name,partition_position,tablespace_name,high_value from user_tab_partitions where table _name= ' P_andy ';
table_name Partition_name partition_position Tablespace_name High_value
------------------------- ------------------------- ------------------ ------------------------------ -------------
P_andy P1 1 USERS 10
P_andy P2 2 USERS 20
P_andy P3 3 USERS 30
P_andy P5 4 USERS 33
P_andy P6 5 USERS 36
P_andy P4 6 USERS 40
Iv. merging multiple partitions into one partition
--Table partitioning situation
Sql>
Select Table_name,partition_name,partition_position,tablespace_name,high_value from user_tab_partitions where table _name= ' P_andy ';
table_name Partition_name partition_position Tablespace_name High_value
------------------------- ------------------------- ------------------ ------------------------------ -------------
P_andy P1 1 USERS 10
P_andy P2 2 USERS 20
P_andy P3 3 USERS 30
P_andy P5 4 USERS 33
P_andy P6 5 USERS 36
P_andy P4 6 USERS 40
--Multiple partitions merged into one partition
sql> ALTER TABLE p_andy MERGE partitions p5,p6 into PARTITION P4 UPDATE GLOBAL INDEXES;
Ora-14012:resulting partition name conflicts with a existing partition
sql> ALTER TABLE p_andy MERGE partitions p5,p6,p4 into PARTITION p_merge UPDATE GLOBAL INDEXES;
Table altered.
--After you view merge merge partition, the partition condition
Sql> Select Table_name,partition_name,partition_position,tablespace_name,high_value from User_tab_partitions where table_name= ' P_andy ';
table_name Partition_name partition_position Tablespace_name High_value
------------------------- ------------------------- ------------------ ------------------------------ -------------
P_andy P1 1 USERS 10
P_andy P2 2 USERS 20
P_andy P3 3 USERS 30
P_andy P_merge 4 USERS 40

Oracle 12C new features table partition with asynchronous Global index asynchronous maintenance (once add, truncate, drop, spilt, merge multiple partitions)