Oracle database experiences

Oracle database blog

Custom Partitioning – Part V – List-Range partitioning – Instead Of Trigger – Multi level partitioning 1

Scope

In this post we will use a composite partitioning that we will call a multi-level partitioning and more precisely an homogeneous multi-level partitioning.

Case study

Let’s create the CUSTOMER_MODEL_M table as follow in the DEV3 schema:

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_MODEL_M
  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)        DEFAULT 'N' NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

and a sequence:

DEV3@pdb1> CREATE SEQUENCE  DEV3.S_CUSTOMER_M INCREMENT BY 1 START WITH 1 CACHE 20 NOCYCLE;

Sequence created.

Let’s now fill this table:

DEV3@pdb1> INSERT INTO DEV3.CUSTOMER_MODEL_M
  2    (
  3     cust_id,
  4     first_name,
  5     last_name,
  6     birth_date,
  7     creation_date,
  8     modification_date
  9    )
 10  SELECT DEV3.S_CUSTOMER_M.NEXTVAL,
 11         DBMS_RANDOM.STRING('a',TRUNC(DBMS_RANDOM.VALUE(8,20))),
 12         DBMS_RANDOM.STRING('u',TRUNC(DBMS_RANDOM.VALUE(8,20))),
 13         SYSDATE - (366*18) - TRUNC(DBMS_RANDOM.VALUE(1,10000)),
 14         SYSDATE,
 15         NULL
 16  FROM DUAL
 17  CONNECT BY LEVEL <= 10000;

10000 rows created.

DEV3@pdb1> COMMIT;

Commit complete.

Basically, the column archive_state indicates that a row is archived (‘Y’) or not archived (‘N’).
We will archive some rows as follow:

DEV3@pdb1> UPDATE DEV3.CUSTOMER_MODEL_M
  2  SET archive_state = 'Y'
  3  WHERE cust_id < 8001;

8000 rows updated.

DEV3@pdb1> COMMIT;

Commit complete.

We would like to partition this table firstly on the key archive_state column using a list partitioning distribution method and secondly on the key birth_date column using a range partitioning distribution method.
In our multi-level partitioning paradigm, the list partitioning is the first level and the range partitioning is the second level. We call it homogeneous, because there is the same distribution method at each level.

The following figure describes this case:

listrange01

We will start by implementing the “Part 1” e.g. the range partitioning for the ‘N’ archive_state column rows.
We will set up 4 sub-partitions in the same way than in 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_ACTIVE_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_ACTIVE_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_ACTIVE_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_ACTIVE_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_ACTIVE
  2  AS
  3   SELECT * FROM DEV3.P_CUSTOMER_ACTIVE_MINVALUE
  4  UNION ALL
  5   SELECT * FROM DEV3.P_CUSTOMER_ACTIVE_1980
  6  UNION ALL
  7   SELECT * FROM DEV3.P_CUSTOMER_ACTIVE_1990
  8  UNION ALL
  9   SELECT * FROM DEV3.P_CUSTOMER_ACTIVE_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_ACTIVE
INSTEAD OF INSERT ON DEV3.P_CUSTOMER_ACTIVE
-- =======================================================================
--  Trigger       : TI_P_CUSTOMER_ACTIVE
--  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_ACTIVE_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_ACTIVE_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_ACTIVE_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_ACTIVE_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_ACTIVE
INSTEAD OF DELETE ON DEV3.P_CUSTOMER_ACTIVE
-- =======================================================================
--  Trigger       : TD_P_CUSTOMER_ACTIVE
--  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_ACTIVE_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_ACTIVE_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_ACTIVE_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_ACTIVE_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_ACTIVE
INSTEAD OF UPDATE ON DEV3.P_CUSTOMER_ACTIVE
-- =======================================================================
--  Trigger       : TU_P_CUSTOMER_ACTIVE
--  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_ACTIVE_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_ACTIVE_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_ACTIVE_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_ACTIVE_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_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));

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

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