JP2001282829A - Device for and method of retrieval of database - Google Patents

Abstract

PROBLEM TO BE SOLVED: To decrease feed of data transfer about a device and method for retrieval of data in distributed database system which is distributed into plural computers. SOLUTION: A locus of database control 11 in a computer 1 sends a retrieved document that data item is converted into data item identifier function, A locus of database control 11 in a computer 2 converts data value contained in results of retrieval to data item identifier at a locus of generating data identifier 15, and sends a reply to a computer 1, A locus of database control 11 in a computer 1 generates a result of retrieval by converting data item identifier to data value at a locus of data item value restoration 14.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a data retrieval method and apparatus for a distributed database system in which data is distributed among a plurality of computers, and to reduce the data transfer amount.

[0002]

2. Description of the Related Art FIG. 10 is a diagram showing a configuration of a data search device in a conventional distributed database system. In the conventional distributed database system, when an SQL statement 30a, which is a database query language, is issued from the user program 10 on the computer 1 to the database control system 19, the database control system 19 refers to the database definition information 16a. Then, from the issued SQL statement, an SQL statement 40 executable by the computer 2 having the data is generated, and the SQL statement 40 is transmitted to the computer 2 having the data.

In this case, if the data specified in the SQL statement 30a is distributed and stored in a plurality of computers, the computer 1 generates an SQL statement for searching data on each computer, and Send to computer.

A database control system 1 on a computer 2
9 executes a search on the data 18a according to the SQL sentence 40, and transmits the search result 41 to the computer 1.

The computer 1 receives the database search result for the issued SQL statement from all the computers, and returns a final result 39a of the collected data to the user program 10.

As described above, in a distributed database system in which data exists in a plurality of computers and the computers are connected via a network, when the amount of data transmitted and received between the computers increases, it takes time for data transfer, and There is a problem that the response is reduced.

As means for solving this problem, Japanese Patent Laid-Open No.
At -301578, instead of sending an SQL statement,
A method of transmitting a transmission ID to reduce the amount of data transmitted between computers is disclosed. Hereinafter, JP-A-6
The operation at −301578 will be briefly described with reference to the drawings.

FIG. 11 is a block diagram of a data search apparatus disclosed in Japanese Patent Laid-Open No. 6-301578. The data search apparatus includes a client system 102 having an application program 101 for accessing a database in a database operation language (hereinafter, referred to as DML), a conversion table 104, a client DML conversion unit 105, and a client DML storage unit 106, and a DML execution. The server 103 includes a unit 107, a database 108, a DML storage file 109, a server DML conversion unit 110, and a server DML storage unit 111.

[0009] Each time a DML is issued from the application program 101, the client system 102 creates a conversion table 104 that associates the program name with the DML and a uniquely created transmission ID. With reference to the conversion table 104, the shorter one of the DML and the transmission ID is transmitted to the server 103.

[0010] The server 103 stores the correspondence record between the transmission ID and the DML in the DML storage file 109,
Upon receiving the transmission ID, the server DML conversion means 110 converts the data into DML with reference to the DML storage file 109,
The DML is accessed by accessing the database 108, and the result is returned to the client system.

As described above, Japanese Patent Laid-Open No. Hei 6-301578 discloses that the shorter of the DML and the transmission ID is transmitted to the server, thereby reducing the data transfer amount between computers in the distributed database system. I have.

However, in the above-described method, since the database search result is transmitted as it is, when the data amount of the database search result is large, it takes time to transfer the data, and the response of the entire system is improved. It is not possible. Such a specific example is shown below.

In FIG. 10, the SQL statement issued from the user program 10 is for summing up the total sales amount for each product shown in FIG. 12, and the structure of the sales statement 1 described in the SQL statement is shown in FIG. 13, the contents returned from the computer 2 to the computer 1 as a result of searching the database are in the format shown in FIG. 14, and the value of the product code and the corresponding total sales amount are the number of types of the product code. Only returned.

Therefore, when there are many types of product codes, the amount of data transferred between computers increases, and this data transfer takes time.

[0015]

SUMMARY OF THE INVENTION In view of the above-mentioned problems, the present invention improves the response as a system by reducing the data amount when transmitting a database search result to another computer via a network. The purpose is to:

[0016]

A database search device according to the present invention includes a search execution computer having a database for storing data values included in data items, and a search request for connecting to the search execution computer and requesting a search. A search request input unit for specifying a data item and inputting a search request requesting a search result including a data value included in the data item. , Based on the search request entered,
A search statement generation unit that specifies a data item, generates a search statement including a data item identifier function that requests to convert a data value included in the specified data item into a data item value identifier, and transmits the generated search statement A search sentence receiving unit for receiving the search sentence, and a data value included in the data item specified by the data identifier function included in the received search sentence. A search execution unit that searches a database and obtains a search result including the searched data value, a data value conversion unit that converts a data value included in the obtained search result into a data item identifier, and a converted data item identifier And a search result transmitting unit that transmits a search result, wherein the search requesting computer further comprises: a search result receiving unit that receives the search result;
It is characterized by having a data item identifier conversion unit for converting a data item identifier included in the received search result into a data value, and a search result output unit for outputting a search result including the converted data value.

[0017] The data item identifier is characterized in that the data size is smaller than the data value.

The search requesting computer and the search execution computer are connected via a network, and the search statement generation unit specifies the search execution computer based on the data item specified in the search request. The search text transmitting unit transmits the search text to the specified search execution computer.

The data value conversion unit has a search execution side classification information storage unit that stores a hierarchical structure for managing data values, and stores the data value in the data item identifier based on the hierarchical structure. The data item identifier conversion unit has a search request side classification information storage unit that stores the same hierarchical structure as the hierarchical structure stored in the search execution side classification information storage unit. The method is characterized in that the data item identifier is converted into the data value based on a structure.

[0020] The database search method according to the present invention comprises:
A database search method using a database search device including a search execution computer having a database for storing data values included in data items and a search requesting computer connected to the search execution computer and requesting a search, The step in which the search requesting computer requests a search,
A search request inputting step of specifying a data item and inputting a search request requesting a search result including a data value included in the data item; and specifying the data item based on the input search request, and specifying the specified data item. A search statement generating step of generating a search statement including a data item identifier function requesting to convert a data value included in the data item value identifier into a data item value identifier;
A search sentence transmitting step of transmitting the generated search sentence, wherein the step of executing a search by the search execution side computer includes a search sentence receiving step of receiving the search sentence, and data included in the received search sentence A search step for retrieving a data value included in the data item specified by the identifier function from the database and obtaining a search result including the searched data value, and a data value included in the obtained search result as a data item identifier. A data value converting step of converting, and a search result transmitting step of transmitting a search result including the converted data item identifier, wherein the search requesting computer acquires the search result, the step of receiving the search result A search result receiving step, a data item identifier conversion step of converting a data item identifier included in the received search result into a data value, and a search result including the converted data value And having a search result output step of outputting.

[0021]

DESCRIPTION OF THE PREFERRED EMBODIMENTS Embodiment 1 Embodiments of the present invention will be described with reference to the drawings.

FIG. 1 is a diagram showing an example of the configuration of a data retrieval device of a distributed database system according to the present invention. The data search device includes two computers, a computer 1 and a computer 2. The computer 1 includes a user program 10, a database control unit 11, a query analysis unit 12, an identifier function generation unit 13, a data item value restoring unit 14, a database definition information 16a, and a classification information 17.
It comprises a database control unit 11, a query analysis unit 12, a data identifier generation unit 15, database definition information 16b, classification information 17, and data 18a.

Here, the classification information 17 is information that defines the classification of a finite number of data items whose types of possible elements are known in advance, and has a structure having one or more hierarchical levels. It handles data values. For example, a product code included in a sales statement can be defined as classification information because possible values are determined. In the case of a product code, the in-product category can be defined as a higher level as shown in FIG. 5 described later.

Next, the operation will be described. When the user program 10 issues an SQL statement 30 a on the computer 1, the database control unit 11 passes the issued SQL statement to the query analysis unit 12.

The query analysis unit 12 analyzes the passed SQL statement 30a and refers to the database definition information 16a. If the data described in the SQL statement does not exist on the computer 1, the query statement 30a Generate a search statement that can be executed on the computer where the data exists. This is because the database definition information 16a on the computer 1 makes it possible to assign a name different from the data name on the computer 2 where the data exists. The search sentence 31a generated here
Is passed to the identifier function generator 13.

In the identifier function generating unit 13, the classification information 17
, And if there is a data item defined as classification information in the selection item in the passed search sentence, a data item identifier function using the data item is generated, and the data item in the search sentence is replaced with the data item Replaced by an identifier function,
The search sentence 32a after the replacement is transmitted to the database control unit 11 again.
Return to

The database control unit 11 transmits a search statement 32a including the data item identifier function to the computer 2 where the data exists.

The computer 2 that has received the search sentence analyzes the search sentence 32a by referring to the database definition information 16b in the query analysis unit 12.

In this case, since the data 18a exists on the computer 2, the database control unit 11 executes a data search process using the analysis result 33a from the query analysis unit 12.

For the data item identifier function 34, the data identifier generation unit 15 generates a data item identifier 35 with reference to the classification information 17 and returns it to the database control unit 11.

The computer 2 transmits a result 36a including the data item identifier to the computer 1 as a result of the transmitted search sentence 32a.

In the computer 1, for the data item identifier 37 included in the result of the search statement, the data item value restoring unit 1
4 refers to the classification information 17, restores to the data item value 38, and replaces the data item identifier of the result 36 a returned from the computer 2 with the actual data item value 38 and outputs the final database search result 39 a to the user program. Return to 10.

Here, the data item identifier will be described. The data item identifier is a value expressed using a hierarchy level number and a serial number in the level, and all the values in the hierarchical structure can be uniquely identified by this expression. FIG.
FIG. 3 is a diagram showing a hierarchical structure of data. Assuming a larger structure, there are about 100,000 kinds of values at the lowest level,
If the number of hierarchy levels is about 10, 4
If 20 bits are provided for Bit and the serial number within the level, all values can be uniquely identified. Therefore, the data item identifier takes a value of 24 bits (3 bytes) here.

The data item identifier for the data item 50c in FIG. 2 is expressed as 0x002 in hexadecimal notation, and
The data item identifier for the data item 1b is expressed as 0x101 in hexadecimal notation.

A description will be given using a specific example. FIG. 3 is a diagram showing an SQL statement issued from the user program in the present embodiment. This SQL statement is for totalizing the total sales amount from “sales statement table 1”. FIG. 4 is a diagram showing the structure of “sales statement table 1”. It is assumed that classification information is defined for “product code” in the data items of the sales statement table 1. FIG. 5 is a diagram showing the structure of the classification information.

FIG. 6 is a diagram showing an example of a search sentence transmitted from the computer 1 to the computer 2. Here, "DimID
“(Product code)” represents a data item identifier function that requests to return a data item identifier for the data item “Product code”.

FIG. 7 is a diagram showing a search result transmitted from the computer 2 to the computer 1 as a result of executing the search sentence of FIG.

Since a code system such as a product code generally has a unique meaning for each digit in many cases, all values that can be represented by the number of digits representing the product code are used as the product code. Not necessarily. For example, even when a character string of 10 digits (10 Bytes) is given as a product code, the type of the product code actually used may be about 100,000, which is much smaller.

On the other hand, 100,000 kinds of values can be expressed in 3 bytes. Therefore, if data of 3 bytes is generated as a data item identifier instead of the product code, the data transfer amount of 7 bytes per product code can be reduced. 700,000B for 100,000 kinds of product codes
yte, that is, a data transfer amount of about 684 Kbytes can be reduced.

Embodiment 2 FIG. 8 is a configuration diagram of a data search device of a distributed database system including four computers, a computer 1, a computer 3, a computer 4, and a computer 5.

FIG. 9 is a diagram showing an example of an SQL statement issued from the user program 10 in the second embodiment. The structure of “sales statement table 2” included in the SQL statement is as shown in FIG. 15, and the data is distributed and stored in three computers 3, 4 and 5.

The SQL statement shown in FIG. 9 is for obtaining a product whose total sales amount is 100,000 yen or more.
The product code and the total sales amount of each of the three computers 3, 4, and 5 are transmitted to the computer 1 by the number of types of the product code, and the computer 1 combines the results received from the three computers. Total sales amount is 100,000
It is necessary to judge whether it is 0 yen or more.

In the present embodiment, in the computer 1, the search statements 31b, 31c, 31d executable by each of the three computers
Are generated, and the search sentences 32b, 32c, and 32d obtained by converting the product codes into the data item identifier functions are transmitted to the three computers 3, 4, and 5, respectively. The three computers execute a database search in parallel, and transmit the search results 36b, 36c, and 36d including the data item identifier to the computer 1.

In this embodiment, when there are 100,000 kinds of product codes, it is possible to reduce the data transfer amount of about 2 Mbytes with three units.

[0045]

As described above, in the distributed database system according to the present invention in which a data item identifier is generated and returned in a database search result, when there are many types of data items, and when a plurality of computers When the data is stored in a distributed manner, the amount of data transferred between the computers can be reduced, and the response of the system is improved.

When data is distributed to a plurality of computers, it is necessary to finally execute the process of summarizing the results executed by each computer. The results executed by each computer are sorted in the order of the data item identifier. Because it has been generated,
This has the effect that the load of the final tallying process is also improved.

[Brief description of the drawings]

FIG. 1 is a diagram illustrating a configuration example of a data search device of a distributed database system according to the present invention.

FIG. 2 is a diagram showing a hierarchical structure of data.

FIG. 3 is a diagram showing an SQL statement issued from a user program.

FIG. 4 is a diagram showing a structure of “sales statement table 1”.

FIG. 5 is a diagram showing a structure of classification information.

FIG. 6 is a diagram showing an example of a search sentence transmitted from the computer 1 to the computer 2.

FIG. 7 is a diagram showing a search result transmitted from the computer 2 to the computer 1 as a result of executing the search sentence of FIG.

FIG. 8 is a configuration diagram of a data retrieval device of a distributed database system including four computers, a computer 1, a computer 3, a computer 4, and a computer 5.

FIG. 9 is a diagram showing an example of an SQL statement issued from a user program in the second embodiment.

FIG. 10 is a diagram showing a configuration of a data search device in a conventional distributed database system.

FIG. 11 is a configuration diagram of a data search device disclosed in JP-A-6-301578.

FIG. 12 is a diagram showing an SQL statement issued from a user program.

FIG. 13 is a diagram showing a structure of “sales statement table 1”.

FIG. 14 is a diagram showing search results according to the related art.

FIG. 15 is a diagram showing a structure of “sales statement table 2”.

[Explanation of symbols]

1, 2, 3, 4, 5 computer, 10 user program, 11 database control unit, 12 query analysis unit,
13 identifier function generating unit, 14 data item value restoring unit,
15 data identifier generation units, 16a, 16b, 16c,
16d, 16e database definition information, 17 classification information, 18a, 18b, 18c, 18d data, 19
Database control system, 30a, 30b SQL
Statement, 31a, 31b, 31c, 31d Generated SQ
L sentence, 32a, 32b, 32c, 32d Search sentence including data item identifier function, 33a, 33b, 33c, 3
3d search statement analysis result, 34 data item identifier function,
35 data item identifiers, 36a, 36b, 36c, 3
6d Search results including data item identifiers, 37 data item identifiers, 38 data item values, 39a, 39b
Search results, 40 SQL sentences, 41 Data search results, 5
0a, 50b, 50c, 50d, 50e, 50f, 50
g, 50h First hierarchical level item of classification information, 51
a, 51b, 51c items of the second hierarchy level of the classification information, 52a items of the third hierarchy level of the classification information, 101
Application program, 102 client system, 1
03 server, 104 conversion table, 105 client DML conversion means, 106 client DML storage means, 107 DML execution means, 108 database,
109 DML storage file, 110 server DML conversion means, 111 server DML storage means, 112 DM
L, 113 DML or transmission ID.

Claims (5)

[Claims] 1. A database search device comprising: a search execution computer having a database for storing data values included in data items; and a search requesting computer connected to the search execution computer and requesting a search. The search requesting computer specifies a data item and specifies a data item based on the input search request, and a search request input unit for inputting a search request for a search result including a data value included in the data item. And a search statement generation unit for generating a search statement including a data item identifier function for requesting to convert a data value included in the specified data item into a data item value identifier, and transmitting a search statement for transmitting the generated search statement A search statement receiving unit for receiving the search statement, and a data identifier function included in the received search statement. A search execution unit that searches a database for data values included in data items and obtains search results including the searched data values, and a data value that converts data values included in the obtained search results into data item identifiers A search unit that transmits a search result including the converted data item identifier; the search requesting computer further includes a search result receiving unit that receives the search result; and a received search result. A data item identifier conversion unit for converting a data item identifier included in the data item into a data value, and a search result output unit for outputting a search result including the converted data value. 2. The data item identifier according to claim 1, wherein the data size is smaller than the data value.
Database search device as described. 3. The search requesting computer and the search execution side computer are connected via a network, and the search statement generation unit controls the search execution side computer based on a data item specified by a search request. 2. The database search device according to claim 1, wherein the search text transmitting unit transmits the search text to the specified search execution computer. 4. The data value conversion unit includes a search execution side classification information storage unit that stores a hierarchical structure for managing data values, and stores the data value based on the hierarchical structure. The data item identifier conversion unit has a search request side classification information storage unit that stores the same hierarchical structure as the hierarchical structure stored in the search execution side classification information storage unit. 2. The database search device according to claim 1, wherein the data item identifier is converted into the data value based on a hierarchical structure. 5. A database search using a database search device including a search execution computer having a database for storing data values included in data items, and a search requesting computer connected to the search execution computer and requesting a search. The method, wherein the step of requesting a search by the search requesting computer comprises: a search request inputting step of inputting a search request specifying a data item and requesting a search result including a data value included in the data item. Generating a search statement including a data item identifier function for requesting to specify a data item based on an input search request and to convert a data value included in the specified data item into a data item value identifier; A search sentence transmitting step of transmitting the generated search sentence, wherein the step of performing a search by the search execution computer comprises: And a search execution step of searching the database for a data value included in the data item specified by the data identifier function included in the received search sentence, and obtaining a search result including the searched data value A data value conversion step of converting a data value included in the obtained search result into a data item identifier; and a search result transmission step of transmitting a search result including the converted data item identifier. A step in which the computer obtains the search result includes: a search result receiving step of receiving the search result; a data item identifier converting step of converting a data item identifier included in the received search result into a data value; A search result output step of outputting a search result including a value.