Tags

,

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.