Autopartitioning Scheme for Sybase

For general information about this feature, see Autopartitioning Techniques in SAS/ACCESS.

Sybase autopartitioning uses the Sybase MOD function (%) to create multiple SELECT statements with WHERE clauses. In the optimum scenario, the WHERE clauses divide the result set into equal chunks: one chunk per thread. For example, assume that your original SQL statement was SELECT * FROM DBTAB, and assume that DBTAB has a primary key column PKCOL of type integer and that you want it partitioned into three threads. Here is how the autopartitioning scheme would break up the table into three SQL statements:

select * from DBTAB where (abs(PKCOL))%3=0
select * from DBTAB where (abs(PKCOL))%3=1
select * from DBTAB where (abs(PKCOL))%3=2

Since PKCOL is a primary key column, you should receive a fairly even distribution among the three partitions, which is the primary goal.

An index on a SAS partitioning column increases performance of the threaded Read. If a primary key is not defined for the table, an index should be placed on the partitioning column in order to attain similar benefits. To achieve optimum database performance, it is essential to understand and follow the recommendations in the Sybase ASE Performance and Tuning Guide for creating and using indexes. Here is the order of column selection for the partitioning column.

If the column selected is a bit type, only two partitions are created because the only values are 0 and 1.

The most efficient partitioning column is an Identity column, which is usually identified as a primary key column. Identity columns usually lead to evenly partitioned result sets because of the sequential values that they store.

The least efficient partitioning column is a numeric, decimal, or float column that is NULLABLE, and does not have an index defined.

Given equivalent selection criteria, columns defined at the beginning of the table definition that meet the selection criteria takes precedence over columns defined toward the end of the table definition.

These data types are supported in partitioning column selection:

Here are examples of generated SELECT statements involving various column data types.

COL1 is numeric, decimal, or float. This example uses three threads (the default) and COL1 is NOT NULL.

select * from DBTAB where (abs(convert(INTEGER, COL1)))%3=0
select * from DBTAB where (abs(convert(INTEGER, COL1)))%3=1
select * from DBTAB where (abs(convert(INTEGER, COL1)))%3=2

COL1 is bit, integer, smallint, or tinyint. This example uses two threads (the default) and COL1 is NOT NULL.

select * from DBTAB where (abs(COL1))%3=0
select * from DBTAB where (abs(COL1))%3=1

COL1 is and integer and is nullable.

select * from DBTAB where (abs(COL1))%3=0 OR COL1 IS NULL
select * from DBTAB where (abs(COL1))%3=1