Jonathan Lalou's blog

Posts Tagged ‘10g’

On last Friday I attented a presentation about Oracle (10g) Partitioning. Here is a summary of interesting points:

Why?

On some projects, data bases reach tens of TB.  For so huge volume of data, developpers often are used to archiving or historizing datas. Partitioning the base is another way to face mass data.

What?

Partitioning is allowing a table or an index to be divided into smaller pieces, called “partitions”. Please notice that, from a logical point of view, we keep one object (table or index) ; yet, physically, we have many physical partitions.

When?

• DBAs advise to consider partitioning prior to create the tables. Indeed, partitioning when the table reaches a critical size is more expensive than sooner.
• Tables may be partionioned if they are or are assumed to be 2GB or more.

Partition key/ pruning

• From a certain viewpoint, a partition key may be seen as a “super index”
• Rows of the base are assigned to a unique partition.
• The partitioning key contains one column (or more) which determine the partition where any row is to be stored.
• During insert/update/delete operations, the right partition is automatically found owing to the partition key.
• Warning: any table with columns of type LONG or LONG_RAW cannot be partitioned.

Purpose

• Improvement of availability and performance of the base. At first glance, on select operations the benefit is small, but on insert operations the benefit is felt.
• Reduction of administrative burden and input/ouput access
• Anticipation of high volumes of data and cost of development

TYPES

Three types of partitions are available:

• range: eg by month, year, etc.
• list: eg by country, currency, etc. This is the case when the column values are among an enumeration
• hash

Composite keys are allowed, yet they are not advised to be used.

INDEX

Indexes can be partitioned, too. All combinations are allowed: partioned indexes for partioned tables, partioned indexes for global tables, etc.

Syntax

CREATE TABLE range_example
(
RANGE_KEY_COLUMN   DATE,
MY_DATA            VARCHAR2 (20)
)
PARTITION BY RANGE (range_key_column)
(
PARTITION part_1 VALUES LESS THAN (TO_DATE ('01/01/2005', 'dd/mm/yyyy')),
PARTITION part_2 VALUES LESS THAN (TO_DATE ('01/01/2006', 'dd/mm/yyyy')))

Oracle 11g

Oracle 11g will bring a lot of improvements on partitioning: extended composite partitioning, reference partitioning, partitioning on virtual columns, etc.