Partition tables or indexes in any way that best reflects the needs of your application. Sybase recommends that you put partitions on segments that use different physical disks so that enough I/O parallelism is present. For example, you can have a well-defined partition based on hashing of certain columns of a table, or certain ranges, or a list of values ascribed to a partition. Hash, range, and list partitions belong to the category of “semantic-based” partitioning—given a row, you can determine to which partition the row belongs.
Round-robin partitioning has no semantics associated with its partitioning. A row can occur in any of its partitions. The choice of columns to partition and the type of partitioning used can have a significant impact on the performance of the application. Think of partitions as a low-cardinality index; the columns on which partitioning must be defined are based on the queries in the application.
The query processing engine and its operators take advantage of the Adaptive Server partitioning strategy. Partitioning defined on table and indexes is called static partitioning. In addition, Adaptive Server dynamically repartitions data to match the needs for relational operations like joins, vector aggregation, distincts, unions, and so on. Repartitioning is done in streaming mode and no storage is associated with it. Repartitioning is not the same as issuing the alter table repartition command, where static repartitioning is done.
A query plan consists of query execution operators. In Adaptive Server, operators belong to one of two categories:
Attribute-insensitive operators include scans, union alls, and scalar aggregation. Underlying partitions do not affect attribute-insensitive operators.
Attribute-sensitive operators (for example, join, distinct, union, and vector aggregation) allow for an operation on a given amount of data to be broken into a smaller number of operations on smaller fragments of the data using semantics-based partitioning. Afterwards, a simple union all provides the final result set. The union all is implemented using a many-to-one exchange operator.
The following sections discuss these two classes of operators. The examples in these sections use the following table with enough data to trigger parallel processing.
create table RA2(a1 int, a2 int, a3 int)
