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

This article shows you how to merge partition coalesce partition, the content is concise and easy to understand, can definitely brighten your eyes, through the detailed introduction of this article, I hope you can get something.

Merge partitions (coalesce partition): merge partitions are used to manipulate HASH partition tables and hash global indexes. He will reassign the deleted partition's data to your existing partition.

Restrictions: operate HASH partitioned tables and hash global indexes. If you merge LIST/RANGE partitioned tables and report errors ORA-14259: the table is not partitioned by the hash method

Global indexes are available only for RANGE and HASH, and RANGE cannot use merging, nor can it add partitions to range partitions and report errors ORA-14640: add / merge index partitions are only valid for global indexes with hash partitions

And the establishment of a global RANGE partition must have the MAXVALUE attribute, and it is a MAXVALUE partition that cannot be deleted. If you delete the error ORA-14078: you cannot delete the highest partition of the GLOBAL index.

We discuss the impact on indexes here, including global indexes, local indexes, and general indexes.

Use scripts:

Drop table t_pe_h

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

PARTITION BY hash (j)

(PARTITION p1

PARTITION p2

Partition p4

Partition p3)

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 range (f)

(PARTITION p1 VALUES less than (10)

PARTITION p2 VALUES less than (20)

PARTITION p3 VALUES less than (maxvalue))

Insert into t_pe_h

Values (2, 5, 5, 5, 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,

Insert into t_pe_h

Values (1, 15, 10, 5, 5, 10, 5, 5, 10, 5, 10, 10, 10, 10, 10, 10, 10, 15, 15, 10, B').

Insert into t_pe_h

Values (3, 25, 40, C')

Insert into t_pe_h

Values (2, 5, 5, 30, 7, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 6, 5, 5, 5, 5, 5, 5, 6, 5, 4, 4, 4, 4, 4, 4, 4, 6, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5, 6, 5,

Insert into t_pe_h

Values (1, 7, 5, 20, 7, 7, 7, 5, 5, 5, 2, 4, 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 (3, 85, 50, 5, 7, 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,

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.'

Then check the index status

SQL > ALTER TABLE t_pe_h

2 COALESCE PARTITION

Table altered

SQL >

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 P4

T_PE_H_L UNUSABLE P2

T_PE_H_L USABLE P1

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 P3

T_PE_H_G UNUSABLE P2

T_PE_H_G UNUSABLE P1

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

INDEX_NAME STATUS

T_PE_H_N UNUSABLE

It can be found that all of them are invalid, the obvious merge partition automatically merges P2 and P3 into P2, and the P2 partition related to the local index is also invalid because the ROWID of the underlying table has changed.

Both normal and global indexes are invalid.

Then let's try UPDATE INDEXES.

SQL > ALTER TABLE t_pe_h

2 COALESCE PARTITION update indexes

Table altered

SQL >

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 P4

T_PE_H_L USABLE P2

T_PE_H_L USABLE P1

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 USABLE P3

T_PE_H_G USABLE P2

T_PE_H_G USABLE P1

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

INDEX_NAME STATUS

T_PE_H_N VALID

It can be seen that all of them are valid and there is no problem. In addition, you can also use merge partitions to merge global HASH indexes, as follows:

ALTER INDEX hgidx COALESCE PARTITION; (valid for hash global partitioned index)

The above content is how to merge the partition coalesce partition, have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.