JP6971929B2 - Inquiry statement output device and inquiry statement output method - Google Patents

Description

The present invention relates to a query statement output device and a query statement output method that output query statements that can be executed in a database system.

Conventionally, in a relational database, for the purpose of reducing processing costs, a materialized view, which is a physical view corresponding to processing commonly performed in a plurality of queries, has been created (for example, a patent). See Document 1).

Japanese Unexamined Patent Publication No. 2006-343798

It is preferable to create the materialized view corresponding to the process having a high processing cost among the processes common to a plurality of execution plans executed in the database system. For this reason, it is performed to identify common processes in a plurality of execution plans. However, in the execution plan, the description may be different even if it is a common process. Moreover, the description may be the same even if the processing is different. Since there are many-to-many processes and execution plans in this way, there arises a problem that it is difficult to identify common processes based on the execution plans.

Therefore, the present invention has been made in view of these points, and an object of the present invention is to provide a query statement output device and a query statement output method that enable identification of common processes in a plurality of execution plans. ..

The query statement output device according to the first aspect of the present invention has a specific unit that specifies a plurality of execution plans executed in a database system, and a plurality of the execution plans specified by the specific unit. Is converted into the query statement based on the relationship between the sequence of character strings included in the query statement and the sequence of character strings included in the query statement in the database system corresponding to the execution plan, and is part of the execution plan. It includes a first conversion unit that converts a certain partial execution plan into the query statement, and an output unit that outputs the query statement converted by the first conversion unit.

The output unit may output a query statement having a high degree of similarity to other query statements among the plurality of query statements converted by the first conversion unit.

The query statement output device further includes a generation unit that generates a conversion model that converts the execution plan into a query statement, and the first conversion unit inputs each of the plurality of execution plans into the conversion model, and the above-mentioned By acquiring the query statement output from the conversion model, each of the plurality of execution plans is converted into a query statement, and each of the plurality of the partial execution plans is input to the conversion model and output from the conversion model. By acquiring the query statement to be performed, each of the plurality of partial execution plans may be converted into a query statement.

The query statement output device further includes a second conversion unit that converts the query statement into an execution plan, the generation unit generates the conversion model, and the execution unit specified by the specific unit for the conversion model. By inputting a plan, an inquiry statement is acquired, and by inputting the inquiry statement into the second conversion unit, an execution plan corresponding to the inquiry statement is acquired, and the execution plan specified by the specific unit is obtained. , The degree of similarity with the execution plan corresponding to the conversion model may be calculated, and the conversion model having a relatively high degree of similarity may be selected while changing the setting information in the conversion model.

The inquiry statement output device further includes a second conversion unit that converts the inquiry statement into an execution plan, and the generation unit inputs the inquiry statement to the second conversion unit to provide an execution plan corresponding to the inquiry statement. Is obtained, and the obtained execution plan is input to the conversion model to acquire a query statement, and the query statement input to the second conversion unit is similar to the query statement acquired from the conversion model. A conversion model having a relatively high degree of similarity may be selected while calculating the degree and changing the setting information in the conversion model.

The second conversion unit may convert the query statement into an execution plan by causing the database system to generate an execution plan corresponding to the query statement.
The specific unit may specify a plurality of execution plans executed in the database system and having a relatively high execution cost.

In the query statement output method according to the second aspect of the present invention, a step of specifying a plurality of execution plans executed by a computer and executed in a database system, and the specified plurality of the execution plans are combined with the execution plan. Is converted into the query statement based on the relationship between the sequence of character strings included in the query statement and the sequence of character strings included in the query statement in the database system corresponding to the execution plan, and is part of the execution plan. It includes a step of converting a certain partial execution plan into the query statement and a step of outputting the query statement converted from the execution plan.

According to the present invention, there is an effect that a common process can be specified in a plurality of execution plans.

It is a figure which shows the outline of the inquiry sentence output device which concerns on this embodiment. It is a figure which shows the structure of the inquiry sentence output device which concerns on this embodiment. It is a figure which shows an example of an inquiry sentence. It is a figure which shows the execution plan corresponding to the query statement shown in FIG. It is a figure which shows the execution plan tree corresponding to the execution plan shown in FIG. It is a figure which shows the execution plan character string corresponding to the execution plan tree shown in FIG. It is a figure which shows the selection example of the transformation model. It is a figure which shows the other selection example of the transformation model. It is a flowchart which shows the flow of the process in the inquiry sentence output apparatus which concerns on this embodiment.

[Overview of inquiry message output device 1]
FIG. 1 is a diagram showing an outline of the inquiry text output device 1 according to the present embodiment. The query statement output device 1 is a computer that converts an execution plan generated by the database system 2 into a query statement and outputs it.

The inquiry text output device 1 is communicably connected to the database system 2 via a communication network such as a LAN (Local Area Network) or the Internet. The query statement output device 1 acquires an execution plan corresponding to the query statement executed in the database system 2 from the database system 2 ((1) in FIG. 1).

The query statement output device 1 converts the acquired execution plan into a query statement based on the conversion model that converts the execution plan into a query statement ((2) in FIG. 1). The query statement output device 1 uses a query statement having a high degree of similarity to other query statements among a plurality of converted query statements as a query statement for which processing that is likely to be suitable for creating a materialized view is executed. Output ((3) in FIG. 1).

Although the execution plan may have different descriptions even if they are the same query statement, the query statement output device 1 can convert a plurality of execution plans including the different description into the same query statement and output the query statement. .. As a result, the user of the query statement output device 1 can specify the processes common to these execution plans as compared with the case where only a plurality of execution plans are referred to.
Hereinafter, the configuration of the inquiry statement output device 1 will be described.

[Configuration example of inquiry message output device 1]
FIG. 2 is a diagram showing a configuration of the inquiry text output device 1 according to the first embodiment.
The inquiry text output device 1 includes a storage unit 11 and a control unit 12.

The storage unit 11 is, for example, a ROM (Read Only Memory), a RAM (Random Access Memory), or the like. The storage unit 11 stores various programs for operating the inquiry statement output device 1. The storage unit 11 provides a query text output program that causes the control unit 12 of the query text output device 1 to function as a specific unit 121, a generation unit 122, a second conversion unit 123, a first conversion unit 124, and an output unit 125, which will be described later. Remember. Further, the storage unit 11 stores the conversion model M, which is a program generated by the generation unit 122 to convert the execution plan into a query statement.

The control unit 12 is, for example, a CPU (Central Processing Unit). The control unit 12 controls the function related to the inquiry statement output device 1 by executing various programs stored in the storage unit 11. By executing the inquiry statement output program, the control unit 12 functions as a specific unit 121, a generation unit 122, a second conversion unit 123, a first conversion unit 124, and an output unit 125. Further, the control unit 12 converts the execution plan into a query statement by executing the conversion model M.

The specifying unit 121 identifies a plurality of execution plans by acquiring from the database system 2 a plurality of execution plans having a relatively high execution cost among the plurality of execution plans executed in the database system 2. For example, the execution plan generated in the database system 2 is associated with an execution cost, and the specifying unit 121 identifies a plurality of execution plans having a relatively high associated execution cost.

Further, the specifying unit 121 identifies the query statements associated with each of the plurality of execution plans by acquiring the query statements corresponding to each of the plurality of execution plans from the database system 2. Here, the execution plan is information generated when the database system 2 executes the process corresponding to the query statement. The inquiry statement is an inquiry statement such as SQL executed in the database system 2.

FIG. 3 is a diagram showing an example of a query statement. FIG. 4 is a diagram showing an execution plan corresponding to the query statement shown in FIG. The character string "Sort" first shown in FIG. 4 and the character string shown after "->" indicate a node as a process executed by a query statement. Further, the character string following the node including the character string "Scan" (hereinafter, also referred to as "Scan node") indicates the table name of the referenced table.

The specific unit 121 generates an execution plan character string representing the tree structure indicated by the execution plan based on the specified execution plan. Specifically, the specifying unit 121 extracts the node indicating the process included in the execution plan and the table name of the table referred to in the process corresponding to the execution plan based on the specified execution plan. The specific unit 121 extracts the tree structure of the query statement corresponding to the execution plan based on the description order of the code indicating the specified execution plan and the extracted node and table name. The specific unit 121 generates an execution plan tree which is a graph of a tree structure.

FIG. 5 is a diagram showing an execution plan tree corresponding to the execution plan shown in FIG. In the execution plan tree, the abbreviations of the processes in each node N1 to N9 are shown in parentheses. For example, the abbreviation of the process "Sort" corresponding to the root node is "S", and the abbreviation of the process "Group Aggregate" is "GA". In the execution plan tree, the table name is shown at the bottom of the Scan node.

The specific unit 121 generates an execution plan character string by scanning the extracted execution plan tree in the order of going and returning from the node that is the root. That is, the specific unit 121 generates a character string in which the abbreviations of the nodes detected when the execution plan tree is scanned from the root node in the order of going and returning are arranged in the order of detection as the execution plan character string. do.

Here, the specific unit 121 assigns a serial number to the nodes whose processes overlap. For example, the node N1 and the node N3 shown in FIG. 5 are both “Sort (S)”. Therefore, in the specific unit 121, the abbreviation of the node N1 is "S1", and the abbreviation of the node N2 is "S2". Further, the specific unit 121 associates the abbreviation corresponding to the Scan node with the table name, the list of column names, and the filter conditions of the table referenced in the Scan node.

The specific unit 121 creates a list of column names by storing the column names detected in each node and the corresponding table names when scanning the execution plan tree in the order of the destinations. Then, when the Scan node related to the table name is detected, the specific unit 121 associates a list of column names associated with the table.
Further, the specific unit 121 associates the filter condition of the table referred to in the Scan node.

FIG. 6 is a diagram showing an execution plan character string corresponding to the execution plan tree shown in FIG. As shown in FIG. 6, in the execution plan character string, it can be confirmed that the abbreviations of the nodes are listed in the order in which the nodes are detected. Further, in FIG. 6, it can be confirmed that the abbreviation of the Scan node is associated with the table name of the referenced table, such as “→ table name [column name list] (filter condition)”.

The specific unit 121 generates an execution plan character string for each of the specified plurality of execution plans. Further, the specifying unit 121 further specifies a partial execution plan that is a part of the execution plan from the specified execution plan, and generates an execution plan character string for the partial execution plan as well.

Specifically, the specific unit 121 extracts a group of nodes under "Next Loop" and "Hash Join" indicating the join of a plurality of tables as a partial execution plan in the execution plan tree. Then, the specific unit 121 generates an execution plan character string for the extracted partial execution plan. When creating the execution plan character string, the specific unit 121 inserts or replaces the reference table name or the like with respect to the abbreviation of the Scan node, so that the character string such as the reference table name or the like is used as the execution plan character string. You may take it in. For example, the specific unit 121 may replace parentheses, commas, etc. with spaces when incorporating a character string such as a referenced table name into the execution plan character string. For example, in the case of the character string "IS → lineitem [l_extendedprice, l_discount, l_orderkey] (l_shipdate> '1995-03-12' :: date)", it is "IS lineitem l_extendedprice l_discount l_orderkey l_shipdate> 1995-03-12 date". It may be converted into a character string.

[Generation of conversion model M]
The generation unit 122 generates a conversion model M that converts an execution plan into a query statement. Specifically, when the generation unit 122 inputs an execution plan character string indicating an execution plan, the generation unit 122 generates a conversion model M that outputs a query statement corresponding to the execution plan character string. For example, the generation unit 122 generates a conversion model M by giving teacher data to a Sequence-to-Sequence model, which is a model that outputs a corresponding symbol string when a symbol string is input, and training the model.

The teacher data is a combination of the input symbol string and the output symbol string. The input symbol string is an execution plan character string generated from the execution plan specified by the specific unit 121, and the output symbol string is a query statement associated with the execution plan.

The generation unit 122 generates a conversion model M, and acquires an inquiry statement by inputting an execution plan specified by the specific unit 121 to the conversion model M. Then, the generation unit 122 acquires the execution plan corresponding to the inquiry statement by inputting the inquiry statement into the second conversion unit 123.

Here, the second conversion unit 123 has a function of converting the query statement into an execution plan. The second conversion unit 123 inputs a query statement into the database system 2 and generates an execution plan corresponding to the query statement. Then, the second conversion unit 123 converts the query statement into an execution plan by acquiring the execution plan from the database system 2.

Subsequently, the generation unit 122 calculates the degree of similarity between the one or more execution plans specified by the specific unit 121 and the one or more execution plans converted from the query statement output from the conversion model M. For example, the generation unit 122 calculates the Levenshtein distance between the execution plan specified by the specific unit 121 and the execution plan converted from the query statement output from the conversion model M. Here, the Levenshtein distance indicates the number of edits required when converting a character string to a character string to be compared, and if it is 0 times, the character string and the character string to be compared match. It shows that there is. Further, the smaller the value of the Levenshtein distance is, the higher the similarity between the character string and the character string to be compared is shown. The generation unit 122 calculates the similarity so that the smaller the calculated Levenshtein distance, the greater the similarity.

The generation unit 122 generates a plurality of conversion models M while changing the setting information in the Sequence-to-Sequence model. Then, the generation unit 122 calculates the similarity between one or more execution plans and one or more execution plans corresponding to the conversion model M for each of the generated plurality of conversion models M, and based on the calculated similarity. And calculate the average value of the degree of similarity. Then, the generation unit 122 selects one conversion model M having a relatively high average value of the calculated similarity.

FIG. 7 is a diagram showing a selection example of the conversion model M. As shown in FIG. 7, the generation unit 122 generates a plurality of conversion models set by different setting information. In the example shown in FIG. 7, the conversion model M1 set by the setting information A and the conversion model M2 set by the setting information B are generated.

The generation unit 122 inputs an execution plan specified by the specific unit 121 into each of the generated conversion models and converts the query statement into an execution plan, and the second conversion unit 123 converts the query statement into an execution plan. Then, as shown in FIG. 7, the generation unit 122 has one or more execution plans specified by the specific unit 121 and one or more converted by the second conversion unit 123, which are input to each of the plurality of conversion models. The average similarity, which is the average of the similarity with the execution plan, is calculated, and the transformation model having the highest average similarity is selected. By doing so, the query statement output device 1 can generate a conversion model M with high conversion accuracy.

As shown in FIG. 7, a plurality of generation units 122 are provided based on the degree of similarity between the one or more execution plans specified by the specific unit 121 and the one or more execution plans converted by the second conversion unit 123. The transformation model M was selected from the transformation models of, but it is not limited to this.

The generation unit 122 acquires the execution plan corresponding to the query statement by inputting the query statement specified by the specific unit 121 into the second conversion unit 123, and inputs the acquired execution plan to the conversion model. By doing so, the query text may be acquired, and the similarity between the query text input to the second conversion unit 123 and the query text acquired from the conversion model may be calculated. Then, the generation unit 122 may select a conversion model having a relatively high degree of similarity calculated while changing the setting information in the conversion model.

FIG. 8 is a diagram showing another selection example of the transformation model M. In the example shown in FIG. 8, similarly to the example shown in FIG. 7, the generation unit 122 generates a plurality of conversion models set by different setting information. In the example shown in FIG. 8, the conversion model M1 set by the setting information A and the conversion model M2 set by the setting information B are generated.

The generation unit 122 converts the query statement that is the generation source into which one or more execution plans specified by the specific unit 121 are generated by the second conversion unit 123 into the execution plan. Then, the generation unit 122 inputs the converted execution plan into each of the generated transformation models and converts them into query statements. Then, as shown in FIG. 8, the generation unit 122 includes one or more query statements converted by the second conversion unit 123 and one or more queries converted by the conversion model, which are input to each of the plurality of conversion models. The average similarity with the sentence is calculated, and the transformation model with the highest average similarity is selected. By doing so, the inquiry sentence output device 1 can generate a conversion model M having high conversion accuracy as in the example shown in FIG. 7.

[Output of inquiry statement]
The first conversion unit 124 describes a plurality of execution plans specified by the specific unit 121 as a sequence of character strings included in the execution plan and a character string included in a query statement in the database system 2 corresponding to the execution plan. Based on the relationship with the sequence, it is converted into a query statement in the database system 2, and the partial execution plan that is a part of the execution plan specified by the specific unit 121 is converted into a query statement. It should be noted that the first conversion unit 124 lacks the column names and filter conditions associated with the table names included in the partial execution plan that are not included in the query statement with respect to the table names included in the query statement. You may add it to the query statement as information.

Specifically, the first conversion unit 124 inputs an execution plan character string corresponding to each of the plurality of execution plans into the conversion model M, and acquires a plurality of query statements output from the conversion model M. Convert each of the execution plans of. Similarly, the first conversion unit 124 inputs an execution plan character string corresponding to each of the plurality of partial execution plans into the conversion model M, and acquires a plurality of query statements output from the conversion model M. Convert each of the partial execution plans into a query statement.

The execution plan and the partial execution plan input by the first conversion unit 124 to the conversion model M may be different from the execution plan and the partial execution plan used to generate the conversion model M. In this case, the specifying unit 121 may newly specify the execution plan and the partial execution plan after the generation unit 122 generates the conversion model M. Then, the first conversion unit 124 may input the execution plan character string corresponding to the newly specified execution plan and the partial execution plan into the conversion model M, and convert these execution plans into the query statement.

The output unit 125 outputs the inquiry statement converted by the first conversion unit 124. Specifically, the output unit 125 displays a query statement having a high degree of similarity to other query statements among the plurality of query statements converted by the first conversion unit 124, provided in the query statement output device 1. It is output to (hidden) or output to a text file and the text file is stored in the storage unit 11. Further, the output unit 125 may output to a management terminal (not shown) that is communicably connected to the inquiry text output device 1.

For example, the output unit 125 compares each of the plurality of query statements converted by the first conversion unit 124 with other query statements converted in the same manner, and if there is a query statement having the same character string, names it. The frequency of appearance is calculated based on the number of query statements that have been identified. Then, the output unit 125 outputs a query statement having a high frequency of appearance as a query statement to be executed, which is likely to be suitable for creating a materialized view.

Further, the output unit 125 calculates the Levenshtein distance between each of the plurality of query statements converted by the first conversion unit 124 and the other plurality of query statements converted in the same manner, and is based on the Levenshtein distance. The total value of the similarity may be calculated. For example, the output unit 125 calculates the total value of the Levenshtein distances with each of the other plurality of query statements as the total value of the similarity. Then, the output unit 125 may output a query statement having a high total similarity value as a query statement to be executed, which is likely to be suitable for creating a materialized view.

Further, the output unit 125 compares the similarity of each of the one or more subquery statements converted from the partial execution plan, and the processing is likely to be suitable for creating the materialized view of the subquery statement having the similarity degree. It may be output as a subquery statement corresponding to.

[Process flow in inquiry message output device 1]
Subsequently, the flow of processing in the inquiry statement output device 1 will be described. FIG. 9 is a flowchart showing a processing flow in the inquiry text output device 1 according to the present embodiment.

First, the specifying unit 121 specifies a plurality of execution plans by acquiring a plurality of execution plans having relatively high execution costs, and also specifies a partial execution plan included in the execution plan (S1).
Subsequently, the generation unit 122 generates the conversion model M based on the execution plan specified in S1 (S2). The generation unit 122 may determine whether or not the conversion model M has already been generated. Then, if the generation unit 122 determines that the conversion model M has already been generated, the conversion model M may not be generated.

Subsequently, the first conversion unit 124 converts the execution plan and the partial execution plan into the query statement by inputting the execution plan and the partial execution plan into the conversion model M and acquiring the query statement from the conversion model M (the execution plan and the partial execution plan are converted into the query statements). S3).

Subsequently, the output unit 125 outputs a query statement having a high degree of similarity to the other query statements among the plurality of query statements converted in S3 (S4).

[Effects in this embodiment]
As described above, the query statement output device 1 according to the present embodiment converts a plurality of execution plans executed in the database system 2 into query statements in the database system, and a partial execution plan that is a part of the execution plan. Is converted into a query statement, and the converted query statement is output. By doing so, the user of the query statement output device 1 can specify the processes common to these execution plans as compared with the case of referring only to a plurality of execution plans.

Further, the query statement output device 1 identifies a plurality of execution plans executed in the database system and having a relatively high execution cost, and outputs a query statement corresponding to the execution plan. By doing so, the output query statement becomes a query statement with a relatively high execution cost. Therefore, the user of the inquiry statement output device 1 can specify the process corresponding to the effective materialized view based on the inquiry statement.

Further, the inquiry statement output device 1 outputs an inquiry statement having a high degree of similarity to other inquiry statements among the plurality of converted inquiry statements. By doing so, the user of the query statement output device 1 can specify a subquery statement corresponding to the process suitable for creating the materialized view from the output query statement.

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 embodiment of the distribution / integration of the apparatus is not limited to those shown above, and all or a part thereof may be arbitrary according to various additions or the like, or according to the functional load. It can be functionally or physically distributed / integrated in units.

1 ... Inquiry text output device, 11 ... Storage unit, 12 ... Control unit, 121 ... Specific unit, 122 ... Generation unit, 123 ... Second conversion unit, 124 ... 1st conversion unit, 125 ... output unit, 2 ... database system

Claims (8)

A specific part that identifies multiple execution plans executed in the database system,
For the plurality of execution plans specified by the specific unit, the relationship between the sequence of character strings included in the execution plan and the sequence of character strings included in the query statement in the database system corresponding to the execution plan. Based on the first conversion unit that converts the partial execution plan that is a part of the execution plan into the query statement while converting it into the query statement.
An output unit that outputs a query statement converted by the first conversion unit, and an output unit.
Inquiry statement output device. The output unit outputs a query statement having a high degree of similarity to other query statements among a plurality of query statements converted by the first conversion unit.
The inquiry text output device according to claim 1. It further includes a generator that generates a conversion model that converts the execution plan into a query statement.
The first conversion unit converts each of the plurality of execution plans into a query statement by inputting each of the plurality of execution plans into the conversion model and acquiring the query statement output from the transformation model. At the same time, each of the plurality of partial execution plans is input to the conversion model, and each of the plurality of partial execution plans is converted into a query statement by acquiring the query statement output from the transformation model.
The inquiry text output device according to claim 1 or 2. It also has a second conversion unit that converts the query statement into an execution plan.
The generation unit generates the conversion model, acquires an inquiry statement by inputting the execution plan specified by the specific unit to the conversion model, and inputs the inquiry statement to the second conversion unit. By doing so, the execution plan corresponding to the query statement is acquired, the similarity between the execution plan specified by the specific unit and the execution plan corresponding to the conversion model is calculated, and the setting information in the conversion model is calculated. Select a transformation model with a relatively high degree of similarity calculated while changing
The inquiry text output device according to claim 3. It also has a second conversion unit that converts the query statement into an execution plan.
The generation unit acquires an execution plan corresponding to the inquiry statement by inputting the inquiry statement into the second conversion unit, and inputs the acquired execution plan to the conversion model to input the inquiry statement. The similarity between the query statement acquired and input to the second conversion unit and the query statement acquired from the conversion model is calculated, and the similarity is calculated while changing the setting information in the conversion model. Choose a relatively high transformation model,
The inquiry text output device according to claim 3. The second conversion unit converts the query statement into an execution plan by causing the database system to generate an execution plan corresponding to the query statement.
The inquiry text output device according to claim 4 or 5. The identification unit identifies a plurality of execution plans executed in the database system, which have relatively high execution costs.
The inquiry text output device according to any one of claims 1 to 6. Computer runs,
Steps to identify multiple execution plans performed in the database system,
The query for the specified plurality of execution plans is based on the relationship between the sequence of character strings included in the execution plan and the sequence of character strings included in the query statement in the database system corresponding to the execution plan. A step of converting a partial execution plan that is a part of the execution plan into the query statement while converting it into a statement.
A step to output the query statement converted from the execution plan, and
Query statement output method.

Images