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

Abstract

To improve execution performance of an inquiry sentence in an environment where a plurality of databases are used.SOLUTION: A database management device 2 stores a physical model for managing a storage destination of a plurality of tables included in each of a plurality of databases 11 and a logical model for managing a virtual table including a view of the table to which a user refers, and acquires a plurality of inquiry sentences corresponding to at least any of the plurality of databases 11, and specifies a sub-inquiry for referring to the plurality of databases 11 among sub-inquiries commonly included in at least a part of the plurality of acquired inquire sentences on the basis of the physical model and the logical model, and creates a materialized view corresponding to the specified sub-inquiry, and updates the logical model and the physical model such that the user can refer to the created materialized view.SELECTED DRAWING: Figure 1

Description

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

  2. Description of the Related Art Conventionally, it has been practiced to construct a data integration environment in which a plurality of database systems (hereinafter referred to as databases) are virtually integrated by using data virtualization technology.

  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 destination of the table and holds connection information for connecting to the database in which the table is stored. Logical models construct virtual tables that correspond to one or more physical models or logical models. The client executes the query statement by referring to the virtual table, so that the query statement can be executed without being aware of the database in which the table is arranged.

  In the data integration environment, a processing device that processes a query sentence received from a user acquires a table necessary for executing the query sentence from the database via the network, and executes the query sentence using the acquired table. Therefore, the execution performance of a query statement in a data integration 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 integration environment.

  Non-Patent Document 1 discloses, as a method of reducing the amount of data communication, a method of performing processing corresponding to a query sentence on a database storing a table corresponding to the query sentence when executing the query sentence. 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 databases is limited and the amount of communication can not be reduced efficiently. .

  With regard to such a problem, in Patent Document 1, when referring to a table stored in each of a plurality of databases at the time of executing a query statement, a table with a small amount of data is temporarily moved to one of the databases After that, a method of performing processing corresponding to a query sentence on the database is disclosed.

  Further, in Patent Document 2, in cooperation with 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, A method is disclosed to streamline query processing by using products.

JP, 2016-91356, A JP-A-2016-529586

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 the plurality of databases. However, when the query statement refers to the tables stored in each of a plurality of databases, if the tables have the same size, moving the tables may increase the amount of communication. Occurs.

  Moreover, by using the method described in Patent Document 2, it is possible to reduce the amount of processing and the amount of communication for regenerating the intermediate product. However, the method described in Patent Document 2 has a problem that it can not be extended to any plurality of database systems because it uses the intermediate product generated on Hadoop.

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

  A database management apparatus according to a first aspect of the present invention refers to a user including a physical model managing a storage destination of each of a plurality of tables included in each of a plurality of databases, and a view of the table. Acquired based on a storage unit that stores a logical model that manages a virtual table, an 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. Among the subqueries commonly included in at least some of the plurality of query sentences, a specific unit that specifies a subquery that refers to a plurality of the databases, and a physical corresponding to the specified subquery A creation unit for creating a unique view, and when the view is created, the logical mo- Comprising an update unit for updating the Le and the physical model, the.

  The database management apparatus identifies, as a migration target table, a table to be migrated to a database different from the currently stored database among the plurality of databases based on the use status of the plurality of tables. The migration control unit is further configured to migrate the migration target table to the migration destination database, the updating unit updates the storage destination of the migration target table indicated by the physical model to the migration destination database, and the identification unit 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 selected based on the plurality of acquired inquiry statements. You may specify a subquery to refer to.

  The database management apparatus further includes a calculation unit that calculates the processing cost of the identified subquery and the processing cost of the view when the subquery is replaced with the view, and the creation unit calculates 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 sorted in the descending order of the expected value of the reduction amount of the processing cost by replacing the view with the view. You may create it.

The calculation unit, when creating the physical view in any of the plurality of databases, executes the query statement corresponding to the view between the database management device and the plurality of databases. The predicted value of the communication amount may be calculated, and the creation unit may create the physical view in the database in which the calculated predicted value is relatively small.
The calculation unit specifies, for each of a plurality of the query sentences, a range in which processing is performed in the database in which a view corresponding to the subquery is created, and based on data corresponding to the specified range, The predicted value is calculated, and the predicted value of the communication amount is corrected based on the predicted value of the processing cost, and the creation unit is configured to calculate the physical property of the database in which the predicted value of the communication amount after correction relatively decreases. You may create a static view.

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

  A database management method according to a second aspect of the present invention includes steps of acquiring a plurality of query sentences corresponding to at least one of a plurality of databases, which are executed by a computer, and each of the plurality of databases. At least a portion of the plurality of acquired query statements based on a physical model that manages each storage destination of a plurality of tables, and a logical model that manages virtual tables referenced by a user, including a view of the tables Identifying a subquery that refers to a plurality of the databases among subqueries commonly included in the query statement, creating a physical view corresponding to the identified subquery, and the view Updating the logical model so that the user can refer to the created view when created , Comprising a.

  A database management program according to a third aspect of the present invention includes a computer, an acquisition unit for acquiring a plurality of query sentences corresponding to at least one of a plurality of databases, and a plurality of the plurality of databases included in each of the plurality of databases. A query of at least a part of the plurality of acquired query statements based on a physical model that manages each storage destination of the table and a logical model that manages a virtual table referenced by the user, including a view of the table Among the subqueries commonly included in the statement, an identifying unit that identifies subqueries that refer to a plurality of the databases, a creating unit that creates a physical view corresponding to the identified subqueries, and the view An update unit that updates the logical model so that the user can refer to the created view when created Make.

  According to the present invention, it is possible to improve the execution performance of a query statement in an environment using a plurality of databases.

It is a figure showing composition of a virtual database system concerning a 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 query sentence shown in FIG. It is a flow chart which shows a flow of processing concerning creation of a materialized view concerning a 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 the 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 for physically storing databases 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. The physical model is a model that indicates the physical storage location of the table. The logical model is a model for managing 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 the view created by the database management device 2. When acquiring the query sentence corresponding to the logical model from the user terminal 3, the database management device 2 refers to the physical model and specifies one or more databases 11 corresponding to the query sentence.

  The database management device 2 acquires data from the identified database 11, executes an operation corresponding to the inquiry sentence, and outputs an execution result to the user terminal 3. Thereby, 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 processing corresponding to the query statement to reduce the amount of data communication when the query statement is executed. Let's do it on 11th. Also, in the case where the plurality of tables corresponding to the query statement are not stored in the same database 11, the database management device 2 acquires the table corresponding to the query statement, and the database management device 2 makes an inquiry Perform processing corresponding to the statement. For this reason, when the table corresponding to the query statement is not stored in the same database 11, the amount of data communication is increased, which causes a problem that the execution performance of the query statement is deteriorated.

With respect to such a problem, the database management device 2 according to the present embodiment is configured to use the plurality of databases 11 among the sub-queries commonly included in at least some of the plurality of query sentences 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 identified 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 this, the database management device 2 can improve the execution performance of the query statement in an environment using any plurality of databases 11.
Subsequently, the configuration of the database management device 2 will be described.

[Configuration of Database Management Device 2]
FIG. 2 is a diagram showing the 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 the plurality of storage devices 1 and the user terminal 3 via a LAN (Local Area Network) or the like.

The database management device 2 includes a storage unit 21 and a control unit 22 as shown in FIG.
The storage unit 21 is, for example, a read only memory (ROM) or a random access memory (RAM). The storage unit 21 stores various programs for causing the database management device 2 to function. 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 specification unit 224, a calculation unit 225, a creation unit 226, and an update unit 227 described later. Remember the management program.

  The storage unit 21 also stores the physical model PM and the logical model LM as schema information that is information indicating a table stored in the database 11. The physical model PM manages storage destinations of a plurality of tables stored in a plurality of databases 11. Specifically, the physical model PM associates and stores a table name for identifying a table stored in the database 11 and database identification information for identifying the database 11 in which the table is stored.

  The logical model LM manages virtual tables referenced by the user. The logical model LM associates and stores a table name for identifying a virtual table and information indicating the configuration of the virtual table. Here, the information indicating the configuration of the virtual table is a table name indicated by the physical model PM, or a view created by the user of the database management device 2 or the user terminal 3. Here, the view may include not only the physical view materialized view but also a logical view describing the contents of the subquery.

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

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

  The execution unit 222 performs processing corresponding to the inquiry sentence 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 sentences are shown as shown in (a) and (b). FIGS. 4A and 4B are diagrams showing an execution plan corresponding to the query statement shown in FIGS. 3A and 3B. As shown in FIG. 4, it can be confirmed that the execution plan is a tree structure. Further, as shown in FIG. 4, the type of processing executed by the query sentence is described in the ellipse. Also, when the type of process is scan of a table, the table name (for example, customer or orders) to be scanned after the process is shown. In the execution plan shown in FIG. 4, the contents of processing such as designation of a column to be selected from the table and a condition to select a row from the table are omitted.

  The execution unit 222 performs processing 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 database 11 to perform processing corresponding to the query statement. In addition, 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 uses the table to process the table corresponding to the query statement. I do.

  In addition, when the materialized view corresponding to the query statement is stored in the storage unit 21, the execution unit 222 refers to the materialized view to obtain data, and uses the data to perform processing corresponding to the query statement. Do.

  The execution unit 222 outputs the execution result of the inquiry sentence to the user terminal 3. In addition, the execution unit 222 associates the acquired query sentence, the execution plan corresponding to the query sentence, and the date and time when the query sentence is received, and stores the associated query sentence in the storage unit 21.

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

  The identifying unit 224 identifies a sub-query that is commonly included in at least some of the plurality of query sentences acquired by the acquiring unit 223. For example, the identifying unit 224 identifies a common subtree which 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 sentences and which indicates the structure of the subquery. Identify the corresponding subquery. The identifying unit 224 identifies, based on the logical model LM and the physical model PM stored in the storage unit 21, sub-queries that reference the plurality of databases 11 among the identified sub-queries.

  The calculating unit 225 calculates the processing cost of the identified subquery and the processing cost of the materialized view when the subquery is replaced with a materialized view. Here, the processing cost includes the calculation cost for executing the subquery and the communication cost indicating the time for acquiring data via the communication network or the like. For example, the calculating unit 225 calculates the calculation cost of the subquery based on the number of records in the table referred to by the specified subquery, and the communication cost based on the number of records or the data amount 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 computational cost is significantly reduced as compared with the case of executing the subquery. In addition, when the materialized view is stored in the storage unit 21, the communication cost when acquiring data corresponding to the materialized view is significantly reduced.

  In addition, the calculation unit 225 identifies the execution frequency of the subquery based on the ratio of the subquery included in the plurality of query sentences 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-stationarily, the calculation unit 225 may change the probability in consideration of the fluctuation, and may calculate the expected value based on the probability.

  The creating unit 226 creates a materialized view corresponding to the subquery specified by the specifying unit 224 to refer to the plurality of databases 11. Specifically, the creating unit 226 determines the materialized view 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. Create materialized views in order of decreasing expected processing cost by replacing with.

  Here, the creation unit 226 identifies the storable capacity of the storage unit 21 of the database management device 2. Then, the creation unit 226 creates the materialized view in the storage unit 21 within the range not exceeding the identified storage capacity. By doing this, the database management device 2 can prevent the storage capacity of the storage unit 21 from running short while creating the materialized view.

  Although the creating unit 226 creates the materialized view in the storage unit 21 of the database management apparatus 2, the present invention is not limited to this. When the calculating unit 225 creates the materialized view in any of the plurality of databases 11, it estimates 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 amount may be calculated based on statistical information of past communication, such as average throughput or 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 becomes relatively small.

  In addition, the calculation unit 225 may add not only the communication amount but also the processing cost to the calculated predicted value. For example, the calculation unit 225 identifies, for each of a plurality of query sentences, a range in which processing is performed in the database 11 that has created a view corresponding to a subquery, and predicts processing costs based on data corresponding to the identified range. A value may be calculated, and the predicted value of the communication amount 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 communication amount after correction is relatively small.

  For example, when the materialized view is created in any of the plurality of databases 11, the calculation cost may be reduced. For example, if the subquery has a nested structure and the parent of the subquery corresponding to the materialized view has a subquery (parent subquery) related to join processing with a table in the database in which the materialized view is arranged: Calculation cost is 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 by narrowing down the data by the parent subquery.

  In order to consider the above case, the calculation unit 225 sequentially refers to a subtree that includes the common subtree, going back from the common subtree to the direction of the root, for each query, and places the materialized view. Search for the range that is complete only with the tables in the Then, the calculation unit 225 refers to the number of records or the data amount of the subtree if there is a subtree that includes the common subtree as a result of the search and completes only with the table in the database in which the materialized view is arranged. And 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 reference to the number of records to be referred to when calculating the prediction value for the calculation cost. Replace the sum of the number of records by the sum of the number of records replaced with the number of records acquired from the materialized view. Subsequently, for the communication cost, the calculating unit 225 substitutes the number of records or the amount of data to be referred to in calculating the predicted value 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 that does not exceed the storage capacity of the storage unit 21 of the database management device 2, the creation unit 226 creates a materialized view that can not be created in the storage unit 21. You may make it to.

  In addition, although the creating unit 226 automatically creates the materialized view in the storage unit 21 of the database management device 2, 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 a materialized view. Then, the creation unit 226 may create a materialized view in response to an instruction to create a 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.

  The creating unit 226 may also evaluate the materialized view already created in the storage unit 21. Then, the creation unit 226 may delete the materialized view from the storage unit 21 according to the first-in first-out method, the Least Recently Used (LRU) method, or the Least Frequently Used (LFU) method. In addition, the creation unit 226 may delete, from the storage unit 21, a materialized view having a relatively small expected value of the reduction amount of the processing cost. By doing this, the database management device 2 deletes the materialized view having a small expected value of the reduction amount of the processing cost from the storage unit 21 and the materialized view 21 has a large expected value of the reduction amount of the processing cost. Can be arranged efficiently.

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

  When the materialized view is created, the updating unit 227 updates the physical model PM and the logical model LM so that the user of the executing unit 222 and the user terminal 3 can refer to the created materialized view. Further, the updating unit 227 may associate the information indicating that the materialized view is created with the information indicating the subquery corresponding to the materialized view, and may output the information to the user terminal 3. Thereby, when the user of the user terminal 3 generates a query sentence including the subquery, the user may specify a materialized view instead of the subquery to shorten the processing time of the query sentence in the execution unit 222. it can.

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

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

Subsequently, the calculation unit 225 calculates the processing cost of the identified subquery and the processing cost when the subquery is replaced with a materialized view (S30).
Subsequently, among the identified subqueries, the creation unit 226 creates materialized views in order from the subquery in which the expected value of the reduction amount of the processing cost is large by replacing with the materialized view (S40).

  Subsequently, the creation unit 226 determines whether or not there is a storable area of the materialized view to be created next in the storage unit 21 of the database management device 2 (S50). If the creating unit 226 determines that there is a storable area, it shifts the process to S40 and creates a materialized view to be created next. If the creating unit 226 determines that there is no storage area, the processing according to the present flowchart ends.

[Effect in the first embodiment]
As described above, the database management device 2 according to the first embodiment acquires a plurality of query sentences corresponding to at least one of the plurality of databases 11, and commonly uses at least a part of the query sentences of the plurality of query sentences. Among the included subqueries, subqueries that reference a plurality of databases 11 are identified. Then, the database management device 2 creates a materialized view corresponding to the identified 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 simultaneous reference to a plurality of databases 11, and can improve the query statement execution performance in an environment using any plurality of databases.

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

  FIG. 6 is a diagram showing the 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 sentences stored in the storage unit 21 at predetermined time intervals and an execution plan corresponding to the inquiry sentence. Then, the migration control unit 228 identifies the usage status of the plurality of tables based on the acquired plurality of inquiry sentences and the execution plan.

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

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

  When the migration of the migration target table by the migration control unit 228 is completed, the updating 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 this, the database management device 2 can arrange the table in an appropriate database based on the usage of the table. As a result, the database management device 2 increases the chances of causing the database 11 to execute the query statement, so the amount of data communication at the time of execution of the query statement can be reduced and the query statement execution performance can be improved.

  After the migration control unit 228 completes migration of the migration target table and the updating unit 227 updates the storage destination of the migration target table indicated by the physical model, the acquiring unit 223 executes a plurality of inquiry statements and execution from the storage unit 21. Get a plan.

The identifying unit 224 identifies sub-queries that reference the plurality of databases 11 based on the plurality of query sentences acquired after the storage destination of the migration target table indicated by the physical model is updated by the updating unit 227.
Then, the creating unit 226 creates a materialized view corresponding to the subquery identified by the identifying unit 224 referring to the plurality of databases 11.

[Effect in Second Embodiment]
As described above, the database management device 2 according to the second embodiment migrates to the database 11 different from the database 11 currently stored among the plurality of databases 11 based on the usage status of the plurality of tables. The table is identified 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 plurality of acquired query statements. Identify the subquery to be created, and create a materialized view corresponding to the subquery.

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

  As mentioned above, although this invention was demonstrated using embodiment, the technical scope of this invention is not limited to the range as described in the said embodiment. It is apparent to those skilled in the art that various changes or modifications can be added to the above embodiment. For example, the above embodiments may be combined. Further, in particular, the specific embodiment of the distribution and integration of the devices is not limited to the one illustrated above, but all or part of the embodiments according to various additions or the like, or according to the functional load. It can be configured to be distributed or integrated functionally or physically in units.

  For example, in the above-described embodiment, although the database management device 2 performs execution management and transition control of a query statement, 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 the query statement and transition control of the table.

DESCRIPTION OF SYMBOLS 1 ... Storage device, 11 ... Database, 2 ... Database management device, 21 ... Storage part, 22 ... Control part, 221 ... Reception part, 222 ... Execution part, 223 ... acquisition unit, 224 ... identification unit, 225 ... calculation unit, 226 ... creation unit, 227 ... update unit, 228 ... transition control unit, 3 ... user terminal, S ... Virtual database system

Claims (8)

A storage unit that stores a physical model that manages the storage destination of each of a plurality of tables included in each of a 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 for acquiring a plurality of query sentences corresponding to at least one of the plurality of databases;
An identifying unit that identifies, based on the logical model and the physical model, among the subqueries commonly included in at least a part of the plurality of acquired query sentences, a subquery that refers to the plurality of databases; When,
A creation unit that creates a physical view corresponding to the identified subquery;
An updating unit that updates the logical model and the physical model so that the user can refer to the created view when the view is created;
Database management device comprising: Among the plurality of databases, a table to be migrated to a database different from the currently stored database is identified as a migration target table based on the usage status of the plurality of tables, and the identified migration target table is a migration destination And a migration control unit for migrating to a database of
The updating unit updates the storage destination of the migration target table indicated by the physical model to the database of the migration destination,
The identification unit completes the migration of the migration target table by the migration control unit, and updates the storage destination of the migration target table indicated by the physical model, based on the plurality of acquired inquiry statements. Identify subqueries that reference multiple said databases,
The database management device according to claim 1. The system further includes a calculation unit that calculates the processing cost of the identified subquery and the processing cost of the view when the subquery is replaced with the view,
The creation unit is expected to reduce the processing cost by replacing the view with the view based on the calculated process cost of the subquery and the process cost of the view when the subquery is replaced with the view. Create the view in descending order of the value,
The database management device according to claim 1. The calculation unit, when creating the physical view in any of the plurality of databases, executes the query statement corresponding to the view between the database management device and the plurality of databases. Calculate the forecast value of traffic volume,
The creation unit creates the physical view in the database in which the calculated predicted value relatively decreases.
The database management device according to claim 3. The calculation unit specifies, for each of a plurality of the query sentences, a range in which processing is performed in the database in which a view corresponding to the subquery is created, and based on data corresponding to the specified range, Calculating a predicted value, and correcting the predicted value of the communication amount 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 communication amount after correction relatively decreases.
The database management device according to claim 4. The creation unit creates the physical view in a storage unit of the database management device.
The database management device according to any one of claims 1 to 5. Computer runs,
Obtaining a plurality of query statements corresponding to at least one of a plurality of databases;
Acquisition based on the physical model that manages the storage destination of each of the plurality of tables included in each of the plurality of databases, and the logical model that manages the virtual tables referenced by the user, including the view of the tables Identifying a subquery that refers to a plurality of the databases among subqueries commonly included in at least a part of the plurality of the query statements.
Creating a physical view corresponding to the identified subquery;
Updating the logical model so that the user can refer to the created view when the view is created;
Database management method comprising: Computer,
An acquisition unit that acquires a plurality of query statements corresponding to at least one of a plurality of databases;
Acquisition based on the physical model that manages the storage destination of each of the plurality of tables included in each of the plurality of databases, and the logical model that manages the virtual tables referenced by the user, including the view of the tables An identifying unit that identifies a subquery that refers to a plurality of the databases among subqueries commonly included in at least a part of the plurality of the query statements;
A creation unit that creates a physical view corresponding to the identified subquery;
An updating unit that updates the logical model so that the user can refer to the created view when the view is created;
Database management program to act as.

Images