I have an Oracle DB with version 19c (19.0.0.0.0). And I get the error "SQL Error [14400][72000]: ORA-14400: inserted partition key doesn't map to any partition" even thought a range interval N is set up on the table.
I use the following 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);
And I make the following insert:
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');
After that I get the error ORA-14400.
But when I shift the date in of the initial partition to a later date like
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
it works.
Are there any limites? I have seen that there are some logical limits on partitions number. But on the insert mentioned upper in the question DB creates only one additional partition independently on which date I specify, so that shouldn't be a problem, am I wrong? Maybe there are any other limits that I didn't notice?
CodePudding user response：
The max number of (sub-)partitions does not apply to the real number of (sub-)partitions. The limit applies to the theoretical number based on existing min and max value, i.e. gaps are taken into consideration.
As already mentioned in my commend you should never store date values as number (or string), it's a design flaw. Use always proper DATE or TIMESTAMP data type.
20210731 - 20190431 = 20'300 - not 823 days. Each of your partition has 52 subpartitions. 20'300 x 52 = 1'055'600 which exceeds the limit of 1024K - 1 = 1'023'999 subpartitions
Apart from that 20190431 may represent 31st of April, which does not exist.
You see, there are several reasons to use proper data types.