Scheduling and tuning of Informix Dynamic server table fragmentation strategy

Data fragmentation allows data storage to be controlled at the table level. "Table Fragmentation" is a feature of the Informix database. Users can group records or indexes in a table and store them in different locations so that data can be stored on multiple disks, thereby reducing competition for disk I/O. The scheme of data slicing and a set of dbspace stored in the fragment data constitute the "slicing strategy". There are two basic types of data fragmentation: web-based fragmentation and expression-based fragmentation, which, as with its name, is stored in the table according to the rotation method. Expression fragmentation defines the fragmented rules based on one or more fields in the table. This approach is generally used when predicting query conditions, thus avoiding the scanning of certain fragments in queries.

The "Data in the table" and "index" fragmentation is mainly to improve the efficiency of the program, because Informix Dynamic server can scan the data on multiple disks in parallel, so as to achieve the parallel operation of internal query, so the use of "fragmentation" technology can improve query efficiency. The parallelism of internal queries helps reduce the response time to a complex query. The "Table fragmentation" technique is associated with parallel data query (PDQ) features so that the Informix server can allocate more than one thread. Selects data in parallel from all data slices. In addition, you can scan only data fragments that contain "target data." Thus, the efficiency of the whole system is greatly improved. The "Dataskip" feature also allows users to skip data fragmentation that fails or does not contain "target data". When some data is in the disk failure, it can reflect a high degree of "data availability" We use a large number of data fragments to spread the data on many disks, Also implements the parallel operation of the external query. This can reduce the competition for I/O when a large number of users are accessing the same table, and the number of transactions completed per second (System throughput capability) is also improved. The table fragmentation technique also backs up/restores data slices that are stored on dbspace.

Slicing scheme

You can use the partitioning scheme when creating tables and indexing. An index can be attached to the data associated with it or separated from the data. An index can be considered to be attached to the data if it appears in the partitioning scheme for the table data. Alternatively, if the partitioning scheme of the index is different from the data, it is called Index and data separation. In this case, the index is stored on the specified dbspace.

There are two main types of "Data fragmentation": "Based on the rotation method" and "based on the expression" scheme,

Conversion scheme

The fragmentation rules used in the Rotation method (PR) are defined within the system. With this scenario, the newly appended record is stored in the next data fragment specified in the rotation method. The first data fragment inserted by the record is also randomly selected, for example, the following syntax is used to create a table named XYZ:

CREATE TABLE xyz FRAGMENT BY ROUND ROBIN　
IN dbspace1,dbspace2......dbspaceN

Informix does not support the use of rotation to create indexes, as this can degrade system performance. The advantage of the Rotary method scheme is that the data on each data fragment is more homogeneous, and the records are not transferred when the records are updated. However, there is a flaw in the rotation method, which scans all data fragments when making a query. Therefore, the rotation method does not support Dataskip and does not allow Informix dynamic servers to cross a data fragment. If Dataskip is not supported and a data fragment error occurs, the entire query fails because there is no certainty that a qualifying data record exists on the faulted data fragment. The application of the rotation method is that the user needs to load the data quickly, the user does not know the way of data access in advance. The user's data is often updated, or the user is unknown about how the data is distributed.

"Expression-based" slicing method

For an expression-based slicing scheme, the user can fragment the data using the following two kinds of rules

Scope rule

Absolute rule

Scope rule

The scope rule defines the bounds of the data fragmentation of a table using SQL relational or logical operations. A scope rule can contain relational operators, such as >;,<,>;=,<=, and can contain some logical operators, such as and. The scope rule is best fragmented according to one of the fields of the table, but it can also be fragmented according to two or more fields. The following fragment instance is inefficient to run because too many operations are required to insert a row into a table. All records that do not conform to the expression criteria are stored in the remainder dbspace. A fragmentation scheme based on "range expression" ensures that only data fragments containing the target data are scanned.

Here is an example of creating a table using the "Scope expression fragmentation scheme":

CREATE TABLE xyz (aa integer....)
FRAGMENT BY EXPRESSION
aa<=100 IN dbspace1
aa>;1000 AND aa<2000 IN dbspace2
REMAINDER IN dbspace3