Splitting tables

You can split a large, heavily used table across devices on separate disk controllers to improve the overall read performance of a table. When a large table exists on multiple devices, it is more likely that small, simultaneous reads will take place on different disks. Figure 8-2 shows a table that is split across the two devices in its segment.

Figure 8-2: Partitioning a table across physical devices

Graphic showing how table A’s segment  is split across two disks

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


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 (see Figure 8-2). Partitioning a table increases both insert and read performance, since multiple page chains are available for insertions.

Before you can partition a table, you must create the table on a segment that contains the desired number of devices. The remainder of this chapter describes how to create and modify segments. See Chapter 1, “Controlling Physical Data Placement,” in Performance and Tuning Series: Physical Database Tuning 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 provides a way to split tables and their clustered indexes across physical devices. See “Placing existing objects on segments” for more information and syntax.


Separating text and image columns

Adaptive Server 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. See “Placing text pages on a separate device” for more information and syntax.