A connection query statement processing method, device, equipment and storage medium

Abstract

The present invention discloses a connection query statement processing method, device, equipment and storage medium. The original connection query statement is obtained, wherein the original connection query statement is a connection query statement for a data table and a view; whether the original connection query statement meets the preset optimization conditions is determined; if so, the view definition statement corresponding to the original connection query statement is obtained, the view definition statement and the original connection query statement are merged, and the optimized target connection query statement is determined based on the merge processing result, so that when the target connection query statement is executed, the connection operation of the data table is first performed, and then the calculation is performed based on the result set obtained after the connection operation. The technical solution of the embodiment of the present invention greatly reduces the amount of data for group calculation and greatly improves the execution efficiency of the entire connection query statement.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a connection query statement processing method, device, equipment and storage medium. Background Art

[0002] Structured Query Language (SQL) is a special-purpose programming language, a database query and programming language used to access data and query, update and manage relational database systems.

[0003] In Structured Query Language (SQL), joins are generally divided into four categories: inner joins, outer joins, natural joins, and cross joins. Outer joins include left outer joins and right outer joins. In current database technology, to implement a join query between tables and views, the view result set is typically first calculated, then joined with the table data to obtain the query result.

[0004] However, in some cases, for example, if the view contains group calculations, the table data queried by the view is large and there are no filter conditions, then when the view is grouped and calculated, the efficiency of the join query will be low due to the excessive amount of data. Summary of the Invention

[0005] The present invention provides a connection query statement processing method, device, equipment and storage medium to solve the problem of low efficiency of connection query statements between table data and views.

[0006] In a first aspect, an embodiment of the present invention provides a method for processing a connection query statement, comprising:

[0007] Obtaining an original connection query statement, wherein the original connection query statement is a connection query statement for a data table and a view;

[0008] Determine whether the original connection query statement meets the preset optimization conditions;

[0009] If it is satisfied, the view definition statement corresponding to the original connection query statement is obtained, the view definition statement and the original connection query statement are merged, and the optimized target connection query statement is determined according to the merge processing result, so that when the target connection query statement is executed, the data table connection operation is performed first, and then the calculation is performed based on the result set obtained after the connection operation.

[0010] In a second aspect, an embodiment of the present invention provides a connection query statement processing device, comprising:

[0011] A connection query statement acquisition module is used to acquire an original connection query statement, wherein the original connection query statement is a connection query statement for a data table and a view;

[0012] A connection query statement judgment module is used to judge whether the original connection query statement meets the preset optimization conditions;

[0013] A merging processing module is used to obtain a view definition statement corresponding to the original connection query statement when the judgment result of the connection query statement judgment module is satisfied, and merge the view definition statement and the original connection query statement;

[0014] The connection query statement determination module is used to determine the optimized target connection query statement based on the processing results of the merge processing module, so that when the target connection query statement is executed, the data table connection operation is first performed, and then the calculation is performed based on the result set obtained after the connection operation.

[0015] In a third aspect, an embodiment of the present invention provides an electronic device, the electronic device comprising:

[0016] at least one processor;

[0017] and a memory communicatively coupled to the at least one processor;

[0018] The memory stores a computer program that can be executed by at least one processor. The computer program is executed by at least one processor so that the at least one processor can execute the connection query statement processing method of the first aspect.

[0019] In a fourth aspect, an embodiment of the present invention provides a computer-readable storage medium storing computer instructions, which are used to enable a processor to implement the connection query statement processing method of the first aspect when executed.

[0020] The connection query statement processing solution provided by the embodiment of the present invention obtains the original connection query statement, wherein the original connection query statement is a connection query statement for a data table and a view; determines whether the original connection query statement meets the preset optimization conditions; if so, obtains the view definition statement corresponding to the original connection query statement, merges the view definition statement and the original connection query statement, and determines the optimized target connection query statement based on the merge processing result, so that when the target connection query statement is executed, the connection operation of the data table is first performed, and then the calculation is performed based on the result set obtained after the connection operation. By adopting the above technical solution, the original connection query statement that meets the preset optimization conditions and its corresponding view definition statement are merged, and the optimized target connection query statement is determined based on the merge processing result, which greatly reduces the amount of data for group calculation, thereby greatly improving the execution efficiency of the entire connection query statement.

[0021] It should be understood that the content described in this section is not intended to identify the key or important features of the embodiments of the present invention, nor is it intended to limit the scope of the present invention. Other features of the present invention will become readily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS

[0022] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0023] Figure 1 This is a flowchart of a method for processing a connection query statement provided according to the first embodiment of the present invention;

[0024] Figure 2 This is a flowchart of a method for processing a connection query statement provided in accordance with a second embodiment of the present invention;

[0025] Figure 3 This is a structural diagram of a connection query statement processing device provided according to a third embodiment of the present invention;

[0026] Figure 4 This is a schematic diagram of the hardware structure of an electronic device provided in Example 4 of the present invention. DETAILED DESCRIPTION

[0027] In order to enable those skilled in the art to better understand the solutions of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the embodiments described are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts should fall within the scope of protection of the present invention.

[0028] It should be noted that the terms "first," "second," and the like in the specification and claims of the present invention and the accompanying drawings are used to distinguish similar objects and are not necessarily used to describe a specific order or precedence. It should be understood that the terms used in this manner are interchangeable where appropriate, so that the embodiments of the present invention described herein can be implemented in an order other than that illustrated or described herein. In the description of the present invention, unless otherwise specified, "plurality" refers to two or more. "And / or" describes an association relationship between associated objects, indicating that three relationships can exist. For example, "A and / or B" can mean: A exists alone, A and B exist simultaneously, and B exists alone. The character " / " generally indicates that the associated objects are in an "or" relationship. In addition, the terms "including" and "having," and any variations thereof, are intended to cover non-exclusive inclusions. For example, a process, method, system, product, or device that includes a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units that are not explicitly listed or that are inherent to such process, method, product, or device.

[0029] Example 1

[0030] Figure 1 A flowchart of a connection query statement processing method is provided for the first embodiment of the present invention. This embodiment is applicable to the situation where connection query statements between table data and view data are optimized. The method can be executed by a connection query statement processing device. The connection query statement processing device can be implemented in the form of hardware and / or software. The connection query statement processing device can be configured in an electronic device. The electronic device can be configured as a database management system. The electronic device can be composed of two or more physical entities or one physical entity.

[0031] like Figure 1 As shown, the first embodiment of the present invention provides a connection query statement processing method, which specifically includes the following steps:

[0032] S101: Obtain an original connection query statement.

[0033] The original connection query statement is a connection query statement for data tables and views.

[0034] In this embodiment, the join query statement can be a statement in SQL that implements the join query function of data tables and views, for example, select a.c2,v.c1,v.c2 from a,v where a.c1=v.c1 order by a.c2, which means: query the result set of table a and view v with a.c1=v.c1 as the join condition, and sort the data of column c2 in table a.

[0035] S102: Determine whether the original connection query statement meets the preset optimization conditions.

[0036] In this embodiment, before optimizing the original connection query statement, it is necessary to first determine whether the original connection query statement meets the preset optimization conditions, and optimize the original connection query statement that meets the preset optimization conditions. The specific content of the preset optimization conditions may include limitations on the type of clauses in the original connection query statement, the content of the clauses, the type and content of each item, etc.

[0037] S103. If the conditions are met, obtain the view definition statement corresponding to the original connection query statement, merge the view definition statement and the original connection query statement, and determine the optimized target connection query statement based on the merge processing result, so that when the target connection query statement is executed, the connection operation of the data table is first performed, and then the calculation is performed based on the result set obtained after the connection operation.

[0038] In this embodiment, when the original join query statement meets the preset optimization condition, the view definition statement corresponding to the original join query statement may be obtained, and the view definition statement and the original join query statement may be analyzed and merged for processing.

[0039] For example, a view definition statement can be interpreted as a definition statement for creating a view. It is generally placed before the original join query statement. For example, statement 1, create view v as select b.c1, sum(b.c2) c2 from b where b.c1 < 10 group by b.c1, means: Create view v. Statement 2, select a.c2, v.c1, v.c2 from a, v where a.c1 = v.c1 order by a.c2, means: Query the result set of table a and view v with the join condition a.c1 = v.c1, and sort the data in column c2 of table a. Statement 1 is the definition statement for view v corresponding to statement 2.

[0040] In this embodiment, the connection query statement obtained after the original connection query statement is optimized through merging processing can be determined as the target connection query statement, and setting processing can also be performed on the basis of the connection query statement obtained after optimization to obtain the target connection query statement for execution. The specific method of setting processing is not limited. Among them, the result set can be interpreted as an object returned by the query result in the data, which is generally a table. Compared with the initial connection query statement, the advantage of the target connection query statement can be explained as follows: when the target connection query statement is executed, the corresponding table data in the target connection query statement can be connected first, and then the result set after connection can be calculated accordingly according to the target connection query statement. After the table data is connected, the data in the result set obtained is less than the table data before the connection, which can greatly reduce the amount of data for group calculation and avoid consuming the main execution time of the query statement in the group calculation process in the view definition statement.

[0041] In an embodiment of the present invention, an original connection query statement is first obtained, wherein the original connection query statement is a connection query statement for a data table and a view, and then it is determined whether the original connection query statement meets the preset optimization conditions. If so, the view definition statement corresponding to the original connection query statement is obtained, the view definition statement and the original connection query statement are merged, and the optimized target connection query statement is determined based on the merge processing result, so that when the target connection query statement is executed, the connection operation of the data table is first performed, and then the calculation is performed based on the result set obtained after the connection operation. In the technical solution of the embodiment of the present invention, the original connection query statement that meets the preset optimization conditions and its corresponding view definition statement are merged, and the optimized target connection query statement is determined based on the merge processing result, which greatly reduces the amount of data for group calculation, thereby greatly improving the execution efficiency of the entire connection query statement.

[0042] In some embodiments, after determining the optimized target connection query statement, the target connection query statement may be used to replace the original connection query statement, and the target connection query statement may be re-analyzed in terms of lexical structure, syntax, and semantics before being executed.

[0043] Example 2

[0044] Figure 2 This is a flowchart of a method for processing a connection query statement provided in the second embodiment of the present invention. The technical solution of the embodiment of the present invention is further optimized on the basis of the above-mentioned optional technical solutions, and more reasonably merges and processes the original connection query statements.

[0045] Specifically, the view definition statement and the original connection query statement are merged, including: replacing the view in the source necklace table of the original connection query statement with the source necklace table in the view definition statement to obtain a first statement; replacing the query items of the view columns in the first statement with the query items corresponding to the corresponding columns in the view definition statement to obtain a second statement; if the second statement contains a filter condition, replacing the filter condition in the second statement with the target filter condition to obtain a third statement, wherein the target filter condition is the result of the AND of the filter condition in the second statement and the filter condition in the view definition statement; presetting the grouping items in the view definition statement to obtain the target grouping items, and adding the target grouping items to the third statement to obtain a fourth statement; and determining the merge processing result according to the fourth statement. The advantage of this setting is that the table-to-table connection operation is performed first and then the group calculation is performed, which greatly reduces the amount of data for the group calculation.

[0046] like Figure 2 As shown, a connection query statement processing method provided by the second embodiment of the present invention specifically includes the following steps:

[0047] S201: Obtain an original connection query statement.

[0048] The original connection query statement is a connection query statement for data tables and views.

[0049] S202: Determine whether the original connection query statement meets the preset optimization conditions. If so, execute step 203; otherwise, end the process.

[0050] Optionally, the preset optimization condition is based on at least one of the following settings: the type of each item in the query necklace table of the original connection query statement, whether there is a second preset pseudo column item or a first preset clause in the original connection query statement, whether there is a second preset clause in the original connection query statement, whether there is an equal value filtering condition or an equal value joining condition in the original connection query statement, the number of views included in the source necklace table of the original connection query statement, and the content of the views included in the source necklace table of the original connection query statement.

[0051] In this embodiment, the preset optimization conditions can be based on: the types of items in the query necklace table of the original connection query statement, whether there is a second preset pseudo-column item or a first preset clause in the original connection query statement, whether there is a second preset clause in the original connection query statement, whether there is an equal-value filtering condition or an equal-value connection condition in the original connection query statement, the number of views included in the source necklace table of the original connection query statement, and the content of the views included in the source necklace table of the original connection query statement, etc., to limit the original connection statement, filter out the original connection statement that meets the preset optimization conditions, and perform the next step of merging processing on it.

[0052] Specifically, the query items of the original join query statement may include: complex query expressions, subqueries, set functions or columns, etc. The original join query statement may include: grouping clauses, grouping column filtering clauses, filter clauses, hierarchical query clauses, fixed range acquisition clauses or pseudo columns, etc. Among them, in this embodiment, the grouping clause can be interpreted as a clause containing groupby; the grouping column filtering clause can be interpreted as a clause containing having; the filter clause can be interpreted as a clause containing where; the hierarchical query clause can be interpreted as a clause containing connect by; the fixed range acquisition clause can be interpreted as a clause containing top; and the pseudo column can be interpreted as a rownum column or a rowid column. Exemplarily, an equal value filter condition can be interpreted as the existence of a filter clause in the original join query statement and the clause contains an equal value filter condition, such as: where a.c1=v.c1; and an equal value join condition can be interpreted as the existence of a join clause in the original join query statement and the clause contains an equal value join condition, such as: from a join b on a.c1=b.c1.

[0053] Furthermore, the preset optimization conditions include meeting all of the following items simultaneously:

[0054] 1) The type of each item in the query necklace table of the original join query statement is column.

[0055] Specifically, the memory of the original connection query statement is traversed. After obtaining the query necklace table of the original connection query statement, the type of each item in the query necklace table of the original connection query statement can be determined. If the type of each item is a column, the preset optimization condition is met and the next step of judgment can be performed.

[0056] 2) The original connection query statement does not contain the second preset pseudo column item and the get fixed range clause, wherein the second preset pseudo column item indicates the position in the result set.

[0057] Specifically, the memory of the original connection query statement is traversed. If the second preset pseudo column item and the fixed range acquisition clause do not exist in the original connection query statement, the preset optimization condition is met and the next step of judgment can be performed.

[0058] Exemplarily, the memory structure of the original connection query statement can be used to determine whether the original connection query statement contains a second preset pseudo column item and a fixed range clause, where the second preset pseudo column item can be interpreted as a rownum item, and the query returns a sequence of rows in the result set. Users can query the pseudo column but cannot insert, update, or delete the value of the pseudo column; the fixed range clause can be interpreted as a TOP clause, which obtains a fixed range result set, such as select TOP10from T1, which means obtaining the first 10 rows of data.

[0059] 3) The original join query statement does not contain a grouping clause, a grouping column filter clause, or a hierarchical query clause.

[0060] Specifically, the memory of the original connection query statement is traversed. If the original connection query statement does not contain a grouping clause, a grouping column filter clause, or a hierarchical query clause, the preset optimization condition is met and the next step of judgment can be performed.

[0061] Exemplarily, by traversing the memory of the original join query statement, a grouping necklace table in the original join query statement can be obtained. If the length of the grouping necklace table is zero, it indicates that there is no grouping clause in the original join query statement. By traversing the memory of the original join query statement, a grouping column filter necklace table in the original join query statement can be obtained. If the length of the grouping column filter necklace table is zero, it indicates that there is no grouping column filter clause in the original join query statement. By traversing the memory of the original join query statement, a hierarchical query memory in the original join query statement can be obtained. If the hierarchical query memory is empty, it indicates that there is no hierarchical query clause in the original join query statement. It is worth noting that the second preset clause above includes: a grouping clause, a grouping column filter clause, and a hierarchical query clause, etc.

[0062] 4) There are equal-value filtering conditions or equal-value joining conditions in the source item and filter necklace table of the original join query statement.

[0063] Specifically, the memory of the original connection query statement is traversed. If the connection condition or the filter condition exists in the source item and the filter necklace table of the original connection query statement, and the connection clause has a non-empty and equal-valued condition, or the filter clause has a non-empty and equal-valued condition, then the preset optimization condition is met and the next step can be judged.

[0064] 5) The source necklace table of the original join query statement contains a view, the view definition statement contains a group by clause, the view definition statement does not contain a second preset pseudo column item, a fixed range get clause, and a hierarchical query clause, and the types of the items in the source necklace table of the view definition statement are all table types.

[0065] Specifically, traverse the memory of the original connection query statement. If the source necklace table of the original connection query statement contains only one view, obtain the memory of the view definition statement corresponding to the view. If the definition statement of the view contains a grouping clause, and there is no second preset pseudo-column item, obtain a fixed range clause and a hierarchical query clause, and each item in the source necklace table of the view definition statement is a table type, then the preset optimization conditions are met.

[0066] S203: Obtain a view definition statement corresponding to the original connection query statement, and replace the view in the source necklace table of the original connection query statement with the source necklace table in the view definition statement to obtain a first statement.

[0067] Specifically, if the original connection statement meets the preset optimization conditions, the source necklace table of the original connection query statement and the source necklace table of the view definition statement corresponding to the original connection query statement are obtained. If there is a view in the source necklace table of the original connection query statement, the view in the source necklace table of the original connection query statement is replaced with the source necklace table in the view definition statement. The statement after this replacement is the first statement.

[0068] For example, if the source item of the original join query statement is: from a,v, and the source item of the view definition statement is: from b, the replacement statement is: from a,b. If the original join query statement is: select / *+ENABLE_HASH_JOIN(1)* / a.c2,v.c1,v.c2 from a,v where a.c1=v.c1 order by a.c2, and the view definition statement is: create view v as select / *+INDEX(b,ind)* / b.c1,sum(b.c2)c2 from b where b.c1<10group by b.c1, the first statement after replacement is: select / *+ENABLE_HASH_JOIN(1)* / a.c2,v.c1,v.c2 from a,b where a.c1=v.c1 order by a.c2.

[0069] S204: Replace the query items of the view columns in the first statement with the query items corresponding to the corresponding columns in the view definition statement to obtain a second statement.

[0070] Specifically, after obtaining the first statement, the query necklace table of the first statement is traversed and analyzed from the memory. If the query necklace table of the first statement references a view column, the query item corresponding to the view column in the first statement is replaced with the query item corresponding to the corresponding column in the view definition statement, and the second statement can be obtained.

[0071] For example, if the first statement is: select a.c2,v.c1,v.c2 from a join b on a.c1=v.c1, the query item in the first statement is: select a.c2,v.c1,v.c2, and the view definition statement is: create view vas select b.c1,sum(b.c2)c2 from b where b.c1<10group by b.c1, the second statement after replacement is: select a.c2,b.c1,sum(b.c2)from a join b on a.c1=v.c1.

[0072] Optionally, after replacing the query item of the view column in the first statement with the query item corresponding to the corresponding column in the view definition statement, it also includes traversing and analyzing the source necklace table of the first statement from the memory. If the source necklace table of the first statement references the view column, the view column is replaced with the query item corresponding to the corresponding column in the view definition statement.

[0073] For example, if the first statement is: select a.c2,b.c1,sum(b.c2)from a join b on a.c1=v.c1, the source item in the first statement is: from a join b on a.c1=v.c1, and the view definition statement is: createview v as select b.c1,sum(b.c2)c2 from b where b.c1<10group by b.c1, the second statement after replacement is: select a.c2,b.c1,sum(b.c2)from a join b on a.c1=b.c1.

[0074] Optionally, after replacing the query item of the view column in the first statement with the query item corresponding to the corresponding column in the view definition statement, it also includes traversing and analyzing the filter necklace table of the first statement from the memory. If the view column is referenced in the filter necklace table of the first statement, the view column is replaced with the query item corresponding to the corresponding column in the view definition statement.

[0075] For example, if the first statement is: select a.c2,b.c1,sum(b.c2)from a,b where a.c1=v.c1, the filter clause in the first statement is: where a.c1=v.c1, and the view definition statement is: create view v asselect b.c1,sum(b.c2)c2 from b where b.c1<10group by b.c1, the second statement after replacement is: select a.c2,b.c1,sum(b.c2)from a,b where a.c1=b.c1.

[0076] Optionally, after replacing the query item of the view column in the first statement with the query item corresponding to the corresponding column in the view definition statement, it also includes traversing and analyzing the sorting necklace table of the first statement from the memory. If the sorting necklace table of the first statement references the view column, the view column is replaced with the query item corresponding to the corresponding column in the view definition statement.

[0077] For example, if the first statement is: select a.c2,b.c1,sum(b.c2)from a,b where a.c1=b.c1 order by v.c1, the sort clause in the first statement is: order by v.c1, and the view definition statement is: createview v as select b.c1,sum(b.c2)c2 from b where b.c1<10group by b.c1, the second statement after replacement is: select a.c2,b.c1,sum(b.c2)from a,b where a.c1=b.c1 order by b.c1.

[0078] S205. When the second statement contains a filter clause, the filter condition in the second statement is replaced with the target filter condition to obtain a third statement, wherein the target filter condition is the result of the AND of the filter condition in the second statement and the filter condition in the view definition statement.

[0079] Specifically, after obtaining the second statement, if it contains a filter clause, the filter conditions in the second statement's filter clause are ANDed with those in the view definition statement's filter clause. This operation condition becomes the target filter condition, and the filter condition in the second statement is replaced with the target filter condition, thereby obtaining the third statement. Note that if the second statement does not contain a filter clause, this step is not performed; the third statement is equivalent to the second statement.

[0080] For example, if the second statement is: select a.c2,b.c1,sum(b.c2)from a,b where a.c1=b.c1 order by a.c2, the view definition statement is: create view v as select b.c1,sum(b.c2)c2from b where b.c1<10group by b.c1, the replaced third statement is: select a.c2,b.c1,sum(b.c2)from a,b where a.c1=b.c1&&b.c1<10order by a.c2.

[0081] S206: Perform preset processing on the grouping items in the view definition statement to obtain a target grouping item, and add the target grouping item to the third statement to obtain a fourth statement.

[0082] Specifically, after obtaining the third statement, the grouping items in the view definition statement are pre-processed to obtain the target grouping items, which are then added to the third statement to obtain the fourth statement. Pre-processing includes replacing, modifying, or deleting content in the original join query statement or view definition statement.

[0083] Furthermore, the grouping items in the view definition statement are preset to obtain target grouping items, including: traversing the non-view source item data table included in the source item of the third statement, and adding the first preset pseudo column of the traversed non-view source item data table to the grouping item in the view definition statement to obtain a first grouping item, wherein the first preset pseudo column is used to represent the physical address; adding the column of the non-view source item data table involved in the query item of the third statement to the first grouping item to obtain a second grouping item; adding the column of the non-view source item data table in the sorting item of the third statement that does not currently exist in the second grouping item to the second grouping item to obtain the target grouping item.

[0084] Specifically, the preset processing may include the following steps: first, traverse the non-view source item data table included in the source item of the third statement, and add the first preset pseudo column of the table to the grouping item of the view definition statement, thereby obtaining a first grouping item, wherein the first preset pseudo column can be interpreted as: a rowid pseudo column, used to represent a physical address, for example: adding the rowid column of table a to the grouping item of the view definition statement, the grouping item of the view definition statement changes from group by b.c1 to group by b.c1, a.rowid; then add the column of the non-view source item data table involved in the query item of the third statement to the first grouping item to obtain a second grouping item, for example: if the column of the non-view source item data table involved in the query item of the third statement is a.c2, then the second grouping item is group by b.c1, a.rowid, a.c2; then add the column of the non-view source item data table of the sorting item of the third statement that does not currently exist in the second grouping item to the second grouping item to obtain a target grouping item, for example, if the sorting item of the third statement is: order by a.c2, the second grouping item is: group by b.c1,a.rowid,a.c2, then the target grouping item is: group by b.c1,a.rowid,a.c2.

[0085] For example, if the target grouping item is: group by b.c1,a.rowid,a.c2, the third statement is: selecta.c2,b.c1,sum(b.c2)from a,b where a.c1=b.c1&&b.c1<10order by a.c2. Add the target grouping item to the third statement, and the fourth statement is: select a.c2,b.c1,sum(b.c2)from a,b wherea.c1=b.c1&&b.c1<10group by b.c1,a.rowid,a.c2 order by a.c2.

[0086] S207: Determine the merging result according to the fourth statement.

[0087] Specifically, after obtaining the fourth statement, the merging processing result can be determined according to the fourth statement.

[0088] Optionally, determining the merge processing result based on the fourth statement includes: when the view definition statement contains grouping column filter items and / or prompt items, adding the contained grouping column filter items and / or prompt items to the fourth statement to obtain a fifth statement; and determining the merge processing result based on the fifth statement.

[0089] Specifically, if the view definition statement contains grouping column filter items and / or prompt items, that is, the grouping column filter items and / or prompt necklace list of the view definition statement is not empty, it is necessary to traverse the chain list and add the grouping column filter items and / or prompt items to the fourth statement to obtain the fifth statement, which is the result after the merge processing.

[0090] For example, if the fourth statement is: select / *+ENABLE_HASH_JOIN(1)* / a.c2,b.c1,sum(b.c2)from a,b where a.c1=b.c1&&b.c1<10group by b.c1,a.rowid,a.c2 order bya.c2, and the view definition statement is: create view v as select / *+INDEX(b,ind)* / b.c1,sum(b.c2)c2from b where b.c1<10group by b.c1, then the fifth statement is: select / *+ENABLE_HASH_JOIN(1)INDEX(b,ind)* / a.c2,b.c1,sum(b.c2)from a,b where a.c1=b.c1&&b.c1<10group byb.c1,a.rowid,a.c2 order by a.c2.

[0091] S208: Determine the optimized target connection query statement based on the merge processing result.

[0092] When the target connection query statement is executed, the data table connection operation is performed first, and then the calculation is performed based on the result set obtained after the connection operation.

[0093] The connection query statement processing method provided by the embodiment of the present invention first merges the original connection query statement and its corresponding view definition statement that meet the preset optimization conditions, and determines the optimized target connection query statement based on the merge processing result, and merges the view definition statement and the connection query statement together to achieve the purpose of first connecting the tables and then performing group calculation, which greatly reduces the data volume of the group calculation, thereby greatly improving the execution efficiency of the entire connection query statement.

[0094] Based on the above embodiment, the method may further include:

[0095] Before replacing the view in the source item of the original join query statement with the source table in the view definition statement, the process also includes: determining whether the table name or table alias in the source item of the view definition statement is the same as the table name or table alias in the source item of the original join query statement. If so, renaming the table name or table alias in the source item of the view definition statement. This setting has the advantage of avoiding errors caused by duplicate names when the statement is executed.

[0096] Specifically, before the view in the source item of the original connection query statement is replaced with the source necklace table in the view definition statement, that is, when it is determined that the original connection query statement meets the preset optimization conditions, after obtaining the view definition statement corresponding to the original connection query statement, the source necklace table of the query clause of the view definition statement can be traversed first. If the table name or table alias in the source item of the query clause of the view definition statement is repeated with the table name or table alias in the source item of the original connection query statement, the table name or table alias in the source item of the query clause of the view definition statement is renamed, where the naming rule can be: letter_time_number, and the number part can be superimposed. Then, the table name or table alias of the source item of the query clause of the view definition statement after the alias is renamed is added to the temporary chain table. Finally, the table name to which the referenced column in the view definition statement belongs is replaced with the renamed alias.

[0097] For example, if the view definition statement is: create view v as select a.c1 as c1 from a, and the original connection query statement is: select a.c1,v.c1 from a,v, the replaced view definition statement is: createview v as select dm_20220301_1.c1as c1 from a as dm_20220301_1.

[0098] For example, if the original join query statement is not merged, the following steps are performed:

[0099]

[0100] Specifically, the execution process is: the control flow is transferred from top to bottom, and the data flow is transferred from bottom to top. In the execution plan, the meaning of each node can be explained as follows: CSCN2 indicates an index scan, and line 10 indicates scanning the data in table b; SLCT2 indicates row filtering, and line 9 adjusts row 10 to filter the condition b.c1<10; HAGR2 indicates table grouping, and line 8 indicates grouping the results returned by the operation in line 9; PRJT2 indicates query column calculation projection, and line 7 indicates obtaining the result set of the grouped column c1 and the sum of columns c2; the node in line 6 indicates scanning the data in table A; #NEST LOOP INNER JOIN2 indicates a nested loop join, and line 5 indicates joining the results returned by the operation in line 7 with the results returned by the operation in line 6; SLCT2 indicates row filtering, and line 4 indicates finding rows in the result in line 5 that meet the condition a.c1=v.c1; SORT3 indicates sorting, and line 3 indicates sorting the results returned by line 4; line 2 indicates obtaining data in columns a.c2, v.c1, and v.c2; and NSET2 indicates returning the final result.

[0101] Furthermore, the original join query statement above is a join query statement between table a and view v. When executing this original join query statement, the result set of view v must be calculated first, which requires querying the data in table b. If the data in table b is large, the majority of the execution time of this query statement will be consumed by the group calculation process in the definition statement of view v. However, if the original join query statement between view v and table a is merged first, thus performing the join operation between table a and table b first, and then performing the group calculation, the execution efficiency of the entire query statement will be improved and the data required for the group calculation will be reduced. The specific execution steps will change to:

[0102]

[0103] Example 3

[0104] Figure 3 This is a structural diagram of a connection query statement processing device provided by the third embodiment of the present invention. Figure 3 As shown, the device includes: a connection query statement acquisition module 301, a connection query statement judgment module 302, a merging processing module 303 and a connection query statement determination module 304, wherein:

[0105] The connection query statement acquisition module 301 is used to acquire an original connection query statement, wherein the original connection query statement is a connection query statement for a data table and a view;

[0106] A connection query statement judgment module 302 is used to judge whether the original connection query statement meets the preset optimization conditions;

[0107] The merging processing module 303 is configured to obtain the view definition statement corresponding to the original join query statement when the judgment result of the join query statement judgment module is satisfied, and merge the view definition statement and the original join query statement;

[0108] The connection query statement determination module 304 is used to determine the optimized target connection query statement according to the processing result of the merge processing module, so that when the target connection query statement is executed, the connection operation of the data table is first performed, and then calculation is performed based on the result set obtained after the connection operation.

[0109] The connection query statement processing device provided by the embodiment of the present invention merges the original connection query statement and its corresponding view definition statement that meet the preset optimization conditions, and determines the optimized target connection query statement based on the merge processing result, which greatly reduces the amount of data for group calculation, thereby greatly improving the execution efficiency of the entire connection query statement.

[0110] Optionally, the merging processing module 303 includes:

[0111] A first statement acquisition unit is configured to replace the view in the source necklace table of the original connection query statement with the source necklace table in the view definition statement to obtain a first statement;

[0112] A second statement acquisition unit is configured to replace the query items of the view columns in the first statement with the query items corresponding to the corresponding columns in the view definition statement to obtain a second statement;

[0113] A third statement acquisition unit is configured to, when the second statement contains a filter condition, replace the filter condition in the second statement with a target filter condition to obtain a third statement, wherein the target filter condition is the result of the AND of the filter condition in the second statement and the filter condition in the view definition statement;

[0114] The fourth statement acquisition unit is used to perform preset processing on the grouping items in the view definition statement to obtain the target grouping items, and add the target grouping items to the third statement to obtain the fourth statement;

[0115] Furthermore, the grouping items in the view definition statement are preset to obtain target grouping items, including:

[0116] The non-view source item data table included in the source item of the third statement is traversed, and the first preset pseudo column of the non-view source item data table obtained by traversal is added to the grouping item in the view definition statement to obtain a first grouping item, wherein the first preset pseudo column is used to represent the physical address; the column of the non-view source item data table involved in the query item of the third statement is added to the first grouping item to obtain a second grouping item; the column of the non-view source item data table in the sorting item of the third statement that does not currently exist in the second grouping item is added to the second grouping item to obtain a target grouping item.

[0117] A merging process determining unit is configured to determine a merging process result according to the fourth statement.

[0118] Furthermore, determining the merge processing result according to the fourth statement includes:

[0119] When the view definition statement includes grouping column filter items and / or prompt items, the included grouping column filter items and / or prompt items are added to the fourth statement to obtain a fifth statement; and the merging processing result is determined according to the fifth statement.

[0120] Optionally, the preset optimization condition is based on at least one of the following settings: the type of each item in the query necklace table of the original connection query statement, whether there is a second preset pseudo column item or a first preset clause in the original connection query statement, whether there is a second preset clause in the original connection query statement, whether there is an equal value filtering condition or an equal value joining condition in the original connection query statement, the number of views included in the source necklace table of the original connection query statement, and the content of the views included in the source necklace table of the original connection query statement.

[0121] Furthermore, the preset optimization conditions include meeting all of the following items simultaneously:

[0122] The type of each item in the query necklace table of the original connection query statement is a column; the original connection query statement does not contain the second preset pseudo column item and the Get Fixed Range clause, where the second preset pseudo column item indicates the position in the result set; the original connection query statement does not contain a grouping clause, a grouping column filter clause, or a hierarchical query clause; the source item and the filter necklace table of the original connection query statement contain an equal value filter condition or an equal value join condition; the source necklace table of the original connection query statement contains a view, the view definition statement contains a grouping clause, the view definition statement does not contain the second preset pseudo column item, the Get Fixed Range clause, or the hierarchical query clause, and the type of each item in the source necklace table of the view definition statement is a table type.

[0123] Optionally, the merge processing module also includes:

[0124] The table name processing unit is used to determine whether the table name or table alias in the source item of the view definition statement is repeated with the table name or table alias in the source item of the original connection query statement before the first statement acquisition unit replaces the view in the source necklace table of the original connection query statement with the source necklace table in the view definition statement. If so, rename the table name or table alias in the source item of the view definition statement.

[0125] The connection query statement processing device provided by the embodiment of the present invention can execute the connection query statement processing method provided by any embodiment of the present invention, and has the corresponding functional modules and beneficial effects of the execution method.

[0126] Example 4

[0127] Figure 4 A schematic diagram of the structure of an electronic device 40 that can be used to implement an embodiment of the present invention is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processing, cellular phones, smart phones, wearable devices (such as helmets, glasses, watches, etc.) and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the present invention described and / or claimed herein.

[0128] like Figure 4 As shown, the electronic device 40 includes at least one processor 41 and a memory, such as a read-only memory (ROM) 42, a random access memory (RAM) 43, etc., which is communicatively connected to the at least one processor 41. The memory stores a computer program that can be executed by the at least one processor, and the processor 41 can perform various appropriate actions and processes according to the computer program stored in the read-only memory (ROM) 42 or the computer program loaded from the storage unit 48 into the random access memory (RAM) 43. Various programs and data required for the operation of the electronic device 40 can also be stored in the RAM 43. The processor 41, ROM 42, and RAM 43 are connected to each other via a bus 44. An input / output (I / O) interface 45 is also connected to the bus 44.

[0129] Multiple components in the electronic device 10 are connected to the I / O interface 45, including an input unit 46, such as a keyboard, a mouse, etc.; an output unit 47, such as various types of displays, speakers, etc.; a storage unit 48, such as a magnetic disk, an optical disk, etc.; and a communication unit 49, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 49 allows the electronic device 40 to exchange information / data with other devices via a computer network such as the Internet and / or various telecommunication networks.

[0130] Processor 41 can be any general-purpose and / or specialized processing component with processing and computing capabilities. Some examples of processor 41 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various specialized artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, digital signal processors (DSPs), and any suitable processor, controller, microcontroller, etc. Processor 41 executes the various methods and processes described above, such as a method for processing a join query statement.

[0131] In some embodiments, the connection query statement processing method can be implemented as a computer program, which is tangibly contained in a computer-readable storage medium, such as the storage unit 48. In some embodiments, part or all of the computer program can be loaded and / or installed on the electronic device 40 via the ROM 42 and / or the communication unit 49. When the computer program is loaded into the RAM 43 and executed by the processor 41, one or more steps of the connection query statement processing method described above can be performed. Alternatively, in other embodiments, the processor 41 can be configured to execute the connection query statement processing method by any other appropriate means (for example, by means of firmware).

[0132] Various embodiments of the systems and techniques described herein can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), system-on-chip systems (SOCs), programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments can include being implemented in one or more computer programs that are executable and / or interpreted on a programmable system that includes at least one programmable processor, which can be a special purpose or general purpose programmable processor that can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.

[0133] Computer programs for implementing the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when the computer program is executed by the processor, the functions / operations specified in the flowcharts and / or block diagrams are implemented. The computer program may be executed entirely on the machine, partially on the machine, as a stand-alone software package, partially on the machine and partially on a remote machine, or entirely on a remote machine or server.

[0134] The computer device provided above can be used to execute the connection query statement processing method provided in any of the above embodiments, and has corresponding functions and beneficial effects.

[0135] Example 5

[0136] In the context of the present invention, a computer-readable storage medium may be a tangible medium, wherein the computer-executable instructions, when executed by a computer processor, are used to perform a method for processing a connection query statement, the method comprising:

[0137] Obtaining an original connection query statement, wherein the original connection query statement is a connection query statement for a data table and a view;

[0138] Determine whether the original connection query statement meets the preset optimization conditions;

[0139] If it is satisfied, the view definition statement corresponding to the original connection query statement is obtained, the view definition statement and the original connection query statement are merged, and the optimized target connection query statement is determined according to the merge processing result, so that when the target connection query statement is executed, the data table connection operation is performed first, and then the calculation is performed based on the result set obtained after the connection operation.

[0140] In the context of the present invention, computer-readable storage medium can be a tangible medium that can contain or store a computer program for use with an instruction execution system, device or equipment or used in conjunction with an instruction execution system, device or equipment. Computer-readable storage medium can include but is not limited to electronic, magnetic, optical, electromagnetic, infrared or semiconductor systems, devices or equipment, or any suitable combination of the foregoing. Alternatively, computer-readable storage medium can be a machine-readable signal medium. A more specific example of a machine-readable storage medium can include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0141] The computer device provided above can be used to execute the connection query statement processing method provided in any of the above embodiments, and has corresponding functions and beneficial effects.

[0142] It is worth noting that in the embodiment of the above-mentioned connection query statement processing device, the various units and modules included are only divided according to functional logic, but are not limited to the above-mentioned division, as long as the corresponding functions can be achieved; in addition, the specific names of the functional units are only for the convenience of distinguishing each other, and are not used to limit the scope of protection of the present invention.

[0143] Note that the above are only preferred embodiments of the present invention and the technical principles employed. Those skilled in the art will understand that the present invention is not limited to the specific embodiments described herein, and that various obvious changes, readjustments, and substitutions can be made by those skilled in the art without departing from the scope of protection of the present invention. Therefore, although the present invention has been described in detail through the above embodiments, the present invention is not limited to the above embodiments and may include many other equivalent embodiments without departing from the concept of the present invention. The scope of the present invention is determined by the scope of the appended claims.

Claims

1. A connection query statement processing method, characterized in that: include: Obtaining an original connection query statement, wherein the original connection query statement is a connection query statement for a data table and a view; Determine whether the original connection query statement meets the preset optimization conditions; If the conditions are met, obtaining a view definition statement corresponding to the original join query statement, merging the view definition statement and the original join query statement, and determining an optimized target join query statement based on the merging result, so that when the target join query statement is executed, a join operation on the data tables is first performed, and then calculation is performed based on a result set obtained after the join operation; The merging of the view definition statement and the original connection query statement includes: Replacing the view in the source necklace table of the original connection query statement with the source necklace table in the view definition statement to obtain a first statement; Replacing the query items of the view columns in the first statement with the query items corresponding to the corresponding columns in the view definition statement to obtain a second statement; In a case where the second statement includes a filter condition, the filter condition in the second statement is replaced with a target filter condition to obtain a third statement, wherein the target filter condition is the result of the AND of the filter condition in the second statement and the filter condition in the view definition statement; Performing preset processing on the grouping items in the view definition statement to obtain a target grouping item, and adding the target grouping item to the third statement to obtain a fourth statement; Determine a merging result according to the fourth statement; The performing preset processing on the grouping items in the view definition statement to obtain target grouping items includes: traversing a non-view source item data table included in the source item of the third statement, and adding a first preset pseudo column of the non-view source item data table obtained through the traversal to the grouping item in the view definition statement to obtain a first grouping item, wherein the first preset pseudo column is used to represent a physical address; Adding the columns of the non-view source item data table involved in the query item of the third statement to the first grouping item to obtain a second grouping item; The columns of the non-view source item data table in the sort item of the third statement that do not currently exist in the second group item are added to the second group item to obtain the target group item.

2. The method according to claim 1, characterized in that Determining the merging result according to the fourth statement includes: In a case where the view definition statement includes a grouping column filter item and / or a prompt item, the included grouping column filter item and / or prompt item is added to the fourth statement to obtain a fifth statement; A merging result is determined according to the fifth statement.

3. The method according to claim 1, characterized in that Before replacing the view in the source necklace table of the original connection query statement with the source necklace table in the view definition statement, the method further includes: Determine whether the table name or table alias in the source item of the view definition statement is repeated with the table name or table alias in the source item of the original connection query statement. If so, rename the table name or table alias in the source item of the view definition statement.

4. The method according to claim 1, wherein The preset optimization conditions are based on the following settings: The types of each item in the query necklace table of the original connection query statement, whether the second preset pseudo column item or the first preset clause exists in the original connection query statement, whether the second preset clause exists in the original connection query statement, whether there is an equal value filter condition or an equal value join condition in the original connection query statement, the number of views included in the source necklace table of the original connection query statement, and the content of the views included in the source necklace table of the original connection query statement.

5. The method according to claim 4, characterized in that The preset optimization conditions include meeting all of the following items at the same time: The type of each item in the query necklace table of the original join query statement is column; The original join query statement does not contain a second preset pseudo column item and a get fixed range clause, wherein the second preset pseudo column item indicates a position in the result set; The original join query statement does not contain a grouping clause, a grouping column filter clause, or a hierarchical query clause; The source item and filter necklace table of the original join query statement contain equal-value filter conditions or equal-value join conditions; The source necklace table of the original connection query statement contains a view, the view definition statement contains a grouping clause, the view definition statement does not contain the second preset pseudo column item, the fixed range acquisition clause and the hierarchical query clause, and the types of each item in the source necklace table of the view definition statement are all table types.

6. A connection query statement processing device, characterized in that: include: A connection query statement acquisition module is used to acquire an original connection query statement, wherein the original connection query statement is a connection query statement for a data table and a view; A connection query statement judgment module is used to judge whether the original connection query statement meets the preset optimization conditions; a merging processing module, configured to, when the judgment result of the connection query statement judgment module is satisfied, obtain a view definition statement corresponding to the original connection query statement, and merge the view definition statement with the original connection query statement; a connection query statement determination module, configured to determine an optimized target connection query statement based on the processing result of the merge processing module, so that when the target connection query statement is executed, a connection operation of the data tables is first performed, and then calculation is performed based on a result set obtained after the connection operation; The merge processing module includes: A first statement acquisition unit is configured to replace the view in the source necklace table of the original connection query statement with the source necklace table in the view definition statement to obtain a first statement; A second statement acquisition unit is configured to replace the query items of the view columns in the first statement with the query items corresponding to the corresponding columns in the view definition statement to obtain a second statement; A third statement acquisition unit is configured to, when the second statement contains a filter condition, replace the filter condition in the second statement with a target filter condition to obtain a third statement, wherein the target filter condition is the result of the AND of the filter condition in the second statement and the filter condition in the view definition statement; The fourth statement acquisition unit is used to perform preset processing on the grouping items in the view definition statement to obtain the target grouping items, and add the target grouping items to the third statement to obtain the fourth statement; A merging process determining unit: configured to determine a merging process result according to the fourth statement; The presetting process of the grouping items in the view definition statement to obtain the target grouping items includes: The non-view source item data table included in the source item of the third statement is traversed, and the first preset pseudo column of the non-view source item data table obtained by traversal is added to the grouping item in the view definition statement to obtain a first grouping item, wherein the first preset pseudo column is used to represent the physical address; the column of the non-view source item data table involved in the query item of the third statement is added to the first grouping item to obtain a second grouping item; the column of the non-view source item data table in the sorting item of the third statement that does not currently exist in the second grouping item is added to the second grouping item to obtain a target grouping item.

7. An electronic device, characterized in that: The electronic device comprises: at least one processor; and a memory communicatively coupled to the at least one processor; The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute the connection query statement processing method according to any one of claims 1 to 5.

8. A computer-readable storage medium, characterized in that The computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement the connection query statement processing method according to any one of claims 1 to 5 when executed.

Images