You cannot enforce unique indexes, including primary keys, if the partition keys are not the same as the local index or primary key columns (or a subset of them).
Most customers consider partitioning their tables because it allows more efficient and practical use of the parallel query feature, or because it makes database administration (DBA) tasks easier, saving time and money.
For DBA tasks, partitioning may cause unexpected behavior.. Customers typically partition a table for DBA tasks based on a date or day – for example, a modulo day number calculated as an offset, which is typically done to allow faster archiving of older data. However, sometimes this date is not in the primary key, or is only one column in the primary key. The same problems occur if the table is partitioned according to a lower cardinality division such as using a state or country and a unique key.
For example, the following table contains customers (uniquely identified by cust_id) in the United States and divides them according to sales region, and is partitioned by state.
Figure 4-1: Partitioned tables with local indexes or primary keys
Even though you insert the same cust_id value (“12345”) into the table, the insert succeeds because you insert the data into different state partitions. The index partitions act independently, and when you insert the values, you cannot know if the value already exists in another partition. For this reason, Adaptive Server displays a warning when you partition a table on a column list that does not include the primary keys, or when you attempt to create a unique local index on columns that are not used for partition keys.
For performance reasons, Adaptive Server does not enforce uniqueness. For example, in a 50-million row table, the primary key and any nonclustered index need about seven levels of indexing to find a data leaf node from the root node of the index. If you partition this table (assuming an even distribution of values), each partition has one million rows, which requires five levels of indexing. In an unpartitioned table, the unique value check takes only seven I?Os to read to the point of insertion to determine if a row with that value already exists. However, for a partitioned index, the value chech must traverse all five levels for all 50 partitions—250 I/Os total.
As a workaround, create a global unique index to enforce uniqueness instead of a local index or primary key constraint (all primary key constraints are partitioned according to the table schema). Since a global index is unpartitioned, uniqueness can still be enforced.
