Oracle database experiences

Oracle database blog

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