Analysis of incomplete Index of oracle 12c Partition Table
This article mainly explains "incomplete Index Analysis of oracle 12c Partition Table". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn "oracle 12c partition table incomplete index analysis" it!
Experiment one
Experimental preparation
Create table part1
(id int, code int,name varchar2)
Indexing off
Partition by range (id)
(partition p1 values less than (1000)
Partition p2 values less than (2000)
Partition p3 values less than (3000) indexing on
);
MING@ming (MING) > col partition_name for A30
MING@ming (MING) > select PARTITION_NAME,indexing from dba_tab_partitions where table_owner='MING' AND TABLE_NAME='PART1'
PARTITION_NAME INDE
-
P1 OFF
P2 OFF
P3 ON
Create an index
MING@ming (MING) > create index code_part1_global on part1 (code) global indexing partial
Index created.
MING@ming (MING) > create index id_part1_partial on part1 (id) local indexing partial
Index created.
Index status
MING@ming (MING) > COL INDEX_NAME FOR A30
MING@ming (MING) > select index_name,staTUS from user_indexes where table_name='PART1'
INDEX_NAME STATUS
CODE_PART1_GLOBAL VALID
ID_PART1_PARTIAL N/A
MING@ming (MING) > SELECT PARTITION_NAME, INDEX_NAME,STATUS FROM USER_IND_PARTITIONS WHERE INDEX_NAME='ID_PART1_PARTIAL'
PARTITION_NAME INDEX_NAME STATUS
-
P1 ID_PART1_PARTIAL UNUSABLE
P2 ID_PART1_PARTIAL UNUSABLE
P3 ID_PART1_PARTIAL USABLE
P2 partitioned ID_PART1_PARTIAL index is unusable, rebuild this index
MING@ming (MING) > alter index ID_PART1_PARTIAL rebuild partition p2 parallel 2 online
Index altered.
MING@ming (MING) > col partition_name for A30
MING@ming (MING) > SELECT PARTITION_NAME, INDEX_NAME,STATUS FROM USER_IND_PARTITIONS WHERE INDEX_NAME='ID_PART1_PARTIAL'
PARTITION_NAME INDEX_NAME STATUS
-
P1 ID_PART1_PARTIAL UNUSABLE
P2 ID_PART1_PARTIAL USABLE
P3 ID_PART1_PARTIAL USABLE
MING@ming (MING) > select PARTITION_NAME,indexing from dba_tab_partitions where table_owner='MING' AND TABLE_NAME='PART1'
PARTITION_NAME INDE
-
P1 OFF
P2 OFF
P3 ON
The rebuild partition method is used to rebuild the index of a partition.
Previous experiments have shown that modifying the indexing property changes the state of the index accordingly; through the above experiment, we can rebuild the index only for a partition, and modifying the state of the index does not change the indexing property.
Of course, you can also change the index to unusable when indexing is on.
MING@ming (MING) > alter index ID_PART1_PARTIAL modify partition p3 unusable
Index altered.
Experiment two
Research on the state modification behavior of index when modifying indexing attribute
Delete the ID_PART1_PARTIAL index and rebuild, so the P2 partition is UNUSABLE.
P2 Partition data Open transaction
MING@ming (MING) > update part1 set name='yy' where id=1500
2 rows updated.
Modify indexing attribute for new session session
MING@ming (MING) > alter table part1 modify partition p2 indexing on
Alter table part1 modify partition p2 indexing on
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
This means that the index on the modified partition indexing is not rebuilt in the way of online, and the production environment will fail if there are frequent DML transactions. At this time, you can adopt the method in the above experiment, only for the index, change the state to usable, and then find the appropriate time to modify the indexing property.
MING@ming (MING) > alter index ID_PART1_PARTIAL rebuild partition p2 online
Index altered.
For the 10046 event of alter table part1 modify partition p2 indexing on, the partial recursive sql is as follows:
LOCK TABLE "PART1" PARTITION ("P2") IN EXCLUSIVE MODE NOWAIT
Alter index "MING". "CODE_PART1_GLOBAL" coalesce cleanup
Insert into index_orphaned_entry$ (indexobj#, tabpartdobj#, hidden) values (: 1, 2,: 3)
Insert / * + RELATIONAL ("PART1") NO_PARALLEL APPEND NESTED_TABLE_SET_SETID NO_REF_CASCADE * / into "MING". "PART1" pa
Rtition ("P2") select / * + RELATIONAL ("PART1") NO_PARALLEL * / * from "MING". "PART1" partition ("P2") insert not u
Nique partial global indexes
Delete from index_orphaned_entry$ where indexobj#=:1
You can see that when you modify the indexing property, you get an exclusive lock, which is why you modify the indexing error when there is an active transaction.
Experiment 3
Can spaced partitions also use incomplete indexes?
Create an interval partition table
MING@ming (MING) > create table day_part (id number,eitime date)
2 indexing off
3 partition by range (eitime)
4 interval (numtodsinterval (3 recorder daylight))
5 (
6 partition p1 values less than (to_date ('2000-01-01-01 / 01 / 01 / 01 / 01 / 01 / 01 / 01)
7)
Table created.
Created successfully!
Insert data and create an index
MING@ming (MING) > insert into day_part values (1Magne sysdate)
MING@ming (MING) > insert into day_part values (2MagneSysdate)
MING@ming (MING) > insert into day_part values (2m sysdatekeeper 5)
MING@ming (MING) > insert into day_part values (2m sysdatekeeper 10)
MING@ming (MING) > commit
MING@ming (MING) > create index id_day_part on day_part (id) local indexing partial
Index created.
Query
MING@ming (MING) > col PARTITION_NAME for A30
MING@ming (MING) > col INDEX_NAME for A30
MING@ming (MING) > SELECT PARTITION_NAME, INDEX_NAME,STATUS FROM USER_IND_PARTITIONS WHERE INDEX_NAME='ID_DAY_PART'
PARTITION_NAME INDEX_NAME STATUS
-
P1 ID_DAY_PART USABLE
SYS_P420 ID_DAY_PART USABLE
SYS_P421 ID_DAY_PART USABLE
SYS_P422 ID_DAY_PART USABLE
MING@ming (MING) > alter table DAY_PART modify partition SYS_P420 indexing off
Table altered.
It is not shown here, but incomplete indexes are also available for spaced partitioned tables.
At this point, I believe that everyone on the "oracle 12c partition table incomplete index analysis" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!
