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

How to understand ADD and DROP partition, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Add and drop Partition

Syntax:

ALTER TABLE t_pe_r

ADD PARTITION p4 VALUES LESS THAN (30)

ALTER TABLE t_pe_l

ADD PARTITION p4 VALUES (30)

ALTER TABLE t_pe_h

ADD PARTITION p3

Alter table t_pe_r drop partition p3

Limit: an error is reported if the range partition uses the maxvalue option ORA-14074: the partition limit must be adjusted higher than the last partition limit

Error ORA-14323 if default option is used for LIST partition: cannot add partition if DEFAULT partition already exists

Drop partition cannot be used in HASH partition table. Error ORA-14255: table is not partitioned by range, combined range, or list method

Here we discuss the status of add partition and drop partition on local index, global index and general index in HASH, list, range 3 mode.

Use script

Drop table t_pe_r

Drop table t_pe_l

Drop table t_pe_h

CREATE TABLE t_pe_r (I NUMBER, j NUMBER, f varchar2 (20), k varchar2 (20))

PARTITION BY RANGE (j)

(PARTITION p1 VALUES LESS THAN (10)

PARTITION p2 VALUES LESS THAN (20))

Create index t_pe_r_n on t_pe_r (I)

Create index t_pe_r_l on t_pe_r (j) local

Create index t_pe_r_g on t_pe_r (f)

GLOBAL PARTITION BY hash (f)

(partition pg1

Partition pg2)

CREATE TABLE t_pe_l (I NUMBER, j NUMBER, f varchar2 (20), k varchar2 (20))

PARTITION BY list (j)

(PARTITION p1 VALUES (10)

PARTITION p2 VALUES (20))

Create index t_pe_l_n on t_pe_l (I)

Create index t_pe_l_l on t_pe_l (j) local

Create index t_pe_l_g on t_pe_l (f)

GLOBAL PARTITION BY hash (f)

(partition pg1

Partition pg2)

CREATE TABLE t_pe_h (I NUMBER, j NUMBER, f varchar2 (20), k varchar2 (20))

PARTITION BY hash (j)

(PARTITION p1

PARTITION p2)

Create index t_pe_h_n on t_pe_h (I)

Create index t_pe_h_l on t_pe_h (j) local

Create index t_pe_h_g on t_pe_h (f)

GLOBAL PARTITION BY hash (f)

(partition pg1

Partition pg2)

Insert into t_pe_r

Values (2, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5,

Insert into t_pe_r

Values (1, 5, 15, 1, 5, 5, 5, 5, 5, 5, 5, 4, 4, 4, 4, 4, 4, 2, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 9, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 3, 4, 4, 4, 4, 4, 4, 4, 4, 6, 6,

Insert into t_pe_l

Values (2, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 2, 10, 10, 2, 10, 10, 2, 10, 10, 2, 10, 10, 2, 10, 10, 2, 10, 10, 2, 10, 10, 2, 10, 10, 2, 10, 10, 2, 10, 10, 2, 10, 10, 2, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10,

Insert into t_pe_l

Values (1, 2, 2, 5, 5, 5, 5, 5, 5, 5, 2, 4, 2, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6,

Insert into t_pe_h

Values (2, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5,

Insert into t_pe_h

Values (1, 5, 15, 1, 5, 5, 5, 5, 5, 5, 5, 4, 4, 4, 4, 4, 4, 2, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 9, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 3, 4, 4, 4, 4, 4, 4, 4, 4, 6, 6,

Insert into t_pe_h

Values (3pr 25pr)

Select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tasking pedicure ritual'

Select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tasking pedicure ritual'

Select INDEX_NAME, STATUS from dba_indexes where lower (index_name) ='t responsible for ringing'

Select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tasking pedicure lager'

Select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tasking peeled laggard'

Select INDEX_NAME, STATUS from dba_indexes where lower (index_name) = 'tasking peeled lumbago'

Select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tact peeled hourly'

Select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tcm peeled hogg'

Select INDEX_NAME, STATUS from dba_indexes where lower (index_name) = 'tasking pedicure hogn.'

Add the following

ALTER TABLE t_pe_r

ADD PARTITION p4 VALUES LESS THAN (30)

ALTER TABLE t_pe_l

ADD PARTITION p4 VALUES (30)

ALTER TABLE t_pe_h

ADD PARTITION p3

Then check the index status

SQL > select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tasking pedicure ritual'

INDEX_NAME STATUS PARTITION_NAME

-

T_PE_R_L USABLE P2

T_PE_R_L USABLE P1

T_PE_R_L USABLE P4

SQL > select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tasking pedicure ritual'

INDEX_NAME STATUS PARTITION_NAME

-

T_PE_R_G USABLE PG2

T_PE_R_G USABLE PG1

SQL > select INDEX_NAME, STATUS from dba_indexes where lower (index_name) = 'tasking pedicure ritual'

INDEX_NAME STATUS

T_PE_R_N VALID

SQL > select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tasking pedigree'

INDEX_NAME STATUS PARTITION_NAME

-

T_PE_L_L USABLE P2

T_PE_L_L USABLE P1

T_PE_L_L USABLE P4

SQL > select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tasking pediglingg'

INDEX_NAME STATUS PARTITION_NAME

-

T_PE_L_G USABLE PG2

T_PE_L_G USABLE PG1

SQL > select INDEX_NAME, STATUS from dba_indexes where lower (index_name) = 'tasking peeling lumbago'

INDEX_NAME STATUS

T_PE_L_N VALID

SQL > select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tasking peeling hourly'

INDEX_NAME STATUS PARTITION_NAME

-

T_PE_H_L USABLE P2

T_PE_H_L UNUSABLE P1

T_PE_H_L UNUSABLE P3

SQL > select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tasking peeling hog.'

INDEX_NAME STATUS PARTITION_NAME

-

T_PE_H_G UNUSABLE PG2

T_PE_H_G UNUSABLE PG1

SQL > select INDEX_NAME, STATUS from dba_indexes where lower (index_name) = 'tasking peeling hogn.'

INDEX_NAME STATUS

T_PE_H_N UNUSABLE

You can see that in fact, all indexes of LIST and HASH are not affected, while HASH partitions are different, all indexes are invalid, after adding partitions, rows are redistributed through the HASH algorithm, then ROWID should also be affected, you can DUMP to have a look.

Index for rebuild

Alter index T_PE_H_L rebuild partition p1

The best thing to do when joining a HASH partition is as follows:

ALTER TABLE t_pe_h

ADD PARTITION p3 update indexes; plus UPDATE INDEXES, and in fact, the number of HASH partitions should be 2 to the N power, otherwise the distribution will be uneven.

Then we test that DROP partition,drop partition can only be used for RANGE and LIST partitions, but not for HASH partitions:

You can drop partitions from range, list, or composite range-list partitioned tables. For hash-partitioned tables, or hash subpartitions of range-hash partitioned

Tables, you must perform. A coalesce operation instead.

Use scripts:

Alter table t_pe_r drop partition p1

Alter table t_pe_l drop partition p1

Then observe:

SQL > select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tasking pedicure ritual'

INDEX_NAME STATUS PARTITION_NAME

-

T_PE_R_L USABLE P2

T_PE_R_L USABLE P4

SQL > select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tasking pedicure ritual'

INDEX_NAME STATUS PARTITION_NAME

-

T_PE_R_G UNUSABLE PG2

T_PE_R_G UNUSABLE PG1

SQL > select INDEX_NAME, STATUS from dba_indexes where lower (index_name) = 'tasking pedicure ritual'

INDEX_NAME STATUS

T_PE_R_N UNUSABLE

SQL > select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tasking pedigree'

INDEX_NAME STATUS PARTITION_NAME

-

T_PE_L_L USABLE P2

T_PE_L_L USABLE P4

SQL > select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tasking pediglingg'

INDEX_NAME STATUS PARTITION_NAME

-

T_PE_L_G UNUSABLE PG2

T_PE_L_G UNUSABLE PG1

SQL > select INDEX_NAME, STATUS from dba_indexes where lower (index_name) = 'tasking peeling lumbago'

INDEX_NAME STATUS

T_PE_L_N UNUSABLE

You can see that the general index, the global index is invalid, but the local index is not affected.

What happens if we bring UPDATE INDEXES?

SQL > alter table t_pe_r drop partition p1 update indexes

Table altered

SQL > alter table t_pe_l drop partition p1 update indexes

Table altered

SQL >

SQL > select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tasking pedicure ritual'

INDEX_NAME STATUS PARTITION_NAME

-

T_PE_R_L USABLE P2

T_PE_R_L USABLE P4

SQL > select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tasking pedicure ritual'

INDEX_NAME STATUS PARTITION_NAME

-

T_PE_R_G USABLE PG2

T_PE_R_G USABLE PG1

SQL > select INDEX_NAME, STATUS from dba_indexes where lower (index_name) = 'tasking pedicure ritual'

INDEX_NAME STATUS

T_PE_R_N VALID

SQL > select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tasking pedigree'

INDEX_NAME STATUS PARTITION_NAME

-

T_PE_L_L USABLE P2

T_PE_L_L USABLE P4

SQL > select INDEX_NAME, STATUS,a.partition_name from dba_ind_partitions a where lower (index_name) = 'tasking pediglingg'

INDEX_NAME STATUS PARTITION_NAME

-

T_PE_L_G USABLE PG2

T_PE_L_G USABLE PG1

SQL > select INDEX_NAME, STATUS from dba_indexes where lower (index_name) = 'tasking peeling lumbago'

INDEX_NAME STATUS

T_PE_L_N VALID

You can see that adding UPDATE INDEXES automatically rebuilds the invalid index.

Conclusion:

1. If the range partition uses the maxvalue option, the error ORA-14074: the partition limit must be adjusted higher than the last partition limit.

2. If the default option is used in the LIST partition, the error ORA-14323: cannot add the partition when the DEFAULT partition already exists.

3. Drop partition cannot be used in HASH partition table. Error ORA-14255: the table is not partitioned by range, combination range or list method. If you want to reduce the partition in an HASH partition table, you need to use ALTER TABLE. COALESCE PARTITION

4. HASH partitions perform ADD PARTITION operations. General indexes, local indexes and global indexes all fail unless UPDATE INDEXES is used, but LIST and RANGE partitions are not affected.

5. LIST,RANGE partition for DROP PARTITION operation global index and ordinary index will be invalid, but LOCAL index will not be affected. Unless you use UPDATE INDEXES.

6. If you want to add a partition to the global index, this operation is only valid for the global HASH partition. ORA-14640: the add / merge index partition operation is only valid for the global index of the hash partition, but the partition of the DROP global index is valid for HASH\ LIST\ RANGE.

The answer to the question on how to understand ADD and DROP zoning is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.