From Range Partition to Range-Interval

Question

I would like to move from Range Partition to Range-Interval, but my current table has a partition on MAXVALUE and the column used for partitioning allows null values :(

E.g.: Say we have:

create table a (b number)
partition by range (b) (
  PARTITION p0 VALUES LESS THAN (10),
  PARTITION p1 VALUES LESS THAN (50),
  PARTITION p2 VALUES LESS THAN (MAXVALUE)
);

Then we populate:

INSERT INTO a(b) VALUES (1);
INSERT INTO a(b) VALUES (11);
INSERT INTO a(b) VALUES (51);
INSERT INTO a(b) VALUES (null);

To be able to move to interval, we need to remove the partition with MAXVALUE, therefore, other values should be moved to a new partition.
51 is not a problem, I would create partition where with VALUES LESS than 100, but what about NULL ones?

I was thinking about changing to something like partition by range (NVL(b,0)), but I am scared about that having to reprocess the whole table (not possible, real table has a lot of data).

Any idea?

Vincent Malgrat · Accepted Answer · 2017-12-15T08:30:45.097

You can't have NULL in the partition key column of an interval partitioned table (as of 12.1):

Restrictions on Interval Partitioning

You can specify only one partitioning key column, and it must be of NUMBER, DATE, FLOAT, or TIMESTAMP data type.

[...]

You cannot specify NULL values for the partitioning key column.

You can't use expressions either for the partitioning key. However as suggested by @Shoelace you can use a virtual column (containing your expression) as a partition column:

SQL> CREATE TABLE a (b NUMBER, comput_b NUMBER AS (NVL(b, 0)))
  2  PARTITION BY RANGE (comput_b) (
  3    PARTITION p0 VALUES LESS THAN (0),
  4    PARTITION p1 VALUES LESS THAN (50),
  5    PARTITION p2 VALUES LESS THAN (MAXVALUE)
  6  );

Table created

SQL> INSERT INTO a(b) VALUES (1);
1 row inserted
SQL> INSERT INTO a(b) VALUES (11);
1 row inserted
SQL> INSERT INTO a(b) VALUES (51);
1 row inserted
SQL> INSERT INTO a(b) VALUES (null);
1 row inserted

SQL> SELECT * FROM a;

         B   COMPUT_B
---------- ----------
         1          1
        11         11
                    0
        51         51

In this particular case I think you would need a table rebuild.

thats not exactly true. since at least 11g you can create a virtual column (which is just an expression) and use that to partition by... — ShoeLace, Dec 15 '17 at 02:46

score 0 · Answer 2 · answered Dec 15 '17 at 02:59

http://www.dba-oracle.com/t_interval_partitioning.htm says you can change range to interval and back again by using the alter table syntax

ie

alter table a set INTERVAL(100) ;

the full alter table syntax for 11g is available here.

unfortunately that does not seem to allow you to change partition columns .. so i think you are out of luck. but you can always try.

From Range Partition to Range-Interval

2 Answers2