Splitting Tables

To improve the overall read performance of a table, split a large, heavily used table across devices on separate disk controllers.

When a large table exists on multiple devices, it is more likely that small, simultaneous reads take place on different disks.

You can split a table across devices using these different methods, each of which requires the use of segments:

Use table partitioning.
If the table has a clustered index, use partial loading.
If the table contains text or image datatypes, separate the text chain from other data.

Partitioning Tables

Partitioning a table creates multiple page chains for the table and distributes those page chains over all the devices in the table’s segment.

Partitioning a table increases both insert and read performance, since multiple page chains are available for insertions.

Partitioning a table across physical devices
Graphic showing how table A’s segment is split across two disks

Before you can partition a table, you must create the table on a segment that contains a specified number of devices. See Performance and Tuning Series: Physical Database Tuning > Controlling Physical Data Placement for information about partitioning tables using alter table.

Partial Loading

To split a table with a clustered index, use sp_placeobject with multiple load commands to load different parts of the table onto different segments.

This method can be difficult to execute and maintain, but it does allow you to split tables and their clustered indexes across physical devices.

Separating Text and Image Columns

SAP ASE stores the data for text and image columns on a separate chain of data pages.

By default, this text chain is placed on the same segment as the table’s other data. Since reading a text column requires a read operation for the text pointer in the base table and an additional read operation on the text page in the separate text chain, placing the text chain and base table data on a separate physical device can improve performance.