In the previous blog Oracle Range Partition we have explored how we can create range partition in oracle using date as partition key. Also we have seen one example where the range partition key is defined maximum upto ’01-APR-2019′ (Non-inclusive). But we are trying to insert ’20-OCT-2019′ date data, which is not getting mapped to any partition, that’s why ORA-14400 error occurred. See below error report provided by oracle.
SQL Error: ORA-14400: inserted partition key does not map to any partition
14400. 00000 – “inserted partition key does not map to any partition”
Here comes the Interval Partition, which is extension of Oracle Range Partition in which, interval partitions are automatically created by the database when data is inserted into the partition.
CREATE TABLE T_ACCOUNT ( V_ACCOUNT_NUM VARCHAR2(100 CHAR), V_ACCOUNT_NAME VARCHAR2(200 CHAR), ACC_DATE DATE ) PARTITION BY RANGE ( ACC_DATE ) INTERVAL ( NUMTOYMINTERVAL(1, 'MONTH') ) ( PARTITION P_DEC2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY')) );
After that we have inserted the data of future months, i.e. SEP & OCT for which Partitions were not available. See below insert statements.
INSERT INTO "SAIL_IN_DEV"."T_ACCOUNT" (V_ACCOUNT_NUM, V_ACCOUNT_NAME, ACC_DATE) VALUES ('1234567890', 'ABC', TO_DATE('20-SEP-19', 'DD-MON-RR')); INSERT INTO "SAIL_IN_DEV"."T_ACCOUNT" (V_ACCOUNT_NUM, V_ACCOUNT_NAME, ACC_DATE) VALUES ('1234567891', 'ABC', TO_DATE('20-OCT-19', 'DD-MON-RR')); COMMIT;
After committing the data let see the automatic partitions created by Oracle :
Notice that a system generated partition named SYS_P601 & SYS_P402 has been created after inserting rows with a partition key greater than the provided partition.
Fetching the data from particular partition : In below example you will able to see only match partition data.
SELECT * FROM T_ACCOUNT partition (SYS_P601);
It would be really painful to look up the system generated partition name every time, if you have to query directly partition. Therefore, oracle provided a syntax to specify a partition is by using the partition for (DATE) clause in the query: See below example:
partition for (to_date(’15-OCT-2019′,’DD-MON-YYYY’));
SELECT * FROM T_ACCOUNT partition for (to_date('01-SEP-2019','DD-MON-YYYY'));
The following restrictions apply on Interval Partition:
- Only one partitioning key column allowed (NUMBER or DATE)
- Minimum one partition must be defined when the table is created.
- MAXVALUE partition cannot be defined.
- NULL values are not allowed in the partition column.
- Can’t be used at the subpartition level.
- Not supported for index-organized tables.
- You cannot create a domain index on an interval partitioned table.
Daily Partitions Example:
CREATE TABLE T_TRANSACTIONS ( TRANSACTION_KEY NUMBER, AMOUNT NUMBER, CUST_ID NUMBER, TRANSACTION_DATE DATE ) PARTITION BY RANGE ( TRANSACTION_DATE ) INTERVAL ( NUMTODSINTERVAL(1, 'day') ) ( PARTITION P_01012019 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY')) );
Happy Learning! Your feedback would be appreciated!Follow @shobhitsinghIN