Creating a Range Partition

Range partitioning divides large tables by a range of partition-key values established for each partition. Range partitioning can shorten backup and restore times, provide a finer level of granularity for data validation, and support tiered storage.

Prerequisites

Database Version	Table Partition Privileges
SAP Sybase IQ 15.3 and 15.4	Requires one of: DBA authority. CREATE permission on the dbspaces where the partitions are being created. Also requires one of: ALTER permission on the table. You own the table.
SAP Sybase IQ 16.0	Requires one of the following: ALTER ANY OBJECT system privilege. CREATE permission on the dbspaces along with one of: ALTER ANY TABLE system privilege. ALTER permission on the table. You own the table.

Database Version

Table Partition Privileges

SAP Sybase IQ 15.3 and 15.4

Requires one of:

DBA authority.
CREATE permission on the dbspaces where the partitions are being created.

Also requires one of:

ALTER permission on the table.
You own the table.

SAP Sybase IQ 16.0

Requires one of the following:

ALTER ANY OBJECT system privilege.
CREATE permission on the dbspaces along with one of:
- ALTER ANY TABLE system privilege.
- ALTER permission on the table.
- You own the table.

The SAP Sybase IQ resource is authenticated and running.
In a multiplex configuration, the coordinator node is running.
You are licensed for the Very Large Database Management option (IQ_VLDBMGMT).
If the table to be partitioned contains data, the resulting partitioning must have all rows in the first partition.

Task

In the Perspective Resources view, select the resource and select Resource > Administration Console.
In the Administration Console, expand IQ Servers > Schema Objects > Tables.
Select Tables, select the table in the right pane, click the drop-down arrow that appears next to the table name, and select Properties.
Select Partitions and click New.
The Create Partition Wizard appears.
On the Partition Type page, specify a range partition:

Option Description

What type of partition do you want to create? Select Range.
Click Next.

Option	Description
What type of partition do you want to create?	Select Range.

On the Partition Key page, specify a column:

Option	Description
Which column do you want to include in the partition key?	Select a table column and click Add. A partition key cannot contain LOB, binary, varbinary, bit, float, double, or real data, or any char or varchar column with a length that exceeds 255 bytes.

Option

Description

Which column do you want to include in the partition key?

Select a table column and click Add.

A partition key cannot contain LOB, binary, varbinary, bit, float, double, or real data, or any char or varchar column with a length that exceeds 255 bytes.

Click Next.
On the Partitions page, click Add.
The Define a Partition dialog appears.

On the Define a Partition dialog, specify the range partition definition:

Option	Description
Specify the name, value and dbspace for the range partition.	Name – Enter a descriptive name for the partition. Constraint – Less than or equal (<=) is the only constraint currently supported. Value – Enter the upper limit value of the partition. Dbspace – Select the dbspace for the partition from the pull-down.

On the Columns page, click Add if you want to store any column data in a separate dbspace from the partition. Otherwise, click Finish.
If you click Add, the Specify Dbspace for a Partition Column page appears.
Choose the column, partition, and dbspace from the pull-down and click OK.
When you have specified all the columns desired, click Finish.