Partitioning is useful in managing large tables and indexes by dividing them into smaller, more manageable pieces. Partitions, like a large-scale index, provide faster and easier access to data.
Partitions are database objects and can be managed independently. You can, for example load data, and create index cannot be done at a partition level.. Yet partitions are transparent to the end user, who can select, insert, and delete data using the same commands whether the table is partitioned or not.
Adaptive Server 15.0 supports horizontal partitioning, in which a selection of table rows can be distributed among partitions on different disk devices. Individual table or index rows are assigned to a partition according to a semantic or to a round-robin partitioning strategy.
Semantic partitioning strategies use the data values in specified, key columns in each row to determine the partition assignment of that row. The round-robin partitioning strategy assigns rows randomly without reference to data values.
Hash partitioning (semantic) – a system-supplied hash function determines the partition assignment for each row.
List partitioning (semantic) – values in key columns are compared with sets of user-supplied values specific to each partition. Exact matches determine the partition assignment.
Range partitioning (semantic) – values in key columns are compared with a user-supplied set of upper and lower bounds associated with each partition. Key column values falling within the stated bounds determine the partition assignment.
Round-robin partitioning – rows are assigned randomly to partitions in a round-robin manner so that each partition contains a more or less equal number of rows. This is the default strategy.
Create partitions when you create a table or index using the create table and create index commands.
Alter a table’s partitioning strategy using the alter table command.
Add a partition to an existing table with add partition.
You can use partitioning to expedite the loading of large amounts of table data—even when the table eventually will be used as an unpartitioned table.