"ORA-14400: inserted partition key doesn't map to any partition"甚至认为在桌子上设置了一个范围间隔 N

Question

我有一个版本为19c(19.0.0.0.0(的Oracle数据库；SQL错误[14400][72000]：ORA-1400:插入的分区键没有映射到任何分区"；即使考虑到在表上设置了范围间隔N。

我使用以下DDL：

CREATE TABLE BAISMART.GSWBIF_IFRS_TEST_MARS3035
(
GSWBIF_ID          NUMBER,
MODUL              CHAR(2 BYTE)               NOT NULL,
KDNRH              NUMBER(14)                 NOT NULL,
KTONR              NUMBER(10)                 NOT NULL,
GSREF              VARCHAR2(50 BYTE)          NOT NULL,
EWBBT              NUMBER(18,2)               NOT NULL,
EWBWH              CHAR(3 BYTE)               NOT NULL,
EWBBP              NUMBER(18,2)               NOT NULL,
EWBWP              CHAR(3 BYTE)               NOT NULL,
ABSBT              NUMBER(18,2)               NOT NULL,
ABTYP              CHAR(1 BYTE)               NOT NULL,
EWBBS              NUMBER(18,2)               NOT NULL,
EWBWS              CHAR(3 BYTE)               NOT NULL,
RSTBT              NUMBER(18,2)               DEFAULT 0                     NOT NULL,
RSTWH              CHAR(3 BYTE)               DEFAULT ' '                   NOT NULL,
ABSWH              CHAR(3 BYTE)               DEFAULT 'EUR'                 NOT NULL,
DXIFD              NUMBER(8),
IFNAM              CHAR(10 BYTE)              NOT NULL,
BPLO_ID            NUMBER(10)                 NOT NULL,
MAP_RUN_ID_INSERT  NUMBER(22)                 NOT NULL,
MAP_RUN_ID_UPDATE  NUMBER(22),
MDANT              CHAR(3 BYTE)               DEFAULT '100'
)
COLUMN STORE COMPRESS FOR QUERY HIGH ROW LEVEL LOCKING
TABLESPACE BAIS_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
BUFFER_POOL      DEFAULT
)
PARTITION BY RANGE (BPLO_ID) INTERVAL(1)
SUBPARTITION BY LIST (IFNAM, MODUL)
SUBPARTITION TEMPLATE (
SUBPARTITION SYS_SUB_CB_N1_K1 VALUES (('CB        ', 'N1'),('CB        ', 'K1')),
SUBPARTITION SYS_SUB_CB_EC VALUES (('CB        ', 'EC')),
SUBPARTITION SYS_SUB_CB_G1 VALUES (('CB        ', 'G1')),
SUBPARTITION SYS_SUB_CB_A1 VALUES (('CB        ', 'A1')),
SUBPARTITION SYS_SUB_CB_S1 VALUES (('CB        ', 'S1')),
SUBPARTITION SYS_SUB_CB_AK VALUES (('CB        ', 'AK')),
SUBPARTITION SYS_SUB_CB_ZU VALUES (('CB        ', 'ZU')),
SUBPARTITION SYS_SUB_INGAT_N1_K1 VALUES (('INGAT     ', 'N1'),('INGAT     ', 'K1')),
SUBPARTITION SYS_SUB_INGAT_G1_EC VALUES (('INGAT     ', 'G1'),('INGAT     ', 'EC')),
SUBPARTITION SYS_SUB_INGAT_A1 VALUES (('INGAT     ', 'A1')),
SUBPARTITION SYS_SUB_INGAT_S1 VALUES (('INGAT     ', 'S1')),
SUBPARTITION SYS_SUB_INGAT_AK VALUES (('INGAT     ', 'AK')),
SUBPARTITION SYS_SUB_INGAT_ZU VALUES (('INGAT     ', 'ZU')),
SUBPARTITION SYS_SUB_LOANIQ_N1_K1 VALUES (('LOANIQ    ', 'N1'),('LOANIQ    ', 'K1')),
SUBPARTITION SYS_SUB_LOANIQ_G1_EC VALUES (('LOANIQ    ', 'G1'),('LOANIQ    ', 'EC')),
SUBPARTITION SYS_SUB_LOANIQ_A1 VALUES (('LOANIQ    ', 'A1')),
SUBPARTITION SYS_SUB_LOANIQ_S1 VALUES (('LOANIQ    ', 'S1')),
SUBPARTITION SYS_SUB_LOANIQ_AK VALUES (('LOANIQ    ', 'AK')),
SUBPARTITION SYS_SUB_LOANIQ_ZU VALUES (('LOANIQ    ', 'ZU')),
SUBPARTITION SYS_SUB_VORTEXCB_N1_K1 VALUES (('VORTEXCB  ', 'N1'),('VORTEXCB  ', 'K1')),
SUBPARTITION SYS_SUB_VORTEXCB_G1_EC VALUES (('VORTEXCB  ', 'G1'),('VORTEXCB  ', 'EC')),
SUBPARTITION SYS_SUB_VORTEXCB_A1 VALUES (('VORTEXCB  ', 'A1')),
SUBPARTITION SYS_SUB_VORTEXCB_S1 VALUES (('VORTEXCB  ', 'S1')),
SUBPARTITION SYS_SUB_VORTEXCB_AK VALUES (('VORTEXCB  ', 'AK')),
SUBPARTITION SYS_SUB_VORTEXCB_ZU VALUES (('VORTEXCB  ', 'ZU')),
SUBPARTITION SYS_SUB_CBAT_N1_K1 VALUES (('CBAT      ', 'N1'),('CBAT      ', 'K1')),
SUBPARTITION SYS_SUB_CBAT_G1 VALUES (('CBAT      ', 'G1')),
SUBPARTITION SYS_SUB_CBAT_EC VALUES (('CBAT      ', 'EC')),
SUBPARTITION SYS_SUB_CBAT_A1 VALUES (('CBAT      ', 'A1')),
SUBPARTITION SYS_SUB_CBAT_S1 VALUES (('CBAT      ', 'S1')),
SUBPARTITION SYS_SUB_CBAT_AK VALUES (('CBAT      ', 'AK')),
SUBPARTITION SYS_SUB_CBAT_ZU VALUES (('CBAT      ', 'ZU')),
SUBPARTITION SYS_SUB_KORDOBA_N1_K1 VALUES (('KORDOBA   ', 'N1'),('KORDOBA   ', 'K1')),
SUBPARTITION SYS_SUB_KORDOBA_G1_EC VALUES (('KORDOBA   ', 'G1'),('KORDOBA   ', 'EC')),
SUBPARTITION SYS_SUB_KORDOBA_A1 VALUES (('KORDOBA   ', 'A1')),
SUBPARTITION SYS_SUB_KORDOBA_S1 VALUES (('KORDOBA   ', 'S1')),
SUBPARTITION SYS_SUB_KORDOBA_AK VALUES (('KORDOBA   ', 'AK')),
SUBPARTITION SYS_SUB_KORDOBA_ZU VALUES (('KORDOBA   ', 'ZU')),
SUBPARTITION SYS_SUB_EQUENS_WL_N1_K1 VALUES (('EQUENS-WL ', 'N1'),('EQUENS-WL ', 'K1')),
SUBPARTITION SYS_SUB_EQUENS_WL_G1_EC VALUES (('EQUENS-WL ', 'G1'),('EQUENS-WL ', 'EC')),
SUBPARTITION SYS_SUB_EQUENS_WL_A1 VALUES (('EQUENS-WL ', 'A1')),
SUBPARTITION SYS_SUB_EQUENS_WL_S1 VALUES (('EQUENS-WL ', 'S1')),
SUBPARTITION SYS_SUB_EQUENS_WL_AK VALUES (('EQUENS-WL ', 'AK')),
SUBPARTITION SYS_SUB_EQUENS_WL_ZU VALUES (('EQUENS-WL ', 'ZU')),
SUBPARTITION SYS_SUB_PROFILE7_N1_K1 VALUES (('PROFILE7  ', 'N1'),('PROFILE7  ', 'K1')),
SUBPARTITION SYS_SUB_PROFILE7_G1_EC VALUES (('PROFILE7  ', 'G1'),('PROFILE7  ', 'EC')),
SUBPARTITION SYS_SUB_PROFILE7_A1 VALUES (('PROFILE7  ', 'A1')),
SUBPARTITION SYS_SUB_PROFILE7_S1 VALUES (('PROFILE7  ', 'S1')),
SUBPARTITION SYS_SUB_PROFILE7_AK VALUES (('PROFILE7  ', 'AK')),
SUBPARTITION SYS_SUB_PROFILE7_ZU VALUES (('PROFILE7  ', 'ZU')),
SUBPARTITION SYS_SUB_KORDOBA VALUES (
('KORDOBA-BF', 'N1'),
('KORDOBA-BF', 'G1'),
('KORDOBA-BF', 'A1'),
('KORDOBA-BF', 'S1'),
('KORDOBA-BF', 'AK'),
('KORDOBA-BF', 'ZU'),
('KORDOBA-BF', 'K1'),
('KORDOBA-BF', 'EC'),
('KORDOBA-PK', 'N1'),
('KORDOBA-PK', 'G1'),
('KORDOBA-PK', 'A1'),
('KORDOBA-PK', 'S1'),
('KORDOBA-PK', 'AK'),
('KORDOBA-PK', 'ZU'),
('KORDOBA-PK', 'K1'),
('KORDOBA-PK', 'EC'),
('KORDOBA-RK', 'N1'),
('KORDOBA-RK', 'G1'),
('KORDOBA-RK', 'A1'),
('KORDOBA-RK', 'S1'),
('KORDOBA-RK', 'AK'),
('KORDOBA-RK', 'ZU'),
('KORDOBA-RK', 'K1'),
('KORDOBA-RK', 'EC'),
('KORDOBA-GK', 'N1'),
('KORDOBA-GK', 'G1'),
('KORDOBA-GK', 'A1'),
('KORDOBA-GK', 'S1'),
('KORDOBA-GK', 'AK'),
('KORDOBA-GK', 'ZU'),
('KORDOBA-GK', 'K1'),
('KORDOBA-GK', 'EC'),
('KORDOBA-TH', 'N1'),
('KORDOBA-TH', 'G1'),
('KORDOBA-TH', 'A1'),
('KORDOBA-TH', 'S1'),
('KORDOBA-TH', 'AK'),
('KORDOBA-TH', 'ZU'),
('KORDOBA-TH', 'K1'),
('KORDOBA-TH', 'EC')
),
SUBPARTITION SYS_SUB_DEF VALUES (DEFAULT) )
(
PARTITION SYS_P2064940 VALUES LESS THAN (20190431) COLUMN STORE COMPRESS FOR QUERY HIGH ROW LEVEL LOCKING TABLESPACE BAIS_DATA
PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE (MAXSIZE UNLIMITED BUFFER_POOL DEFAULT )
);

ALTER TABLE BAISMART.GSWBIF_IFRS_TEST_MARS3035 ADD (
CONSTRAINT UK_GSWBIF_IFRS_TEST_MARS3035__I
UNIQUE (MODUL, KDNRH, KTONR, GSREF, BPLO_ID, IFNAM, MDANT)
USING INDEX LOCAL
ENABLE VALIDATE);

我插入以下内容：

Insert into BAISMART.GSWBIF_IFRS_TEST_MARS3035
(MODUL, KDNRH, KTONR, GSREF, EWBBT, 
EWBWH, EWBBP, EWBWP, ABSBT, ABTYP, 
EWBBS, EWBWS, RSTBT, RSTWH, ABSWH, 
DXIFD, IFNAM, BPLO_ID, MAP_RUN_ID_INSERT, MDANT)
Values
('ZU', 7701810149, 0, '000301NEARTNALON', 3.31, 
'EUR', 0, '   ', 0, ' ', 
3.31, 'EUR', 0, '   ', '   ', 
20210731, 'CB        ', 20210731, 269494, '100');

之后我得到了错误ORA-14400。

但是，当我将初始分区的日期转移到像这样的较晚日期时

PARTITION SYS_P2064940 VALUES LESS THAN (20190431) COLUMN STORE COMPRESS FOR QUERY HIGH ROW LEVEL LOCKING TABLESPACE BAIS_DATA
PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE (MAXSIZE UNLIMITED BUFFER_POOL DEFAULT

它是有效的。

有限制吗？我已经看到分区数量有一些逻辑限制。但是在上面提到的插入问题中，DB只独立地创建了一个额外的分区，在我指定的日期，所以这应该不是问题，我错了吗？也许还有其他我没有注意到的限制？

Answer 1

分区的最大数目不适用于分区的实际数目。该限制适用于基于现有最小值和最大值的理论数，即考虑间隙。

正如我在推荐中已经提到的，您应该永远不要将日期值存储为数字(或字符串(，这是一个设计缺陷。始终使用正确的DATE或TIMESTAMP数据类型。

20210731 - 20190431 = 20'300——不是823天。每个分区都有52个子分区。20'300 x 52 = 1'055'600超过1024K-1=1'023'999子分区的限制

除此之外，20190431可能代表不存在的4月31日。

您知道，使用正确的数据类型有几个原因。