Autopartitioning Scheme for SAP ASE

Overview

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

SAP ASE autopartitioning uses the SAP ASE 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

Because PKCOL is a primary key column, you should receive 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 specified 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 SAP ASE Performance and Tuning Guide for creating and using indexes. Here is the order of column selection for the partitioning column.

1. Identity column
2. Primary key column (INTEGER or NUMERIC)
3. INTEGER, NUMERIC, or BIT; not nullable
4. 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 data 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 that does not have a specified index.

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

Data Types

These data types are supported in partitioning column selection:

• BIT
• DECIMAL
• FLOAT
• INTEGER
• NUMERIC
• SMALLINT
• TINYINT

Autopartitioning Examples

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