2023年11月26日发(作者：)

Oracle11g：合并分区表-mergepartition

Merging Partitions

使⽤ALTER TABLE .. MERGE PARTITION语句将两个分区的内容合并到另外⼀个分区，两个源分区和关联的local index都会被drop

不能⽤于 hash分区表或 hash subpartitions of a composite *-hash partitioned table

不能⽤于合并引⽤分区表(reference-partitioned table)

合并范围分区

允许合并两个临近范围的分区到另外分区，不相邻分区⽆法合并。合并结果分区继承两个源分区的最⼤边界。

demo:

-- Create a Table with four partitions each on its own tablespace

-- Partitioned by range on the data column.

CREATE TABLE four_seasons

(

one DATE,

two VARCHAR2(60),

three NUMBER

)

PARTITION BY RANGE ( one )

(

PARTITION quarter_one

VALUES LESS THAN ( TO_DATE('01-apr-1998','dd-mon-yyyy'))

TABLESPACE quarter_one,

PARTITION quarter_two

VALUES LESS THAN ( TO_DATE('01-jul-1998','dd-mon-yyyy'))

TABLESPACE quarter_two,

PARTITION quarter_three

VALUES LESS THAN ( TO_DATE('01-oct-1998','dd-mon-yyyy'))

TABLESPACE quarter_three,

PARTITION quarter_four

VALUES LESS THAN ( TO_DATE('01-jan-1999','dd-mon-yyyy'))

TABLESPACE quarter_four

);

-- Create local PREFIXED index on Four_Seasons

-- Prefixed because the leftmost columns of the index match the

-- Partitioning key CREATE INDEX i_four_seasons_l ON four_seasons ( one,two )

LOCAL (

PARTITION i_quarter_one TABLESPACE i_quarter_one,

PARTITION i_quarter_two TABLESPACE i_quarter_two,

PARTITION i_quarter_three TABLESPACE i_quarter_three,

PARTITION i_quarter_four TABLESPACE i_quarter_four

);

下⼀步，合并分区.

-- Merge the first two partitions

--

ALTER TABLE four_seasons

-- Rebuild index for quarter_two, which has been marked unusable

-- because it has not had all of the data from Q1 added to it.

-- Rebuilding the index will correct this.

--

ALTER TABLE four_seasons MODIFY PARTITION

quarter_two REBUILD UNUSABLE LOCAL INDEXES;

合并Interval Partitions