Creating Tables with Partitions

Considerations for creating tables with partitions.

  • Before you create a table with partitions, you must prepare the disk devices and segments that you are using for the partitions.

  • Range partitioning is dependent on sort order. If the sort order is changed, you must repartition the table for the new sort order.

  • Range-partition bounds must be in ascending order according to the order in which the partitions are created.

  • A column of text, unitext, image, or bit, Java datatype, or computed column cannot be part of a partition key, but a partitioned table can include columns with these datatypes. A composite partition key can contain up to 31 columns.

  • For range and hash partitions, the partition key can be a composite key with as many as 31 columns. In general, however, a table with more than four partition columns becomes hard to manage and is not useful.

  • Bound values for range and list partitions must be compatible with the corresponding partition key datatype. If a bound value is specified in a compatible but different datatype, the SAP ASE server converts the bound value to the partition key’s datatype. The SAP ASE server does not support:

    • Explicit conversions.

    • Implicit conversions that result in data loss.

    • NULL as a boundary in a range-partitioned table.

    • Conversions from nonbinary datatypes to binary or varbinary datatypes.

  • You can use NULL in a value list for list-partitioned tables.

  • You can partition a table that contains text and image columns, but partitioning has no effect on the way the SAP ASE server stores the text and image columns because they reside on their own partition.

  • You cannot partition remote tables.

  • The SAP ASE server considers NULL to be lower than any other partition key value for a given parition key column.