JP2020013448A - Inquiry sentence output apparatus and inquiry sentence output method - Google Patents

Abstract

To enable identifying a common process in a plurality of execution plans.SOLUTION: An inquiry sentence output apparatus 1 comprises: an identification unit 121 that identifies a plurality of execution plans executed in a database system 2; a first conversion unit 124 that converts the plurality of execution plans identified by the identification unit 121 on the basis of a relationship between a sequence of character strings included in the execution plans and a sequence of character strings included in an inquiry sentence in a database system 2 corresponding to the execution plan, and that converts a partial execution plan being a part of the execution plan to the inquiry sentence; and an output unit 125 that outputs the inquiry sentence converted by the first conversion unit 124.SELECTED DRAWING: Figure 2

Description

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

  2. Description of the Related Art Conventionally, in a relational database, for the purpose of reducing processing costs, a materialized view, which is a physical view corresponding to a process commonly performed in a plurality of queries, is created (for example, see Patent Reference 1).

JP 2006-343798 A

  It is preferable that the materialized view is created corresponding to a process having a high processing cost among processes common to a plurality of execution plans executed in the database system. For this reason, a process common to a plurality of execution plans is specified. However, in the execution plan, the description may be different even for a common process. Further, the description may be the same even in different processes. Since the processing and the execution plan are many-to-many as described above, there is a problem that it is difficult to specify a common processing based on the execution plan.

  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 can specify a process common to a plurality of execution plans. .

  A query statement output device according to a first aspect of the present invention includes a specifying unit that specifies a plurality of execution plans executed in a database system, and a plurality of the execution plans specified by the specifying unit. Is converted into the query statement based on the relevance between the arrangement of the character strings included in the query plan and the arrangement of the character strings included in the query statement in the database system corresponding to the execution plan. A first conversion unit that converts a certain partial execution plan into the query sentence; and an output unit that outputs the query sentence converted by the first conversion unit.

  The output unit may output a query sentence having a high degree of similarity to another query sentence among the plurality of query sentences converted by the first conversion unit.

  The query sentence output device further includes a generating unit that generates a conversion model that converts the execution plan into a query sentence, wherein the first conversion unit inputs each of the plurality of execution plans to the conversion model, By obtaining the query sentence output from the conversion model, each of the plurality of execution plans is converted into a query sentence, and each of the plurality of partial execution plans is input to the conversion model and output from the conversion model. Each of the plurality of partial execution plans may be converted into a query statement by acquiring the query statement.

  The query sentence output device further includes a second conversion unit that converts the query sentence into an execution plan, wherein the generation unit generates the conversion model, and the execution model specified by the specification unit for the conversion model. By acquiring a query sentence by inputting a plan, by inputting the query sentence to the second conversion unit, an execution plan corresponding to the query sentence is acquired, and the execution plan identified by the identification unit is obtained. Alternatively, the similarity with the execution plan corresponding to the conversion model may be calculated, and a conversion model having a relatively higher calculated similarity may be selected while changing the setting information in the conversion model.

  The query sentence output device further includes a second conversion unit that converts the query sentence into an execution plan, and the generation unit inputs the query sentence to the second conversion unit to execute the execution plan corresponding to the query sentence. Is obtained, and the obtained execution plan is input to the conversion model to obtain a query sentence. The query sentence input to the second conversion unit is similar to the query sentence obtained from the conversion model. While calculating the degree and changing the setting information in the conversion model, a conversion model having a relatively high calculated similarity may be selected.

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 identification unit may identify a plurality of execution plans executed in the database system, the execution plans of which are relatively high.

  A query statement output method according to a second aspect of the present invention is a computer-implemented step of specifying a plurality of execution plans executed in a database system, and converting the specified plurality of execution plans to the execution plan. Is converted into the query statement based on the relevance between the arrangement of the character strings included in the query plan and the arrangement of the character strings included in the query statement in the database system corresponding to the execution plan. Converting a partial execution plan into the query sentence; and outputting a query sentence converted from the execution plan.

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

It is a figure showing the outline of the inquiry sentence output device concerning this embodiment. It is a figure showing the composition of the inquiry sentence output device concerning this embodiment. It is a figure showing an example of a query sentence. FIG. 4 is a diagram showing an execution plan corresponding to the query sentence shown in FIG. 3. FIG. 5 is a diagram showing an execution plan tree corresponding to the execution plan shown in FIG. 4. FIG. 6 is a diagram showing an execution plan character string corresponding to the execution plan tree shown in FIG. 5. It is a figure showing the example of selection of a conversion model. It is a figure showing other examples of selection of a conversion model. It is a flow chart which shows a flow of processing in an inquiry sentence output device concerning this embodiment.

[Overview of Query Statement Output Device 1]
FIG. 1 is a diagram showing an outline of a query output device 1 according to the present embodiment. The query output device 1 is a computer that converts an execution plan generated by the database system 2 into a query and outputs the query.

  The query 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 sentence output device 1 acquires an execution plan corresponding to the query sentence 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 a conversion model that converts the execution plan into a query statement ((2) in FIG. 1). The query statement output device 1 regards a query statement having a high degree of similarity to another query statement among the 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 for the same query statement, the query statement output device 1 can convert a plurality of execution plans including the different descriptions into the same query statement and output the same. . Thereby, the user of the query output device 1 can specify a process common to these execution plans as compared with a case where only a plurality of execution plans are referred to.
Hereinafter, the configuration of the query output device 1 will be described.

[Configuration Example of Query Statement Output Device 1]
FIG. 2 is a diagram illustrating a configuration of the inquiry sentence output device 1 according to the first embodiment.
The query 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) and a RAM (Random Access Memory). The storage unit 11 stores various programs for causing the query output device 1 to function. The storage unit 11 stores a query output program that causes the control unit 12 of the query output device 1 to function as a specification 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. The storage unit 11 stores a conversion model M generated by the generation unit 122, which is a program for converting an execution plan into a query sentence.

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

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

  In addition, the specifying unit 121 obtains query statements corresponding to each of the plurality of execution plans from the database system 2 to specify the query statement associated with each of the plurality of execution plans. Here, the execution plan is information generated when the database system 2 executes a process corresponding to the query sentence. The query sentence is a query sentence such as SQL executed in the database system 2.

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

  The specifying unit 121 generates an execution plan character string representing a tree structure indicated by the execution plan based on the specified execution plan. Specifically, the specifying unit 121 extracts a node indicating a process included in the execution plan and a table name of a table referred to in the process corresponding to the execution plan, based on the specified execution plan. The specifying unit 121 extracts a tree structure of the query sentence corresponding to the execution plan based on the description order of the codes indicating the specified execution plan and the extracted nodes and table names. The identification unit 121 generates an execution plan tree that is a graph having 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 at the nodes 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, a table name is shown below the Scan node.

  The identifying unit 121 generates an execution plan character string by scanning the extracted execution plan tree from the root node in the preorder and return order. In other words, the identification 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 preorder order and the return order are arranged in the detected order as an execution plan character string. I do.

  Here, the specifying unit 121 assigns a serial number to a node whose processing is duplicated. For example, both the node N1 and the node N3 shown in FIG. 5 are “Sort (S)”. Therefore, the specifying unit 121 sets the abbreviation of the node N1 to “S1” and the abbreviation of the node N2 to “S2”. In addition, the identification unit 121 associates a table name, a column name list, and a filter condition of a table referred to in the Scan node with an abbreviation corresponding to the Scan node.

When scanning the execution plan tree in the destination order, the identification unit 121 creates a list of column names by storing the table names corresponding to the column names detected at each node. Then, when a Scan node related to the table name is detected, the specifying unit 121 associates a list of column names associated with the table.
Further, the specifying unit 121 associates a filter condition of a 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 specifying unit 121 generates an execution plan character string for each of the specified plurality of execution plans. In addition, the specification 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.

  Specifically, the specifying unit 121 extracts, from the execution plan tree, a node group below “Nested Loop” or “Hash Join” indicating a join of a plurality of tables as a partial execution plan. Then, the specifying unit 121 generates an execution plan character string for the extracted partial execution plan. When creating the execution plan character string, the identification unit 121 inserts or replaces the table name or the like of the reference destination with respect to the abbreviation of the Scan node, thereby converting the character string of the reference table name or the like into the execution plan character string. You may take in. For example, the specification unit 121 may replace parentheses, commas, and the like with blanks (spaces) when incorporating a character string such as a table name of a reference destination into an execution plan character string. For example, in the case of the 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 to 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 sentence. Specifically, when an execution plan character string indicating an execution plan is input, the generation unit 122 generates a conversion model M that outputs a query sentence corresponding to the execution plan character string. For example, the generation unit 122 generates the 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 trains the model.

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

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

  Here, the second conversion unit 123 has a function of converting the query sentence into an execution plan. The second conversion unit 123 inputs a query sentence to the database system 2 and causes the database system 2 to generate an execution plan corresponding to the query sentence. 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 a similarity between the one or more execution plans identified by the identification unit 121 and the one or more execution plans converted from the query sentence output from the conversion model M. For example, the generation unit 122 calculates the Levenshtein distance between the execution plan specified by the specification unit 121 and the execution plan converted from the query sentence output from the conversion model M. Here, the Levenshtein distance indicates the number of edits required to convert a character string into a character string to be compared. If the number of edits is 0, the character string matches the character string to be compared. It indicates that In addition, the smaller the value of the Levenshtein distance, the higher the similarity between the character string and the character string to be compared. The generation unit 122 calculates the similarity so that the smaller the calculated Levenshtein distance is, the larger the similarity becomes.

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

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

  The generating unit 122 inputs the execution plan specified by the specifying unit 121 to each of the generated plurality of conversion models, converts the execution plan into a query, and causes the second conversion unit 123 to convert the query into an execution plan. Then, as illustrated in FIG. 7, the generation unit 122 inputs one or more execution plans specified by the specification unit 121 and one or more execution plans 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 similarities with the execution plan, is calculated, and the conversion model having the highest average similarity is selected. By doing so, the query sentence output device 1 can generate the conversion model M with high conversion accuracy.

  As illustrated in FIG. 7, the generation unit 122 may generate a plurality of execution plans based on the similarity between the one or more execution plans identified by the identification unit 121 and the one or more execution plans converted by the second conversion unit 123. The conversion model M is selected from among the conversion models described above, but is not limited thereto.

  The generation unit 122 acquires the execution plan corresponding to the query sentence by inputting the query sentence specified by the identification unit 121 to the second conversion unit 123, and inputs the acquired execution plan to the conversion model. Thus, the query sentence may be acquired, and the similarity between the query sentence input to the second conversion unit 123 and the query sentence acquired from the conversion model may be calculated. Then, the generation unit 122 may select a conversion model whose calculated similarity is relatively high while changing the setting information in the conversion model.

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

  The generation unit 122 causes the second conversion unit 123 to convert the query sentence from which one or more execution plans identified by the identification unit 121 have been generated into an execution plan. Then, the generation unit 122 inputs the converted execution plan to each of the generated plurality of conversion models, and converts the execution plan into a query sentence. Then, as illustrated in FIG. 8, the generation unit 122 includes at least one query sentence 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, which is the average of the similarity with the sentence, is calculated, and the conversion model having the highest average similarity is selected. By doing so, the query sentence output device 1 can generate the conversion model M with high conversion accuracy as in the example shown in FIG.

[Output of query statement]
The first conversion unit 124 converts the plurality of execution plans identified by the identification unit 121 into a list 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 relevance to the arrangement, it is converted into a query sentence in the database system 2 and the partial execution plan specified by the specifying unit 121 and being a part of the execution plan is converted into a query sentence. For the table names included in the query statement, the first conversion unit 124 ran out of column names and filter conditions associated with the table names included in the partial execution plan that were not included in the query statement. It may be added to the inquiry sentence as information.

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

  Note that the execution plan and the partial execution plan input to the conversion model M by the first conversion unit 124 may be different from the execution plan and the partial execution plan used for generating 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 an execution plan character string corresponding to the newly specified execution plan and partial execution plan to the conversion model M, and convert these execution plans into a query sentence.

  The output unit 125 outputs the query sentence converted by the first conversion unit 124. Specifically, the output unit 125 outputs a query sentence having a high similarity to another query sentence among the plurality of query sentences converted by the first conversion unit 124 to a display unit provided in the query sentence output device 1. (Non-display) or output to a text file and store the text file in the storage unit 11. Further, the output unit 125 may output to a management terminal (not shown) communicably connected to the inquiry sentence 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 another query statement that has been similarly converted, and if there is a query statement with the same character string, performs name matching. The appearance frequency is calculated based on the number of the query sentence that has been identified. Then, the output unit 125 outputs the query sentence having a high appearance frequency as a query sentence for which a process highly likely to be suitable for creating the materialized view is executed.

  Further, the output unit 125 calculates the Levenshtein distance between each of the plurality of query sentences converted by the first conversion unit 124 and the other plurality of query sentences similarly converted, and based on the Levenshtein distance. To calculate the total value of the similarities. For example, the output unit 125 calculates the total value of the Levenshtein distances with each of the other plurality of query sentences as the total value of the similarities. Then, the output unit 125 may output a query sentence with a high total value of similarity as a query sentence that 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 processes the subquery statement whose similarity is likely to be suitable for creating a materialized view. May be output as a subquery sentence corresponding to.

[Processing flow in query output device 1]
Next, the flow of processing in the query output device 1 will be described. FIG. 9 is a flowchart illustrating a flow of processing in the inquiry sentence 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 specifies a partial execution plan included in the execution plan (S1).
Subsequently, the generation unit 122 generates a conversion model M based on the execution plan specified in S1 (S2). Note that the generation unit 122 may determine whether the conversion model M has already been generated. Then, when determining that the conversion model M has already been generated, the generation unit 122 may not generate the conversion model M.

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

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

[Effects in the present 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 also executes a partial execution plan which is a part of the execution plan. Is converted to a query sentence, and the converted query sentence is output. In this way, the user of the query output device 1 can specify a process common to these execution plans as compared to a case where only a plurality of execution plans are referred to.

  In addition, the query sentence output device 1 specifies a plurality of execution plans that are executed in the database system and have relatively high execution costs, and outputs a query sentence corresponding to the execution plan. By doing so, the output query statement is a query statement having a relatively high execution cost. Therefore, the user of the query output device 1 can specify a process corresponding to an effective materialized view based on the query.

  Further, the query sentence output device 1 outputs a query sentence having a high degree of similarity to another query sentence among the plurality of converted query sentences. In this way, the user of the query output device 1 can specify a subquery corresponding to a process suitable for creating a materialized view from the output query.

  As described above, the present invention has been described using the embodiments, but the technical scope of the present invention is not limited to the scope described in the above embodiments. It is apparent to those skilled in the art that various changes or improvements can be made to the above embodiment. For example, the above embodiments may be combined. Further, in particular, the specific embodiment of the distribution / integration of the apparatus is not limited to the above illustrated one, and all or a part of the apparatus may be arbitrarily added according to various additions or the like, or according to a functional load. It can be configured to be distributed or integrated functionally or physically in units.

DESCRIPTION OF SYMBOLS 1 ... Inquiry sentence output device, 11 ... Storage part, 12 ... Control part, 121 ... Identification part, 122 ... Generation part, 123 ... Second conversion part, 124 ... 1st conversion unit, 125 ... output unit, 2 ... database system

Claims (8)

A specifying unit that specifies a plurality of execution plans executed in the database system;
The plurality of execution plans identified by the identification unit are associated with a sequence of character strings included in the execution plan and a relationship between the sequence of character strings included in a query sentence in the database system corresponding to the execution plan. A first conversion unit that converts the partial execution plan that is a part of the execution plan into the query statement,
An output unit that outputs the query sentence converted by the first conversion unit;
A query output device comprising: The output unit outputs a query sentence having a high degree of similarity to another query sentence among a plurality of query sentences converted by the first conversion unit.
The query sentence output device according to claim 1. A generating unit that generates a conversion model that converts the execution plan into a query sentence,
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 to the conversion model and acquiring the query statement output from the conversion model. With, each of the plurality of partial execution plans is input to the conversion model, and by acquiring the query sentence output from the conversion model, each of the plurality of partial execution plans is converted into a query sentence,
The query sentence output device according to claim 1. A second conversion unit that converts the query statement into an execution plan;
The generating unit generates the conversion model, obtains a query by inputting the execution plan specified by the specifying unit for the conversion model, and inputs the query to the second conversion unit. Thereby obtaining an execution plan corresponding to the query sentence, calculating a similarity between the execution plan specified by the specifying unit and an execution plan corresponding to the conversion model, and setting information in the conversion model. While selecting a conversion model having a relatively high calculated similarity,
The query output device according to claim 3. A second conversion unit that converts the query statement into an execution plan;
The generation unit obtains an execution plan corresponding to the query sentence by inputting the query sentence to the second conversion unit, and inputs the obtained execution plan to the conversion model to generate the query sentence. Calculating the similarity between the query sentence obtained and input to the second conversion unit and the query sentence obtained from the conversion model, and calculating the similarity while changing the setting information in the conversion model. Select a relatively high conversion model,
The query 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 query output device according to claim 4. The identification unit, executed in the database system, to specify a plurality of execution plans having relatively high execution costs,
The query output device according to any one of claims 1 to 6. Computer runs,
Identifying a plurality of execution plans executed in the database system;
The plurality of identified execution plans are queried based on a relevance between a sequence of character strings included in the execution plan and a sequence of character strings included in a query statement in the database system corresponding to the execution plan. And converting the partial execution plan that is a part of the execution plan into the query statement.
Outputting a query sentence converted from the execution plan;
A query sentence output method comprising:

Images