Creating and Using Segments

A segment is a label that points to one or more database devices.

Segment names are used in create table and create index commands to place tables or indexes on specific database devices. Using segments can improve SAP ASE performance and give the system administrator or database owner increased control over the placement, size, and space usage of database objects.

Create segments within a database to describe the database devices that are allocated to the database. Each SAP ASE database can contain up to 32 segments, including the system-defined segments. Before assigning segment names, you must initialize the database devices with disk init and then make them available to the database with create database or alter database.

See Performance and Tuning Series: Physical Database Tuning > Controlling Physical Data Placement for information about how segments can improve system performance.

• System-Defined Segments
  When you create a database, SAP ASE creates three segments in the database.
• Segment Usage in SAP ASE
  When you add a new device to a database, SAP ASE places the new device in a default pool of space (the database’s default and system segments).
• Creating Segments
  SAP ASE includes two methods for creating a segment in a database.
• Changing the Scope of Segments
  When you use segments, you must also manage their scope—the number of database devices to which each segment points. You can extend or reduce the scope of a segment.
• Assigning Database Objects to Segments
  Assign new or existing database objects to user-defined segments to restrict new objects to one or more devices, place a table and its index on separate devices, or split an existing object over multiple devices.
• Dropping Segments
  When you use sp_dropsegment with only a segment name and the database name, the named segment is dropped from the database.
• Getting Information About Segments
  SAP ASE includes a number of system procedures that provide information about segments.
• Segments and System Tables
  Three system tables store information about segments: master..sysusages and two system tables in the user database, sysindexes and syssegments.
• A Segment Tutorial
  There are a number of things to consider when you are working with segments and devices.