Oracle database experiences

Oracle database blog

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”.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: