Oracle database experiences

Oracle database blog

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!

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: