Oracle 12cR2自动列表分区

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.

Oracle 12cR2中的自动列表分区是为了解决列表分区键中新的不同值。

• The Problem
• Automatic List Partitioning

Related articles.

• All Partitioning Articles
• Partitioning Enhancements in Oracle Database 12c Release 2 (12.2)

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.

公司正在处理来自 USA, UK 和Ireland 的客户，你希望根据国家来分区你的订单表。采用如下方式实现：

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.

 销售部门接受了一个来自不同国家新客户的一个订单，该问题无人告知DBA。

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 TABLEcommand.

自动列表分区会创建一个新的分区为新的分区键值。我们可以是用ALTER TABLE命令开启自动列表分区在一个已经存在的表上,如下：

ALTER TABLE orders SET PARTITIONING AUTOMATIC;

Alternatively we could recreate the table using the AUTOMATIC keyword.

另外我们也可以使用AUTOMATIC关键字重建表，如下：

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.

可以通过查询视图{CDB|DBA|ALL|USER}_TAB_PARTITIONS来查看自动新建的分区，如下：

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.

可以通过查询{CDB|DBA|ALL|USER}_PART_TABLES视图的 AUTOLIST 列来确定是否开启了自动列表分区。

COLUMN table_name FORMAT A30
COLUMN autolist FORMAT A8

SELECT table_name,
       autolist
FROM   user_part_tables;

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

SQL>

来源： <https://oracle-base.com/articles/12c/automatic-list-partitioning-12cr2>