JP4033207B2 - Database management method - Google Patents

Description

  The present invention relates to a database management system, and more particularly to a query processing method suitable for parallel processing of queries suitable for a relational database management system.

  As the prior art related to the present invention, two points of SQL3 Abstruct Data Type and parallel database system will be described. First, SQL3 Abstruct Data Type will be described. Application of relational databases, mainly SQL database systems, is progressing mainly in office data processing. In addition, an object database has been put into practical use with the purpose of handling data having a complicated structure, which is difficult to handle efficiently in the framework of a conventional relational database.

  On the other hand, research has been conducted on extending relational databases to handle data having a complex structure, and standardization is being promoted in SQL3. In SQL3, user-defined data (type) having a complicated structure called Abstruct Data Type (abstract data type, hereinafter referred to as ADT) can be handled. ADT is a complex approach that incorporates an object-oriented concept that allows multiple types of data called attributes (hereinafter referred to as sub-data) to be concealed by function interfaces and have inheritance relationships between types. Can handle data. Define the type using an ADT definition SQL statement that begins with CREATE TYPE. The defined types can be used for variable declarations, table column definitions, etc., as well as system-defined types such as integer types and character types. Each type makes it possible to create and use data with a complex structure. SQL3 and ADT are described in Andrew E. Wade, Ph.D .: "Object Query Standards", ACM SIGMOD Record, Vol. 25, No. 1, pp87-92, March 1996, and the like. The standardization draft of SQL3 is ISO / IEC JTC1 / SC21 / WG3 DBL-MCI-004, ISO Working Draft Database Language SQL, 1996.

  Next, a parallel database system is described. In a relational database system, it is easy to improve performance by dividing data into a plurality of database processing servers and accessing them in parallel. The demand for such a parallel database system has been increased as the amount of data has increased. The parallel database system is described in DeWitt, D., et.al .: “Parallel Database Systems: The Future of High Performance Database Systems”, CACM, Vol. 35, No. 6, 1992.

  The parallel database system consists of a server that analyzes and compiles a database query from a user application program (hereinafter referred to as UAP) on the host computer (hereinafter referred to as a front-end server), and data. A plurality of servers (referred to as database operation servers) for accessing the disk device and operating data. In the following, for the sake of simplicity, description will be made with a configuration of one host, one front-end server, and a plurality of database operation servers. However, multiple front-end servers can handle multiple queries from one or multiple hosts. Even in this case, each query has a configuration of one host, one front-end server, and a plurality of database operation servers, and can be applied to the following description and the present invention without any problem.

  In general, SQL that is a query to a database (hereinafter referred to as a database query) is often used in a form embedded in a computer language such as C language (hereinafter referred to as a parent language) (hereinafter referred to as a database language). This will be referred to as embedded SQL). Database queries such as searching the database and updating, deleting and inserting data into the database are issued from the host's parent language, the database system parses and executes the query, and returns the results to the host. The host parent language receives the result and uses it for control processing such as condition determination and processing such as substitution and calculation. The present invention can be applied even when a database inquiry including control processing and processing processing is issued as in a stored procedure. (In this case, processing performed on the database operation server side such as search, insertion, update, and deletion processing is sometimes referred to as a database operation statement in the sense that it is separated from processing performed on the front-end server side such as control processing and processing processing. ). Stored procedures are described in Hatsuko Katayama: Mastering Stored Procedures and Triggers, NIKKEI OPEN SYSTEMS, No.2, pp.133-144, 1993.

  A plurality of database queries can be embedded in the parent language, and results can be exchanged via variables in the parent language. The passing of values by variables depends on the processing method of the analysis result of the parent language. For example, if you decide the area for storing the value for each variable and use the value of the variable, you can decide how to bind the variable so that you can see the area.

  An example of creating, transferring, and executing a processing procedure in a general parallel database system internal format using embedded SQL will be described below. UAP control syntax written in embedded SQL performs processing and control on the results of database operations. Database queries are sent one sentence at a time via the network to the front-end server with the UAP. Then, by performing syntax analysis, semantic analysis, optimization, and compilation by the compiler, a processing procedure in an internal format for performing an actual database operation based on the sent database query is created.

  The processing procedure in the internal format is a code that is interpreted and executed by an interpreter or a code in an execution format. Definition information necessary for compiling exists as dictionary information accessible from the front-end server. The created processing procedure is transferred to the database operation server that actually performs the database operation through the network, and is executed by the activation request. The server that actually performs database operations is determined by information related to table partitioning that is normally operated. Information about table partitioning is specified in the table definition and entered into the dictionary.

  The database operation server has a processor and one or more disk devices. By placing the processing procedure in the internal format in the cache of the database operation server, there is an improvement plan for issuing the execution request and using the processing procedure in the cache for the second and subsequent executions. In a parallel database system, there are a plurality of database operation servers, and SQL processing is parallelized. The SQL processing results are exchanged with other database operation servers through the network as necessary, and finally the execution results are returned to the UAP through the front end server's result reception processing with the UAP. Process and control the results. Thereafter, the same processing is repeated for each SQL sentence.

Andrew E. Wade, Ph.D .: "Object Query Standards", ACM SIGMODRecord, Vol.25, No.1, pp87-92, March 1996. ISO / IEC JTC1 / SC21 / WG3 DBL-MCI-004, ISO Working Draft Data base Language SQL, 1996. DeWitt, D., et.al .: "Parallel Database Systems: The Future of High Performance Database Systems", CACM, Vol. 35, No. 6, 1992. Hatsuko Katayama: Mastering Stored Procedures and Triggers, NIKKEI OPEN SYSTEMS, No.2, pp.133-144, 1993.

  When the data that can be handled by a database query is a collection of multiple data (attributes in ADT, hereinafter referred to as sub-data), processing such as search, update, insertion, processing, and control for the data is performed one by one. Can be considered separately, and the case where the entire data, which is a collection of sub data, is processed. Here, the following can be considered as a method of using a query to the database.

  First, the entire data is searched by an inquiry made first. Then, the retrieved data is passed to a later inquiry via the parent language variable. Each sub-data of the data passed in a later inquiry is processed separately. In the case of such a usage method, a later inquiry does not always use all sub-data of the retrieved data. However, when the parallel database system technology described in the prior art is applied to ADT as it is, the data returned as a result of the search query is analyzed and executed from the database operation server with the data. All forwarded to the front-end server.

  If unused sub-data is large-scale data such as LOB data, transferring large-scale unused data from the database operation server to the front-end server will increase the transfer time and increase the time required for inquiries. . An object of the present invention is to reduce the inquiry time by realizing transfer of only data used in the subsequent processing from the database operation server to the front-end server.

  In order to achieve the above object, at the time of a search inquiry to be performed first, only the data location information is returned from the database operation server to the front-end server for data consisting of a plurality of sub-data. The location information includes an address of data on the database operation server and an identifier of the database operation server. The position information is passed to the subsequent query via a variable. In the subsequent inquiry, each sub-data of the data in the database operation server is extracted from the passed position information, the dictionary information of the position of each sub-data in the data, and the sub-data identifier required for the inquiry. Since the position information includes the identifier of the database operation server with data, a data retrieval request can be made to the database operation server with data.

  Further, since the position information includes the address of data on the database operation server, the data can be extracted. Based on the dictionary information of the position of each sub-data in the data and the identifier of the necessary sub-data, the position of the necessary sub-data can be determined from the data and individual sub-data can be extracted. The extracted sub data is returned to the front-end server, and processing using the sub data can be performed.

  Since only the data used in the subsequent processing is transferred from the database operation server to the front-end server, the purpose of reducing the inquiry time is realized when the unused data is large.

  When the processing after using the sub data is an update processing for the data of the previous search processing, the processing procedure in the internal format of the update processing receives the necessary sub data on the database operation server side and performs the update processing. be able to. In this case, since the sub-data transfer from the database operation server to the front-end server is eliminated, the inquiry time is further reduced.

  In addition, the first method is to extract only the position information of the data in the above-described search processing and to extract the sub-data in the subsequent processing. In the first search processing, the entire data including the sub-data is front-mounted. The second method is the method of taking it out to the end server and passing it to the subsequent processing. Compare the cost of each method calculated from the data length of sub-data and the time required for communication, etc. By selecting one of the two methods depending on the presence of long sub-data, etc., a method with a small inquiry time can be selected according to the situation, and the above-described object of reducing the inquiry time is achieved.

  The present invention will be described using a relational database, SQL, which is the mainstream database. Further, the data having a complicated structure handled in the present invention will be described using the SQL3 ADT. However, the present invention can be applied to any database management system in which a database query is embedded in the parent language, data can be exchanged between a plurality of database queries, and data consisting of a collection of a plurality of data can be handled. .

According to the present invention, in processing for data consisting of a plurality of sub data, position information can be transferred and the sub data of the data can be used later.

  Hereinafter, an embodiment of the present invention will be described with reference to the drawings. FIG. 1 is a configuration diagram of an embodiment of the present invention. The database server 12 serves as a front-end for a database inquiry 122, and a plurality of database operation servers 13 serve as operations for the database. It is assumed that the front-end database server 12 and the database operation server 13 are connected by a high-speed interconnection network.

  However, the present invention can be applied to a single processor system as long as a parallel process is assigned as a role of each server, not a parallel database system of a plurality of processors connected by a network.

  The front-end server 12 is assumed to be connected to an external host 11 via a network. However, since the present invention reduces the transfer amount between the front-end server 12 and the database operation server 13, there is an alternative in which the role of the host is taken into the database system side and connected by the internal high-speed network, or the role of the host and the front The present invention can also be applied to an alternative in which end database servers are integrated into one. Even if a series of inquiries is not a host UAP but a stored procedure, it can be handled in the form of a search query for multiple sub-data, passing by variable, and using the sub-data in subsequent queries. The invention can be applied.

  FIG. 1 shows an example of database query analysis and execution. Refer to FIG. 10 described later for analysis of definition statements such as tables and types. Whether it is a database query or a definition statement can be determined by semantic analysis. The analysis 101 includes the determination.

  First, the front-end server 12 analyzes 101 the search query 122a to be performed first, thereby creating a processing procedure 125 in an internal format. The processing procedure in the internal format may be an execution format code or an interpreter code.

  For the execution of the search query 122a, it is assumed that there is no operation on the sub data 132, and since it is a database operation statement 133, the processing procedure 125 in the internal format is transferred 102 to the database operation server 13. The database operation server 13 receives the processing procedure 125 111, executes the processing procedure, and obtains the identifier ID of the database operation server and the data address 112 for the retrieval of data consisting of a plurality of sub data. The search result 126 is transferred 113 to the front end server side. The front-end server side receives the result 103 and returns 105 the received result to the UAP as information 127 to be passed to the subsequent inquiry 122b via a variable.

  Next, the front end server 12 analyzes 101 the inquiry 122b after using the sub data. Since there is a variable to be input, information 127 bound to the variable is passed from the UAP. Further, when the information 127 bound to the variable is the position information 126 of data composed of a plurality of sub data, the offset information 124 for extracting the sub data is acquired from the dictionary 14. Based on the information 127 and 124, a processing procedure in an internal format for acquiring sub data and a processing procedure for an inquiry using the acquired sub data are created.

  In response to the execution of the inquiry 122b, since it is an operation on sub data 131, sub data acquisition 106 is performed. Since the database operation server with the data is known from the identifier of the database operation server in the position information 126 acquired in the previous search process, the sub-data retrieval request is sent to the database operation server 13 together with information 128 necessary for sub-data retrieval. put out. The information 128 necessary for sub data retrieval is the address of data in the database operation server and offset information 124 for retrieving necessary sub data. Which sub-data is necessary can be extracted at the time of analysis 101, and information is embedded in an internal format for acquiring the sub-data.

  When there is a request for taking out the sub data, the database operation server side takes out the sub data from the data address and the information on the offset position of the sub data, and returns the sub data 129 to the requesting front end server 12. In the embodiment, the processing on the database operation server 13 side is represented by a system binding process that receives the data address and sub-data offset information and returns the sub-data. The present invention can be applied to an alternative in which the processing procedure is created, transferred to the database operation server at the time of execution, and executed.

  The front-end server side receives 107 the necessary sub data 129. The place where the sub data is received is pointed from the sub data use place in the processing procedure of the internal format of the process using the sub data which is the main process of the inquiry 122b at the time of the analysis 101. Regardless of whether the subsequent inquiry is a database operation statement 133 without 133, the sub-data can be used in the subsequent execution 104, 112 of the processing procedure in the internal format.

  Since only the position information 126 is acquired by the data search query 122a made of a plurality of sub-data, and only the necessary sub-data 129 is acquired by the query 122b using the sub-data, which is made later. If the data to be processed is large-scale data such as LOB, the inquiry time can be reduced.

  FIG. 2 is an example of data composed of a plurality of sub data. A type definition example 21, a data example 22 created from the type, and a data search query example 23 are described.

  The data type definition includes the name of each sub-data (attribute in ADT) and the type declaration 201. The sub-data type may be a system-defined type or a user-defined type. A function or procedure definition 202 for data of a type and an inheritance relationship between types can be specified, but there is no need to specify them.

  Data consisting of a plurality of sub-data is used for the table definition 22 as with the system-defined type. By inserting data into the table, it is possible to create data 203 consisting of post data, address, and telephone number sub-data 204, like address data.

  The created data can be inquired such as a search 205, 206. 205 is a query for searching for the sub-data zip code of the address data. Reference numeral 206 denotes an inquiry for searching for address data whose sub-data address is address Yokohama. In this way, it is possible to search for sub data as well as for data consisting of a plurality of sub data.

  FIG. 3 shows an example of the position information 126 of FIG. 1 in the present invention. The position information 126 includes an identifier 301 of the database operation server 13 and an address 302 of data in the server 13. Additional information such as a type identifier may be included as information used for control processing in the front-end server 12 or the like. The database operation server identifier 301 may be information that can identify the database operation server that stores the data. The data address 302 may be a real address for data storage or may be represented by a logical address obtained by taking out data from the memory and offsetting from the head address.

  FIG. 4 shows an example of information 127 to be bound to the variables of FIG. 1 in the present invention. FIG. 4 shows a case where search results of data composed of a plurality of sub data are bound. When the search is not for the entire data composed of a plurality of sub-data, the information to be bound to the variable is the data to be searched. The information 127 to be bound to the variable includes the identifier 401 of the database operation server 13 and the data address 402 in the server 13. Additional information such as a type identifier may be included as information used for control processing in the front-end server 12 or the like. The database operation server identifier 401 may be information that can identify the database operation server that stores the data. The data address 402 may be a real address of data storage or may be represented by a logical address obtained by taking out the data from the memory. When the method of selecting whether to place data on the front end side or the back end side according to cost as in the embodiment of FIG. 21, is the data on the front end side or the back end side? Include information that represents in the information that is bound to the variable. On the UAP side of the host, when searching for data including sub-data, it is necessary to prepare an area for the length of the variable area to receive information to be bound to the variable.

  FIG. 5 shows an example of the processing procedure 125 in the internal format of FIG. 1 in the present invention. The processing procedure 125 in the internal format includes a code that is interpreted and executed by an interpreter and information accompanying each code. The information includes various types of information according to processing, such as information on the type and length of data to be extracted, offset information on sub-data, and information on the location where the extracted data is placed. The information includes information on the code to be executed next. It is also possible to divide the code to be executed next by conditional branch information. Information 502 accompanying the sub-data extraction code 501 includes data position information 126 and sub-data offset information to be used. The processing procedure in the internal format is not a code that is interpreted and executed by an interpreter, but can be applied to a code in an executable format. FIG. 5 shows the relationship between the internal-format processing procedure 51 for extracting the sub-data 129 from the position information 126 and the internal-format processing procedure 52 using the sub-data. A place 505 where the sub data 129 extracted by the processing procedure 51 is placed is represented by an offset position 506 on the shared memory, and is put in the information 504 of the code using the sub data in the processing procedure 52. Based on the above information, the sub-data 129 extracted in the processing procedure 51 can be used on the processing procedure 52 side. The internal format processing procedure 51 for extracting sub data and the internal format processing procedure 52 using the sub data may be combined into one processing procedure.

  In the example of FIG. 5, the process of extracting the sub data 129 is interpreted and executed by the code 501a using the sub data information 502 to be extracted, but may be composed of a plurality of codes representing the extraction procedure. In the example of FIG. 5, the processing procedure of the internal format when using the sub-data 129 is used. However, in the processing procedure of the internal format not using the sub-data, the processing procedure 51 for extracting the sub-data and the sub-data 129 are changed. Information on the offset position of the place 505 is not necessary. In the case of the processing procedure of the internal format for extracting the position information 126, the offset position to the area corresponding to the length of the extracted position information is included as information necessary for execution of interpretation.

  FIG. 6 is an example of the offset information 124 of the sub data in FIG. 1 in the present invention. The sub data offset information 124 is created for each piece of data composed of a plurality of sub data. The sub data offset information 124 includes a sub data identifier 601, a data type 602, a data length 603, and an offset position 604. The offset position is an offset from a reference address such as the head of data. If each sub-data is clustered, the offset position of each sub-data can be calculated from the data length 603. Therefore, the data type 602 and the offset position 604 may be omitted. Further, when variable length sub-data exists, an offset position cannot be placed in the dictionary 14. In this case, an alternative to incorporate the offset position into the data 130 can be applied. It is incorporated in the data 130 so that the sub-data identifier 601 can be associated with the offset. For example, when the sub-data identifiers are assigned in order of definition in the data from 1 in order, an offset may be placed on the data 130 in the same order.

  FIG. 7 shows an example of the sub-data extraction information 128 of FIG. 1 in the present invention. The sub-data retrieval information 128 includes data address 302 in the database operation server 13 and offset information 701 of sub-data 129 to be used. The sub-data offset information 701 to be used includes a sub-data identifier 601, a data type 602, a data length 603, and an offset position 604. As the offset information 701 of the sub data 129 to be used, the offset information of the identifier 601 of the sub data 129 to be used is extracted from the offset information 124 of the sub data at the time of analysis 101. An alternative is also applicable in which the offset information 124 of the sub data and the identifier of the sub data 129 to be used are included in the sub data extraction information 128 and the offset information of the sub data 129 to be used is selected on the database operation server 13 side.

  FIG. 8 shows an example of the sub-data 129 of FIG. 1 in the present invention. The sub data 129 is actual data 801 of a system definition type or a user definition type. When the sub-data to be extracted is a user-defined type and the data to be used is sub-data sub-data, the database operation server 13 side includes sub-data sub-data offset information in the sub-data extraction information 701 on the side of the sub-data. Alternatives for retrieving subdata are applicable. Even when the sub-data of the sub-data follows the sub-data, it can be similarly applied by including the offset information in the sub-data extracting information 701.

  FIG. 9 shows an example of data 130 stored in the data operation server of FIG. 1 in the present invention. Data 130 stored in the data operation server is a collection of data 901 for each column. There may be additional information such as offset information from the head for each column of data in order to speed up the extraction of each column. Each column of data includes system-defined type data 902 and user-defined type data 903. The data type of each column is based on a table definition such as CREATE TABLE, and both the system-defined type data 902 and the user-defined type data 903 may appear 0 or more times in any order. However, at least one of them is required.

  FIG. 9A shows a format in which column data composed of a plurality of sub-data is clustered with other column data and embedded in the entire data 130. When sub-data includes variable-length data, an alternative that incorporates an offset 904 of each sub-data can be applied to a column of data having sub-data as shown in FIG. The structure of the data 903b having sub data is the same as the structure of the entire data 130. In FIG. 9 (b), the offset is from the start address of the sub data, but any other form may be used as long as information that can determine the position of the sub data is incorporated from the sub data identifier. There is also an alternative in which data length information is incorporated into variable-length subdata instead of offset information for variable-length subdata. The present invention can be applied if the sub-data can be extracted from the sub-data identifier of the dictionary information in FIG.

  An alternative is also applicable in which column data 903 composed of a plurality of sub-data is stored in an area different from the entire data 130 and only a pointer to the area is stored in the data 130.

  FIG. 10 shows an example of creating the offset information 124 of the sub data. The sub data offset information 124 is created when defining the type of data composed of a plurality of sub data. The front end server 12 analyzes the type definition statement 1001 such as CREATE TYPE 101. The type is examined for each subdata, and the subdata identifier 601, data type 602, data length are determined according to the length determined by the type of type, or in the case of a character string, etc. 603, an offset position 604 is obtained. The sub data identifier 601 may be anything that is associated with the name of the sub data. An alternative to separate the front-end server and the server with the dictionary and performing the analysis with the server with the dictionary is also applicable.

  FIG. 11 shows an example of creating data 130 composed of a plurality of sub data. Data 130 composed of a plurality of sub-data is created at the time of an insertion inquiry. Use table definition information created by a table definition statement such as CREATE TABLE. The table definition information includes the column identifier and data type of each column. As a change from the past, a user-defined type can be used as a data type. FIG. 11 shows an example of an insertion inquiry. The front-end server 12 analyzes the insertion inquiry 1101. The insertion query 1101 includes value data to be inserted into each column. When the value data to be inserted is data composed of a plurality of sub-data, there are a method of specifying the value of each sub-data, a method of creating data and a method of specifying its argument, and the like. In the case of ADT, specify the function to create data and its argument (there may be no argument). When data is created by a function, the analysis result of the function or the like specified in the definition is stored in the dictionary when the type shown in FIG. 10 is defined.

  Based on the analysis 101 of the insertion query 1101, a processing procedure 1102 in an internal format that includes a value to be inserted or a function for creating a value and information as information to be interpreted and executed by an interpreter is created. The information includes the type and length of each column and sub data obtained from the table definition information and sub data offset information. The processing procedure 1102 in the internal format is transferred to the data operation server 13 in which the table to be inserted is stored. The data operation server 13 side receives and executes the processing procedure 1102 in the internal format. The code executed by the interpreter is assembled and stored 1105 in the format of the data 130 from the column / subdata type / length information and the inserted value information, through the creation of the inserted value 1103 and the type conversion 1104. For the sub data of the sub data, the data 130 is assembled using recursive processing or the like. The processing procedure 1102 in the internal format can be applied to the execution format code, not the code that is interpreted and executed by the interpreter.

  FIG. 12 is an explanatory diagram of the processing procedure creation 101 in the internal format of FIG. 1 in the present invention. First, if there is an inquiry 122 and an input by a variable, the binding information 127 is received 1201. The syntax analysis 1202 and the semantic analysis 1203 of the query 122 are performed, and in the process, whether or not the variable sub-data is used is also analyzed. Since there may be a plurality of sub-data used in the inquiry 122, the sub-data on the use side and the sub-data on the extraction side are included in the analysis result information with the same identifier.

  If there is use of variable sub-data 1204, the data position information 126 is extracted 1205 from the variable binding information 1205, the data position information 126, the sub-data offset information 124 in the dictionary, and the sub-data to be used A processing procedure 51 in an internal format for extracting sub-data is created 1206 from the identifier. Next, the processing procedure 52 in the internal format of the query 122 using sub data is created 1207. By giving an offset indicating the same storage location to sub-data with the same identifier, sub-data can be exchanged between the sub-data extracting side 51 and the using side 52.

  If there is no use of variable sub-data, no processing procedure in the internal format for extracting the sub-data is required, and only the processing procedure in the internal format of the query 122 is created 1207. In the case of the database operation server in which the inquiry is performed on the database operation server side, the processing procedure in the internal format is accompanied by information on the database operation server to be executed. The information of the database operation server to be executed is obtained from the information related to the table partition to be operated. Table partition information is specified at the time of table definition and is stored in the dictionary.

  FIG. 13 is a process explanatory diagram of an embodiment of the processing procedure transfer 102 and the processing procedure reception 111 in the internal format of FIG. 1301 is transferred to each database operation server 13 to be executed, and 1302 is transferred 1302 in the internal format. The database operation server 13 side receives 111 the processing procedure 125 in the internal format and executes 112. An alternative is also applicable in which the database operation server returns a reception report of the processing procedure 125 to the front-end server 12, confirms that all have been received, makes an activation request to each database operation server 13, and moves to execution 112. By placing the processing procedure 125 in the internal format in the cache of the database operation server, the second and subsequent executions can also apply an improvement plan that issues an execution request and uses the processing procedure 125 in the cache.

  FIG. 14 is a process explanatory diagram of an embodiment of the processing procedure execution 104 in the internal format of FIG. 1 in the present invention. The interpreter 1401 interprets the code one by one with accompanying information 1402. The next code is extracted from the information 1403 and executed one after another.

  FIG. 15 is a process explanatory diagram of an embodiment of the process procedure execution 112 of FIG. 1 in the present invention. Code execution by the interpreter is the same as in FIG. The type of code handled by the interpreter may be different on the front end server 12 side and the back end server 13 side. An interpreter interprets and executes codes 1501 one by one with accompanying information 1502. The next code to be executed is extracted from the information 1503 and executed one after another. If the code to be executed is retrieval of data consisting of a plurality of sub-data 1504, the identifier 301 and the data address 302 of the database operation server 13 a are acquired, and the position information 126 is stored in an area prepared for the result at the time of analysis. create. The identifier of the database operation server 13 may be prepared as information used by the code for the processing procedure 125 in the internal format. In the case of data search when the data is not composed of a plurality of sub-data, the data itself is taken out in an area prepared for the result at the time of analysis.

  FIG. 16 is a diagram for explaining the processing of the embodiment of the result reception 103 and the result transfer 113 shown in FIG. The result is transferred 1601 from the 102 database operation server 13 that has transferred the processing procedure 125 in the internal format to 1602 when the execution result disappears. An alternative of transferring the results every plural numbers can also be applied. The results from each database operation server 13 are extracted 1603 in the order in which the results are sent by a queue or the like. A processing procedure for receiving results may be created at the time of analysis. Until the report of the end of the result is sent from the activated database operation server 13, 1604 and the result are received 1603. The result is returned 105 to the host UAP. In the case of FIG. 1, all the results are received and then returned to the UAP. However, an alternative to returning one or a plurality of results to the UAP each time it is received is also applicable. If the query is a search, the result is a search result. In the case of searching for data consisting of a plurality of sub data, the search result includes the position information 126. If the inquiry is other than the search result, only the result end report is given.

  FIG. 17 is a process explanatory diagram of an embodiment of the sub data acquisition 106, sub data extraction 114, data transfer 115, and data reception 107 of FIG. The identifier of the database operation server 13 with the data is extracted from the data position information 127, and a request for sub-data extraction is sent to the database operation server 13 together with information 128 necessary for sub-data extraction 1702. . The database operation server side receives 1703 sub-data retrieval information 128 from the front-end server 12 side. Data is acquired 1704 by the data address 302 in the database operation server 13. Sub data 129 is extracted 1705 according to the data type 602 from the offset position 604 by the data length 603 based on the offset information 701 of the sub data to be used. In an alternative in which the offset is incorporated in the data 130 with respect to the variable length sub-data, the offset incorporated in the data 130 may be extracted from the sub-data identifier, and the sub-data may be extracted using the offset. The extracted sub-data 129 is transferred 115 to the front-end server side. One or more sub-data 129, which is the result of 1706 received from the database operation server 13, is placed 1707 in the area 505 for storing the result prepared at the time of analysis 101. This area is designated by the offset 506 in the processing procedure 52 of the internal format using the sub data, and the use of the sub data is realized.

  When the sub-data inquiry 122b can deliver the sub-data on the database operation server 13 side as in the update process for the retrieved data, the sub-data inquiry 122b is set in the area shared with the sub-data using side. It is possible to apply an alternative in which the processing 107 and the location 505 in FIG. It is possible to determine whether or not it is an update process for the retrieved data when an update search is designated during the analysis of the search query 122a. In this case, since there is no data transfer between the front-end server 12 and the database operation server 13, the inquiry time can be reduced.

  If the query 122b using the sub data is an update process for the retrieved data, and the data address 402 is a real address for data storage, the sub data is not fetched and the processing procedure in the internal format of the update process is performed. Alternatively, an alternative to directly updating the data on the database can be applied by incorporating the position information 127 and the offset information 124 of the sub data to be used. In this case, since the data is directly updated without fetching the data on the memory, the inquiry time can be reduced.

  18 and 19 are schematic diagrams of examples in which the present invention is applied to a specific SQL. FIG. 18 shows an example of a search SQL for data composed of a plurality of sub data, and FIG. 19 shows an example of SQL using the sub data of the data searched in FIG. In the example shown in the figure, one search result is extracted by INTO and used later. However, the present invention is also applicable to a case where a plurality of search results are extracted and one result is used in a subsequent query in a loop or the like. Is applicable.

  FIG. 18 shows the SQL analysis and execution for retrieving address data from the address book. The address data is composed of three sub-data items such as a zip code, an address, and a telephone number. The address book table and address data type definition information are acquired from the dictionary, analyzed, and a processing procedure 125 in an internal format is created. It is assumed that the address book is divided and stored in the database operation server 1 and the server 2. It is assumed that the data that meets the WHERE condition is in the server 2. The processing procedure in the internal format is transferred 102 to the server 1 and the server 2 and executed 112. Since the server 2 has data that meets the conditions, the server 2 which is the identifier of the database operation server 13b is acquired 1505, the data address 1801 is acquired 1506, the position information 126 is created 1507, and the result 126 is returned 113. The result 126 is returned as information 127 to be bound to the variable on the UAP side of the host 11. Although the disk is omitted in FIG. 18, the address may be a real address for storing data, or may be represented by a logical address obtained by taking out the data from the memory.

  FIG. 19 is an example of an inquiry using a telephone number, which is sub-data of address data, as a determination condition. Since the processing after the determination is irrelevant to the gist of the invention, it is omitted in. Since the process receives the query 1201, performs syntax analysis and semantic analysis, and uses the sub-data telephone number of the variable: X, the position information 127 bound to the variable and the offset of the sub-data of the sub-data telephone number to be used A processing procedure 51 in an internal format for extracting sub-data is created 1206 from the information 124. Next, the processing procedure 52 in the internal format of the IF statement that uses the sub data is created 1207.

  At the time of execution, the address of the address data and the offset information of the sub data telephone number to be used, which is the sub data extraction information 128, are transferred to the server 2 which is the base operation server of the data acquired from the position information 127. The telephone number offset is 26. The data length of the zip code is 6, the data length of the address is 20, and there is a telephone number at a position 26 from the head address of the address data, and the sub data telephone number can be taken out. However, the sub-data is assumed to be clustered. For simplicity, the offset is represented by 0 at the beginning of the first sub data. It is possible to apply an alternative that places address data on the cache by the search query of FIG. 18 and does not cause extra IO. Since the retrieved subdata 129 is placed in an area shared by the IF statement side that uses the subdata in the data reception 107, the subdata 129 can be used when the IF statement processing procedure is executed 104.

  FIG. 20 shows an alternative embodiment of the present invention. FIG. 20 shows an example of analysis and execution of the search query 122a that is performed first. The difference from FIG. 1 is that, in the data search 122 a made up of a plurality of sub-data, the search result 2001 is not the position information 126 but the data itself 2001. In this case, the information 127 to be bound to the variable is an address of data on the front-end server or the data itself. If there is no function for receiving data consisting of multiple sub-data on the UAP side, the address is on the front-end server. In the inquiry 122b after using the sub-data, there is data on the front-end server side, so there is no need to retrieve sub-data on the database operation server side. Data and sub-data on the front-end server side can be used directly.

  FIG. 21 shows another example of the information 127 to be bound to the variable shown in FIG. Used in the example of FIG. The information 127 bound to the variable includes flag information 2101 indicating whether the data is transferred to the front-end server or whether the position information is transferred to the front-end server without transferring the data. The flag information may be 0 when data is transferred to the front-end server and 1 when position information is transferred to the front-end server. If it is possible to determine whether data is transferred to the front-end server or whether position information is transferred to the front-end server without transferring data, the flag information may not be used. The information 127 to be bound to the variable includes information 2102 when data is transferred to the front-end server and information 2103 when position information is transferred to the front-end server. Information 2102 when data is transferred to the front-end server is the address of the data on the front-end or the data itself. Information 2103 when the position information is transferred to the front-end server is the position information 126.

  FIGS. 22 and 23 are schematic diagrams of an example in which the method of FIG. 1 (method of transferring position information) and the method of FIG. 20 (method of transferring data) are selected based on selection criteria such as cost calculation. Selection criteria include a method for calculating and comparing the cost of each method from dictionary information such as the data length of the sub-data, and the method shown in FIG. 1 if there is a large amount of sub-data such as LOB data in the retrieved data. There is a method of dividing as shown in FIG.

  FIG. 22 is a schematic diagram of retrieval of data including a plurality of sub data. At the time of analysis 101, analysis and execution of the method 2202 in FIG. 1 and the method 2203 in FIG. However, the information 127 to be bound to the variables shown in FIG. On the UAP side of the host, when searching for data that includes sub-data, it is necessary to prepare an area for the length of the variable area to receive the information to be bound to the variable.

  FIG. 23 is a schematic diagram of an inquiry using sub-data. At the time of analysis 101, the information 127 bound to the variable is 2301 based on flag information 2101 indicating whether the data is transferred to the front-end server or whether the position information is transferred to the front-end server without transferring the data. The method 2302 of FIG. 20 and the analysis and execution of the method 2303 of FIG. 20 are selected.

  As described above, according to the present invention, in the search for data consisting of a plurality of sub-data, only the position information is transferred, and the sub-data to be used later is extracted in the inquiry using the sub-data of the data. It is possible to reduce communication time by transferring unused sub data and to reduce inquiry time. This is particularly effective when the unused sub data is large-scale data such as LOB data.

It is a block diagram of an Example. It is an example of data consisting of a plurality of sub data. It is an example of position information. It is an example of information bound to a variable. It is an example of the processing procedure of an internal format. It is an example of offset information of sub data. It is an example of the information for subdata extraction. It is an example of subdata. It is an example of the data stored in the data operation server. It is an Example of offset information creation of subdata. It is an Example of the data preparation which consists of several sub data. It is an explanation of processing for creating a processing procedure in an internal format. It is a process description of processing procedure transfer and processing procedure reception in an internal format. It is a process description of execution of a processing procedure in an internal format. It is process description of processing procedure execution. It is a process description of result reception and result transfer. It is process description of subdata acquisition and subdata extraction. It is a schematic diagram of a specific example. It is a schematic diagram of a specific example. This is an alternative embodiment. It is an example of information bound to a variable. It is an outline figure of search of data consisting of a plurality of sub data. It is a schematic diagram of the inquiry which uses subdata.

Explanation of symbols

DESCRIPTION OF SYMBOLS 11 ... Host 12 ... Front-end server 13 ... Database operation server 14 ... Dictionary 101 ... Analysis, 102 ... Transfer processing procedure in internal format, 103 ... Result reception 104 ... Execution of processing procedure in internal format, 106 ... Sub-data acquisition, 107 ... Data reception 111 ... processing procedure reception, 112 ... processing procedure execution, 113 ... result transfer 114 ... sub-data retrieval, 115 ... data transfer 121 ... UAP, 122 ... inquiry, 124 sub-data offset information 125 ... processing procedure, 126 ... position Information, 127: Information bound to variable 128: Information for sub data extraction, 129: Sub data, 130: Data

Claims (8)

In a database management method in a database management system having a front-end server that analyzes a query to a database and a database operation server that performs data operations based on the analysis result of the query in the database,
The front-end server includes an identifier of each subdata of a hierarchical data having a hierarchical structure defined from a plurality of subdata names and type declarations, and from the storage location of the hierarchical data to a storage location of each subdata. Storing dictionary information having offset information, and generating a processing procedure based on the analysis of the query with respect to the hierarchical data having a plurality of sub-data,
The database operation server returns position information of the hierarchical data to the front-end server based on the processing procedure input from the front-end server,
The front-end server, in response to an input of an operation request for the sub data, the position information of the returned hierarchical data, offset information of the operation target sub data obtained from dictionary information, and the operation target A database management method comprising: extracting the subdata of the hierarchical data in the database operation server based on the identifier of the subdata, and processing the subdata based on the operation request. The database management method according to claim 1,
The location information of the hierarchical data includes an identifier of the database operation server and an address of the hierarchical data in the database operation server. In the database management method according to claim 1 or 2,
The process based on the operation request for the extracted subdata is a process of determining whether the extracted subdata satisfies a predetermined condition, and the result of querying the hierarchical data including the subdata satisfying the condition Database management method characterized by returning as In the database management method according to claim 1 or 2,
The database management method characterized in that the processing based on the operation request for the extracted sub data is an update processing for the sub data. In the database management system,
A dictionary of hierarchical data having a hierarchical structure defined from a plurality of sub-data names and type declarations, and an identifier of each sub-data and offset information from the storage location of the hierarchical data to the storage location of each sub-data Means for storing information;
Means for generating a processing procedure based on an analysis of a query for the hierarchical data having a plurality of sub-data;
In response to the input of the operation request for the sub data, the position information of the hierarchical data returned from the database operation server, the offset information of the operation target sub data obtained from the dictionary information, and the operation target sub data Means for retrieving sub-data of the hierarchical data in the database operation server according to an identifier, and processing the sub-data based on the operation request;
A database management system comprising: the database operation server having means for returning position information of the hierarchical data to the front end server based on the processing procedure input from the front end server. The database management system according to claim 5 , wherein
The position information of the hierarchy data includes an identifier of the database operation server and an address of the hierarchy data in the database operation server. In the database management system according to claim 5 or 6 ,
The process based on the operation request for the extracted subdata is a process of determining whether the extracted subdata satisfies a predetermined condition, and the result of querying the hierarchical data including the subdata satisfying the condition Database management system characterized by being returned as In the database management system according to claim 5 or 6 ,
The database management system according to claim 1, wherein the process based on the operation request for the extracted sub data is an update process for the sub data.

Images