Enhanced Partitioning Oracle Database 12C release 2 (12.2)

Posted on by

This article describes new options available in Oracle Database Release 12.2 for partitioning:

  • extensions for list partitioning
    • automatic list partitioned table
    • multi-column list partitioned table
  • read-only partitions and subpartitions
  • filtering maintenance operations
  • creating a table for exchange with a partitioned table
  • online converting non-partitioned table to a partitioned table
  • online split partition and subpartition

Extensions for list partitioning

Automatic list partitioned table

New clause AUTOMATIC enables to create automatically partitions for LIST partitioned tables. It requires at least one partition to be defined manually.

CREATE TABLE test_tbl
(
   id1   NUMBER,
   id2   NUMBER
)
  PARTITION BY LIST (id1) AUTOMATIC
 (PARTITION p_1 VALUES (1)
);

SELECT table_name, partition_name, high_value
  FROM user_tab_partitions
 WHERE table_name='TEST_TBL';

TABLE_NAME  PARTITION_NAME HIGH_VALUE
----------- -------------- -----------
TEST_TBL    P_1            1

INSERT INTO test_tbl VALUES(2,1);
INSERT INTO test_tbl VALUES(3,1);
INSERT INTO test_tbl VALUES(4,1);

COMMIT;

TABLE_NAME  PARTITION_NAME HIGH_VALUE
----------- -------------- -----------
TEST_TBL    P_1            1
TEST_TBL    SYS_P415       2
TEST_TBL    SYS_P416       3
TEST_TBL    SYS_P417       4

It’s reflected in dictionary columns of USER|ALL|DBA_PART_TABLES

SELECT table_name, partitioning_type, autolist, autolist_subpartition
  FROM user_part_tables
 WHERE table_name='TEST_TBL';

TABLE_NAME  PARTITIONING_TYPE  AUTOLIST  AUTOLIST_SUBPARTITION
----------- ------------------ --------- ----------------------
TEST_TBL    LIST               YES       NO

Multi-column list partitioning

In this release it’s possible to define more than one column for LIST partitioning like

PARTITION BY LIST (column1,..,columnN)

Here is simple example. AUTOMATIC option will not work here

CREATE TABLE test_tbl
(
  id1       NUMBER, 
  id2       NUMBER, 
  id3       NUMBER,
  id4       NUMBER
)
PARTITION BY LIST (id1, id2, id3)
(
  PARTITION p_1       VALUES ((1,2,3), (2,5,7)),
  PARTITION p_2       VALUES ((1,3,5), (2,6,22)),
  PARTITION p_default VALUES (DEFAULT)
);
 SELECT table_name, partitioning_type
   FROM user_part_tables
  WHERE table_name='TEST_TBL';

TABLE_NAME  PARTITIONING_TYPE
----------- ------------------
TEST_TBL    LIST

SELECT name, object_type, column_name 
  FROM user_part_key_columns
 WHERE NAME='TEST_TBL';

NAME      OBJECT_TYPE  COLUMN_NAME
--------- ------------ ------------
TEST_TBL  TABLE        ID1
TEST_TBL  TABLE        ID2
TEST_TBL  TABLE        ID3

Read-only partitions and subpartitions

In 12.2 partitions and subpartitions can be protected against unintentional DML operations.

CREATE TABLE test_tbl 
(
  id1 NUMBER,
  id2 NUMBER,
  id3 NUMBER
) 
READ WRITE
PARTITION BY RANGE (id1)
SUBPARTITION BY LIST (id2)
( 
  PARTITION p_1 VALUES LESS THAN (10) READ ONLY
  ( 
    SUBPARTITION s_1_1 VALUES (5) READ WRITE,
    SUBPARTITION s_1_d VALUES (DEFAULT)
  ),
  PARTITION p_2 VALUES LESS THAN (20)
  ( 
    SUBPARTITION s_2_1 VALUES (7) READ ONLY,
    SUBPARTITION s_2_d VALUES (DEFAULT)
  )
);

It’s reflected in database dictionary

SELECT table_name, def_read_only
  FROM user_part_tables
 WHERE table_name='TEST_TBL';

TABLE_NAME  DEF_READ_ONLY
----------- --------------
TEST_TBL    NO

SELECT table_name, partition_name, read_only
  FROM user_tab_partitions
 WHERE table_name='TEST_TBL';

TABLE_NAME  PARTITION_NAME  READ_ONLY
----------- --------------- ---------
TEST_TBL    P_1             YES
TEST_TBL    P_2             NONE

SELECT table_name, partition_name, subpartition_name, read_only
  FROM user_tab_subpartitions
 WHERE table_name='TEST_TBL';

TABLE_NAME  PARTITION_NAME  SUBPARTITION_NAME  READ_ONLY
----------- --------------- ------------------ ----------
TEST_TBL    P_1             S_1_1              NO
TEST_TBL    P_1             S_1_D              YES
TEST_TBL    P_2             S_2_1              YES
TEST_TBL    P_2             S_2_D              NO

Let’s load data

--it goes to subpartition s_1_1
INSERT INTO test_tbl VALUES(5,5, 100);

1 row inserted.

--it goes to subpartition s_1_d and fails 
INSERT INTO test_tbl VALUES(5,15, 100);

ORA-14466: Data in a read-only partition 
            or subpartition cannot be modified

--it goes to subpartition s_2_1 and fails
INSERT INTO test_tbl VALUES(15,7, 100);

ORA-14466: Data in a read-only partition 
            or subpartition cannot be modified

--it goes to subpartition s_2_d
INSERT INTO test_tbl VALUES(15,8, 100);

1 row inserted.

Filtering maintenance operations

ALTER TABLE … MOVE command is extended with possibility to add filter clause INLUDING ROWS WHERE. It enables during rebuild to remove unwanted records. It’s beautiful fantastic option ! No more CTAS and exchange partitions DELETE and so on …

Let’s prepare test data

CREATE TABLE test_tbl
(
 id1 NUMBER,
 id2 NUMBER,
 id3 NUMBER
)
PARTITION BY RANGE(id1)
(
  PARTITION p1 VALUES LESS THAN(10),
  PARTITION p2 VALUES LESS THAN(MAXVALUE)
);

INSERT INTO test_tbl VALUES(1, 1, 1);
INSERT INTO test_tbl VALUES(2, 2, 2);
INSERT INTO test_tbl VALUES(3, 3, 3);

COMMIT;

SELECT * FROM test_tbl PARTITION(p1);

ID1  ID2  ID3
---  ---  ---
1    1    1
2    2    2
3    3    3

Simple rebuild with filtering 🙂

ALTER TABLE test_tbl
MOVE PARTITION p1
INCLUDING ROWS WHERE id1 < 3;

SELECT * FROM test_tbl PARTITION(p1);

ID1  ID2  ID3
---  ---  ---
1    1    1
2    2    2

It works for non-partitioned tables as well

CREATE TABLE test_tbl
(
 id1 NUMBER,
 id2 NUMBER,
 id3 NUMBER
);


INSERT INTO test_tbl VALUES(1, 1, 1);
INSERT INTO test_tbl VALUES(2, 2, 2);
INSERT INTO test_tbl VALUES(3, 3, 3);

COMMIT;

ALTER TABLE test_tbl MOVE
INCLUDING ROWS WHERE id1 < 3;

SELECT * FROM test_tbl PARTITION(p1);

ID1  ID2  ID3
---  ---  ---
1    1    1
2    2    2

Creating a table for exchange with a partitioned table

This is new way to create temporary table for exchange operations. New command FOR EXCHANGE WITH creates temporary tables preserving all features of source table. It’s not the same as CREATE TABLE AS SELECT commonly called CTAS option.

Sample data

CREATE TABLE test_tbl
(
 id1 NUMBER,
 id2 NUMBER,
 id3 NUMBER
)
PARTITION BY RANGE(id1)
(
  PARTITION p1 VALUES LESS THAN(10),
  PARTITION p2 VALUES LESS THAN(MAXVALUE)
);

INSERT INTO test_tbl VALUES(1, 1, 1);
INSERT INTO test_tbl VALUES(2, 2, 2);
INSERT INTO test_tbl VALUES(3, 3, 3);

COMMIT;

SELECT * FROM test_tbl PARTITION(p1);

Now it’s time to create two exchange tables using old method CREATE TABLE AS SELECT and with new one FOR EXCHANGE WITH.

But before I do that let’s make one column unusable. It will cause problems for CTAS option.

ALTER TABLE test_tbl SET UNUSED COLUMN id3;
CREATE TABLE test_tbl_exchg
FOR EXCHANGE WITH TABLE test_tbl;

CREATE TABLE ctas_test_tbl_exchg
AS 
SELECT * FROM test_tbl 
WHERE ROWNUM < 0;

Now simple testes that shows it’s different. It fails for CTAS and raises error

ALTER TABLE test_tbl 
EXCHANGE PARTITION p1 
WITH TABLE ctas_test_tbl_exchg;

SQL Error: ORA-14097: column type or size mismatch 
in ALTER TABLE EXCHANGE PARTITION

And works for new option

ALTER TABLE test_tbl 
EXCHANGE PARTITION p1 
WITH TABLE test_tbl_exchg;

Table TEST_TBL altered

Some people could say that I can drop unused columns. It’s not always possible especially for exadata types more in the following article:

Exchange partitions for compressed table and unused columns ORA-39726, ORA-14097

Summary for it

The following list is a summary of the effects of the CREATE TABLE FOR EXCHANGE WITH DDL operation:

  • The use case of this DDL operation is to facilitate creation of a table to be used for exchange partition DDL.
  • The operation creates a clone of the for exchange table in terms of column ordering and column properties.
  • Columns cannot be renamed. The table being created inherits the names from the for exchange table.
  • The only logical property that can be specified during the DDL operation is the partitioning specification of the table.The partitioning clause is only relevant for the exchange with a partition of a composite-partitioned table. In this case, a partition with n subpartitions is exchanged with a partitioned table with n partitions matching the subpartitions. You are responsible for the definition of the partitioning clause for this exchange in this scenario.The subpartitioning can be asymmetrical across partitions. The partitioning clause has to match exactly the subpartitioning of the partition to being exchanged.
  • The physical properties which can be specified are primarily table segment attributes.
  • Column properties copied with this DDL operation include, but are not limited to, the following: unusable columns, invisible columns, virtual expression columns, functional index expression columns, and other internal settings and attributes.

Online converting non-partitioned table to a partitioned table

Nonpartitioned tables can be now converted to partitioned table online. Associated indexes are maintained and can be partitioned as well. The conversion has no impact on DML operations.

CREATE TABLE test_tbl
(  
  id1 NUMBER,
  id2 NUMBER,
  id3 NUMBER,
  id4 NUMBER
);

CREATE INDEX test_tbl_idx1 ON test_tbl(id2);
CREATE INDEX test_tbl_idx2 ON test_tbl(id3, id4);

You need to establish two session. In first one just insert one row

--session1

INSERT INTO test_tbl VALUES(10, 10, 20, 30);

1 row inserted.

if you start DDL to transform table TEST_TBL to partitioned table it will hang

--session2

ALTER TABLE test_tbl
MODIFY
PARTITION BY RANGE (id1) INTERVAL (100)
(
  PARTITION P1 VALUES LESS THAN (100),
  PARTITION p2 VALUES LESS THAN (500)
) ONLINE
UPDATE INDEXES
(
  test_tbl_idx1 LOCAL,
  test_tbl_idx2 GLOBAL 
  PARTITION BY RANGE (id3)
  ( 
    PARTITION ip1 VALUES LESS THAN (MAXVALUE)
  )
);

But still you can execute new DML on new sessions

--session 3

INSERT INTO test_tbl VALUES(10, 10, 20, 30);

1 row inserted.

You need to commit session1 and session3 so DDL can complete in session2. I really like how it’s done :o). No cascade hangouts for DMLs

Online split partition and subpartition

Split partitions and subpartitions can be executed with new keyword ONLINE. It enables to run concurrent DML operations when DDL is running.

CREATE TABLE test_tbl
(  
  id1 NUMBER,
  id2 NUMBER,
  id3 NUMBER,
  id4 NUMBER
)
PARTITION BY RANGE(id1)
(
  PARTITION p1 VALUES LESS THAN(10)
);

CREATE INDEX test_tbl_idx1 ON test_tbl(id2) LOCAL;
CREATE INDEX test_tbl_idx2 ON test_tbl(id3, id4) LOCAL;

You need to establish two session. In first one just insert one row

--session1

INSERT INTO test_tbl VALUES(5, 10, 20, 30);

1 row inserted.

If you start DDL to transform table TEST_TBL to partitioned table it will hang

--session2

ALTER TABLE test_tbl 
SPLIT PARTITION p1 AT(5) INTO
(
  PARTITION p1,
  PARTITION p2
) 
ONLINE;

But still you can execute new DML on new sessions

--session 3

INSERT INTO test_tbl VALUES(8, 10, 20, 30);

1 row inserted.

You need to commit session1 and session3 so DDL can complete in session2. I really like how it’s done :o). No cascade hangouts for DMLs.

Have a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.