From Oracle 12.2, you can LIST partition a table using multiple columns:
CREATE TABLE regional_orders(
id NUMBER,
country_code VARCHAR2(5),
region_code VARCHAR2(20),
customer_id NUMBER,
order_date DATE,
order_total NUMBER(8,2),
CONSTRAINT regional_orders_pk PRIMARY KEY (id)
)
PARTITION BY LIST (country_code,region_code)
(
PARTITION NORWAY_SOUTH_WEST VALUES (
( 'NOR','AUST-AGDER'),
( 'NOR','VEST-AGDER'),
( 'NOR','ROGALAND')
),
PARTITION NORWAY_WEST VALUES (
( 'NOR','HORDALAND'),
( 'NOR','SOGN OG FJORDANDE'),
( 'NOR','MØRE OG ROMSDAL')
),
PARTITION NORWAY_CENTRAL VALUES (
( 'NOR','TRØNDELAG')
),
PARTITION NORWAY_EAST VALUES (
( 'NOR','BUSKERUD'),
( 'NOR','HEDMARK'),
( 'NOR','TELEMARK')
)
);
Incoming data that has any of the combinations below, will enter the partition NORWAY_SOUTH_WEST
'NOR','AUST-AGDER'
'NOR','VEST-AGDER'
'NOR','ROGALAND'
This functionality removes the need for LIST-LIST subpartitioning, as far as I can see.
Some queries to verify that the partitions exist:
select partition_name,partition_position,tablespace_name
from user_tab_partitions
where table_name='REGIONAL_ORDERS';
| PARTITION_NAME | PARTITION_POSITION | TABLESPACE_NAME |
| NORWAY_SOUTH_WEST | 1 | USERS |
| NORWAY_WEST | 2 | USERS |
| NORWAY_CENTRAL | 3 | USERS |
| NORWAY_EAST | 4 | USERS |
SELECT PARTITIONING_TYPE,PARTITION_COUNT,PARTITIONING_KEY_COUNT
FROM USER_PART_TABLES
where table_name='REGIONAL_ORDERS';
| PARTITIONING_TYPE | PARTITION_COUNT | PARTITIONING_KEY_COUNT |
| LIST | 4 | 2 |
SELECT column_name,column_position
FROM USER_PART_KEY_COLUMNS
where name='REGIONAL_ORDERS';
| COLUMN_NAME | COLUMN_POSITION |
| COUNTRY_CODE | 1 |
| REGION_CODE | 2 |
Let's insert a row:
insert into regional_orders
values (1,'NOR','ROGALAND',3344,'04.01.2019', 1000);
commit;
Analyze the table:
exec DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'SCOTT',TabName => 'REGIONAL_ORDERS');
Verify:
select partition_name, num_rows
from user_tab_partitions
where table_name='REGIONAL_ORDERS';
| PARTITION_NAME | NUM_ROWS |
| NORWAY_CENTRAL | 0 |
| NORWAY_EAST | 0 |
| NORWAY_SOUTH_WEST | 1 |
| NORWAY_WEST | 0 |
Insert with a different value:
insert into regional_orders
values (2,'NOR','BUSKERUD',3345,'04.01.2019', 1200);
COMMIT;
Analyze again:
exec DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'SCOTT',TabName => 'REGIONAL_ORDERS');
Verify:
select partition_name, num_rows
from user_tab_partitions
where table_name='REGIONAL_ORDERS';
| PARTITION_NAME | NUM_ROWS |
| NORWAY_CENTRAL | 0 |
| NORWAY_EAST | 1 |
| NORWAY_SOUTH_WEST | 1 |
| NORWAY_WEST | 0 |
If you send in a value that is not covered in your pre-specified list, your statement will fail:
insert into regional_orders
values (3,'NOR','NORDLAND',3346,'04.01.2019', 1100);
ORA-14400: inserted partition key does not map to any partition
The solution to this is to either create your table with a default partition or to use the Oracle 12.2 new feature of automatic list partitioning.
Using a default partition will be as follows:
CREATE TABLE regional_orders(
id NUMBER,
country_code VARCHAR2(5),
region_code VARCHAR2(20),
customer_id NUMBER,
order_date DATE,
order_total NUMBER(8,2),
CONSTRAINT regional_orders_pk PRIMARY KEY (id)
)
PARTITION BY LIST (country_code,region_code)
(
PARTITION NORWAY_SOUTH_WEST VALUES (
( 'NOR','AUST-AGDER'),
( 'NOR','VEST-AGDER'),
( 'NOR','ROGALAND')
),
PARTITION NORWAY_WEST VALUES (
( 'NOR','HORDALAND'),
( 'NOR','SOGN OG FJORDANDE'),
( 'NOR','MØRE OG ROMSDAL')
),
PARTITION NORWAY_CENTRAL VALUES (
( 'NOR','TRØNDELAG')
),
PARTITION NORWAY_EAST VALUES (
( 'NOR','BUSKERUD'),
( 'NOR','HEDMARK'),
( 'NOR','TELEMARK')
),
PARTITION NORWAY_REST VALUES (DEFAULT)
)
;
Using the AUTOMATIC keyword will be as easy as adding the keyword AUTOMATIC to your partitioning-clause and to remove the specification for your overflow partition:
CREATE TABLE regional_orders(
id NUMBER,
country_code VARCHAR2(5),
region_code VARCHAR2(20),
customer_id NUMBER,
order_date DATE,
order_total NUMBER(8,2),
CONSTRAINT regional_orders_pk PRIMARY KEY (id)
)
PARTITION BY LIST (country_code,region_code) AUTOMATIC
(
PARTITION NORWAY_SOUTH_WEST VALUES (
( 'NOR','AUST-AGDER'),
( 'NOR','VEST-AGDER'),
( 'NOR','ROGALAND')
),
PARTITION NORWAY_WEST VALUES (
( 'NOR','HORDALAND'),
( 'NOR','SOGN OG FJORDANDE'),
( 'NOR','MØRE OG ROMSDAL')
),
PARTITION NORWAY_CENTRAL VALUES (
( 'NOR','TRØNDELAG')
),
PARTITION NORWAY_EAST VALUES (
( 'NOR','BUSKERUD'),
( 'NOR','HEDMARK'),
( 'NOR','TELEMARK')
)
)
;
New values will be added automatically and given system-generated partition names.
There is an obvious trade-off here: if you use automation, you will not be able to give your partition logical names and place values logically belong together, in the same partition.
For example, the values
'NOR','NORDLAND'
may logically belong to the same partition as the values
'NOR','TROMS'
and
'NOR','FINNMARK'
If I knew such a value may be sent at the time of table creation, I would of course create another partition, like this:
PARTITION NORWAY_NORTH VALUES (
( 'NOR','NORDLAND'),
( 'NOR','TROMS'),
( 'NOR','FINNMARK')
)
If you use a default partition, you will always be able to save your incoming rows, and you have full controll over your partition names and their values.
But new rows end up in an overflow partition together with any other record that cannot find its way into a properly named and configured partition.
You may need to split your default partition later, to satisify your business needs or to reduce the size of your default partition.
