今天朋友问我一个问题,如何建立一个分区表,使得包含NULL记录在单独的分区,而其他记录在另一个分区。
由于NULL的特殊性,任何与NULL的比较结果都是NULL,所以在处理分区列的时候有点麻烦。如果要加上NVL、DECODE等函数来处理NULL,则Oracle必须是11g以上的版本,利用虚拟列分区的技术才能实现。
由于Oracle在比较的时候认为NULL的值最大,因此解决这个问题其实很容易,只需要在设置分区键值的时候,将边界设定的尽可能的大就可以了。
在朋友的需要中,需要分区的列是DATE类型。设置DATE类型的分区范围,可以仿照Oracle的物化视图的例子,将分区上限设置为TO_DATE(‘4000-1-1’, ‘YYYY-MM-DD’),或者干脆将分区上限设置为DATE所允许的最大值:
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
会话已更改。
SQL> create table t_part_range (id number, created date)
2 partition by range (created)
3 (partition p1 values less than (to_date('9999-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')),
4 partition p2 values less than (maxvalue));
表已创建。
SQL> insert into t_part_range values (1, sysdate);
已创建 1 行。
SQL> insert into t_part_range values (2, null);
已创建 1 行。
SQL> select * from t_part_range partition (p1);
ID CREATED
---------- -------------------
1 2009-07-16 15:40:12
SQL> select * from t_part_range partition (p2);
ID CREATED
---------- -------------------
2
对于任何正常的日期数据都能满足条件,但是如果真的插入这个极限的日期值,就会导致问题:
SQL> insert into t_part_range values (3, to_date('9999-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss'));
已创建 1 行。
SQL> select * from t_part_range partition (p2);
ID CREATED
---------- -------------------
2
3 9999-12-31 23:59:59
因此,从严谨的角度讲,应该选择列表分区方式:
SQL> create table t_part_list (id number, created date)
2 partition by list (created)
3 (partition p1 values (null),
4 partition p2 values (default));
表已创建。
SQL> insert into t_part_list values (1, sysdate);
已创建 1 行。
SQL> insert into t_part_list values (2, null);
已创建 1 行。
SQL> select * from t_part_list partition (p1);
ID CREATED
---------- --------------
2
SQL> select * from t_part_list partition (p2);
ID CREATED
---------- -------------------
1 2009-07-16 16:10:57
只要数据库版本在Oracle 9.2以上,支持列表分区的DEFAULT值,都可以使用这个方法来实现功能。