Informix Dynamic Server Database segmented storage

Informix Dynamic Server segmented storage, also known as fragmented storage, is based on the principle of regularly dispersed records or indexes in a datasheet to different areas of the disk, to store data on multiple disks, and to increase the efficiency of the database by reducing the competition for disk I/O. In contrast to this is the basic storage principle, which encourages the data in a database table to be kept on a storage device as continuously as possible, while the Informix Dynamic server reads out data in batches to increase the efficiency of the database.

Segmented storage is to improve query efficiency by balancing disk I/O, realizing parallel operation of internal query, scanning data on multiple disks in parallel, and improving the performance of database, which mainly comes from the increase of I/O parallelism, rather than the improvement of I/O performance. In fact, segmented storage is a method of storing a table with large amounts of data, which allows the user to select segmented storage or not to select segmented storage. A good segmented storage tool can improve query efficiency. Otherwise, not only the database query efficiency can not be improved but will be reduced. In practical applications, Informix Dynamic server does not care about which specific physical disk The data is placed on, but rather on the corresponding dbspace, because the smallest unit that Informix dynamic server queries can execute concurrently is dbspace.

The principle of segmented storage

Segmented storage can be divided into rotary method and an expression based approach. The rotation method uses the rules defined within the Informix Dynamic server to store the tables in fragments. When you are building a table, you can specify that the data is stored in a rotational way, with the syntax:

CREATE TABLE tablename (aa integer …) FRAGMENT BY ROUND ROBIN IN dbspace1,dbspace2, ……dbspaceN。
An expression-based approach is to store a table or index fragment in a different dbspace using user-defined rules. The method based on expression can be divided into scope rules and absolute rules, the scope rule is to define the bounds of the data fragmentation of a table with the relational (>, <, >=, <=) or logical operations (and, or) of the SQL, typically in one field, or 2 or more fields as needed. The syntax for the scope rule is:
CREATE TABLE tablename (a1integer....)
FRAGMENT BY EXPRESSION
a1 <= 0 IN dbspace1，
a1 >= 0 AND a1 <= 100000 IN dbspace2，
REMAINDER IN dbspace3;

Absolute rules define rules with relational operators (>, <, >=, <=) and logical operators (and, or), and can be multiple fields in a table participating in a fragment. The syntax for absolute rules is:

CREATE TABLE tablename (a1 integer....)
FRAGMENT BY EXAMPLE
a1 = 100000 or a1=200000 IN dbspace1，
a1 = 300000 or a1=400000 IN dbspace2，
REMAIDER IN dbspace3;

Since the rotation method uses the rules defined within the Informix Dynamic server, it is simpler and more convenient to use in the process. Based on the expression method, using user-defined rules, in order to improve query efficiency, it is necessary to reduce CPU load, balance disk I/O operations. As a result, users should follow these guidelines when they use expressions to define fragmentation rules:

1. To make the expression as straightforward as possible, avoid using a data type conversion in an expression to parse the expression as quickly as possible when the Informix Dynamic server writes or reads the data;

2. In order to reduce the computational load of expressions, the most restrictive part should be placed in the front;

3. In the segmented expression to avoid the frequently updated fields, so that the segmented stored data has a relatively fixed position;

4. The stored data and query statements are analyzed, and the results of the query output are clarified, so that the data accessed frequently can be distributed evenly on multiple disks.

The goal of segmented storage design is to provide users with better response time, better concurrency, better and faster backup and recovery, and better availability. But segmented storage can increase the management cost of the database, and the transfer of the database is not convenient. In order to achieve this goal, when the database is segmented design, it is necessary to consider the hardware characteristics of the host, such as the number of CPUs, speed, number of disks, the number of disk controllers, the number of disks connected to each disk controller, and the performance of the operating system. When hardware characteristics and operating system performance meet segmented storage, some tables with large data storage can be segmented to store, so that their performance will be improved.