Oracle database experiences

Oracle database blog

Modyfing, testing and debugging PL/SQL packages – Part II – Big packages

Scope

Following the previous post we will continue our investigations concerning the modifications of a big package.

Third approach – Associative array

This approach is similar to the second one, except that the name of the conditions can be more verbose. We will use an associative array of boolean in order to store the conditions. We must add an “initialization” procedure to set up the different conditions that will be executed in the initialization part of the package body.
You will find below the modified package:

CREATE OR REPLACE PACKAGE BODY DEV3.BIG_PACKAGE1 AS

   TYPE param_ct IS TABLE OF BOOLEAN INDEX BY VARCHAR2(250);

   l_param_type param_ct;
...
-- =======================================================================
-- function : function51 lvl:1
-- =======================================================================
FUNCTION function51(in_param NUMBER) RETURN NUMBER AS
l_GogRtbjjixylNzMdLPb NUMBER;
BEGIN
l_GogRtbjjixylNzMdLPb  := in_param;
IF in_param = 9878 AND l_param_type('function51_condition1') -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7684;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 448;
END IF;
l_GogRtbjjixylNzMdLPb  := function14(l_GogRtbjjixylNzMdLPb);
IF in_param = 5554
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 8352;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 3003;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 458 AND l_param_type('function51_condition2') -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7434;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 2579;
END IF;
l_GogRtbjjixylNzMdLPb  := function3(l_GogRtbjjixylNzMdLPb);
IF in_param = 8905 AND l_param_type('function51_condition3') -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7326;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 6456;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 9569 AND l_param_type('function51_condition4') -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 1804;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 4303;
END IF;
l_GogRtbjjixylNzMdLPb  := function29(l_GogRtbjjixylNzMdLPb);
...
RETURN l_GARQUWfazCmqmeKNsNRQ;
END function175;

PROCEDURE init_param
AS
BEGIN

   --
   -- function51
   --
   l_param_type('function51_condition1') := FALSE;
   l_param_type('function51_condition2') := TRUE;
   l_param_type('function51_condition3') := FALSE;
   l_param_type('function51_condition4') := TRUE;

   --
   -- function63
   --
   l_param_type('function63_condition1') := TRUE;
   l_param_type('function63_condition2') := FALSE;
   l_param_type('function63_condition3') := FALSE;
   l_param_type('function63_condition4') := TRUE;

   --
   -- function88
   --
   l_param_type('function88_condition1') := FALSE;
   l_param_type('function88_condition2') := TRUE;
   l_param_type('function88_condition3') := FALSE;
   l_param_type('function88_condition4') := FALSE;

   --
   -- function114
   --
   l_param_type('function114_condition1') := FALSE;
   l_param_type('function114_condition2') := TRUE;
   l_param_type('function114_condition3') := TRUE;
   l_param_type('function114_condition4') := TRUE;

   --
   -- function152
   --
   l_param_type('function152_condition1') := FALSE;
   l_param_type('function152_condition2') := FALSE;
   l_param_type('function152_condition3') := FALSE;
   l_param_type('function152_condition4') := TRUE;

   --
   -- function166
   --
   l_param_type('function166_condition1') := FALSE;
   l_param_type('function166_condition2') := TRUE;
   l_param_type('function166_condition3') := FALSE;
   l_param_type('function166_condition4') := TRUE;

END init_param;

BEGIN
   init_param;

END BIG_PACKAGE1 ;
/

Fourth approach – Conditional compilation

This approach is also similar to the second one, except that we will use inquiry directives e.g. $$function51_condition1 to set the different conditions. Those conditions do not need to be declared and to be initialized in the package body. We will set up the values using the command “ALTER SESSION SET PLSQL_CCFLAGS = …”.

Remark: The value of an inquiry directive can be a BOOLEAN or a PLS_INTEGER.

You will find below the modified package:

CREATE OR REPLACE PACKAGE BODY DEV3.BIG_PACKAGE1 AS
-- lvl:3
FUNCTION function6(in_param NUMBER) RETURN NUMBER;
FUNCTION function7(in_param NUMBER) RETURN NUMBER;
...
-- =======================================================================
-- function : function51 lvl:1
-- =======================================================================
FUNCTION function51(in_param NUMBER) RETURN NUMBER AS
l_GogRtbjjixylNzMdLPb NUMBER;
BEGIN
l_GogRtbjjixylNzMdLPb  := in_param;
IF in_param = 9878 AND $$function51_condition1 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7684;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 448;
END IF;
l_GogRtbjjixylNzMdLPb  := function14(l_GogRtbjjixylNzMdLPb);
IF in_param = 5554
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 8352;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 3003;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 458 AND $$function51_condition2 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7434;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 2579;
END IF;
l_GogRtbjjixylNzMdLPb  := function3(l_GogRtbjjixylNzMdLPb);
IF in_param = 8905 AND $$function51_condition3 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7326;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 6456;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 9569 AND $$function51_condition4 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 1804;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 4303;
END IF;
l_GogRtbjjixylNzMdLPb  := function29(l_GogRtbjjixylNzMdLPb);
...
RETURN l_GARQUWfazCmqmeKNsNRQ;
END function175;
END BIG_PACKAGE1 ;
/

At this point the values of the different inquiry directives are equal to NULL. We will set up the values as follow:

DEV3@pdb1> ALTER SESSION SET PLSQL_CCFLAGS = 'function51_condition1:FALSE, function51_condition2:TRUE, function51_condition3:FALSE, function51_condition4:TRUE';

Session altered.

and then we have to compile the package body in order to be taken into account:

DEV3@pdb1> ALTER PACKAGE DEV3.BIG_PACKAGE1 COMPILE BODY;

Package body altered.

Remarks:

  • The package has to be compiled every time the values of the different inquiry directives change,
  • The number of inquiry directives is limited as shown in the following example.
DEV3@pdb1> ALTER SESSION SET PLSQL_CCFLAGS = 'function51_condition1:FALSE, function51_condition2:TRUE, function51_condition3:FALSE, function51_condition4:TRUE, function51_condition1:FALSE, function51_condition2:TRUE, function51_condition3:FALSE, function51_condition4:TRUE, function51_condition1:FALS
E, function51_condition2:TRUE, function51_condition3:FALSE, function51_condition4:TRUE';
ERROR:
ORA-32021: parameter value longer than 255 characters

Advertisements

Modifying, testing and debugging PL/SQL packages – Part I – Big packages

Scope

From time to time you have to modify what I call a “big PL/SQL package” e.g. a package with more than 20 000 source code lines which is not a good practise. I had to modify such a package where some conditions have to be adjusted for a subset of functions. In this specific case, the package could not be transformed in order to “loose weigth”.

Set up

For the demonstration purposes, I have generated a big package with 175 functions all containing a variable number of IF THEN ELSE statements. Each function is linked to a level from 1 to 3. The level 1 is the top level. A function whose level is N can only call function whose level is N+1.

The functions to be modified are the following:

  • function51,
  • function63,
  • function88,
  • function114,
  • function152,
  • function166.

What I have not indicated in the generated package is that some code must be added.

Remarks:

  • The variable names have been randomly generated,
  • The different IF THEN ELSE statements have been randomly generated,
  • The number of IF THEN ELSE statements have been also randomly generated.

You will find below an extract of this package:

CREATE OR REPLACE PACKAGE DEV3.BIG_PACKAGE1 AS
--
FUNCTION function1(in_param NUMBER)
RETURN NUMBER;
--
FUNCTION function9(in_param NUMBER)
RETURN NUMBER;
--
FUNCTION function12(in_param NUMBER)
RETURN NUMBER;
--
FUNCTION function20(in_param NUMBER)
RETURN NUMBER;
--
FUNCTION function23(in_param NUMBER)
RETURN NUMBER;
--
FUNCTION function30(in_param NUMBER)
RETURN NUMBER;
...
--
FUNCTION function172(in_param NUMBER)
RETURN NUMBER;
END BIG_PACKAGE1;
/
CREATE OR REPLACE PACKAGE BODY DEV3.BIG_PACKAGE1 AS
-- lvl:3
FUNCTION function6(in_param NUMBER) RETURN NUMBER;
FUNCTION function7(in_param NUMBER) RETURN NUMBER;
...
-- =======================================================================
-- function : function51 lvl:1
-- =======================================================================
FUNCTION function51(in_param NUMBER) RETURN NUMBER AS
l_GogRtbjjixylNzMdLPb NUMBER;
BEGIN
l_GogRtbjjixylNzMdLPb  := in_param;
IF in_param = 9878
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7684;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 448;
END IF;
l_GogRtbjjixylNzMdLPb  := function14(l_GogRtbjjixylNzMdLPb);
IF in_param = 5554
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 8352;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 3003;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 458
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7434;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 2579;
END IF;
l_GogRtbjjixylNzMdLPb  := function3(l_GogRtbjjixylNzMdLPb);
IF in_param = 8905
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7326;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 6456;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 9569
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 1804;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 4303;
END IF;
l_GogRtbjjixylNzMdLPb  := function29(l_GogRtbjjixylNzMdLPb);
...
END IF;
RETURN l_GARQUWfazCmqmeKNsNRQ;
END function175;
END BIG_PACKAGE1 ;
/

and below the number of lines:

First approach

The first approach is to:

  • Step1: Modify directly the conditions of the different functions,
  • Step2: Test the package,
  • Step3: Go to Step1 if the expected result is not correct.

Nevertheless, there are some drawbacks:

  • Step1 is not convenient because you have to locate in the package the conditions to be modified which is not easy in a “big package”,
  • If you are using a PL/SQL editor it can take a long time to navigate through the code.

Second approach

In this approach I will used boolean constants that will enable or not some conditions. Thoses constants are defined in the begining of the package body. Each functions to be modified has a set of constants that control the conditions behavior.
You will find below the definitions of the constants and the code of a function with thoses constants:

CREATE OR REPLACE PACKAGE BODY DEV3.BIG_PACKAGE1 AS

   --
   -- function51
   --
   co_function51_condition1 CONSTANT BOOLEAN := FALSE;
   co_function51_condition2 CONSTANT BOOLEAN := TRUE;
   co_function51_condition3 CONSTANT BOOLEAN := FALSE;
   co_function51_condition4 CONSTANT BOOLEAN := TRUE;

   --
   -- function63
   --
   co_function63_condition1 CONSTANT BOOLEAN := TRUE;
   co_function63_condition2 CONSTANT BOOLEAN := FALSE;
   co_function63_condition3 CONSTANT BOOLEAN := FALSE;
   co_function63_condition4 CONSTANT BOOLEAN := TRUE;

   --
   -- function88
   --
   co_function88_condition1 CONSTANT BOOLEAN := FALSE;
   co_function88_condition2 CONSTANT BOOLEAN := TRUE;
   co_function88_condition3 CONSTANT BOOLEAN := FALSE;
   co_function88_condition4 CONSTANT BOOLEAN := FALSE;

   --
   -- function114
   --
   co_function114_condition1 CONSTANT BOOLEAN := FALSE;
   co_function114_condition2 CONSTANT BOOLEAN := TRUE;
   co_function114_condition3 CONSTANT BOOLEAN := TRUE;
   co_function114_condition4 CONSTANT BOOLEAN := TRUE;

   --
   -- function152
   --
   co_function152_condition1 CONSTANT BOOLEAN := FALSE;
   co_function152_condition2 CONSTANT BOOLEAN := FALSE;
   co_function152_condition3 CONSTANT BOOLEAN := FALSE;
   co_function152_condition4 CONSTANT BOOLEAN := TRUE;

   --
   -- function166
   --
   co_function166_condition1 CONSTANT BOOLEAN := FALSE;
   co_function166_condition2 CONSTANT BOOLEAN := TRUE;
   co_function166_condition3 CONSTANT BOOLEAN := FALSE;
   co_function166_condition4 CONSTANT BOOLEAN := TRUE;


-- lvl:3
FUNCTION function6(in_param NUMBER) RETURN NUMBER;
FUNCTION function7(in_param NUMBER) RETURN NUMBER;
...
-- =======================================================================
-- function : function51 lvl:1
-- =======================================================================
FUNCTION function51(in_param NUMBER) RETURN NUMBER AS
l_GogRtbjjixylNzMdLPb NUMBER;
BEGIN
l_GogRtbjjixylNzMdLPb  := in_param;
IF in_param = 9878 AND co_function51_condition1 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7684;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 448;
END IF;
l_GogRtbjjixylNzMdLPb  := function14(l_GogRtbjjixylNzMdLPb);
IF in_param = 5554
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 8352;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 3003;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 458 AND co_function51_condition2 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7434;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 2579;
END IF;
l_GogRtbjjixylNzMdLPb  := function3(l_GogRtbjjixylNzMdLPb);
IF in_param = 8905 AND co_function51_condition3 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 7326;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 6456;
END IF;
l_GogRtbjjixylNzMdLPb  := function149(l_GogRtbjjixylNzMdLPb);
IF in_param = 9569 AND co_function51_condition4 -- modified
THEN
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb + 1804;
ELSE
l_GogRtbjjixylNzMdLPb  := l_GogRtbjjixylNzMdLPb - 4303;
END IF;
l_GogRtbjjixylNzMdLPb  := function29(l_GogRtbjjixylNzMdLPb);
...
RETURN l_GARQUWfazCmqmeKNsNRQ;
END function175;
END BIG_PACKAGE1 ;
/

Remarks:

  • Of course you must at the very beginning modify the code to add the constants, but once in place, every time you modify it, you simply need to change the constants at the beginning of the package body,
  • As you can imagine, the final result of a function is not sufficient to check if the modifications are correct. We will see in a future post how we can display the intermediate results.

Custom Partitioning – Part VIII – List-Range-List partitioning – Instead Of Trigger – Multi level partitioning 4

Scope

In the last post, we have implemented a list-range partitioning. In this post, we would like to partition the sub-partition DEV3.P_CUSTOMER_ARCHIVED_1990 based on the first character of the last_name column.
This will lead to a List-Range-List partitioning. This composite partitioning will be called an heterogeneous multi-level partitioning, because the level 3 list partitioning will be applied only to the DEV3.P_CUSTOMER_ARCHIVED_1990 sub-partition. The following figure shows this partitioning:

We will set up 3 sub-sub-partitions:

  • One for a last_name begining with an A or an E,
  • One for a last_name begining with an B,
  • One for the other case.

Let’s create the sub-sub-partitions:

DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ARCHIVED_1990_AE
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ARCHIVED_1990_B
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ARCHIVED_1990_DEF
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

We will drop the table DEV3.P_CUSTOMER_ARCHIVED_1990:

DEV3@pdb1> DROP TABLE DEV3.P_CUSTOMER_ARCHIVED_1990;

Table dropped.

and then we will create a view with the same name that is the UNION of the three previous tables (sub-sub-partitions):

DEV3@pdb1> CREATE OR REPLACE VIEW DEV3.P_CUSTOMER_ARCHIVED_1990
  2  AS
  3   SELECT * FROM DEV3.P_CUSTOMER_ARCHIVED_1990_AE
  4  UNION ALL
  5   SELECT * FROM DEV3.P_CUSTOMER_ARCHIVED_1990_B
  6  UNION ALL
  7   SELECT * FROM DEV3.P_CUSTOMER_ARCHIVED_1990_DEF;

View created.

Instead of Trigger – Insert

We will create an instead of trigger for the insert operation in the same way than for the list partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TI_P_CUSTOMER_ARCHIVED_1990
INSTEAD OF INSERT ON DEV3.P_CUSTOMER_ARCHIVED_1990
-- =======================================================================
--  Trigger       : TI_P_CUSTOMER_ARCHIVED_1990
--  Description   : Insert trigger for custom partitioning (List-Range-List)
--                  List partition
--  Author        : Arnaud Fargues
--  Creation      : 06.05.2017
--  Modifications : 
-- =======================================================================
DECLARE
   v_partition_list VARCHAR2(5);
BEGIN
   v_partition_list := SUBSTR(UPPER(:new.last_name),1,1);
  
   CASE 
      -- A,E --
      WHEN v_partition_list IN ('A','E') THEN   
         INSERT INTO DEV3.P_CUSTOMER_ARCHIVED_1990_AE
           (
            cust_id,    
            first_name,  
            last_name, 
            birth_date,
            archive_state,
            creation_date,      
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
      -- B --      
      WHEN v_partition_list IN ('B') THEN    
         INSERT INTO DEV3.P_CUSTOMER_ARCHIVED_1990_B
           (
            cust_id,    
            first_name,  
            last_name,
            birth_date,
            archive_state,
            creation_date,      
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
      -- Default --      
      ELSE
         INSERT INTO DEV3.P_CUSTOMER_ARCHIVED_1990_DEF
           (
            cust_id,    
            first_name,  
            last_name,
            birth_date,
            archive_state,
            creation_date,      
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
   END CASE;
END;

Instead of Trigger – Delete

We will create an instead of trigger for the delete operation in the same way than for the list partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TD_P_CUSTOMER_ARCHIVED_1990
INSTEAD OF DELETE ON DEV3.P_CUSTOMER_ARCHIVED_1990
-- =======================================================================
--  Trigger       : TD_P_CUSTOMER_ARCHIVED_1990
--  Description   : Delete trigger for custom partitioning (List-Range-List)
--                  List partition
--  Author        : Arnaud Fargues
--  Creation      : 06.05.2017
--  Modifications : 
-- =======================================================================
DECLARE
   v_old_partition_list VARCHAR2(5);
BEGIN
   v_old_partition_list := SUBSTR(UPPER(:old.last_name),1,1);
  
   CASE 
      -- A,E --
      WHEN v_old_partition_list IN ('A','E') THEN   
         DELETE DEV3.P_CUSTOMER_ARCHIVED_1990_AE
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date  
           AND archive_state = :old.archive_state   
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
      -- B --      
      WHEN v_old_partition_list IN ('B') THEN    
         DELETE DEV3.P_CUSTOMER_ARCHIVED_1990_B
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date 
           AND archive_state = :old.archive_state    
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
      -- Default --      
      ELSE
         DELETE DEV3.P_CUSTOMER_ARCHIVED_1990_DEF
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date   
           AND archive_state = :old.archive_state  
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
  END CASE;
END;

Instead of Trigger – Update

We will create an instead of trigger for the update operation in the same way than for the list partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TU_P_CUSTOMER_ARCHIVED_1990
INSTEAD OF UPDATE ON DEV3.P_CUSTOMER_ARCHIVED_1990
-- =======================================================================
--  Trigger       : TU_P_CUSTOMER_ARCHIVED_1990
--  Description   : Update trigger for custom partitioning (List-Range-List)
--                  List partition
--  Author        : Arnaud Fargues
--  Creation      : 06.05.2017
--  Modifications : 
-- =======================================================================
DECLARE
   v_old_partition_list VARCHAR2(5);
   v_new_partition_list VARCHAR2(5);
BEGIN
   v_old_partition_list := SUBSTR(UPPER(:old.last_name),1,1);
   v_new_partition_list := SUBSTR(UPPER(:new.last_name),1,1);
  
   -- Same paritition
   IF (    (v_old_partition_list IS NULL AND v_new_partition_list IS NULL) 
        OR ((v_old_partition_list IS NOT NULL AND v_new_partition_list IS NOT NULL) AND v_old_partition_list = v_new_partition_list))
   THEN
      CASE 
         -- A,E --
         WHEN v_old_partition_list IN ('A','E') THEN   
            UPDATE DEV3.P_CUSTOMER_ARCHIVED_1990_AE
            SET cust_id = :new.cust_id,   
                first_name = :new.first_name,   
                last_name= :new.last_name,   
                birth_date= :new.birth_date,  
                archive_state = :new.archive_state,    
                creation_date= :new.creation_date,        
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id  
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date   
              AND archive_state = :old.archive_state   
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
         -- B --      
         WHEN v_old_partition_list IN ('B') THEN    
            UPDATE DEV3.P_CUSTOMER_ARCHIVED_1990_B
            SET cust_id = :new.cust_id,   
                first_name = :new.first_name,   
                last_name= :new.last_name,   
                birth_date= :new.birth_date,  
                archive_state = :new.archive_state,    
                creation_date= :new.creation_date,        
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id  
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date  
              AND archive_state = :old.archive_state    
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  

         -- Default --      
         ELSE
            UPDATE DEV3.P_CUSTOMER_ARCHIVED_1990_DEF
            SET cust_id = :new.cust_id,   
                first_name = :new.first_name,   
                last_name= :new.last_name,   
                birth_date= :new.birth_date, 
                archive_state = :new.archive_state,     
                creation_date= :new.creation_date,        
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id  
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date  
              AND archive_state = :old.archive_state    
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  

      END CASE;
   ELSE
      
      -- Delete --
      DELETE DEV3.P_CUSTOMER_ARCHIVED_1990
      WHERE cust_id = :old.cust_id  
        AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
        AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
        AND birth_date = :old.birth_date   
        AND archive_state = :old.archive_state 
        AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
        AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
 
      -- Insert --
      INSERT INTO DEV3.P_CUSTOMER_ARCHIVED_1990
        (
         cust_id,    
         first_name,  
         last_name, 
         birth_date,
         archive_state,
         creation_date,      
         modification_date
        )
      VALUES
        (
         :new.cust_id,
         :new.first_name,
         :new.last_name,
         :new.birth_date,
         :new.archive_state,
         :new.creation_date,
         :new.modification_date
        );
        
   END IF;
  
END;

We will delete the rows of all the underlying partitions as follow:

DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ACTIVE_MINVALUE;

Table truncated.

DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ACTIVE_1980;

Table truncated.

DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ACTIVE_1990;

Table truncated.

DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ACTIVE_MAXVALUE;

Table truncated.

DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ARCHIVED_MINVALUE;

Table truncated.

DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ARCHIVED_1980;

Table truncated.

DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ARCHIVED_MAXVALUE;

Table truncated.

DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ARCHIVED_1990_AE;

Table truncated.

DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ARCHIVED_1990_B;

Table truncated.

DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ARCHIVED_1990_DEF;

Table truncated.

Now we will partition (List-Range-List) the source table as follow:

DEV3@pdb1> INSERT INTO DEV3.CUSTOMER_LIST_RANGE
  2    (
  3     cust_id,
  4     first_name,
  5     last_name,
  6     birth_date,
  7     archive_state,
  8     creation_date,
  9     modification_date
 10    )
 11  SELECT *
 12  FROM DEV3.CUSTOMER_MODEL_M;

10000 rows created.

DEV3@pdb1> COMMIT;

Commit complete.

Remark: We have kept the previous view name DEV3.CUSTOMER_LIST_RANGE instead of a DEV3.CUSTOMER_LIST_RANGE_LIST name.

Let’s find an archived row where birth_date >= 01.01.1990 and < 01.01.1995 and last_name begins with a 'B':

DEV3@pdb1> SELECT cust_id,
  2         TO_CHAR(birth_date,'DD.MM.YYYY') birth_date,
  3         last_name,
  4         archive_state
  5  FROM dev3.customer_list_range
  6  WHERE archive_state = 'Y'
  7    AND (birth_date >= TO_DATE('01.01.1990') AND birth_date < TO_DATE('01.01.1995'))
  8    AND SUBSTR(UPPER(last_name),1,1) ='B'
  9  FETCH FIRST 1 ROWS ONLY;

   CUST_ID BIRTH_DATE LAST_NAME
---------- ---------- -----------------------------------------------------------------
        25 10.02.1992 BPLVIEPEMZQGKHK

1 row selected.

This row should be in the sub-partition DEV3.P_CUSTOMER_ARCHIVED_1990_B. Let’s check it:

DEV3@pdb1> SELECT cust_id,
  2         TO_CHAR(birth_date,'DD.MM.YYYY') birth_date,
  3         last_name,
  4         archive_state
  5  FROM dev3.p_customer_archived_1990_b
  6  WHERE cust_id = 25;

   CUST_ID BIRTH_DATE LAST_NAME
---------- ---------- ----------------------------------
        25 10.02.1992 BPLVIEPEMZQGKHK

1 row selected.

We will update this row in order to have a last_name beginning with ‘A’:

DEV3@pdb1> UPDATE dev3.customer_list_range
  2  SET last_name = 'A' || last_name
  3  WHERE cust_id = 25;

1 row updated.

DEV3@pdb1> COMMIT;

Commit complete.

This row should be in the sub-partition DEV3.P_CUSTOMER_ARCHIVED_1990_AE. Let’s check it:

DEV3@pdb1> SELECT cust_id,
  2         TO_CHAR(birth_date,'DD.MM.YYYY') birth_date,
  3         last_name,
  4         archive_state
  5  FROM dev3.p_customer_archived_1990_ae
  6  WHERE cust_id = 25;

   CUST_ID BIRTH_DATE LAST_NAME
---------- ---------- ----------------------------------
        25 10.02.1992 ABPLVIEPEMZQGKHK

1 row selected.

We will update this row in order to become ‘active’:

DEV3@pdb1> UPDATE dev3.customer_list_range
  2  SET archive_state = 'N'
  3  WHERE cust_id = 25;

1 row updated.

DEV3@pdb1> COMMIT;

Commit complete.

This row should be in the sub-partition DEV3.P_CUSTOMER_ACTIVE_1990. Let’s check it:

DEV3@pdb1> SELECT cust_id,
  2         TO_CHAR(birth_date,'DD.MM.YYYY') birth_date,
  3         last_name,
  4         archive_state
  5  FROM dev3.p_customer_active_1990
  6  WHERE cust_id = 25;

   CUST_ID BIRTH_DATE LAST_NAME
---------- ---------- ----------------------------------
        25 10.02.1992 ABPLVIEPEMZQGKHK

1 row selected.

Custom Partitioning – Part VII – List-Range partitioning – Instead Of Trigger – Multi level partitioning 3

Scope

In the last two posts we have implemented the part 1 and the part 2 and we have created two views and six triggers as shown in the following figure:

In this post we will implement the “Part 3” e.g. the list partitioning for the archive_state column rows.
All the sub-partitions have been created in the last two posts, and the views DEV3.P_CUSTOMER_ACTIVE and DEV3.P_CUSTOMER_ARCHIVED represent in fact the “partitions”.

We will create a view that is the UNION of the two previous views (partitions).

DEV3@pdb1> CREATE OR REPLACE VIEW DEV3.CUSTOMER_LIST_RANGE
  2  AS
  3   SELECT * FROM DEV3.P_CUSTOMER_ACTIVE
  4  UNION ALL
  5   SELECT * FROM DEV3.P_CUSTOMER_ARCHIVED;

View created.

Instead of Trigger – Insert

We will create an instead of trigger for the insert operation in the same way than for the list partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TI_CUSTOMER_LIST_RANGE
INSTEAD OF INSERT ON DEV3.CUSTOMER_LIST_RANGE
-- =======================================================================
--  Trigger       : TI_CUSTOMER_LIST_RANGE
--  Description   : Insert trigger for custom partitioning (List-Range)
--                  List partition
--  Author        : Arnaud Fargues
--  Creation      : 12.03.2017
--  Modifications : 09.04.2017 AF Update
-- =======================================================================
DECLARE
   v_partition_list VARCHAR2(5);
BEGIN
   v_partition_list := :new.archive_state;
  
   CASE 
      -- Y --
      WHEN v_partition_list = 'Y' THEN   
         INSERT INTO DEV3.P_CUSTOMER_ARCHIVED
           (
            cust_id,    
            first_name,  
            last_name, 
            birth_date,
            archive_state,
            creation_date,      
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
      -- N --      
      WHEN v_partition_list = 'N' THEN    
         INSERT INTO DEV3.P_CUSTOMER_ACTIVE
           (
            cust_id,    
            first_name,  
            last_name,
            birth_date,
            archive_state,
            creation_date,      
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
      -- Default --      
      ELSE
         NULL;
   END CASE;
END;

Instead of Trigger – Delete

We will create an instead of trigger for the delete operation in the same way than for the list partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TD_CUSTOMER_LIST_RANGE
INSTEAD OF DELETE ON DEV3.CUSTOMER_LIST_RANGE
-- =======================================================================
--  Trigger       : TD_CUSTOMER_LIST_RANGE
--  Description   : Delete trigger for custom partitioning (List-Range)
--                  List partition
--  Author        : Arnaud Fargues
--  Creation      : 12.03.2017
--  Modifications : 09.04.2017 AF Update
-- =======================================================================
DECLARE
   v_old_partition_list VARCHAR2(5);
BEGIN
   v_old_partition_list := :old.archive_state;
  
   CASE 
      -- Y --
      WHEN v_old_partition_list = 'Y' THEN   
         DELETE DEV3.P_CUSTOMER_ARCHIVED
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date  
           AND archive_state = :old.archive_state  
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
      -- N --      
      WHEN v_old_partition_list = 'N' THEN    
         DELETE DEV3.P_CUSTOMER_ACTIVE
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date  
           AND archive_state = :old.archive_state   
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
      -- Default --      
      ELSE
         NULL;
  END CASE;
END;

Instead of Trigger – Update

We will create an instead of trigger for the update operation in the same way than for the list partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TU_CUSTOMER_LIST_RANGE
INSTEAD OF UPDATE ON DEV3.CUSTOMER_LIST_RANGE
-- =======================================================================
--  Trigger       : TU_CUSTOMER_LIST_RANGE
--  Description   : Update trigger for custom partitioning (List-Range)
--                  List partition
--  Author        : Arnaud Fargues
--  Creation      : 12.03.2017
--  Modifications : 09.04.2017 AF Update
-- =======================================================================
DECLARE
   v_old_partition_list VARCHAR2(5);
   v_new_partition_list VARCHAR2(5);
BEGIN
   v_old_partition_list := :old.archive_state;
   v_new_partition_list := :new.archive_state;
  
   -- Same paritition
   IF (    (v_old_partition_list IS NULL AND v_new_partition_list IS NULL) 
        OR ((v_old_partition_list IS NOT NULL AND v_new_partition_list IS NOT NULL) AND v_old_partition_list = v_new_partition_list))
   THEN
      CASE 
         -- Y --
         WHEN v_old_partition_list = 'Y' THEN   
            UPDATE DEV3.P_CUSTOMER_ARCHIVED
            SET cust_id = :new.cust_id,   
                first_name = :new.first_name,   
                last_name= :new.last_name,   
                birth_date= :new.birth_date,
                archive_state = :new.archive_state,   
                creation_date= :new.creation_date,        
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id  
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date 
              AND archive_state = :old.archive_state    
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
         -- N --      
         WHEN v_old_partition_list = 'N' THEN    
            UPDATE DEV3.P_CUSTOMER_ACTIVE
            SET cust_id = :new.cust_id,   
                first_name = :new.first_name,   
                last_name= :new.last_name,   
                birth_date= :new.birth_date, 
                archive_state = :new.archive_state,     
                creation_date= :new.creation_date,        
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id  
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date   
              AND archive_state = :old.archive_state  
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
         -- Default --      
         ELSE
            NULL;
      END CASE;
   ELSE
      
      -- Delete --
      DELETE DEV3.CUSTOMER_LIST_RANGE
      WHERE cust_id = :old.cust_id  
        AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
        AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
        AND birth_date = :old.birth_date  
        AND archive_state = :old.archive_state  
        AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
        AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
 
      -- Insert --
      INSERT INTO DEV3.CUSTOMER_LIST_RANGE
        (
         cust_id,    
         first_name,  
         last_name, 
         birth_date,
         archive_state,
         creation_date,      
         modification_date
        )
      VALUES
        (
         :new.cust_id,
         :new.first_name,
         :new.last_name,
         :new.birth_date,
         :new.archive_state,
         :new.creation_date,
         :new.modification_date
        );
        
   END IF;
  
END;

Basically, we must know if the updated row remains in the same partition or not.

Now we will partition (list-range) the source table as follow:

DEV3@pdb1> INSERT INTO DEV3.CUSTOMER_LIST_RANGE
  2    (
  3     cust_id,
  4     first_name,
  5     last_name,
  6     birth_date,
  7     archive_state,
  8     creation_date,
  9     modification_date
 10    )
 11  SELECT *
 12  FROM DEV3.CUSTOMER_MODEL_M;

10000 rows created.

DEV3@pdb1> COMMIT;

Commit complete.

Let’s find an archived row where birth_date < 01.01.1980:

DEV3@pdb1> SELECT cust_id,
  2         TO_CHAR(birth_date,'DD.MM.YYYY') birth_date,
  3         archive_state
  4  FROM dev3.customer_list_range
  5  WHERE archive_state = 'Y'
  6    AND birth_date < TO_DATE('01.01.1980')
  7  FETCH FIRST 1 ROWS ONLY;

   CUST_ID BIRTH_DATE A
---------- ---------- -
      1883 28.08.1972 Y

1 row selected.

This row should be in the sub-partition DEV3.P_CUSTOMER_ARCHIVED_MINVALUE. Let's check it:

DEV3@pdb1> SELECT cust_id,
  2         TO_CHAR(birth_date,'DD.MM.YYYY') birth_date,
  3         archive_state
  4  FROM dev3.p_customer_archived_minvalue
  5  WHERE cust_id = 1883;

   CUST_ID BIRTH_DATE A
---------- ---------- -
      1883 28.08.1972 Y

1 row selected.

We will update this row in order to become 'active':

DEV3@pdb1> UPDATE dev3.customer_list_range
  2  SET archive_state = 'N'
  3  WHERE cust_id = 1883;

1 row updated.

DEV3@pdb1> COMMIT;

Commit complete.

This row should be in the sub-partition DEV3.P_CUSTOMER_ACTIVE_MINVALUE. Let's check it:

DEV3@pdb1> SELECT cust_id,
  2         TO_CHAR(birth_date,'DD.MM.YYYY') birth_date,
  3         archive_state
  4  FROM dev3.p_customer_active_minvalue
  5  WHERE cust_id = 1883;

   CUST_ID BIRTH_DATE A
---------- ---------- -
      1883 28.08.1972 N

1 row selected.

We will update this row in order to have a birth_date = 01.01.1991.

DEV3@pdb1> UPDATE dev3.customer_list_range
  2  SET birth_date = TO_DATE('01.01.1991')
  3  WHERE cust_id = 1883;

1 row updated.

DEV3@pdb1> COMMIT;

Commit complete.

This row should be in the sub-partition DEV3.P_CUSTOMER_ACTIVE_1990. Let's check it:

DEV3@pdb1> SELECT cust_id,
  2         TO_CHAR(birth_date,'DD.MM.YYYY') birth_date,
  3         archive_state
  4  FROM dev3.p_customer_active_1990
  5  WHERE cust_id = 1883;

   CUST_ID BIRTH_DATE A
---------- ---------- -
      1883 01.01.1991 N

1 row selected.

You will find below a figure that summarizes the list-range implementation:

Remark:

  • We could add a check constraint on all the sub-partitions on the archive_state column in order to have only the two values ‘Y’ and ‘N’ e.g.
    ALTER TABLE DEV3.P_CUSTOMER_ACTIVE_1990 
    ADD CONSTRAINT P_CUSTOMER_ACTIVE_1990_AR_CK
    CHECK (ARCHIVE_STATE IN ('Y','N'));

    .

Custom Partitioning – Part VI – List-Range partitioning – Instead Of Trigger – Multi level partitioning 2

Scope

In the last post we have worked on the part 1 of the implementation and we have created one view and three triggers as shown in the following figure:

In this post we will implement the “Part 2” e.g. the Range partitioning for the ‘Y’ archive_state column rows.
We will set up 4 sub-partitions in the same way than the part III:

  • One for a birth_date < 01.01.1980,
  • One for a birth_date >= 01.01.1980 and < 01.01.1990,
  • One for a birth_date >= 01.01.1990 and < 01.01.1995,
  • One for a birth_date >= 01.01.1995.

Let’s create the four sub-partitions:

DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ARCHIVED_MINVALUE
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ARCHIVED_1980
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ARCHIVED_1990
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ARCHIVED_MAXVALUE
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

Now we will create a view that is the UNION of all the previous tables (sub-partitions).

DEV3@pdb1> CREATE OR REPLACE VIEW DEV3.P_CUSTOMER_ARCHIVED
  2  AS
  3   SELECT * FROM DEV3.P_CUSTOMER_ARCHIVED_MINVALUE
  4  UNION ALL
  5   SELECT * FROM DEV3.P_CUSTOMER_ARCHIVED_1980
  6  UNION ALL
  7   SELECT * FROM DEV3.P_CUSTOMER_ARCHIVED_1990
  8  UNION ALL
  9   SELECT * FROM DEV3.P_CUSTOMER_ARCHIVED_MAXVALUE;

View created.

Instead of Trigger – Insert

We will create an instead of trigger for the insert operation in the same way than for the range partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TI_P_CUSTOMER_ARCHIVED
INSTEAD OF INSERT ON DEV3.P_CUSTOMER_ARCHIVED
-- =======================================================================
--  Trigger       : TI_P_CUSTOMER_ARCHIVED
--  Description   : Insert trigger for custom partitioning (List-Range)
--                  Range sub-partition
--  Author        : Arnaud Fargues
--  Creation      : 04.02.2017
--  Modifications :
-- =======================================================================
BEGIN

   CASE
      -- < 1980
      WHEN :new.birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN
         INSERT INTO DEV3.P_CUSTOMER_ARCHIVED_MINVALUE
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            archive_state,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
      -- >= 1980 and < 1990
      WHEN :new.birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND :new.birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN
         INSERT INTO DEV3.P_CUSTOMER_ARCHIVED_1980
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            archive_state,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
      -- >= 1990 and < 1995
      WHEN :new.birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND :new.birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN
         INSERT INTO DEV3.P_CUSTOMER_ARCHIVED_1990
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            archive_state,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
      -- >= 1995
      WHEN :new.birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') OR :new.birth_date IS NULL THEN
         INSERT INTO DEV3.P_CUSTOMER_ARCHIVED_MAXVALUE
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            archive_state,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
   END CASE;
END;

Instead of Trigger – Delete

We will create an instead of trigger for the delete operation in the same way than for the range partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TD_P_CUSTOMER_ARCHIVED
INSTEAD OF DELETE ON DEV3.P_CUSTOMER_ARCHIVED
-- =======================================================================
--  Trigger       : TD_P_CUSTOMER_ARCHIVED
--  Description   : Delete trigger for custom partitioning (List-Range)
--                  Range sub-partition
--  Author        : Arnaud Fargues
--  Creation      : 04.02.2017
--  Modifications :
-- =======================================================================
BEGIN

   CASE
      -- < 1980
      WHEN :old.birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN
         DELETE DEV3.P_CUSTOMER_ARCHIVED_MINVALUE
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND archive_state = :old.archive_state
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- >= 1980 and < 1990
      WHEN :old.birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN
         DELETE DEV3.P_CUSTOMER_ARCHIVED_1980
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND archive_state = :old.archive_state
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- >= 1990 and < 1995
      WHEN :old.birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN
         DELETE DEV3.P_CUSTOMER_ARCHIVED_1990
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND archive_state = :old.archive_state
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- >= 1995
      WHEN :old.birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') THEN
         DELETE DEV3.P_CUSTOMER_ARCHIVED_MAXVALUE
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND archive_state = :old.archive_state
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
   END CASE;
END;

Instead of Trigger – Update

We will create an instead of trigger for the update operation in the same way than for the range partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TU_P_CUSTOMER_ARCHIVED
INSTEAD OF UPDATE ON DEV3.P_CUSTOMER_ARCHIVED
-- =======================================================================
--  Trigger       : TU_P_CUSTOMER_ARCHIVED
--  Description   : Update trigger for custom partitioning (List-Range)
--                  Range sub-partition
--  Author        : Arnaud Fargues
--  Creation      : 04.02.2017
--  Modifications : 06.05.2017 AF Update
-- =======================================================================
DECLARE
   FUNCTION get_partition(p_birth_date DATE)
   RETURN NUMBER
   IS
      v_return NUMBER := 0;
   BEGIN
      CASE
          WHEN p_birth_date < TO_DATE('01.01.1980','DD.MM.YYYY')
             THEN v_return := 1;
          WHEN p_birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND p_birth_date < TO_DATE('01.01.1990','DD.MM.YYYY')
             THEN v_return := 2;
          WHEN p_birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND p_birth_date < TO_DATE('01.01.1995','DD.MM.YYYY')
             THEN v_return := 3;
          WHEN p_birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY')
             THEN v_return := 4;
          ELSE v_return := -1;
      END CASE;
      RETURN v_return;
   END;
BEGIN
   -- Same paritition
   IF get_partition(:old.birth_date) = get_partition(:new.birth_date)
   THEN
      CASE
         -- < 1980
         WHEN :old.birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN
            UPDATE DEV3.P_CUSTOMER_ARCHIVED_MINVALUE
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                archive_state= :new.archive_state,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND archive_state = :old.archive_state
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
         -- >= 1980 and < 1990
         WHEN :old.birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN
            UPDATE DEV3.P_CUSTOMER_ARCHIVED_1980
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                archive_state= :new.archive_state,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND archive_state = :old.archive_state
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date));
         -- >= 1990 and < 1995
         WHEN :old.birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN
            UPDATE DEV3.P_CUSTOMER_ARCHIVED_1990
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                archive_state= :new.archive_state,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND archive_state = :old.archive_state
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
         -- >= 1995
         WHEN :old.birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') THEN
            UPDATE DEV3.P_CUSTOMER_ARCHIVED_MAXVALUE
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                archive_state= :new.archive_state,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND archive_state = :old.archive_state
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));

      END CASE;
   ELSE

      -- Delete --
      DELETE DEV3.P_CUSTOMER_ARCHIVED
      WHERE cust_id = :old.cust_id
        AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
        AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
        AND birth_date = :old.birth_date
        AND archive_state= :old.archive_state
        AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
        AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));

      -- Insert --
      INSERT INTO DEV3.P_CUSTOMER_ARCHIVED
        (
         cust_id,
         first_name,
         last_name,
         birth_date,
         archive_state,
         creation_date,
         modification_date
        )
      VALUES
        (
         :new.cust_id,
         :new.first_name,
         :new.last_name,
         :new.birth_date,
         :new.archive_state,
         :new.creation_date,
         :new.modification_date
        );

   END IF;

END;

Basically, we must know if the updated row remains in the same partition or not. We have created the subprogram function get_partition in order to have a “partition id”.

09.05.2017

The DEV3.TU_P_CUSTOMER_ARCHIVED trigger source code has been updated.

Custom Partitioning – Part V – List-Range partitioning – Instead Of Trigger – Multi level partitioning 1

Scope

In this post we will use a composite partitioning that we will call a multi-level partitioning and more precisely an homogeneous multi-level partitioning.

Case study

Let’s create the CUSTOMER_MODEL_M table as follow in the DEV3 schema:

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_MODEL_M
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        DEFAULT 'N' NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

and a sequence:

DEV3@pdb1> CREATE SEQUENCE  DEV3.S_CUSTOMER_M INCREMENT BY 1 START WITH 1 CACHE 20 NOCYCLE;

Sequence created.

Let’s now fill this table:

DEV3@pdb1> INSERT INTO DEV3.CUSTOMER_MODEL_M
  2    (
  3     cust_id,
  4     first_name,
  5     last_name,
  6     birth_date,
  7     creation_date,
  8     modification_date
  9    )
 10  SELECT DEV3.S_CUSTOMER_M.NEXTVAL,
 11         DBMS_RANDOM.STRING('a',TRUNC(DBMS_RANDOM.VALUE(8,20))),
 12         DBMS_RANDOM.STRING('u',TRUNC(DBMS_RANDOM.VALUE(8,20))),
 13         SYSDATE - (366*18) - TRUNC(DBMS_RANDOM.VALUE(1,10000)),
 14         SYSDATE,
 15         NULL
 16  FROM DUAL
 17  CONNECT BY LEVEL <= 10000;

10000 rows created.

DEV3@pdb1> COMMIT;

Commit complete.

Basically, the column archive_state indicates that a row is archived (‘Y’) or not archived (‘N’).
We will archive some rows as follow:

DEV3@pdb1> UPDATE DEV3.CUSTOMER_MODEL_M
  2  SET archive_state = 'Y'
  3  WHERE cust_id < 8001;

8000 rows updated.

DEV3@pdb1> COMMIT;

Commit complete.

We would like to partition this table firstly on the key archive_state column using a list partitioning distribution method and secondly on the key birth_date column using a range partitioning distribution method.
In our multi-level partitioning paradigm, the list partitioning is the first level and the range partitioning is the second level. We call it homogeneous, because there is the same distribution method at each level.

The following figure describes this case:

listrange01

We will start by implementing the “Part 1” e.g. the range partitioning for the ‘N’ archive_state column rows.
We will set up 4 sub-partitions in the same way than in the part III:

  • One for a birth_date < 01.01.1980,
  • One for a birth_date >= 01.01.1980 and < 01.01.1990,
  • One for a birth_date >= 01.01.1990 and < 01.01.1995,
  • One for a birth_date >= 01.01.1995.

Let’s create the four sub-partitions:

DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ACTIVE_MINVALUE
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ACTIVE_1980
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ACTIVE_1990
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ACTIVE_MAXVALUE
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

Now we will create a view that is the UNION of all the previous tables (sub-partitions).

DEV3@pdb1> CREATE OR REPLACE VIEW DEV3.P_CUSTOMER_ACTIVE
  2  AS
  3   SELECT * FROM DEV3.P_CUSTOMER_ACTIVE_MINVALUE
  4  UNION ALL
  5   SELECT * FROM DEV3.P_CUSTOMER_ACTIVE_1980
  6  UNION ALL
  7   SELECT * FROM DEV3.P_CUSTOMER_ACTIVE_1990
  8  UNION ALL
  9   SELECT * FROM DEV3.P_CUSTOMER_ACTIVE_MAXVALUE;

View created.

Instead of Trigger – Insert

We will create an instead of trigger for the insert operation in the same way than for the range partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TI_P_CUSTOMER_ACTIVE
INSTEAD OF INSERT ON DEV3.P_CUSTOMER_ACTIVE
-- =======================================================================
--  Trigger       : TI_P_CUSTOMER_ACTIVE
--  Description   : Insert trigger for custom partitioning (List-Range)
--                  Range sub-partition
--  Author        : Arnaud Fargues
--  Creation      : 04.02.2017
--  Modifications :
-- =======================================================================
BEGIN

   CASE
      -- < 1980
      WHEN :new.birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN
         INSERT INTO DEV3.P_CUSTOMER_ACTIVE_MINVALUE
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            archive_state,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
      -- >= 1980 and < 1990
      WHEN :new.birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND :new.birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN
         INSERT INTO DEV3.P_CUSTOMER_ACTIVE_1980
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            archive_state,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
      -- >= 1990 and < 1995
      WHEN :new.birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND :new.birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN
         INSERT INTO DEV3.P_CUSTOMER_ACTIVE_1990
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            archive_state,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
      -- >= 1995
      WHEN :new.birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') OR :new.birth_date IS NULL THEN
         INSERT INTO DEV3.P_CUSTOMER_ACTIVE_MAXVALUE
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            archive_state,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
   END CASE;
END;

Instead of Trigger – Delete

We will create an instead of trigger for the delete operation in the same way than for the range partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TD_P_CUSTOMER_ACTIVE
INSTEAD OF DELETE ON DEV3.P_CUSTOMER_ACTIVE
-- =======================================================================
--  Trigger       : TD_P_CUSTOMER_ACTIVE
--  Description   : Delete trigger for custom partitioning (List-Range)
--                  Range sub-partition
--  Author        : Arnaud Fargues
--  Creation      : 04.02.2017
--  Modifications :
-- =======================================================================
BEGIN

   CASE
      -- < 1980
      WHEN :old.birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN
         DELETE DEV3.P_CUSTOMER_ACTIVE_MINVALUE
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND archive_state = :old.archive_state
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- >= 1980 and < 1990
      WHEN :old.birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN
         DELETE DEV3.P_CUSTOMER_ACTIVE_1980
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND archive_state = :old.archive_state
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- >= 1990 and < 1995
      WHEN :old.birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN
         DELETE DEV3.P_CUSTOMER_ACTIVE_1990
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND archive_state = :old.archive_state
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- >= 1995
      WHEN :old.birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') THEN
         DELETE DEV3.P_CUSTOMER_ACTIVE_MAXVALUE
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND archive_state = :old.archive_state
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
   END CASE;
END;

Instead of Trigger – Update

We will create an instead of trigger for the update operation in the same way than for the range partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TU_P_CUSTOMER_ACTIVE
INSTEAD OF UPDATE ON DEV3.P_CUSTOMER_ACTIVE
-- =======================================================================
--  Trigger       : TU_P_CUSTOMER_ACTIVE
--  Description   : Update trigger for custom partitioning (List-Range)
--                  Range sub-partition
--  Author        : Arnaud Fargues
--  Creation      : 04.02.2017
--  Modifications : 06.05.2017 AF Update
-- =======================================================================
DECLARE
   FUNCTION get_partition(p_birth_date DATE)
   RETURN NUMBER
   IS
      v_return NUMBER := 0;
   BEGIN
      CASE
          WHEN p_birth_date < TO_DATE('01.01.1980','DD.MM.YYYY')
             THEN v_return := 1;
          WHEN p_birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND p_birth_date < TO_DATE('01.01.1990','DD.MM.YYYY')
             THEN v_return := 2;
          WHEN p_birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND p_birth_date < TO_DATE('01.01.1995','DD.MM.YYYY')
             THEN v_return := 3;
          WHEN p_birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY')
             THEN v_return := 4;
          ELSE v_return := -1;
      END CASE;
      RETURN v_return;
   END;
BEGIN
   -- Same paritition
   IF get_partition(:old.birth_date) = get_partition(:new.birth_date)
   THEN
      CASE
         -- < 1980
         WHEN :old.birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN
            UPDATE DEV3.P_CUSTOMER_ACTIVE_MINVALUE
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                archive_state= :new.archive_state,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND archive_state = :old.archive_state
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
         -- >= 1980 and < 1990
         WHEN :old.birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN
            UPDATE DEV3.P_CUSTOMER_ACTIVE_1980
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                archive_state= :new.archive_state,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND archive_state = :old.archive_state
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date));
         -- >= 1990 and < 1995
         WHEN :old.birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN
            UPDATE DEV3.P_CUSTOMER_ACTIVE_1990
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                archive_state= :new.archive_state,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND archive_state = :old.archive_state
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
         -- >= 1995
         WHEN :old.birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') THEN
            UPDATE DEV3.P_CUSTOMER_ACTIVE_MAXVALUE
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                archive_state= :new.archive_state,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND archive_state = :old.archive_state
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));

      END CASE;
   ELSE

      -- Delete --
      DELETE DEV3.P_CUSTOMER_ACTIVE
      WHERE cust_id = :old.cust_id
        AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
        AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
        AND birth_date = :old.birth_date
        AND archive_state= :old.archive_state
        AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
        AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));

      -- Insert --
      INSERT INTO DEV3.P_CUSTOMER_ACTIVE
        (
         cust_id,
         first_name,
         last_name,
         birth_date,
         archive_state,
         creation_date,
         modification_date
        )
      VALUES
        (
         :new.cust_id,
         :new.first_name,
         :new.last_name,
         :new.birth_date,
         :new.archive_state,
         :new.creation_date,
         :new.modification_date
        );

   END IF;

END;

Basically, we must know if the updated row remains in the same partition or not. We have created the subprogram function get_partition in order to have a “partition id”.

09.05.2017

The DEV3.TU_P_CUSTOMER_ACTIVE trigger source code has been updated.

Custom Partitioning – Part IV – Hash partitioning – Instead Of Trigger – INSERT – DELETE – UPDATE

Scope

In this post we will use the hash partitioning distribution method.
We would like to partition the table DEV3.CUSTOMER_MODEL in five homogeneous partitions i.e. with nearly the same number of rows in each.

First approach

The cust_id column is a good candidate for the partitioning key because it is derived from a sequence. More precisely, we will use the following partitioning expression : MOD(ABS(cust_id),5)

Let’s execute the following query on the source table which will give us the number of rows in each of the future partitions using this distribution method:

DEV3@pdb1> SELECT 'HASH_CUST_' || MOD(ABS(cust_id),5) partition_name,
  2         COUNT(*)
  3  FROM DEV3.CUSTOMER_MODEL
  4  GROUP BY 'HASH_CUST_' || MOD(ABS(cust_id),5)
  5  ORDER BY 1;

PARTITION_NAME                                       COUNT(*)
-------------------------------------------------- ----------
HASH_CUST_0                                              2000
HASH_CUST_1                                              2000
HASH_CUST_2                                              2000
HASH_CUST_3                                              2000
HASH_CUST_4                                              2000

5 rows selected.

As you can see there is the same number of rows in each partition.

Second approach

In the standard hash partitioning distribution method the ora_hash function is used internally on the partitioning keys to distribute the rows in the different partitions. We will use in this case ORA_HASH((cust_id,4) as the partitioning expression.

Let’s execute the following query on the source table which will give us the number of rows in each of the future partitions using this distribution method:

DEV3@pdb1> SELECT 'HASH_CUST1_' || ORA_HASH((cust_id),4) partition_name,
  2         COUNT(*)
  3  FROM DEV3.CUSTOMER_MODEL
  4  GROUP BY 'HASH_CUST1_' || ORA_HASH((cust_id),4)
  5  ORDER BY 1;

PARTITION_NAME                                        COUNT(*)
--------------------------------------------------- ----------
HASH_CUST1_0                                              2045
HASH_CUST1_1                                              1992
HASH_CUST1_2                                              2010
HASH_CUST1_3                                              1979
HASH_CUST1_4                                              1974

5 rows selected.

The lines are much less well distributed than with the previous distribution method.

If you want more information on the ora_hash function linked to the hash partitioning distribution method, I suggest you to consult the post of Jonathan Lewis which can be found at https://jonathanlewis.wordpress.com/2009/11/21/ora_hash-function/.

We will choose the first approach to implement the hash partitioning.

Let’s create the five partitions:

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_HASH_CUST_0
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_HASH_CUST_1
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_HASH_CUST_2
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_HASH_CUST_3
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_HASH_CUST_4
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

Now we will create a view that is the UNION of all the previous tables (partitions).

DEV3@pdb1> CREATE OR REPLACE VIEW DEV3.CUSTOMER2
  2  AS
  3   SELECT * FROM DEV3.CUSTOMER_HASH_CUST_0
  4  UNION ALL
  5   SELECT * FROM DEV3.CUSTOMER_HASH_CUST_1
  6  UNION ALL
  7   SELECT * FROM DEV3.CUSTOMER_HASH_CUST_2
  8  UNION ALL
  9   SELECT * FROM DEV3.CUSTOMER_HASH_CUST_3
 10  UNION ALL
 11   SELECT * FROM DEV3.CUSTOMER_HASH_CUST_4;

View created.

We will also create a function that will compute the partition id as follow:

CREATE OR REPLACE FUNCTION GET_HASH_PARTITION_ID (p_cust_id NUMBER)
RETURN NUMBER AS
-- =======================================================================
--  Function      : GET_HASH_PARTITION_ID
--  Description   : Return the custom hash partitioning id
--  Author        : Arnaud Fargues
--  Creation      : 11.12.2016
--  Modifications : 
-- =======================================================================
   v_return NUMBER := 0;

BEGIN
   IF p_cust_id IS NULL
   THEN
      v_return := -1;
   ELSE
      v_return := MOD(ABS(p_cust_id),5);
   END IF;
   
   RETURN v_return;
   
END GET_HASH_PARTITION_ID;

We will create an instead of trigger for the insert operation in the same way than for the range partitioning:

Instead of Trigger – Insert

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TI_CUSTOMER2
INSTEAD OF INSERT ON DEV3.CUSTOMER2
-- =======================================================================
--  Trigger       : TI_CUSTOMER2
--  Description   : Insert trigger for custom partitioning (hash)
--  Author        : Arnaud Fargues
--  Creation      : 04.12.2016
--  Modifications : 07.01.2017 AF Update
-- =======================================================================
BEGIN

   CASE
      -- MOD(5) = 0
      WHEN get_hash_partition_id(:new.cust_id) = 0 THEN
         INSERT INTO DEV3.CUSTOMER_HASH_CUST_0
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
      -- MOD(5) = 1
      WHEN get_hash_partition_id(:new.cust_id) = 1 THEN
         INSERT INTO DEV3.CUSTOMER_HASH_CUST_1
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
      -- MOD(5) = 2
      WHEN get_hash_partition_id(:new.cust_id) = 2 THEN
         INSERT INTO DEV3.CUSTOMER_HASH_CUST_2
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
      -- MOD(5) = 3
      WHEN get_hash_partition_id(:new.cust_id) = 3 THEN
         INSERT INTO DEV3.CUSTOMER_HASH_CUST_3
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
      -- MOD(5) = 4
      WHEN get_hash_partition_id(:new.cust_id) IN (4,-1) THEN
         INSERT INTO DEV3.CUSTOMER_HASH_CUST_4
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
   END CASE;
END;

Remark: The value for partition id = -1 has been added in the last insert section (DEV3.CUSTOMER_HASH_CUST_4) in order to have all possible values for the get_hash_partition_id function in the CASE statement.

Now we will partition the source table as follow:

DEV3@pdb1> INSERT INTO DEV3.CUSTOMER2
  2    (
  3     cust_id,
  4     first_name,
  5     last_name,
  6     birth_date,
  7     creation_date,
  8     modification_date
  9    )
 10  SELECT *
 11  FROM DEV3.CUSTOMER_MODEL;

10000 rows created.

DEV3@pdb1> COMMIT;

Commit complete.

Let’s execute the following check query:

DEV3@pdb1>  SELECT 'CUSTOMER_HASH_CUST_0' part, COUNT(*)
  2   FROM DEV3.CUSTOMER_HASH_CUST_0
  3  UNION ALL
  4   SELECT 'CUSTOMER_HASH_CUST_1' part, COUNT(*)
  5   FROM DEV3.CUSTOMER_HASH_CUST_1
  6  UNION ALL
  7   SELECT 'CUSTOMER_HASH_CUST_2' part, COUNT(*)
  8   FROM DEV3.CUSTOMER_HASH_CUST_2
  9  UNION ALL
 10   SELECT 'CUSTOMER_HASH_CUST_3' part, COUNT(*)
 11   FROM DEV3.CUSTOMER_HASH_CUST_3
 12  UNION ALL
 13   SELECT 'CUSTOMER_HASH_CUST_4' part, COUNT(*)
 14   FROM DEV3.CUSTOMER_HASH_CUST_4;

PART                   COUNT(*)
-------------------- ----------
CUSTOMER_HASH_CUST_0       2000
CUSTOMER_HASH_CUST_1       2000
CUSTOMER_HASH_CUST_2       2000
CUSTOMER_HASH_CUST_3       2000
CUSTOMER_HASH_CUST_4       2000

5 rows selected.

Instead of Trigger – Delete

We will create an instead of trigger for the delete operation in the same way than for the range partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TD_CUSTOMER2
INSTEAD OF DELETE ON DEV3.CUSTOMER2
-- =======================================================================
--  Trigger       : TD_CUSTOMER2
--  Description   : Delete trigger for custom partitioning (hash)
--  Author        : Arnaud Fargues
--  Creation      : 10.12.2016
--  Modifications :
-- =======================================================================
BEGIN

   CASE
      -- MOD(5) = 0
      WHEN get_hash_partition_id(:old.cust_id) = 0 THEN
         DELETE DEV3.CUSTOMER_HASH_CUST_0
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- MOD(5) = 1
      WHEN get_hash_partition_id(:old.cust_id) = 1 THEN
         DELETE DEV3.CUSTOMER_HASH_CUST_1
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- MOD(5) = 2
      WHEN get_hash_partition_id(:old.cust_id) = 2 THEN
         DELETE DEV3.CUSTOMER_HASH_CUST_2
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- MOD(5) = 3
      WHEN get_hash_partition_id(:old.cust_id) = 3 THEN
         DELETE DEV3.CUSTOMER_HASH_CUST_3
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- MOD(5) = 4
      WHEN get_hash_partition_id(:old.cust_id) = 4 THEN
         DELETE DEV3.CUSTOMER_HASH_CUST_4
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));

  END CASE;
END;

Instead of Trigger – Update

We will create an instead of trigger for the update operation in the same way than for the range partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TU_CUSTOMER2
INSTEAD OF UPDATE ON DEV3.CUSTOMER2
-- =======================================================================
--  Trigger       : TU_CUSTOMER2
--  Description   : Update trigger for custom partitioning (hash)
--  Author        : Arnaud Fargues
--  Creation      : 10.12.2016
--  Modifications :
-- =======================================================================

BEGIN
   -- Same paritition
   IF get_hash_partition_id(:old.cust_id) = get_hash_partition_id(:new.cust_id)
   THEN
      CASE
         -- MOD(5) = 0
         WHEN get_hash_partition_id(:old.cust_id) = 0 THEN
            UPDATE DEV3.CUSTOMER_HASH_CUST_0
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
         -- MOD(5) = 1
         WHEN get_hash_partition_id(:old.cust_id) = 1 THEN
            UPDATE DEV3.CUSTOMER_HASH_CUST_1
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
         -- MOD(5) = 2
         WHEN get_hash_partition_id(:old.cust_id) = 2 THEN
            UPDATE DEV3.CUSTOMER_HASH_CUST_2
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
         -- MOD(5) = 3
         WHEN get_hash_partition_id(:old.cust_id) = 3 THEN
            UPDATE DEV3.CUSTOMER_HASH_CUST_3
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
         -- MOD(5) = 4
         WHEN get_hash_partition_id(:old.cust_id) = 4 THEN 
            UPDATE DEV3.CUSTOMER_HASH_CUST_4
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      END CASE;
   ELSE

      -- Delete --
      DELETE DEV3.CUSTOMER2
      WHERE cust_id = :old.cust_id
        AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
        AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
        AND birth_date = :old.birth_date
        AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
        AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));

      -- Insert --
      INSERT INTO DEV3.CUSTOMER2
        (
         cust_id,
         first_name,
         last_name,
         birth_date,
         creation_date,
         modification_date
        )
      VALUES
        (
         :new.cust_id,
         :new.first_name,
         :new.last_name,
         :new.birth_date,
         :new.creation_date,
         :new.modification_date
        );

   END IF;

END;

Basically, we must know if the updated row remains in the same partition or not.

Custom Partitioning – Part III – Range partitioning – Instead Of Trigger – INSERT – DELETE – UPDATE

Scope

In this post we will use the range partitioning distribution method.
We would like to partition the table DEV3.CUSTOMER_MODEL based on the birth_date column.

We will set up 4 partitions:

  • One for a birth_date < 01.01.1980,
  • One for a birth_date >= 01.01.1980 and < 01.01.1990,
  • One for a birth_date >= 01.01.1990 and < 01.01.1995,
  • One for a birth_date >= 01.01.1995.

Let’s create the four partitions:

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_BIRTH_DT_MINVALUE
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_BIRTH_DT_1980
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_BIRTH_DT_1990
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_BIRTH_DT_MAXVALUE
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

Remarks:

  • In a range partitioning method you can have a MAXVALUE partition that we have used,
  • We have also defined a MINVALUE partition which represents the first “temporal” partition e.g. based on the minimum birth_date.

Now we will create a view that is the UNION of all the previous tables (partitions).

DEV3@pdb1> CREATE OR REPLACE VIEW DEV3.CUSTOMER1
  2  AS
  3   SELECT * FROM DEV3.CUSTOMER_BIRTH_DT_MINVALUE
  4  UNION ALL
  5   SELECT * FROM DEV3.CUSTOMER_BIRTH_DT_1980
  6  UNION ALL
  7   SELECT * FROM DEV3.CUSTOMER_BIRTH_DT_1990
  8  UNION ALL
  9   SELECT * FROM DEV3.CUSTOMER_BIRTH_DT_MAXVALUE;

View created.

We will create an instead of trigger for the insert operation in the same way than for the list partitioning.

Instead of Trigger – Insert

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TI_CUSTOMER1
INSTEAD OF INSERT ON DEV3.CUSTOMER1 
-- =======================================================================
--  Trigger       : TI_CUSTOMER1
--  Description   : Insert trigger for custom partitioning (range)
--  Author        : Arnaud Fargues
--  Creation      : 20.11.2016
--  Modifications : 02.12.2016 AF Update
-- =======================================================================
BEGIN

   CASE 
      -- < 1980
      WHEN :new.birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN   
         INSERT INTO DEV3.CUSTOMER_BIRTH_DT_MINVALUE
           (
            cust_id,    
            first_name,  
            last_name, 
            birth_date,
            creation_date,      
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
      -- >= 1980 and < 1990 
      WHEN :new.birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND :new.birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN   
         INSERT INTO DEV3.CUSTOMER_BIRTH_DT_1980
           (
            cust_id,    
            first_name,  
            last_name,
            birth_date,
            creation_date,      
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
      -- >= 1990 and < 1995
      WHEN :new.birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND :new.birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN   
         INSERT INTO DEV3.CUSTOMER_BIRTH_DT_1990
           (
            cust_id,    
            first_name,  
            last_name,
            birth_date,
            creation_date,      
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
      -- >= 1995
      WHEN :new.birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') OR :new.birth_date IS NULL THEN   
         INSERT INTO DEV3.CUSTOMER_BIRTH_DT_MAXVALUE
           (
            cust_id,    
            first_name,  
            last_name,
            birth_date,
            creation_date,      
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
   END CASE;
END;

Note that the condition “:new.birth_date IS NULL” has been added in the last insert section (DEV3.CUSTOMER_BIRTH_DT_MAXVALUE) in order to have all possible values for birth_date column in the CASE statement.

What happens if we try to insert a NULL value in the birth_date column:

DEV3@pdb1> INSERT INTO DEV3.CUSTOMER1
  2    (
  3     cust_id,
  4     first_name,
  5     last_name,
  6     birth_date,
  7     creation_date,
  8     modification_date
  9    )
 10  VALUES
 11    (
 12     -9,
 13     '1',
 14     '2',
 15     NULL,
 16     NULL,
 17     NULL
 18     );
INSERT INTO DEV3.CUSTOMER1
                 *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("DEV3"."CUSTOMER_BIRTH_DT_MAXVALUE"."BIRTH_DATE")
ORA-06512: at "DEV3.TI_CUSTOMER1", line 66
ORA-04088: error during execution of trigger 'DEV3.TI_CUSTOMER1'

This message is not very clear because it doesn’t hide the instead of trigger implementation.

Let’s execute the following query on the source table which will give us the number of rows in each of the previous defined partitions:

DEV3@pdb1> SELECT SUM(p_minvalue) nb_p_minvalue,
  2         SUM(p_1980)     nb_p_1980,
  3         SUM(p_1990)     nb_p_1990,
  4         SUM(p_maxvalue) nb_p_maxvalue
  5  FROM
  6     (SELECT CASE
  7            WHEN birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN 1
  8            ELSE 0
  9         END p_minvalue,
 10         CASE
 11            WHEN birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN 1
 12            ELSE 0
 13         END p_1980,
 14         CASE
 15            WHEN birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN 1
 16            ELSE 0
 17         END p_1990,
 18         CASE
 19            WHEN birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') THEN 1
 20            ELSE 0
 21         END p_maxvalue
 22      FROM DEV3.CUSTOMER_MODEL
 23      );

NB_P_MINVALUE  NB_P_1980  NB_P_1990 NB_P_MAXVALUE
------------- ---------- ---------- -------------
         3236       3710       1751          1303

1 row selected.

Now we will partition the source table as follow:

DEV3@pdb1> INSERT INTO DEV3.CUSTOMER1
  2    (
  3     cust_id,
  4     first_name,
  5     last_name,
  6     birth_date,
  7     creation_date,
  8     modification_date
  9    )
 10  SELECT *
 11  FROM DEV3.CUSTOMER_MODEL;

10000 rows created.

DEV3@pdb1> COMMIT;

Commit complete.

Let’s execute the following check query:

DEV3@pdb1>  SELECT 'CUSTOMER_BIRTH_DT_MINVALUE' part, COUNT(*)
  2   FROM DEV3.CUSTOMER_BIRTH_DT_MINVALUE
  3  UNION ALL
  4   SELECT 'CUSTOMER_BIRTH_DT_1980' part, COUNT(*)
  5   FROM DEV3.CUSTOMER_BIRTH_DT_1980
  6  UNION ALL
  7   SELECT 'CUSTOMER_BIRTH_DT_1990' part, COUNT(*)
  8   FROM DEV3.CUSTOMER_BIRTH_DT_1990
  9  UNION ALL
 10   SELECT 'CUSTOMER_BIRTH_DT_MAXVALUE' part, COUNT(*)
 11   FROM DEV3.CUSTOMER_BIRTH_DT_MAXVALUE;

PART                         COUNT(*)
-------------------------- ----------
CUSTOMER_BIRTH_DT_MINVALUE       3236
CUSTOMER_BIRTH_DT_1980           3710
CUSTOMER_BIRTH_DT_1990           1751
CUSTOMER_BIRTH_DT_MAXVALUE       1303

4 rows selected.

Instead of Trigger – Delete

We will create an instead of trigger for the delete operation in the same way than for the list partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TD_CUSTOMER1 
INSTEAD OF DELETE ON DEV3.CUSTOMER1 
-- =======================================================================
--  Trigger       : TD_CUSTOMER1
--  Description   : Delete trigger for custom partitioning (range)
--  Author        : Arnaud Fargues
--  Creation      : 20.11.2016
--  Modifications : 
-- =======================================================================
BEGIN
  
   CASE 
      -- < 1980
      WHEN :old.birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN   
         DELETE DEV3.CUSTOMER_BIRTH_DT_MINVALUE
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date   
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
      -- >= 1980 and < 1990 
      WHEN :old.birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN   
         DELETE DEV3.CUSTOMER_BIRTH_DT_1980
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date   
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
      -- >= 1990 and < 1995 
      WHEN :old.birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN   
         DELETE DEV3.CUSTOMER_BIRTH_DT_1990
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date   
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
      -- >= 1995 
      WHEN :old.birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') THEN   
         DELETE DEV3.CUSTOMER_BIRTH_DT_MAXVALUE
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date   
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
   END CASE;
END;

Instead of Trigger – Update

We will create an instead of trigger for the update operation in the same way than for the list partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TU_CUSTOMER1 
INSTEAD OF UPDATE ON DEV3.CUSTOMER1 
-- =======================================================================
--  Trigger       : TU_CUSTOMER
--  Description   : Update trigger for custom partitioning (range)
--  Author        : Arnaud Fargues
--  Creation      : 20.11.2016
--  Modifications : 02.12.2016 AF Update
-- =======================================================================
DECLARE
   FUNCTION get_partition(v_birth_date DATE)
   RETURN NUMBER 
   IS
      v_return NUMBER := 0;
   BEGIN
      CASE
          WHEN v_birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') 
             THEN v_return := 1;
          WHEN v_birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND v_birth_date < TO_DATE('01.01.1990','DD.MM.YYYY')             
             THEN v_return := 2;
          WHEN v_birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND v_birth_date < TO_DATE('01.01.1995','DD.MM.YYYY')              
             THEN v_return := 3;
          WHEN v_birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY')              
             THEN v_return := 4;
          ELSE v_return := -1;
      END CASE;
      RETURN v_return;
   END;
BEGIN  
   -- Same paritition
   IF get_partition(:old.birth_date) = get_partition(:new.birth_date)
   THEN
      CASE 
         -- < 1980
         WHEN :old.birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN   
            UPDATE DEV3.CUSTOMER_BIRTH_DT_MINVALUE
            SET cust_id = :new.cust_id,   
                first_name = :new.first_name,   
                last_name= :new.last_name,   
                birth_date= :new.birth_date,   
                creation_date= :new.creation_date,        
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id  
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date   
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
         -- >= 1980 and < 1990 
         WHEN :old.birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN   
            UPDATE DEV3.CUSTOMER_BIRTH_DT_1980
            SET cust_id = :new.cust_id,   
                first_name = :new.first_name,   
                last_name= :new.last_name,   
                birth_date= :new.birth_date,   
                creation_date= :new.creation_date,        
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id  
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date   
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date));
         -- >= 1990 and < 1995
         WHEN :old.birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN   
            UPDATE DEV3.CUSTOMER_BIRTH_DT_1990
            SET cust_id = :new.cust_id,   
                first_name = :new.first_name,   
                last_name= :new.last_name,   
                birth_date= :new.birth_date,   
                creation_date= :new.creation_date,        
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id  
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date   
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
         -- >= 1995
         WHEN :old.birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') THEN   
            UPDATE DEV3.CUSTOMER_BIRTH_DT_MAXVALUE
            SET cust_id = :new.cust_id,   
                first_name = :new.first_name,   
                last_name= :new.last_name,   
                birth_date= :new.birth_date,   
                creation_date= :new.creation_date,        
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id  
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date   
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  

      END CASE;
   ELSE
      
      -- Delete --
      DELETE DEV3.CUSTOMER1
      WHERE cust_id = :old.cust_id  
        AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
        AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
        AND birth_date = :old.birth_date   
        AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
        AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
 
      -- Insert --
      INSERT INTO DEV3.CUSTOMER1
        (
         cust_id,    
         first_name,  
         last_name, 
         birth_date,
         creation_date,      
         modification_date
        )
      VALUES
        (
         :new.cust_id,
         :new.first_name,
         :new.last_name,
         :new.birth_date,
         :new.creation_date,
         :new.modification_date
        );
        
   END IF;
  
END;

Basically, we must know if the updated row remains in the same partition or not. We have created the subprogram function get_partition in order to have a “partition unique id”.

Custom Partitioning – Part II – List partitioning – Instead Of Trigger – DELETE – UPDATE

Instead of Trigger – Delete

The delete operation is similar to the insert operation e.g. we must find first in which partition the row is located. Nevertheless it is not as simple as it seems at first glance.
We must delete the rows based on all the “:old” column values (an instead of DML trigger is a row-level trigger) because currently we do not have any unique key on the partitioned table even if the cust_id column is filled with a sequence value. The partitioned index topic will be discussed in a later post.

Let’s try several approaches for the delete instead of trigger:

First approach – Standard

...
      -- A,E --
      WHEN v_old_partition_list IN ('A','E') THEN   
         DELETE DEV3.CUSTOMER_AE
         WHERE cust_id = :old.cust_id    
           AND first_name = :old.first_name   
           AND last_name = :old.last_name   
           AND birth_date = :old.birth_date   
           AND creation_date = :old.creation_date        
           AND modification_date = :old.modification_date;  
...

The issue with this approach is that the NULL values are not handled e.g. if the value :old.modification_date is NULL and a row has the column modification_date equal to NULL the condition modification_date= :old.modification_date will not be equal to TRUE.

Second approach – NVL

...
      -- A,E --
      WHEN v_old_partition_list IN ('A','E') THEN   
         DELETE DEV3.CUSTOMER_AE
         WHERE cust_id = :old.cust_id    
           AND NVL(first_name,'Unknown') = NVL(:old.first_name,'Unknown')  
           AND NVL(last_name,'Unknown') = NVL(:old.last_name,'Unknown')   
           AND birth_date = :old.birth_date   
           AND NVL(creation_date,TO_DATE('10000101','YYYYMMDD')) = NVL(:old.creation_date,TO_DATE('10000101','YYYYMMDD'))  
           AND NVL(modification_date,TO_DATE('10000101','YYYYMMDD')) = NVL(:old.modification_date,TO_DATE('10000101','YYYYMMDD'));  
...

We have added the NVL function to compare nullable columns with a default value. The issue with this approach is that the default value that we use, can be an existing value in the column e.g. we could have a row whose first_name value is set to ‘Unknown’.

Third approach – DECODE

...
      -- A,E --
      WHEN v_old_partition_list IN ('A','E') THEN   
         DELETE DEV3.CUSTOMER_AE
         WHERE cust_id = :old.cust_id    
           AND DECODE(first_name,:old.first_name,1,0) = 1
           AND DECODE(last_name,:old.last_name,1,0) = 1 
           AND birth_date = :old.birth_date   
           AND DECODE(creation_date,:old.creation_date,1,0) = 1 
           AND DECODE(modification_date,:old.modification_date,1,0) = 1;  
...

We have added the DECODE function to compare nullable columns. DECODE is one of the few functions that can compare NULL values. A drawback is that the decode function works with numeric and character types. It can not handle directly clob/lob datatypes.

Fourth approach – IS NULL – IS NOT NULL

...
      -- A,E --
      WHEN v_old_partition_list IN ('A','E') THEN   
         DELETE DEV3.CUSTOMER_AE
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date   
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
...

For each nullable column we have made a condition for NULL values and a condition for not NULL values. This approach can not also handle directly clob/lob datatypes, nevertheless we will choose this one to implement the delete operation.

You will find below the source code of instead the trigger for the delete operation:

CREATE OR REPLACE TRIGGER DEV3.TD_CUSTOMER 
INSTEAD OF DELETE ON DEV3.CUSTOMER 
-- =======================================================================
--  Trigger       : TD_CUSTOMER
--  Description   : Delete trigger for custom partitioning (list)
--  Author        : Arnaud Fargues
--  Creation      : 01.10.2016
--  Modifications : 
-- =======================================================================
DECLARE
   v_old_partition_list VARCHAR2(5);
BEGIN
   v_old_partition_list := SUBSTR(UPPER(:old.last_name),1,1);
  
   CASE 
      -- A,E --
      WHEN v_old_partition_list IN ('A','E') THEN   
         DELETE DEV3.CUSTOMER_AE
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date   
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
      -- B --      
      WHEN v_old_partition_list IN ('B') THEN    
         DELETE DEV3.CUSTOMER_B
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date   
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
      -- Default --      
      ELSE
         DELETE DEV3.CUSTOMER_DEFAULT
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date   
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
  END CASE;
END;

Instead of Trigger – Update

The update operation is more complicated than the other ones, because we must first know if we are going to move the row from one partition to another partition.

You will find below the source code of the instead of trigger for the update operation:

CREATE OR REPLACE TRIGGER DEV3.TU_CUSTOMER 
INSTEAD OF UPDATE ON DEV3.CUSTOMER 
-- =======================================================================
--  Trigger       : TU_CUSTOMER
--  Description   : Update trigger for custom partitioning (list)
--  Author        : Arnaud Fargues
--  Creation      : 01.10.2016
--  Modifications : 
-- =======================================================================
DECLARE
   v_old_partition_list VARCHAR2(5);
   v_new_partition_list VARCHAR2(5);
BEGIN
   v_old_partition_list := SUBSTR(UPPER(:old.last_name),1,1);
   v_new_partition_list := SUBSTR(UPPER(:new.last_name),1,1);
  
   -- Same partition
   IF (    (v_old_partition_list IS NULL AND v_new_partition_list IS NULL) 
        OR ((v_old_partition_list IS NOT NULL AND v_new_partition_list IS NOT NULL) AND v_old_partition_list = v_new_partition_list))
   THEN
      CASE 
         -- A,E --
         WHEN v_old_partition_list IN ('A','E') THEN   
            UPDATE DEV3.CUSTOMER_AE
            SET cust_id = :new.cust_id,   
                first_name = :new.first_name,   
                last_name= :new.last_name,   
                birth_date= :new.birth_date,   
                creation_date= :new.creation_date,        
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id  
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date   
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
         -- B --      
         WHEN v_old_partition_list IN ('B') THEN    
            UPDATE DEV3.CUSTOMER_B
            SET cust_id = :new.cust_id,   
                first_name = :new.first_name,   
                last_name= :new.last_name,   
                birth_date= :new.birth_date,   
                creation_date= :new.creation_date,        
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id  
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date   
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  

         -- Default --      
         ELSE
            UPDATE DEV3.CUSTOMER_DEFAULT
            SET cust_id = :new.cust_id,   
                first_name = :new.first_name,   
                last_name= :new.last_name,   
                birth_date= :new.birth_date,   
                creation_date= :new.creation_date,        
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id  
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date   
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  

      END CASE;
   ELSE
      
      -- Delete --
      DELETE DEV3.CUSTOMER
      WHERE cust_id = :old.cust_id  
        AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
        AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
        AND birth_date = :old.birth_date   
        AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
        AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
 
      -- Insert --
      INSERT INTO DEV3.CUSTOMER
        (
         cust_id,    
         first_name,  
         last_name, 
         birth_date,
         creation_date,      
         modification_date
        )
      VALUES
        (
         :new.cust_id,
         :new.first_name,
         :new.last_name,
         :new.birth_date,
         :new.creation_date,
         :new.modification_date
        );
        
   END IF;
  
END;

Basically, if the updated row remains in the same partition, we do an update, meanwhile if the updated row moves to another partition we must first delete the row from the “old” partition and insert it into the “new” partition. Like the delete operation we must update the rows based on all the “:old” column values.

Remark:

  • Currently you can not issue a merge statement on the CUSTOMER view.

Warning : The custom list partitioning has only be tested on development databases!

Custom Partitioning – Part I – List partitioning – Instead Of Trigger

Scope

In this series of post, we will focus on partitioning tables without having the “Partitioning” option which is an option of Oracle Database Enterprise Edition.
We will start in a first step to partition tables.

Partitioning concepts

Basically a partitioned table is a set of several sub-tables called partitions. The partitioning is based on one or more fields (called partitioning key) and on a distribution method:

The main distribution methods are:

  • List partitioning,
  • Range partitioning,
  • Hash partitioning.

You can have a single level partitioning e.g. only one distribution method on a table or a composite partitioning where we apply a first distribution method on a table, and a second distribution method on the partitions (which give us sub-partitions).

CUSTOMER table example

In the rest of this post we will use a 12cR1 PDB database.

Let’s create a CUSTOMER_MODEL table in the DEV3 schema as follow:

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_MODEL
  2  (  cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4     last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

and a sequence:

DEV3@pdb1> CREATE SEQUENCE  DEV3.S_CUSTOMER INCREMENT BY 1 START WITH 1 CACHE 20 NOCYCLE;

Sequence created.

Let’s now fill this table:

DEV3@pdb1> INSERT INTO DEV3.CUSTOMER_MODEL
  2    (
  3     cust_id,
  4     first_name,
  5     last_name,
  6     birth_date,
  7     creation_date,
  8     modification_date
  9    )
 10  SELECT DEV3.S_CUSTOMER.NEXTVAL,
 11         DBMS_RANDOM.STRING('a',TRUNC(DBMS_RANDOM.VALUE(8,20))),
 12         DBMS_RANDOM.STRING('u',TRUNC(DBMS_RANDOM.VALUE(8,20))),
 13         SYSDATE - (366*18) - TRUNC(DBMS_RANDOM.VALUE(1,10000)),
 14         SYSDATE,
 15         NULL
 16  FROM DUAL
 17  CONNECT BY LEVEL <= 10000;

10000 rows created.

DEV3@pdb1> COMMIT;

Commit complete.

At this point we would like to partition this table based on the first character of the last_name column.
In this case the partitioning key is similar to a virtual column because this expression is not a base column of the table, and the distribution method is similar to a List partitioning method.

Remark: In a List partitioning method there is a default partition that we will use.

Custom partitioning principles

We will set up 3 partitions:

  • One for a last_name column begining with an A or an E,
  • One for a last_name column begining with an B,
  • One for the other cases.

Let’s create the partitions:

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_AE
  2  (  cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4     last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_B
  2  (  cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4     last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_DEFAULT
  2  (  cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4     last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

Now we will create a view that is the UNION of all the previous tables (partitions).

DEV3@pdb1> CREATE OR REPLACE VIEW DEV3.CUSTOMER
  2  AS
  3   SELECT * FROM DEV3.CUSTOMER_AE
  4  UNION ALL
  5   SELECT * FROM DEV3.CUSTOMER_B
  6  UNION ALL
  7   SELECT * FROM DEV3.CUSTOMER_DEFAULT;

View created.

At this point, we can only select rows of the partitioned table (CUSTOMER) but we can not insert, update and delete rows directly through this view.
Nevertheless, we could manually copy the rows of the table CUSTOMER_MODEL whose first character of the last_name is ‘B’ in the following way:

INSERT INTO DEV3.CUSTOMER_B
  (
   cust_id,    
   first_name,  
   last_name, 
   birth_date,
   creation_date,      
   modification_date
  )       
SELECT *
FROM DEV3.CUSTOMER_MODEL
WHERE SUBSTR(UPPER(last_name),1,1) = 'B';

and we could manually also delete and update a row by finding first in which partition it is.

We could have a more transparent process using the Instead of trigger.

Instead of Trigger – Insert

This kind of trigger is used to update for example a non updatable view which is our case.

We will start with the insert operation:

CREATE OR REPLACE TRIGGER DEV3.TI_CUSTOMER 
INSTEAD OF INSERT ON DEV3.CUSTOMER
-- =======================================================================
--  Trigger       : TI_CUSTOMER
--  Description   : Insert trigger for custom partitioning (list)
--  Author        : Arnaud Fargues
--  Creation      : 20.09.2016
--  Modifications : 
-- =======================================================================
DECLARE
   v_partition_list VARCHAR2(5);
BEGIN
   v_partition_list := SUBSTR(UPPER(:new.last_name),1,1);
  
   CASE 
      -- A,E --
      WHEN v_partition_list IN ('A','E') THEN   
         INSERT INTO DEV3.CUSTOMER_AE
           (
            cust_id,    
            first_name,  
            last_name, 
            birth_date,
            creation_date,      
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
      -- B --      
      WHEN v_partition_list IN ('B') THEN    
         INSERT INTO DEV3.CUSTOMER_B
           (
            cust_id,    
            first_name,  
            last_name,
            birth_date,
            creation_date,      
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
      -- Default --      
      ELSE
         INSERT INTO DEV3.CUSTOMER_DEFAULT
           (
            cust_id,    
            first_name,  
            last_name,
            birth_date,
            creation_date,      
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
   END CASE;
END;

Let’s execute the following query on the source table:

DEV3@pdb1> SELECT SUBSTR(UPPER(last_name),1,1) fl,
  2         COUNT(*)
  3  FROM DEV3.CUSTOMER_MODEL
  4  GROUP BY CUBE(SUBSTR(UPPER(last_name),1,1))
  5  ORDER BY 1;

FL     COUNT(*)
---- ----------
A           374
B           386
C           419
D           413
E           403
F           360
G           425
H           363
I           415
J           359
K           399
L           359
M           394
N           373
O           362
P           359
Q           353
R           390
S           438
T           360
U           373
V           416
W           375
X           369
Y           363
Z           400
          10000

27 rows selected.

We have:

  • 777 rows whose last_name is begining with A or E,
  • 386 rows whose last_name is begining with B.

Now we will partition the source table as follow:

DEV3@pdb1> INSERT INTO DEV3.CUSTOMER
  2    (
  3     cust_id,
  4     first_name,
  5     last_name,
  6     birth_date,
  7     creation_date,
  8     modification_date
  9    )
 10  SELECT *
 11  FROM DEV3.CUSTOMER_MODEL;

10000 rows created.

DEV3@pdb1> COMMIT;

Commit complete.

Let’s execute the following check query:

DEV3@pdb1>  SELECT 'CUSTOMER_AE' part, COUNT(*)
  2   FROM DEV3.CUSTOMER_AE
  3  UNION ALL
  4   SELECT 'CUSTOMER_B' part, COUNT(*)
  5   FROM DEV3.CUSTOMER_B
  6  UNION ALL
  7   SELECT 'CUSTOMER_DEFAULT' part, COUNT(*)
  8   FROM DEV3.CUSTOMER_DEFAULT;

PART               COUNT(*)
---------------- ----------
CUSTOMER_AE             777
CUSTOMER_B              386
CUSTOMER_DEFAULT       8837

3 rows selected.

Remarks:

  • If you have already a trigger on the table to be partitioned, you might integrate this behaviour in the Instead of trigger,
  • We could have create a virtual column on the source table equal to “SUBSTR(UPPER(last_name),1,1)” and use it in the Instead of trigger.

Shortly:

We will see how to delete and update rows in the partitioned table.