The latest in Oracle Partitioning – Part 3: Auto List Partitioning

This is the third blog about new partitioning functionality in Oracle
Database 12c Release 2. It’s about the new and exciting Auto List Partitioning, an extension to List Partitioning. And yes, it works for both “old” single column list and the
new multi column list partitioned tables …

As the name already suggests, something is done ‘automatic’. In this . That’s it.

So conceptually auto-list partitioning is a similar extension to list partitioning than interval partitioning is to range partitioning. But there’s a couple of subtle differences we will discuss later. Stay on the
tour.


So let’ start and create our first simple auto-list partitioned table and see what happens:

CREATE TABLE alp (col1 NUMBER, col2
NUMBER)

PARTITION BY LIST (col1) AUTOMATIC

(PARTITION p1 VALUES (1,2,3,4),

 PARTITION p2 VALUES (5));

The first thing you’ll see is that the syntax does not look really different. There is only one new keyword named ‘AUTOMATIC’. That’s it.

As you can also see with this first little example, the fact that we use the new automatic functionality to create new partitions for every new partition key values does not preclude us from having multiple partition
key values stored in a single partition. That’s somewhat similar to interval partitioning, where you can have different ranges for partitions in the range section (as contrast to the equi-width partitions in the interval section).

Let’s now quickly look at the metadata of this newly created partition
before we want to see the automatic partition creation in action.

SQL> SELECT table_name,
partitioning_type, autolist, partition_count FROM user_part_tables WHERE table_name=’ALP';

TABLE_NAME                    
PARTITIONING_TYPE              AUTOLIST   PARTITION_COUNT

—————————— —————————— ———- —————

ALP                            LIST                          
YES                      2

As you can see, we have a new metadata flag for this extension of list
partitioning, but it is still a list partitioned table. There is also one subtle difference to interval partitioning: you will note that there is
not a million partitions unlike what you would see for an interval partitioned table. You see the absolute number of partitions created at
that point in time, and not the theoretical maximum of 1048575 (1024^2 -1) (roughly “a million”).

Hmm, so what’s the difference here? Well, an interval partitioned table
is defined by having a fixed interval definition for all range-based partitions for the future. So by knowing an absolute partition bound, the
number of existing partitions, and the interval, all future partitions are mathematically pre-defined. For example, with a numeric interval of 1 and
one existing partition with a ‘values less than (2)’ boundary (partition number one), we know that a partition key value of 99 would end up in
partition number 99′ we would also know that “one million” is the highest number we can enter as partition key value. With auto-list on the other
hand we do not know (or care) what partition key values will be added to the table; there’s nothing pre-defined, and Oracle takes this one ‘one
partition at a time’ until we reach the maximum of “one million”.

Ok, enough theory. Let’s see the new functionality in action by adding a
record with a new partition key value:

INSERT INTO alp VALUES (999,999);

COMMIT;


You can see that the kernel created a new partition for the new partition key value of 999:

SQL> SELECT partition_name,
high_value FROM user_tab_partitions WHERE table_name=’ALP’ ORDER BY partition_position;

PARTITION_NAME                
HIGH_VALUE

—————————— ——————————

P1                             1, 2, 3, 4

P2                             5

SYS_P2581                      999

Now back to another subtle difference between interval partitioning and
auto-list partitioning. With interval partitioning you cannot ADD a new partition, because all possible partitions are mathematically pre-created
with the interval definition, as discussed earlier. With auto-list there is no pre-creation per se and no pre-defined partition key values. So just
like the kernel will add new partitions for every new partition key value, you can do so, too. The usage of the new auto-list partitioning does not
limit any partition maintenance operation for such a table.

Let’s do this now.

ALTER TABLE alp ADD PARTITION
pnew VALUES  (10,11,12);

Voila, we can manually add partitions, as we can see in the data
dictionary (as a side note: the partition position is derived by the order of creation, not the partition key values):

SQL> SELECT partition_name,
high_value FROM user_tab_partitions WHERE table_name=’ALP’ ORDER BY partition_position;

PARTITION_NAME                
HIGH_VALUE

—————————— ——————————

P1                             1, 2, 3, 4

P2                             5

SYS_P2581                      999

PNEW                           10, 11, 12

So an auto-list partitioned table is almost identical to a manual list
partitioned table. There is ultimately only ONE difference from a partition setup and maintenance perspective: an auto-list partitioned
table must not have a DEFAULT partition. Conceptually the functionality of auto-list and a having a default partitioning are contradictory and
mutually exclusive:

  • A DEFAULT partition acts as a catch-it-all partition. Any new
    partition key value that is not explicitly covered with an existing partition will be stored in this catch-it-all partition
  • An auto-list partitioned table will create a new partition for any new
    partition key value that is not explicitly covered with an existing partition. Any new partition key value will get its own partition

One important key functionality that auto-list partitioning is having in common with interval partitioning is the capability for tables to evolve.
List partitioned tables, single and multi-column ones, can be evolved into auto-list partitioned tables as long as they do not have a DEFAULT
partition, as discussed previously.

Let do this quickly:

CREATE TABLE mc (col1 NUMBER, col2 NUMBER) PARTITION BY LIST (col1,col2)

(PARTITION p1 VALUES (1,1),

 PARTITION p2 VALUES (2,2),

 PARTITION p3 VALUES (3,3));

As you can guess, we cannot insert any record that has any partition key
column value that is not defined with the existing partitions. We get an error message:

SQL> INSERT INTO mc VALUES
(1234,5678);


Error
starting at line : 1 in command -

INSERT
INTO mc VALUES (1234,5678)

Error
report -

ORA-14400:
inserted partition key does not map to any partition

We are now evolving table MC into an auto-list partitioned table and try
it again:

SQL> ALTER TABLE mc SET
AUTOMATIC;

Table MC altered.

SQL> INSERT INTO mc VALUES
(1234,5678);

1 row inserted.

You see that the new auto-list functionality has kicked in and has
created a new partition for our inserted record, using the inserted data as new partition key values.

SQL> SELECT partition_name, high_value FROM user_tab_partitions WHERE table_name=’MC’ ORDER BY partition_position;

PARTITION_NAME                 HIGH_VALUE

—————————— ——————————

P1                             ( 1, 1 )

P2                             ( 2, 2 )

P3                             ( 3, 3 )

SYS_P2586                      ( 1234, 5678 )</span>

That’s about it for now with auto-list partitioning. Feel free to take
these little example tables and play around with it a bit more .. for example, try other partition maintenance operations that I did not
explicitly cover. I am almost sure as well that I have forgotten some little details here and there, so please send me any comments,
corrections, or questions that you are having around Partitioning. Don’t have to be specific to the available functionality in the Oracle Database
Exadata Express Cloud Service but can be any topic you want to know more about it or want to see covered as a future blog post. You can always
reach me at hermann.baer@oracle.com.


Another one down, more to come.

Let’s block ads! (Why?)

Oracle Blogs | Oracle The Data Warehouse Insider Blog