8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Automatic List Partitioning in Oracle Database 12c Release 2 (12.2)

Automatic list partitioning was introduced in Oracle Database 12c Release 2 (12.2) to solve the problem of how to handle new distinct values of the list partitioning key.

The Problem
Automatic List Partitioning

The Problem

Your company currently deals with customers from USA, UK and Ireland and you want to partition your orders table based on the country. You achieve this as follows.

DROP TABLE orders PURGE;

CREATE TABLE orders
(
  id            NUMBER,
  country_code  VARCHAR2(5),
  customer_id   NUMBER,
  order_date    DATE,
  order_total   NUMBER(8,2),
  CONSTRAINT orders_pk PRIMARY KEY (id)
)
PARTITION BY LIST (country_code)
(
  PARTITION part_usa VALUES ('USA'),
  PARTITION part_uk_and_ireland VALUES ('GBR', 'IRL')
);

INSERT INTO orders VALUES (1, 'USA', 10, SYSDATE, 10200.93);
INSERT INTO orders VALUES (2, 'USA', 11, SYSDATE, 948.22);
INSERT INTO orders VALUES (3, 'GBR', 22, SYSDATE, 300.83);
INSERT INTO orders VALUES (4, 'IRL', 43, SYSDATE, 978.43);
COMMIT;

Your sales team accept an order from a new customer that happens to be based in a different country. The problem is nobody told the DBAs.

INSERT INTO orders VALUES (5, 'BGR', 96, SYSDATE, 2178.43);
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition


SQL>

Automatic List Partitioning

Automatic list partitioning creates a partition for any new distinct value of the list partitioning key. We can enable automatic list partitioning on the existing table using the ALTER TABLE command.

ALTER TABLE orders SET PARTITIONING AUTOMATIC;

Alternatively we could recreate the table using the AUTOMATIC keyword.

DROP TABLE orders PURGE;

CREATE TABLE orders
(
  id            NUMBER,
  country_code  VARCHAR2(5),
  customer_id   NUMBER,
  order_date    DATE,
  order_total   NUMBER(8,2),
  CONSTRAINT orders_pk PRIMARY KEY (id)
)
PARTITION BY LIST (country_code) AUTOMATIC
(
  PARTITION part_usa VALUES ('USA'),
  PARTITION part_uk_and_ireland VALUES ('GBR', 'IRL')
);

INSERT INTO orders VALUES (1, 'USA', 10, SYSDATE, 10200.93);
INSERT INTO orders VALUES (2, 'USA', 11, SYSDATE, 948.22);
INSERT INTO orders VALUES (3, 'GBR', 22, SYSDATE, 300.83);
INSERT INTO orders VALUES (4, 'IRL', 43, SYSDATE, 978.43);
COMMIT;

Once automatic list partitioning is enabled we can successfully insert the new order.

INSERT INTO orders VALUES (5, 'BGR', 96, SYSDATE, 2178.43);

1 row created.

SQL>

We can see a new partition has been created to hold the new order by querying the {CDB|DBA|ALL|USER}_TAB_PARTITIONS view.

EXEC DBMS_STATS.gather_table_stats(USER, 'orders', cascade => TRUE);

SET LINESIZE 100

COLUMN table_name FORMAT A30
COLUMN partition_name FORMAT A30
COLUMN high_value FORMAT A15

SELECT table_name,
       partition_name,
       high_value,
       num_rows
FROM   user_tab_partitions
ORDER BY 1, 2;

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE        NUM_ROWS
------------------------------ ------------------------------ --------------- ----------
ORDERS                         PART_UK_AND_IRELAND            'GBR', 'IRL'             2
ORDERS                         PART_USA                       'USA'                    2
ORDERS                         SYS_P549                       'BGR'                    1

SQL>

We can see automatic list partitioning is enabled by querying the AUTOLIST column in the {CDB|DBA|ALL|USER}_PART_TABLES view.

COLUMN table_name FORMAT A30
COLUMN autolist FORMAT A8

SELECT table_name,
       autolist
FROM   user_part_tables;

TABLE_NAME                     AUTOLIST
------------------------------ --------
ORDERS                         YES

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.