Oracle database experiences

Oracle database blog

Custom Partitioning – Part I – List partitioning – Instead Of Trigger

Scope

In this series of post, we will focus on partitioning tables without having the “Partitioning” option which is an option of Oracle Database Enterprise Edition.
We will start in a first step to partition tables.

Partitioning concepts

Basically a partitioned table is a set of several sub-tables called partitions. The partitioning is based on one or more fields (called partitioning key) and on a distribution method:

The main distribution methods are:

  • List partitioning,
  • Range partitioning,
  • Hash partitioning.

You can have a single level partitioning e.g. only one distribution method on a table or a composite partitioning where we apply a first distribution method on a table, and a second distribution method on the partitions (which give us sub-partitions).

CUSTOMER table example

In the rest of this post we will use a 12cR1 PDB database.

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

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_MODEL
  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.

and a sequence:

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

Sequence created.

Let’s now fill this table:

DEV3@pdb1> INSERT INTO DEV3.CUSTOMER_MODEL
  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.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.

At this point we would like to partition this table based on the first character of the last_name column.
In this case the partitioning key is similar to a virtual column because this expression is not a base column of the table, and the distribution method is similar to a List partitioning method.

Remark: In a List partitioning method there is a default partition that we will use.

Custom partitioning principles

We will set up 3 partitions:

  • One for a last_name column begining with an A or an E,
  • One for a last_name column begining with an B,
  • One for the other cases.

Let’s create the partitions:

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_AE
  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_B
  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_DEFAULT
  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.CUSTOMER
  2  AS
  3   SELECT * FROM DEV3.CUSTOMER_AE
  4  UNION ALL
  5   SELECT * FROM DEV3.CUSTOMER_B
  6  UNION ALL
  7   SELECT * FROM DEV3.CUSTOMER_DEFAULT;

View created.

At this point, we can only select rows of the partitioned table (CUSTOMER) but we can not insert, update and delete rows directly through this view.
Nevertheless, we could manually copy the rows of the table CUSTOMER_MODEL whose first character of the last_name is ‘B’ in the following way:

INSERT INTO DEV3.CUSTOMER_B
  (
   cust_id,    
   first_name,  
   last_name, 
   birth_date,
   creation_date,      
   modification_date
  )       
SELECT *
FROM DEV3.CUSTOMER_MODEL
WHERE SUBSTR(UPPER(last_name),1,1) = 'B';

and we could manually also delete and update a row by finding first in which partition it is.

We could have a more transparent process using the Instead of trigger.

Instead of Trigger – Insert

This kind of trigger is used to update for example a non updatable view which is our case.

We will start with the insert operation:

CREATE OR REPLACE TRIGGER DEV3.TI_CUSTOMER 
INSTEAD OF INSERT ON DEV3.CUSTOMER
-- =======================================================================
--  Trigger       : TI_CUSTOMER
--  Description   : Insert trigger for custom partitioning (list)
--  Author        : Arnaud Fargues
--  Creation      : 20.09.2016
--  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.CUSTOMER_AE
           (
            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
           );
      -- B --      
      WHEN v_partition_list IN ('B') THEN    
         INSERT INTO DEV3.CUSTOMER_B
           (
            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
           );
      -- Default --      
      ELSE
         INSERT INTO DEV3.CUSTOMER_DEFAULT
           (
            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;

Let’s execute the following query on the source table:

DEV3@pdb1> SELECT SUBSTR(UPPER(last_name),1,1) fl,
  2         COUNT(*)
  3  FROM DEV3.CUSTOMER_MODEL
  4  GROUP BY CUBE(SUBSTR(UPPER(last_name),1,1))
  5  ORDER BY 1;

FL     COUNT(*)
---- ----------
A           374
B           386
C           419
D           413
E           403
F           360
G           425
H           363
I           415
J           359
K           399
L           359
M           394
N           373
O           362
P           359
Q           353
R           390
S           438
T           360
U           373
V           416
W           375
X           369
Y           363
Z           400
          10000

27 rows selected.

We have:

  • 777 rows whose last_name is begining with A or E,
  • 386 rows whose last_name is begining with B.

Now we will partition the source table as follow:

DEV3@pdb1> INSERT INTO DEV3.CUSTOMER
  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_AE' part, COUNT(*)
  2   FROM DEV3.CUSTOMER_AE
  3  UNION ALL
  4   SELECT 'CUSTOMER_B' part, COUNT(*)
  5   FROM DEV3.CUSTOMER_B
  6  UNION ALL
  7   SELECT 'CUSTOMER_DEFAULT' part, COUNT(*)
  8   FROM DEV3.CUSTOMER_DEFAULT;

PART               COUNT(*)
---------------- ----------
CUSTOMER_AE             777
CUSTOMER_B              386
CUSTOMER_DEFAULT       8837

3 rows selected.

Remarks:

  • If you have already a trigger on the table to be partitioned, you might integrate this behaviour in the Instead of trigger,
  • We could have create a virtual column on the source table equal to “SUBSTR(UPPER(last_name),1,1)” and use it in the Instead of trigger.

Shortly:

We will see how to delete and update rows in the partitioned table.

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: