From Range Range to Range-Interval

Question

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

For example: Let's 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 fill in:

 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 go to the interval, we need to delete the section with MAXVALUE , so the other values ​​must be transferred to the new section.
51 is not a problem, I would create a section where with VALUES LESS than 100 , but what about NULL units?

I was thinking about moving on to something like a section on a range (NVL(b,0)) , but I'm afraid that I will have to process the whole table (not possible, the real table has a lot of data).

Any idea?

Answer 1

You cannot have NULL in the key column column of a partition table of partitioned intervals (starting from 12.1):

Interval Separation Restrictions

• You can specify only one column of a split key , and it must be NUMBER, DATE, FLOAT, or TIMESTAMP.

[...]

• You cannot specify NULL values ​​for a partition key column.

You cannot use expressions for a split key. However, as suggested by @Shoelace , you can use a virtual column (containing your expression) as the section 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 will need a table rebuild.

Answer 2

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

t

 alter table a set INTERVAL(100) ; 

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

Unfortunately, this does not allow you to change the columns of the sections .. so I think you're out of luck. but you can always try.