A query statement processing method, device and equipment

Abstract

Embodiments of the present specification disclose a query statement processing method, device and equipment. The scheme comprises: obtaining a first query statement; identifying whether a query scenario corresponding to the first query statement belongs to a repeated value insensitive scenario; if the query scenario belongs to the repeated value insensitive scenario, and columns in a complement side table of an outer join in the first query statement only appear in a connection condition of the outer join, eliminating processing is performed on the outer join to obtain a second query statement to replace the first query statement for querying.

Description

Technical Field

[0001] This specification relates to the field of database technology, and in particular to a query statement processing method, apparatus, and device. Background Technology

[0002] In databases, querying target data using Structured Query Language (SQL) statements is a frequently performed operation. Traditional SQL statements are manually written by developers.

[0003] As technology advances, more and more SQL statements are automatically generated by programs. Therefore, in actual business scenarios, some automatically generated SQL statements contain statements that have no semantic impact on the query results. Moreover, not all developers who write SQL statements are senior SQL experts, so they may also manually write such statements that have no semantic impact on the query results.

[0004] These types of statements can negatively impact execution performance; therefore, they need to be optimized to improve performance. Summary of the Invention

[0005] This specification provides one or more embodiments of a query statement processing method, apparatus, device, and storage medium to solve the following technical problem: the need to optimize some SQL statements that have no semantic impact on the query results in order to improve execution performance.

[0006] To solve the above-mentioned technical problems, one or more embodiments of this specification are implemented as follows:

[0007] This specification provides a query statement processing method according to one or more embodiments, including:

[0008] Retrieve the first query statement;

[0009] Identify whether the query scenario corresponding to the first query statement belongs to a scenario where duplicate values ​​are not sensitive;

[0010] If the query scenario is one where duplicate values ​​are not sensitive, and the columns in the filler table of the outer join in the first query statement only appear in the join condition of the outer join, then the outer join is eliminated to obtain a second query statement, which is used to replace the first query statement for querying.

[0011] This specification provides a query statement processing apparatus according to one or more embodiments, comprising:

[0012] The statement retrieval module retrieves the first query statement;

[0013] The scene recognition module identifies whether the query scene corresponding to the first query statement belongs to a scene where duplicate values ​​are not sensitive.

[0014] If the query scenario is one where duplicate values ​​are not sensitive, and the columns in the filler table of the outer join in the first query statement only appear in the join condition of the outer join, then the outer join is eliminated to obtain a second query statement, which is used to replace the first query statement for querying.

[0015] This specification provides a query statement processing device according to one or more embodiments, comprising:

[0016] At least one processor; and,

[0017] A memory communicatively connected to the at least one processor; wherein,

[0018] The memory stores instructions that can be executed by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to:

[0019] Retrieve the first query statement;

[0020] Identify whether the query scenario corresponding to the first query statement belongs to a scenario where duplicate values ​​are not sensitive;

[0021] If the query scenario is one where duplicate values ​​are not sensitive, and the columns in the filler table of the outer join in the first query statement only appear in the join condition of the outer join, then the outer join is eliminated, and a second query statement is obtained to replace the first query statement for querying.

[0022] This specification provides one or more embodiments of a non-volatile computer storage medium storing computer-executable instructions, wherein the computer-executable instructions are configured as follows:

[0023] Retrieve the first query statement;

[0024] Identify whether the query scenario corresponding to the first query statement belongs to a scenario where duplicate values ​​are not sensitive;

[0025] If the query scenario is one where duplicate values ​​are not sensitive, and the columns in the filler table of the outer join in the first query statement only appear in the join condition of the outer join, then the outer join is eliminated, and a second query statement is obtained to replace the first query statement for querying.

[0026] The above-described at least one technical solution adopted in one or more embodiments of this specification can achieve the following beneficial effects: It is noted that when an outer join exists in a query statement, the outer join may have no semantic impact on the query result; by identifying whether the query scenario corresponding to the query statement belongs to a scenario where duplicate values ​​are not sensitive, it is determined whether the duplicate values ​​caused by the outer join can have a substantial impact on the query result; and by detecting whether the columns in the filler table of the outer join in the query statement only appear in the join condition of the outer join, it is determined whether the outer join can have other substantial impacts on the query result; and based on this, a decision is made on whether to eliminate the outer join to rewrite the query statement, thereby optimizing the query statement and improving execution performance. Attached Figure Description

[0027] To more clearly illustrate the technical solutions in the embodiments or prior art of this specification, the drawings used in the description of the embodiments or prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this specification. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0028] Figure 1 A flowchart illustrating a query statement processing method provided in one or more embodiments of this specification;

[0029] Figure 2 In one practical application scenario provided by one or more embodiments of this specification, Figure 1 A schematic diagram of one implementation scheme of the Chinese method;

[0030] Figure 3 A schematic diagram of the structure of a query statement processing device provided for one or more embodiments of this specification;

[0031] Figure 4 This is a schematic diagram of the structure of a query statement processing device provided for one or more embodiments of this specification. Detailed Implementation

[0032] This specification provides a query statement processing method, apparatus, device, and storage medium through its embodiments.

[0033] To enable those skilled in the art to better understand the technical solutions in this specification, the technical solutions in the embodiments of this specification will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this specification, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this application.

[0034] The problem described in the background section is that it's not just manual SQL writing or automatic SQL generation by programs that can cause this issue. Some original SQL statements are normal, but after being rewritten and transformed by optimizers, the resulting new SQL statements may contain parts that are actually useless in terms of logic, thus leading to the problem described in the background section.

[0035] The inventors conducted research on this issue in practice, particularly regarding SQL statements containing outer joins. They observed scenarios in queries where the number of duplicate values ​​in the intermediate result set can be ignored; these are termed "duplicate value insensitive scenarios." For outer joins, taking a left outer join as an example, a left outer join is likely to increase the number of duplicate rows in the output of the left table (or the right table in a right outer join) in the query results, leading to duplicate values. However, in duplicate value insensitive scenarios, for queries containing outer joins, the outer join may have no effect on the query results. In such cases, the outer join is essentially an invalid operation, reducing the execution efficiency of the query and impacting performance.

[0036] To address these invalid operations, this application explicitly summarizes and proposes several scenarios where duplicate values ​​are not sensitive, in order to facilitate efficient scenario identification. Based on these scenarios, a corresponding outer join elimination scheme is proposed. By identifying and eliminating outer joins in scenarios where duplicate values ​​are not sensitive, the aforementioned invalid operations are reduced, and execution performance is improved. Therefore, this approach can solve the problems in the background technology to a certain extent. The following section will elaborate on this idea.

[0037] Figure 1 This diagram illustrates a query processing method provided in one or more embodiments of this specification. The method can be applied to various business domains, including electronic payment, e-commerce, instant messaging, gaming, and government services. The process can be executed on database-related devices within these domains, such as distributed database servers and database optimization servers. Certain input parameters or intermediate results in the process can be manually adjusted to improve accuracy.

[0038] Figure 1 The process includes the following steps:

[0039] S102: Obtain the first query statement.

[0040] The first query statement refers to the query statement that is currently to be rewritten and optimized. The query statement obtained after rewriting and optimizing the first query statement is called the second query statement.

[0041] Query statements mainly refer to SQL statements used in databases. Statements based on database languages ​​other than SQL that can also perform database queries are also included in the scope of query statements.

[0042] In one or more embodiments of this specification, the first query statement includes an outer join. An outer join is a relational algebraic join method used to return matching rows from two tables (i.e., the left and right tables of the outer join), and pads the output of the non-matching table with null values. Outer joins can include left outer joins and right outer joins. For a left outer join, it may result in duplicate values ​​being output from the left table; for a right outer join, it may result in duplicate values ​​being output from the right table. These duplicate values ​​are collectively referred to as duplicate values ​​caused by the outer join. It should be noted that outer joins can also include full outer joins; however, the solution in this application addresses the cases of left and right outer joins, not full outer joins.

[0043] S104: Identify whether the query scenario corresponding to the first query statement belongs to a scenario where duplicate values ​​are not sensitive.

[0044] In one or more embodiments of this specification, in practical applications, duplicate values ​​are not necessarily caused by outer joins. However, in scenarios where duplicate values ​​are not sensitive, since the query results no longer need duplicate values ​​or may even avoid outputting duplicate values, the possibility of outer joins being invalid operations will increase significantly.

[0045] The main reason why duplicate values ​​are insensitive is that duplicate values ​​only apply to a local range in the query statement to obtain an intermediate result. The duplicate value itself is discarded after use and does not spread out from that local range. Furthermore, the intermediate result will not reproduce duplicate values ​​later, so the presence or absence of duplicate values ​​in the query result is irrelevant.

[0046] In one or more embodiments of this specification, three types of scenarios where duplicate values ​​are not sensitive are summarized to facilitate more accurate and efficient identification of the query scenario corresponding to the first query statement through scenario matching. In the first type, the query statement actively performs deduplication; in the second type, the query statement contains a subquery, which limits the scope of duplicate values; and in the third type, the query statement contains specific joins such as semi-joins or anti-joins, which limit the scope of duplicate values. Detailed examples and analyses will follow.

[0047] S106: If the query scenario is a scenario where duplicate values ​​are not sensitive, and the columns in the filler table of the outer join in the first query statement only appear in the join condition of the outer join, then the outer join is eliminated to obtain a second query statement, which is used to replace the first query statement for querying.

[0048] In a left outer join, the left table is the primary table (referred to as the primary table for ease of description), and the right table is the filler table; similarly, in a right outer join, the right table is the primary table, and the left table is the filler table. If no join is required, the primary table can generally be used directly.

[0049] As mentioned earlier, the inventors primarily focused on optimizing outer joins. More specifically, regarding insensitivity to duplicate values, this application focuses on ensuring that the query results are insensitive to duplicate values ​​caused by outer joins within the query statement. This application aims to eliminate invalid outer joins to optimize the first query statement. This elimination process includes, for example, removing the keywords of the outer join and filling empty side tables within the outer join. Naturally, the join conditions also become meaningless and are eliminated, leaving only the main table for normal use. The following embodiments are primarily based on this principle.

[0050] Based on the analysis above, the fact that the query scenario is insensitive to duplicate values ​​is only one of the conditions that needs to be met. In this scenario, the impact of the outer join on the query results through the main table is suppressed. However, it is also necessary to consider whether the outer join also affects the query results through the padding side table. If so, it indicates that the outer join may be valid and not suitable for elimination. Therefore, the inventors explicitly proposed another condition that needs to be met: the columns in the padding side table of the outer join in the first query statement only appear in the join condition of that outer join. In this case, the impact of the padding side table will not spill over outside the outer join, or it can only indirectly affect the results through duplicate values ​​in the main table. However, this indirect impact will also be suppressed by the insensitivity to duplicate values.

[0051] In summary, if both conditions in the previous paragraph are met simultaneously, the outer join can be considered essentially invalid and can be eliminated. However, if this elimination is not performed and the first query is executed directly, unnecessary table scans and outer join operations will occur, which is detrimental to execution performance and wastes computational resources.

[0052] pass Figure 1 The method takes into account that when an outer join exists in a query statement, the outer join may have no semantic impact on the query results. By identifying whether the query scenario corresponding to the query statement belongs to a scenario where duplicate values ​​are not sensitive, it determines whether the duplicate values ​​caused by the outer join can have a substantial impact on the query results. Furthermore, by detecting whether the columns in the padding side table of the outer join in the query statement appear only in the join condition of the outer join, it determines whether the outer join can have other substantial impacts on the query results. Based on this, it decides whether to eliminate the outer join to rewrite the query statement, thereby optimizing the query statement and improving execution performance.

[0053] based on Figure 1 In addition to the method described herein, this specification also provides some specific implementation schemes and extension schemes of this method, which will be further explained below.

[0054] The three scenarios where duplicate values ​​are not sensitive were mentioned earlier, and we will analyze them in detail below.

[0055] The first type of scenario where duplicate values ​​are not sensitive is the scenario that involves direct deduplication operations.

[0056] In one or more embodiments of this specification, during scene recognition, it can be determined whether a deduplicated query result is required in the first query statement. If so, it can be determined that the query scene corresponding to the first query statement belongs to a scene insensitive to duplicate values. The reason is that duplicate values ​​will be directly filtered out by the deduplication operation, so the query result will not detect duplicate values, and therefore it is naturally insensitive to duplicate values.

[0057] The deduplication process described above can be determined by detecting keywords in the first query statement. For example, it can be determined whether the keyword "DISTINCT" exists in the first query statement. "DISTINCT" is used for direct deduplication. More precisely, the position of "DISTINCT" in the statement and the deduplication target it targets can also be detected. If it is determined that the first query statement will obtain deduplicated query results based on "DISTINCT", then the query scenario corresponding to the first query statement belongs to a scenario where duplicate values ​​are not sensitive.

[0058] Based on a similar line of thought, even without using a direct deduplication keyword like DISTINCT, deduplication can be indirectly achieved through other combined calculation expressions. Therefore, by detecting and analyzing such expressions, the corresponding scenarios can be identified as scenarios that are not sensitive to duplicate values.

[0059] To make it easier to understand, let's take the keyword DISTINCT as an example.

[0060] For example, the first query statement is the following SQL statement, denoted as Q1:

[0061] SELECT DISTINCT T1.C1 FROM T1 LEFT JOIN T2 ON T1.C1 <T2.C1;

[0062] Here, T1 and T2 are two data tables, each containing a column C1. LEFT JOIN is the keyword used to perform a left outer join, and ON is the keyword used to specify the join condition for the left outer join.

[0063] It can be seen that the left table (main table) of this left outer join is T1, the right table (padding table) is T2, and the join condition is T1.C1 < T2.C1.

[0064] In Q1, a left outer join is performed on T1 and T2, and a DISTINCT de-duplication operation is performed on the query projection output columns. The query result is insensitive to the duplicate values existing in T1.C1 after the outer join. Therefore, it belongs to the scenario insensitive to duplicate values. Moreover, columns in T2 are not referenced at positions other than the join condition T1.C1 < T2.C1, and the column in T2 (i.e., T2.C1) only appears in T1.C1 < T2.C1. Therefore, the left outer join in Q1 can be eliminated.

[0065] After processing Q1, the second query statement is obtained, denoted as Q2:

[0066] SELECT DISTINCT T1.C1 FROM T1;

[0067] It can be seen that in Q2, only T1 remains from the content related to the original left outer join in Q1.

[0068] The second type of scenario insensitive to duplicate values is the scenario containing subqueries.

[0069] In one or more embodiments of this specification, when performing scenario recognition, it can be determined whether the outer join in the first query statement is included in the subquery in the first query statement. If so, the expression using the result of the subquery in the first query statement is determined. If the calculation result of this expression is insensitive to the duplicate values caused by the outer join in the subquery (which also reflects that the influence of the duplicate values caused by the outer join does not spread outside this subquery), it is determined that the query scenario corresponding to the first query statement belongs to the scenario insensitive to duplicate values.

[0070] For ease of understanding, an example is also given. The same symbols are used with the same meanings as in the above example and will not be elaborated.

[0071] For example, the first query statement is the following SQL statement, denoted as Q3:

[0072] SELECT C1,C2 FROM T1

[0073] WHERE T1.C1 < ALL(SELECT T2.C2 FROM T2 LEFT JOIN T3 ON T2.C1 < T3.C1);

[0074] Among them, T3 is a data table, and there is a column C1 in T3.

[0075] It can be seen that Q3 contains a subquery, which is the content within the parentheses. The left outer join is included in the subquery. The left table (the main table) of this left outer join is T2, the right table (the table for filling null values) is T3, and the join condition is T2.C1 < T3.C1.

[0076] In Q3, within its subquery, a left outer join is performed on T2 and T3. After the join, T2.C2 is output. In the main query, the result of the subquery is used with the expression T1.C1 < ALL. At this time, the query result of Q3 is insensitive to the duplicate values of T2.C2 output in the subquery. Therefore, it belongs to the scenario insensitive to duplicate values. Moreover, columns in T3 are not referenced at positions other than the join condition T2.C1 < T3.C1. The column in T3 (i.e., T3.C1) only appears in T2.C1 < T3.C1. Therefore, the left outer join in Q3 can be eliminated.

[0077] For the second query statement obtained after processing Q3, it is denoted as Q4:

[0078] SELECT C1,C2 FROM T1

[0079] WHERE T1.C1 < ALL(SELECT T2.C2 FROM T2);

[0080] It can be seen that in Q4, only T2 remains from the content related to the original left outer join in Q3.

[0081] The third type of scenario insensitive to duplicate values is the scenario containing a specific join, and this specific join is a semi-join or an anti-join. The actual output of this specific join is only its left table, and its right table only affects whether the left table is to be output. Therefore, it is insensitive to duplicate values.

[0082] It should be noted that generally, this specific join is not written directly in the syntax, but is often written based on some special keywords with the help of subqueries. These special keywords include IN, NOT IN, EXISTS, NOT EXISTS, etc. Since not all statements containing subqueries can be rewritten as this specific join, the scenario containing this specific join is listed separately as a category.

[0083] In one or more embodiments of this specification, when performing scenario recognition, it is determined whether the first query statement contains this specific join. If it contains this specific join, and the outer join in the first query statement is on the right table side of this specific join and is used to join the right table of this specific join, then it is determined that the query scenario corresponding to the first query statement belongs to the scenario insensitive to duplicate values.

[0084] For ease of understanding, an example is also given.

[0085] For example, the first query statement is the following SQL statement, denoted as Q5:

[0086] SELECT C1,C2 FROM T1

[0087] WHERE EXISTS(SELECT 1FROM T2 LEFT JOIN T3 ON T2.C1<T3.C1 WHERE T1.C1=T2.C2);

[0088] In Q3, a semi-join is constructed by combining a subquery (i.e., the content in parentheses) through the EXISTS keyword. The table in the subquery is the right table of the semi-join, and the table in the main query is the left table of the semi-join. The subquery will output a true value or a false value. If it is a true value, the left table will be retained and the corresponding query data will be output. If it is a false value, the corresponding query data in the left table will be discarded.

[0089] In the right table of the semi-join, T2 and T3 are left outer joined. Based on the semantics of the semi-join, the query result of Q5 is insensitive to the duplicate values output by this left outer join in the right table of the semi-join. Therefore, it belongs to the scenario insensitive to duplicate values. Moreover, columns in T3 are not referenced at positions other than the join condition T2.C1<T3.C1 in the right table of the semi-join. The column in T3 (i.e., T3.C1) only appears in T2.C1<T3.C1. Therefore, the left outer join in Q5 can be eliminated.

[0090] The second query statement obtained after processing Q5 is denoted as Q6:

[0091] SELECT C1,C2 FROM T1

[0092] WHERE EXISTS(SELECT 1FROM T2 WHERE T1.C1=T2.C2);

[0093] It can be seen that in Q6, only T2 remains in the content related to the original left outer join in Q5.

[0094] Based on the above scenario division, in an actual application scenario provided by one or more embodiments of this specification Figure 1 A schematic diagram of an implementation scheme of the method in Figure 2 is as shown

[0095] Figure 2 The scheme in

[0096] For the original first query statement, identify that the first query statement contains an outer join.

[0097] The first step involves identifying the query scenario corresponding to the first query statement and determining whether the columns in the padded side table of the outer join in the first query statement appear only in the join condition of that outer join. The order of these two sub-steps is not limited; executing only one sub-step may directly lead to the conclusion that the first query statement does not need optimization.

[0098] The identification results for the query scenario have four branches: scenarios that are not sensitive to duplicate values ​​and include direct deduplication operations, scenarios that are not sensitive to duplicate values ​​and include subqueries, scenarios that are not sensitive to duplicate values ​​and include semi-joins or anti-joins, and other scenarios. Other scenarios can be considered as scenarios that are sensitive to duplicate values.

[0099] The result of this judgment has two branches: yes and no.

[0100] If the identification result belongs to the first three branches, and the judgment result is this branch (that is, it is identified as belonging to the scenario where duplicate values ​​are not sensitive, and the columns in the filler table of the outer join in the first query statement only appear in the join condition of the outer join), then both conditions are met at the same time, and the outer join can be eliminated to obtain the second query statement, which can be used to replace the first query statement for querying.

[0101] For other branch combinations, it can be considered that it is not suitable to eliminate the outer join, that is, there is no need to optimize the first query statement for the time being.

[0102] Based on the same idea, one or more embodiments of this specification also provide apparatus and devices corresponding to the above methods, such as... Figure 3 , Figure 4 As shown. The apparatus and equipment are capable of performing the above methods and related alternatives accordingly.

[0103] Figure 3 This specification provides a schematic diagram of the structure of a query statement processing apparatus according to one or more embodiments, the apparatus comprising:

[0104] Statement retrieval module 302 retrieves the first query statement;

[0105] The scene recognition module 304 identifies whether the query scene corresponding to the first query statement belongs to a scene where duplicate values ​​are not sensitive.

[0106] If the query scenario is one where duplicate values ​​are not sensitive, and the columns in the filler table of the outer join in the first query statement only appear in the join condition of the outer join, then the outer join is eliminated to obtain a second query statement, which is used to replace the first query statement for querying.

[0107] Optionally, the insensitivity to duplicate values ​​includes: the query result of the query statement is insensitive to duplicate values ​​caused by the outer join in the query statement.

[0108] Optionally, the scene recognition module 304 determines whether to obtain deduplicated query results in the first query statement;

[0109] If so, then the query scenario corresponding to the first query statement is determined to be a scenario where duplicate values ​​are not sensitive.

[0110] Optionally, the scene recognition module 304 determines whether there is a DISTINCT keyword in the first query statement used to obtain deduplicated query results.

[0111] Optionally, the scene recognition module 304 determines whether the outer join in the first query statement is included in the subquery in the first query statement;

[0112] If so, then determine the expression in the first query statement that uses the result of the subquery;

[0113] If the result of the expression is not sensitive to duplicate values ​​caused by the outer join in the subquery, then the query scenario corresponding to the first query statement is determined to be a scenario where duplicate values ​​are not sensitive.

[0114] Optionally, the scene recognition module 304 determines whether the first query statement contains a specific connection, wherein the specific connection is a half-connection or an anti-connection;

[0115] If a specific join is included, and the outer join in the first query statement is on the right side of the specific join, used to connect the right table of the specific join, then the query scenario corresponding to the first query statement is determined to be a scenario where duplicate values ​​are not sensitive.

[0116] Optionally, the specific connection is constructed using at least one of the following keywords: IN, NOT IN, EXISTS, NOT EXISTS.

[0117] Optionally, the connection elimination module 306 eliminates the keyword of the outer connection and the blanking side table of the outer connection.

[0118] Figure 4 This specification provides a schematic diagram of the structure of a query statement processing device according to one or more embodiments, the device comprising:

[0119] At least one processor; and,

[0120] A memory communicatively connected to the at least one processor; wherein,

[0121] The memory stores instructions executable by the at least one processor, which, when executed by the at least one processor, enable the at least one processor to:

[0122] Retrieve the first query statement;

[0123] Identify whether the query scenario corresponding to the first query statement belongs to a scenario where duplicate values ​​are not sensitive;

[0124] If the query scenario is one where duplicate values ​​are not sensitive, and the columns in the filler table of the outer join in the first query statement only appear in the join condition of the outer join, then the outer join is eliminated to obtain a second query statement, which is used to replace the first query statement for querying.

[0125] Based on the same idea, one or more embodiments of this specification also provide a non-volatile computer storage medium storing computer-executable instructions, wherein the computer-executable instructions are configured as follows:

[0126] Retrieve the first query statement;

[0127] Identify whether the query scenario corresponding to the first query statement belongs to a scenario where duplicate values ​​are not sensitive;

[0128] If the query scenario is one where duplicate values ​​are not sensitive, and the columns in the filler table of the outer join in the first query statement only appear in the join condition of the outer join, then the outer join is eliminated to obtain a second query statement, which is used to replace the first query statement for querying.

[0129] In the 1990s, improvements to a technology could be clearly distinguished as either hardware improvements (e.g., improvements to the circuit structure of diodes, transistors, switches, etc.) or software improvements (improvements to the methodology). However, with technological advancements, many methodological improvements today can be considered direct improvements to the hardware circuit structure. Designers almost always obtain the corresponding hardware circuit structure by programming the improved methodology into the hardware circuit. Therefore, it cannot be said that a methodological improvement cannot be implemented using hardware physical modules. For example, a Programmable Logic Device (PLD) (such as a Field Programmable Gate Array (FPGA)) is such an integrated circuit whose logic function is determined by the user programming the device. Designers can program and "integrate" a digital system onto a PLD themselves, without needing chip manufacturers to design and manufacture dedicated integrated circuit chips. Furthermore, nowadays, instead of manually manufacturing integrated circuit chips, this programming is mostly implemented using "logic compiler" software. Similar to the software compiler used in program development, the original code before compilation must be written in a specific programming language, called a Hardware Description Language (HDL). There are many HDLs, such as ABEL (Advanced Boolean Expression Language), AHDL (Altera Hardware Description Language), Confluence, CUPL (Cornell University Programming Language), HDCal, JHDL (Java Hardware Description Language), Lava, Lola, MyHDL, PALASM, and RHDL (Ruby Hardware Description Language). Currently, the most commonly used are VHDL (Very-High-Speed ​​Integrated Circuit Hardware Description Language) and Verilog. Those skilled in the art should understand that by simply performing some logic programming on the method flow using one of these hardware description languages ​​and programming it into an integrated circuit, the hardware circuit implementing the logical method flow can be easily obtained.

[0130] The controller can be implemented in any suitable manner. For example, it can take the form of a microprocessor or processor and a computer-readable medium storing computer-readable program code (e.g., software or firmware) executable by the (micro)processor, logic gates, switches, application-specific integrated circuits (ASICs), programmable logic controllers, and embedded microcontrollers. Examples of controllers include, but are not limited to, the following microcontrollers: ARC625D, Atmel AT91SAM, Microchip PIC18F26K20, and Silicon Labs C8051F320. A memory controller can also be implemented as part of the control logic of the memory. Those skilled in the art will also recognize that, in addition to implementing the controller in purely computer-readable program code form, the same functionality can be achieved by logically programming the method steps to make the controller take the form of logic gates, switches, ASICs, programmable logic controllers, and embedded microcontrollers. Therefore, such a controller can be considered a hardware component, and the means included therein for implementing various functions can also be considered as structures within the hardware component. Alternatively, the means for implementing various functions can be considered as both software modules implementing the method and structures within the hardware component.

[0131] The systems, devices, modules, or units described in the above embodiments can be implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer. Specifically, a computer can be, for example, a personal computer, laptop computer, cellular phone, camera phone, smartphone, personal digital assistant, media player, navigation device, email device, game console, tablet computer, wearable device, or any combination of these devices.

[0132] For ease of description, the above devices are described in terms of function, divided into various units. Of course, in implementing this specification, the functions of each unit can be implemented in one or more software and / or hardware components.

[0133] Those skilled in the art will understand that the embodiments of this specification can be provided as methods, systems, or computer program products. Therefore, the embodiments of this specification can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the embodiments of this specification can take the form of a computer program product implemented on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0134] This specification is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this specification. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create a machine for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0135] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0136] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0137] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0138] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0139] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0140] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0141] This specification can be described in the general context of computer-executable instructions that are executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc., that perform a specific task or implement a specific abstract data type. This specification can also be practiced in distributed computing environments, where tasks are performed by remote processing devices connected via a communication network. In distributed computing environments, program modules can reside in local and remote computer storage media, including storage devices.

[0142] The various embodiments in this specification are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the embodiments of apparatus, devices, and non-volatile computer storage media are basically similar to the method embodiments, so the descriptions are relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0143] The foregoing has described specific embodiments of this specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims may be performed in a different order than that shown in the embodiments and may still achieve the desired result. Furthermore, the processes depicted in the drawings do not necessarily require the specific or sequential order shown to achieve the desired result. In some embodiments, multitasking and parallel processing are possible or may be advantageous.

[0144] The above description is merely one or more embodiments of this specification and is not intended to limit this specification. Various modifications and variations can be made to the one or more embodiments of this specification by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principle of one or more embodiments of this specification should be included within the scope of the claims of this specification.

Claims

1. A query statement processing method, comprising: Retrieve the first query statement; Identify whether the query scenario corresponding to the first query statement belongs to a scenario where duplicate values ​​are not sensitive. The scenario where duplicate values ​​are not sensitive includes scenarios where the number of times intermediate result set values ​​are repeated can be ignored in the query. If the query scenario is one where duplicate values ​​are not sensitive, and the columns in the filler table of the outer join in the first query statement only appear in the join condition of the outer join, then the outer join is eliminated to obtain a second query statement, which is used to replace the first query statement for querying. The insensitivity to duplicate values ​​includes: the query results of the query statement are insensitive to duplicate values ​​caused by the outer join in the query statement; The process of eliminating the outer join specifically includes: eliminating the key of the outer join and the empty side table of the outer join.

2. The method as described in claim 1, wherein identifying whether the query scenario corresponding to the first query statement belongs to a scenario where duplicate values ​​are not sensitive, specifically includes: Determine whether to obtain deduplicated query results in the first query statement; If so, then the query scenario corresponding to the first query statement is determined to be a scenario where duplicate values ​​are not sensitive.

3. The method as described in claim 2, wherein determining whether to obtain deduplicated query results in the first query statement specifically includes: Determine whether the DISTINCT keyword exists in the first query statement to obtain deduplicated query results.

4. The method as described in claim 1, wherein identifying whether the query scenario corresponding to the first query statement belongs to a scenario where duplicate values ​​are not sensitive, specifically includes: Determine whether the outer join in the first query statement is contained in a subquery of the first query statement; If so, then determine the expression in the first query statement that uses the result of the subquery; If the result of the expression is not sensitive to duplicate values ​​caused by the outer join in the subquery, then the query scenario corresponding to the first query statement is determined to be a scenario where duplicate values ​​are not sensitive.

5. The method as described in claim 1, wherein identifying whether the query scenario corresponding to the first query statement belongs to a scenario where duplicate values ​​are not sensitive, specifically includes: Determine whether the first query statement contains a specific join, wherein the specific join is a half-join or an anti-join; If a specific join is included, and the outer join in the first query statement is on the right side of the specific join, used to connect the right table of the specific join, then the query scenario corresponding to the first query statement is determined to be a scenario where duplicate values ​​are not sensitive.

6. The method of claim 5, wherein the specific connection is constructed using at least one of the following keywords: IN, NOTIN, EXISTS, NOT EXISTS.

7. A query statement processing apparatus, comprising: The statement retrieval module retrieves the first query statement; The scene recognition module identifies whether the query scene corresponding to the first query statement belongs to a scene where duplicate values ​​are not sensitive. The scene where duplicate values ​​are not sensitive includes: a scene where the number of times intermediate result set values ​​are repeated can be ignored in the query. The join elimination module, if the query scenario is a scenario where duplicate values ​​are not sensitive, and the columns in the filler table of the outer join in the first query statement only appear in the join condition of the outer join, then eliminates the outer join and obtains a second query statement to replace the first query statement for querying; The insensitivity to duplicate values ​​includes: the query results of the query statement are insensitive to duplicate values ​​caused by the outer join in the query statement; The process of eliminating the outer join specifically includes: eliminating the key of the outer join and the empty side table of the outer join.

8. The apparatus of claim 7, wherein the scene recognition module determines whether to obtain deduplicated query results in the first query statement; If so, then the query scenario corresponding to the first query statement is determined to be a scenario where duplicate values ​​are not sensitive.

9. The apparatus of claim 8, wherein the scene recognition module determines whether a DISTINCT keyword exists in the first query statement for obtaining deduplicated query results.

10. The apparatus of claim 7, wherein the scene recognition module determines whether the outer join in the first query statement is included in a subquery in the first query statement; If so, then determine the expression in the first query statement that uses the result of the subquery; If the result of the expression is not sensitive to duplicate values ​​caused by the outer join in the subquery, then the query scenario corresponding to the first query statement is determined to be a scenario where duplicate values ​​are not sensitive.

11. The apparatus of claim 7, wherein the scene recognition module determines whether the first query statement contains a specific connection, wherein the specific connection is a half-connection or an anti-connection; If a specific join is included, and the outer join in the first query statement is on the right side of the specific join, used to connect the right table of the specific join, then the query scenario corresponding to the first query statement is determined to be a scenario where duplicate values ​​are not sensitive.

12. The apparatus of claim 11, wherein the specific connection is constructed using at least one of the following keywords: IN, NOT IN, EXISTS, NOT EXISTS.

13. A query statement processing device, comprising: At least one processor; as well as, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform: Retrieve the first query statement; Identify whether the query scenario corresponding to the first query statement belongs to a scenario where duplicate values ​​are not sensitive. The scenario where duplicate values ​​are not sensitive includes scenarios where the number of times intermediate result set values ​​are repeated can be ignored in the query. If the query scenario is one where duplicate values ​​are not sensitive, and the columns in the filler table of the outer join in the first query statement only appear in the join condition of the outer join, then the outer join is eliminated, and a second query statement is obtained to replace the first query statement for querying. The insensitivity to duplicate values ​​includes: the query results of the query statement are insensitive to duplicate values ​​caused by the outer join in the query statement; The process of eliminating the outer join specifically includes: eliminating the key of the outer join and the empty side table of the outer join.

Images