Chapter 10: Partitioning Tables and Indexes

Partitioning tasks

Before you partition a table or index, you must prepare the disk devices and the segments or other storages devices that you will use for the partitions.

You can assign multiple partitions to a segment, but a partition can be assigned to only one segment. Assigning a single partition to each segment, with devices bound to individual segments, ensures the most benefit from parallelization and partitioning.

A typical order of partitioning tasks is:

Use disk init to initialize a new database device. disk init maps a physical disk device or operating system file to a logical database device name. For example:
```
use master
```
```
disk init
name ="pubs_dev1",
physname = "SYB_DEV01/pubs_dev",
size = "50M"
```
For more information about initializing disks, see Chapter 16, “Initializing Database Devices,” in the System Administration Guide.
Use alter database to assign the new device to the database containing the table or index to partition. For example:
```
use master
```
```
alter database pubs2 on pubs_dev1
```
[Optional] Use sp_addsegment to define the segments in the database. This example assumes that pubs_dev2, pubs_dev3, and pubs_dev4 have been created in a similar manner to pubs_dev1.
```
use pubs2
```
```
sp_addsegment seg1, pubs2, pubs_dev1
sp_addsegment seg2, pubs2, pubs_dev2
sp_addsegment seg3, pubs2, pubs_dev3
sp_addsegment seg4, pubs2, pubs_dev4
```

Drop all indexes from the table to partition. For example:

use pubs2

drop index salesdetail.titleidind,
		salesdetail.salesdetailind

Using sp_dboption, enable the bulk copy of table or index data to the new partitions. For example:
```
use master
```
```
sp_dboption pubs2,"select into", true
```
Use alter table to repartition a table or create table to create a new table with partitions; use create index to create a new, partitioned index; or use select into to create a new, partitioned table from an existing table.

For example, to repartition the salesdetail table in pubs2:
```
use pubs2
```
```
alter table salesdetail partition by range (qty)
		(smsales values <= (1000) on seg1,
		medsales values <= (5000) on seg2,
		lgsales values <= (10000) on seg3)
```

Re-create indexes on the partitioned table. For example, on the salesdetail table:

use pubs2

create nonclustered index titleidind
		on salesdetail (title_id)

create nonclustered index salesdetailind
		on salesdetail (stor_id)

View this book as PDF