Oracle database experiences

Oracle database blog

Custom Partitioning – Part IV – Hash partitioning – Instead Of Trigger – INSERT – DELETE – UPDATE

Scope

In this post we will use the hash partitioning distribution method.
We would like to partition the table DEV3.CUSTOMER_MODEL in five homogeneous partitions i.e. with nearly the same number of rows in each.

First approach

The cust_id column is a good candidate for the partitioning key because it is derived from a sequence. More precisely, we will use the following partitioning expression : MOD(ABS(cust_id),5)

Let’s execute the following query on the source table which will give us the number of rows in each of the future partitions using this distribution method:

DEV3@pdb1> SELECT 'HASH_CUST_' || MOD(ABS(cust_id),5) partition_name,
  2         COUNT(*)
  3  FROM DEV3.CUSTOMER_MODEL
  4  GROUP BY 'HASH_CUST_' || MOD(ABS(cust_id),5)
  5  ORDER BY 1;

PARTITION_NAME                                       COUNT(*)
-------------------------------------------------- ----------
HASH_CUST_0                                              2000
HASH_CUST_1                                              2000
HASH_CUST_2                                              2000
HASH_CUST_3                                              2000
HASH_CUST_4                                              2000

5 rows selected.

As you can see there is the same number of rows in each partition.

Second approach

In the standard hash partitioning distribution method the ora_hash function is used internally on the partitioning keys to distribute the rows in the different partitions. We will use in this case ORA_HASH((cust_id,4) as the partitioning expression.

Let’s execute the following query on the source table which will give us the number of rows in each of the future partitions using this distribution method:

DEV3@pdb1> SELECT 'HASH_CUST1_' || ORA_HASH((cust_id),4) partition_name,
  2         COUNT(*)
  3  FROM DEV3.CUSTOMER_MODEL
  4  GROUP BY 'HASH_CUST1_' || ORA_HASH((cust_id),4)
  5  ORDER BY 1;

PARTITION_NAME                                        COUNT(*)
--------------------------------------------------- ----------
HASH_CUST1_0                                              2045
HASH_CUST1_1                                              1992
HASH_CUST1_2                                              2010
HASH_CUST1_3                                              1979
HASH_CUST1_4                                              1974

5 rows selected.

The lines are much less well distributed than with the previous distribution method.

If you want more information on the ora_hash function linked to the hash partitioning distribution method, I suggest you to consult the post of Jonathan Lewis which can be found at https://jonathanlewis.wordpress.com/2009/11/21/ora_hash-function/.

We will choose the first approach to implement the hash partitioning.

Let’s create the five partitions:

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_HASH_CUST_0
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_HASH_CUST_1
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_HASH_CUST_2
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_HASH_CUST_3
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_HASH_CUST_4
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

Now we will create a view that is the UNION of all the previous tables (partitions).

DEV3@pdb1> CREATE OR REPLACE VIEW DEV3.CUSTOMER2
  2  AS
  3   SELECT * FROM DEV3.CUSTOMER_HASH_CUST_0
  4  UNION ALL
  5   SELECT * FROM DEV3.CUSTOMER_HASH_CUST_1
  6  UNION ALL
  7   SELECT * FROM DEV3.CUSTOMER_HASH_CUST_2
  8  UNION ALL
  9   SELECT * FROM DEV3.CUSTOMER_HASH_CUST_3
 10  UNION ALL
 11   SELECT * FROM DEV3.CUSTOMER_HASH_CUST_4;

View created.

We will also create a function that will compute the partition id as follow:

CREATE OR REPLACE FUNCTION GET_HASH_PARTITION_ID (p_cust_id NUMBER)
RETURN NUMBER AS
-- =======================================================================
--  Function      : GET_HASH_PARTITION_ID
--  Description   : Return the custom hash partitioning id
--  Author        : Arnaud Fargues
--  Creation      : 11.12.2016
--  Modifications : 
-- =======================================================================
   v_return NUMBER := 0;

BEGIN
   IF p_cust_id IS NULL
   THEN
      v_return := -1;
   ELSE
      v_return := MOD(ABS(p_cust_id),5);
   END IF;
   
   RETURN v_return;
   
END GET_HASH_PARTITION_ID;

We will create an instead of trigger for the insert operation in the same way than for the range partitioning:

Instead of Trigger – Insert

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TI_CUSTOMER2
INSTEAD OF INSERT ON DEV3.CUSTOMER2
-- =======================================================================
--  Trigger       : TI_CUSTOMER2
--  Description   : Insert trigger for custom partitioning (hash)
--  Author        : Arnaud Fargues
--  Creation      : 04.12.2016
--  Modifications : 07.01.2017 AF Update
-- =======================================================================
BEGIN

   CASE
      -- MOD(5) = 0
      WHEN get_hash_partition_id(:new.cust_id) = 0 THEN
         INSERT INTO DEV3.CUSTOMER_HASH_CUST_0
           (
            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
           );
      -- MOD(5) = 1
      WHEN get_hash_partition_id(:new.cust_id) = 1 THEN
         INSERT INTO DEV3.CUSTOMER_HASH_CUST_1
           (
            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
           );
      -- MOD(5) = 2
      WHEN get_hash_partition_id(:new.cust_id) = 2 THEN
         INSERT INTO DEV3.CUSTOMER_HASH_CUST_2
           (
            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
           );
      -- MOD(5) = 3
      WHEN get_hash_partition_id(:new.cust_id) = 3 THEN
         INSERT INTO DEV3.CUSTOMER_HASH_CUST_3
           (
            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
           );
      -- MOD(5) = 4
      WHEN get_hash_partition_id(:new.cust_id) IN (4,-1) THEN
         INSERT INTO DEV3.CUSTOMER_HASH_CUST_4
           (
            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 CASE;
END;

Remark: The value for partition id = -1 has been added in the last insert section (DEV3.CUSTOMER_HASH_CUST_4) in order to have all possible values for the get_hash_partition_id function in the CASE statement.

Now we will partition the source table as follow:

DEV3@pdb1> INSERT INTO DEV3.CUSTOMER2
  2    (
  3     cust_id,
  4     first_name,
  5     last_name,
  6     birth_date,
  7     creation_date,
  8     modification_date
  9    )
 10  SELECT *
 11  FROM DEV3.CUSTOMER_MODEL;

10000 rows created.

DEV3@pdb1> COMMIT;

Commit complete.

Let’s execute the following check query:

DEV3@pdb1>  SELECT 'CUSTOMER_HASH_CUST_0' part, COUNT(*)
  2   FROM DEV3.CUSTOMER_HASH_CUST_0
  3  UNION ALL
  4   SELECT 'CUSTOMER_HASH_CUST_1' part, COUNT(*)
  5   FROM DEV3.CUSTOMER_HASH_CUST_1
  6  UNION ALL
  7   SELECT 'CUSTOMER_HASH_CUST_2' part, COUNT(*)
  8   FROM DEV3.CUSTOMER_HASH_CUST_2
  9  UNION ALL
 10   SELECT 'CUSTOMER_HASH_CUST_3' part, COUNT(*)
 11   FROM DEV3.CUSTOMER_HASH_CUST_3
 12  UNION ALL
 13   SELECT 'CUSTOMER_HASH_CUST_4' part, COUNT(*)
 14   FROM DEV3.CUSTOMER_HASH_CUST_4;

PART                   COUNT(*)
-------------------- ----------
CUSTOMER_HASH_CUST_0       2000
CUSTOMER_HASH_CUST_1       2000
CUSTOMER_HASH_CUST_2       2000
CUSTOMER_HASH_CUST_3       2000
CUSTOMER_HASH_CUST_4       2000

5 rows selected.

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_CUSTOMER2
INSTEAD OF DELETE ON DEV3.CUSTOMER2
-- =======================================================================
--  Trigger       : TD_CUSTOMER2
--  Description   : Delete trigger for custom partitioning (hash)
--  Author        : Arnaud Fargues
--  Creation      : 10.12.2016
--  Modifications :
-- =======================================================================
BEGIN

   CASE
      -- MOD(5) = 0
      WHEN get_hash_partition_id(:old.cust_id) = 0 THEN
         DELETE DEV3.CUSTOMER_HASH_CUST_0
         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));
      -- MOD(5) = 1
      WHEN get_hash_partition_id(:old.cust_id) = 1 THEN
         DELETE DEV3.CUSTOMER_HASH_CUST_1
         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));
      -- MOD(5) = 2
      WHEN get_hash_partition_id(:old.cust_id) = 2 THEN
         DELETE DEV3.CUSTOMER_HASH_CUST_2
         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));
      -- MOD(5) = 3
      WHEN get_hash_partition_id(:old.cust_id) = 3 THEN
         DELETE DEV3.CUSTOMER_HASH_CUST_3
         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));
      -- MOD(5) = 4
      WHEN get_hash_partition_id(:old.cust_id) = 4 THEN
         DELETE DEV3.CUSTOMER_HASH_CUST_4
         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

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_CUSTOMER2
INSTEAD OF UPDATE ON DEV3.CUSTOMER2
-- =======================================================================
--  Trigger       : TU_CUSTOMER2
--  Description   : Update trigger for custom partitioning (hash)
--  Author        : Arnaud Fargues
--  Creation      : 10.12.2016
--  Modifications :
-- =======================================================================

BEGIN
   -- Same paritition
   IF get_hash_partition_id(:old.cust_id) = get_hash_partition_id(:new.cust_id)
   THEN
      CASE
         -- MOD(5) = 0
         WHEN get_hash_partition_id(:old.cust_id) = 0 THEN
            UPDATE DEV3.CUSTOMER_HASH_CUST_0
            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));
         -- MOD(5) = 1
         WHEN get_hash_partition_id(:old.cust_id) = 1 THEN
            UPDATE DEV3.CUSTOMER_HASH_CUST_1
            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));
         -- MOD(5) = 2
         WHEN get_hash_partition_id(:old.cust_id) = 2 THEN
            UPDATE DEV3.CUSTOMER_HASH_CUST_2
            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));
         -- MOD(5) = 3
         WHEN get_hash_partition_id(:old.cust_id) = 3 THEN
            UPDATE DEV3.CUSTOMER_HASH_CUST_3
            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));
         -- MOD(5) = 4
         WHEN get_hash_partition_id(:old.cust_id) = 4 THEN 
            UPDATE DEV3.CUSTOMER_HASH_CUST_4
            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.CUSTOMER2
      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.CUSTOMER2
        (
         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, we must know if the updated row remains in the same partition or not.

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: