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.
Oracle database experiences 