JP6157004B2 - Virtual database system management apparatus, management method, and management program - Google Patents

Description

  The present invention optimizes the data arrangement in accordance with the user's past inquiry contents when selecting a database system in an environment where there are a plurality of database systems capable of holding at least partially the same data. The present invention relates to a virtual database system management apparatus, a management method, and a management program that execute efficient processing.

Conventionally, in order to improve the processing capability by distributing the load of database access, a plurality of databases including the same data have been prepared in the database system management apparatus.
For example, when a load distribution device is introduced and a database connection is established from a user, the databases are selected and connected in order or in order of decreasing database load. In this case, since the database to be connected is determined when the database connection is established, the database cannot be selected according to the contents of the query from the user.

On the other hand, in the environment where a plurality of databases exist, several methods for handling them in a unified manner have been proposed.
For example, Patent Literature 1 proposes a search method that enables a plurality of databases including information requested by a user to be searched in an environment where a plurality of databases exist. However, the method described in Patent Document 1 does not assume that a database having the same content exists, and therefore cannot select a database in a situation where the database can be selected.

  Patent Document 2 describes a case where tables having the same name exist in a plurality of databases. However, even if a table has the same name, the contents are not necessarily the same, and priorities are set in advance. Therefore, it is not possible to select a database according to the contents of the user's inquiry.

  In addition, when considering the database system configuration as a collection of the database itself and the database management system (DBMS) that manages the database, one database is shared by multiple database management systems (DBMS), and as a result, the same data is retained. It is also assumed that there will be a plurality of database systems.

  When there are multiple database systems that have the same data and the query processing engine of each DBMS that executes query processing in each database system is unique, and you want to select the database system (query processing engine) according to the user's query content In addition, the above-described prior art cannot cope with this.

Therefore, the inventor logically defines databases and tables in an environment where a plurality of database systems exist, and assigns a set of a plurality of DBMSs, databases, tables, and range conditions in each database system to them. As a result, a virtual database system management apparatus having means for selecting one of a plurality of sets according to the contents of a user's inquiry and advancing the process has been proposed (Patent Document 3).
According to this apparatus, it is possible to select a database system and execute a query process in an environment where a plurality of database systems including the same data exist.

Japanese Patent Laid-Open No. 10-307743 JP 2000-82080 A Japanese Patent Application No. 2013-176709

  However, according to the apparatus described above, the database system can be selected according to the user's inquiry content and the inquiry content can be executed. However, in order to efficiently process the inquiry content from the user, which data is duplicated There is no mention of what to put in the database system.

  The present invention has been proposed in view of the above circumstances, and in an environment where there are a plurality of database systems, in order to improve the efficiency of query processing, the data to be duplicated and a database system for arranging the data are provided. It is an object to provide a virtual database system management apparatus, a management method, and a management program that can be selected.

To achieve the above object, the present invention (Claim 1) is connected to a plurality of database systems capable of holding at least partially the same data in a database, and outputs data in response to access from a user system. The database system management apparatus includes the following configuration.
A physical database management table for managing entry numbers associated with the database.
A logical database management table that defines logical database information to be accessed from the user system and manages the data in the physical database management table in association with the corresponding entry numbers.
A database system information table for managing processing priorities, resource capacities, and resource utilization rates in each database system.
In response to the query from the user system, the logical database information to be accessed is extracted, the corresponding set of physical database information is acquired from the logical database management table and the physical database management table, and the acquired set of physical database information A database selection unit that selects one set according to the content of an inquiry when there are a plurality of items.
An execution history management unit that acquires an execution plan and an execution result of the inquiry content and creates and manages an execution history management table.
A data placement determination unit that determines data to be duplicated (data rearrangement candidates) and a database system in which the data is to be placed by using information managed by the execution history management unit and the database system information table.
A data placement execution unit that instructs the database system to create or move data according to the determination of the data placement determination unit, and updates information in the logical database management table and the physical database management table.

Claim 2 is the virtual database system management apparatus according to claim 1,
The execution history management table has information on a link destination of a table in which each inquiry content is executed,
The execution history management unit creates a database access management table for each table of the physical database described in the link destination of the execution history management table,
In the database access management table, a plurality of columns referred to for each inquiry executed in the target table are recorded,
The data arrangement determining unit is characterized by extracting column conditions that are frequently used for each table and determining the data (data relocation candidates) that should be duplicated.

In the virtual database system management method of the present invention (claim 3), in an environment in which a plurality of database systems capable of holding the same data in a database are connected and data is output in response to access from a user system,
Logical database management table defining logical database information to be accessed from the user system, one or a plurality of database management systems (DBMS) in the plurality of database systems, and a set of database tables for the logical database information A management table creation procedure for creating a physical database management table to which
An execution history management procedure for acquiring an execution plan and execution results of the query content from the user system and managing them as an execution history management table;
Database system management procedure for acquiring and managing resource usage information of each database system,
A data arrangement determination procedure for determining data to be duplicated using the information managed by the execution history management table and the resource usage information (data relocation candidates) and a database system in which the data is to be arranged;
An update procedure for instructing the database system to create or move data according to the determination, and updating information in the logical database management table and physical database management table;
It is characterized by performing by.

Claim 4 is the virtual database system management method of claim 3,
The execution history management table has information on a link destination of a table in which each inquiry content is executed,
The execution history management procedure creates a database access management table for each table of the physical database described in the link destination of the execution history management table,
In the database access management table, a plurality of columns referred to for each inquiry executed in the target table are recorded,
The data arrangement determination procedure is characterized by extracting column conditions that are frequently used for each table and determining the data that should be duplicated (data relocation candidates).

  A fifth aspect of the present invention is a virtual database system management program, characterized in that each procedure of the virtual database system management method according to the third or fourth aspect can be executed by a computer.

According to the present invention, when there are a plurality of database systems, and the database system is selected according to the contents of the user's query and the process is executed, the execution plan and execution result of the query statement are stored in the execution history management table. The processing priority, resource capacity, and resource usage rate (resource usage information of the database system) in the system are managed in the database system information table.
Then, from these pieces of information, the data to be duplicated (data relocation candidate) and the database system to which the data is to be determined are determined, data creation instructions are issued to each database system, and the virtual database system management device The information of the logical database management table and the physical database management table is updated with respect to the database management unit.
As a result, it is possible to select a more appropriate physical database in the database selection unit for the input of a new query statement.

It is a block diagram which shows the whole structure of the virtual database system management apparatus of this invention. It is a model figure which shows an example in case a some database system exists in a virtual database system management apparatus. It is a table figure which shows the example of a physical database management table. It is a table figure which shows the example of a logical database management table. It is a flowchart figure which shows the process sequence in a database selection part. It is a model figure which shows an example in case a some database system exists in a virtual database system management apparatus. It is a table figure which shows the example of a physical database management table. It is a table figure which shows the example of a logical database management table. It is a table figure which shows the example of a database system information table. It is a table figure which shows the example of an execution log | history management table. It is a table figure which shows the example of a database access management table. It is a flowchart figure which shows the process sequence in a data arrangement | positioning determination part. It is a flowchart figure which shows the process sequence in a data arrangement | positioning execution part. It is a model figure of the virtual database system management apparatus after rearrangement. It is a table figure which shows the example of the physical database management table after a rearrangement. It is a table figure which shows the example of the logical database management table after a rearrangement. It is a model figure which shows an example in case a some database system exists in a virtual database system management apparatus. It is a table figure which shows the example of a physical database management table. It is a table figure which shows the example of a logical database management table. It is a table figure which shows the example of a database system information table. It is a table figure which shows the example of an execution log | history management table. It is a table figure which shows the example of a database access management table. It is a table figure which shows the example of a database access management table. It is a table figure which shows the example of table T1 with respect to S1 = {P1, P2}. It is a model figure of the virtual database system management apparatus after rearrangement. It is a table figure which shows the example of the physical database management table after a rearrangement. It is a table figure which shows the example of the logical database management table after a rearrangement.

An example of an embodiment of a virtual database system management apparatus of the present invention will be described with reference to the drawings.
The virtual database system management device is a system that enables execution of query contents by selecting a database system in accordance with a user query content in an environment where a plurality of database systems having the same data exist. It is constructed on a computer by installing it by downloading software via a recording medium storing the program or the Internet.

The computer in which the virtual database system management apparatus is constructed includes a ROM that stores a basic program including an operating system (OS) and various basic devices, a hard disk drive (HDD) that stores various programs and data, A media drive device that reads programs and data from a storage medium such as a CD-ROM or DVD, a CPU that executes the programs, a RAM that provides a work area for the CPU, and an input / output interface (I / F) A general configuration mainly includes a pointing device such as a display, a keyboard and a mouse, and a parallel / serial I / F communicating with an external device.
In the virtual database system management apparatus of this embodiment, a virtual database system management program is input from a serial / parallel I / F or read by a media drive device and stored in the HDD in advance. The virtual database system management program is stored in a storage medium, read by the media drive device, and installed in the HDD.

A virtual database system management apparatus constructed on a computer defines logical database information (database, table) to be accessed by a user in an environment where a plurality of database systems exist, and Assign one or more DBMS, database, table, and range condition pairs.
Then, when a plurality of sets are assigned to the logical database information, the same data can be managed by selecting one of the plurality of sets and executing the process according to the inquiry content from the user. In an environment in which a plurality of database systems are included, a database system is selected, and data suitable for query processing from a user can be selected.

Hereinafter, the configuration of the virtual database system management apparatus 10 will be described with reference to the block diagram of FIG.
The virtual database system management apparatus 10 includes a database interface unit 11 connected to a plurality of database systems 20, a database management unit 12 that acquires and manages information of each database system 20 through the database interface unit 11, and a user system 1. A user interface unit 13 that performs input / output to / from the user, a query input analysis unit 14 that analyzes a query statement input from the user system 1, an execution plan generation unit 15 that generates an execution plan of the query statement, and a database interface unit 11 And a query execution unit 16 that executes a query statement and outputs the result to the user system 1.

The database management unit 12 includes a logical database management table 12a for managing logically defined logical database information (database, table, etc.) and a physical database management table 12b for managing a set of database tables, etc. Information of each database system 20 is managed. The association between data in the logical database management table 12a and the physical database management table 12b will be described later.
In addition, the database management unit 12 includes a database system information table 12c that manages processing priority, resource capacity, and resource usage rate (resource usage information of the database system) in each database system. The database system information table 12 c acquires resource usage information of each database system 20 via the database interface unit 11.

  The query input analysis unit 14 analyzes the lecture of the query sentence input via the user interface unit 13 and inputs the analysis result to the execution plan generation unit 15.

The execution plan generation unit 15 includes a database selection unit 15a that acquires database information corresponding to the analysis result of the query input analysis unit 14 from the database management unit 12 and selects the database system 20 that executes the query statement. Then, an execution plan of a query statement is generated for the selected database system 20.
That is, the database selection unit 15a includes a rule evaluation unit that performs conformity evaluation with a predefined rule when selecting one database system 20 according to a query. Then, the database system 20 is selected based on the matching result in the rule evaluation unit.
The query execution unit 16 executes the execution plan generated by the execution plan generation unit 15 and outputs the execution result to the user system 1 via the user interface unit 13.

Further, in the virtual database system management apparatus 10 of the present invention, when there are a plurality of database systems 20, in order to determine which data should be duplicated and arranged in the plurality of database systems 20, and to change the arrangement , An execution history management unit 17, a data arrangement determination unit 18, and a data arrangement execution unit 19.
The execution history management unit 17 manages an execution history management table 17a that acquires an execution plan of a query statement from the execution plan generation unit 15, an execution result of the query statement from the query execution unit 16, and physical data for each query statement. A database access management table 17b is provided. Details of the execution history management table 17a and the database access management table 17b will be described later.

The data arrangement determining unit 18 uses the information managed by the database management unit 12 and the execution history management unit 17 to determine the data that should be duplicated and the database system 20 in which the data is to be arranged.
The data placement execution unit 19 issues an instruction to create or move data to each database system 20 through the database interface unit 11 according to the determination of the data placement determination unit 18, and the logical database management table 12 a managed by the database management unit 12 and Information in the physical database management table 12b is updated.

First, when there are a plurality of database systems 20 having at least a part of the same data by the virtual database system management apparatus 10, a plurality of databases are selected so that the database system 20 is selected according to the content of the user's inquiry and the process is executed. A procedure for managing the system 20 will be described with reference to FIGS.
A plurality of database systems 20 a and 20 b are connected to the virtual database system management apparatus 10, and data is output in response to access from the user system 1. Each database system 20a, 20b includes a database 21 in which various data are stored, and a database management system (hereinafter referred to as DBMS) 22 that manages the database 21, and the environment in which the same data is held in the database 21. It has become.
Each database system 20 may use, for example, those having different properties such as those suitable for a large capacity and those having a fast access time.

More specifically, the database system 20a includes a database management system (DBMS1) 22 and a database (DB1) 21, and two tables TBL1 and TBL2 are defined in DB1.
Similarly, the database system 20b includes a database management system (DBMS2) 22 and a database (DB1) 21, and two tables TBL1 and TBL2 are defined in DB1. It is assumed that the contents of the database (DB1) 21 of the database system 20a and the database system 20b are the same. In this example, it is assumed that the two databases (DB1) 21 are the same. However, the two databases (DB1) 21 do not necessarily have to be completely the same, and can be applied even when partially having the same data.

When the two database systems 20a and 20b are connected to the virtual database system management apparatus 10, the database management unit 12 acquires database information from each of the database systems 20a and 20b, and, as shown in FIG. P1-P4), a DBMS (DBMS1 or DBMS2), a database (DB1), a table (TBL1 or TBL2), and a range condition group are managed as a physical database management table 12b (management table creation procedure).
Note that it is not always necessary to acquire data from each database system 20, and for example, a physical database management table may be created manually.

The range condition is used to specify a condition for determining an effective range of data in the table corresponding to the entry number in the database system 20.
In the example of FIG. 3, it is indicated that data having a “date and time” value of “January 2013” or later is valid assuming that there is an attribute of “date and time” for TBL1 of the database system 20b.

Also, as shown in FIG. 4, logical database information (database, table) to be accessed from the user system 1 is defined and managed as a logical database management table 12a (management table creation procedure).
In the logical database management table 12a, link items are set as information for managing the data of the physical database management table in association with the corresponding entry numbers.
In the example shown in FIGS. 2 to 4, the entry number P1 and entry number P3 and the entry number P2 and entry number P4 in the physical database management table have the same contents as a part or the whole, so that VDB1 is used as a logical database. / VTBL1 and VDB2 / VTBL2 are defined and associated with the physical database.

  When a query statement is input from the user system 1 through the user interface unit 13, the syntax is checked by the query input analysis unit 14, and the analysis result is input to the execution plan generation unit 15.

Next, the database / table to be queried is extracted by the execution plan generation process by the execution plan generation unit 15, and the database system that executes the query statement by the procedure shown in FIG. 5 is specified by the database selection unit 15a (database selection). procedure).
That is, the database selection unit 15a acquires the query content from the execution plan generation unit 15, and extracts a database and table set included in the query content (step 61).

The database selection unit 15a calls the database management unit 12 with the extracted database and table set as input (step 62).
The database management unit 12 searches the logical database management table 12a for the entry number corresponding to the acquired database and table set, searches the physical database management table 12b for the entry number described in the link item, One or a plurality of sets of tables and range conditions are acquired and output to the database selection unit 15a (step 63).

The database selection unit 15a determines whether the number of result sets per input set is one or more (step 64).
The database selection unit 15a selects a set of query contents to be executed in consideration of the search condition included in the query contents (step 65).

In the database selection unit 15a, the database / table combination (logical database) acquired from the execution plan generation unit 15 is associated with the searched / selected DBMS, database, and table combination (physical database), and the execution plan generation unit 15 (Step 66).
The execution plan generation unit 15 generates an execution plan for the query statement based on the acquired information and inputs the execution plan to the query execution unit 16.
In the query execution unit 16, a query statement is executed via the database interface unit 11 based on the execution plan (processing procedure), and finally the execution result is output to the user system 1.

By executing the above procedure, the database selection unit 15a can select the database system corresponding to the query statement by referring to the contents of the query statement.
For example, the update system query can be preset to be the database system 20a (database system 1), and the reference query can be set to the database system 20b (database system 2). Further, when the capacity, access speed, etc. of each database system 20 are different, considering the capacity, access speed, etc. suitable for the query contents, for example, the query to TBL1 is the database system 20a (database system 1), Queries other than can be preset with the database system 20b (database system 2).

  For example, in the example of FIGS. 2 to 4, when the target table of the query statement is VTBL1, it is found from the logical database management table 12a that it is linked to P1 and P3. Thus, a set of information corresponding to P1 and P3 is returned to the database selection unit 15a. The database selection unit 15a selects P1 or P3 according to the contents of the inquiry. For example, the condition of the inquiry sentence is suitable for “date and time ≧ January 2013”, and DBMS2 has higher processing capability. In such a case, P3 is selected.

  Further, the database selection unit 15a may be provided with a cost evaluation unit that performs cost evaluation on the contents of the inquiry sentence, and the database system 20 may be selected based on the value calculated by the cost evaluation unit. For example, the cost evaluation unit calculates an evaluation value based on the analysis result of the inquiry input analysis unit 14.

  In accordance with the database information specified by the database selection unit 15a, the execution plan generation unit 15 generates an execution plan and a query statement suitable for each database system 20, and the query execution unit 16 sends the query statement via the database interface unit 11. Is executed, and the execution result is finally output to the user system 1.

  Next, in order to efficiently process the inquiry contents from the user, which is a characteristic configuration of the present invention, when there are a plurality of database systems 20, which data should be duplicated and placed in the plurality of database systems 20. Regarding the management of the kimono, the functions of the execution history management unit 17, the data arrangement determination unit 18, and the data arrangement execution unit 19 in FIG. 1 are exemplified with the database system A and the database system B of the virtual database system management apparatus 10 shown in FIG. explain.

  In the virtual database system management apparatus (example 1) of FIG. 6, as shown in FIGS. 7 and 8, DBMS-A. DB1. TBL1 exists and the logical database VDB1. Linked to VTBL1 (link destination P1). The physical database management table of FIG. 7 corresponds to FIG. 3 described above, and includes an entry number (only P1 in this example), a DBMS (in this example, DBMS-A), a database (DB1), and a table. (TBL1), a set of range conditions is managed. The logical database management table in FIG. 8 corresponds to FIG. 4 described above, and shows a link destination P1.

Further, as shown in FIG. 9, the database system information table 12c of the database management unit 12 manages the priority, resource capacity, and resource utilization rate in the processing of each database system 20 (database system A, database system B). Yes. The priority sets which database system 20 has priority. The capacity is a capacity value recorded in the database system 20. The utilization rate is an index of the utilization status in each database system 20, and is indicated by%.
In this example, the database system B is a database having a small capacity (1 TB) and a high processing speed, and the database system A is a database having a large capacity (100 TB), for example, until the utilization rate reaches 90%. Are preferentially registered in the database system B.
Hereinafter, a management procedure for rearranging (copying) TBL1 existing as physical data in the database system A to the database system B having a high processing speed will be described.

In the execution history management unit 17, information on an execution plan (which query is processed in which database) and an execution result (processing time, processing number, data amount, etc.) of the query statement (query) executed by the query execution unit 16. Is recorded in the execution history management table 17a.
The query sentence from the user is converted into a sub-query sentence to be executed in each database system 20, the sub-query sentence execution for each data system 20 is stage 1, and the query sentence is processed for the user based on the result. Are recorded in an execution history management table as shown in FIG. That is, each query statement is divided into stage 1 and stage 2, and the database used, the table used, information about the link destination, and the execution results of stage 1 and stage 2 are recorded.
The execution result of stage 1 is the execution result (α1, β1, γ1, δ1, ε1) of each subquery statement in each database system 20, and the execution result of stage 2 is the execution result of the entire query statement (α2, β2, γ2, δ2, ε2).

Further, the execution history management unit 17 creates a database access management table 17b for each table of the physical database management table described in the link of the execution history management table. That is, as many database access management tables as the number of physical table types are created.
As shown in FIG. 11, the database access management table records columns referred to from a plurality of columns (COL1 to COL5) for each subquery executed in the target table. In FIG. 11, for each column, a check indicating that the reference portion indicates the entire column is added. However, when the reference portion is limited to a part rather than the entire column, not the check but the range (for example, January 2014). ) May be recorded.

Next, processing in the data arrangement determining unit 18 will be described with reference to FIG.
From the execution history management table 17a of the execution history management unit 17, a query statement Qi whose stage 2 execution result (processing time, etc.) in the query statement exceeds a certain level is extracted (step 81).
In each query sentence Qi, a set of target physical tables of the sub-query sentence is extracted, and the deduplicated one is Sj. For each Sj, a table Tj is generated by extracting the corresponding Qi record from the database access management table (step 82).
For each table Tj, a column condition Cj having a high usage frequency is extracted and set as a data rearrangement candidate (step 83).
For each Cj, the database system information table is referenced, and the relocation destination database DBMSj. DBj is determined and {Cj, DBMSj. DBj} is output.
If the relocation destination database cannot be determined, the process is skipped (step 84).

A specific example of processing in the data arrangement determining unit 18 will be described with reference to the example of FIG.
In the example of FIG. 10, assuming that all conditions of the execution result (processing time, etc.) are satisfied for the query statement, Q1 to Q5 are extracted as the query statements.
Since all the target physical tables of the sub-query statements of these query statements Q1 to Q5 are P1, when deduplication is performed, S1 = {P1}. When target records are extracted from the database access management table of FIG. 11 for P1, T1 becomes the same as the table of FIG.

  Considering column conditions that are frequently used in T1 (for example, columns referenced by four or more subquery statements for five subquery statements), in this example, COL1, COL2, and COL3 are applicable. Therefore, {DBMS-A. DB1. TBL1. COL1, DBMS-A. DB1. TBL1. COL2, DBMS-A. DB1. TBL1. Let COL3} be a data relocation candidate.

  Referring to the database system information table of FIG. 9, the database system B has a higher priority than the database system A, and it can be expected that the processing efficiency is improved when the data is arranged in the database system B. If there is no problem after checking the free resources of the database, the database system B (DBMS-B.DB1) is determined as the placement destination, and {{DBMS-A. DB1. TBL1. COL1, DBMS-A. DB1. TBL1. COL2, DBMS-A. DB1. TBL1. COL3}, DBMS-B. DB1} is output.

Processing in the data arrangement execution unit 19 will be described with reference to FIG.
Each {Cj, DBMSj. DBj}, the data in the range of Cj is sent to DBMSj. Duplicate or move to DBj (step 91).
The physical database management table 12b and the logical database management table 12a are updated through the database management unit 12 (step 92).

  6 to 11, the input from the data arrangement determining unit 18 is {{DBMS-A. DB1. TBL1. COL1, DBMS-A. DB1. TBL1. COL2, DBMS-A. DB1. TBL1. COL3}, DBMS-B. DB1}, DBMS-A. DB1. Columns COL1, COL2, and COL3 are extracted from TBL1, and DBMS-B. Data is replicated to DB1 as TBL1.

As a result, as shown in FIG. 14, the data of TBL1 exists in the database system A and the database system B, and the physical database management table and the logical database management table are updated as shown in FIGS. 15 and 16, respectively. The That is, in the physical database management table (FIG. 15), data of DB1, TBL1, range condition columns COL1, COL2, COL3 are created in DBMS-B as entry number P2, and in the logical database management table (FIG. 16), a link P2 is added first.
As a result, the query statement for VTBL1 can be processed by selecting P1 or P2. Access to the frequently used columns COL1, COL2, and COL3 is a database system having a higher processing capacity than that associated with P2. Since processing in B becomes possible, processing efficiency is improved.
Since access to the column COL4 in the query statement for VTBL1 cannot be processed by selecting P2, P1 is selected and processed by the database system A.

  When the replication target is the entire table, it is not always necessary to retain the original data. Therefore, the original data can be deleted after the replication in order to secure the capacity of the database. In this case, the physical database management table and the logical database management table are also updated accordingly.

Subsequently, a more complicated example (example 2) regarding the arrangement of physical data with respect to the virtual database system management apparatus 10 will be described with reference to FIGS. 17 to 27.
In this example, DBMS-A. DB1. TBL1 (entry number P1) and DBMS-B. DB1. TBL2 (entry number P2) exists, and logical databases VDB1. VTBL1 and VDB1. Assume that it is linked to VTBL2 (FIGS. 17, 18, and 19).
Further, in the database system information table 12c of the database management unit 12, as shown in FIG. 20, the priority, resource capacity, and resource utilization rate in the processing of each database system 20 (database system A, database system B) are managed. Yes. In this example, the database system A (capacity 100 TB) and the database system B (capacity 50 TB) are different in capacity, but the priority is set to the same (MEDIAUM).

Assuming that all conditions of the execution result are satisfied in the data arrangement determining unit 18 as in the above example, the query statements Q1 to Q5 are extracted from the execution history management table of FIG. The query statements Q1 to Q5 have two types of subquery statements Q1A to Q5A and Q1B to Q5B. Since the target physical tables of these subquery statements are all {P1, P2}, S1 = {P1, P2 }. That is, the database access management table (P1) for the sub-query statements Q1A to Q5A is as shown in FIG. 22, and the database access management table (P2) for the sub-query statements Q1B to Q5B is as shown in FIG.
When the target record is extracted from the database access management table of FIGS. 22 and 23 for S1, the table T1 becomes as shown in FIG.

Considering column conditions that are frequently used in T1 (for example, columns referred to by four or more subquery statements for five subquery statements), DBMS-A. DB1. TBL1 COL1, COL2, COL3, DBMS-B. DB1. Since COL1 and COL2 of TBL2 are applicable, {DBMS-A. DB1. TBL1. COL1, DBMS-A. DB1. TBL1. COL2, DBMS-A. DB1. TBL1. COL3, DBMS-B. DB1. TBL2. COL1, DBMS-B. DB1. TBL2. COL2} is a data relocation candidate.
Since column conditions are distributed in a plurality of database systems (DBMS-A.DB1, DBMS-B.DB1), it is possible to expect improvement in processing efficiency by consolidating data in one database system.

Based on the information in the database system information table, a database system to be aggregated is selected, a free resource is confirmed, and a data relocation destination is determined.
In the case of this example, there is no difference in priority between the two database systems in the database system information table of FIG. DB1. Data on column conditions of TBL2 is DBMS-A. Suppose that it replicates in DB1, {{DBMS-B. DB1. TBL2. COL1, DBMS-B. DB1. TBL2. COL2}, DBMS-A. DB1} is output.

Next, in the data placement execution unit 19, data replication and update of the physical database management table and logical database management table are performed, and the model diagram of the virtual database system management device after the rearrangement is as shown in FIG. The database system A has a configuration in which TBL2 is replicated.
In the physical database management table after relocation (FIG. 26), data of DB1, TBL2, and range condition columns COL1, COL2 are created in DBMS-A as entry number P3. In 27), P3 is added to the link destination of VTBL2.

According to the virtual database system management apparatus 10 described above, in the environment where a plurality of database systems 20 exist, when the database system 20 is selected according to the user's inquiry content and the process is executed, the user in the execution history management unit 17 The data arrangement determination unit 18 selects and determines the data to be duplicated (data relocation candidate) and the database system 20 to which the data is to be arranged based on the past inquiry contents and the processing result of the data, and executes the data arrangement Data relocation can be executed by the unit 19.
As a result, a plurality of database systems 20 having different processing capacities can be used more efficiently, and processing time is greatly reduced by executing processing queries on data distributed to the plurality of database systems 20, thereby significantly reducing the execution time. Can be expected.

  Accordingly, in an environment where a plurality of database systems 20 exist, the data arrangement is optimized according to the user's usage, regardless of which data is stored in which database system 20, so that the user and system management The user's burden on database system management is reduced, and convenience can be improved.

  DESCRIPTION OF SYMBOLS 1 ... User system, 10 ... Virtual database system management apparatus, 11 ... Database interface part, 12 ... Database management part, 12a ... Logical database management table, 12b ... Physical database management table, 12c ... Database system information table, 13 ... User interface , 14 ... Query input analysis unit, 15 ... Execution plan generation unit, 15a ... Database selection unit, 16 ... Query execution unit, 17 ... Execution history management unit, 17a ... Execution history management table, 17b ... Database access management table, 18 ... Data arrangement determination unit, 19 ... Data arrangement execution unit, 20 ... Database system, 21 ... Database, 22 ... Database management system (DBMS).

Claims (5)

In a database system management apparatus in which a plurality of database systems capable of holding at least partially the same data in a database are connected, and data is output in response to access from a user system,
A physical database management table for managing entry numbers associated with the database;
A logical database management table that defines logical database information to be accessed from the user system and manages the data of the physical database management table in association with the corresponding entry numbers;
A database system information table for managing processing priority, resource capacity, and resource utilization in each database system;
In response to the query from the user system, the logical database information to be accessed is extracted, the corresponding set of physical database information is acquired from the logical database management table and the physical database management table, and the acquired set of physical database information A database selection unit that selects one set according to the content of the query,
An execution history management unit that acquires an execution plan and execution result of the inquiry content and creates and manages an execution history management table;
A data placement determination unit for determining data to be duplicated (data rearrangement candidates) using the information managed by the execution history management unit and the database system information table, and a database system in which the data is to be placed;
A data placement execution unit that instructs the database system to create or move data according to the determination of the data placement determination unit, and updates information of the logical database management table and the physical database management table;
A virtual database system management apparatus comprising: The execution history management table has information on a link destination of a table in which each inquiry content is executed,
The execution history management unit creates a database access management table for each table of the physical database described in the link destination of the execution history management table,
In the database access management table, a plurality of columns referred to for each inquiry executed in the target table are recorded,
The virtual database system management apparatus according to claim 1, wherein the data arrangement determining unit determines column data (data relocation candidates) that should be duplicated by extracting column conditions that are frequently used for each table. In an environment where multiple database systems that allow the same data to be stored in a database are connected and data is output in response to access from a user system,
Logical database management table defining logical database information to be accessed from the user system, one or a plurality of database management systems (DBMS) in the plurality of database systems, and a set of database tables for the logical database information A management table creation procedure for creating a physical database management table to which
An execution history management procedure for acquiring an execution plan and execution results of the query content from the user system and managing them as an execution history management table;
Database system management procedure for acquiring and managing resource usage information of each database system,
A data arrangement determination procedure for determining data to be duplicated using the information managed by the execution history management table and the resource usage information (data relocation candidates) and a database system in which the data is to be arranged;
An update procedure for instructing the database system to create or move data according to the determination, and updating information in the logical database management table and physical database management table;
The virtual database system management method characterized by performing by this. The execution history management table has information on a link destination of a table in which each inquiry content is executed,
The execution history management procedure creates a database access management table for each table of the physical database described in the link destination of the execution history management table,
In the database access management table, a plurality of columns referred to for each inquiry executed in the target table are recorded,
The virtual database system management method according to claim 3, wherein the data arrangement determination procedure determines column data (data relocation candidates) that should be duplicated by extracting column conditions that are frequently used for each table.   A virtual database system management program that enables a computer to execute each procedure of the virtual database system management method according to claim 3.