Partition index note (4) -- when the partition index will expire and be processed

Partition index note (4) -- when the partition index will expire and process the partition index note (3) -- global partition Index http://www.bkjia.com/database/201308/238740.html Local indexes in partitioned tables are automatically maintained when oracle operates table partitions. Note that global indexes involve the following operations when the alter table operation is performed on the global index table, will cause the index to become invalid and need to be re-created: Ø add partition | SUBPARTITION Ø partial PARTITION | SUBPARTITION Ø drop partition | SUBPARTITION Ø exchange partition | Ø merge partition | MOVE Ø move partition | Ø split partition | SUBPARTITION Ø partial PARTITION | partial therefore, we recommend that you attach the upda In the te indexes clause, oracle automatically maintains the global index. Of course, you must note that there is a balance between this. You need to balance the ddl operation time and the time to re-create the index, to determine whether to add the updateindexes clause. --------------------------------------- Conclusion: ---- No matter global index or local index, if data is moved, the index or partition index will fail. 1. When alter table add partition is executed, the update indexes clause is not specified:. if it is a range/list partition, its local index and global index will not be affected; B. if it is a hash partition, the local index and global index of the newly added partition and the partition with data movement will be set to unuseable and need to be re-compiled. 2. If the update indexes clause is not specified during the execution of drop partition, the glocal index will fail. For local indexes, the corresponding index partition will be deleted at the same time when the partition is deleted, the local index of its partition is not affected. 3. When executing split partition/subpartition, if the update indexes clause is not specified, local and global indexes will be invalidated. However, if you split partition/subpartition in an empty partition or do not trigger any data movement or change, the index will not be affected even if you do not add update indexes. For the sake of insurance, we recommend that you query the data dictionary and check the status of the current index. ---------------------------------------------------------------------- The following is a simple test: 1. CREATE a Range Partition TABLE: create table dfms. TEST04 partition by range (OBJECT_ID) (PARTITION P1 values less than (2000) TABLESPACE LOG_DATA, PARTITION P2 values less than (8000) TABLESPACE LOG_DATA, PARTITION P3 values less than (20000) TABLESPACE LOG_DATA, PARTITION P4 values less than (40000) TABLESPACE LOG_DATA, PARTITION P Max values less than (MAXVALUE) TABLESPACE LOG_DATA) as select * FROM DBA_OBJECTS; 2. create a primary key and generate global index: alter table DFMS. TEST04 add constraint pk_id primary key (object_id); CREATE a local index: create index dfms. IDX1_TEST04 on dfms. TEST04 (OBJECT_NAME) LOCAL; 3. we can view the status of global index in the dba_indexes view and find that valid: select index_name, status, last_analyzed, partitioned from dba_indexes where index_na Me = 'pk _ id'; the local index of the local index is in the N/A State through dba_indexes. You can see that each index partition is in the USABLE state through dba_ind_partitions. DBA_PART_INDEXES shows the overall status of the local partition index. Select * from dba_ind_partitions where index_name = 'idx1 _ TEST04 '; select * from dba_indexes where index_name = 'idx1 _ TEST04'; select * from DBA_PART_INDEXES where index_name = 'idx1 _ TEST04 '; 4. because maxvalue exists, we first test the impact of split on global and local indexes. 4.1 alter table test04 split partition pmax at (80000) into (partition p5 tablespace log_data, partition pmax tablespace log_data) when data exists in the new partition ); we can see from the table script that pmax is divided into p5 and p Max :.... PARTITION P5 values less than (80000), partition pmax values less than (MAXVALUE );..... apparently, because the number of rows in select max (object_id) from TEST04 is 101769, the rows in the old split partition that meet less than 80000 are left in the first partition p5, the second partition (New pmax partition) exists in other partitions ). We can query the status of global index and local index: select index_name, status, last_analyzed, partitioned from dba_indexes where index_name = 'pk _ id'; data movement is obviously triggered here, the global index status changes to UNUSABLE. select * from dba_ind_partitions where index_name = 'idx1 _ TEST04 '; because the new split partition (here, p5) contains data, the data in the original pmax is split into the new partition p5 and the new pmax, and the local indexes corresponding to p1, p2, p3, and p4 are still USABLE, the new p5 and the local indexes corresponding to the new pmax are all UNUSABLE. OK. We will rebuild the global index and the local partition index corresponding to p5 and pmax: al Ter index PK_ID rebuild online; then the query finds that global index becomes valid: select index_name, status, last_analyzed, partitioned from dba_indexes where index_name = 'pk _ id '; alter index idx0000test04 rebuild partition p5 online; alter index idx0000test04 rebuild partition pmax online; query after execution: select * from dba_ind_partitions where index_name = 'idx1 _ TEST04 '; we can see that the statuses of the two index partitions p5 and pmax change to USABLE. 4.2 The number of rows in the new partition without data TEST04 is 101. 769, then we set the new partition p6 to 110000, so the pmax partition obviously has no data. (The new partition contains data from all PMAX partitions.) alter table test04 split partition pmax at (110000) into ---- note that a new partition is added, however, the data is not moved (partition p6 tablespace log_data, partition pmax tablespace log_data); check global index and local index to see that the global index and each local index partition are USABLE, this is because data movement is not triggered. Select index_name, status, last_analyzed, partitioned from dba_indexes where index_name = 'pk _ id'; select * from dba_ind_partitions where index_name = 'idx1 _ TEST04 '; Remarks: when you split a pmax partition, the new partition name can start at Will (not necessarily including pmax). For example, the preceding partition name can be p6 or p7, but p6 will follow the less than 110000, the second partition p7 is still less than maxvalue. in addition, the tablespace remains unchanged. If the non-empty partition storage attribute is different from the original storage attribute, data will also be moved, leading to index failure. 5. test the impact of drop partition on global and local indexes. Delete the last non-data pmax partition in Table test04. Alter table test04 drop partition pmax; because the deleted partition does not have data, it does not involve data changes, so it does not affect the global and local data. If we want to delete the part with data, neither the partition nor the data are retained, the local index will not be affected and the global index will become invalid. Alter table test04 drop partition p6; query select * from dba_indexes where index_name = 'pk _ id'; the global index fails and the status changes to UNUSABLE. select * from dba_ind_partitions where index_name = 'idx1 _ TEST04 '; the local index (other partitions) status remains unchanged and is USABLE. 6. test the effect of add partition on global and local indexes. Alter table test04 add partition p6 values less than (120000); query status: select * from dba_indexes where index_name = 'pk _ id '; select * from dba_ind_partitions where index_name = 'idx1 _ TEST04 '; it is found that adding partitions to Range partitions does not affect global and local indexes. Similarly, if you test list partitions, you can know that adding partitions does not affect global and local indexes. This is mainly because data movement is not triggered. 6.1 for Hash partitions, because add parittion performs an I/O operation to balance the data distribution and move the data, both the local partition index and global index are set to UNUSABLE and need to be rebuilt. the following is a simple test: create table dfms. TEST05 partition by hash (OBJECT_ID) PARTITIONS 8 store in (LOG_DATA) as select * FROM DBA_OBJECTS; add global and local index. alter table DFMS. TEST05 add constraint pk_test05_id primary key (object_id); create index dfms. idx0000test05 on dfms. TEST05 (OBJECT_NAME) LOCAL; Add new partition: alter table Test05 add partition; query select * from dba_indexes where index_name = 'pk _ TEST05_ID '; select * from dba_ind_partitions where index_name = 'idx1 _ TEST05'; the global index is UNUSABLE, the index of the first and last partitions in the local partition index is UNUSABLE, and the other is USABLE. apparently, because the data is split from the first partition to the new hasn partition, the data in the two partitions is moved, leading to the failure of the local partition index because of the movement of data rows, of course, global index also becomes invalid ). SQL> select index_owner, index_name, STATUS from dba_ind_partitions where Index_name = 'idx1 _ TEST05 '; INDEX_OWNER INDEX_NAME STATUS has reached -------- SYS idx0000test05 unusable sys idx0000test05 usable sys idx0000test05 UNUSABLE 7. test the impact of truncate partition on global and local indexes. Truncate partition truncates data from the header just like the truncate table. If the update indexes clause is not specified, truncate partition also invalidates the global index of the table where the partition is located. Syntax is very simple: alter table tbname truncate partition/subpartition ptname; alter table test04 truncate partition p6; query select * from dba_indexes where index_name = 'pk _ id '; select * from dba_ind_partitions where index_name = 'idx1 _ TEST04 '; the global index fails and the local partition index status is USABLE. 8. other operations such as Merge Partitions, Exchange Partitions, and coalesce partitions are rarely used. tests are not performed here. In short, if data is moved, the index must be noted.