>  기사  >  데이터 베이스  >  Oracle 11g: 合并分区表-merge partition

Oracle 11g: 合并分区表-merge partition

WBOY
WBOY원래의
2016-06-07 17:28:041332검색

使用ALTER TABLE .. MERGE PARTITION语句将两个分区的内容合并到另外一个分区,两个源分区和关联的local index都会被drop,不能用

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
MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two
UPDATE INDEXES;

如果不显式声明 UPDATE INDEXES 语句,就必须给受影响的分区 rebuild the local index.

-- 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
 
同RANGE一样,必须两个相邻分区才能合并
合并 List partitions则没有限制

linux

성명:
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.