JP6898215B2 - Database management device, database management method and database management program - Google Patents

Description

The present invention relates to a database management device, a database management method, and a database management program.

Conventionally, by using data virtualization technology, a data integration environment in which a plurality of database systems (hereinafter referred to as databases) are virtually integrated has been constructed.

In a data integration environment, schema information, which is information indicating a table stored in a database, is represented by a physical model and a logical model. The physical model indicates the physical storage location of the table and holds connection information for connecting to the database in which the table is stored. In the logical model, a virtual table corresponding to one or more physical models or logical models is constructed. By referring to the virtual table and executing the query statement, the client can execute the query statement without being aware of the database in which the table is located.

In the data integration environment, the processing device that processes the query statement received from the user acquires the table required when executing the query statement from the database via the network, and executes the query statement using the acquired table. Therefore, the execution performance of the query statement in the data integrated environment depends on the amount of data communication. Therefore, by reducing the amount of data communication, it is possible to improve the execution performance of the query statement in the data integrated environment.

Non-Patent Document 1 discloses, as a method of reducing the amount of data communication, a method of performing a process corresponding to a query statement on a database storing a table corresponding to the query statement when the query statement is executed. However, when the query statement refers to a table stored in each of a plurality of databases, the method described in Non-Patent Document 1 has a problem that the processing in the database is limited and the communication volume cannot be efficiently reduced. ..

In response to such a problem, in Patent Document 1, when a table stored in each of a plurality of databases is referred to when executing a query statement, a table having a small amount of data is temporarily moved to one database. Later, a method of performing processing corresponding to the inquiry statement is disclosed on the database.

Further, in Patent Document 2, in cooperation between Hadoop (registered trademark), which is a framework for managing distributed processing of large-scale data, and a database, an intermediate product related to query processing is generated on Hadoop, and the intermediate product is generated. A method for streamlining query processing by using a product is disclosed.

Japanese Unexamined Patent Publication No. 2016-91356 Special Table 2016-528586

Laura M. Haas, et al. “Optimizing Queries across Diverse Data Sources”, VLDB '97 Proceedings of the 23rd International Conference on Very Large Data Bases, pp. 276-285 (1997)

By using the method described in Patent Document 1, it is possible to reduce the amount of data communication when referring to the tables stored in each of a plurality of databases. However, when the query statement refers to the tables stored in each of a plurality of databases, when these tables are of the same size, there is a problem that the amount of communication increases by moving the tables. Occurs.

Further, by using the method described in Patent Document 2, the amount of processing and the amount of communication for regenerating the intermediate product can be reduced. However, since the method described in Patent Document 2 uses an intermediate product generated on Hadoop, there is a problem that it cannot be extended to any plurality of database systems.

Therefore, the present invention has been made in view of these points, and provides a database management device, a database management method, and a database management program capable of improving the execution performance of query statements in an environment using a plurality of databases. The purpose is to do.

The database management device according to the first aspect of the present invention is referred to by the user, including a physical model for managing the storage location of each of the plurality of tables included in each of the plurality of databases, and a view of the table. Acquired based on the storage unit that stores the logical model that manages the virtual table, the acquisition unit that acquires a plurality of query statements corresponding to at least one of the plurality of databases, and the logical model and the physical model. Of the subqueries commonly included in at least a part of the query statements of the plurality of query statements, a specific part that identifies the subquery that refers to the plurality of databases and the physical corresponding to the identified subquery. A creation unit for creating a view, and an update unit for updating the logical model and the physical model so that the user can refer to the created view when the view is created.

Based on the usage status of the plurality of tables, the database management device identifies a table to be migrated to a database different from the database currently stored among the plurality of databases as a migration target table, and the identified table is specified. A migration control unit that migrates the migration target table to the migration destination database is further provided, and the update unit updates the storage destination of the migration target table indicated by the physical model to the migration destination database, and the specific unit displays the migration target table. After the migration of the migration target table by the migration control unit is completed and the storage destination of the migration target table indicated by the physical model is updated, a plurality of the databases are created based on the acquired query statements. You may specify the subquery to refer to.

The database management device further includes a calculation unit for calculating the processing cost of the specified subquery and the processing cost of the view when the subquery is replaced with the view, and the creation unit is calculated. Based on the processing cost of the subquery and the processing cost of the view when the subquery is replaced with the view, the view is displayed in descending order of the expected value of the amount of reduction in the processing cost by replacing the subquery with the view. You may create it.

When the calculation unit creates the physical view in any of the plurality of databases and executes the query statement corresponding to the view, the calculation unit is between the database management device and the plurality of databases. The predicted value of the communication volume may be calculated, and the creating unit may create the physical view in the database in which the calculated predicted value is relatively small.
The calculation unit specifies a range in which processing is performed in the database that creates a view corresponding to the subquery for each of the plurality of query statements, and based on the data corresponding to the specified range, the processing cost is calculated. The predicted value is calculated, the predicted value of the communication amount is corrected based on the predicted value of the processing cost, and the creation unit performs the physical in the database in which the predicted value of the corrected communication amount is relatively small. View may be created.

The creation unit may create the physical view in the storage unit of the database management device.

The database management method according to the second aspect of the present invention includes a step of acquiring a plurality of query statements corresponding to at least one of a plurality of databases executed by a computer, and each of the plurality of databases. At least a part of the plurality of query statements acquired based on a physical model that manages the storage location of each of the plurality of tables and a logical model that manages the virtual table referenced by the user, including the view of the table. Among the subqueries commonly included in the query statement of, the step of identifying a subquery that refers to a plurality of the databases, the step of creating a physical view corresponding to the identified subquery, and the view are Once created, it comprises a step of updating the logical model so that the user can refer to the created view.

The database management program according to the third aspect of the present invention includes an acquisition unit for acquiring a plurality of query statements corresponding to at least one of a plurality of databases, and a plurality of databases included in each of the plurality of databases. Queries of at least some of the retrieved query statements based on a physical model that manages each storage location of the table and a logical model that manages the virtual table referenced by the user, including views of the table. Among the subqueries commonly included in the statement, a specific part that identifies a subquery that refers to a plurality of the databases, a creation part that creates a physical view corresponding to the specified subquery, and the view When created, the created view is made to function as an update unit that updates the logical model so that the user can refer to it.

According to the present invention, there is an effect that the execution performance of a query statement can be improved in an environment in which a plurality of databases are used.

It is a figure which shows the structure of the virtual database system which concerns on 1st Embodiment. It is a figure which shows the structure of the database management apparatus which concerns on 1st Embodiment. It is a figure which shows an example of the inquiry sentence which concerns on 1st Embodiment. It is a figure which shows the execution plan corresponding to the inquiry statement shown in FIG. It is a flowchart which shows the flow of the process which concerns on the creation of the materialized view which concerns on 1st Embodiment. It is a figure which shows the structure of the database management apparatus which concerns on 2nd Embodiment.

<First Embodiment>
[Overview of virtual database system S]
FIG. 1 is a diagram showing a configuration of a virtual database system S according to the first embodiment. The virtual database system S is a system that includes a plurality of storage devices 1 that physically store the database 11, a database management device 2, and a user terminal 3, and virtually integrates the plurality of databases 11. Although only one user terminal 3 is shown in FIG. 1, it is assumed that a plurality of user terminals 3 are connected to the database management device 2.

The database management device 2 stores a physical model and a logical model as schema information which is information indicating a table stored in the database 11. A physical model is a model that shows the physical storage location of a table. The logical model is a model that manages a virtual table that can be referred to by the user terminal 3, and associates the virtual table with the table indicated by the physical model. Here, the virtual table includes a view created by the database management device 2. When the database management device 2 acquires a query statement corresponding to the logical model from the user terminal 3, it refers to the physical model and identifies one or more databases 11 corresponding to the query statement.

The database management device 2 acquires data from the specified database 11, executes an operation corresponding to the query statement, and outputs the execution result to the user terminal 3. As a result, the user can refer to the data stored in the plurality of databases 11 without being aware of the physical position of the database 11.

When a plurality of tables corresponding to a query statement are stored in the same database 11, the database management device 2 performs a process corresponding to the query statement in order to reduce the amount of data communication at the time of executing the query statement. Let it be done on 11. Further, in the database management device 2, when a plurality of tables corresponding to the query statements are not stored in the same database 11, the database management device 2 acquires the tables corresponding to the query statements, and the database management device 2 inquires. Performs processing corresponding to the statement. Therefore, when the table corresponding to the query statement is not stored in the same database 11, there arises a problem that the amount of data communication increases and the execution performance of the query statement deteriorates.

In response to such a problem, the database management device 2 according to the present embodiment has a plurality of databases 11 among subqueries commonly included in at least a part of the query statements acquired from the user terminal 3. Identify the subquery that references. Then, the database management device 2 creates a materialized view, which is a physical view corresponding to the specified subquery, in its own storage area. The database management device 2 updates the logical model so that the user can refer to the created materialized view. By doing so, the database management device 2 can improve the execution performance of the query statement in an environment in which an arbitrary plurality of databases 11 are used.
Subsequently, the configuration of the database management device 2 will be described.

[Configuration of database management device 2]
FIG. 2 is a diagram showing a configuration of the database management device 2 according to the first embodiment. The database management device 2 is a computer, and is communicably connected to a plurality of storage devices 1 and a user terminal 3 via a LAN (Local Area Network) or the like.

As shown in FIG. 2, the database management device 2 includes a storage unit 21 and a control unit 22.
The storage unit 21 is, for example, a ROM (Read Only Memory), a RAM (Random Access Memory), or the like. The storage unit 21 stores various programs for operating the database management device 2. For example, the storage unit 21 causes the control unit 22 of the database management device 2 to function as a reception unit 221, an execution unit 222, an acquisition unit 223, a specific unit 224, a calculation unit 225, a creation unit 226, and an update unit 227, which will be described later. Remember the management program.

Further, the storage unit 21 stores the physical model PM and the logical model LM as schema information which is information indicating the table stored in the database 11. The physical model PM manages the storage destinations of a plurality of tables stored in the plurality of databases 11. Specifically, the physical model PM stores the table name that identifies the table stored in the database 11 and the database identification information that identifies the database 11 in which the table is stored in association with each other.

The logical model LM manages the virtual table referenced by the user. The logical model LM stores the table name that identifies the virtual table and the information indicating the configuration of the virtual table in association with each other. Here, the information indicating the configuration of the virtual table is the table name indicated by the physical model PM, or the view created by the user of the database management device 2 or the user terminal 3. Here, the view may include not only a materialized view, which is a physical view, but also a logical view that describes the contents of a subquery.

The control unit 22 is, for example, a CPU (Central Processing Unit). The control unit 22 controls the functions related to the database management device 2 by executing various programs stored in the storage unit 21. By executing the database management program, the control unit 22 functions as a reception unit 221, an execution unit 222, an acquisition unit 223, a specific unit 224, a calculation unit 225, a creation unit 226, and an update unit 227.

The reception unit 221 receives an inquiry statement corresponding to at least one of the plurality of databases 11 from the user terminal 3. Specifically, the reception unit 221 receives a command statement described in SQL (Structured Query Language) as a plurality of query statements from the user terminal 3.

The execution unit 222 performs a process corresponding to the inquiry statement received by the reception unit 221. First, the execution unit 222 generates execution plan information indicating an execution plan corresponding to the acquired query statement. Here, the execution plan has a tree structure.

FIG. 3 is a diagram showing an example of an inquiry sentence according to the first embodiment. In FIG. 3, two query statements are shown as shown in (a) and (b). 4 (a) and 4 (b) are diagrams showing an execution plan corresponding to the query statements shown in FIGS. 3 (a) and 3 (b). As shown in FIG. 4, it can be confirmed that the execution plan has a tree structure. Further, as shown in FIG. 4, the type of processing executed by the query statement is described in the ellipse. When the type of processing is table scanning, the name of the table to be scanned (for example, customers or orders) is shown after the processing. In the execution plan shown in FIG. 4, the description of the processing contents such as the specification of the column to be selected from the table and the condition for selecting the row from the table is omitted.

The execution unit 222 performs a process corresponding to the acquired inquiry statement based on the execution plan. For example, when the table corresponding to the query statement is stored in the same database 11, the execution unit 222 causes the processing corresponding to the query statement to be performed on the database 11. Further, when the table corresponding to the query statement is not stored in the same database 11, the execution unit 222 acquires the table corresponding to the query statement from the database 11 and processes the table corresponding to the query statement using the table. I do.

Further, when the materialized view corresponding to the query statement is stored in the storage unit 21, the execution unit 222 acquires data by referring to the materialized view, and uses the data to perform processing corresponding to the query statement. Do.

The execution unit 222 outputs the execution result of the inquiry statement to the user terminal 3. Further, the execution unit 222 stores the acquired inquiry statement, the execution plan corresponding to the inquiry statement, and the date and time when the inquiry statement is received in the storage unit 21 in association with each other.

The acquisition unit 223 acquires a plurality of query statements corresponding to at least one of the plurality of databases 11. Specifically, the acquisition unit 223 acquires a plurality of inquiry statements stored in the storage unit 21 and an execution plan associated with the inquiry statement at predetermined time intervals (for example, one week). Here, the acquisition unit 223 may acquire the inquiry sentence received by the reception unit 221 within a predetermined period from the current time among the plurality of inquiry sentences stored in the storage unit 21. By doing so, the database management device 2 can create a materialized view corresponding to the usage tendency of the latest query statement.

The identification unit 224 specifies a subquery statement commonly included in at least a part of the query statements acquired by the acquisition unit 223. For example, the specific unit 224 identifies a common subtree that is commonly included in at least a part of the execution plans of the plurality of execution plans acquired together with the plurality of query statements and indicates the structure of the subquery, and is used in the common subtree. Identify the corresponding subquery. The identification unit 224 identifies a subquery that refers to a plurality of databases 11 among the specified subquery statements based on the logical model LM and the physical model PM stored in the storage unit 21.

The calculation unit 225 calculates the processing cost of the specified subquery and the processing cost of the materialized view when the subquery is replaced with the materialized view. Here, the processing cost includes a calculation cost when executing a subquery and a communication cost indicating the time for acquiring data via a communication network or the like. For example, the calculation unit 225 calculates the calculation cost of the subquery based on the number of records in the table referenced by the specified subquery, and calculates the communication cost based on the number of records or the amount of data obtained as a result of the subquery. calculate. Then, the calculation unit 225 calculates the processing cost of the subquery based on the calculated calculation cost and the communication cost.

Similarly, the calculation unit 225 calculates the processing cost of the materialized view when the subquery is replaced with the materialized view. Here, since the materialized view does not require the process of concatenating the tables, the calculation cost is significantly smaller than that in the case of executing the subquery. Further, when the materialized view is stored in the storage unit 21, the communication cost when acquiring the data corresponding to the materialized view is remarkably reduced.

Further, the calculation unit 225 specifies the execution frequency of the subquery based on the ratio of the subquery included in the plurality of query statements acquired by the acquisition unit 223. Then, the calculation unit 225 reduces the processing cost by replacing the subquery with the materialized view based on the execution frequency of the subquery, the processing cost of the subquery, and the processing cost of the materialized view corresponding to the subquery. Calculate the expected value of the quantity. Here, the expected value is a weighted average of the processing costs in which the probability based on the execution frequency of the subquery is added as a weight. When the execution frequency fluctuates non-steadily, the calculation unit 225 may fluctuate the probability in consideration of the fluctuation and calculate the expected value based on the probability.

The creation unit 226 creates a materialized view corresponding to the subquery specified by the specific unit 224 to refer to the plurality of databases 11. Specifically, the creation unit 226 is based on the calculated processing cost of the subquery, the processing cost of the view when the subquery is replaced with the materialized view, and the execution frequency of the subquery, and the materialized view. By replacing with, materialized views are created in descending order of expected value of reduction in processing cost.

Here, the creation unit 226 specifies the storable capacity of the storage unit 21 of the database management device 2. Then, the creation unit 226 creates a materialized view in the storage unit 21 within a range that does not exceed the specified storable capacity. By doing so, the database management device 2 can prevent the storage capacity of the storage unit 21 from becoming insufficient while creating the materialized view.

The creation unit 226 creates the materialized view in the storage unit 21 of the database management device 2, but the present invention is not limited to this. When the materialized view is created in any of the plurality of databases 11, the calculation unit 225 predicts the amount of communication between the database management device 2 and the plurality of databases 11 when executing the query statement corresponding to the view. May be calculated. Here, the communication volume may be calculated based on the statistical information of past communication, for example, the average throughput, or the information between the upper limit value and the lower limit value of the confidence interval. Then, the creation unit 226 may create a materialized view in the database 11 in which the calculated predicted value is relatively small.

Further, the calculation unit 225 may consider not only the communication amount but also the processing cost for the predicted value to be calculated. For example, the calculation unit 225 specifies the range to be processed in the database 11 that creates the view corresponding to the subquery for each of the plurality of query statements, and predicts the processing cost based on the data corresponding to the specified range. The value may be calculated and the predicted value of the communication volume may be corrected based on the predicted value of the processing cost. Then, the creation unit 226 may create a materialized view in the database 11 in which the predicted value of the corrected communication amount is relatively small.

For example, when a materialized view is created in any of a plurality of databases 11, the calculation cost may be reduced. For example, if the subquery is nested and the parent of the subquery corresponding to the materialized view has a subquery (parent subquery) related to join processing with the table in the database in which the materialized view is placed. Calculation costs are reduced. Further, in this case, the amount of communication between the database management device 2 and the database in which the materialized view is arranged may be further reduced because the data is narrowed down by the parent / subquery.

In order to consider the above case, the calculation unit 225 sequentially refers to the subtree containing the intersection tree from the common subtree in the direction of the root for each query, and arranges the materialized view. Search the range that can be completed only by the table in the database. Then, as a result of the search, the calculation unit 225 refers to the number of records or the amount of data of the subtree if there is a subtree that includes the common subtree and is completed only by the table in the database in which the materialized view is arranged. , Calculate the predicted value of the processing cost. Specifically, the calculation unit 225 first corresponds to the common subtree with respect to the total number of records of each node of the subtree with respect to the number of records referred to in calculating the predicted value for the calculation cost. The part related to the sum of the number of records is replaced by the sum of the number of records replaced with the number of records obtained from the materialized view. Subsequently, the calculation unit 225 substitutes the number of records or the amount of data referred to in calculating the predicted value for the communication cost by the number of records or the amount of data corresponding to the subtree.

Here, as a result of creating the materialized view in the storage unit 21 within the range not exceeding the storage capacity of the storage unit 21 of the database management device 2, the creation unit 226 creates the materialized view in the storage unit 21 and creates the materialized view in the database 11 It may be created in.

Further, the creation unit 226 has decided to automatically create a materialized view in the storage unit 21 of the database management device 2, but the present invention is not limited to this. The creation unit 226 may receive an instruction from the user terminal 3 as to whether or not to create the materialized view. Then, the creation unit 226 may create the materialized view in response to the instruction to create the materialized view. Further, the creation unit 226 may create the same materialized view in the storage unit 21 of the database management device 2 and the database 11.

Further, the creation unit 226 may evaluate the materialized view already created in the storage unit 21. Then, the creating unit 226 may delete the materialized view from the storage unit 21 by the first-in first-out method, the LRU (Least Recently Used) method, or the LFU (Least Frequently Used) method. Further, the creation unit 226 may delete the materialized view from the storage unit 21 in which the expected value of the reduction in the processing cost is relatively small. By doing so, the database management device 2 deletes the materialized view from the storage unit 21 having a small expected value of the reduction amount of the processing cost, and the materialized view having a large expected value of the reduction amount of the processing cost in the storage unit 21. Can be placed efficiently.

Further, the creation unit 226 may create the materialized views in descending order of the expected value of the reduction amount of the processing cost after deleting all the materialized views already created in the storage unit 21.

When the materialized view is created, the update unit 227 updates the physical model PM and the logical model LM so that the user of the execution unit 222 and the user terminal 3 can refer to the created materialized view. Further, the update unit 227 may output the information indicating that the materialized view has been created and the information indicating the subquery corresponding to the materialized view to the user terminal 3 in association with each other. As a result, when the user of the user terminal 3 generates a query statement including the subquery, the materialized view can be specified instead of the subquery to shorten the processing time of the query statement in the execution unit 222. it can.

[Process flow related to materialized view creation]
Next, the flow of processing related to the creation of the materialized view will be described. FIG. 5 is a flowchart showing a flow of processing related to the creation of the materialized view according to the first embodiment.

First, the acquisition unit 223 acquires a plurality of query statements and execution plans stored in the storage unit 21 corresponding to at least one of the plurality of databases 11 (S10).
Subsequently, the specifying unit 224 specifies a subquery commonly included in the plurality of query statements based on the execution plan (S20).

Subsequently, the calculation unit 225 calculates the processing cost of the specified subquery and the processing cost when the subquery is replaced with the materialized view (S30).
Subsequently, the creation unit 226 creates the materialized view in order from the subquery with the largest expected value of the reduction in processing cost by replacing the specified subquery with the materialized view (S40).

Subsequently, the creation unit 226 determines whether or not the storage unit 21 of the database management device 2 has a storable area for the materialized view to be created next (S50). When the creation unit 226 determines that there is a storable area, the process is transferred to S40, and the materialized view to be created next is created. If the creation unit 226 determines that there is no storage area, the creation unit 226 ends the process related to this flowchart.

[Effect in the first embodiment]
As described above, the database management device 2 according to the first embodiment acquires a plurality of query statements corresponding to at least one of the plurality of databases 11, and is common to at least a part of the query statements of the plurality of query statements. Among the included subqueries, the subqueries that refer to a plurality of databases 11 are specified. Then, the database management device 2 creates a materialized view corresponding to the specified subquery, and updates the logical model LM so that the user of the user terminal 3 can refer to the materialized view. By doing so, the database management device 2 can suppress that a plurality of databases 11 are referred to at the same time, and can improve the execution performance of the query statement in an environment in which a plurality of arbitrary databases are used.

<Second Embodiment>
[Create a materialized view after migrating the table based on the usage status of database 11]
Subsequently, the second embodiment will be described. The virtual database system S according to the second embodiment is different from the first embodiment in that a materialized view is created after migrating the tables based on the usage status of the database 11. The virtual database system S according to the second embodiment will be described below. The same parts as those in the first embodiment will be omitted as appropriate.

FIG. 6 is a diagram showing a configuration of the database management device 2 according to the second embodiment. As shown in FIG. 6, the control unit 22 of the database management device 2 further includes a transition control unit 228.

The transition control unit 228 acquires a plurality of inquiry statements stored in the storage unit 21 at predetermined time intervals and an execution plan corresponding to the inquiry statements. Then, the transition control unit 228 specifies the usage status of the plurality of tables based on the acquired plurality of inquiry statements and execution plans.

The migration control unit 228 specifies a table to be migrated to a database 11 different from the currently stored database 11 as a migration target table among the plurality of databases 11 based on the usage status of the specified plurality of tables.

The migration control unit 228 migrates the specified migration target table to the migration destination database 11. Specifically, the migration control unit 228 replicates the specified migration target table to the migration destination database 11, and when the replication of the migration target table is completed, deletes the migration target table from the migration source database 11.

When the migration of the migration target table by the migration control unit 228 is completed, the update unit 227 updates the storage destination of the migration target table indicated by the physical model of the database management device 2 to the migration destination database.

By doing so, the database management device 2 can arrange the table in an appropriate database based on the usage status of the table. As a result, the database management device 2 has more opportunities to execute the query statement in the database 11, so that the amount of data communication at the time of executing the query statement can be reduced and the execution performance of the query statement can be improved.

After the migration of the migration target table by the migration control unit 228 is completed and the storage destination of the migration target table indicated by the physical model is updated by the update unit 227, the acquisition unit 223 executes a plurality of inquiry statements and executions from the storage unit 21. Get the plan.

The identification unit 224 specifies a subquery that refers to a plurality of databases 11 based on a plurality of query statements acquired after the storage destination of the migration target table indicated by the physical model is updated by the update unit 227.
Then, the creation unit 226 creates a materialized view corresponding to the subquery specified by the specific unit 224 to refer to the plurality of databases 11.

[Effect in the second embodiment]
As described above, the database management device 2 according to the second embodiment shifts to a database 11 different from the currently stored database 11 among the plurality of databases 11 based on the usage status of the plurality of tables. The table is specified as a migration target table, and the identified migration target table is migrated to the migration destination database 11. Then, after the migration of the migration target table is completed and the storage destination of the migration target table indicated by the physical model is updated, the database management device 2 refers to the plurality of databases 11 based on the acquired plurality of query statements. Identify the subquery to be used, and create a materialized view corresponding to the subquery.

By doing so, the database management device 2 can improve the execution performance of the query statement including the subquery statement that refers to a plurality of tables stored in different databases 11 after the migration of the migration target table.

Although the present invention has been described above using the embodiments, the technical scope of the present invention is not limited to the scope described in the above embodiments. It will be apparent to those skilled in the art that various changes or improvements can be made to the above embodiments. For example, the plurality of embodiments described above may be combined. Further, in particular, the specific embodiments of the distribution / integration of the devices are not limited to those shown above, and all or a part thereof may be arbitrarily added according to various additions or functional loads. It can be functionally or physically distributed / integrated in units.

For example, in the above-described embodiment, the database management device 2 performs execution management and migration control of the query statement, but the present invention is not limited to this. For example, one or more servers different from the database management device 2 may perform execution management of query statements and table migration control.

1 ... storage device, 11 ... database, 2 ... database management device, 21 ... storage unit, 22 ... control unit, 221 ... reception unit, 222 ... execution unit, 223 ... acquisition unit, 224 ... specific unit, 225 ... calculation unit, 226 ... creation unit, 227 ... update unit, 228 ... transition control unit, 3 ... user terminal, S・ ・ ・ Virtual database system

Claims (6)

A database management device that manages databases
A storage unit that stores a physical model that manages the storage location of each of a plurality of tables included in each of the plurality of databases, and a logical model that manages a virtual table referenced by a user, including a view of the table. ,
An acquisition unit that acquires a plurality of query statements corresponding to at least one of the plurality of databases, and an acquisition unit.
A specific unit that identifies a plurality of subqueries that refer to the database among the subqueries commonly included in at least a part of the acquired query statements based on the logical model and the physical model. When,
When a physical view corresponding to the specified subquery is created in any of the plurality of databases, the database management device and the plurality of databases when executing the query statement corresponding to the view. A calculation unit that calculates the predicted value of the communication volume between the two databases for each of the plurality of databases.
A creation unit that creates a physical view corresponding to the specified subquery in the database in which the predicted value calculated by the calculation unit is relatively small.
When the view is created, an update unit that updates the logical model and the physical model so that the user can refer to the created view.
A database management device equipped with. Based on the usage status of the plurality of tables, among the plurality of the databases, the table to be migrated to the database different from the currently stored database is specified as the migration target table, and the identified migration target table is the migration destination. It also has a migration control unit that migrates to the database of
The update unit updates the storage destination of the migration target table indicated by the physical model to the database of the migration destination.
The specific unit is based on the plurality of query statements acquired after the migration of the migration target table by the migration control unit is completed and the storage destination of the migration target table indicated by the physical model is updated. Identify subqueries that reference multiple databases,
The database management device according to claim 1. The calculation unit calculates the processing cost of the specified subquery and the processing cost of the view when the subquery is replaced with the view .
Based on the calculated processing cost of the subquery and the processing cost of the view when the subquery is replaced with the view, the creating unit expects a reduction in processing cost by replacing the subquery with the view. Create the view in descending order of value,
The database management device according to claim 1 or 2. The calculation unit specifies a range in which processing is performed in the database that creates a view corresponding to the subquery for each of the plurality of query statements, and based on the data corresponding to the specified range, the processing cost is calculated. The predicted value is calculated, and the predicted value of the communication volume is corrected based on the predicted value of the processing cost.
The creation unit creates the physical view in the database in which the predicted value of the corrected communication volume is relatively small.
The database management device according to any one of claims 1 to 3. Computer runs,
Steps to get multiple query statements corresponding to at least one of multiple databases,
Obtained based on a physical model that manages the storage location of each of the plurality of tables included in each of the plurality of databases and a logical model that manages the virtual table referenced by the user, including the view of the table. A step of identifying a subquery that refers to a plurality of the databases among the subqueries commonly included in at least a part of the query statements of the plurality of the query statements.
Between the computer and the plurality of databases when executing the query statement corresponding to the view when a physical view corresponding to the specified subquery is created in any of the plurality of databases. And the step of calculating the predicted value of the communication volume for each of the plurality of databases.
A step of creating a physical view corresponding to the specified subquery in the database in which the calculated predicted value is relatively small, and
When the view is created, the step of updating the logical model so that the user can refer to the created view, and
Database management method with. Computer,
Acquisition unit that acquires multiple query statements corresponding to at least one of multiple databases,
Obtained based on a physical model that manages the storage location of each of the plurality of tables included in each of the plurality of databases and a logical model that manages the virtual table referenced by the user, including the view of the table. A specific part that identifies a subquery that refers to a plurality of the databases among the subqueries commonly included in at least a part of the query statements of the plurality of the query statements.
Between the computer and the plurality of databases when executing the query statement corresponding to the view when a physical view corresponding to the specified subquery is created in any of the plurality of databases. A calculation unit that calculates the predicted value of the communication volume of the above-mentioned multiple databases for each of the plurality of databases.
A creation unit that creates a physical view corresponding to the specified subquery in the database in which the predicted value calculated by the calculation unit is relatively small, and a creation unit.
When the view is created, an update unit that updates the logical model so that the user can refer to the created view.
A database management program that functions as.

Images