SAS/ACCESS Interface to Sybase

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, which, in the optimum scenario, 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 get a fairly even distribution among the three partitions, which is the primary goal.

Indexes

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. Understanding and following Sybase ASE Performance and Tuning Guide documentation recommendations with respect to index creation and usage is essential in order to achieve optimum database performance. Here is the order of column selection for the partitioning column:

Identity column
Primary key column (integer or numeric)
integer, numeric, or bit; not nullable
integer, numeric, or bit; nullable

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

Partitioning Criteria

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 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.

Data Types

These data types are supported in partitioning column selection:

	INTEGER
	TINYINT
	SMALLINT
	NUMERIC
	DECIMAL
	FLOAT
	BIT

Examples

The following 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

Top of Page