Partitioning Strategies

Data partitioning breaks up large tables and indexes into smaller pieces that can reside on separate partitions.
Note: A segment is a portion of a device that is defined within ASE. It is used for the storage of specific types of data such as system data, log data, and the data itself.

Partitions can be placed on individual segments and multiple partitions can be placed on a single segment. In turn, a segment or segments can be placed on any logical or physical device, thus isolating I/O and aiding performance and data availability.

Note: Partitions are transparent to the end user, who can select, insert, and delete data using the same DML commands whether the table is partitioned or not.
Note: To view information about partitions use sp_helpartition.

Benefits of Partitioning:

  • Improved scalability.
  • Improved performance – concurrent multiple I/O on different partitions, and multiple threads on multiple CPUs working concurrently on multiple partitions.
  • Faster response time.
  • Partition transparency to applications.
  • Very large database (VLDB) support – concurrent scanning of multiple partitions of very large tables.
  • Range partitioning to manage historical data.

Data partitions: A data partition is an independent database object with a unique partition ID. It is a subset of a table, and shares the column definitions and referential and integrity constraints of the base table.

Note:
Sybase recommends that you bind each partition to a different segment, and bind each segment to a different storage device to maximize I/O parallelism, Each semantically partitioned table has a partition key that determines how individual data rows are distributed to different partitions.

Update  statistics on paritioned table

Above above shows a number of activities happening on a table like Update statistics, Load data, a large query. So many activities on a large and unpartitioned table will slow down performance.By partitioning a table or index into smaller pieces, DBAs can run utilities on a per partition basis. This results in the utilities running faster, allowing other operations to work efficiently on data in other partitions and insuring that the bulk of the data in the table is available for applications.

Partition IDs: A partition ID is a pseudo‐random number similar to object ID. An index or data partition is identified with a unique combination of index ID and partition ID.

Partitioning types:

  • Range partitioning
  • Hash partitioning
  • List partitioning
  • Round

Range partitioning: Rows in a range partitioned table or index are distributed among partitions according to values in the partitioning key columns.
Example:
create table employees(emp_id int )
partition by range(emp_id)
(p1 values <=(100000),
p2 values <= (200000))

Range partitioning is particularly useful for high performance applications in both OLTP and decisionsupport environments like tables with constant updates, inserts, and deletes that contain a column or columns with sequential data in them such as a customer ID or a transaction date.Select ranges carefully so that rows are assigned equally to all partitions
Note: Range partitions are ordered; that is, each succeeding partition must have a higher bound than the previous partition.
Example: if you run below; it will give error “Partition ‘p2′ ‐‐ Partition condition not in incremental order.”
create table employees(emp_id int )

partition by range(emp_id)
(p1 values <=(200000),
p2 values <= (100000))

Hash partitioning: Uses a hash function to specify the partition assignment for each row. You select the partitioning key columns, but Adaptive Server chooses the hash function that controls the partition assignment. If you choose an appropriate partition key, hash partitioning distributes data evenly across all partitions. However, if you choose an inappropriate key—for example, a key that has the same value for many rows—the result may be skewed data, with an unbalanced distribution of rows among the partitions.

Hash partitioning is a good choice for:

  • Large tables with many partitions—particularly in decision‐support environments.
  • Efficient equality searches on hash key columns.
  • Data with no particular order, for example, alphanumeric product code keys

List partitioning: List partitioning is similar to range partitioning, but here the actual values to be placed on a partition are specified. List partitioning is useful for controlling where specific values are stored, even if the column itself is not sorted, and in cases where the order of values in the partition is not important.
Example of of the syntax used to create a table with list partitioning.
create table nation|
(nationkey integer not null,
name char(25) not null,
regionkey varchar(30) not null,
comment varchar(152) not null)
partition by list (regionkey)
(region1 values (‘Americas’),
region2 values (‘Asia’),
region3 values (‘Europe’),
region4 values (‘Australia’, ‘Other’))

As with range partitioning, list partitioning distributes rows semantically; that is, according to the actual value in the partitioning key column. The value in the partitioning key column is compared with sets of user‐supplied values to determine the partition to which each row belongs. The partition key must match exactly one of the values specified for a partition.
A list partition has only one key column. The value list for each partition must contain at least one value, and value lists must be unique across all partitions. You can specify as many as 250 values in each list partition. List partitions are not ordered.

Round‐robin partitioning: In round ‐robin partitioning, Adaptive Server does not use partitioning criteria. Round‐robin‐partitioned tables have no partition key. Adaptive Server assigns rows in a round‐robin manner to each partition so that each partition contains a more or less equal number of rows and load balancing is achieved. Because there is no partition key, rows are distributed randomly across all partitions.
Example:
create table emp_details
(emp_id int not null,
emp_name varchar(40) null,
city varchar(20) null,
state char(2) null)
partition by roundrobin 3 on (seg1)

Round‐robin partitioning is supported primarily for compatibility with versions of Adaptive Server earlier than 15.0. In addition, round‐robin partitioning offers:

  • Multiple insertion points for future inserts.
  • A way to enhance performance using parallelism.
  • A way to perform administrative tasks, such as updating statistics and truncating data on individual partitions.

PartitionKeyAllowed

Note:  You cannot specify a partition when using such DML commands as select, insert, and delete. Partitions are transparent when you access the table using DML commands.
Note: You can assign multiple partitions to a segment, but a partition can be assigned to only one segment. See below image to understand.

PartitionBasic

Partitioning options with alter table: You can use the alter table command to:

  • Change an unpartitioned table to a multipartitioned table.
  • Add one or more partitions to a list or range‐partitioned tables.
  • Repartition a table for a different partitioning type.
  • Repartition a table for a different partitioning key or bound.
  • Repartition a table for a different number of partitions.
  • Repartition a table to assign partitions to different segments.

Repartitioning a table: procedure for repartitioning a table
1. If the partition key or type is to change during the repartition process, drop all indexes on the table.
2. Repartition the table using alter table.
3. If the partition key or type changed during the repartition process, recreate the indexes on the table.

Changing an unpartitioned table to a partitioned table:
Example:
alter table employee_transaction partition by range(emp_id)
(p1 values <= (100) on seg1,
p2 values <=(200) on seg2,
p3 values <= (300) on seg3)

Adding partitions to a partitioned table:
Example:
alter table employee_transaction add partition
(p4 values <= (500) on seg4)
Note: You can’t add partitions to a hash or round ‐robin partitioned table. you can to list or range partitioned tables.

Changing partition type:
Example:
alter table employee_transaction partition by hash(emp_id) 2
Note:You must drop all indexes before changing the partitioning type.

Changing the partitioning key:
alter table employee_transaction partition by hash(transaction_date) 2
Note: You must drop all indexes before changing the partitioning key.

Note: You can change partition type and partition key in a single alter command as well.
For example in above example table employee_transaction is hash partitioned and partition key is transaction_date, now we will change partition type and key in single alter command as follows:
alter table employee_transaction partition by range(emp_id)
(p1 values <= (100),
p2 values <=(200),
p3 values <= (300))

In above alter command, we changed partition type from hash to range and partition key from transaction_date to emp_id. (of course there should not be any index on the table if you want to do so).

Note: You can create an unpartitioned round‐robin table from a partitioned roundrobin table using alter table with the unpartition clause—as long as all partitions are on the same segment, and there are no indexes on the table.
Example:
alter table employee_transaction unpartition

Note: alter table with the partition clause is supported only for the creation of round‐robin partitions. It is not supported for the creation of other types of partitions.
Example:
alter table discounts partition 3

Configuration partitions:
number of open partitions – specifies the number of partitions that Adaptive Server can access at one time. The default value is 500.

partition spinlock ratio – specifies the number of spinlocks used to protect against concurrent access of open partitions. The default value is 10.

You can delete all the information in a partition without affecting information in other partitions:
Example:
truncate table emp_detail partition p1

Update statistics on partitions:
You can action following tasks for a partition

  • update statistics
  • update table statistics
  • update all statistics
  • update index statistics
  • delete statistics

Example:
update statistics employee_transaction partition p1



This entry was posted in RDBMS concepts and tagged . Bookmark the permalink.

Leave a Reply