Month: September 2021

Reference Partitioning-Partitioning-2

Posted on by Barbara Raymond

The simple syntax to reimplement our previous example could be as follows. We’ll reuse the existing parent table ORDERS and just truncate that table:
SQL> drop table order_line_items cascade constraints; Table dropped.
SQL> truncate table orders;Table truncated.
SQL> insert into orders values ( 1, to_date( ’01-jun-2020′, ‘dd-mon-yyyy’ ), ‘xxx’ );1 row created.
SQL> insert into orders values ( 2, to_date( ’01-jun-2021′, ‘dd-mon-yyyy’ ), ‘xxx’ );1 row created.

And create a new child table:
SQL> create table order_line_items(order# number,line# number,datavarchar2(30),constraint c1_pk primary key(order#,line#),constraint c1_fk_p foreign key(order#) references orders)enable row movementpartition by reference(c1_fk_p);Table created.
SQL> insert into order_line_items values ( 1, 1, ‘yyy’ ); 1 row created.
SQL> insert into order_line_items values ( 2, 1, ‘yyy’ ); 1 row created.

The magic is on line 10 of the CREATE TABLE statement. Here, we replaced the range partitioning statement with PARTITION BY REFERENCE. This allows us to name the foreign key constraint to use to discover what our partitioning scheme will be.

Here, we see the foreign key is to the ORDERS table—the database read the structure of the ORDERS table and determined that it had two partitions—therefore, our child table will have two partitions.

In fact, if we query the data dictionary right now, we can see that the two tables have the same exact partitioning structure:
SQL> select table_name, partition_name from user_tab_partitionswhere table_name in ( ‘ORDERS’, ‘ORDER_LINE_ITEMS’ )order by table_name, partition_name;
TABLE_NAME PARTITION_NAME
ORDERS PART_2020
ORDERS PART_2021
ORDER_LINE_ITEMS PART_2020
ORDER_LINE_ITEMS PART_2021

Further, since the database understands these two tables are related, we can drop the parent table partition and have it automatically clean up the related child table partitions (since the child inherits from the parent, any alteration of the parent’s partition structure cascades down):
SQL> alter table orders drop partition part_2020 update global indexes; Table altered.
SQL> select table_name, partition_name from user_tab_partitionswhere table_name in ( ‘ORDERS’, ‘ORDER_LINE_ITEMS’ )order by table_name, partition_name;
TABLE_NAME PARTITION_NAME
ORDERS PART_2021
ORDER_LINE_ITEMS PART_2021

So, the DROP we were prevented from performing before is now permitted, and it cascades to the child table automatically.

Further, if we ADD a partition, as follows, we can see that that operation is cascaded as well; there will be a one-to-one parity between the parent and the child:
SQL> alter table orders add partitionpart_2022 values less than(to_date( ’01-01-2023′, ‘dd-mm-yyyy’ )); Table altered.
SQL> select table_name, partition_name from user_tab_partitionswhere table_name in ( ‘ORDERS’, ‘ORDER_LINE_ITEMS’ )order by table_name, partition_name;
TABLE_NAME PARTITION_NAME
ORDERS PART_2021
ORDERS PART_2022
ORDER_LINE_ITEMS PART_2021
ORDER_LINE_ITEMS PART_2022

A part of the preceding CREATE TABLE statement that we did not discuss is the ENABLE ROW MOVEMENT. In short, the syntax allows an UPDATE to take place such that the UPDATE modifies the partition key value and modifies it in such a way as to cause the row to move from its current partition into some other partition.

Now, since we defined our parent table originally as permitting row movement, we were forced to define all of our child tables (and their children and so on) as having that capability as well, for if the parent row moves and we are using reference partitioning, we know the child row(s) must move as well. For example:
SQL> select ‘2021’, count() from order_line_items partition(part_2021) union all select ‘2022’, count() from order_line_items partition(part_2022);’202 COUNT(*)

We can see that right now our data in the child table ORDER_LINE_ITEMS is in the 2021 partition. By performing a simple update against the parent ORDERS table, as follows, we can see our data moved—in the child table:
SQL> update orders set order_date = add_months(order_date,12); 1 row updated.
SQL> select ‘2021’, count() from order_line_items partition(part_2021) union all select ‘2022’, count() from order_line_items partition(part_2022);’202 COUNT(*)

An update against the parent was cascaded down to the child table and caused the child table to move a row (or rows as needed).

To summarize, reference partitioning removes the need to denormalize data when partitioning parent and child tables. Furthermore, when dropping a parent partition, it will automatically drop the referenced child partition. These features are very useful in data warehousing environments.

List Partitioning-Partitioning

Posted on by Barbara Raymond

List partitioning provides the ability to specify in which partition a row will reside, based on discrete lists of values. It is often useful to be able to partition by some code, such as a state or region code. For example, we might want to pull together in a single partition all records for people in the states of Maine (ME), New Hampshire (NH), Vermont (VT), and Massachusetts (MA), since those states are located next to or near each other, and our application queries data by geographic region. Similarly, we might want to group together Connecticut (CT), Rhode Island (RI), and New York (NY).

We can’t use a range partition, since the range for the first partition would be ME through VT, and the second range would be CT through RI. Those ranges overlap. We can’t use hash partitioning since we can’t control which partition any given row goes into; the built-in hash function provided by Oracle does that. With list partitioning, we can accomplish this custom partitioning scheme easily:
$ sqlplus eoda/foo@PDB1
SQL> create table list_example( state_cd varchar2(2),data varchar2(20))partition by list(state_cd)(partition part_1 values ( ‘ME’, ‘NH’, ‘VT’, ‘MA’ ), partition part_2 values ( ‘CT’, ‘RI’, ‘NY’ )
);Table created.

Figure 13-3 shows that Oracle will inspect the STATE_CD column and, based on its value, place the row into the correct partition.

Figure 13-3.  List partition insert example

As we saw for range partitioning, if we try to insert a value that isn’t specified in the list partition, Oracle will raise an appropriate error back to the client application. In other words, a list partitioned table without a DEFAULT partition will implicitly impose a constraint much like a check constraint on the table:
SQL> insert into list_example values ( ‘VA’, ‘data’ ); insert into list_example values ( ‘VA’, ‘data’ )
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

If we want to segregate these seven states into their separate partitions, as we have, but have all remaining state codes (or, in fact, any other row that happens to be inserted that doesn’t have one of these seven codes) go into a third partition, then we can use the VALUES ( DEFAULT ) clause. Here, we’ll alter the table to add this partition (we could use this in the CREATE TABLE statement as well):
SQL1> alter table list_example add partition part_3 values ( DEFAULT ); Table altered.
SQL> insert into list_example values ( ‘VA’, ‘data’ ); 1 row created.

All values that are not explicitly in our list of values will go here. A word of caution on the use of DEFAULT: Once a list partitioned table has a DEFAULT partition, you cannot add any more partitions to it, so
SQL> alter table list_example add partition part_4 values( ‘CA’, ‘NM’ ); alter table list_example
ERROR at line 1:
ORA-14323: cannot add partition when DEFAULT partition exists

We would have to remove the DEFAULT partition, then add PART_4, and then put the DEFAULT partition back. The reason behind this is that the DEFAULT partition could have had rows with the list partition key value of CA or NM—they would not belong in the DEFAULT partition after adding PART_4.