Chapter 10: Partitioning Tables and Indexes

Using partitions to load table data

You can use partitioning to expedite the loading of large amounts of table data, even when the table eventually will be used as an unpartitioned table.

Use the round-robin partitioning method, and place all partitions on the same segment.

The steps are:

Create an empty table, and partition it n ways. For example, enter:

create table currentpublishers
(pub_id char(4) not null,
pub_name varchar(40) null,
city varchar(20) null,
state char(2) null)
partition by roundrobin 3 on (seg1)

Run bcp in using the partition_id option. Copy presorted data into each partition. For example, to copy datafile1.dat into the first partition of currentpublishers, enter:
```
bcp pubs2..currentpublishers:1 in datafile1.dat
```
Unpartition the table using alter table unpartition. For example, enter:
```
alter table currentpublishers unpartition
```
Create a clustered index using created clustered index with the with sorted_data clause. For example, enter:
```
create clustered index pubnameind
		on currentpublishers(pub_name)
```
```
		with sorted_data
```

When the partitions are created, Adaptive Server places an entry for each one in the syspartitions table. bcp in with the partition_id option loads data into each partition in the order listed in syspartitions. You unpartitioned the table before creating the clustered index to maintain this order.

View this book as PDF