Range partition table creation with large number of paritions

Question

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?

Answer 1

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;

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 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 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 indicated in a comment an workaround for point one:

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

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.

Answer 2

You didn't say what version of Oracle you are using, but if it happens that your Oracle version is 11g then there is INTERVAL clause of CREATE TABLE statement that will allow you to establish interval partitioning for a table. Here is an example:

SQL> Create table TB_PART_TEST(
  2    id number
  3  )
  4  partition by range(id)
  5  interval(1000)
  6  (
  7    partition Part_01 values less than (1000)
  8  );  

 table created

 SQL> select partition_name
   2       , high_value
   3    from user_tab_partitions
   4   where table_name = 'TB_PART_TEST';



PARTITION_NAME  HIGH_VALUE
 ------------------------------
  PART_01           1000 


  SQL> insert into TB_PART_TEST(id)
    2    values(1500); 

  1 row created

  SQL> commit;

  commit complete

  SQL> select partition_name
   2       , high_value
   3    from user_tab_partitions
   4   where table_name = 'TB_PART_TEST';

  PARTITION_NAME  HIGH_VALUE
 ------------------------------
  PART_01           1000 
  SYS_P63           2000