建立單獨的分割槽儲存NULL記錄

今天朋友問我一個問題，如何建立一個分割槽表，使得包含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值，都可以使用這個方法來實現功能。