<

Zero-cost partitioning (an option for the penniless)

Published on
12,538 Points
2,938 Views
1 Endorsement
Last Modified:
Awarded
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)

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’)

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;
 

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 );

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;

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 );

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;
/

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;

Open in new window

Pro’s

Useful if you need to deploy Standard Edition databases that could benefit from table partitioning
You save some money on licensing the partitioning option
Partitioning is unlimited and can be based on almost any expression (unavailable in regular partitioning until 11g)
Can be implemented in any Oracle edition and/or version, and perhaps even on on-oracle databases.
Works best (less or no maintenance) with circular partitioning where there is a fixed number of partitions.
Not perfect, but it gets the job done

Con’s

May need lots of manual intervention
And then some more…
ZeroCostPartDDL.zip
1
Comment
Author:MikeOM_DBA
  • 3
4 Comments
LVL 29

Author Comment

by:MikeOM_DBA
Thanks guys!
I already have prepared full working example, I will correct article and post the code.
0
LVL 29

Author Comment

by:MikeOM_DBA
Perhaps because I chose a bad table as example, but sales_part_uniq_keys  serves the equivalent of "Global indexes". It is necessary because even if each table has primary key, it could become duplicate on another of the tables. The same for unique keys across all tables.

The script is ready and it works, to test:

1) Grant "scott" create view and perhaps "resource".
2) Gran select on sh.sales to scott
3) Connect as "scott" and execute the script.

Regards,
MikeOM>
0
LVL 29

Author Comment

by:MikeOM_DBA
Thanks!
If you are going for a trans_id as the PK, why are cust_id, prod_id and channel_id part of the uniq_keys table?
Because they are a composite unique key (for this example):
CREATE UNIQUE INDEX sales_part_uk1
  ON sales_part_uniq_keys( time_id,
                           cust_id,
                           prod_id,
                           channel_id );

Open in new window

The idea being that any unique key be added to the uniq_keys table.
0
LVL 13

Expert Comment

by:magarity
I thought about doing something just like this except that the loading process would have to be customized per underlying table. That was the deciding factor.  Instead, I've gone with an Index Organized Table and designed the index around what I would have partitioned on.  It's working nicely; about 25% faster load and read than a heap table with regular index and about 25% slower than a version with proper partitions. The downside is an IOT needs to be rebuilt every once in a while when the b-tree gets unbalanced but I have a wide maintenance window to do it.
0

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month