Oracle database experiences

Oracle database blog

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.

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: