DBA Hub

📋Steps in this guide1/2

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 12cconfigurationintermediate
by OracleDba
15 views
1

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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;

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>
2

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 command. Alternatively we could recreate the table using the keyword. Once automatic list partitioning is enabled we can successfully insert the new order. We can see a new partition has been created to hold the new order by querying the view. We can see automatic list partitioning is enabled by querying the column in the view. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
ALTER TABLE orders SET PARTITIONING 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;

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

1 row created.

SQL>

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>

COLUMN table_name FORMAT A30
COLUMN autolist FORMAT A8

SELECT table_name,
       autolist
FROM   user_part_tables;

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

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!