BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

EnterpriseDB: Partitioned Tables

Oracle Tips by Burleson Consulting
 

By combining several types of DDL that we have already covered above, it is possible to create a partitioned table.  A partitioned table is a table that is split into multiple files in the file system.  The benefit of a partitioned table is that you can get better performance for queries that will only use one or a few partitions.  Partitioning large tables also eases some maintenance activities (such as backups, archiving and purging).  You could also choose to store more frequently used data on faster more expensive disks and older, less frequently accessed data on slower, but cheaper, disks.

Partitioned tables in EnterpriseDB are conceptually the same as a partitioned table in Oracle.  The actual implementation varies quite a bit though.

In EnterpriseDB, you may have a RANGE partitioned table or a LIST partitioned table.  A RANGE is exactly what it sounds like, 1-10, 50-100, Jan-01-2005 - Jan-01-2006, etc.  A LIST partition is a list of values: 'A%', 'B%', 'TOM', 'JOHN', 'NEW YORK', 'TAMPA', etc.  In reality, a LIST is a limited sort of a RANGE.  The implementation of a range partition and a list partition are identical and, in EnterpriseDB, there is no real functional difference between them.

You will primarily use a range partition with data ranges.  In a data warehouse, you may want to keep two years worth of data and partition by month.  This would allow you to easily perform maintenance (update, insert, select) against the current month and would allow you to easily drop older partitions when you no longer need them.

A list partition is used for data that may contain cities, names of things or other items where the names are known in advance.

Oracle also offers HASH partitions and mixed partitions.  EnterpriseDB does not offer these partitioning methods currently.  It is possible to create a homegrown hash partitioned table, which I will provide an example of later.

Creating a partitioned table requires several steps.  You must first create the base table, then you create each partition and its indexes and finally, the partitioning trigger.

CREATE PARTITIONED TABLE

First, we will create the base table.  This will be a range-partitioned table with a date range as the partition key.  The primary key of our table will be a numeric key.  I am doing this mainly to demonstrate that the partition key does not need to be the same as the primary key.

CREATE TABLE base_table (
  pk_field   NUMBER NOT NULL PRIMARY KEY,
  dt_field   DATE,
  char_field VARCHAR2(100) ); 

edb=# desc base_table

                Table "public.base_table"

   Column   |              Type              | Modifiers
------------+--------------------------------+-----------
 pk_field   | numeric                        | not null
 dt_field   | timestamp(0) without time zone |
 char_field | character varying(100)         |

Indexes:

    "base_table_pkey" PRIMARY KEY, btree (pk_field)

We will create three partitions.  We want each partition to store a month's worth of data.  The first partition will store Jan 2006, the second will store Feb 2006 and the third will store Mar 2006.

CREATE TABLE base_table_part_200601
  ( CHECK (TO_NUMBER(TO_CHAR(dt_field,'YYYYMMDD'))
            BETWEEN 20060101 AND 20060131 ) )
  INHERITS (base_table);

CREATE TABLE base_table_part_200602
  ( CHECK (TO_NUMBER(TO_CHAR(dt_field,'YYYYMMDD'))
            BETWEEN 20060201 AND 20060228 ) )
  INHERITS (base_table);

CREATE TABLE base_table_part_200603
  ( CHECK (TO_NUMBER(TO_CHAR(dt_field,'YYYYMMDD'))
            BETWEEN 20060301 AND 20060331 ) )
  INHERITS (base_table);

edb=# desc base_table_part_200601

          Table "public.base_table_part_200601"

   Column   |              Type              | Modifiers
------------+--------------------------------+-----------
 pk_field   | numeric                        | not null
 dt_field   | timestamp(0) without time zone |
 char_field | character varying(100)         |

Check constraints:

    "base_table_part_200601_dt_field_check" CHECK (
       to_number(to_char(dt_field, 'YYYYMMDD'::character varying)::text)
                     >= 20060101::numeric AND
              to_number(to_char(dt_field, 'YYYYMMDD'::character varying)::text)
                     <= 20060131::numeric)

Inherits: base_table

edb=# desc base_table_part_200602

          Table "public.base_table_part_200602"

   Column   |              Type              | Modifiers
------------+--------------------------------+-----------
 pk_field   | numeric                        | not null
 dt_field   | timestamp(0) without time zone |
 char_field | character varying(100)         |

Check constraints:

    "base_table_part_200602_dt_field_check" CHECK (
       to_number(to_char(dt_field, 'YYYYMMDD'::character varying)::text)
             >= 20060201::numeric AND
       to_number(to_char(dt_field,'YYYYMMDD'::character varying)::text)
             <= 20060228::numeric)

Inherits: base_table

edb=# desc base_table_part_200603

          Table "public.base_table_part_200603"

   Column   |              Type              | Modifiers
------------+--------------------------------+-----------
 pk_field   | numeric                        | not null
 dt_field   | timestamp(0) without time zone |
 char_field | character varying(100)         |
 

Check constraints:

    "base_table_part_200603_dt_field_check" CHECK (
       to_number(to_char(dt_field, 'YYYYMMDD'::character varying)::text)
             >= 20060301::numeric AND          
       to_number(to_char(dt_field,'YYYYMMDD'::character varying)::text)
             <= 20060331::numeric)

Inherits: base_table

Next, we need to create an index on the partition key in each partition of the base table.  As we will not be storing data in base_table itself, there is no need to index it.

CREATE INDEX partition1_part_key ON base_table_part_200601 (dt_field);
CREATE INDEX partition2_part_key ON base_table_part_200602 (dt_field);
CREATE INDEX partition3_part_key ON base_table_part_200603 (dt_field);

As it stands, any inserts would insert into the base table and not in our partitions.  At this point, we can either create a set of rules or a trigger.  I would like to keep this as Oracle compatible as I can so I will use a trigger instead of a rule.  I also find triggers to be more intuitive and less maintenance.

In this trigger, I am only going to handle inserts.  You would also want to handle updates and deletes.  The gist of this trigger is to take what would normally be inserted into the base table and instead insert it into the correct partition.

CREATE OR REPLACE TRIGGER base_table_handler
  BEFORE INSERT OR UPDATE OR DELETE
  ON base_table
  FOR EACH ROW
BEGIN 

  IF INSERTING THEN
    IF :new.dt_field BETWEEN to_date('20060101', 'YYYYMMDD' )
                         AND to_date('20060131', 'YYYYMMDD' )
    THEN
      INSERT INTO base_table_part_200601
         (pk_field, dt_field, char_field)
        VALUES (:new.pk_field, :new.dt_field, :new.char_field);
    ELSIF :new.dt_field BETWEEN to_date('20060201', 'YYYYMMDD' )
                            AND to_date('20060228', 'YYYYMMDD' )
    THEN
      INSERT INTO base_table_part_200602
         (pk_field, dt_field, char_field)
        VALUES (:new.pk_field, :new.dt_field, :new.char_field);
    ELSIF :new.dt_field BETWEEN to_date('20060301', 'YYYYMMDD' )
                            AND to_date('20060331', 'YYYYMMDD' )
    THEN
      INSERT INTO base_table_part_200603
         (pk_field, dt_field, char_field)
        VALUES (:new.pk_field, :new.dt_field, :new.char_field);
    END IF;
  ELSIF UPDATING THEN
    -- Do the same for update
    NULL;
  ELSIF DELETING THEN
     -- Do the same for delete
    NULL;
  END IF; 

  RETURN NULL;
END;
/

You should notice the RETURN NULL command on the second to last line.  This return null tells the database engine to stop processing this transaction.  Without it, we would end up with a record in the base table and in the partition.

If we had to re-write the trigger (or rules) each time we added new partitions, this could be a high-maintenance process.  In the next chapter, I will show how to partially automate this process by using dynamic SQL.

Now we want to insert a few rows.

-- Insert a row into the first partition

INSERT INTO base_table
  (pk_field, dt_field, char_field)
  VALUES (1, to_date('20060110', 'YYYYMMDD'), 'Record 1');

-- Insert a row into the second partition

INSERT INTO base_table
  (pk_field, dt_field, char_field)
  VALUES (1, to_date('20060210', 'YYYYMMDD'), 'Record 2');

-- Insert a row into the third partition

INSERT INTO base_table
  (pk_field, dt_field, char_field)
  VALUES (1, to_date('20060310', 'YYYYMMDD'), 'Record 3');

-- Save our records

COMMIT;

The results of the insert statements:

edb=# -- Insert a row into the first partition
edb=# INSERT INTO base_table
edb-#   (pk_field, dt_field, char_field)
edb-#   VALUES (1, to_date('20060110', 'YYYYMMDD'), 'Record 1');
INSERT 0 0
edb=#
edb=# -- Insert a row into the second partition
edb=# INSERT INTO base_table
edb-#   (pk_field, dt_field, char_field)
edb-#   VALUES (1, to_date('20060210', 'YYYYMMDD'), 'Record 2');
INSERT 0 0
edb=#
edb=# -- Insert a row into the third partition
edb=# INSERT INTO base_table
edb-#   (pk_field, dt_field, char_field)
edb-#   VALUES (1, to_date('20060310', 'YYYYMMDD'), 'Record 3');
INSERT 0 0
edb=#
edb=# -- Save our records
edb=# COMMIT;
COMMIT
edb=#

The thing to notice here is that the primary key constraint is not enforced across dimensions as it is in Oracle.  You would need to write additional rules or triggers to enforce that uniqueness.  It is enforced for each partition though.

Select the data back out:

-- Select from the base table

SELECT * FROM base_table;

-- Select from the partition table 200601

SELECT * FROM base_table_part_200601;

-- Select from the partition table 200602

SELECT * FROM base_table_part_200602;

-- Select from the partition table 200603

SELECT * FROM base_table_part_200603;

The results from these select statements:

edb=# -- Select from the base table
edb=# SELECT * FROM base_table;

 pk_field |      dt_field      | char_field
----------+--------------------+------------
        1 | 10-JAN-06 00:00:00 | Record 1
        1 | 10-FEB-06 00:00:00 | Record 2
        1 | 10-MAR-06 00:00:00 | Record 3

(3 rows)

edb=#
edb=# -- Select from the partition table 200601
edb=# SELECT * FROM base_table_part_200601;

 pk_field |      dt_field      | char_field
----------+--------------------+------------
        1 | 10-JAN-06 00:00:00 | Record 1

(1 row)

edb=#
edb=# -- Select from the partition table 200602
edb=# SELECT * FROM base_table_part_200602;

 pk_field |      dt_field      | char_field
----------+--------------------+------------
        1 | 10-FEB-06 00:00:00 | Record 2

(1 row)

edb=#
edb=# -- Select from the partition table 200603
edb=# SELECT * FROM base_table_part_200603;

 pk_field |      dt_field      | char_field
----------+--------------------+------------
        1 | 10-MAR-06 00:00:00 | Record 3

(1 row)

edb=#




This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter