Postgres Plus Advanced Server Oracle Compatibility Developer's Guide :

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

12 Table Partitioning

In a partitioned table, one logically large table is broken into smaller physical pieces. Partitioning can provide several benefits:

    ● Query performance can be improved dramatically in certain situations, particularly when most of the heavily accessed rows of the table are in a single partition or a small number of partitions. Partitioning allows you to omit the partition column from the front of an index, reducing index size and making it more likely that the heavily used parts of the index fits in memory.

    ● When a query or update accesses a large percentage of a single partition, performance may improve because the server will perform a sequential scan of the partition instead of using an index and random access reads scattered across the whole table.

    ● A bulk load (or unload) can be implemented by adding or removing partitions, if you plan that requirement into the partitioning design. ALTER TABLE is far faster than a bulk operation. It also entirely avoids the VACUUM overhead caused by a bulk DELETE.

    ● Seldom-used data can be migrated to less-expensive (or slower) storage media.

Table partitioning is worthwhile only when a table would otherwise be very large. The exact point at which a table will benefit from partitioning depends on the application; a good rule of thumb is that the size of the table should exceed the physical memory of the database server.

LIST partitioning

In a LIST partitioned table, each partition stores data that is described by a partitioning rule. For example:

      PARTITION BY LIST(country)
      (
      PARTITION europe VALUES( 'ITALY', 'FRANCE'),
      PARTITION asia VALUES('INDIA', 'PAKISTAN')

      );

When you INSERT a new row into the table, Advanced Server decides which partition will hold the row based on the value of the country column. If country is ITALY or FRANCE, Advanced Server stores the row in the partition named europe. If country is INDIA or PAKISTAN, Advanced Server stores the row in the partition named asia. If country contains any other value, Advanced Server reports an error (you can specify a DEFAULT partition to catch other values).

RANGE partitioning

In a RANGE partitioned table, each partition specifies a range of values. For example:

PARTITION BY RANGE(order_date)
(
PARTITION q1_2011 VALUES LESS THAN('2011-Apr-01'),
PARTITION q2_2011 VALUES LESS THAN('2011-Jul-01'),
PARTITION q3_2011 VALUES LESS THAN('2011-Oct-01'),
PARTITION q4_2011 VALUES LESS THAN('2012-Jan-01'),
PARTITION others VALUES LESS THAN(MAXVALUE)
);

When you INSERT a row into this table, Advanced Server chooses which partition will hold the row based on the value of the order_date column. Advanced Server compares the value of order_date to 2011-Apr-01; if it is less than 2011-Apr-01, it stores the row in the q1_2011 partition. If not, it continues to the next rule, and so on until it finds a partition that describes the value of order_date.

The others partition catches any orders with an order_date that does not fit the rules that define the other partition. The MAXVALUE keyword matches any value that does not fit into one of the specified ranges.

Please note: range values are non-inclusive (only those values less than the value specified will satisfy the rule), and must be specified in ascending order.

Subpartitioning

You can define a subpartition to further divide the data stored in a table partition into smaller (more manageable) subsets. For example:

PARTITION BY RANGE(order_date) SUBPARTITION BY LIST(country)
(
PARTITION q1_2011 VALUES LESS THAN('2011-Apr-01')
(
SUBPARTITION q1_2011_europe VALUES('ITALY', 'GERMANY'),
SUBPARTITION q1_2011_asia VALUES('INDIA', 'PAKISTAN')
),
PARTITION q2_2011 VALUE LESS THAN '2011-Jul-01')
(
SUBPARTITION q2_2011_europe VALUES('ITALY', 'GERMANY'),
SUBPARTITION q2_2011_asia VALUES('INDIA', 'PAKISTAN')
),
...
)

When you INSERT a row into this table, Advanced Server chooses which subpartition will hold the row first based on the value of the order_date column, and then based on the location specified in the country column. An entry with an order_date of 2011-May-01, with a country value of INDIA, will be stored in the q2_2011_asia subpartition.

You can divide a RANGE partitioned table by LIST subpartitions, or a LIST partitioned table by RANGE subpartitions.

You cannot add partitions or subpartitions to an existing table that does not have a partition (or subpartitions); to create a partitioned table, you must use the CREATE TABLE…PARTITION BY command..

Previous PageTable Of ContentsNext Page