There are a few special considerations for loading partitioned tables:
When modifying a partitioned table, the best performance is achieved when the partitioning column is the first column in the column list of the command.
List partitioning columns before large object (LOB) columns in the SELECT statement clause of an INSERT...LOCATION statement and load data from a primary file. The data in the primary file should be rearranged using a pre-load process, if possible.
The START ROW ID clause of the LOAD TABLE and the INSERT statements is not allowed on a partitioned table. The following error is reported and a rollback is performed on the load operation:
"Option START ROW ID not allowed on a partitioned table."
(SQLCODE -1009416L, SQLSTATE QCB14, Sybase error code 21054)
Partial width inserts are not recommended, as the START ROW ID clause of the INSERT statement is not supported on a partitioned table.
Be sure to include the partition key column of the partitioning columns of the table in the column list of the load operation and leave all non-specified columns as NULL, if you do perform a partial width insert. If the partition key column is omitted from the column list, the following error is reported:
"Operation not allowed — partition key column %2 not specified."
(SQLCODE -1009418L, SQLSTATE QCB16, Sybase error code 21056) where %2 is the name of the partition key.
The APPEND_LOAD database option behaves differently for partitioned and non-partitioned tables. Row ID ranges are assigned to each partition in a partitioned table. For partitioned tables, when APPEND_LOAD is ON, new rows are appended at the end of the appropriate partition. When APPEND_LOAD is OFF, the load reuses the first available row IDs and space from deleted rows of the appropriate partition.
For non-partitioned tables, when APPEND_LOAD is ON, new rows are added after the maximum row ID that is at the end of the table rows. When APPEND_LOAD is OFF, the load reuses the deleted row IDs. With non-partitioned tables, you can also control where rows are inserted by using the LOAD or INSERT START ROW ID clause to specify the row at which to start inserting.
An attempt to update the contents of a partitioning column results in the following error:
"Updating partition key column on a partitioned table is not allowed."
(SQLCODE -1009417L, SQLSTATE QCB15, Sybase error code 21055)
Table partitioning is part of the separately licensed Sybase IQ VLDB Management option.
