Oracle分区键值存在空值如何建分区表

maxvalue， 空值是无限大值

要存储分区键为空值的记录，那么就得有一个特定的分区来存储分区键为空值的记录。
1.Range-Partitioned Table
我们知道范围分区可以建values less than (maxvalue)
SQL> CREATE TABLE sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE(‘01-APR-2006’,‘dd-MON-yyyy’))
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE(‘01-JUL-2006’,‘dd-MON-yyyy’))
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE(‘01-OCT-2006’,‘dd-MON-yyyy’))
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE(‘01-JAN-2007’,‘dd-MON-yyyy’))
, partition sales_default values less than (maxvalue)
); 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Table created.

SQL> insert into sales values(1,1,sysdate,1,‘1’,1,1);
1 row created.
SQL> insert into sales values(1,1,null,1,‘2’,1,1);
1 row created.

发现是可以把空值存入maxvalue的分区的，看来maxvalue分区是用来存储非规范值，可以存储超出指定分区范围的值，也可以存储空值。
当然我们可以在建表初始把10年甚至20年、30年内用到的分区都预先创建好，然后再创建一个maxvalue分区来存储空值或超出范围的值，但是生产中一般不这样用，通常情况是采用间隔分区方式，超出预先创建的范围分区之外的按固定间隔创建分区。
2.Interval-Partitioned Table
间隔分区表是不允许创建maxvalue分区的，这与超出范围按指定间隔创建分区冲突。
创建时会报出错误
ORA-14761: MAXVALUE partition cannot be specified for Interval partitioned。
SQL> CREATE TABLE sales1
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, ‘MONTH’))
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE(‘01-APR-2006’,‘dd-MON-yyyy’))
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE(‘01-JUL-2006’,‘dd-MON-yyyy’))
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE(‘01-OCT-2006’,‘dd-MON-yyyy’))
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE(‘01-JAN-2007’,‘dd-MON-yyyy’))
, partition sales_default values less than (maxvalue)
); 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
CREATE TABLE sales1
*
ERROR at line 1:
ORA-14761: MAXVALUE partition cannot be specified for Interval partitioned
objects

间隔分区表分区键使用空值插入时会报错：
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions.
SQL> CREATE TABLE sales1
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, ‘MONTH’))
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE(‘01-APR-2006’,‘dd-MON-yyyy’))
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE(‘01-JUL-2006’,‘dd-MON-yyyy’))
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE(‘01-OCT-2006’,‘dd-MON-yyyy’))
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE(‘01-JAN-2007’,‘dd-MON-yyyy’))
); 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Table created.

SQL> insert into sales1(prod_id,cust_id,time_id,channel_id) values(1,1,null,1);
insert into sales1(prod_id,cust_id,time_id,channel_id) values(1,1,null,1)
*
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted
number of partition
3.Hash-Partitioned Tables
哈希分区，分区键值为空时可以存储，说明空值也可以计算hash值。
SQL> CREATE TABLE scubagear
(id NUMBER,
name VARCHAR2 (60))
PARTITION BY HASH (id)
PARTITIONS 4
STORE IN (users,example); 2 3 4 5 6
Table created.
SQL> insert into scubagear values(1,‘qq’);
1 row created.
SQL> insert into scubagear values(null,‘aa’);
1 row created.
4.List-Partitioned
SQL> CREATE TABLE sales_by_region (item# INTEGER, qty INTEGER,
store_name VARCHAR(30), state_code VARCHAR(2),
sale_date DATE)
STORAGE(INITIAL 10K NEXT 20K) TABLESPACE users
PARTITION BY LIST (state_code)
(
PARTITION region_east
VALUES (‘MA’,‘NY’,‘CT’,‘NH’,‘ME’,‘MD’,‘VA’,‘PA’,‘NJ’),
PARTITION region_west
VALUES (‘CA’,‘AZ’,‘NM’,‘OR’,‘WA’,‘UT’,‘NV’,‘CO’),
PARTITION region_south
VALUES (‘TX’,‘KY’,‘TN’,‘LA’,‘MS’,‘AR’,‘AL’,‘GA’),
PARTITION region_central
VALUES (‘OH’,‘ND’,‘SD’,‘MO’,‘IL’,‘MI’,‘IA’),
PARTITION region_null
VALUES (NULL),
PARTITION region_unknown
VALUES (DEFAULT)
); 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Table created.
SQL> insert into sales_by_region(item#,qty,state_code) values(1,1,null);
1 row created.
SQL> insert into sales_by_region(item#,qty,state_code) values(1,1,‘AA’);
1 row created.

列表分区表可以专门创建存储空值的分区，也可以创建default分区来存储键值不能映射到其他分区的数据。

总结：
可以存储分区键值为空的分区表类型如下：
(1)范围分区表创建maxvalue分区时
(2)哈希分区表
(3)列表分区表创建null值列表分区或default列表分区时。