Partitioning Tables for Improved Performance

Use the partition by clause to partition an unpartitioned table or repartition an already partitioned table. The task requires a data copy; all data rows are redistributed according to the specified partition criteria.

  • You may run this task in parallel if the SAP ASE server is configured for parallel processing. You must set the select into/bulkcopy/pllsort option to true. If the table has indexes, you must drop the indexes before you can change:

    • An unpartitioned table into a semantic-partitioned table.

    • The partitioning type.

    • The partitioning key – you need not drop indexes to change other attributes of the partitions, such as number of partitions, partition bounds, or partition location; the indexes are built automatically.

  • You can use the add partition clause to add empty partitions to list- or range-partitioned tables, but not to hash or round-robin-partitioned tables.

    For range-partitioned tables, you can add new partitions only to the high end of the partition conditions. If the last existing partition has the maximum boundary (values <= (MAX)), you cannot add new partitions.

  • The partition number_of_partition and unpartition clauses are provided for compatibility with versions of SAP ASE earlier than 15.0. You can use partition number_of_partition only on unpartitioned tables to add (number_of_partition-1) empty round-robin partitions; existing data is placed on the first partition, with subsequent data distributed among all partitions. If the table has a global clustered index, the SAP ASE server places subsequent data rows in the first partition. To redistribute the data, drop and re-create the index.

    Note: These commands do not require data movement. However, because the SAP ASE server performs a number of internal steps, the commands, especially when executed on large tables, do not occur instantly. To avoid data corruption, do not interrupt the operation while you partition or unpartition a table.

    You can use the unpartition clause only on round-robin-partitioned tables without indexes.

  • You cannot partition system tables.

  • You cannot partition remote proxy tables.

  • You cannot issue the partition-related alter table commands within a user-defined transaction.

  • You cannot change a table’s partitioning properties using the partition by clause if there are active open cursors on the table.

  • After using the partition by clause, you must perform a full database dump before you can use dump transaction.

  • You cannot drop a column that is part of a partitioning key.

  • Alter key columns with care. In some cases, modifying the datatype of a key column might redistribute data among partitions. See the Transact-SQL Users Guide.

  • Changing a table’s partitioning properties increments the schema count, which causes existing stored procedures that access this table to recompile the next time they are executed.