SLTechnology News&Howtos › Database ›

The global index of Oracle partitioned tables will not fail when adding partitions

Shulou Source: shulou.com Published: 2022-06-01 06:57:47 10月03日 Update

COALESCE PARTITION | SUBPARTITION (hash)

DROP PARTITION | SUBPARTITION

EXCHANGE PARTITION | SUBPARTITION

MERGE PARTITION | SUBPARTITION

MOVE PARTITION | SUBPARTITION

SPLIT PARTITION | SUBPARTITION

TRUNCATE PARTITION | SUBPARTITION

All of the above will cause the global index to fail.

The following actions will cause the local index to fail.

MERGE PARTITION

MOVE PARTITION

SPLIT PARTITION

COALESCE PARTITION

EXCHANGE PARTITION

Interval partition and time partition are tested here. Adding partition global and local partition will not fail. Deleting partition will cause global index to fail.

Create a test table

Create table FACT_MONTH1

(

Stat_date DATE

Name VARCHAR2 (6)

)

PARTITION BY RANGE (stat_date)

INTERVAL (NUMTOYMINTERVAL (1)

(

PARTITION P1 VALUES LESS THAN (TO_DATE ('2014-1-1,' YYYY-MM-DD'))

);

Insert test data

Insert into FACT_MONTH1TIME values (TO_DATE ('2013-11-1,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-1-1),' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-1-15,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-2-15,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-3-15,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-4-15,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-5-15,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-6-15,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-7-15,' YYYY-MM-DD'), 'ABCDE')

Commit

View all kinds of information

Select * from FACT_MONTH1

Select * from user_tab_partitions

Select * from user_part_tables

Select * from user_ind_partitions

Select * from user_part_indexes

Create an index

Create index fa_idx_g on FACT_MONTH1 (stat_date)

Create index fa_idx_local on FACT_MONTH1 (name) local

View index information

Select * from user_tab_partitions

Select * from user_part_tables

Select * from user_ind_partitions

Select * from user_part_indexes

Select * from user_indexes

Add Partition:

Insert into FACT_MONTH1 values (TO_DATE ('2014-9-15,' YYYY-MM-DD'), 'ABCDE')

Commit

Check to see if the index is available:

Alter session set statistics_level=all

Select / * + index (FA_IDX_G) * / * from FACT_MONTH1 where stat_date=TO_DATE ('2014-9-15,' YYYY-MM-DD')

Select * from table (dbms_xplan.display_cursor (null,null,'allstats last'))

Time partition test

CREATE TABLE FACT_MONTH1TIME

(

Stat_date DATE

Name VARCHAR2 (6)

)

PARTITION BY RANGE (stat_date)

(

PARTITION p1 VALUES LESS THAN (TO_DATE ('2014-1-1,' yyyy-mm-dd'))

PARTITION p2 VALUES LESS THAN (TO_DATE ('2014-2-1,' yyyy-mm-dd'))

PARTITION p3 VALUES LESS THAN (TO_DATE ('2014-3-1,' yyyy-mm-dd'))

Partition p4 values less than (maxvalue)