Scope
In the last post, we have implemented a list-range partitioning. In this post, we would like to partition the sub-partition DEV3.P_CUSTOMER_ARCHIVED_1990 based on the first character of the last_name column.
This will lead to a List-Range-List partitioning. This composite partitioning will be called an heterogeneous multi-level partitioning, because the level 3 list partitioning will be applied only to the DEV3.P_CUSTOMER_ARCHIVED_1990 sub-partition. The following figure shows this partitioning:
We will set up 3 sub-sub-partitions:
- One for a last_name begining with an A or an E,
- One for a last_name begining with an B,
- One for the other case.
Let’s create the sub-sub-partitions:
DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ARCHIVED_1990_AE
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_B
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_DEF
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.
We will drop the table DEV3.P_CUSTOMER_ARCHIVED_1990:
DEV3@pdb1> DROP TABLE DEV3.P_CUSTOMER_ARCHIVED_1990;
Table dropped.
and then we will create a view with the same name that is the UNION of the three previous tables (sub-sub-partitions):
DEV3@pdb1> CREATE OR REPLACE VIEW DEV3.P_CUSTOMER_ARCHIVED_1990
2 AS
3 SELECT * FROM DEV3.P_CUSTOMER_ARCHIVED_1990_AE
4 UNION ALL
5 SELECT * FROM DEV3.P_CUSTOMER_ARCHIVED_1990_B
6 UNION ALL
7 SELECT * FROM DEV3.P_CUSTOMER_ARCHIVED_1990_DEF;
View created.
Instead of Trigger – Insert
We will create an instead of trigger for the insert operation in the same way than for the list partitioning:
You will find below the source code of this trigger:
CREATE OR REPLACE TRIGGER DEV3.TI_P_CUSTOMER_ARCHIVED_1990
INSTEAD OF INSERT ON DEV3.P_CUSTOMER_ARCHIVED_1990
-- =======================================================================
-- Trigger : TI_P_CUSTOMER_ARCHIVED_1990
-- Description : Insert trigger for custom partitioning (List-Range-List)
-- List partition
-- Author : Arnaud Fargues
-- Creation : 06.05.2017
-- Modifications :
-- =======================================================================
DECLARE
v_partition_list VARCHAR2(5);
BEGIN
v_partition_list := SUBSTR(UPPER(:new.last_name),1,1);
CASE
-- A,E --
WHEN v_partition_list IN ('A','E') THEN
INSERT INTO DEV3.P_CUSTOMER_ARCHIVED_1990_AE
(
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
);
-- B --
WHEN v_partition_list IN ('B') THEN
INSERT INTO DEV3.P_CUSTOMER_ARCHIVED_1990_B
(
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
);
-- Default --
ELSE
INSERT INTO DEV3.P_CUSTOMER_ARCHIVED_1990_DEF
(
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 list partitioning:
You will find below the source code of this trigger:
CREATE OR REPLACE TRIGGER DEV3.TD_P_CUSTOMER_ARCHIVED_1990
INSTEAD OF DELETE ON DEV3.P_CUSTOMER_ARCHIVED_1990
-- =======================================================================
-- Trigger : TD_P_CUSTOMER_ARCHIVED_1990
-- Description : Delete trigger for custom partitioning (List-Range-List)
-- List partition
-- Author : Arnaud Fargues
-- Creation : 06.05.2017
-- 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.P_CUSTOMER_ARCHIVED_1990_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 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));
-- B --
WHEN v_old_partition_list IN ('B') THEN
DELETE DEV3.P_CUSTOMER_ARCHIVED_1990_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 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));
-- Default --
ELSE
DELETE DEV3.P_CUSTOMER_ARCHIVED_1990_DEF
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 list partitioning:
You will find below the source code of this trigger:
CREATE OR REPLACE TRIGGER DEV3.TU_P_CUSTOMER_ARCHIVED_1990
INSTEAD OF UPDATE ON DEV3.P_CUSTOMER_ARCHIVED_1990
-- =======================================================================
-- Trigger : TU_P_CUSTOMER_ARCHIVED_1990
-- Description : Update trigger for custom partitioning (List-Range-List)
-- List partition
-- Author : Arnaud Fargues
-- Creation : 06.05.2017
-- 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 paritition
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.P_CUSTOMER_ARCHIVED_1990_AE
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));
-- B --
WHEN v_old_partition_list IN ('B') THEN
UPDATE DEV3.P_CUSTOMER_ARCHIVED_1990_B
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));
-- Default --
ELSE
UPDATE DEV3.P_CUSTOMER_ARCHIVED_1990_DEF
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_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));
-- Insert --
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
);
END IF;
END;
We will delete the rows of all the underlying partitions as follow:
DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ACTIVE_MINVALUE;
Table truncated.
DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ACTIVE_1980;
Table truncated.
DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ACTIVE_1990;
Table truncated.
DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ACTIVE_MAXVALUE;
Table truncated.
DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ARCHIVED_MINVALUE;
Table truncated.
DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ARCHIVED_1980;
Table truncated.
DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ARCHIVED_MAXVALUE;
Table truncated.
DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ARCHIVED_1990_AE;
Table truncated.
DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ARCHIVED_1990_B;
Table truncated.
DEV3@pdb1> TRUNCATE TABLE DEV3.P_CUSTOMER_ARCHIVED_1990_DEF;
Table truncated.
Now we will partition (List-Range-List) the source table as follow:
DEV3@pdb1> INSERT INTO DEV3.CUSTOMER_LIST_RANGE
2 (
3 cust_id,
4 first_name,
5 last_name,
6 birth_date,
7 archive_state,
8 creation_date,
9 modification_date
10 )
11 SELECT *
12 FROM DEV3.CUSTOMER_MODEL_M;
10000 rows created.
DEV3@pdb1> COMMIT;
Commit complete.
Remark: We have kept the previous view name DEV3.CUSTOMER_LIST_RANGE instead of a DEV3.CUSTOMER_LIST_RANGE_LIST name.
Let’s find an archived row where birth_date >= 01.01.1990 and < 01.01.1995 and last_name begins with a 'B':
DEV3@pdb1> SELECT cust_id,
2 TO_CHAR(birth_date,'DD.MM.YYYY') birth_date,
3 last_name,
4 archive_state
5 FROM dev3.customer_list_range
6 WHERE archive_state = 'Y'
7 AND (birth_date >= TO_DATE('01.01.1990') AND birth_date < TO_DATE('01.01.1995'))
8 AND SUBSTR(UPPER(last_name),1,1) ='B'
9 FETCH FIRST 1 ROWS ONLY;
CUST_ID BIRTH_DATE LAST_NAME
---------- ---------- -----------------------------------------------------------------
25 10.02.1992 BPLVIEPEMZQGKHK
1 row selected.
This row should be in the sub-partition DEV3.P_CUSTOMER_ARCHIVED_1990_B. Let’s check it:
DEV3@pdb1> SELECT cust_id,
2 TO_CHAR(birth_date,'DD.MM.YYYY') birth_date,
3 last_name,
4 archive_state
5 FROM dev3.p_customer_archived_1990_b
6 WHERE cust_id = 25;
CUST_ID BIRTH_DATE LAST_NAME
---------- ---------- ----------------------------------
25 10.02.1992 BPLVIEPEMZQGKHK
1 row selected.
We will update this row in order to have a last_name beginning with ‘A’:
DEV3@pdb1> UPDATE dev3.customer_list_range
2 SET last_name = 'A' || last_name
3 WHERE cust_id = 25;
1 row updated.
DEV3@pdb1> COMMIT;
Commit complete.
This row should be in the sub-partition DEV3.P_CUSTOMER_ARCHIVED_1990_AE. Let’s check it:
DEV3@pdb1> SELECT cust_id,
2 TO_CHAR(birth_date,'DD.MM.YYYY') birth_date,
3 last_name,
4 archive_state
5 FROM dev3.p_customer_archived_1990_ae
6 WHERE cust_id = 25;
CUST_ID BIRTH_DATE LAST_NAME
---------- ---------- ----------------------------------
25 10.02.1992 ABPLVIEPEMZQGKHK
1 row selected.
We will update this row in order to become ‘active’:
DEV3@pdb1> UPDATE dev3.customer_list_range
2 SET archive_state = 'N'
3 WHERE cust_id = 25;
1 row updated.
DEV3@pdb1> COMMIT;
Commit complete.
This row should be in the sub-partition DEV3.P_CUSTOMER_ACTIVE_1990. Let’s check it:
DEV3@pdb1> SELECT cust_id,
2 TO_CHAR(birth_date,'DD.MM.YYYY') birth_date,
3 last_name,
4 archive_state
5 FROM dev3.p_customer_active_1990
6 WHERE cust_id = 25;
CUST_ID BIRTH_DATE LAST_NAME
---------- ---------- ----------------------------------
25 10.02.1992 ABPLVIEPEMZQGKHK
1 row selected.
Oracle database experiences 