具有大量分区的范围分区表创建 [英] Range partition table creation with large number of paritions

问题描述

我必须创建一个包含200个分区的范围分区表.例如:

i have to create an range partitioned table with two hundred partitions. for eg:

CREATE TABLE emp (
     empno NUMBER(4), 
     ename VARCHAR2(30), 
     sal   NUMBER
) 
PARTITION BY RANGE(empno) (
partition e1 values less than (1000)     , 
partition e2 values less than (2000)     , 
...
partition e200 values less than (MAXVALUE) 

);

有没有一种方法可以指定范围间隔，而不必只为指定范围而写两百行?

Is there a way to specify the range interval without writing two hundred lines for just specifing the range?

推荐答案

CREATE TABLE emp (
     empno NUMBER(4), 
     ename VARCHAR2(30), 
     sal   NUMBER
) 
PARTITION BY RANGE(empno) (
partition e1 values less than (1000));

begin
  for k in 2..200 
  loop
     execute immediate 
       'alter table emp add partition e'||k||' values less than  ('||k*1000||')';
  end loop
end;

更新:11g中存在一项功能，用于指定范围分区的间隔，当您插入表时将创建分区.

UPDATE: In 11g exists a feature to specify an interval for range partitions and partitions will be created when you insert into the table.

但是我不喜欢它，也不推荐它有两个原因:

But I don't like it and I don't recommend it for two reasons:

1您应该始终保留第一个分区，因为这是参考.如果尝试将其删除，则会显示SQL Error: ORA-14758: Last partition in the range section cannot be dropped;

1 You should allways keep the first partition, because is the reference. If you try to drop it you'll get SQL Error: ORA-14758: Last partition in the range section cannot be dropped;

2您无法控制分区名称(AFAIK)和间隔(这很丑).如果错误地在将来插入一个值，则某些分区将被跳过，并且会出现胖分区: (研究了一小撮，没有脂肪分区.添加到示例中.)

2 You don't have control on partition names(AFAIK) and interval(this is ugly). If, by mistake you insert a value in future some partitions will be skipped and you'll get fat partitions: (studied a litle and there is no fat partition. Added to example.)

 Create table Z_TB_PART_TEST(
    id number
  )
  partition by range(id)
  interval(1000)
 (
    PARTITION PART_01 VALUES LESS THAN (1000)
 );  

 INSERT INTO Z_TB_PART_TEST values (1500);
 INSERT INTO Z_TB_PART_TEST VALUES (10000);
 INSERT INTO Z_TB_PART_TEST VALUES (5000);


  SELECT partition_name , high_value
  FROM USER_TAB_PARTITIONS
  WHERE table_name = 'Z_TB_PART_TEST';

  PART_01   1000
  SYS_P141  2000
  SYS_P142  11000
  SYS_P143  6000

UPDATE2 :Nicholas Krasnov在评论中指出了第一点的解决方法:

UPDATE2: Nicholas Krasnov indicated in a comment an workaround for point one:

ORA-14758呢?可以很容易避免: 我们暂时将间隔分区表转换为范围 分区表(alter table tb_table_test set interval())，删除 分区，然后切换回间隔分区表 (alter table tb_part_test set interval(1000)).

What about ORA-14758? It can be easily avoided: We temporarily convert our interval partitioning table to the range partitioning table (alter table tb_table_test set interval()), drop partition and then switch back to the interval partitioning table (alter table tb_part_test set interval(1000)).

它有效，我已经测试过了.但是应注意，所有分区都将冻结，它们将是范围分区.如果有间隙，将保留(不会在间隙中添加任何分区).因此，参考分区将是更改为interval之前的最后一个分区.这就是错误说明:Last partition in the range section cannot be dropped.

It works, I've tested it. However should be noticed that all partitions will freeze, they will be range partitions. If you had gaps will remain(no partition will be added in gaps). So, the reference partition will be the last partition before altering to interval. This is what the error says: Last partition in the range section cannot be dropped.

因此，您将拥有一个范围分区部分和一个区间分区部分，并具有所有优点.

So, you'll have a section of range partitioning and a section of Interval partitioning with all its benefits.

这篇关于具有大量分区的范围分区表创建的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！