Shulou(Shulou.com)06/01 Report--

In the previous article, we learned about the methods of converting oracle plain tables into partitioned tables. In the next article, we will explore the failure and reconstruction of oracle partitioned indexes, and provide relevant code examples for your reference, as shown below.

-- create a test table SQL > create table t as select object_id,object_name from dba_objects

The table has been created.

SQL > select min (object_id), max (object_id) from t MIN (OBJECT_ID) MAX (OBJECT_ID)-2 76083SQL > create table t_part (object_id int,object_name varchar2 (1000)) partition by range (object_id) 2 (3 partition p1 values less than (10000), 4 partition p2 values less than (20000), 5 partition p3 values less than (30000), 6 partition p4 values less than (40000), 7 partition pm values less than (maxvalue))

The table has been created.

SQL > insert into t_part select * from t

72663 rows were created.

SQL > commit;-- create local partition index SQL > create index idx_part_local on t_part (object_name) local

The index has been created.

Create a global non-partitioned index

SQL > create index idx_part_global on t_part (object_id) global

The index has been created.

Delete one of the partitions

SQL > alter table t_part drop partition p1

The table has changed.

The global non-partitioned index fails, and the local partitioned index does not fail

SQL > select status,index_name from user_indexes s where index_name='IDX_PART_GLOBAL';STATUS INDEX_NAME- UNUSABLE IDX_PART_GLOBALSQL > select status,index_name from user_ind_partitions s where index_name='IDX_PART_LOCAL' STATUS INDEX_NAME- USABLE IDX_PART_LOCALUSABLE IDX_PART_LOCAL-- rebuild invalid index SQL > alter index idx_part_global rebuild

The index has changed.

When you delete a table partition, you can rebuild the index with the following command

Alter table t_part drop partition p2 update indexes

Create a global partitioned index

SQL > drop index idx_part_global

The index was deleted.

SQL > CREATE INDEX idx_part_global_full ON t_part (object_id) 2 GLOBAL PARTITION BY RANGE (object_id) 3 (PARTITION p1 VALUES LESS THAN (10000), 4 PARTITION p2 VALUES LESS THAN (30000), 5 PARTITION p3 VALUES LESS THAN (MAXVALUE))

The index has been created.

-- delete one of the partitions SQL > alter table t_part drop partition p3

The table has changed.

-- Global partitioned index invalidation SQL > select status,index_name from user_ind_partitions s where index_name='IDX_PART_GLOBAL_FULL' STATUS INDEX_NAME- UNUSABLE IDX_PART_GLOBAL_FULLUNUSABLE IDX_PART_GLOBAL_FULLUNUSABLE IDX_PART_GLOBAL_FULLSQL > select / * + index (t IDX_PART_LOCAL) * / * from t_part t where object_name ='/ 7f6c264csubscription IIOPAddresses' OBJECT_ID OBJECT_NAME- 35031 / 7f6c264c_IIOPAddress 35030 / 7f6c264c_IIOPAddressSQL > select / * + index (t IDX_PART_GLOBAL_FULL) * / * from t_part t where object_id > 35000

Select / * + index (t IDX_PART_GLOBAL_FULL) * / * from t_part t where object_id > 35000

*

An error occurred on line 1:

ORA-01502: index 'SCOTT.IDX_PART_GLOBAL_FULL' or the partition of such an index is unavailable

When you need to do the following on the partitioned table, it will cause the global index to fail.

ADD (HASH) COALESCE (HASH) DROP EXCHANGE MERGE MOVE SPLIT TRUNCATE

After that, you need to rebuild the invalid index, or you can specify UPDATE INDEXES to rebuild the index directly when you delete the partitioned table.

Summary

The above is the whole content of this article on the invalidation of the oracle partition index and the reconstruction code example. I hope it will be helpful to you. Interested friends can continue to refer to this site: oracle database startup phase analysis, oracle virtual private database detailed introduction, etc., if you have any questions, you can leave a message directly, the editor will reply you in time. Thank you for your support to this site!

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.