When tables grow large, queries on the table will be slow. Index range scans, table scans, backups and restores will be slow. The most common characteristic of such tables is that the older entries in the table are seldom changed and updates happen on the newer entries. By partitioning the table, the older entries can be maintained in a seperate partion, thereby increasing performance--looking up for query results in a smaller set of data, parallel processing in different partitions, faster backups etc.
Types of Partitions:
- Range Partition: This allows you to partition data by range of values. Example: 1st partition by column Date: Jan 01 2000 - Dec 31 2005; 2nd partition by column Date: Jan 01 2006 - Dec 31 2010 and 3rd partition by column Date: Jan 01 2011 - Dec 31 2015.
- List Partition: This allows you to maintain a list of values that constitute a partition. Ex: 1st partition by Plant: 5000; 2nd partition by Plant:6000.
- Hash Partition: A hash algorithm is used to distribute data into different partitions.
- Combined Partition: A combination of above three partitions to create partitions and sub-partitions.
To partition a table in SAP:
- Call transaction SE14
- Enter the table to be partitioned and click on Edit
- Click on Storage Parameters
- Click on For new creation
- Place the cursor on Table and click Create parameter values (second button) in the toolbar
- Choose a template for the parameter values (ex: Current database parameters)
- Place the cursor on PARTITION BY and press F4 to select the partitioning method.
- Depending on the partition method you choose, some more parameters will populate. You can enter one or more values for these parameters. The values are dependent on the data type and are case-sensitive.
- Save the changes
- Choose Activate and adjust database to create partitions