A number of enhancements to the Oracle database Partitioning option have been introduced in Oracle Database 12c Release 2.
These include:
- Automatic List Partitioning
- Multi-Column List Partitioning
- Read-only Partitions
- Filtered Partition maintenance operations
- Online conversion of non-partitioned to partitioned table
- Partitioned External Tables
Similar to the interval partitioning method which was introduced in Oracle Database 12c Release 1, one of the new partitioning features introduced in Oracle 12c Release 2 is the Automatic List Partitioning feature which makes the partitioned table easier to manage.
The ability to partition a table based on distinct values in a column has been around since quite some time, but what is new in Oracle database 12.2 is that we can initially partition the table based on values that are known and present in the column we would like to partition the table on. As data is loaded into the table, new partitions are automatically created by the database if the loaded partition key value does not correspond to any of the existing partitions.
In Oracle Database 12c Release 2, we can now partition a table based on list of values of multiple columns and not just a single column as in the case of previous versions. This is called Multi-Column List Partitioning.
So if we have a very large table containing sales data for the entire country, we can partition the table based on a state and as well as a city located in the same state.
Another new feature is Read-Only Partitions. So, the entire table is not made read-only, but just some partitions in the table can be made read-only. Maybe we have a requirement to store historical data and older partitions in the table can be made read-only to prevent any DML operations on such ‘old’ data.
As DBA’s, one of the common partition maintenance operations we had to carry out quite regularly was moving partitions – maybe older partitions of the table are moved to a different tablespace hosted on low cost storage because this data is infrequently accessed. Now in Oracle Database 12.2, we can combine partition maintenance operations like MOVE PARTITION, MERGE PARTITION and SPLIT PARTITION with data filtering. So for example, while we are moving data of a particular partition, we can also filter data which will be moved. By specifying a filtering predicate via the INCLUDING ROWS keyword, we can determine what data should be preserved when the partition maintenance operation is carried out and what data can be discarded.
But one of the best new 12.2 features is that we can now convert a non-partitioned table into a partitioned table very easily via the ALTER TABLE command with the MODIFY clause as well as while concurrent DML operations are being performed on the table. So the conversion of the table is an online process and with the UPDATE INDEXES clause we can also convert any indexes on the table into either local or global partitioned indexes as well.
We can also now create a Partitioned External Table. So basically, we are creating a table based on data hosted outside the Oracle database and we partition the table which contains this external data for better optimization of queries which would be really beneficial if we are dealing with very large volumes of these external data sets.
Let us have a look at all these 12c Release 2 Partitioning new features.
First create a test non-partitioned table with an index and insert some data into the table.
SQL> CREATE TABLE sales_by_state
(sales_state VARCHAR2(3),
sales_city VARCHAR2(20),
sales_quarter NUMBER,
sales_amount NUMBER);
Table created.
SQL> CREATE INDEX sales_state_ind ON sales_by_state (sales_state);
Index created.
SQL> INSERT INTO sales_by_state
VALUES
('WA','PERTH',1,10000);
1 row created.
SQL> INSERT INTO sales_by_state
VALUES
('WA','ALBANY',1,9500);
1 row created.
SQL> INSERT INTO sales_by_state
VALUES
('VIC','MELBOURNE',1,75000);
1 row created.
SQL> INSERT INTO sales_by_state
VALUES
('VIC','GEELONG',1,6500);
1 row created.
SQL> INSERT INTO sales_by_state
VALUES
('NSW','SYDNEY',1,50000);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM sales_by_state;
SAL SALES_CITY SALES_QUARTER SALES_AMOUNT
--- -------------------- ------------- ------------
WA PERTH 1 10000
WA ALBANY 1 9500
VIC MELBOURNE 1 75000
VIC GEELONG 1 6500
NSW SYDNEY 1 50000
We now convert the non-partitioned table to a multi-column List-Partitioned table online and also create a local partitioned index on the table as well as part of the same operation.
SQL> ALTER TABLE system.sales_by_state
MODIFY
PARTITION BY LIST (sales_state,sales_city) AUTOMATIC
(PARTITION p_wa_1 VALUES ('WA','PERTH'),
PARTITION p_wa_2 values ('WA','ALBANY'),
PARTITION p_vic_1 values ('VIC','MELBOURNE'),
PARTITION p_vic_2 values ('VIC','GEELONG'),
PARTITION p_nsw_1 values ('NSW','SYDNEY'))
ONLINE
UPDATE INDEXES;
Table altered.
SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SALES_BY_STATE';
PARTITION_NAME
--------------------------------------------------------------------------------
P_NSW_1
P_VIC_1
P_VIC_2
P_WA_1
P_WA_2
SQL> select partition_name from user_IND_PARTITIONS WHERE INDEX_NAME='SALES_STATE_IND';
PARTITION_NAME
--------------------------------------------------------------------------------
P_NSW_1
P_VIC_1
P_VIC_2
P_WA_1
P_WA_2
Notice what happens when we insert some new rows into the partitioned table with a state and city value not already present in the table.
SQL> INSERT INTO sales_by_state
VALUES
('SA','ADELAIDE',1,10500);
1 row created.
SQL> INSERT INTO sales_by_state
VALUES
('SA','ADELAIDE',2,8000);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SALES_BY_STATE';
PARTITION_NAME
--------------------------------------------------------------------------------
P_NSW_1
P_VIC_1
P_VIC_2
P_WA_1
P_WA_2
SYS_P1323
6 rows selected.
SQL> SELECT COUNT(*) FROM sales_by_state PARTITION(SYS_P1323);
COUNT(*)
----------
2
Make one of the partitions in the table read-only.
SQL> ALTER TABLE sales_by_state MODIFY PARTITION p_wa_1 READ ONLY;
Table altered.
SQL> INSERT INTO sales_by_state
VALUES
('WA','PERTH',2,5000);
INSERT INTO sales_by_state
*
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.
Next, we will create an External partitioned table. We have 4 flat files which contain sales information for particular states and we would like to now create a table which is partitioned by the state name.
SQL> !cat salesdata_wa.txt WA PERTH 1 10000 WA ALBANY 1 9500 SQL> !cat salesdata_nsw.txt NSW SYDNEY 1 50000 SQL> !cat salesdata_vic.txt VIC MELBOURNE 1 75000 VIC GEELONG 1 6500 SQL> !cat salesdata.txt SA ADELAIDE 1 10500 SA ADELAIDE 2 8000
CREATE TABLE system.sales_by_state
(sales_state VARCHAR2(3),
sales_city VARCHAR2(20),
sales_quarter NUMBER,
sales_amount NUMBER)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY data_pump_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY WHITESPACE)
)
PARTITION BY LIST (sales_state)
(PARTITION p_wa VALUES ('WA') LOCATION ('salesdata_wa.txt'),
PARTITION p_vic values ('VIC') LOCATION ('salesdata_vic.txt'),
PARTITION p_nsw values ('NSW') LOCATION ('salesdata_nsw.txt'),
PARTITION p_others values (DEFAULT) LOCATION ('salesdata.txt')
)
;
Table created.
Query the Partitioned External table.
SQL> SELECT * FROM sales_by_state; SAL SALES_CITY SALES_QUARTER SALES_AMOUNT --- -------------------- ------------- ------------ WA PERTH 1 10000 WA ALBANY 1 9500 VIC MELBOURNE 1 75000 VIC GEELONG 1 6500 NSW SYDNEY 1 50000 SA ADELAIDE 1 10500 SA ADELAIDE 2 8000 7 rows selected. SQL> SELECT * FROM sales_by_state partition (p_wa); SAL SALES_CITY SALES_QUARTER SALES_AMOUNT --- -------------------- ------------- ------------ WA PERTH 1 10000 WA ALBANY 1 9500 SQL> SELECT * FROM sales_by_state partition (p_nsw); SAL SALES_CITY SALES_QUARTER SALES_AMOUNT --- -------------------- ------------- ------------ NSW SYDNEY 1 50000
In this example we will move a partition to another tablespace maybe to archive data and as part of the partition maintenance operation we would like to only preserve rows which satisfy a particular filter which in this case is only rows in the partition belonging to the SALES_QUARTER column with the value 2. All other rows which do not conform to this filter condition will be purged as part of the MOVE PARTITION operation.
SQL> SELECT * FROM sales_by_state_part PARTITION (p_wa_); SAL SALES_CITY SALES_QUARTER SALES_AMOUNT --- -------------------- ------------- ------------ WA PERTH 1 10000 WA ALBANY 1 9500 WA BROOME 1 9700 WA PERTH 2 11000 SQL> ALTER TABLE sales_by_state_part 2 MOVE PARTITION p_wa_ TABLESPACE archive_data COMPRESS ONLINE 3 INCLUDING ROWS WHERE sales_quarter=2; Table altered. SQL> SELECT * FROM sales_by_state_part PARTITION (p_wa_); SAL SALES_CITY SALES_QUARTER SALES_AMOUNT --- -------------------- ------------- ------------ WA PERTH 2 11000 SQL> SELECT TABLESPACE_NAME FROM USER_TAB_PARTITIONS WHERE PARTITION_NAME='P_WA_'; TABLESPACE_NAME ------------------------------ ARCHIVE_DATA
