CN114490724B

CN114490724B - Method and device for processing database query statement

Info

Publication number: CN114490724B
Application number: CN202210392238.8A
Authority: CN
Inventors: 施文俊; 朱涛
Original assignee: Beijing Oceanbase Technology Co Ltd
Current assignee: Beijing Oceanbase Technology Co Ltd
Priority date: 2022-04-15
Filing date: 2022-04-15
Publication date: 2022-06-14
Anticipated expiration: 2042-04-15
Also published as: CN114490724A

Abstract

The disclosure discloses a method and apparatus for processing a database query statement. The method comprises the following steps: receiving a first database query statement, wherein the first database query statement comprises a first view in an FROM clause and a connection operation of the first view and a first data table, and a first window function is packaged in the first view; and generating a second database query statement according to the first database query statement, wherein the second database query statement is equivalent to the first database query statement, the second database query statement comprises a second view in the FROM clause, a first window function is packaged in the second view, and the connection operation of the first data table and a second data table is performed, and the second data table is a data table corresponding to the first view.

Description

Method and device for processing database query statement

Technical Field

The present disclosure relates to the field of database technologies, and more particularly, to a method and apparatus for processing a database query statement.

Background

Query statements such as join operations and window functions are often used in database queries for business analysis. In some scenarios, if the window function in the database query statement is located in the FROM clause of the database query statement, the query optimizer may execute the window function first and then execute the join operation, which results in low execution efficiency of the database query statement.

Disclosure of Invention

In view of this, the present disclosure provides a method and an apparatus for processing a database query statement to improve the execution efficiency of the database query statement.

In a first aspect, a method for processing a database query statement is provided, including: receiving a first database query statement, wherein the first database query statement comprises a first view in a FROM clause and a connection operation of the first view and a first data table, and a first window function is packaged in the first view; and generating a second database query statement according to the first database query statement, wherein the second database query statement is equivalent to the first database query statement, the second database query statement comprises a second view in the FROM clause, the second view is packaged with the first window function and the connection operation between the first data table and a second data table, and the second data table is a data table corresponding to the first view.

Optionally, the generating a second database query statement according to the first database query statement includes: judging whether the connection operation of the first view and the first data table can be pushed down to the inside of the first view; and if the connection operation of the first view and the first data table can be pushed down to the inside of the first view, according to the first database query statement, the connection operation of the first view and the first data table is pushed down to the inside of the first view, and the second database query statement is obtained.

Optionally, the determining whether the connection operation between the first view and the first data table can be pushed down to the inside of the first view includes: judging whether the first view and the first data table meet a first condition, and if so, considering that the connection operation of the first view and the first data table can be pushed down to the inside of the first view; wherein the first condition comprises: an isojoin condition exists between the first view and the first data table, and an expression in the isojoin condition for the first view depends on a packet field in the first window function.

Optionally, the method further comprises: checking whether an expression related to the first data table in a first connection condition satisfies uniqueness, wherein the first connection condition is a connection condition corresponding to connection operation of the first view and the first data table; adding a unique key of the first data table in a packet field of the first window function if the expression for the first data table does not satisfy uniqueness.

Optionally, after the checking whether the expression regarding the first data table in the first connection condition satisfies uniqueness, the method further includes: if the expression for the first data table does not satisfy uniqueness, calculating an execution cost of the second database query statement to determine whether to generate the second database query statement.

Optionally, before the generating a second database query statement according to the first database query statement, the method further includes: checking the validity of the first view; the generating a second database query statement according to the first database query statement includes: and under the condition that the first view is legal, generating the second database query statement according to the first database query statement.

Optionally, in a case that a second window function is further encapsulated in the first view, the first condition includes: an isojoin condition exists between the first view and the first data table, and an expression in the isojoin condition for the first view depends on an intersection of a grouping field in the first window function and a grouping field in the second window function.

Optionally, the second view further encapsulates a filter condition only with respect to the first data table.

In a second aspect, an apparatus for processing a database query statement is provided, including: the system comprises a receiving module, a searching module and a processing module, wherein the receiving module is used for receiving a first database query statement, the first database query statement comprises a first view in a FROM clause and a connection operation between the first view and a first data table, and a first window function is packaged in the first view; a generating module, configured to generate a second database query statement according to the first database query statement, where the second database query statement is equivalent to the first database query statement, the second database query statement includes a second view in a FROM clause, the second view is encapsulated with the first window function, and the first data table and a second data table are connected through a connection operation, and the second data table is a data table corresponding to the first view.

Optionally, the generating module further comprises: the judging module is used for judging whether the connection operation of the first view and the first data table can be pushed down to the inside of the first view; and the push-down module is used for pushing down the connection operation between the first view and the first data table to the inside of the first view according to the first database query statement to obtain the second database query statement if the connection operation between the first view and the first data table can be pushed down to the inside of the first view.

Optionally, the determining module is further configured to: judging whether the first view and the first data table meet a first condition, and if so, considering that the connection operation of the first view and the first data table can be pushed down to the inside of the first view; wherein the first condition comprises: an isojoin condition exists between the first view and the first data table, and an expression in the isojoin condition for the first view depends on a packet field in the first window function.

Optionally, the apparatus further comprises: the first checking module is used for checking whether an expression related to the first data table in a first connection condition meets uniqueness, wherein the first connection condition is a connection condition corresponding to connection operation of the first view and the first data table; an adding module for adding a unique key of the first data table in a grouping field of the first window function if the expression about the first data table does not satisfy uniqueness.

Optionally, the apparatus further comprises: a calculation module to calculate an execution cost of the second database query statement to determine whether to generate the second database query statement if the expression for the first data table does not satisfy uniqueness.

Optionally, the apparatus further comprises: a second checking module for checking the validity of the first view; the generating module is further configured to generate the second database query statement according to the first database query statement when the first view is legal.

In a third aspect, there is provided an apparatus for processing a database query statement, comprising a memory and a processor, wherein the memory stores executable code, and the processor is configured to execute the executable code to implement some or all of the steps of the method according to the first aspect.

In a fourth aspect, there is provided a computer readable storage medium having stored thereon executable code which, when executed, is capable of carrying out some or all of the steps of the method according to the first aspect.

In a fifth aspect, there is provided a computer program product comprising executable code which, when executed, is capable of implementing some or all of the steps of the method of the first aspect.

In the first database query statement, when the execution order of the first window function is earlier than the connection operation, the embodiment of the disclosure generates the equivalent second database query statement by rewriting the first database query statement, so that the execution order of the connection operation in the second database query statement is earlier than the first window function, thereby improving the execution efficiency of the first database query statement.

Drawings

In order to more clearly illustrate the technical solutions of the embodiments of the present disclosure, the drawings required to be used in the embodiments of the present disclosure will be described below.

Fig. 1 is a flowchart illustrating a method for processing a database query statement according to an embodiment of the present disclosure.

Fig. 2 is a schematic flowchart of a possible implementation manner of step S120 provided in the embodiment of the present disclosure.

Fig. 3 is a flowchart illustrating a method for processing a database query statement according to another embodiment of the disclosure.

Fig. 4 is a flowchart illustrating a method for processing a database query statement according to yet another embodiment of the disclosure.

Fig. 5 is a flowchart illustrating a method for processing a database query statement according to yet another embodiment of the disclosure.

Fig. 6 is a schematic structural diagram of an apparatus for processing a database query statement according to an embodiment of the present disclosure.

Fig. 7 is a schematic structural diagram of an apparatus for processing a database query statement according to another embodiment of the present disclosure.

Detailed Description

The technical solutions in the embodiments of the present disclosure are clearly and completely described below, and it is obvious that the described embodiments are only a part of the embodiments of the present disclosure, and not all embodiments.

For ease of understanding, some relevant technical knowledge related to the embodiments of the present disclosure will be introduced. The following related arts as alternatives can be arbitrarily combined with the technical solutions of the embodiments of the present disclosure, which all belong to the scope of protection of the embodiments of the present disclosure. Embodiments of the present disclosure include at least some of the following.

Connecting operation

In a relational database, database query statements (e.g., Structured Query Language (SQL) statements) often contain JOIN Operations (JOINs). Based on join operations, two or more data tables (or views) may be merged according to join conditions to form a new result set. Based on the result set formed by the join operation, database operations such as query, add, delete, change, etc. may be implemented.

The syntax format of the connection operation includes a connection type and a connection condition. Taking the SQL statement as an example, the SQL syntax format of the join operation can be expressed as: data table 1 < connection type > JOIN data table 2 < connection condition >, which may also be a view.

The database may support multiple types of connection operations, such as, for example, INNER connections (INNER JOIN), OUTER connections (OUTER JOIN), or CROSS-connects (CROSS JOIN). Different connection operations can be subdivided, for example, an internal connection can also include an equal connection, a non-equal connection, a natural connection, and the like; the external connection operation may also include left connection, right connection, full connection, etc.

The connection conditions of the connection operation may include a variety of, and common connection conditions may be classified as equal-value connection conditions (i.e., a relationship in the connection conditions is an equal-value relationship, such as t1.c1= t2.c 2) or non-equal-value connection conditions. In some embodiments, the connection condition may be a simple condition or a complex condition. The complex condition is a combination of a plurality of simple conditions, AND for example, a plurality of simple conditions may be combined by using a logical relationship such as "AND", "OR" to form a complex condition.

It should be noted that the objects of the join operation are two objects, for example, two data tables, two views, one data table, and one view. The following description will be given of a case where a plurality of objects need to be connected, taking as an example that a plurality of data tables need to be connected. In the case of requiring connection of multiple data tables, two of the data tables may be connected first, and then the formed result set and the third data table may be connected, and so on. Based on this, in some embodiments, the join operation may also be referred to as a multi-table join. For the sake of simplifying the description, the connection operation hereinafter is exemplified by connection between two objects.

It should be appreciated that in some scenarios, the join operation has strong data filtering capability and the amount of intermediate result set data after the join is relatively small, because the join operation can filter the data table according to the join condition and the filtering condition to screen out a useful result set.

Window function

The database query statement may support WINDOW FUNCTION (WINDOW FUNCTION) for real-time analysis processing of data in the database. In some embodiments, the window function may also be referred to as an online analytical processing (OLAP) function. Taking SQL statements as an example, the SQL syntax format of the window function can be expressed as: for example, the < packet field > and the < sorting field > may be column names in a data table or view for grouping or sorting according to the column names < window function name > may include a dedicated window function or aggregation function, as one example, the < window function name > may be a dedicated window function such as RANK, density, MAX, MIN, etc., or may be an aggregation function such as SUM, COUNT, MAX, MIN, etc.

In some embodiments, the window function may be used to further operate on the result after the clause processing, such as JOIN, WHERE, or GROUP BY, in the database query statement, so that the window function is generally written in the SELECT clause. As a specific example, the SQL statement of the window function may be expressed as:

SELECT *,

RANK () OVER (PARTITION BY < PACKET FIELD >

ORDER BY < sort field >) AS RANKING

FROM < table name >;

it should be noted that, when the database query is performed BY using the window function, the grouping (PARTITION BY) and sorting (ORDER BY) functions can be simultaneously implemented without reducing the number of rows in the original table.

View of the drawing

A VIEW (VIEW) may be understood as a table that is a visualization of a result set based on a database query statement. The views contain rows and columns and the fields in the views are fields in real tables from one or more databases. In some embodiments, the view may contain SQL functions (e.g., window functions), WHERE statements, JOIN (JOIN) statements, and the like.

The types of views may be varied, such as standard views, inline views, materialized views, and so forth. Wherein the inline view may be understood as a sub-query in the FROM clause of the SQL statement. It should be understood that in the FROM clause, a data table for the data to be queried may be specified, and that in addition to the data table, a view (subquery) may be possible. Since the inline view may replace a table in a query, in some embodiments, the inline view may also be referred to as a derived table.

Query statements such as join operations and window functions are often used in database query statements to implement business analysis (e.g., analytical queries). When multiple different operations are included in a database query statement (e.g., a join operation and a window function are included at the same time), different orders of execution may produce different sets of intermediate results. In the process of query optimization, reducing the data volume of the intermediate result set as much as possible can generally greatly reduce the influence time of the query. Taking the execution order of the join operation and the window function as an example, the window function does not reduce the number of rows in the original table, and the join operation can generate different result sets according to the different join conditions and the different filter conditions. Based on this, a better query plan should perform join operations first, and then perform the window function.

Generally, when a join operation and a window function are both included in a database query statement, the join operation is executed in an order before the window function, because the join operation is generally located in a FROM clause, and the window function is generally located in a SELECT clause, and the query optimizer executes the database query statement to preferentially execute the FROM clause to assemble data FROM different data sources. Take the following SQL1 statement as an example:

SELECT *,

RANK() OVER (PARTITION BY T1.C1

ORDER BY T1.C2) AS RANKING

FROM T1, T2

WHERE T1.C1=T2.C3;

When the SQL1 statement is executed, the execution order is: firstly, performing a connection operation between T1 and T2 based on a connection condition (T1. c1= T2.c 3); the window function (the RANK function in the SQL1 statement) is then executed based on the result set of the join operation.

However, in some scenarios, if a window function in a database query statement is located in the FROM clause of the database query statement (i.e., the window function is located in a view of the FROM clause, or referred to as being located in the FROM subquery), then the query optimizer can only process the window function in the order of executing the window function first and then executing the join operation. As described above, the window function does not reduce the number of lines in the original table, and in this case, executing the window function first and then executing the connection operation would result in a large workload when the window function is scanned, and further result in a low execution efficiency of the database query statement. Take the following SQL2 statement as an example:

SELECT *

FROM T0, T1, T2,

(SELECT MAX (C1) OVER (PARTITION BY (C2, C3)), C2, C3

FROM T3) VIEW1

WHERE T1.UNIQUE=T2.C1

AND T1.UNIQUE=VIEW1.C2

AND T1.C2=5

AND T0.C1=T2.C3；

in the SQL2 statement, (SELECT MAX (C1) OVER (PARTITION BY (C2, C3)), C2, C3 FROM T3) VIEW1 is an inline VIEW (which may also be referred to as a FROM sub-query) in which window function MAX (C1) OVER (PARTITION BY (C2, C3)) is located. Therefore, when the query optimizer executes the SQL2 statement, the window function MAX (C1) OVER (PARTITION BY (C2, C3)) is preferentially executed, and then the connection operation between the inline VIEW1 and the data tables T0, T1, and T2 corresponding to the window function is executed.

In order to solve the above problem, embodiments of the present disclosure provide a method and an apparatus for processing a database query statement, so as to improve execution efficiency of the database query statement.

The following describes the technical solutions of the embodiments of the present disclosure in detail with reference to the accompanying drawings.

Fig. 1 is a flowchart illustrating a method for processing a database query statement according to an embodiment of the present disclosure. The method shown in FIG. 1 may be performed by a query optimizer in a database. The database may be a general database or a distributed database. The user of the database can perform operations on the database through the database query statement, such as database query. The database query statement referred to herein may be, for example, an SQL statement. One or more data tables may be stored in the database. As shown in fig. 1, the method may include step S110 and step S120. These steps are described in detail below.

In step S110, a first database query statement is received. The first database query statement comprises a first view in the FROM clause, and the connection operation of the first view and the first data table, wherein a first window function is packaged in the first view.

The first database query statement may be used to perform a database query, which may be an SQL statement, for example. In embodiments of the present disclosure, the component of the first data query statement may include a plurality (i.e., the first database query statement may include a plurality of clauses), for example, the first database query statement may include a SELECT-FROM-WHERE clause.

The FROM clause of the first database query statement includes a first view, which may be understood as a sub-query in the FROM clause of the first database query statement, which may be, for example, an inline view. The first view is packaged with a first window function, and the first window function may be used for query computation of aggregation functions such as SUM (SUM), Average (AVG), Maximum (MAX), and Minimum (MIN), or for statistical analysis computation of dedicated window functions such as RANK (RANK). In some embodiments, multiple views may be included in the FROM clause of the first database query statement, in which case the first view may refer to any one of the multiple views.

The first database query statement further includes a join operation of the first view and the first data table. The first database query statement includes a join operation of the first view and the first data table, and may refer to a join type (hereinafter, referred to as a first join type) and a join condition (hereinafter, referred to as a first join condition) including the first view and the first data table. The first connection type is not particularly limited in the embodiments of the present disclosure, and the first connection type may be an internal connection, an external connection, or a cross connection. Further, when the first connection type is an internal connection, the first connection type may also be an equal connection, a non-equal connection, a natural connection, or the like; alternatively, when the first connection type is an external connection, the first connection type may be a left connection, a right connection, a full connection, or the like. The first connection condition is also not specifically limited in the embodiments of the present disclosure, and for example, the first connection condition may be an equivalent connection condition or may also be a simple condition.

In some embodiments, when a connection condition exists between the first view and the plurality of data tables, the first data table may refer to any one of the plurality of data tables. For example, if equal join conditions exist in both the first view and tables T1 and T2, the first data table may refer to T1 or T2, which is not limited by the present disclosure.

In some embodiments, the first database query statement may be a database query statement processed by a query optimizer. For example, in a database query statement input by a user, a WHERE clause includes a window function, and when the query optimizer performs optimization, the window function may be extracted into a view in a FROM clause. In this case, the extracted database query statement may be the first database query statement mentioned in the embodiments of the present disclosure.

After receiving the first database query statement, the query optimizer preferentially executes the first window function in the first view under the condition of not performing optimization processing, and then executes the connection operation between the first view and the first data table based on the execution result of the first window function.

Taking the above SQL2 statement as an example of the first database query statement, in the SQL2 statement, the FROM clause in the first database query statement includes a first VIEW1, the first VIEW1 is a FROM subquery (SELECT MAX (C1) OVER (PARTITION BY (C2, C3)), C2, C3 FROM T3) VIEW 1), and the first VIEW1 includes a first window function MAX (C1) OVER (PARTITION BY (C2, C3)), where the first window function is a MAX aggregation function. In addition, in the SQL2 statement, a connection operation of the first VIEW1 and the first data table T1 is also included, the connection condition of the connection operation is T1.unique = VIEW w1.c2, and the connection type is an internal connection. It should be noted that, in the first VIEW1, since the query statement only refers to the table T3, and the table name is omitted in the query statement for brevity, in some embodiments, a complete expression may also be adopted, for example, the first VIEW1 may also be expressed as:

SELECT MAX (T3.C1) OVER (PARTITION BY(T3.C2, T3.C3)), T3.C2, T3.C3 FROM T3) VIEW1。

In step S120, a second database query statement is generated from the first database query statement. In this disclosure, after the query optimizer receives the first database query statement, the query optimizer performs optimization processing on the first database query statement to generate a second database query statement, where the generated second database query statement is equivalent to the first database query statement before processing.

Specifically, the generated second database query statement includes the second view in the FROM clause. The second view includes a first window function in the first database query statement. The second view also comprises a connection operation of the first data table and a second data table, wherein the second data table is a data table corresponding to the first view. It should be understood that the second data table being a data table corresponding to the first view may mean that the query statement in the first view is directed to the second data table, i.e. the second data table is a table in the first view. As an example, the first VIEW is (SELECT MAX (C1) OVER (PARTITION BY (C2, C3)), C2, C3 FROM T3) VIEW 1), the query in the first VIEW is made to Table T3, and the second data table is T3.

The connection operation including the first data table and the second data table in the second view may mean that the connection type (hereinafter, referred to as a second connection type) and the connection condition (hereinafter, referred to as a second connection condition) of the first data table and the second data table are included in the second view. The embodiment of the present disclosure does not limit specific contents of the second connection type and the second connection condition, for example, the second connection type is an internal connection, and the second connection condition is an equivalent connection condition.

It should be noted that, in the embodiments of the present disclosure, the first connection type and the second connection type may be the same, for example, both are internal connections; the first connection condition is equivalent to the second connection condition.

In some embodiments, when the first database query statement further includes a filter condition only on the first data table, the second view may further include (encapsulate) the filter condition only on the first data table in the second database query statement generated according to the first database query statement. As an example, in the foregoing SQL2 statement, after generating the second database query statement according to the SQL2 statement, the second view may further include a filter condition: t1.c2= 5.

In some embodiments, after generating the second database query statement according to step S120, if the second database query statement further includes a join operation between the second view and another data table, steps S110 to S120 may be repeated based on the second database query statement to generate a new database query statement.

An example of the second database query statement generated is given below by taking an SQL2 statement as an example of the first database query statement. After optimizing the SQL2 statement, the generated second database query statement SQL3 statement may be:

SELECT *

FROM T0,T2,

(SELECT MAX (T3.C1) OVER (PARTITION BY (T3.C2, T3.C3)), T3.C2,

T3.C3, T1.*

FROM T3, T1

WHERE T3.C2=T1.UNIQUE AND T1.C2=5) VIEW2

WHERE VIEW2.T1.UNIQUE=T2.C1 AND T0.C1=T2.C3;

wherein, (SELECT MAX (T3.c1) OVER (partial BY (T3.c2, T3.c3)), T3.c2, T3.c3, T1

FROM T3, T1

WHERE T3.c2= T1.unique AND T1.c2=5) VIEW2 is a second VIEW including the first window function MAX (T3.c1) OVER (PARTITION BY (T3.c2, T3.c3)), AND the connecting operation of the first data table T1 AND the second data table T3. It can be seen that in this SQL3 statement, the second view also includes the filter condition T1.c2=5 only with respect to the first data table T1. At this time, after the connection of T3 and T1, the number of lines will be reduced compared to T3 (the number of lines to be filtered depends on the connection condition and the filterability of the filter condition). In this case, the calculation of the connection is unchanged, but the amount of calculation of the first window function is greatly reduced.

According to the embodiment of the disclosure, the first database query statement is rewritten to generate the equivalent second database query statement, so that the execution order of the connection operation in the second database query statement is earlier than that of the first window function (i.e., the execution orders of the connection operation and the window function are exchanged), so that the query optimizer can generate a better execution plan, and thus the execution efficiency of the first database query statement can be improved.

Fig. 2 is a schematic flow chart of one possible implementation of step S120. As shown in fig. 2, the method may include step S122 and step S124. A specific implementation process of generating the second database query statement according to the first database query statement in step S120 is described below with reference to fig. 2.

In step S122, it is determined whether the connection operation between the first view and the first data table can be pushed down (or called, pushed down) to the inside of the first view.

As one implementation, whether the connection of the first view to the first data table can be pushed down to the inside of the first view may be determined according to a first condition. In other words, it is determined whether the first view and the first data table satisfy a first condition, and in the case where the first condition is satisfied, it can be considered that the connection operation of the first view and the first data table can be pushed down to the inside of the first view.

Wherein the first condition comprises: an equivalence join condition exists between the first view and the first data table, and an expression in the equivalence join condition with respect to the first view depends on the grouping field in the first window function. In other words, when there is an equal value connection condition E1= E2 between the first view and the first data table (E1 is an expression on the first view, E2 is an expression of the first data table), and E1 depends on an expression in the PARTITION BY in the first window function, it can be considered that the connection operation of the first view and the first data table can be pushed down to the inside of the first view. As a specific example, the first window functions are grouped in columns C2 and C3 of Table T3, then the expression for E1 should be an expression that is dependent on columns C2 and/or C3.

It should be noted that the expression related to the first view depends on the grouping field in the first window function, which may mean that the expression related to the first view is an expression related to the grouping field in the first window function, for example, the expression related to the first view includes at least one grouping field in the first window function. Illustratively, when there is only one packet field in the first window function, the packet field is included in the expression for the first view; alternatively, when there are multiple (two or more) packet fields in the first window function, the expression for the first view contains at least one of the multiple packet fields.

In some embodiments, the first condition is more stringent in the case where a second window function is also encapsulated in the first view (i.e., the first view includes multiple window functions). Specifically, the first condition may include: an iso join condition exists between the first view and the first data table, and an expression in the iso join condition for the first view depends on an intersection of the grouping field in the first window function and the grouping field in the second window function. In other words, when there is an equal value connection condition E1= E2 between the first view and the first data table (E1 is an expression on the first view, and E2 is an expression of the first data table), and E1 depends on both an expression in the PARTITION BY in the first window function and an expression in the PARTITION BY in the second window function, it can be considered that the connection operation of the first view and the first data table can be pushed down to the inside of the first view. As a specific example, if the first window function is grouped in columns C2 and C3 of Table T3, and the second window function is grouped in column C2 of Table T3, then the expression for E1 should be the expression associated with column C2.

In step S124, if the connection operation between the first view and the first data table can be pushed down to the inside of the first view, the connection between the first view and the first data table is pushed down to the inside of the first view according to the first database query statement, resulting in a second database query statement.

As a specific implementation method, when it is determined that the connection operation between the first view and the first data table can be pushed down to the inside of the first view, the first database query statement may be equivalently rewritten, the connection operation (including the connection type, the connection condition, and the like) about the first data table and the first view in the FROM clause is pushed down to the inside of the first view, whether the filter condition only about the first data table is included in the FROM clause is checked, and if any, the filter condition is pushed down to the inside of the first view.

Taking the SQL2 statement above as an example of the first database query statement, the first VIEW1 includes a first window function, and the first VIEW1 and the first data table T1 satisfy a first condition (there is an equal value connection condition T1. unity = VIEW w1.c2, and VIEW w1.c2 is a partition column in the first window function). At this point, table T1 may be pushed down into first VIEW1.

Fig. 3 is a flowchart illustrating a method for processing a database query statement according to another embodiment of the disclosure. As shown in fig. 3, the method may include steps S310 to S340. Step S310 and step S320 are the same as step S110 and step S120 described above, and for the description of step S310 and step S320, reference may be made to the description of step S110 and step S120 described above, and details are not repeated here. Step S330 and step S340 are described in detail below.

In step S330, it is checked whether the expression regarding the first data table in the first connection condition satisfies uniqueness. As described above, the first connection condition is a connection condition corresponding to a connection operation of the first view and the first data table, for example, the first connection condition is an equal value connection condition.

The embodiment of the present disclosure does not specifically limit the manner of inspection. As one implementation, the check may be performed by determining whether there is a unique key (unique key) in the expression regarding the first data table in the first connection condition. If the judgment result is that the expression related to the first data table in the first connection condition has a unique key, the uniqueness is considered to be satisfied; if the judgment result is that the expression related to the first data table in the first connection condition has no unique key, the uniqueness is not considered to be satisfied. In some embodiments, the unique key may refer to a primary key. In some embodiments, a unique key may refer to a unique index.

The time of the examination is not particularly limited in the embodiments of the present disclosure. In some embodiments, the checking may be performed prior to generating the second database query statement. In some embodiments, the check may also be performed after the second database query statement is generated. In other words, the execution order of step S320 and step S330 is not specifically limited in the embodiments of the present disclosure. The flowchart in fig. 3 is not intended to limit the embodiment of the disclosure to execute step S320 and then execute step S330. In the embodiment of the present disclosure, step S320 may be performed first, and then step S330 is performed, or step S330 may be performed first, and then step S320 is performed.

In step S340, if the expression about the first data table does not satisfy uniqueness, a unique key of the first data table is added in the grouping field of the first window function, for example, a primary key of the first data table may be added as the unique key.

Taking the SQL2 statement shown above as an example, the first VIEW1 and the first data table T1 have a first connection condition T1.unique = VIEW w1.c2, and the unique key in the expression T1.unique of the first connection condition with respect to the first data table T1 is considered to be satisfied.

After T1 is pushed down to get SQL3 statement, it can be seen that T2 and VIEW2 also have satisfied connection condition at this time, because T1.unique = T3.c2, it can be deduced that T2.c1= T3.c 2. So one can try to push table T2 down into VIEW 2. However, when it is checked whether the expression about the first data table T2 in the first connection condition satisfies the uniqueness, it is found that the expression T2.c1 about the first data table T2 in the first connection condition has no unique key, and it can be considered that the uniqueness is not satisfied.

At this time, in the case where the uniqueness is not satisfied, when rewriting the first database query statement SQL3 statement, a unique key of table T2 may be added in the first view to generate the second view. Specifically, when the SQL3 statement is used as the first database query statement, the generated second database query statement SQL4 statement may be expressed as:

SELECT *

FROM T0,

(SELECT MAX (T3.C1) OVER (PARTITION BY (T3.C2, T3.C3, T2.PKEY)),

T3.C2, T3.C3, T1.*, T2.*

FROM T3, T1, T2

WHERE T3.C2=T1.UNIQUE AND T1.C2=5 AND T2.C1=T1.UNIQUE) VIEW3

WHERE T0.C1=VIEW1.T2.C3;

After pushing down table T2, the last table T0 also satisfies the push down condition because T2.pkey uniquely maps one T2.c 3.T 0.c1 and view3.t2.c3 connections can be considered to be in progress with the partition key. After pushing T0 down, because T0.c1 is still not the only key, an additional unique key of T0, such as the primary key T0.pkey, needs to be added to the first window function.

When the expression in the first connection condition about the first data table does not satisfy uniqueness, by adding a unique key of the first data table in the packet field of the first window function, the correctness of the result (second database query statement) after rewriting can be guaranteed.

In some embodiments, if the expression in the first connection condition with respect to the first data table does not satisfy uniqueness, an execution cost of the second database query statement may also be calculated to determine whether to generate the second database query statement. This is because, when the expression about the first data table in the first connection condition does not satisfy uniqueness, the magnitude relationship between the number of lines after the connection of the first data table and the second data table is preferentially performed as compared with the number of lines after the connection of the first data table and the first view is uncertain, and it is necessary to determine whether this push-down is a good push-down by calculating the execution cost.

As an example, when the calculation result is that the execution cost of the second database query statement is less than that of the first database query statement, it may be determined to generate the second database query statement, so as to reduce the data amount of the intermediate result set to improve the query efficiency.

As another example, when the computation result is that the execution cost of the second database query statement is greater than that of the first database query statement, it may be determined that the second database query statement is not generated based on the first view and the first data table (i.e., the push-down of the first data table is denied), and then a new attempt may be made to find a new first data table, so as to generate the second database query statement according to the new first data table and the first view, thereby ensuring that the finally obtained intermediate result set of the second database query statement has a smaller data amount and higher query efficiency.

As yet another example, when the computation results in the second database query statement being executed more expensively than the first database query statement, it may be determined to generate the second database query statement (i.e., to affirm the push down of the first data table) so that other data tables may be subsequently pushed down. In other words, although the currently pushed-down result is not optimal, it is helpful to find a more optimal solution later, so that it can be ensured that the finally obtained intermediate result set of the second database query statement has a smaller data volume and higher query efficiency.

Fig. 4 is a flowchart illustrating a method for processing a database query statement according to yet another embodiment of the disclosure. As shown in fig. 4, the method includes steps S410 to S430.

In step S410, a first database query statement is received. The first database query statement comprises a first view in the FROM clause and a connection operation of the first view and the first data table, and a first window function is packaged in the first view. For details of step S410, reference may be made to step S110 described above, and details are not repeated here.

In step S420, the first view is checked for legitimacy.

In some embodiments, when some specific clauses are included in the first view and the execution order of the specific clauses is after the join operation, the normal execution order is that the specific clauses are executed in the first view, and the join operation is performed on the result set obtained by executing the specific clauses. In the embodiment of the present disclosure, if the join operation is performed prior to executing the specific clause, the execution result may be incorrect. Based on this, after receiving the first database query statement, the embodiment of the present disclosure may further check the validity of the first view, and perform the subsequent step S430 only when the first view does not include the specific clause.

The specific content of the specific clause is not limited in the embodiment of the present disclosure, as long as the existence of the specific clause can cause the final execution result to be incorrect after the sequence of the join operation is advanced. Several examples of specific clauses are given below.

As an example, when the first view includes clauses such as GROUP BY, ROLL UP, etc., the first view is considered illegal. As another example, when the first view includes a clause such as LIMIT, ROWNUM, etc., the first view is considered illegal. As yet another example, when the first view is a hierarchical query or set operation, the first view is considered illegal.

In step S430, in case the first view is legal, a second database query statement is generated from the first database query statement. The second database query statement is equivalent to the first database query statement. The second database query statement comprises a second view in the FROM clause, a first window function is packaged in the second view, and the connection operation of the first data table and a second data table is carried out, wherein the second data table is a data table corresponding to the first view. For details of the step S430 of generating the second database query statement according to the first database query statement, reference may be made to the step S120 described above, and details are not repeated here.

A specific example of an embodiment of the present disclosure is given below in conjunction with fig. 5.

Fig. 5 is a flowchart illustrating a method for processing a database query statement according to yet another embodiment of the disclosure. In this example, the first database query statement may include a plurality of views, and the views may include a plurality of window functions (e.g., a first window function and a second window function). Specifically, referring to fig. 5, after the query optimizer receives the first database query statement, the first view may be determined (i.e., whether the view with the window function is included in the list of FROM clauses) and checked for validity. If the first view is illegal, re-determining a new first view; if the first view is legal, the intersection of the packet field in the first window function and the packet field in the second window function encapsulated in the first view is calculated. Further, the query optimizer may determine a first data table (e.g., randomly select one) and determine whether the first data table and the first view satisfy a first condition. If the first condition is not met, re-determining the first data table; if the first condition is satisfied, a join operation of the first data table and the first view (e.g., equal join conditions of the first data table, and the first view, and a filter condition only with respect to the first data table) is pushed down to the inside of the first view. After the push-down of the connection operation of the first data table and the first view is completed, whether the expression related to the first data table in the first connection condition meets the uniqueness or not can be checked, and if the uniqueness is met, a second database query statement is directly generated; if the uniqueness is not satisfied, a second database query statement is generated after adding the unique key of the first data table in the grouping field of the first window function.

Method embodiments of the present disclosure are described in detail above in conjunction with fig. 1-5. Embodiments of the apparatus provided by the present disclosure are described below with reference to fig. 6 to 7. It should be understood that the description of the apparatus embodiments corresponds to the description of the method embodiments and therefore reference may be made to the previous method embodiments for those parts not described in detail.

Fig. 6 is a schematic structural diagram of an apparatus for processing a database query statement according to an embodiment of the present disclosure. The apparatus 600 shown in fig. 6 may be, for example, a query optimizer. The apparatus 600 may include a receiving module 610 and a generating module 620.

The receiving module 610 is configured to receive a first database query statement, where the first database query statement includes a first view in a FROM clause, and a connection operation between the first view and a first data table, where a first window function is encapsulated in the first view.

The generating module 620 is configured to generate a second database query statement according to the first database query statement, where the second database query statement is equivalent to the first database query statement, the second database query statement includes a second view in the FROM clause, a first window function is encapsulated in the second view, and a connection operation between the first data table and a second data table is performed, where the second data table is a data table corresponding to the first view.

Optionally, the generating module 620 further comprises: the judging module is used for judging whether the connection operation of the first view and the first data table can be pushed down to the inside of the first view; and the push-down module is used for pushing down the connection operation between the first view and the first data table to the inside of the first view according to the first database query statement to obtain a second database query statement if the connection operation between the first view and the first data table can be pushed down to the inside of the first view.

Optionally, the determining module is further configured to: judging whether the first view and the first data table meet a first condition, and under the condition that the first condition is met, considering that the connection operation of the first view and the first data table can be pushed down to the inside of the first view; wherein the first condition comprises: an isojoin condition exists between the first view and the first data table, and an expression in the isojoin condition for the first view depends on the packet field in the first window function.

Optionally, the apparatus 600 further comprises: the first checking module is used for checking whether an expression related to the first data table in a first connection condition meets uniqueness, wherein the first connection condition is a connection condition corresponding to connection operation of the first view and the first data table; an adding module for adding a unique key of the first data table in the grouping field of the first window function if the expression about the first data table does not satisfy uniqueness.

Optionally, the apparatus 600 further comprises: and the calculation module is used for calculating the execution cost of the second database query statement to determine whether to generate the second database query statement or not if the expression related to the first data table does not meet the uniqueness.

Optionally, the apparatus 600 further comprises: the second checking module is used for checking the validity of the first view; the generating module is further used for generating a second database query statement according to the first database query statement under the condition that the first view is legal.

Optionally, in a case where a second window function is further encapsulated in the first view, the first condition includes: an isojoin condition exists between the first view and the first data table, and an expression in the isojoin condition for the first view depends on an intersection of the grouping field in the first window function and the grouping field in the second window function.

Optionally, the second view also has a filter condition only for the first data table encapsulated therein.

Fig. 7 is a schematic structural diagram of an apparatus for processing a database query statement according to another embodiment of the present disclosure. The apparatus 700 shown in fig. 7 may include a memory 710 and a processor 720.

Memory 710 may be used to store instructions. The processor 720 may be configured to execute instructions stored in the memory 710 to implement the steps of the various methods described previously.

In some embodiments, the apparatus 700 may further include a network interface 730, and the data exchange between the processor 720 and the external device may be implemented through the network interface 730.

In the above embodiments, all or part of the implementation may be realized by software, hardware, firmware or any other combination. When implemented in software, may be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. The procedures or functions described in accordance with the embodiments of the disclosure are, in whole or in part, generated when the computer program instructions are loaded and executed on a computer. The computer may be a general purpose computer, a special purpose computer, a network of computers, or other programmable device. The computer instructions may be stored on a computer readable storage medium or transmitted from one computer readable storage medium to another, for example, from one website, computer, server, or data center to another website, computer, server, or data center via wire (e.g., coaxial cable, fiber optic, Digital Subscriber Line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.). The computer-readable storage medium can be any available medium that can be accessed by a computer or a data storage device, such as a server, a data center, etc., that incorporates one or more of the available media. The usable medium may be a magnetic medium (e.g., floppy Disk, hard Disk, magnetic tape), an optical medium (e.g., Digital Video Disk (DVD)), or a semiconductor medium (e.g., Solid State Disk (SSD)), among others.

Those of ordinary skill in the art will appreciate that the various illustrative elements and algorithm steps described in connection with the embodiments disclosed herein may be implemented as electronic hardware, or combinations of computer software and electronic hardware. Whether such functionality is implemented as hardware or software depends upon the particular application and design constraints imposed on the technical solution. Skilled artisans may implement the described functionality in varying ways for each particular application, but such implementation decisions should not be interpreted as causing a departure from the scope of the present disclosure.

In the several embodiments provided in the present disclosure, it should be understood that the disclosed system, apparatus and method may be implemented in other ways. For example, the above-described apparatus embodiments are merely illustrative, and for example, the division of the units is only one logical division, and other divisions may be realized in practice, for example, a plurality of units or components may be combined or integrated into another system, or some features may be omitted, or not executed. In addition, the shown or discussed mutual coupling or direct coupling or communication connection may be an indirect coupling or communication connection through some interfaces, devices or units, and may be in an electrical, mechanical or other form.

The units described as separate parts may or may not be physically separate, and parts displayed as units may or may not be physical units, may be located in one position, or may be distributed on multiple network units. Some or all of the units can be selected according to actual needs to achieve the purpose of the solution of the embodiment.

In addition, functional units in the embodiments of the present disclosure may be integrated into one processing unit, or each unit may exist alone physically, or two or more units are integrated into one unit.

The above description is only for the specific embodiments of the present disclosure, but the scope of the present disclosure is not limited thereto, and any person skilled in the art can easily conceive of the changes or substitutions within the technical scope of the present disclosure, and all the changes or substitutions should be covered within the scope of the present disclosure. Therefore, the protection scope of the present disclosure shall be subject to the protection scope of the claims.

Claims

1. A method of processing a database query statement, comprising:

receiving a first database query statement, wherein the first database query statement comprises a first view in a FROM clause and a connection operation of the first view and a first data table, and a first window function is packaged in the first view;

Generating a second database query statement according to the first database query statement, wherein the second database query statement is equivalent to the first database query statement, the second database query statement comprises a second view in a FROM clause, the second view is packaged with the first window function and a connection operation between the first data table and a second data table, and the second data table is a data table corresponding to the first view.

2. The method of claim 1, the generating a second database query statement from the first database query statement, comprising:

judging whether the connection operation of the first view and the first data table can be pushed down to the inside of the first view or not;

and if the connection operation of the first view and the first data table can be pushed down to the inside of the first view, pushing the connection operation of the first view and the first data table down to the inside of the first view according to the first database query statement to obtain a second database query statement.

3. The method of claim 2, wherein the determining whether the join operation of the first view with the first data table can be pushed down to the inside of the first view comprises:

Judging whether the first view and the first data table meet a first condition, and if so, considering that the connection operation of the first view and the first data table can be pushed down to the inside of the first view;

wherein the first condition comprises: an iso-join condition exists between the first view and the first data table, and an expression in the iso-join condition with respect to the first view depends on a packet field in the first window function.

4. The method of claim 1, further comprising:

checking whether an expression about the first data table in a first connection condition satisfies uniqueness, wherein the first connection condition is a connection condition corresponding to connection operations of the first view and the first data table;

adding a unique key of the first data table in a grouping field of the first window function if the expression for the first data table does not satisfy uniqueness.

5. The method of claim 4, after said checking whether an expression in a first connection condition with respect to the first data table satisfies uniqueness, the method further comprising:

If the expression for the first data table does not satisfy uniqueness, computing an execution cost of the second database query statement to determine whether to generate the second database query statement.

6. The method of claim 1, prior to the generating a second database query statement from the first database query statement, the method further comprising:

checking the validity of the first view;

the generating a second database query statement according to the first database query statement includes:

and under the condition that the first view is legal, generating the second database query statement according to the first database query statement.

7. The method of claim 3, where a second window function is further encapsulated in the first view, the first condition comprising: an isojoin condition exists between the first view and the first data table, and an expression in the isojoin condition for the first view depends on an intersection of a grouping field in the first window function and a grouping field in the second window function.

8. The method of claim 1, further encapsulated in the second view is a filter condition only with respect to the first data table.

9. An apparatus for processing a database query statement, comprising:

the system comprises a receiving module, a searching module and a processing module, wherein the receiving module is used for receiving a first database query statement, the first database query statement comprises a first view in a FROM clause and a connection operation between the first view and a first data table, and a first window function is packaged in the first view;

a generating module, configured to generate a second database query statement according to the first database query statement, where the second database query statement is equivalent to the first database query statement, the second database query statement includes a second view in a FROM clause, the second view is encapsulated with the first window function, and the first data table and a second data table are connected through a connection operation, and the second data table is a data table corresponding to the first view.

10. The apparatus of claim 9, the generating module further comprising:

the judging module is used for judging whether the connection operation between the first view and the first data table can be pushed down to the inside of the first view;

and the push-down module is used for pushing down the connection operation between the first view and the first data table to the inside of the first view according to the first database query statement to obtain the second database query statement if the connection operation between the first view and the first data table can be pushed down to the inside of the first view.

11. The apparatus of claim 10, the determining module further configured to:

12. The apparatus of claim 9, the apparatus further comprising:

the first checking module is used for checking whether an expression related to the first data table in a first connection condition meets uniqueness, wherein the first connection condition is a connection condition corresponding to connection operation of the first view and the first data table;

an adding module for adding a unique key of the first data table in a grouping field of the first window function if the expression about the first data table does not satisfy uniqueness.

13. The apparatus of claim 12, the apparatus further comprising:

A calculation module to calculate an execution cost of the second database query statement to determine whether to generate the second database query statement if the expression for the first data table does not satisfy uniqueness.

14. The apparatus of claim 9, the apparatus further comprising:

the second checking module is used for checking the validity of the first view;

the generating module is further configured to generate the second database query statement according to the first database query statement when the first view is legal.

15. The apparatus of claim 11, the first condition, with a second window function also encapsulated in the first view, comprising: an isojoin condition exists between the first view and the first data table, and an expression in the isojoin condition for the first view depends on an intersection of a grouping field in the first window function and a grouping field in the second window function.

16. The apparatus of claim 9, further packaged in the second view is a filter condition only with respect to the first data table.

17. An apparatus for processing a database query statement, comprising a processor and a memory, the memory having stored therein executable code, the processor being configured to execute the executable code to implement the method of any one of claims 1-8.