Zero-cost partitioning (an option for the penniless)

Background

First I would like to add a disclaimer: Around the year 2002 I was faced with the issue I will be describing later on and as solution I came up with this cool partitioning idea. I thought at the time I was a genius, but unfortunately some six months later (while deploying an Oracle 8i database for one of the largest financial institutions) I received from the Database Architects the DDL to create the schema and lo and behold! they were using my partitioning methodology; I was at the same time devastated and humbled to know it was not my invention after all and perhaps it was common practice.

Many years have passed and I had almost forgotten this partitioning methodology until I was faced recently with the same situation.

Therefore I believe it’s the right time to share this knowledge with those of the the EE community at large which may not know of it’s existence.

Before I describe this partitioning methodology, perhaps the best way to introduce it is to relate the particular situation by posting the conversation between a manager and a dba which gave it birth:

MGR:      Hey Mike, the users are complaining the queries on table X are too slow, see if you can create some indexes or something to speed them up.

DBA:      Table X has as many indexes as possible. The main issue here is the size of the table, it already has more than gazillion rows and keeps growing.
My recommendation is to partition this table.

MGR:      Ok, do it. How much downtime do we need? How will it impact the application? Will it cost us anything?

DBA:      Yes it will cost us, we will need to purchase the Oracle license for the Partitioning option.

MGR:      How much is it? We don’t have any funds in our budget! … besides “upper” management will not authorize any additional expenditures.
You need to come up with a solution that will not cost us any money.

… And so: on, and on, and on …

What can we do?

 Solution: zero-cost partitioning

How?

The methodology is simple:

•      Select an expression on which the table will be partitioned
•      Create one table for each expected partition
•      Create a view to group all tables that form the “partitions”
•      Create “instead of” triggers on the view to consolidate DML operations

As the saying goes “A picture is worth a thousand words” so, for illustration purposes I will give an example of creating a zero-cost partitioned table based on some mythical "SALES" table, for brevity, I will post "snippets" of the code but the full script is attached:

SQL> desc sales
                       Name              Null?    Type
                       ----------------- -------- ------------
                       PROD_ID           NOT NULL NUMBER
                       CUST_ID           NOT NULL NUMBER
                       TIME_ID           NOT NULL DATE
                       CHANNEL_ID        NOT NULL NUMBER
                       PROMO_ID          NOT NULL NUMBER
                       QUANTITY_SOLD     NOT NULL NUMBER(10,2)
                       AMOUNT_SOLD       NOT NULL NUMBER(10,2)

Select all Open in new window

1)      First we select a partitioning expression.
In this example I chose to divide by “year/quarter” and will be using the
TIME_ID column for the partitioning by applying this expression:

TO_CHAR(time_id,’YYYY_Q’)

Select all Open in new window

2) Next we create as many tables as partitions we need:

-- -----------------------------------------------------------
                      -- Create first partition:
                      --
                      CREATE TABLE sales_part_2010_1
                      (
                        trans_id       NUMBER NOT NULL,
                        prod_id        NUMBER NOT NULL,
                        cust_id        NUMBER NOT NULL,
                        time_id        DATE NOT NULL,
                        channel_id     NUMBER,
                        promo_id       NUMBER,
                        quantity_sold  NUMBER( 10, 2 ) NOT NULL,
                        amount_sold    NUMBER( 10, 2 ) NOT NULL
                      );
                      --
                      -- Create the other partitions:
                      --
                      CREATE TABLE sales_part_2010_2 AS
                        SELECT * FROM sales_part_2010_1 WHERE 1 = 0;
                      CREATE TABLE sales_part_2010_3 AS
                        SELECT * FROM sales_part_2010_1 WHERE 1 = 0;
                      -- Etc ...
                      CREATE TABLE sales_part_2012_4 AS
                        SELECT * FROM sales_part_2010_1 WHERE 1 = 0;
                       

Select all Open in new window

3) Perhaps add the primary key and some indexes to each of the tables:

-- -----------------------------------------------------------
                      -- Create local primary keys:
                      -- -----------------------------------------------------------
                      CREATE UNIQUE INDEX sales_part_2010_1_pk
                        ON sales_part_2010_1( trans_id );
                      ALTER TABLE sales_part_2010_1 ADD (
                       CONSTRAINT sales_part_2010_1_pk PRIMARY KEY  (trans_id) USING INDEX sales_part_2010_1_pk ENABLE VALIDATE);
                      -- Etc ...
                      CREATE UNIQUE INDEX sales_part_2012_4_pk
                        ON sales_part_2012_4( trans_id );
                      ALTER TABLE sales_part_2012_4 ADD (
                       CONSTRAINT sales_part_2012_4_pk PRIMARY KEY  (trans_id) USING INDEX sales_part_2012_4_pk ENABLE VALIDATE);
                      -- -----------------------------------------------------------
                      -- Create some other indexes:
                      --
                      CREATE INDEX sales_part_2010_1_idx1
                        ON sales_part_2010_1( time_id, prod_id, cust_id );
                      CREATE INDEX sales_part_2010_2_idx1
                        ON sales_part_2010_2( time_id, prod_id, cust_id );
                      -- Etc ...
                      CREATE INDEX sales_part_2012_4_idx1
                        ON sales_part_2012_4( time_id, prod_id, cust_id );

Select all Open in new window

4) And now, the trick is to create a view to group these tables by using the union set operator:

CREATE OR REPLACE VIEW sales_part AS
                        SELECT * FROM sales_part_2010_1  UNION ALL
                        SELECT * FROM sales_part_2010_2  UNION ALL
                        SELECT * FROM sales_part_2010_3  UNION ALL
                        SELECT * FROM sales_part_2010_4  UNION ALL
                        SELECT * FROM sales_part_2011_1  UNION ALL
                        SELECT * FROM sales_part_2011_2  UNION ALL
                        SELECT * FROM sales_part_2011_3  UNION ALL
                        SELECT * FROM sales_part_2011_4  UNION ALL
                        SELECT * FROM sales_part_2012_1  UNION ALL
                        SELECT * FROM sales_part_2012_2  UNION ALL
                        SELECT * FROM sales_part_2012_3  UNION ALL
                        SELECT * FROM sales_part_2012_4;

Select all Open in new window

5)  Create an index organized table to preserve/manage unique keys:

CREATE TABLE sales_part_uniq_keys
                      (
                        trans_id       NUMBER PRIMARY KEY NOT NULL,
                        time_id        DATE   NOT NULL,
                        cust_id        NUMBER NOT NULL,
                        prod_id        NUMBER NOT NULL,
                        channel_id     NUMBER NOT NULL
                      )
                      ORGANIZATION INDEX INCLUDING channel_id 
                      PCTTHRESHOLD 2 STORAGE( INITIAL 4 K )
                      OVERFLOW STORAGE( INITIAL 4 K );
                      --
                      -- Create the unique key(s):
                      --
                      CREATE UNIQUE INDEX sales_part_uk1
                        ON sales_part_uniq_keys( time_id,
                                                 cust_id,
                                                 prod_id,
                                                 channel_id );

Select all Open in new window

6) In order to apply dml operations to these tables, we will be creating ‘INSTEAD OF’ triggers on the view:

-- -----------------------------------------------------------
                      -- Insert trigger
                      --
                      CREATE OR REPLACE TRIGGER sales_part_insert
                        INSTEAD OF INSERT
                        ON sales_part
                        FOR EACH ROW
                      DECLARE
                        v_sql          VARCHAR2( 4096 );
                      BEGIN
                        --
                        -- Section to manage unique keys:
                        --
                        INSERT INTO sales_part_uniq_keys
                        VALUES ( :new.trans_id,
                                 :new.time_id,
                                 :new.cust_id,
                                 :new.prod_id,
                                 :new.channel_id );
                        --
                        -- Section to manage insert:
                        --
                        v_sql      :=
                             'INSERT INTO sales_part_'|| TO_CHAR( :new.time_id, 'YYYY_Q' )
                          || '  VALUES(:1,:2,:3,:4,:5,:6,:7,:8) ';
                      
                        EXECUTE IMMEDIATE v_sql
                          USING :new.trans_id,
                                :new.prod_id,
                                :new.cust_id,
                                :new.time_id,
                                :new.channel_id,
                                :new.promo_id,
                                :new.quantity_sold,
                                :new.amount_sold;
                      END;
                      /
                      -- -----------------------------------------------------------
                      -- Delete trigger
                      --
                      CREATE OR REPLACE TRIGGER sales_part_delete
                        INSTEAD OF DELETE
                        ON sales_part
                        FOR EACH ROW
                      DECLARE
                        v_sql          VARCHAR2( 4096 );
                      BEGIN
                        --
                        -- Section to manage delete:
                        --
                        v_sql      :=
                             'DELETE FROM sales_part_'|| TO_CHAR( :old.time_id, 'YYYY_Q' )
                          || '  WHERE TRANS_ID = :1';
                      
                        EXECUTE IMMEDIATE v_sql USING :old.trans_id;
                        --
                        -- Section to manage unique keys:
                        --
                        DELETE FROM  sales_part_uniq_keys
                          WHERE TRANS_ID = :old.trans_id;
                      
                      END;
                      /
                      -- -----------------------------------------------------------
                      -- Update trigger
                      --
                      CREATE OR REPLACE TRIGGER sales_part_update
                        INSTEAD OF UPDATE
                        ON sales_part
                        FOR EACH ROW
                      DECLARE 
                        v_sql          VARCHAR2( 4096 );
                      BEGIN
                        --
                        -- Section to manage unique keys:
                        --
                        IF :old.trans_id   <> :new.trans_id
                        OR :old.time_id    <> :new.time_id
                        OR :old.cust_id    <> :new.cust_id
                        OR :old.prod_id    <> :new.prod_id
                        OR :old.channel_id <> :new.channel_id
                        THEN
                      
                          DELETE FROM  sales_part_uniq_keys
                            WHERE TRANS_ID = :old.trans_id;
                      
                          INSERT INTO sales_part_uniq_keys
                          VALUES ( :new.trans_id,
                                   :new.time_id,
                                   :new.cust_id,
                                   :new.prod_id,
                                   :new.channel_id );
                        END IF;
                        --
                        -- Section to manage update:
                        --
                        IF :old.trans_id <> :new.trans_id
                        OR :old.time_id  <> :new.time_id
                        THEN
                          v_sql      :=
                               'DELETE FROM sales_part_'|| TO_CHAR( :old.time_id, 'YYYY_Q' )
                            || '  WHERE TRANS_ID = :1';
                      
                          EXECUTE IMMEDIATE v_sql USING :old.trans_id;
                          
                          v_sql      :=
                               'INSERT INTO sales_part_'|| TO_CHAR( :new.time_id, 'YYYY_Q' )
                            || '  VALUES(:1,:2,:3,:4,:5,:6,:7,:8) ';
                      
                          EXECUTE IMMEDIATE v_sql
                            USING :new.trans_id,
                                  :new.prod_id,
                                  :new.cust_id,
                                  :new.time_id,
                                  :new.channel_id,
                                  :new.promo_id,
                                  :new.quantity_sold,
                                  :new.amount_sold;
                        ELSE
                          v_sql      :=
                               'UPDATE sales_part_'|| TO_CHAR( :new.time_id, 'YYYY_Q' )
                            || '   SET prod_id        = :1'
                            || '     , cust_id        = :2'
                            || '     , time_id        = :3'
                            || '     , channel_id     = :4'
                            || '     , promo_id       = :5'
                            || '     , quantity_sold  = :6'
                            || '     , amount_sold    = :7'
                            || ' WHERE trans_id = '||:old.trans_id;
                      
                          EXECUTE IMMEDIATE v_sql
                            USING :new.prod_id,
                                  :new.cust_id,
                                  :new.time_id,
                                  :new.channel_id,
                                  :new.promo_id,
                                  :new.quantity_sold,
                                  :new.amount_sold;
                      
                        END IF;
                      END;
                      /

Select all Open in new window

7) And finally populate our partitions from the SH.SALES table:

INSERT INTO sales_part
                        SELECT ROW_NUMBER() OVER (ORDER BY time_id, cust_id),
                               prod_id,
                               cust_id,
                               ADD_MONTHS( time_id, 125 ) time_id,
                               channel_id,
                               promo_id,
                               quantity_sold,
                               amount_sold
                        FROM sh.sales
                        WHERE ADD_MONTHS( time_id, 125 ) >= TO_DATE( '20100101', 'yyyymmdd' );
                      
                      COMMIT;

Select all Open in new window

Pro’s


Con’s

ZeroCostPartDDL.zip