Range partitioning is useful when data has some sort of logical range. Example dates or numbers. See below example where monthly range partitions created based on date column in account table.
CREATE TABLE T_ACCOUNT
(
V_ACCOUNT_NUM VARCHAR2(100 CHAR),
V_ACCOUNT_NAME VARCHAR2(200 CHAR),
ACC_DATE DATE
)
PARTITION BY RANGE
(
ACC_DATE
)
(
PARTITION P_DEC2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY')),
PARTITION P_JAN2019 VALUES LESS THAN (TO_DATE('01-FEB-2019','DD-MON-YYYY')),
PARTITION P_FEB2019 VALUES LESS THAN (TO_DATE('01-MAR-2019','DD-MON-YYYY')),
PARTITION P_MAR2019 VALUES LESS THAN (TO_DATE('01-APR-2019','DD-MON-YYYY'))
);
Fetching the data from particular partition : In below example you will able to see only match partition data.
SELECT * FROM T_ACCOUNT PARTITION(P_MAR2019);
Check partitions metadata in user_tab_partitions & subpartitions.
SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='T_ACCOUNT';
To check the Partition KEY of the table, use below query:
SELECT partition_name, column_name, high_value, partition_position FROM USER_TAB_PARTITIONS PART , USER_PART_KEY_COLUMNS PART_KEY WHERE table_name ='T_ACCOUNT' AND PART.table_name = PART_KEY.name;
In the above example any record having acc_date less than ’01-JAN-2019′ will go to P_DEC2018 partition. See below example:
INSERT
INTO "T_ACCOUNT"
(
V_ACCOUNT_NUM,
V_ACCOUNT_NAME,
ACC_DATE
)
VALUES
(
'1234567890',
'ABC',
TO_DATE('20-OCT-2010', 'DD-MON-YYYY')
);
COMMIT;
SELECT * FROM T_ACCOUNT PARTITION (P_DEC2018) ;
In the this example any record having acc_date greater than ’31-MAR-2019′ will error out, as no partition exists for that date.
Example:
INSERT
INTO "T_ACCOUNT"
(
V_ACCOUNT_NUM,
V_ACCOUNT_NAME,
ACC_DATE
)
VALUES
(
'1234567890',
'ABC',
TO_DATE('20-OCT-2019', 'DD-MON-YYYY')
);
Error report:
SQL Error: ORA-14400: inserted partition key does not map to any partition
14400. 00000 – “inserted partition key does not map to any partition”
*Cause: An attempt was made to insert a record into, a Range or Composite
Range object, with a concatenated partition key that is beyond
the concatenated partition bound list of the last partition -OR-
An attempt was made to insert a record into a List object with
a partition key that did not match the literal values specified
for any of the partitions.
*Action: Do not insert the key. Or, add a partition capable of accepting
the key, Or add values matching the key to a partition specification
Here in this example we can see the partition key is defined maximum upto 2019-04-01 (Non-inclusive) . But we are trying to insert 20-OCT-2019, which is not getting mapped to any partition, that’s why this error occured as date value which we are trying to insert is T_ACCOUNT not satisfying the partition key range criteria.
Solution: To fix it, you may have to add new partitions or add a Maxvalue partition.
ALTER TABLE T_ACCOUNT ADD partition P_OCT2019 VALUES less than (TO_DATE('01-NOV-2019', 'DD-MON-YYYY'));
ALTER TABLE T_ACCOUNT ADD partition P_MAXVALUE VALUES less than (MAXVALUE);
Drop Partition:
Once historical data is no longer required for business analysis, after retention period the whole partition can be dropped.
DROP PARTITION P_DEC2018;
Updating Key:
When you are trying to update the partition key column for any record.
UPDATE T_ACCOUNT SET ACC_DATE='20-JAN-2019' WHERE v_account_num='1234567890';
Error report:
SQL Error: ORA-14402: updating partition key column would cause a partition change
14402. 00000 – “updating partition key column would cause a partition change”
*Cause: An UPDATE statement attempted to change the value of a partition
key column causing migration of the row to another partition
*Action: Do not attempt to update a partition key column or make sure that
the new partition key is within the range containing the old
partition key.
You can’t move a row with an update: Row movement disabled, which is the default option.
Solution: We can enable Row movement to partition table T_ACCOUNT. It allows rows to be moved across partitions.
ALTER TABLE T_ACCOUNT ENABLE ROW MOVEMENT; UPDATE T_ACCOUNT SET ACC_DATE='20-JAN-2019' WHERE v_account_num='1234567890'; COMMIT;
Thanks!
Happy Learning! Your feedback would be appreciated!
Follow @shobhitsinghIN