interval分区表,分区字段为空,报错:
ORA-14400: 插入的分区关键字未映射到任何分区。
总结:
1、使用virtual字段,对原来的分区字段做nvl处理,然后基于这个virtual字段做interval分区
2、不使用interval,手工创建分区,此时,可以创建MAXVALUE分区,空值会落在此分区
1.自动分区表
使用interval自动创建分区,无法处理空值
ps:自动分区表无法创建MAXVALUE分区:ORA-14761: 不能对间隔分区对象指定 MAXVALUE 分区
create table test_table1(id number NOT NULL PRIMARY KEY,
ywrq date )
partition by range (ywrq)
interval(numtoyminterval(1,'MONTH'))
(
partition part1 values less than (to_date('2021-06-01','YYYY-MM-DD'))
);
2.虚拟分区字段,自动分区
create table test_table2(id number NOT NULL PRIMARY KEY,
ywrq date,
ywrq_virtual date generated always as (nvl(ywrq,to_date('1900-01-01','yyyy-mm-dd'))) virtual )
partition by range (ywrq_virtual)
interval(numtoyminterval(1,'MONTH'))
(
partition part1 values less than (to_date('2021-06-01','YYYY-MM-DD'))
);
3.手工创建分区,不用interval,可以创建MAXVALUE分区,空值会落在这个分区里
create table test_table3(id number NOT NULL PRIMARY KEY,
ywrq date )
partition by range (ywrq)
(
partition part1 values less than (to_date('2021-06-01','YYYY-MM-DD')),
partition part_max VALUES LESS THAN (MAXVALUE)
);
测试结果
insert into test_table1(id,ywrq) select '1',sysdate from dual ;--ok
insert into test_table1(id,ywrq) select '2',null from dual ; --ORA-14400: 插入的分区关键字未映射到任何分区
insert into test_table2(id,ywrq) select '1',sysdate from dual ;--ok
insert into test_table2(id,ywrq) select '2',null from dual ; --ok
insert into test_table3(id,ywrq) select '1',sysdate from dual ;--ok
insert into test_table3(id,ywrq) select '2',null from dual ; --ok