ORA-14037 分割槽界限過高的原因分析及解決 [轉]

在使用以下語句列表分割槽法建立分割槽表時 

SQL> create table T_parttab_str
  2  (
  3    PROVINCE_ID varchar2(8) not null,
  4    con_autoid      NUMBER(10) not null,
  5    RI_ID       NUMBER(10),
  6    MATER_NO    NVARCHAR2(20),
  7    MATER_NUM   NUMBER(15,6),
  8    MATER_TYPE  NVARCHAR2(2)
  9  )
 10  partition by range (PROVINCE_ID)
 11  subpartition by hash (con_autoid)
 12  subpartitions 8 store  in (tp_data,tp_hotdata)
 13  (
 14    PARTITION p1 VALUES LESS THAN ('2'),
 15  PARTITION p2 VALUES LESS THAN ('3'),
 16  PARTITION p3 VALUES LESS THAN ('4'),
 17  PARTITION p4 VALUES LESS THAN ('5'),
 18  PARTITION p5 VALUES LESS THAN ('6'),
 19  PARTITION p6 VALUES LESS THAN ('7'),
 20  PARTITION p7 VALUES LESS THAN ('8'),
 21  PARTITION p8 VALUES LESS THAN ('9'),
 22  PARTITION p9 VALUES LESS THAN ('10'),
 23  PARTITION p10 VALUES LESS THAN ('11'),
 24  PARTITION p11 VALUES LESS THAN ('12'),
 25    PARTITION p32 VALUES LESS THAN (maxvalue)
 26  )
 27  ;

create table T_parttab_str
(
  PROVINCE_ID varchar2(8) not null,
  con_autoid      NUMBER(10) not null,
  RI_ID       NUMBER(10),
  MATER_NO    NVARCHAR2(20),
  MATER_NUM   NUMBER(15,6),
  MATER_TYPE  NVARCHAR2(2)
)
partition by range (PROVINCE_ID)
subpartition by hash (con_autoid)
subpartitions 8 store  in (tp_data,tp_hotdata)
(
  PARTITION p1 VALUES LESS THAN ('2'),
PARTITION p2 VALUES LESS THAN ('3'),
PARTITION p3 VALUES LESS THAN ('4'),
PARTITION p4 VALUES LESS THAN ('5'),
PARTITION p5 VALUES LESS THAN ('6'),
PARTITION p6 VALUES LESS THAN ('7'),
PARTITION p7 VALUES LESS THAN ('8'),
PARTITION p8 VALUES LESS THAN ('9'),
PARTITION p9 VALUES LESS THAN ('10'),
PARTITION p10 VALUES LESS THAN ('11'),
PARTITION p11 VALUES LESS THAN ('12'),
  PARTITION p32 VALUES LESS THAN (maxvalue)
)

ORA-14037: 分割槽 "P8" 的分割槽界限過高

SQL>

查oracle 錯誤文件 ORA-14037

ORA-14037 partition bound of partition "string" is too high
Cause: High bound of the partition whose name (explicitly specified by the
user) is displayed in this message did not collate lower than that of the
following partition, which is illegal.
Action: Ensure that high bound of every partition (except for the last one)
collates lower than that of a following partition.

　原來是分割槽p8的分割槽界限比P9的大了,按通常的理解 9 比10小，但是oracle比較字串時，會首先從第一個字元比較。因為"9">"1"，所以會認為"9"

將上面的改為

SQL> create table T_parttab_str
  2  (
  3    PROVINCE_ID varchar2(8) not null,
  4    con_autoid      NUMBER(10) not null,
  5    RI_ID       NUMBER(10),
  6    MATER_NO    NVARCHAR2(20),
  7    MATER_NUM   NUMBER(15,6),
  8    MATER_TYPE  NVARCHAR2(2)
  9  )
 10  partition by range (PROVINCE_ID)
 11  subpartition by hash (con_autoid)
 12  subpartitions 8 store  in (tp_data,tp_hotdata)
 13  (
 14    PARTITION p1 VALUES LESS THAN ('02'),
 15  PARTITION p2 VALUES LESS THAN ('03'),
 16  PARTITION p3 VALUES LESS THAN ('04'),
 17  PARTITION p4 VALUES LESS THAN ('05'),
 18  PARTITION p5 VALUES LESS THAN ('06'),
 19  PARTITION p6 VALUES LESS THAN ('07'),
 20  PARTITION p7 VALUES LESS THAN ('08'),
 21  PARTITION p8 VALUES LESS THAN ('09'),
 22  PARTITION p9 VALUES LESS THAN ('10'),
 23  PARTITION p10 VALUES LESS THAN ('11'),
 24  PARTITION p11 VALUES LESS THAN ('12'),
 25    PARTITION p32 VALUES LESS THAN (maxvalue)
 26  )
 27  ;

Table created

SQL>

 成功建立。

建議：

       範圍分割槽時，分割槽鍵儘量不用字元型。如果字元型時要注意保持字元位數一致以避免出現意想不到的情況