Range Partitions

Partitioning is a scheme of dividing large objects into subobjects.

For example:

Storage space is partitioned into tablespaces
Tables can be partitioned into table partitions

Table objects can be table partitions, columns, indexes, IQ base tables, join indexes, unique constraints, primary keys or foreign keys.

A table partition is a collection of rows that is a subset of a user-created table. A given row cannot be placed in two different partitions. Each partition can be placed in its own dbspace and managed individually. A partition shares its parent table’s logical attributes:

Column definition with the same integrity constraints and defaults
The same referential integrity constraints
The same unique and primary key constraints
The same check constraints

The table creator chooses whether to partition a table, how to partition it, and the number of partitions. The table creator defines the partition key, a table column that determines how a table should be partitioned. See Reference: Statements and Options > SQL Statements> CREATE TABLE statement and Reference: Statements and Options > SQL Statements> ALTER TABLE statement for syntax.

A fundamental administration concept of partitioning is the ability to make a subobject read-only. Once a subobject is set read-only, validated, and backed up, it needs minimal maintenance.

Benefits of Dbspace Management and Table Partitioning

Partitioning and dbspace management allow administrative operations (data placement, dbcc, backup, restore) to be performed at a finer granularity than at the table or database level.

In Sybase IQ, dbspace management and range table partitions:

Provide data placement capability
Provide hierarchical storage management by supporting relocation of less critical data to cheaper storage

Dbspace management and range table partitions improve maintainability and availability by:

Supporting dbspace read-only (RO) vs. read-write (RW) status
Supporting dbspace online vs. offline status
Shortening backup/restore by allowing backing up or restoring one or more RO dbspaces and/or files, or all RW files
Supporting data validation on a dbspace or a table partition target. (See Reference: Building Blocks, Tables, and Procedures > System Procedures > sp_iqcheckdb procedure.)
Allowing Sybase IQ servers to start with unavailable and/or non-usable dbspaces marked “offline” except for the catalog store and the Sybase IQ system dbspace.

Partitioning improves administration and runtime operations, particularly backup, restore, and database validation, by organizing storage and data according to business requirements.

Sybase IQ range partitioning divides a table into logical partitions based on the values of a single table column. Only base tables can be partitioned; global temporary tables or declared local temporary tables cannot. All rows of a table partition are physically colocated, and the user must name each partition.

Sybase IQ supports a maximum of 1024 partitions for range partitioning.

The most common form of range partitioning is to partition the table by date; for example, June_2009, July_2009, and so on. A range table partition may be assigned to a dbspace.