Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.