Another new feature in the partitioning aerea is the automatic list partitioning. Like with the interval partitioning that came with ORACLE 11.1 the automatic list partitioning creates new partitions as they are required.
The following lines show an example of automatic list partitioning:
Example:
-- =============================================================================
-- Create an automatic list partitioned table
-- =============================================================================
CREATE TABLE employees_part (
employee_id NUMBER(4)
,first_name VARCHAR2(30)
,last_name VARCHAR2(30)
,email VARCHAR2(30)
,phone_number VARCHAR2(20)
,hire_date DATE
,salary NUMBER(6,0)
,commission_pct NUMBER(3,2)
,manager_id NUMBER(4)
,department_id NUMBER(4)
)
PARTITION BY LIST (department_id) AUTOMATIC (
PARTITION P_10 VALUES (10)
);
SELECT partition_name, high_value, partition_position
FROM all_tab_partitions
WHERE table_name = 'EMPLOYEES_PART';
PARTITION_NAME HIGH_VALUE PARTITION_POSITION
-------------------- -------------------- ------------------
P_10 10 1
Adding new values to the partitioned table:
In a first step I try to create a list partitioned table having a global and a local index.
INSERT INTO employees_part (employee_id , first_name , last_name , email , phone_number , hire_date , salary , commission_pct , manager_id , department_id) SELECT employee_id , first_name , last_name , email , phone_number , hire_date , salary , commission_pct , manager_id , department_id FROM employees; 107 rows inserted.
SELECT partition_name, high_value, partition_position FROM all_tab_partitions WHERE table_name = 'EMPLOYEES_PART'; PARTITION_NAME HIGH_VALUE PARTITION_POSITION -------------------- -------------------- ------------------ P_10 10 1 SYS_P2161 90 2 SYS_P2162 60 3 SYS_P2163 100 4 SYS_P2164 30 5 SYS_P2165 50 6 SYS_P2166 80 7 SYS_P2167 NULL 8 SYS_P2168 20 9 SYS_P2169 40 10 SYS_P2170 70 11 SYS_P2171 110 12
How to find out whether a partitioned table is set to automatic?
The information whether a table is set to automatic is stored in the …_PART_TABLES dictionary views.
SELECT table_name, partitioning_type, autolist
FROM user_part_tables;
TABLE_NAME PARTITIONING_TYPE AUTOLIST
--------------- ------------------ --------
EMPLOYEES_PART LIST YES
Conclusion:
The new automatic list partitioning feature dispenses us from the duty to either have to manually (or programatically) create new partitions for new values or having a default partition to store all values that are not covered by the existing partitions.
This burdon was not that big…but it is still a useful simplification.