Query statement optimization method and related device

Abstract

The embodiment of the invention provides a query statement optimization method and a related device, and relates to the technical field of databases, the method comprises the following steps: obtaining a to-be-processed query statement; under the condition that the query statement comprises a CTE sub-statement, determining whether the query statement is an optimizable query statement or not according to a filtering condition of the query statement and a filtering condition of the CTE sub-statement; if yes, optimizing the query statement by utilizing a filtering condition of the CTE sub-statement to obtain a target query statement; and executing the target query statement. When the query statement meets the optimization condition, the constant equal to the filtering condition of the CTE sub-statement can be pushed down to the filtering condition of the query, and the query result set is filtered in advance, so that the query efficiency is effectively improved.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a query statement optimization method and related devices. Background Art

[0002] A common table expression (CTE) is a named temporary result set that exists in the scope of a single statement and can be referenced multiple times in the statement, thereby improving the query efficiency of the query statement. However, the query efficiency of the query statement using CTE is still low. Summary of the invention

[0003] The embodiments of the present application provide a query statement optimization method and related devices, which can effectively improve the query efficiency of query statements.

[0004] In a first aspect, an embodiment of the present application provides a query statement optimization method, comprising:

[0005] Get the query statement to be processed;

[0006] In the case where the query statement includes a CTE sub-statement, determining whether the query statement is an optimizable query statement according to a filtering condition of the query statement and a filtering condition of the CTE sub-statement;

[0007] If so, optimize the query statement using the filter condition of the CTE substatement to obtain a target query statement;

[0008] Execute the target query statement.

[0009] In some embodiments, determining whether the query statement is an optimizable query statement according to the filtering condition of the query statement and the filtering condition of the CTE substatement includes:

[0010] If the filter condition of the query statement uses an equivalue connection to reference the filter condition of the CTE substatement, and there is a condition equal to a constant in the referenced filter condition of the CTE substatement, then it is determined that the query statement is an optimizable query statement.

[0011] In some embodiments, before optimizing the query statement by using the filtering condition of the CTE substatement to obtain the target query statement, the method further includes:

[0012] Obtain the number of times the CTE substatement is referenced and / or the configuration parameters of the CTE substatement;

[0013] Based on the number of references and / or configuration parameters, the determination is made whether to optimize the query statement using the filtering condition of the CTE substatement.

[0014] In some embodiments, optimizing the query statement by using the CTE substatement to obtain a target query statement includes:

[0015] When the filtering condition of the CTE substatement is referenced multiple times in the query statement, the filtering condition of the query statement is optimized using the filtering condition of the CTE substatement to obtain the target query statement.

[0016] In some embodiments, optimizing the query statement by using the CTE substatement to obtain a target query statement includes:

[0017] When the configuration parameters of the CTE substatement indicate that the CTE substatement is prohibited from being promoted and optimized, the filtering conditions of the query statement are optimized using the filtering conditions of the CTE substatement to obtain the target query statement.

[0018] In some embodiments, the method further comprises:

[0019] Split the CTE substatement into multiple fields;

[0020] If there is a target field among the multiple fields, it is determined that the CTE substatement is prohibited from being promoted and optimized; the target field is used to indicate that the result set of the CTE substatement is materialized when executing the query.

[0021] In some embodiments, optimizing the filtering condition of the query statement by using the filtering condition of the CTE substatement to obtain the target query statement includes:

[0022] The filter condition of the CTE sub-statement referenced by the filter condition of the query statement is replaced by the constant in the filter condition of the CTE sub-statement.

[0023] In a second aspect, an embodiment of the present application provides a query statement optimization device, including:

[0024] An acquisition module is used to obtain query statements to be processed;

[0025] A processing module, configured to determine whether the query statement is an optimizable query statement according to a filtering condition of the query statement and a filtering condition of the CTE substatement when the query statement includes a CTE substatement;

[0026] An optimization module, used to optimize the query statement by using the filtering condition of the CTE sub-statement to obtain a target query statement;

[0027] An execution module is used to execute the target query statement.

[0028] In a third aspect, the present application provides an electronic device, including: a memory and a processor;

[0029] The memory is used to store computer instructions; the processor is used to execute the computer instructions stored in the memory to implement any method in the first aspect.

[0030] In a fourth aspect, the present application provides a computer-readable storage medium having a computer program stored thereon, and the computer program is executed by a processor to implement any one of the methods in the first aspect.

[0031] In a fifth aspect, the present application provides a computer program product, comprising a computer program, which implements any one of the methods in the first aspect when executed by a processor.

[0032] The query statement optimization method and related device provided in the embodiment of the present application obtain the query statement to be processed; when the query statement includes a CTE sub-statement, determine whether the query statement is an optimizable query statement based on the filter conditions of the query statement and the filter conditions of the CTE sub-statement; if so, optimize the query statement using the filter conditions of the CTE sub-statement to obtain a target query statement; and execute the target query statement. When the query statement meets the optimization conditions, the constant equal to the filter condition of the CTE sub-statement can be pushed down to the filter condition of the query, and the query result set is filtered in advance, which effectively improves the efficiency of the query. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] Figure 1 A flowchart of a query statement optimization method provided in an embodiment of the present application Figure 1 ;

[0034] Figure 2 A flowchart of a query statement optimization method provided in an embodiment of the present application Figure 2 ;

[0035] Figure 3 An example of an execution result provided in the embodiment of the present application Figure 1 ;

[0036] Figure 4 An example of an execution result provided in the embodiment of the present application Figure 2 ;

[0037] Figure 5 A schematic diagram of the structure of a query statement optimization device provided in an embodiment of the present application;

[0038] Figure 6 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0039] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0040] In the embodiments of the present application, words such as "first" and "second" are used to distinguish the same or similar items with substantially the same functions and effects, and do not limit their order. Those skilled in the art can understand that words such as "first" and "second" do not limit the quantity and execution order, and words such as "first" and "second" do not necessarily limit them to be different.

[0041] It should be noted that in the embodiments of the present application, words such as "exemplary" or "for example" are used to indicate examples, illustrations or descriptions. Any embodiment or design described as "exemplary" or "for example" in the present application should not be interpreted as being more preferred or more advantageous than other embodiments or designs. Specifically, the use of words such as "exemplary" or "for example" is intended to present related concepts in a specific way.

[0042] CTE (also known as common expression, public expression, etc.) corresponds to the with statement. Its function is similar to that of a subquery. It is a separate logical entity and provides a method for defining a temporary result set. This result set is referenced in a separate SQL statement and may appear in FROM, WHERE, JOIN and other clauses. Its main function is "query once, use in multiple places".

[0043] In Structured Query Language (SQL) statements, complex queries can be decomposed into multiple CTEs to make the query clearer and easier to maintain, thereby effectively improving query performance. However, in existing query statements using CTEs, the query performance of SQL statements is still insufficient due to the large amount of connected data.

[0044] In view of this, an embodiment of the present application provides a query statement optimization method and related devices. When certain conditions are met, the filtering conditions in the CTE are pushed down to the query, so that the query result set can be filtered in advance, the amount of connected data can be reduced, and the query efficiency can be improved.

[0045] The following specific embodiments are used to describe in detail the technical solution of the present application and how the technical solution of the present application solves the above technical problems. The following specific embodiments can be implemented independently or in combination with each other, and the same or similar concepts or processes may not be described in detail in some embodiments.

[0046] Figure 1 A flowchart of a query statement optimization method provided in an embodiment of the present application is shown in FIG. Figure 1 As shown, the steps include:

[0047] S101: Obtain a query statement to be processed.

[0048] The execution subject of the embodiment of the present application may be an optimizer or an executor that processes the query statement. The following description will be made using the optimizer as an example.

[0049] The optimizer can obtain query statements input by users from outside.

[0050] S102: When the query statement includes a CTE sub-statement, determine whether the query statement is an optimizable query statement according to a filtering condition of the query statement and a filtering condition of the CTE sub-statement.

[0051] When the optimizer obtains a query statement, it can perform a grammatical analysis on the query statement to determine whether the query statement includes a CTE substatement. If not, it is determined that the query statement does not meet the conditions for CTE optimization, and the optimizer can perform optimization work in the prior art on the query statement.

[0052] If the optimizer determines that the query statement includes a CTE substatement, then the query statement is confirmed to initially meet the conditions for CTE optimization. The query optimizer can further judge the query statement to determine whether the query statement can be optimized by CTE.

[0053] In the case where the query statement includes a CTE sub-statement, the optimizer may determine whether the query statement can be CTE optimized according to whether the filtering condition of the query statement and the filtering condition of the CTE sub-statement meet a preset condition.

[0054] Exemplarily, if the filter condition of the query statement uses an equi-join to reference the filter condition of the CTE substatement, and there is a condition equal to a constant in the referenced filter condition of the CTE substatement, then the query statement is determined to be an optimizable query statement.

[0055] For example, the query statement is as follows:

[0056] with ctedata as (select * from s11 where id1=12345)

[0057] select distinct s22.id3,s22.id4 from s22,ctedata where ctedata.id1=s22.id1 and s22.name1=ctedata.name1

[0058] There is a CTE substatement in the query statement: ctedata as , and the filter condition of the query statement is ctedata.id1=s22.id1 and s22.name1. The filter condition of the query statement uses an equi-join to reference the column id1 in the filter condition id1=12345 in the CTE substatement, and the column id1 has a condition equal to a constant in the filter condition of the CTE (that is, id1=12345), then the query statement is determined to be a query statement that can be optimized by CTE.

[0059] S103: If yes, optimize the query statement using the filter condition of the CTE substatement to obtain a target query statement.

[0060] In some embodiments, when the optimizer determines that a query statement is a query statement that can be optimized by CTE, the optimizer may optimize the filtering condition of the query statement by using the filtering condition of the CTE substatement to obtain the target query statement.

[0061] Exemplarily, the optimizer may use the constant in the filter condition of the CTE sub-statement to replace the filter condition of the CTE sub-statement referenced by the filter condition of the query statement.

[0062] For example, for the following query:

[0063] with ctedata as (select * from s11 where id1=12345)

[0064] select distinct s22.id3,s22.id4 from s22,ctedata where ctedata.id1=s22.id1 and s22.name1=ctedata.name1

[0065] The optimizer may use the constant in the filter condition of the CTE sub-statement to replace the filter condition of the CTE sub-statement referenced by the filter condition of the query statement, and may obtain the target query statement as shown below:

[0066] with ctedata as (select * from s11 where id1=12345)

[0067] select distinct s22.id3,s22.id4 from s22,ctedata where 12345=s22.id1and s22.name1=ctedata.name1

[0068] Since all records of the temporary result set ctedata satisfy id1=12345, the filter condition ctedata.id1=s22.id1 in the query statement also determines that the query result set must also satisfy s22.id1=12345. Therefore, ctedata.id1=s22.id1 can be replaced with 12345=s22.id1 to filter the query result set in advance, thereby effectively improving the query efficiency.

[0069] S104: Execute the target query statement.

[0070] When the optimizer obtains the target query statement, it may perform other optimization processing in the prior art on the target query statement (performing cost evaluation, etc.), and execute the target query statement to obtain a query result.

[0071] The query statement optimization method provided in the embodiment of the present application obtains the query statement to be processed; when the query statement includes a CTE sub-statement, determines whether the query statement is an optimizable query statement based on the filter conditions of the query statement and the filter conditions of the CTE sub-statement; if so, optimizes the query statement using the filter conditions of the CTE sub-statement to obtain a target query statement; and executes the target query statement. When the query statement meets the optimization conditions, the constant equal to the filter condition of the CTE sub-statement can be pushed down to the filter condition of the query, and the query result set is filtered in advance, which effectively improves the efficiency of the query.

[0072] Based on the above embodiments, Figure 2 The query statement optimization method provided in the embodiment of the present application is further explained.

[0073] Figure 2 Schematic diagram of the query statement optimization method provided in the embodiment of the present application Figure 2 ,like Figure 2 As shown, including:

[0074] S201: Obtain a query statement to be processed.

[0075] S202: When the query statement includes a CTE sub-statement, determine whether the query statement is an optimizable query statement according to a filtering condition of the query statement and a filtering condition of the CTE sub-statement.

[0076] In the embodiment of the present application, the specific implementation method of steps S201 and S202 is the same as Figure 1 The specific implementation methods of steps S101 and S102 in the illustrated embodiment are similar and will not be described in detail here.

[0077] S203: If yes, obtain the number of times the CTE substatement is referenced and / or the configuration parameters of the CTE substatement.

[0078] In some embodiments, the number of times a CTE sub-statement is referenced may refer to the number of times a filter condition of the CTE sub-statement is referenced in a filter condition of a query statement.

[0079] For example, the query statement is as follows:

[0080] with ctedata as (select * from s11 where id1=12345)

[0081] select distinct s22.id3,s22.id4 from s22,ctedata where ctedata.id1=s22.id1 and s22.name1=ctedata.name1

[0082] union

[0083] select distinct s33.id3,s33.id4 from s33,ctedata where ctedata.id1=s33.id1 and s33.name1=ctedata.name1;

[0084] In the query statement shown above, the filter condition of the CTE sub-statement ctedata.id1=s22.id1 is referenced twice in the query statement (ctedata.id1=s22.id1, ​​and ctedata.id1=s33.id1). The optimizer can determine the number of times the CTE sub-statement is referenced based on the syntax analysis results of the query statement.

[0085] In some embodiments, the configuration parameter of the CTE sub-statement may be used to indicate whether the CTE sub-statement is prohibited from being promoted and optimized.

[0086] Exemplarily, the optimizer may split the CTE substatement into multiple fields; identify each field, extract the field corresponding to the configuration parameter, and if there is a target field in the field corresponding to the configuration parameter, determine that the CTE substatement is prohibited from being promoted and optimized; the target field is used to indicate that the result set of the CTE substatement is materialized when executing the query.

[0087] For example, for the following query statement:

[0088] explain analyze

[0089] with ctedata as materialized (select * from s11 where id1=12345)

[0090] select distinct s22.id3,s22.id4 from s22,ctedata where ctedata.id1=s22.id1 and s22.name1=ctedata.name1

[0091] In the above statement, the field corresponding to the configuration parameter is the field after ctedata as (that is, the field where materialized is located in the above query statement). Since materialized is used to indicate that the result set of the CTE substatement is materialized when executing the query, that is, the CTE in the above query statement is declared to prohibit hoisting optimization. Among them, hoisting optimization is a compiler optimization technology that aims to extract calculations from loops or repeatedly executed code blocks to reduce unnecessary repeated calculations.

[0092] S204: Based on the number of references and / or configuration parameters, determining whether to optimize the query statement using the filtering condition of the CTE substatement.

[0093] When the optimizer obtains the number of times the CTE sub-statement is referenced and / or the configuration parameters of the CTE sub-statement, if the number of times the CTE sub-statement is referenced and / or the configuration parameters of the CTE sub-statement meet preset conditions, it is determined to execute optimization of the query statement.

[0094] For example, when the filtering condition of the CTE substatement is referenced multiple times (for example, greater than or equal to 2 times) in the query statement, it is determined to optimize the filtering condition of the query statement using the filtering condition of the CTE substatement to obtain the target query statement.

[0095] and / or,

[0096] When the configuration parameters of the CTE substatement indicate that the CTE substatement is prohibited from being promoted and optimized (for example, the CTE substatement includes a materialized field), it is determined to optimize the filtering condition of the query statement using the filtering condition of the CTE substatement to obtain the target query statement.

[0097] S205: If yes, optimize the filtering condition of the query statement using the filtering condition of the CTE substatement to obtain the target query statement.

[0098] S206: Execute the target query statement.

[0099] In the embodiment of the present application, the specific implementation method of steps S205 and S206 is the same as Figure 1 The specific implementation methods of steps S103 and S104 in the illustrated embodiment are similar and will not be described in detail here.

[0100] The query statement optimization method provided in the embodiment of the present application, there is a CTE sub-statement in the query statement. If the CTE sub-statement is referenced multiple times or is set to be non-promotable, and the filter condition of the query statement uses an equi-join to reference a column of the CTE sub-statement filter condition, and the column has a filter condition equal to a constant in the CTE sub-statement, then the constant can be pushed down to the filter condition of the query (i.e., optimized), so that the query result set can be filtered in advance, the amount of connected data can be reduced, and the query efficiency can be improved.

[0101] The following is a specific statement to illustrate the effect of the above optimization:

[0102] For the table creation statement shown below:

[0103] create table s11(id1 int,id2 int,id3 int,id4 int,name1 text,name2text);

[0104] create table s22(id1 int,id2 int,id3 int,id4 int,name1 text,name2text);

[0105] create table s33(id1 int,id2 int,id3 int,id4 int,name1 text,name2text);

[0106] Insert data:

[0107] insert into s11 values(generate_series(1, 1000000), generate_series(1, 1000000), generate_series(1, 1000000), generate_series(1, 1000000), 'x', 'y');

[0108] insert into s22 values(generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),'x','y');

[0109] insert into s33 values(generate_series(1, 1000000), generate_series(1, 1000000), generate_series(1, 1000000), generate_series(1, 1000000), 'x', 'y');

[0110] Test SQL statement:

[0111] explain analyze

[0112] with ctedata as (select * from s11 where id1=12345)

[0113] select distinct s22.id3, s22.id4 from s22, ctedata where ctedata.id1=s22.id1 and s22.name1=ctedata.name1

[0114] union

[0115] select distinct s33.id3, s33.id4 from s33, ctedata where ctedata.id1=s33.id1 and s33.name1=ctedata.name1;

[0116] When the above test SQL statement is not optimized (no filter condition pushdown), the execution result of the statement is as follows: Figure 3As shown in the figure, when the above test SQL statement is optimized (filter condition pushdown is performed), the execution result of the statement is as follows Figure 4 As shown. Figure 3 and Figure 4 It can be seen that when the condition is not pushed down, the result set of the sequential scan of s22 (s33) is 1,000,000 rows, which will be joined with ctedata for calculation. After the condition is pushed down, the result set of the sequential scan of s22 (s33) is 1 row, and the result set joined with ctedata is also only one row, which greatly improves the query efficiency of the statement.

[0117] Based on the above embodiments, the embodiments of the present application also provide a statement optimization device.

[0118] Figure 5 A schematic diagram of the structure of the statement optimization device provided in the embodiment of the present application is shown in FIG. Figure 5 As shown, including:

[0119] The acquisition module 501 is used to acquire the query statement to be processed.

[0120] The processing module 502 is used to determine whether the query statement is an optimizable query statement according to the filtering condition of the query statement and the filtering condition of the CTE sub-statement when the query statement includes a CTE sub-statement.

[0121] The optimization module 503 is used to optimize the query statement by using the filtering condition of the CTE sub-statement to obtain a target query statement.

[0122] The execution module 504 is used to execute the target query statement.

[0123] In some embodiments, the processing module 502 is also used to determine that the query statement is an optimizable query statement if the filter condition of the query statement uses an equivalue connection to reference the filter condition of the CTE sub-statement, and there is a condition equal to a constant in the filter condition of the referenced CTE sub-statement.

[0124] In some embodiments, the acquisition module 501 is further used to acquire the number of times the CTE sub-statement is referenced and / or the configuration parameters of the CTE sub-statement.

[0125] In some embodiments, the processing module 502 is further used to determine whether to optimize the query statement using the filtering condition of the CTE substatement based on the number of references and / or configuration parameters.

[0126] In some embodiments, the processing module 502 is further configured to optimize the filter condition of the query statement using the filter condition of the CTE substatement when the filter condition of the CTE substatement is referenced multiple times in the query statement to obtain the target query statement.

[0127] In some embodiments, the processing module 502 is further used to optimize the filtering conditions of the query statement using the filtering conditions of the CTE substatement to obtain the target query statement when the configuration parameters of the CTE substatement indicate that the CTE substatement is prohibited from being promoted and optimized.

[0128] In some embodiments, the processing module 502 is further used to split the CTE substatement into multiple fields; if there is a target field among the multiple fields, it is determined that the CTE substatement is prohibited from being promoted and optimized; the target field is used to indicate that the result set of the CTE substatement is materialized when executing the query.

[0129] In some embodiments, the optimization module 503 is further configured to replace the filter condition of the CTE sub-statement referenced by the filter condition of the query statement with the constant in the filter condition of the CTE sub-statement.

[0130] The statement optimization device provided in the embodiment of the present application can execute the statement optimization method provided in any of the above embodiments, and its principles and technical effects are similar, which will not be repeated here.

[0131] An embodiment of the present application also provides an electronic device.

[0132] Figure 6 A schematic diagram of the structure of an electronic device 60 provided in an embodiment of the present application is shown in FIG. Figure 6 As shown, including:

[0133] Processor 601.

[0134] The memory 602 is used to store executable instructions of the terminal device.

[0135] Specifically, the program may include program codes, and the program codes include computer operation instructions. The memory 602 may include a high-speed RAM memory, and may also include a non-volatile memory (non-volatile memory), such as at least one disk memory.

[0136] The processor 601 is used to execute the computer execution instructions stored in the memory 602 to implement the technical solution of the database statistical information collection method embodiment described in the above method embodiment.

[0137] The processor 601 may be a central processing unit (CPU), or an application specific integrated circuit (ASIC), or one or more integrated circuits configured to implement the embodiments of the present application.

[0138] Optionally, the electronic device 60 may further include a communication interface, so that it can communicate and interact with an external device through the communication interface, and the external device may be, for example, a user terminal (e.g., a mobile phone, a tablet). In a specific implementation, if the communication interface, the memory 602, and the processor 601 are implemented independently, the communication interface, the memory 602, and the processor 601 may be interconnected through a bus and communicate with each other.

[0139] The bus can be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, or an Extended Industry Standard Architecture (EISA) bus, etc. The bus can be divided into an address bus, a data bus, a control bus, etc., but it does not mean that there is only one bus or one type of bus.

[0140] Optionally, in a specific implementation, if the communication interface, the memory 602 and the processor 601 are integrated on a chip, the communication interface, the memory 602 and the processor 601 can communicate through an internal interface.

[0141] A computer-readable storage medium is also provided in an embodiment of the present application, on which a computer program is stored. When the computer program is executed by a processor, the technical solution of the statistical information collection method embodiment of the above-mentioned database is implemented. The implementation principle and technical effect are similar and will not be repeated here.

[0142] In one possible implementation, a computer-readable medium may include a random access memory (RAM), a read-only memory (ROM), a compact disc read-only memory (CD-ROM) or other optical disk storage, a magnetic disk storage or other magnetic storage device, or any other medium that is intended to carry or store the required program code in the form of instructions or data structures and can be accessed by a computer. Moreover, any connection is appropriately referred to as a computer-readable medium. For example, if the software is transmitted from a website, server or other remote source using a coaxial cable, a fiber optic cable, a twisted pair, a digital subscriber line (DSL) or wireless technologies such as infrared, radio and microwave, then the coaxial cable, fiber optic cable, twisted pair, DSL or wireless technologies such as infrared, radio and microwave are included in the definition of the medium. Disks and optical disks as used herein include optical disks, laser disks, optical disks, digital versatile disks (DVD), floppy disks and Blu-ray disks, where disks usually reproduce data magnetically, while optical disks reproduce data optically using lasers. Combinations of the above should also be included in the scope of computer-readable media.

[0143] A computer program product is also provided in an embodiment of the present application, including a computer program. When the computer program is executed by a processor, the technical solution of the statistical information collection method embodiment of the above-mentioned database is implemented. Its implementation principle and technical effect are similar and will not be repeated here.

[0144] In the specific implementation of the above-mentioned terminal device or server, it should be understood that the processor can be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), etc. A general-purpose processor can be a microprocessor or the processor can be any conventional processor, etc. The steps of the method disclosed in the embodiments of the present application can be directly embodied as being executed by a hardware processor, or can be executed by a combination of hardware and software modules in the processor.

[0145] Those skilled in the art will appreciate that all or part of the steps of any of the above method embodiments may be completed by hardware associated with program instructions. The aforementioned program may be stored in a computer-readable storage medium, and when the program is executed, all or part of the steps of the above method embodiments are executed.

[0146] If the technical solution of the present application is implemented in the form of software and sold or used as a product, it can be stored in a computer-readable storage medium. Based on this understanding, all or part of the technical solution of the present application can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including a computer program or several instructions. The computer software product enables a computer device (which can be a personal computer, a server, a network device, or a similar electronic device) to perform all or part of the steps of the method described in the embodiment of the present application.

[0147] It should be noted that, for the aforementioned method embodiments, for the sake of simplicity, they are all expressed as a series of action combinations, but those skilled in the art should be aware that the present application is not limited by the described order of actions, because according to the present application, certain steps can be performed in other orders or simultaneously. Secondly, those skilled in the art should also be aware that the embodiments described in the specification are all optional embodiments, and the actions and modules involved are not necessarily required by the present application.

[0148] It should be further noted that, although the various steps in the flow chart are displayed in sequence according to the indication of the arrows, these steps are not necessarily executed in sequence according to the order indicated by the arrows. Unless there is a clear explanation in this article, the execution of these steps is not strictly limited in order, and these steps can be executed in other orders. Moreover, at least a portion of the steps in the flow chart may include multiple sub-steps or multiple stages, and these sub-steps or stages are not necessarily executed at the same time, but can be executed at different times, and the execution order of these sub-steps or stages is not necessarily to be carried out in sequence, but can be executed in turn or alternately with other steps or at least a portion of the sub-steps or stages of other steps.

[0149] It should be understood that the above-mentioned device embodiments are only illustrative, and the device of the present application can also be implemented in other ways. For example, the division of units / modules in the above-mentioned embodiments is only a logical function division, and there may be other division methods in actual implementation. For example, multiple units, modules or components can be combined, or can be integrated into another system, or some features can be ignored or not executed.

[0150] In addition, unless otherwise specified, each functional unit / module in each embodiment of the present application may be integrated into one unit / module, each unit / module may exist physically separately, or two or more units / modules may be integrated together. The above-mentioned integrated unit / module may be implemented in the form of hardware or in the form of a software program module.

[0151] If the integrated unit / module is implemented in the form of hardware, the hardware may be a digital circuit, an analog circuit, etc. The physical implementation of the hardware structure includes but is not limited to transistors, memristors, etc. Unless otherwise specified, the processor may be any appropriate hardware processor, such as a CPU, a GPU, an FPGA, a DSP, an ASIC, etc. Unless otherwise specified, the storage unit may be any appropriate magnetic storage medium or magneto-optical storage medium, such as a resistive random access memory (RRAM), a dynamic random access memory (DRAM), a static random access memory (SRAM), an enhanced dynamic random access memory (EDRAM), a high-bandwidth memory (HBM), a hybrid memory cube (HMC), etc.

[0152] If the integrated unit / module is implemented in the form of a software program module and sold or used as an independent product, it can be stored in a computer-readable memory. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art, or all or part of the technical solution can be embodied in the form of a software product. The computer software product is stored in a memory, including a number of instructions to enable a computer device (which can be a personal computer, server or network device, etc.) to execute all or part of the steps of the various embodiments of the present application. The aforementioned memory includes: U disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), mobile hard disk, disk or optical disk and other media that can store program codes.

[0153] In the above embodiments, the description of each embodiment has its own emphasis. For the part not described in detail in a certain embodiment, please refer to the relevant description of other embodiments. The technical features of the above embodiments can be combined arbitrarily. In order to make the description concise, all possible combinations of the technical features in the above embodiments are not described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0154] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit it. Although the present application has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present application.

Claims

1. A query statement optimization method, characterized in that: Get the query statement to be processed; In the case where the query statement includes a CTE sub-statement, determining whether the query statement is an optimizable query statement according to a filtering condition of the query statement and a filtering condition of the CTE sub-statement; If so, optimize the query statement using the filter condition of the CTE substatement to obtain a target query statement; Execute the target query statement.

2. The method according to claim 1, characterized in that The determining whether the query statement is an optimizable query statement according to the filtering condition of the query statement and the filtering condition of the CTE sub-statement includes: If the filter condition of the query statement uses an equivalue connection to reference the filter condition of the CTE substatement, and there is a condition equal to a constant in the referenced filter condition of the CTE substatement, then it is determined that the query statement is an optimizable query statement.

3. The method according to claim 2, characterized in that Before optimizing the query statement by using the filtering condition of the CTE substatement to obtain the target query statement, the method further includes: Obtain the number of times the CTE substatement is referenced and / or the configuration parameters of the CTE substatement; Based on the number of references and / or configuration parameters, the determination is made whether to optimize the query statement using the filtering condition of the CTE substatement.

4. The method according to claim 3, characterized in that The optimizing the query statement by using the CTE substatement to obtain the target query statement includes: When the filtering condition of the CTE substatement is referenced multiple times in the query statement, the filtering condition of the query statement is optimized using the filtering condition of the CTE substatement to obtain the target query statement.

5. The method according to claim 3, characterized in that: The optimizing the query statement by using the CTE substatement to obtain the target query statement includes: When the configuration parameters of the CTE substatement indicate that the CTE substatement is prohibited from being promoted and optimized, the filtering conditions of the query statement are optimized using the filtering conditions of the CTE substatement to obtain the target query statement.

6. The method according to claim 5, characterized in that The method further comprises: Split the CTE substatement into multiple fields; If there is a target field among the multiple fields, it is determined that the CTE substatement is prohibited from being promoted and optimized; the target field is used to indicate that the result set of the CTE substatement is materialized when executing the query.

7. The method according to claim 4 or 5, characterized in that: The step of optimizing the filtering condition of the query statement by using the filtering condition of the CTE substatement to obtain the target query statement includes: The filter condition of the CTE sub-statement referenced by the filter condition of the query statement is replaced by the constant in the filter condition of the CTE sub-statement.

8. An electronic device, characterized in that: include: Memory for storing computer programs; A processor, configured to execute the computer program to implement the method according to any one of claims 1 to 7.

9. A computer-readable storage medium, characterized in that: A computer program is stored thereon, and the computer program is executed by a processor to implement the method according to any one of claims 1 to 7.

10. A computer program product, characterized in that The invention comprises a computer program, which implements the method according to any one of claims 1 to 7 when being executed by a controller.

Images