JPS61180332A - Data storage and retrieval system - Google Patents

Abstract

PURPOSE:To increase the storage amount of data related to the same block and to attain the retrieval at a high speed in a retrieval mode by designating the number of correspondences between related data before storage of data through a table. CONSTITUTION:This system comprises an external memory 201, a card reader 202, a terminal equipment 203, a channel 204, a CPU205 and a main memory 206. The memory 206 contains a data base control system DBMS207 and a buffer area 208. When an inter-table data ratio (inter-table correspondence number) is given to the DBMS207 from the equipment 203, the DBMS207 arranges the received data in the area 208 according to a storage type stored in the memory 201 and transfers them to the memory 201 for writing. When the storage is through with the inter-table data ratio, the DBMS207 decides a storage section for each table with a data storage block.

Description

[Detailed Description of the Invention] [Field of Application of the Invention] The present invention relates to a data storage and retrieval method, and in particular, in a database management system, when a data retrieval request is received from a database user, data stored in an external storage device is stored and retrieved. The present invention relates to a data storage/retrieval method suitable for retrieving data at high speed.

[Background of the invention]

Conventionally, in database management systems, table-type data devices and search methods store only data from the same table or data from different tables in the storage area (hereinafter referred to as a block), which is the unit of transfer between the external storage device and the main storage device. A method of storing mixed data was proposed (SystemR: Re1ational App
reach to Databasa Management
ent;M, M, Astrahan at al,,A
CMTODS 1976 Jun, pp, 97-137
and IBN Database 2 PerformI
IIance: Design.

imple+mentation, and tunein
g J.N., Cheng at al., IBM Syst.
e+a Journal 1984 Vol, 23 N
o, 2 pp189-210) - However, in this method, when the data between different tables are related and the related data is searched at once, only data from the same table is stored in each block. In some cases, a large number of block transfers are required, resulting in a slowdown in processing speed.Also, even in cases where data from different tables are allowed to coexist, mutually related data may not be accumulated in the same block. This means that it depends entirely on chance, and in many cases it is distributed and accumulated in separate blocks, which usually requires a large number of bait transfers, resulting in a reduction in processing speed.

[Purpose of the invention]

The purpose of the present invention is to solve such conventional problems, and to enable a database management system, etc., when defining a table for data accumulation, the user only needs to provide the corresponding number of mutually related data between tables. The object of the present invention is to provide a data storage/retrieval method that increases the amount of related data stored in the same block and enables high-speed retrieval.

[Summary of the invention]

In order to achieve the above object, the present invention stores a plurality of types of table-type data on an external storage device, and when data between different tables is related to each other, a means for searching the related data is provided. In a computer system having a computer system, a table for specifying the number of correspondences between mutually related data is provided on the external storage device, and by specifying the number of correspondences between the related data in the table before data is accumulated, the correspondence can be determined. The method is characterized in that an area corresponding to the number of blocks is set, the related data is stored in the area as the same block data, and the related data is searched block by block.

[Embodiments of the invention]

Embodiments of the present invention will be described below with reference to the drawings.

FIG. 1 is an explanatory diagram of a data storage/retrieval method showing an embodiment of the present invention, FIG. 1(a) shows an example of data before data storage, and FIG. 1(b) shows an example in which data is stored in an external storage device.

In FIG. 1(a), 110 is data to be stored in the external storage device, and there are three tables (11
4,115,116). 111, 112 and 1
13 is a data item name, which indicates a work name and work content, a work name and material name, and a material name and material content, respectively.

117 to 127 indicate the data themselves. Reference numerals 131 and 132 are tables indicating the number of correspondences between tables. 1 to 3 in the table 131 is one data in the work table 114, for example, work 1 x , Work 1/Material 2 (120), and Work 1/Quality 3 (121), and the 2:1 of table 132 corresponds to 1 work material related table (11).
The two data in 5), Saku1・Shi1 (119) and Saku2・Shi1 (122), correspond to one data, Saku1・AAA (125) in the data table (116). The number of correspondences between tables, such as 1 to 3 and 2 to 1, which indicates that there is a data ratio between tables is called an inter-table data ratio.

In FIG. 1(b), 210 indicates the structure of data when data 110 is stored in an external storage device. Work table 1142 Data in the three tables 115 and 116, which are related to each other, are data 117 to 127, that is, two items in the work table (114), and the work data relationship table (114). 115), and the data table (116)
), which constitute one set of related data. 1 to 3 as shown in table 131. The 2 to 1 shown in table 132 indicates data that is related to each other.If the work material relationship table (115) has 6 common items, 1 to 3 becomes 2 to 6°, and 2 to 1 becomes 6 to 3. Rekaru from becoming. 211 indicates a block in which this set of data is stored, 212 indicates two data items in the work table (114), and 213 indicates the work material relationship table (115).
Table 13 shows that 6 data items inside, 214 indicates that 3 data items in the data table (116) have been accumulated.
The 1:3 and 2:1 relationships indicated by 1 and 132 are the same for other data.

215 indicates a block in which a set of other data is stored.In this way, by storing data as shown in data 210, 1 hydraulic formula can search for materials related to a certain work, and perform a search for materials related to a certain task. This makes it possible to perform search processing, such as searching for work to be done, with a single block transfer.

This embodiment will be explained in detail below with reference to FIGS. 2 to 12.

FIG. 2 is a block diagram of the configuration of a computer system to which the present invention is applied.

This computer system includes an external storage device 201, a card reader 202. Terminal device 203. Channel 204, CPU
205. It consists of a main memory device 206, the main memory device 2
06 includes a database management system (hereinafter referred to as DBM).
(abbreviated as S)207. A buffer area 208 is provided.

In such a configuration, when the inter-table data ratio is passed from the terminal device 203 to the DBMS 207, the DBMS
207 arranges the passed data in a storage format in the external storage device 201 in the buffer area 208, transfers it to the external storage device 201, and writes it.

FIG. 3 shows the storage format of the inter-table data ratio in the external storage device 201 of FIG. Here, 301 is the number of pairs of mutually related tables, 302 is the table name, and 303 is the value of the data ratio between tables. Figure 4 shows an example of storing data ratios between tables according to this storage format (see Figure 1 for data in the format). ) and the table called Work Materials Relations (413) are 1:3 (indicated by 412 and 414), and the data between the tables called Work Materials Relations (415) and Materials (417) is 1:3 (indicated by 412 and 414). It shows that the ratio is 2:1 (indicated by 416 and 418).

When the data ratio between tables has been accumulated, 1) BM
S207 determines the storage classification for each table for the data storage block.

FIGS. 5(a) and 5(b) show the external storage device 201 in FIG.
It is a diagram showing the above data accumulation structure.

This shows an example of the intra-block accumulation division determined according to the inter-table data ratio of the three tables in FIG.

In FIG. 5(a), 501 indicates the table name, and 502 and 503 indicate the start address and address of the table storage area within the block.

In this example, the table called 1 work has the first address in the block as 100. The content shows that the final address is 299. These contents indicated by block 500 are information regarding the table, and these information are also stored in the external storage device 201.

In FIG. 5(b), 510 is an example of a block in which storage classification has been performed for data storage.

511 is the block number of the block, 512゜514
．． 516 is the name of the table to be stored in the block, 513, 515, and 517 are the empty start addresses of the storage area for each table, and the areas 521 and 522 are the areas allocated to the work table (address 100). to address 299), 5
The areas indicated by 23 and 524 are the areas assigned to the work material related table (addresses 300 to 899), and the areas indicated by 525 and 526 are the areas assigned to the material table (addresses 900 to 1199). The three areas are.

According to the inter-table data ratio in the example shown in Figure 1,
Moreover, the area is allocated assuming that the data in each table is the same size.

2 for the three tables related to work materials and materials
The ratio is 6 to 3.

FIG. 6 is a processing flowchart showing the data accumulation procedure according to the data accumulation structure shown in FIG. 5, and FIG. FIG.

The processing flowchart shown in FIG. 6 will be explained below.

First, data for one operation is sequentially imported (step 601),
Accumulation starts from address 100 of the allocated block and is accumulated in the area up to address 299. If the area is insufficient, another block is prepared and accumulated using the same method. Further, if the area is insufficient, preparing another block is repeated (step 602). Next, data related to work materials is sequentially imported (step 6o3). Search for a block in which the work name of the data about the work and the work name of the data related to work materials are the same, and start accumulating from address 300 in that block until address 89.
Data is accumulated in areas up to 9. When the area is insufficient, the same process as for work data is performed (step 604). Furthermore, the data of the materials are sequentially imported (step 605).

Search for a block where the data base for data related to work materials and the data base for data on materials are the same, and if there are multiple such blocks, select the first block, and start accumulation from address 900 in that block. 1
Accumulates in areas up to 199. When the area is insufficient, the same processing as in the case of work data is performed (step 606).

When data 110 having the structure as shown in FIG. 1 is input from the terminal device 203 or the card reader 202 when the storage is divided into blocks as shown in FIG. 5, the flowchart shown in FIG. According to the storage process, the data are stored in a block as shown in FIG.

When the data 701 to 705 are accumulated as shown in FIG. It depends on the structure of the correspondence between them.

Further, #OO1, 8002 indicates the number of one set of accumulated blocks.

FIG. 8 shows A (803), B (804), and C
(805), and X (823
), Y (824), and Z (825), the inter-table data ratio is 1:2 (8
06,826) and 2:1 (807,827). Lines indicated by and 829 indicate the correspondence between data between tables.

In the case of three tables A, B, and C, mutually related data sets can be separated as shown in frames 801 and 802, but in the case of three tables x, y, and Z, , mutually related data sets cannot be separated. Therefore, A.

In the case of tables like B and C, a set of mutually related data covers a wide range, so it is difficult to store them in one block. This is not the case when the smallest set of data is constructed, such as A, B, and C, or when the correspondence relationship between data is extremely complex, such as X, Y, and Z.
In the case of an intermediate correspondence relationship, a certain amount of a set of mutually related data will fit into one block, and overflowing data will be stored in another block. The present invention is particularly effective for tables such as A, B, and C.

As a method for quickly retrieving data in a block as shown in FIG. 7, a tree-structured index is usually used. This is shown in the following Figure 9.

FIG. 9 is a diagram showing an index structure used for data search according to the present invention. This is an example of an index used when it is desired to search for data in a work table using the work name as a key.

In FIG. 9, 900 is an area where work names and information for creating a tree structure are stored, and 950 is an area where work names and block numbers of blocks containing data of the work names are stored. , these are called records.

901,904°909, and 951 are record numbers, 902°905.907,910,912,914.
and 952 is the work name, 903,906,908゜91
1.913. and 915 indicates the record number to the next record in the tree structure, 953 indicates that there is only one block containing the work name Work 1, and 954 indicates that the block is #001. .

FIG. 10 is an example of a processing flowchart showing a data search procedure using the index shown in FIG.

The processing flowchart shown in FIG. 10 will be explained below.

(i) Data including the value of work 1 is accumulated using the work name index for the work table, resulting in 1.6 blocks.

Learn about ivy (step 1001).

(ii) From the work name index for the work''!J material relation table, the block in which data including the value of work 1 is stored is determined (step 1002).

(ni) As a result of processing steps 1001 and 1002, the required block is read from the external storage device 201 into the main memory bag PI 206 (step 1003).

(Soup) Extract the data including work table Karasaku 1 from the read block. Further, the data including the work material related table 1 is extracted from the read block, and the name of the material contained in the data is known. The block in which data including the material name is stored is known using the material index for the material table (step 1004).

(v) As a result of processing step 1004, the required block is read from the external storage device 201 to the main storage device 206. However, the block to be read is step 1003
If it has already been read in, it is used (step 1005).

(vi) Retrieve necessary data from the material table in the read block, that is, data corresponding to the material name obtained in processing step 1004 (step 1006).

For example, when searching for the work name Work 1 using the index in Figure 9, first look at the record @01, and since Work 1 is smaller than Work 60 (902) (here, work x (The XX part represents the size relationship of the work name), looking at the record @02 (903), work 1 is smaller than work 10 (9Q 5), so the record @03 (JO6) You will see. Next, when you look at the @03 record, there is work 1 (910), so you will see @04 (911), and the data including work 1 from the @04 record is in the block #001 (954). This means that you will know that it has been accumulated. The record shown in FIG. 9 is normally stored in the external storage device 20.
1, but during operation, some or all of them may be made to reside in the main storage device 206 in order to speed up processing. Indexes are created for data item names to connect mutually related data between tables. In the case of three tables: work, work material related, and material, an index is created for the work name 1 of the work table, the work name and material name of the work material related table, and the material name of the table. For example, if it is desired to search for data related to each other in the three tables of work 9, work material relations, and materials using work 1 as a key, the search is performed according to the flowchart shown in FIG. 10. Process step 100 of FIG.
3 and processing step 1005, the external storage device 20
1 to the main storage device 206, but
If mutually related data are included in the same block as shown in FIG. 7, reading is performed only once. However, as explained in the lower diagram of FIG. 8, if data cannot be grouped together within a range of mutually related data, it is necessary to read many blocks. That is, the present invention
The correspondence between data as shown in the upper diagram of FIG. This is effective when there is very little data overflowing the set.

In FIGS. 3 to 10, the explanation has been given using three tables as an example, but the present method can be implemented in a similar manner for other tables as well. In Figure 11 there are two tables,
Figure 12 shows an example for the case of four tables.
In FIG. 11, when the inter-table data ratio of two tables I (1101) and J (1102) is 1:2 (table 1105), mutually related data sets are separated into 1103 and 1104. It shows the situation. In Figure 12, P (1201), Q (1
202) , R (1203) , and S (1204
), and the inter-table data ratio is 1:2 between P and Q (table 1207), 2:1 between Q and R (table 120g), and between R and S. This shows that the ratio is 2:1 (table 1209). Then, by standardizing the number of correspondences in the R table to 2, P, Q.

1205 and 12 are data sets that are related to each other, with the ratio between R and S being 2:4:1.
It is 06. The data shown in Figures 11 and 12 can be processed at high speed by storing a set of mutually related data in one block in the same manner as explained in Figures 3 to 10. It is possible to search.

In this way, according to this embodiment, the DBMS has a function of accumulating mutually related data of multiple types of tables in one block, and it is possible to execute search processing at high speed. . In conventional DBMS, (i
) data from different types of tables is stored in separate blocks, or (ii) even if there is a function to store data from different types of tables into the same block, data that is related to each other is stored in the same block. Since there is no control function to do this, the data will be distributed and accumulated in several blocks. On the other hand, since the present method has a function of accumulating mutually related data in the same block, it is estimated that the number of block transfers will be reduced to a fraction of that of the conventional method. For index processing, conventional DBM
S index processing.

Normally, it is designed to be the same level or lower than the data transfer processing, so this method, which reduces the data transfer processing to a fraction of the cost, can improve performance by more than twice as much as the conventional method. .

〔Effect of the invention〕

As explained above, according to the present invention, in a database management system or the like, when defining a table for data accumulation, the user can simply specify the number of correspondences of mutually related data across tables, and the data can be stored in the same block. It is possible to increase the accumulation of related data, and it becomes possible to perform high-speed searches when searching.

[Brief explanation of the drawing]

FIG. 1 is an explanatory diagram of a data storage/retrieval method showing an embodiment of the present invention, FIG. 2 is a block diagram showing a configuration of a computer system to which the present invention is applied, and FIG. 3 is an external storage device shown in FIG. 2. Figure 4 is a diagram showing an example of data according to the storage format of Figure 3, and Figure 5 is the data storage structure on the external storage device of Figure 2. Figure 6 is a flowchart showing the data accumulation procedure according to the data accumulation structure in Figure 5, and Figure 7 is a flow chart showing the data accumulation procedure in accordance with the data accumulation structure shown in Figures 5 and 6. Diagram showing an example of data, No. 8
The figure is an explanatory diagram showing the correspondence between data between tables to which the present invention is applied, Figure 9 is a diagram showing the index structure used for data search of the present invention, and Figure 10 is the data using the index in Figure 9. An example of a flowchart showing a search procedure, and FIGS. 11 and 12 are explanatory diagrams showing various types of data that can be handled by the present invention. 131.132... Number of correspondences between tables, 21
2.213,214...Data accumulated according to the ratio of the number of correspondences between tables. Atsushi 1 Figure (0L) +10 (b) 纂 ZI￥l 類 3 fig ￥ J 4 纂 5 Concave (＾) (b) ■ 口 口'fJ 7 子Atsushi F3 fig'fJq fig. 10 zutomi 11 zuatsu Figure 12

Claims (1)

[Claims] 1. In a computer system having means for accumulating a plurality of types of table-type data on an external storage device and searching for the related data when data between different tables is related to each other, the external storage device A table for specifying the number of correspondences between mutually related data is provided above, and by specifying the number of correspondences between the related data in the table before data is accumulated, an area corresponding to the number of correspondences is set. , data accumulation characterized in that data is accumulated in the area as the related data as the same block data, and related data search is performed in block units;
Search method.