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:

  1. 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"
    

    See Chapter 7, “Initializing Database Devices,” in the System Administration Guide: Volume 1.

  2. 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
    
  3. (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
    
  4. Drop all indexes from the table to partition. For example:

    use pubs2
    
    drop index salesdetail.titleidind,
    		salesdetail.salesdetailind
    
  5. Use sp_dboption to enable the bulk-copy of table or index data to the new partitions. For example:

    use master
    
    sp_dboption pubs2,"select into", true
    
  6. 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)
    
  7. 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)