Table Partitioning
enabling these database objects to be managed and accessed at a finer level of granularity.
Advantages of Partitioning
- Partitioning enables data management operations such data loads, index creation and rebuilding, and backup/recovery at the partition level, rather than on the entire table. This results in significantly reduced times for these operations.
- Partitioning improves query performance.
- Partitioning can significantly reduce the impact of scheduled downtime for maintenance operations.
- Partition independence for partition maintenance operations lets you perform concurrent maintenance operations on different partitions of the same table or index.
- Partitioning increases the availability of mission-critical databases if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery times, and impact of failures.
- Partitioning can be implemented without requiring any modifications to your applications.
to modify any of the SELECT statements or DML statements which access that table. You do not
need to rewrite
Partitioning Methods
Oracle provides the following partitioning methods:
methods.
Interval Table Partition in Oracle
Once your table has been partitioned, you may come across a problem in the maintenance of the data.
Let us say that the partitioning is based on the value of the PUBLISHED_DATE of a CD or phonograph record.
For example, when you insert a row with a date, the row is inserted into the first partition. Later,
you discover that the date is incorrect. You attempt to update the date, but you get an error message:
ORA-14402: updating partition key column would cause a partition change
Oracle has stopped the update because it would cause the row to belong to a different partition.
Oracle has implemented a new feature, called row movement, which allows you to make this kind of update.
When you complete the update, the row moves to the appropriate partition automatically.
Enable Row Movement
To change a partitioned table to allow row movement, use this command:
ALTER TABLE tablename ENABLE ROW MOVEMENT;
To change it back to not allowing row movement, use this command:
ALTER TABLE tablename DISABLE ROW MOVEMENT;
The next lesson covers renaming, moving, and coalescing partitions.
Row movement: The automatic shifting of a row from one partition to another when the value of the partitioning column in the row changes
so that the row belongs in a different partition.
Interval partitioning is an enhancement to range partitioning in Oracle 11g and interval partitioning automatically creates time-based partitions as new data is added.
Range partitioning allows an object to be partitioned by a specified range on the partitioning key. For example, if a table was used to store sales data, it might be range partitioned by a DATE column, with each month in a different partition.
Therefore, every month a new partition would need to be defined in order to store rows for that month. If a row was inserted for a new month before a partition was defined for that month, the following error would result:
ORA-14400: inserted partition key does not map to any partition
If this situation occurs, data loading will fail until the new partitions are created. This can cause serious problems in larger data warehouses where complex reporting has many steps and dependencies in a batch process. Mission critical reports might be delayed or incorrect due to this problem.
An Interval Partitioning Example
Interval partitioning can simplify the manageability by automatically creating the new partitions as needed by the data. Interval partitioning is enabled in the table's definition by defining one or more range partitions and including a specified interval. For example, consider the following table:
create table pos_data (
start_date DATE,
store_id NUMBER,
inventory_id NUMBER(6),
qty_sold NUMBER(3)
)
PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
);
Here, two partitions have been defined and an interval of one month has been specified. If data is loaded into this table with a later date than the greatest defined partition, Oracle will automatically create a new partition for the new month. In the table above, the greatest defined interval is between July 1, 2015 and August 1, 2015.
Inserting a row that has a date later than August 1, 2015 would raise an error with normal range partitioning. However, with interval partitioning, Oracle determines the high value of the defined range partitions, called the transition point, and creates new partitions for data that is beyond that high value.
insert into pos_data (start_date, store_id, inventory_id, qty_sold)
values ( '15-AUG-07', 1, 1, 1);
SELECT TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE
FROM DBA_TAB_PARTITIONS WHERE
TABLE_NAME='POS_DATA'
ORDER BY PARTITION_NAME;
PARTITION_NAME HIGH_VALUE
POS_DATA_P0 TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
POS_DATA_P1 TO_DATE(' 2015-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P81 TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
Notice that a system generated partition named SYS_P81 has been created upon inserting a row with a partition key greater than the transition point. Oracle will manage the creation of new partitions for any value beyond the high value. Therefore, the values do not need to be inserted in sequence.
Since the partitions are named automatically, Oracle has added a new syntax in order to reference specific partitions effectively. The normal way to reference a specific partition is to use the partition (partition_name) in the query:
select * from pos_data partition (SYS_P81);
However, it would be cumbersome to look up the system generated partition name each time. Therefore, the new syntax to specify a partition is by using the partition for (DATE) clause in the query:
select * from pos_data partition for (to_date('15-AUG-2007','dd-mon-yyyy'));
Another useful feature of partitioning is the ability to distribute partitions across different tablespaces. With interval partitioning, this can be accomplished by naming all of the tablespaces in the table definition's "store in" clause. The system created partitions are then assigned to different tablespaces in a round robin manner. For example, if the choice was to distribute the table across three tablespaces - tablespaceA, tablespaceB, and tablespac eC - use the following clause in the table definition.
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
STORE IN (tablespaceA, tablespaceB, tablespaceC)
Restrictions on Interval Partitioning
There are a few restrictions on interval partitioning that must be taken into consideration before deciding if it is appropriate for the business requirement:
- Cannot be used for index organized tables
- Must use only one partitioning key column and it must be a DATE or NUMBER
- Cannot create domain indexes on interval partitioned tables
This feature should be used as an enhancement to range partitioning when uniform distribution of range intervals for new partitions is acceptable. If the requirement demands the use of uneven intervals when adding new partitions, then interval partitioning would not be the best solution.
Interval Partitioning Commands
There are a few new commands to manage interval partitioning. First, convert a range partitioned table to use interval partitioning by using alter table <table_name> set interval(expr).
Consider this range partitioned table:
create table pos_data_range (
start_date DATE,
store_id NUMBER,
inventory_id NUMBER(6),
qty_sold NUMBER(3)
)
PARTITION BY RANGE (start_date)
(
PARTITION pos_data_p0 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
PARTITION pos_data_p1 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
);
If a row with a date of August 15, 2015 is inserted into the table, it will cause an error.
SQL> insert into pos_data_range (start_date, store_id, inventory_id, qty_sold)
2 values ( '15-AUG-07', 1, 1, 1);
insert into pos_data_range (start_date, store_id, inventory_id, qty_sold)
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
This range partitioned table can easily be converted to use interval partitioning by using the following command:
alter table pos_data_range set INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));
Interval partitioning is now enabled, and the row with 15-AUG-07 can be inserted without error since Oracle will automatically create the new partition. To convert the table back to only range partitioning, use the following command:
alter table pos_data_range set INTERVAL();
The table is converted back to a range partitioned table and the boundaries for the interval partitions are set to the boundaries for the range partitions.
Using the same syntax, the interval can also be changed for existing interval partitioned tables. If changing the original table to be partitioned every three months instead of monthly, use:
alter table pos_data set INTERVAL(NUMTOYMINTERVAL(3, 'MONTH'));
After inserting a row with the date of 15-NOV-07, a new partition is automatically generated with a high value of 01-DEC-07.
insert into
pos_data (start_date, store_id, inventory_id, qty_sold)
values
('15-NOV-07', 1, 1, 1);
SELECT
TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE
FROM
Remote DBA_TAB_PARTITIONS
WHERE
TABLE_NAME='POS_DATA'
ORDER BY
PARTITION_NAME;
PARTITION_NAME HIGH_VALUE
POS_DATA_P0 TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
POS_DATA_P1 TO_DATE(' 2015-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P81 TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P84 TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
The tablespace storage of the interval partitioned table can also be changed using a similar syntax. For example, when using a round robin tablespace assignment for the table between tablespace1 to tablespace3, issue the following command:
alter table pos_data set STORE IN(tablespace1, tablespace2, tablespace3);
Oracle interval partitioning offers a very useful extension to range partitioning. This greatly improves the manageability of range partitioned tables. In addition to providing system generated new partitions, Oracle has provided a new syntax to simplify the reference of specific partitions.
Realtime Example
CREATE TABLE EASYOWNER.EASYTABLE_PART(ADDRESSID NUMBER(38),
BANK_ID VARCHAR2(8 CHAR),
ISADDRESSVERIFIED VARCHAR2(1 CHAR),
NAME_ALT1 VARCHAR2(80 CHAR),
CORP_REP_ID NUMBER(38),
CITY VARCHAR2(200 CHAR),
START_DATE DATE,
END_DATE DATE,
RESIDENTIALSTATUS VARCHAR2(25 CHAR),
USERFIELD5 VARCHAR2(200 CHAR),
USERDATEFIELD1 DATE,
NAME VARCHAR2(100 CHAR),
SALUTATION_CODE VARCHAR2(5 CHAR),
PHONENO1 VARCHAR2(50 CHAR),
PHONENO1LOCALCODE VARCHAR2(20 CHAR),
CITY_CODE VARCHAR2(5 CHAR),
STATE_CODE VARCHAR2(5 CHAR),
COUNTRY_CODE VARCHAR2(5 CHAR),
CORE_CUST_ID VARCHAR2(9 CHAR),
NONCUSTOMERID NUMBER(38),
IS_ADDRESS_PROOF_RCVD VARCHAR2(1 CHAR),
SWIFT_NAME VARCHAR2(35 CHAR),
TMDATE DATE DEFAULT sysdate,
BUSINESS_DATE DATE,
CREATED_DATE DATE,
LST_UPD_JOB_ID NUMBER(19))
NOCOMPRESS
TABLESPACE EASYOWNER_DATA
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
PARTITION BY RANGE (BUSINESS_DATE)
INTERVAL( NUMTODSINTERVAL(1,'DAY'))
(
PARTITION part1 VALUES LESS THAN (TO_DATE(' 2022-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS
TABLESPACE EASYOWNER_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
);
Range Interval -Hash composite Partition with Real-Time Example
1)
Creating a composite interval-hash partitioned table
CREATE TABLE sales_interval_hash
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY HASH (cust_id) SUBPARTITIONS 4
(PARTITION before_2016 VALUES LESS THAN (TO_DATE('01-JAN-2016','dd-MON-yyyy'))
)
SELECT SUBSTR(TABLE_NAME,1,32), SUBSTR(PARTITION_NAME,1,32), SUBSTR(SUBPARTITION_NAME,1,32)
FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME ='SALES_INTERVAL_HASH'
2) CREATE TABLE "testuser"."TEST_TABLE_BKP_PART" ("TEST_SRL_NUM" VARCHAR2(12 CHAR),
"B2K_ID" VARCHAR2(12 CHAR),
"B2K_TYPE" VARCHAR2(5 CHAR),
"SOL_ID" VARCHAR2(8 CHAR),
"TEST_DATE" DATE,
"TEST_AMT" NUMBER(20,4),
"SYS_MAN_FLG" CHAR(1),
"LAST_TESTSMITTED_BY" VARCHAR2(15 CHAR)
)
TABLESPACE TEST_TBLSPC
PARTITION BY RANGE (TEST_DATE) INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY HASH ("TEST_SRL_NUM") SUBPARTITIONS 128 STORE IN (TEST_TBLSPC)
(
PARTITION part1 VALUES LESS THAN (TO_DATE(' 2021-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE TEST_TBLSPC,
PARTITION part2 VALUES LESS THAN (TO_DATE(' 2021-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part3 VALUES LESS THAN (TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part4 VALUES LESS THAN (TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part5 VALUES LESS THAN (TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part6 VALUES LESS THAN (TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part7 VALUES LESS THAN (TO_DATE(' 2022-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part8 VALUES LESS THAN (TO_DATE(' 2022-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part9 VALUES LESS THAN (TO_DATE(' 2022-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part10 VALUES LESS THAN (TO_DATE(' 2022-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part11 VALUES LESS THAN (TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part12 VALUES LESS THAN (TO_DATE(' 2022-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part13 VALUES LESS THAN (TO_DATE(' 2022-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part14 VALUES LESS THAN (TO_DATE(' 2022-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part15 VALUES LESS THAN (TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part16 VALUES LESS THAN (TO_DATE(' 2023-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part17 VALUES LESS THAN (TO_DATE(' 2023-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part18 VALUES LESS THAN (TO_DATE(' 2023-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part19 VALUES LESS THAN (TO_DATE(' 2023-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part20 VALUES LESS THAN (TO_DATE(' 2023-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part21 VALUES LESS THAN (TO_DATE(' 2023-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part22 VALUES LESS THAN (TO_DATE(' 2023-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part23 VALUES LESS THAN (TO_DATE(' 2023-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part24 VALUES LESS THAN (TO_DATE(' 2023-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,
PARTITION part25 VALUES LESS THAN (TO_DATE(' 2023-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE TEST_TBLSPC
)
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;
Table Purging Activity
alter table sales move partition sales_2009 compress;
alter table sales move partition sales_2009 tablespace crappy_old_disk;
alter tablespace crappy_old_disk read only;
alter index sales_IX partition sales_ix_2009 unusable;
From 12c onward
alter table sales modify partition sales_2012 indexing off;
Exchange partition
---------------
alter table sales exchange partition sales_2013 with table New_table;
