JP2000222434A - Combination retrieval method - Google Patents

Abstract

PROBLEM TO BE SOLVED: To speed up the retrieval of data obtained by combining plural tables while using a field different from a combination field as a retrieving condition by previously preparing an index having the storing position information of data in plural tables for every key value of the combination field. SOLUTION: The method for previously determining a field to be a retrieval condition and a field for combining plural tables, previously preparing an index having the key value of a field to be combined for every key value of the filed to be the retrieving condition and having the storing position information of data in plural tables having a combination key value in each key value of the field to be combined and using the index in the combination retrieval is adopted.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a retrieval method for joining a plurality of tables, and more particularly to a retrieval method when a field used as a retrieval condition and a field for joining a plurality of tables are different.

[0002]

2. Description of the Related Art In a conventional relational database system, as a method of performing a search by connecting a plurality of tables, as disclosed in Japanese Patent Application Laid-Open No. 2-93766, a key value of a field connecting a plurality of tables is used. There is a method in which an index having storage position information of data of a plurality of tables having a key value of the field to be joined is prepared, and the joining process is performed by using the index.

[0003]

In the above-described conventional join search method, when a key value of a field different from a field joining a plurality of tables is used as a search condition, the search of the key value of the search condition is speeded up. It is necessary to create another index in the field used as the search condition, and by using the index prepared in the field used as the search condition, extracting the data having the key value of the search condition, After determining the key value of
By using an index having data storage location information of a plurality of tables from the join key value, the storage location of the data of the plurality of tables is determined.
We had to search for the book index.

An object of the present invention is to provide a combined search in which a field used as a search condition and a field connecting a plurality of tables have a fixed form. In addition, an index having data storage position information of a plurality of tables for each key value of a field to be joined is prepared, and a join search is performed using only the index. An object of the present invention is to speed up a joint search when fields used as conditions are different.

[0005]

In order to solve the above-mentioned object, a field to be used as a search condition and a field to be connected to a plurality of tables are determined in advance, and a field to be connected to each key value of the field to be used as a search condition is determined. An index having a key value and having storage location information of data of a plurality of tables having a join key value for each key value of a field to be joined is prepared, and the index is used at the time of join search. .

[0006]

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

FIG. 1 shows an online system configuration including a relational database system according to an embodiment of the present invention.

In FIG. 1, an online terminal 11 used for online processing and an external storage device 13 for storing a database 12 are connected to the computer 10. The computer 10 includes an online management program 14 for performing transaction control based on an input from the terminal 11 and a business program 1 for performing transaction processing.
5. In response to a request from the business program 15, a database management program 16 for storing and retrieving data from and to an external storage device 13 in which the database 12 is stored is connected.

FIG. 2 shows an example of the configuration of the database 12. The data storage area 2 stores data 21.
2, a binding index 23 for managing a joining relationship between a plurality of tables, a restriction index 25 for managing a key value of a field for joining a plurality of tables for each key value of a field serving as a search condition, and a plurality of A join key pointer unit 27 for managing storage position information of the data 21 of the plurality of tables with respect to the key value of the field joining the tables.

The join index 23 has a tree structure.
It has an entry 24 consisting of a combined key value and storage location information of the combined key pointer unit 27 having the combined key value.

FIG. 3 shows the format of the entry 24 of the binding index 23.

The join index 23 is an index for managing data having the same key value of the field to be joined. Created to maintain the join relationship between data in multiple tables. In the case of a join search using the key value of the join field as a search condition, the join index 23 is used.

The restriction index 25 has a tree structure.
A key value of a field serving as a search condition, a connection key value of a connection field corresponding to the key value of the field serving as a search condition, storage location information of all data 21 having a key value of the field serving as a search condition, It has an entry 26 consisting of storage position information of a combined key pointer section 27 having a key value.

FIG. 4 shows the format of the entry 26 of the restriction index 25.

The restriction index 25 is the combination index 2
3 is an index for managing a set of data combined by using a field different from the field to be combined as a key. A restriction index 25 is used at the time of a search using a field different from the field to be joined as a search condition and a join field as a join condition.

The binding key pointer section 27 is composed of an entry 28 consisting of storage location information of data 21 of a plurality of tables having binding key values.

FIG. 5 shows the format of the entry 28 of the combined key pointer section 27.

The joining key pointer section 27 is a part shared by the joining index 23 and the limiting index 25. By sharing the joining key pointer section 27 with the joining index 23 and the limiting index 25, localization of the update portion and reduction of the index capacity are achieved. The purpose is. When the combined index 23 and the restricted index 25 are used, the combined key pointer unit 27 is used.

FIG. 6 shows a table structure for performing a join search which is an object of the present invention.

FIG. 7 shows the table structure shown in FIG. 6, in which the merchandise table and the sales table are joined by the value of the merchandise number using the branch number of the sales table as a search condition and the entry of the restricted index 25 according to the present invention. 3 shows the storage state of the entry of the key pointer unit 27 and the data 21 of each table.

A method of searching for a data storage position when a search is performed to retrieve the product name of the product handled by the branch number 100 from the sales table and the product table in FIG. 6 will be described as an example.

Entry 2 of the restriction index 25 of the present invention
6 is a key value of a branch number which is a search condition field;
A merchandise number key value which is a joint key value corresponding to the branch number key value, and a joint key pointer unit 2 having the joint key value
7 and the storage position information of the data 21 having the key value of the branch number.

The restriction index 25 is searched using the branch number 100 as a key value. "Branch number, join key value" is "10
The entry 41 of “0, 10” is a corresponding entry,
The storage position 42 of the combined key pointer unit 27 having the combined key value “10” is determined.

The combined key value "1" of the combined key pointer 27
Since the storage position 44 of the data 21 of the product table of the product number 10 is determined from the entry 43 of “0”, the product name “vacuum cleaner” is obtained from the data 45 of the product table of the product number 10. Similarly, the “branch number, join key value” of the restriction index 25 is “100, 20”, “100, 3”.
Since the entry of "0" also corresponds, the product name "refrigerator" and "television" can be obtained by the above method.

According to the above-described processing, the key value of the field serving as the search condition is searched from the restricted index 25, so that only the target data can be extracted without searching other indexes or extracting unnecessary data. ,
Processing time can be reduced.

FIG. 8 is obtained by adding data “200, Kansai, 20, 140” 61 to the sales table of FIG.

FIG. 9 shows an entry of the combined index 23 for the product table and the sales table shown in FIG.
Entry of restriction index 25, binding key pointer part 2
7 shows the storage state of the entry 21 and the data 21 of each table.

The data "20" is stored in the sales table shown in FIG.
[0, Kansai, 20, 140] 61 will be described as an example of how to change the restriction index 25 and the binding key pointer unit 27. Data “20” in the sales table
When "0, Kansai, 0, 140" 61 is added, since the key value of the product number, which is the binding field of the added data 61, is "20", the binding key value "20" exists in the binding index 23. Or search. By searching the join index 23 for the presence or absence of a join key value, it is not necessary to search for a join key value for all entries of the restricted index 25, and the update process is shortened.

The join key value "20" is set in the join index 23.
Since the entry 62 exists, the binding key pointer unit 2
The storage position 63 of the seventh entry 64 is determined. The storage position information 65 of the additional data 61 is set in the entry 64 of the connection key value “20” of the connection key pointer unit 27. The entry 6 corresponding to the additional data 61 is stored in the restriction index 25.
6 is added, and the storage position information 67 of the additional data 61 and the storage position information 68 of the entry 66 of the combined key pointer unit 27 are added.
Is set in the entry 66, the update of the restriction index 25 is completed.

As described above, each data 2 of a plurality of tables
One combined field has the storage position information of the data 21 having the same key value as the combined key pointer unit 27, and the combined index 23 and the restricted index 25 each have the storage position information of the corresponding entry of the combined key pointer unit 27. By sharing the storage position information of each data 21 of a plurality of tables and localizing the update location, the processing is simplified and the update processing is shortened.

FIG. 10 is obtained by adding data “40, washing machine, 63000” 81 to the product table of FIG.

FIG. 11 shows the storage state of the entry of the combined index 23, the entry of the restricted index 25, the entry of the combined key pointer unit 27, and the data 21 of each table for the product table and the sales table shown in FIG. It is.

A method of changing the combination index 23 and the combination key pointer 27 will be described by taking as an example a case where data "40, washing machine, 63000" 81 is added to the commodity table of FIG.

The data "40, washing machine, 6
When "3000" 81 is added, the added data 81
Since the key value of the product number, which is the combination field of "", is "40", it is searched for the combination key value "40" in the combination index 23. Since there is no entry of the binding key value “40” in the binding index 23, an entry 82 of the binding key value “40” is added to the binding key pointer unit 27, and the storage location information 83 that is the storage location information of the additional data 81 is added. Set in entry 82. By adding an entry 84 of the join key value “40” to the join index 23 and setting the storage position information 85 that is the storage position information of the entry 82 of the join key value “40” of the join key pointer unit 27 in the entry 84. The update of the join index 23 is completed.
Since there is no change in the binding relationship corresponding to the key value serving as a search condition, only the entry is added to the binding key pointer unit 27, and the restriction index 25 is not updated.

[0035]

As described above, according to the combined search method of the present invention, when performing a search by combining a plurality of data,
Since the storage positions of a plurality of tables can be determined by searching only the restricted index that manages the join relationship for each key value of a field serving as a search condition, the join search performance is improved.

[Brief description of the drawings]

FIG. 1 is a diagram showing a configuration example of an online system having a database according to the present invention.

FIG. 2 is a diagram showing a configuration example of a database to which the present invention is applied.

FIG. 3 is a diagram showing a format of an entry of a combined index.

FIG. 4 is a diagram showing a format of a restricted index entry.

FIG. 5 is a diagram showing a format of an entry of a combined key pointer unit.

FIG. 6 is a diagram showing an example of a table structure of a database to which the present invention is applied.

FIG. 7 is a view showing a storage state of actual data in a database.

FIG. 8 is a diagram showing an example of a table structure of a database to which the present invention is applied.

FIG. 9 is a view showing the actual data storage state of a database when data is added.

FIG. 10 is a diagram showing an example of a table structure of a database to which the present invention is applied.

FIG. 11 is a diagram showing a storage state of actual data in a database when data is added.

[Explanation of symbols]

11 online terminal, 12 database, 13 external storage device, 14 online management program, 15
Business program, 16 ... database management program,
21: Data, 22: Data storage area, 23: Index storage area, 24: Index entry, 25: Index storage area, 26: Index entry, 27: Combined key pointer section, 28: Entry

 ──────────────────────────────────────────────────の Continuing on the front page (72) Inventor Yuki Segata 6-81 Onoe-cho, Naka-ku, Yokohama-shi, Kanagawa Prefecture In-house Hitachi Software Engineering Co., Ltd. (72) Mamoru Ichikawa 6, Onoe-cho, Naka-ku, Yokohama, Kanagawa 81 Hitachi Software Engineering Co., Ltd. In-house F-term (reference) 5B075 ND03 ND36 NK02 NK04 NK44 NK54 PP02 PP12 PP22 PQ02 PQ15 PQ46 QR01 QT06 UU40

Claims (1)

[Claims] 1. A search method in a relational database system, in which a search is performed by joining a plurality of tables to search for data, wherein a key value of a field different from a joining field joining the plurality of tables is used as a search condition. A key value a of a field as a search condition, a key value b of a combined field corresponding to the key value a, storage position information of all data having the key value a, and a data value of each data of the plurality of tables having the key value b A join search method using an index having storage position information.