Implementing Interval Partition with Enable Row Movement
In this article , we will learn how to implement interval partition with enable row movement.
Table Creation with interval partition (without interval Row Movement.
Create user
|
1 2 3 |
SYS>>grant dba to ajay identified by ajay; Grant succeeded. |
Now create table with interval partition.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
SYS>>CREATE TABLE AJAY.MIC_INS_DD ( ID VARCHAR2(32 CHAR) NOT NULL, NAME VARCHAR2(35 CHAR) NOT NULL, KEY VARCHAR2(30 CHAR) NOT NULL, MIC_TAG_ID DATE default (to_date('01-01-1999', 'MM-DD-YYYY')) ) TABLESPACE MIC_DATA RESULT_CACHE (MODE DEFAULT) PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( MAXSIZE UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) PARTITION BY RANGE (MIC_TAG_ID) INTERVAL( NUMTODSINTERVAL(1, 'DAY')) ( PARTITION VALUES LESS THAN (TO_DATE('01-01-1999', 'MM-DD-YYYY', 'NLS_CALENDAR=GREGORIAN')) LOGGING NOCOMPRESS TABLESPACE MIC_DATA PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 8M NEXT 1M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; |
Now try to insert records into table and write null value in MIC_TAG_ID (interval partition Column).
|
1 |
SYS>>insert into AJAY.MIC_INS_DD values('ABC','123','KEY123',null); |
You will get following error.
|
1 2 3 4 5 6 |
SYS>>insert into AJAY.MIC_INS_DD values('ABC','123','KEY123',null); insert into AJAY.MIC_INS_DD values('ABC','123','KEY123',null) * ERROR at line 1: ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions |
Which means on virtual column we can’t insert Null values .
This issue occurs because the column on which the partition condition was created is a virtual column created in IDR and the value is updated using SQL Expression. It is not a column originated from source.
On using virtual column, Applier first inserts physical columns and then updates virtual columns with the values calculated based on physical columns.
Thus, the partition column is inserted with NULL value first which invalidates the partition condition and results an Oracle error.
In order resolve that issue the work around follows below steps.
Now insert values into same table with actual values and try to update those records
|
1 2 3 |
SYS>>insert into AJAY.MIC_INS_DD values('ABC','123','KEY123',sysdate); 1 row created. |
Now check partitions
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SYS>>set linesize 300 col table_owner format a15 col partition_name for a25 col table_name format a28 col high_value format a90 col COLUMN_name format a30 select pt.table_owner, pt.partition_name , pt.buffer_pool, pt.high_value, pt.partition_position, pt.num_rows , pt.LAST_ANALYZED, pc.COLUMN_NAME, pc.COLUMN_POSITION from dba_tab_partitions pt, dba_part_key_columns pc where table_name=upper('&table_name') AND pt.table_name=pc.name order by partition_position; Enter value for table_name: MIC_INS_DD_MV |
|
1 2 3 4 |
TABLE_OWNER PARTITION_NAME BUFFER_ HIGH_VALUE PARTITION_POSITION NUM_ROWS LAST_ANAL COLUMN_NAME COLUMN_POSITION --------------- ------------------------- ------- ------------------------------------------------------------------------------------------ ------------------ ---------- --------- ------------------------------ --------------- AJAY SYS_P733 DEFAULT TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 1 MIC_TAG_ID 1 AJAY SYS_P764 DEFAULT TO_DATE(' 2019-05-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2 MIC_TAG_ID 1 |
Select the records.
|
1 2 3 4 5 |
SYS>>select * from MIC_INS_DD; ID NAME KEY MIC_TAG_I -------------------------------- ---------------------------------------- ------------------------------ --------- ABC 123 KEY123 18-MAY-19 |
Now update the record.
|
1 2 3 4 5 |
SYS>>update AJAY.MIC_INS_DD set MIC_TAG_ID=to_date('01-01-2019', 'dd-mm-yyyy'); update AJAY.MIC_INS_DD set MIC_TAG_ID=to_date('01-01-2019', 'dd-mm-yyyy') * ERROR at line 1: ORA-14402: updating partition key column would cause a partition change |
Create table with default values and row movement.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
SYS>>CREATE TABLE AJAY.MIC_INS_DD_MV ( ID VARCHAR2(32 CHAR) NOT NULL, NAME VARCHAR2(35 CHAR) NOT NULL, KEY VARCHAR2(30 CHAR) NOT NULL, MIC_TAG_ID DATE default (to_date('01-01-1999', 'MM-DD-YYYY')) ) TABLESPACE MIC_DATA RESULT_CACHE (MODE DEFAULT) PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( MAXSIZE UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) PARTITION BY RANGE (MIC_TAG_ID) INTERVAL( NUMTODSINTERVAL(1, 'DAY')) ( PARTITION VALUES LESS THAN (TO_DATE('01-01-1999', 'MM-DD-YYYY', 'NLS_CALENDAR=GREGORIAN')) LOGGING NOCOMPRESS TABLESPACE MIC_DATA PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 8M NEXT 1M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING enable row movement; Table Created. |
Now insert records with default values
|
1 2 3 |
SYS>>insert into AJAY.MIC_INS_DD_MV values('ABC','123','KEY123',default); 1 row created. |
Check data available on table.
|
1 2 3 4 5 |
SYS>>select * from AJAY.MIC_INS_DD_MV; ID NAME KEY MIC_TAG_I -------------------------------- ---------------------------------------- ------------------------------ --------- ABC 123 KEY123 01-JAN-99 |
Check the partitions on table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
SYS>>set linesize 300 col table_owner format a15 col partition_name for a25 col table_name format a28 col high_value format a90 col COLUMN_name format a30 select pt.table_owner, pt.partition_name , pt.buffer_pool, pt.high_value, pt.partition_position, pt.num_rows , pt.LAST_ANALYZED, pc.COLUMN_NAME, pc.COLUMN_POSITION from dba_tab_partitions pt, dba_part_key_columns pc where table_name=upper('&table_name') AND pt.table_name=pc.name order by partition_position; Enter value for table_name: MIC_INS_DD_MV TABLE_OWNER PARTITION_NAME BUFFER_ HIGH_VALUE PARTITION_POSITION NUM_ROWS LAST_ANAL COLUMN_NAME COLUMN_POSITION --------------- ------------------------- ------- ------------------------------------------------------------------------------------------ ------------------ ---------- --------- ------------------------------ --------------- AJAY SYS_P734 DEFAULT TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 1 MIC_TAG_ID 1 AJAY SYS_P735 DEFAULT TO_DATE(' 1999-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2 MIC_TAG_ID 1 |
Insert one more value into same table
|
1 2 3 |
SYS>>insert into AJAY.MIC_INS_DD_MV values('ABC','123','KEY123','01-JAN-10'); 1 row created. |
Commit the records.
|
1 2 3 |
SYS>>commit; Commit complete. |
Check the partitions with same query.
|
1 2 3 4 5 |
TABLE_OWNER PARTITION_NAME BUFFER_ HIGH_VALUE PARTITION_POSITION NUM_ROWS LAST_ANAL COLUMN_NAME COLUMN_POSITION --------------- ------------------------- ------- ------------------------------------------------------------------------------------------ ------------------ ---------- --------- ------------------------------ --------------- AJAY SYS_P734 DEFAULT TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 1 MIC_TAG_ID 1 AJAY SYS_P735 DEFAULT TO_DATE(' 1999-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2 MIC_TAG_ID 1 AJAY SYS_P736 DEFAULT TO_DATE(' 2010-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 3 MIC_TAG_ID 1 |
Yes its creating new query
Now update one record with latest format.
|
1 2 3 |
SYS>>update AJAY.MIC_INS_DD_MV set MIC_TAG_ID=to_date('01-01-2019', 'dd-mm-yyyy') where MIC_TAG_ID=to_date('01-JAN-2010', 'dd-mm-yyyy') ; 1 row updated. |
Check records in a table.
|
1 2 3 4 5 6 |
SYS>>select * from AJAY.MIC_INS_DD_MV; ID NAME KEY MIC_TAG_I -------------------------------- ---------------------------------------- ------------------------------ --------- ABC 123 KEY123 01-JAN-99 ABC 123 KEY123 01-JAN-19 |
Commit the records
|
1 2 3 |
SYS>>commit; Commit complete. |
Check partitions
|
1 2 3 4 5 6 |
TABLE_OWNER PARTITION_NAME BUFFER_ HIGH_VALUE PARTITION_POSITION NUM_ROWS LAST_ANAL COLUMN_NAME COLUMN_POSITION --------------- ------------------------- ------- ------------------------------------------------------------------------------------------ ------------------ ---------- --------- ------------------------------ --------------- AJAY SYS_P734 DEFAULT TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 1 MIC_TAG_ID 1 AJAY SYS_P735 DEFAULT TO_DATE(' 1999-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2 MIC_TAG_ID 1 AJAY SYS_P736 DEFAULT TO_DATE(' 2010-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 3 MIC_TAG_ID 1 AJAY SYS_P746 DEFAULT TO_DATE(' 2019-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 4 MIC_TAG_ID 1 |
When ever we updated new partition clause will be created.
Note: Please test scripts in Non Prod before trying in Production.
Loading...
