Create and use segments in Sybase

A segment (Segment) is a logical combination of disk space on a database device and can be viewed as a label that points to one or more database devices. Segments can be used to control the location of database objects, the database objects can be categorized into different segments.

The relationship between the device and the segment: Many-to-many relationships. Multiple segments can be created on one device, and one segment can also overwrite multiple devices.

1. Advantages of using segments

A. Use of control space: database objects placed on one segment do not grow outside the paragraph;

B. Improve performance: segments on different disk devices can be read and written in parallel;

D. Handling large tables: Using segments, you can place a large table fragment on a separate physical device, such as storing the text or image data of one table on another segment.

2. Create a segment

Sp_addsegment section name, database name, device name

Description: Creates a segment for a database on the specified device.

Scope of the extension segment

Sp_exetendsegment section name, database name, device name

Note: The device must be available in the database, or you will need to extend the database to a new device; The specified segment, database, device must exist.

Narrow the scope of the paragraph:

Sp_dropsegment section name, database name, device name

Note: When you take the third parameter, the command does not delete the segment, only the range of the segment shrinks. If a segment contains a device that you want to monopolize for another segment, you need to narrow the scope of the paragraph.

3. Use segment

Two databases are placed on different segments of the same device, and they do not affect each other;

When the database increases the space, the additional space is automatically assigned to each segment of it;

For example: ALTER DATABASE my_db

On data_dev=50

Add 50M space to the my_db on the Data_dev device, which is automatically assigned to each segment of the database. Note: If Data_dev is new to the database, the system and default segments are automatically extended to the device.

You can use the Log on option of the ALTER DATABASE command to allocate additional log space.

(1) Create a new object in a paragraph

CREATE TABLE table name (column name data type) [on segment name]

Create [Clusterd|non clusterd]index index name on table name (column name) [on segment name]

Note: By definition, a clustered index is always placed on the same paragraph as the table.

(2) Placing existing objects on a segment

Sp_placeobject section name, object name

Note: This command does not move an object from one database device to another, it affects only the future allocation of space.

You can place the text field or image field of a large table on a separate device segment.

Sp_placeobject segment name, "Table name." Field name "

(3) Create a clustered index on a segment

By definition, a clustered index is always placed on the same paragraph as the table. If you create a table on one segment and you create a clustered index on another, its table moves with its index, and the entire table migrates to the segment where the table was created and migrated to the section where the clustered index was created. In this way, you can quickly and easily move the specified table to the specified device.

(4) predefined segments of the system

When a user creates a database, Sybase automatically creates three predefined segments:

System segment: Storing systems tables (including definitions of all user objects)

Default segment: Stores the various objects created by the user unless they are clearly assigned to a different segment.

Logsegment segment: A transaction log that holds the database.

(5) Delete paragraph

Deleting a segment is a special case of narrowing the scope of a paragraph:

Sp_dropsegment segment name, database name

4. Using Threshold management

Threshold value (Threshold) management is a mechanism for automatically monitoring database free space, and Sybase's threshold management allows users to set thresholds and define corresponding stored procedures for the free space on a segment of a database. Sybase automatically runs the corresponding stored procedure when the free space on the segment is below the threshold value of the set.

In a real-world database, the log grows faster than the data, and once the free space of the log segment runs out, SQL Server suspends all data manipulation transactions by default, and the client application stops executing.

The database in which the transaction log is stored on each detached segment is automatically set to have the last chance threshold (Chance Threshold), whose threshold is the estimate of the free space required to back up the transaction log. Sybase automatically runs a stored procedure named sp_thresholdaction when the free space on the segment is below the threshold value. The name and parameters of the process are predefined by the system, and the content is written by the user. The following is a simple example.

CREATE PROCEDURE dbo.sp_thresholdaction

/* This process parameter passes through the position, the name may change, but its definition and the order cannot change * *

@db_name varchar (30),/* Database name * *

@seg_name varchar (30), * * Section name * *

@space_lefe int,/* remaining free space * *

@status int/* Last chance threshold with a value of 1 and other thresholds with a value of 0*/

As

BEGIN

* * User Writing process content * *

Dump TRANSACTION @db_name

With TRUNCATE_ONLY

End