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'));

  .