CN110990423B - SQL statement execution method, device, equipment and storage medium - Google Patents

SQL statement execution method, device, equipment and storage medium Download PDF

Info

Publication number
CN110990423B
CN110990423B CN201911278107.1A CN201911278107A CN110990423B CN 110990423 B CN110990423 B CN 110990423B CN 201911278107 A CN201911278107 A CN 201911278107A CN 110990423 B CN110990423 B CN 110990423B
Authority
CN
China
Prior art keywords
node
target
original
constant
original node
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Active
Application number
CN201911278107.1A
Other languages
Chinese (zh)
Other versions
CN110990423A (en
Inventor
刘志勇
张钦
韩朱忠
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Shanghai Dameng Database Co Ltd
Original Assignee
Shanghai Dameng Database Co Ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Shanghai Dameng Database Co Ltd filed Critical Shanghai Dameng Database Co Ltd
Priority to CN201911278107.1A priority Critical patent/CN110990423B/en
Publication of CN110990423A publication Critical patent/CN110990423A/en
Application granted granted Critical
Publication of CN110990423B publication Critical patent/CN110990423B/en
Active legal-status Critical Current
Anticipated expiration legal-status Critical

Links

Images

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/242—Query formulation
    • G06F16/2433—Query languages
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/245—Query processing
    • G06F16/2455—Query execution
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/28—Databases characterised by their database models, e.g. relational or object models
    • G06F16/284—Relational databases
    • Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
    • Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
    • Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management

Abstract

The embodiment of the invention discloses an execution method, device, equipment and storage medium of SQL statements. The method comprises the following steps: acquiring an original relation tree of SQL sentences; sequentially judging whether each original node in the original relationship tree is a target filtering node or not, and deleting at least one target column name contained in a first target operation condition of a first target node corresponding to the original node when the original node is judged to be the target filtering node so as to obtain a new relationship tree; and executing the SQL statement according to the new relation tree. By adopting the technical scheme, the embodiment of the invention can reduce the times of the grouping calculation of the SQL sentences and improve the query efficiency of the database.

Description

SQL statement execution method, device, equipment and storage medium
Technical Field
The invention relates to the technical field of databases, in particular to an execution method, an execution device, execution equipment and a storage medium of SQL statements.
Background
Structured Query Language (SQL) is the most important and most commonly used relational database operating Language.
The grouping clause (GROUP BY clause) is a clause used for grouping data in the SQL standard. In the current general database technology, when an SQL query statement including a GROUP BY clause is implemented, no matter what the table structure and data characteristics corresponding to the SQL query statement are, data in the table needs to be read and grouped according to grouping items specified BY the GROUP BY clause.
However, when the database actually faces a more complex SQL query statement, it usually takes more time to perform grouping in the current general grouping manner, so that the query efficiency of the database is low.
Disclosure of Invention
In view of this, embodiments of the present invention provide a method, an apparatus, a device and a storage medium for executing an SQL statement, so as to reduce the time taken for executing the SQL query statement and improve the query efficiency of a database.
In a first aspect, an embodiment of the present invention provides an execution method of an SQL statement, including:
acquiring an original relation tree of SQL sentences;
sequentially judging whether each original node in the original relationship tree is a target filtering node or not, and deleting at least one target column name contained in a first target operation condition of a first target node corresponding to the original node when the original node is judged to be the target filtering node so as to obtain a new relationship tree;
executing the SQL statement according to the new relation tree;
the filtering conditions of the target filtering nodes comprise constant quantum expressions and do not comprise OR operations and NOT operations; the first target node corresponding to the original node comprises a grouping node in an ancestor node of the original node, the first target operation condition comprises at least column names, target column names exist in the at least two column names, and the target column names are constant column names in the constant sub-expression or alias names of the constant column names.
In a second aspect, an embodiment of the present invention provides an apparatus for executing an SQL statement, including:
the acquisition module is used for acquiring an original relation tree of the SQL statement;
the deleting module is used for sequentially judging whether each original node in the original relationship tree is a target filtering node or not, and deleting at least one target column name contained in a first target operation condition of a first target node corresponding to the original node when the original node is judged to be the target filtering node so as to obtain a new relationship tree;
the execution module is used for executing the SQL statement according to the new relation tree;
the filtering condition of the target filtering node comprises a constant quantum expression and does not comprise an OR operation and a NOT operation; the first target node corresponding to the original node comprises a grouping node in an ancestor node of the original node, the first target operation condition comprises at least column names, target column names exist in the at least two column names, and the target column names are constant column names in the constant sub-expression or alias names of the constant column names.
In a third aspect, an embodiment of the present invention provides a computer device, including:
one or more processors;
a memory for storing one or more programs,
when the one or more programs are executed by the one or more processors, the one or more processors implement the execution method of the SQL statement according to the embodiment of the present invention.
In a fourth aspect, an embodiment of the present invention further provides a computer-readable storage medium, on which a computer program is stored, where the computer program is executed by a processor to implement the execution method of the SQL statement according to the embodiment of the present invention.
In the technical scheme for executing the SQL statement, an original relation tree of the SQL statement is obtained; sequentially judging whether each original node in the original relation tree is a target filtering node which contains a constant quantum expression in a filtering condition and does not contain an OR operation and a NOT operation, when a certain original node is judged to be the target filtering node, determining a grouping node which contains at least two column names and at least one column name in an ancestor node of the original node as a first target node, deleting at least one target column name in the first target node to generate a new relation tree, and then executing the SQL statement according to the new relation tree. By adopting the technical scheme, the embodiment of the invention deletes the constant grouping items in the grouping nodes, can reduce the times of grouping calculation of SQL sentences and improve the query efficiency of the database.
Drawings
Other features, objects and advantages of the invention will become more apparent upon reading of the detailed description of non-limiting embodiments made with reference to the following drawings:
fig. 1 is a schematic flowchart of an execution method of an SQL statement according to an embodiment of the present invention;
fig. 2 is a schematic structural diagram of an original relationship tree according to an embodiment of the present invention;
FIG. 3 is a schematic structural diagram of a new relationship tree according to an embodiment of the present invention;
fig. 4 is a schematic flowchart of an execution method of an SQL statement according to a second embodiment of the present invention;
fig. 5 is a schematic structural diagram of an original relationship tree according to a second embodiment of the present invention;
fig. 6 is a schematic structural diagram of a new relationship tree according to a second embodiment of the present invention;
fig. 7 is a block diagram of a structure of an execution apparatus for an SQL statement according to a third embodiment of the present invention;
fig. 8 is a schematic structural diagram of a computer device according to a fourth embodiment of the present invention.
Detailed Description
The present invention will be described in further detail with reference to the accompanying drawings and examples. It is to be understood that the specific embodiments described herein are merely illustrative of the invention and are not to be construed as limiting the invention. It should be further noted that, for the convenience of description, only some but not all of the relevant aspects of the present invention are shown in the drawings. In addition, the embodiments and features of the embodiments of the present invention may be combined with each other without conflict
Example one
The embodiment of the invention provides an execution method of an SQL statement. The method can be executed by an execution device of the SQL statement, wherein the device can be realized by software and/or hardware, can be generally integrated in computer equipment, and is suitable for a scene of constant replacement of the SQL statement. Fig. 1 is a schematic flowchart of an execution method of an SQL statement according to an embodiment of the present invention, as shown in fig. 1, the method includes:
s101, obtaining an original relation tree of the SQL statement.
In this embodiment, after generating the SQL statement, the SQL statement is converted into the relational tree, so as to obtain the original relational tree of the SQL statement). Correspondingly, when any type of SQL statement input by a user is received, regardless of whether the SQL statement contains a where filtering clause, the SQL statement may be converted into a relational tree and an operation of subsequently determining whether each original node in the original relational tree is a target filtering node may be performed on the relational tree. Considering that when the SQL statement does not include the where filter clause, each original node in the relational tree is not necessarily a target filter node, the SQL statement in this embodiment is preferably an SQL query statement that includes the where filter clause, so as to avoid performing unnecessary judgment on each original node in the original relational tree of the SQL statement in the following. In the following, the SQL query statement including the where filter clause is the SQL statement will be described as an example.
Exemplarily, after the SQL statement is obtained, the database may perform syntax and semantic analysis on the SQL statement, determine whether the SQL statement includes a where filter clause, if so, determine that the SQL statement is an SQL query statement, and perform subsequent operations on the SQL query statement; otherwise, generating an original execution plan directly based on the original relational tree of the SQL statement, and executing the SQL statement according to the original execution plan. Or in the process of generating the original relational tree of the SQL statement, each time an original node is added to the original relational tree, an operation of judging whether the original node is a filtering node is executed, and when a certain original node is judged to be a filtering node, the SQL statement is determined to be the SQL query statement containing the where filtering clause, and the judging operation is no longer executed on the subsequently added original node, if no filtering node appears until the generation of the original relational tree is finished, the original execution plan is directly generated based on the generated original relational tree, and the SQL statement is executed according to the original execution plan.
S102, sequentially judging whether each original node in the original relationship tree is a target filtering node or not, and deleting at least one target column name contained in a first target operation condition of a first target node corresponding to the original node when the original node is judged to be the target filtering node so as to obtain a new relationship tree.
The filtering condition of the target filtering node comprises a constant quantum expression and does not comprise an OR operation and a NOT operation; the first target node corresponding to the original node comprises a grouping node in an ancestor node of the original node, the first target operation condition comprises at least two column names, a target column name exists in the at least two column names, and the target column name is a constant column name in the constant sub-expression or an alias of the constant column name.
In this step, the filtering node is a node in the relational tree that contains a where filtering condition, and the target filtering node is a filtering node that contains a constant quantum expression in the filtering condition and does not contain an or operation and a non-operation; a first target node corresponding to a certain original node comprises a grouping node in an ancestor node of the original node, the first target operation condition comprises at least two column names, and the target column names of constant sub-expressions contained in the original node exist in the at least two column names; the node types of the nodes in the relational tree of the database may include, but are not limited to, base TABLE node (REL _ TABLE), filter node (REL _ SELECT), result set projection node (REL _ PROJECT), sort node (REL _ ORDER), inner connection node, LEFT outer connection node (REL _ LEFT), right outer connection node, all outer connection node, half connection node, set operation node (REL _ UNIONALL), grouping node (REL _ GROUP), fetch specified N row node (REL _ top, i.e., SELECT top N operation), hierarchical query node, deduplication node (REL _ distincnt), fetch front N row node (REL _ row), and analysis function node (REL _ AFUN), and so on.
In this embodiment, for an SQL query statement including a where filter condition, when a certain grouping item in the filter condition is a constant, that is, when the filter condition includes a constant quantum expression, values of columns (that is, constant columns) corresponding to the filtering grouping item are all the constant, in this case, even if the filtering data is grouped according to the constant columns, it is only equivalent to dividing all the filtering data into the same group, and it is equivalent to removing the grouping item. For example, assume that Table T1 is defined as: CREATE TABLE T1 (C1 INT, C2 INT); the original SQL query statement is: select c1, c2 from t1 where c2=1group by c1, c2; since the value of the c2 column in the filtering result is constant 1, the classification result obtained by classifying the filtering result according to c1 and c2 is the same as the filtering result obtained by classifying the filtering result only according to c1, that is, the original SQL query statement is actually equivalent to: select c1,1from t1 where c2=1group by c1. Therefore, in this embodiment, when the filtering condition of the filtering node includes the constant sub-expression, the target grouping item in the grouping item grouping the filtering result of the filtering node, the expression of which is the same as the constant sub-expression, is deleted, so as to achieve the purpose of improving the query efficiency.
The constant sub-expression can be understood as an expression of a constant filter term in the target filter node, which is an equation, and one side of the equal sign is a constant column name and the other side is a constant. Since there is not necessarily only one value in the corresponding column of the filtering result when the or operation or the nor operation is performed, in this embodiment, the target filtering node is a filtering node whose expression includes the constant sub-expression and does not include the or operation and the no operation, that is, the filtering condition of the target filtering node is composed of one or more filtering terms, and when the target filtering node is composed of one filtering term, the expression of the filtering term should be the constant sub-expression; when the filtering condition of the target filtering node is composed of a plurality of filtering terms, at least one filtering term of which the expression is a constant sub-expression is included in the plurality of filtering terms, and the expressions are connected through related characters (such as and) representing and operating.
In order to ensure that the SQL statement can be executed correctly based on the obtained new relational tree, at least one column name still exists in the operation condition of the first target node after the target column name is deleted (i.e., at least one operation item is ensured to be included in the operation condition), therefore, in this embodiment, the number of column names included in the operation condition of the first target operation node needs to be further limited to be greater than or equal to 2. Wherein an ancestor node of an original node comprises a parent node of the original node; the operation condition of the first target node comprises at least one target column name, and the node type of the first target node comprises but is not limited to a grouping node, such as a sorting node and/or a deduplication node.
For example, the execution process of this step may be: sequencing each node in the original relation tree according to a certain sequencing rule (such as first-order traversal, middle-order traversal, last-order traversal or random sequencing) and selecting a first node in a sequencing result as a current original node; judging whether the current original node is a filtering node, if so, continuously judging whether the current original node contains a constant sub-expression, if so, further judging whether the current original node contains an OR operation or a NOR operation, if not, determining the current original node as a target filtering node, and deleting at least one target column name contained in a first target operation condition of a first target node corresponding to the current original node; and if the current original node contains OR operation and/or NOR operation, the current original node does not contain the constant sub expression or is not a filtering node, selecting the next node adjacent to the current original node as the current original node according to the sequencing result, returning and executing the operation of judging whether the current original node is the filtering node or not, and repeating the steps until all the nodes are judged to be finished, thereby finishing the deletion of the target column name contained in the first target operation condition of the first target node corresponding to the original node which is determined as the target filtering node in the original relation tree.
In this step, when deleting a target column name included in a first target operation condition of a first target node corresponding to a certain original node, if only one constant sub-expression is included in a filter condition of the original node, or if a plurality of constant sub-expressions are included in the filter condition of the original node and only one target column name is included in the first target operation condition of the first target node, only one target column name in the first target operation condition may be deleted; if the filter condition of the original node includes a plurality of constant sub-expressions and the first target operation condition of the first target node includes a plurality of target column names, one or more target column names in the first target operation condition may be deleted, which is not limited in this embodiment.
In order to further improve the query efficiency of the database, preferably, in this embodiment, the target column names in the first target operation condition of the first target node may be deleted as many as possible on the premise of ensuring that at least one column name remains in the first target operation condition after the target column names are deleted. In this case, the deleting at least one target column name included in the first target operation condition of the first target node corresponding to the target filter node may include: for each first target node corresponding to the original node, if the first target operation condition of the first target node only contains a target column name, selecting one target column name as a reserved column name, and deleting a non-reserved column name in the first target operation condition; and if the first target operation condition of the first target node comprises non-target column names, deleting all target column names in the first target operation condition. That is, if the first target arithmetic condition includes N target column names, when the first arithmetic expression includes a non-target column name, all of the N target column names are deleted from the first target arithmetic expression; when the first operation formula does not contain the non-target column names, deleting N-1 target column names from the first operation formula. The reserved column name may be any target column name in the first operation formula, and may be selected as needed, for example, the reserved column name may be selected randomly, the first one or the last one may be selected.
In this embodiment, considering that when there is a full external connection node between an original node and a first target node thereof, or when the original node is a left external connection node or a right child of a half connection node, performing the full connection operation/left external connection operation/half connection operation may introduce a NULL value into a constant column filtered by the original node, and at this time, if a target column name included in a first operation formula of the first target node is still deleted, a situation that a query result of an SQL statement is inaccurate may occur. In this case, it is preferable that, before the deleting at least one target column name included in the first target operation condition of the first target node corresponding to the original node, the method further includes: determining that there is no fully-outerjoin node between the origin node and the first target node, and that the origin node is not the right child of a left-outerjoin node or a semi-join node.
S103, executing the SQL statement according to the new relation tree.
Specifically, an execution plan may be generated according to the new relational tree, and the SQL statement may be executed according to the execution plan.
For example, assume the definition of table T1 is: CREATE TABLE T1 (C1 INT, C2 INT); the SQL statement is: select c1, c2 from t1 where c2=1group by c1, c2; the original relational tree of the SQL statement is shown in fig. 2, and the new relational tree of the SQL statement is shown in fig. 3, and the SQL statement may be converted into: select c1,1from t1 where c2=1group by c1; the execution plan of the SQL statement determined based on the original relationship tree of the SQL statement may be:
1 #NSET2:[1,1,52]
2 #PRJT2:[1,1,52]；exp_num(2),is_atom(FALSE)
3 #HAGR2:[1,1,52]；grp_num(2),sfun_num(0)；
4 #SLCT2:[0,1,52]；exp_cast(T1.C2)＝1
5 #CSCN2:[0,1,52]；INDEX33555453(T1)
the execution plan of the SQL statement determined based on the new relational tree of the SQL statement may be:
1 #NSET2:[1,1,8]
2 #PRJT2:[1,1,8]；exp_num(2),is_atom(FALSE)
3 #HAGR2:[1,1,8]；grp_num(1),sfun_num(0)；slave_empty(0)
keys(T1.C1)
4 #SLCT2:[0,1,8]；T1.C2＝1
5 #CSCN2:[0,10,8]；INDEX33555449(T1)
based on the two execution plans, it can be seen that, in the prior art, when a GROUP BY grouping item is executed, all values of C1 need to be acquired and filtering results generated BY filtering nodes are grouped according to the value of C1, and then all values of C2 are acquired and results obtained BY grouping the values of C1 according to the value of C2 are grouped again; however, the execution method of the SQL statement provided in this embodiment only needs to obtain all the values of C1 and group the filtering results generated by the filtering nodes according to the values of C1, which can improve the query efficiency of the database, and especially when the SQL statement is relatively complex, this effect is very obvious.
The execution method of the SQL statement provided by the embodiment of the invention obtains the original relation tree of the SQL statement; sequentially judging whether each original node in the original relation tree is a target filtering node which contains a constant quantum expression in a filtering condition and does not contain an OR operation and a NOT operation, when a certain original node is judged to be the target filtering node, determining a grouping node which contains at least two column names and at least one column name in an ancestor node of the original node as a first target node, deleting at least one target column name in the first target node to generate a new relation tree, and then executing the SQL statement according to the new relation tree. By adopting the technical scheme, the constant packet items in the packet nodes are deleted, the times of the SQL statements for packet calculation can be reduced, and the query efficiency of the database is improved.
On the basis of the above embodiment, when it is determined that the original node is the target filter node, the execution method of the SQL statement may further optimize: replacing a target column name contained in a second target operation condition of a second target node corresponding to the original node with a constant in the constant sub-expression, wherein the second target node corresponding to the original node comprises the original node and at least one of a result set projection node, a filter node, an inner connection node, a left outer connection node and a semi-connection node in an ancestor node of the original node; the second target operation condition includes the target column name. In this embodiment, when a first target node capable of deleting the target column name exists in the original relational tree, for the target column name capable of being deleted, the column names and the aliases of the constant columns in the constant sub-expressions corresponding to the target column name in the target filter node and the parent node thereof may be replaced with constants in the constant sub-expressions, so as to further reduce the amount of operations required in the execution process of the SQL statement. The second target node may be a result set projection node, a filter node, an inner connection node, a left outer connection node, and/or a semi-connection node.
Example two
Fig. 4 is a flowchart illustrating an execution method of an SQL statement according to a second embodiment of the present invention. In this embodiment, on the basis of the above embodiment, the step of sequentially judging whether each original node in the original relationship tree is a target filter node, and when the original node is judged to be the target filter node, deleting at least one target column name included in the first target operation condition of the first target node corresponding to the original node is optimized as follows: initializing an ancestor linked list, and selecting a root node of the original relation tree as a current original node; adding the current original node to the end of an ancestor linked list; judging whether the current original node is a target filtering node or not; and if the current original node is a target filtering node, deleting at least one target column name contained in a first target operation condition of a first target node corresponding to the current original node, selecting a next original node according to the order of precedence traversal, determining the next original node as the current original node, and returning to execute the operation of adding the current original node to the tail of the ancestor linked list until the next original node does not exist.
Further, the execution method of the SQL statement further includes: and if the current original node is not the target filtering node, selecting a next original node according to the order of the precedence traversal, determining the next original node as the current original node, and returning to execute the operation of adding the current original node to the tail of the ancestor linked list until the next original node does not exist.
Further, before the determining the next original node as the current original node, the method further includes: if the current original node is not the father node of the next original node, determining the nearest common ancestor node of the current original node and the next original node, and deleting the current original node and other original nodes between the current original node and the nearest common ancestor node stored in the ancestor linked list.
Further, before the deleting at least one target column name included in the operation condition of the first target node corresponding to the current original node, the method further includes: and determining the grouping node, the sorting node and the deduplication node of which the operation conditions stored in the ancestor linked list comprise the target column name as a first target node corresponding to the current original node.
Correspondingly, as shown in fig. 4, the method for executing the SQL statement provided by the embodiment includes:
s201, obtaining an original relation tree of the SQL statement.
S202, initializing an ancestor linked list, and selecting a root node of the original relation tree as a current original node.
In this step, the ancestor linked list may be used to store the node path of the original node in the original relationship tree; assuming that the original relationship tree is the relationship tree shown in fig. 2, the root node REL _ PROJECT in the original relationship tree is determined as the current original node.
S203, adding the current original node to the end of the ancestor linked list.
Specifically, the node path of the current original node may be added to the end of the ancestor linked list, so that the ancestor node (including the parent node) of the current original node may be directly searched based on the ancestor linked list in the following process, and the searching process does not need to be performed in a complex manner of traversing the relationship tree, thereby improving the determination efficiency of the subsequent first target node, and further improving the query efficiency of the database.
S204, judging whether the current original node is a target filtering node, if so, executing S205; if not, go to step S207.
For example, it may be determined whether the current original node is a filtering node, and if not, it is determined that the current original node is not a target filtering node; if the current original node is the filtering node, continuously judging whether a constant filtering item (namely whether a constant sub expression is contained) exists in the filtering condition of the current original node, and if the constant filtering item does not exist, determining that the current original node is not the target filtering node; if the filtering condition of the current original node contains the constant filtering item, further judging whether the filtering condition of the current original node contains OR operation or NOT operation, and if the filtering condition of the current original node contains the OR operation or NOT operation, determining that the current original node is not the target filtering node; and if the operation of the OR and the non-operation are not included, determining the current original node as the target filtering node.
S205, determining the grouping node, the sorting node and the deduplication node of which the operation conditions stored in the ancestor linked list comprise the target column name as a first target node corresponding to the current original node.
S206, deleting at least one target column name contained in the first target operation condition of the first target node corresponding to the current original node.
Illustratively, when an ancestor linked list is initialized, a constant linked list for storing constant filter items is also initialized, correspondingly, when it is determined that a current original node is a target filter node, the constant filter item of the current original node may be added to the constant linked list, and when the above steps S205 and S206 are executed, a constant filter item is taken out from the constant linked list as the current constant filter item, according to a sequence of the nodes recorded in the selected linked list from the back to the front, it is sequentially determined whether each node is a first target node whose operation condition includes at least two column names and whose expression includes a current target column name identical to that of the current constant filter item, and if so, the current target column name is deleted from the first target operation condition of the first target node; if not, continuing to judge whether the next node is the first target operation node or not until the judged node is a result set projection node which does not contain the column name and the alias of the constant column corresponding to the current filter item, the judged node is a set operation node, the judged node is a hierarchical query node or each node in the original relation tree is judged to be finished; deleting the current constant filter item stored in the constant linked list, determining the next constant filter item as the current constant filter item, and returning to execute the operation of sequentially judging whether each node is the first target node or not according to the sequence of each node recorded in the selected linked list from the back to the front until the constant filter item does not exist in the constant linked list.
S207, determining whether a next original node exists according to the sequence of the preorder traversal, and if so, executing S208; if not, S211 is executed.
For example, if the current original node has a left child, the left child is determined as the next original node; if the current original node has no left child but has a right child, determining the right child as the next original node; if the current original node has no left child and right child, the current original node is the left child of the parent node, and the parent node has the right child, the right child of the parent node is determined as the next original node, and so on, the specified sequence of root node-left child-right child is traversed in the first order, and then whether the current original node has the next original node or not can be determined, and when the current original node has the next original node, the next original node is determined. Taking the original relationship tree shown in fig. 2 as an example, if the current original node is REL _ SELECT in fig. 2, then there is a next original node, and S208 is executed; if the current primary node is the REL _ TABLE node in fig. 2, there is no next primary node, and S211 is executed.
S208, judging whether the current original node is a father node of the next original node, and if not, executing S209; if yes, go to S210.
S209, determining the nearest common ancestor node of the current original node and the next original node, and deleting the current original node and other original nodes between the current original node and the nearest common ancestor node stored in the ancestor linked list.
In this embodiment, when the current original node is not the parent node of the next original node, it may be determined that the current original node stored in the ancestry chain table and a node between the current original node and a nearest common ancestor node of the current original node and the current original node (that is, the current original node and the next original node) are not the ancestor node of the next original node, and therefore, the current original node stored in the ancestry chain table and a node between the current original node and the nearest common ancestor node of the current original node and the next original node may be deleted at this time, and it is ensured that when the next original node is determined as the current original node, the nodes recorded in the ancestry chain table except the current original node are the ancestor nodes of the current original node, so that the ancestor node of the current original node can be directly searched based on the ancestor chain table.
S210, determining the next original node as the current original node, and returning to execute S203.
S211, executing the SQL statement according to the new relation tree.
In this embodiment, an execution process of the execution method of the SQL statement provided in the embodiment of the present invention may be exemplarily described as follows:
a. the method comprises the steps of obtaining an original relation tree of SQL sentences, initializing a constant chain table and an ancestor chain table, and obtaining a root node of the original relation tree as a first current original node.
b. Adding the node path of the first current original node to the tail of the ancestor linked list, judging whether the first current original node is a filtering node or not, and if so, executing the step c; if not, executing the step e.
c. Judging the where filter condition on the first current original node, if the where filter condition only has one filter item A (assumed as C1= 1) or is an AND operation (such as A and B and C), finding out a constant filter item (for example, C1= 1) of which the equal sign side is a column name and the equal sign side is a constant, adding the constant filter item into a constant linked list, and executing the step d; and if no constant filter item exists in the filter condition, or the where filter condition of the first current original node contains OR operation (such as D or E) or NOT operation, executing the step E.
d. Each time, taking one constant filter item from the constant linked list as a current constant filter item, carrying out constant replacement (including constant column deletion) on each corresponding upper-layer node on the original relation tree according to the following steps, deleting the taken constant filter item from the constant linked list after the constant replacement is finished, and taking the next constant filter item as the current constant filter item until the constant linked list is empty.
d1, judging whether the length (namely the number of the stored node paths) of the ancestor linked list is 1, namely judging whether the ancestor linked list only stores the path of the first current original node, if so, emptying the constant linked list, and executing the step e; if not, executing d2.
d2, determining the node corresponding to the node path at the tail of the ancestor linked list (namely the first current original node) as a second current original node, and executing a certain item in the steps d3-d 9.
And d3, when the second current original node is a full external connection node, determining that the constant replacement aiming at the current constant filter item is finished.
d4, when the second current original node is a left external connection node or a semi-connection node, if the first current original node is the right child of the second current original node, determining that the constant replacement aiming at the current constant filter item is finished; if the first current original node is not the right child of the second current original node, judging whether the expression of the second current original node contains the column name of the current constant filter item (namely, the column name of the current constant column), if so, replacing the column name of the current constant column in the expression with the constant in the expression of the current constant filter item (namely, the constant sub-expression), and executing the step d10; if not, go to step d10.
d5, when the second current original node is a grouping node, a sorting node or a deduplication node, if the number of column names contained in the operation condition of the second current original node is greater than 1 and a target expression identical to the expression of the current constant filter item exists, deleting the target expression in the operation condition, and executing the step d10; if the operation condition of the second current original node only contains one expression or the operation condition of the second current original node does not contain a target expression, step d10 is executed.
d6, when the second current original node is the result set projection node, if the expression of the result set projection node does not contain the alias of the current constant column name and the current constant column name (at this time, it is stated that the upper node of the second current original node does not need to use the constant column corresponding to the current constant column name either), determining that the constant replacement aiming at the current constant filter item is finished; if the projection node of the result set contains the current constant column name, replacing the constant column name with a constant in the expression of the current constant column name, and executing the step 10; and if the alias of the current constant column name is contained in the result set projection node, replacing the alias of the constant column name with a constant in the expression of the current constant column name, determining the alias of the constant column name as the current constant column name, and executing the step d10.
In this step, if the alias in the filter term of the first current original node (such as internal generation or definition in an SQL statement) is a constant filter term and the expression of the result set projection node includes the alias of the constant filter term, then constant replacement needs to be continuously performed upward, at this time, the constant column name may be replaced with an alias, such as filter term select c1 a1, which indicates that c1 has alias a1, and the expression of the result set projection node includes alias a1, and then constant replacement may be continuously performed on the result set projection node and the node on the upper layer of the result set projection node after replacing expression c1=1 of the constant column with a1= 1.
And d7, when the second current original node is a set operation node or a hierarchy query node, determining that the constant replacement aiming at the current constant filter item is finished. Because the set operation node or the hierarchical query node renames the expression again, the upper nodes use new names, and the column names of the current constant columns can not appear any more, the constant replacement operation aiming at the current constant filter items can be finished.
d8, when the second current original node is a filtering node, an internal connection node, a left external connection node or a semi-connection node, if the expression of the second current original node contains the current constant column name, replacing the current constant column name contained in the expression with a constant in the expression of the current constant column name, and executing the step d10; if the expression of the second current original node does not contain the current constant column name, step d10 is performed.
d9, if the second current original node is a node in which no column name exists in other expressions, such as REL _ TABLE, REL _ TOPN, REL _ ROWNUM or REL _ AFUN, then step d10 is executed.
d10, judging whether the second current original node has a next node according to the sequence of the nodes recorded in the ancestor linked list from back to front, if so, determining the next node as the second current original node, and returning to execute one of d3-d 9; if not, determining that the constant replacement aiming at the current constant filter item is finished.
e. Judging whether a left child exists in the first current original node, if so, determining the left child as the first current original node, and returning to execute the step b; if not, executing step f.
f. Judging whether the first current original node has a right child, if so, determining the right child as the first current original node, and returning to execute the step b; if not, executing step g.
g. Removing the node path of the first current original node from the ancestor linked list, judging whether the first current original node has a father node or not, if so, executing the step h; if not, executing the step i.
h. Judging whether the first current original node is a left child of the father node of the first current original node, if so, determining the father node of the first current original node as the first current original node, and returning to execute the step f; if not, determining the father node as the first current original node, and returning to execute the step g.
i. And executing the SQL statement according to the generated new relation tree.
In a specific application scenario of the embodiment, it is assumed that definitions exist in tables t1 and t 2: create table t1 (c 1 int, c2 int); create table t2 (d 1 int, d2 int); the SQL statement is: select d1, c2 from t1 left outer join t2 on c2= d2 where c2=1group by d1, c2 union all select from t2 where d2=1group by d1, c2 unoon all select from t2 where d2=1group by d1, d2; the original relation tree generated after performing syntax and semantic analysis on the SQL statement is shown in fig. 5, and the execution process of the SQL statement may be described as follows:
first, a constant quantity table and an ancestor linked table are initialized.
Secondly, adding the node path of the root node REL _ PROJECT into an ancestor linked list, wherein the node is not a filter node and is not optimized.
And continuously analyzing the left child REL _ UNIONALL of the REL _ PROJECT, adding the node path of the REL _ UNIONALL into an ancestor linked list, wherein the node is not a filtering node and is not optimized.
And continuously analyzing the left child REL _ PROJECT of the REL _ UNIONALL, adding the node path of the REL _ PROJECT into an ancestor linked list, wherein the node is not a filter node and is not subjected to optimization treatment.
And continuously analyzing the left child REL _ GROUP of the REL _ PROJECT, adding the node path of the REL _ GROUP into an ancestor linked list, wherein the node is not a filter node and is not subjected to optimization treatment.
And continuously analyzing the left child REL _ PROJECT of the REL _ GROUP, adding the node path of the REL _ PROJECT into an ancestor linked list, wherein the node is not a filter node and is not subjected to optimization treatment.
And continuously analyzing the LEFT child REL _ LEFT of the REL _ PROJECT, adding the node path of the REL _ LEFT into an ancestor linked list, wherein the node is not a filtering node and is not subjected to optimization treatment.
Continuously analyzing the LEFT child REL _ SELECT of the REL _ LEFT, adding a node path of the REL _ SELECT into an ancestor linked list, wherein the node is a filtering node, a constant filtering item c2=1 exists on the REL _ SELECT, the node does not contain OR operation and NOT operation, the length of the ancestor linked list is 7 > 1, and performing optimization: adding a constant filter item c2=1 into the constant linked list, continuing the next step because no other constant filter item capable of being added into the constant linked list exists, taking out one constant filter item c2= 1from the constant linked list, and replacing c2 in the REL _ SELECT with a constant 1 according to the sequence of each node path in the ancestor linked list from back to front; the parent node of REL _ SELECT is REL _ LEFT, and REL _ SELECT is the LEFT child of REL _ LEFT, so it may be replaced by replacing the connection condition c2= d2 of REL _ LEFT with 1= d2; the parent node of the REL _ LEFT is REL _ PROJECT, and a query item c2 in the REL _ PROJECT is replaced by a constant 1; the REL _ PROJECT parent node is REL _ GROUP, and REL _ GROUP contains two packet entries, so that the packet entry c2 can be replaced and removed; the REL _ GROUP parent node is REL _ PROJECT, and a query item c2 in the REL _ PROJECT is replaced by a constant 1; and the parent node of the REL _ PROJECT is REL _ UNIONALL, upward replacement is not required, the replacement of the constant filter item c2=1 is finished, the constant filter item c2=1 is deleted from the constant linked list, the constant linked list is empty, and the replacement of the constant filter items of the REL _ SELECT node is finished.
And continuously analyzing the REL _ TABLE of the left child of the REL _ SELECT, adding the node path of the REL _ TABLE into an ancestor linked list, wherein the node is not a filtering node, does not perform optimization processing, and because the REL _ TABLE does not have the left child and the right child, the REL _ TABLE analysis is finished, and the node path of the REL _ TABLE is removed from the ancestor linked list.
And continuously analyzing the REL _ SELECT of the parent node of the REL _ TABLE, wherein the REL _ TABLE is the left child of the REL _ SELECT, and the REL _ SELECT does not have the right child, which shows that the REL _ SELECT analysis is finished, and the node path of the REL _ SELECT is removed from the ancestor linked list.
Because the parent node REL _ LEFT of REL _ SELECT, REL _ SELECT is the LEFT child of REL _ LEFT and REL _ LEFT exists in the right child REL _ TABLE, the analysis continues to analyze the REL _ TABLE of the right child of REL _ LEFT, the node path of REL _ TABLE is added into the ancestor linked list, the node is not a filter node, optimization processing is not performed, and the REL _ TABLE analysis is finished because the REL _ TABLE does not have the LEFT child and the right child, and the node path of REL _ TABLE is removed from the ancestor linked list.
And continuing to analyze the parent node REL _ LEFT of REL _ TABLE, wherein REL _ TABLE is the right child of REL _ LEFT, and the node path of REL _ LEFT is removed from the ancestor linked list after the REL _ LEFT analysis is finished.
And so on, continuing to analyze the following nodes, and when the ancestor linked list is empty, indicating that all the nodes are analyzed and processed completely, and obtaining a new relationship tree, as shown in fig. 6.
And finally, generating an execution plan according to the new relation tree, and executing the SQL statement according to the execution plan.
The method for executing the SQL statement provided by the second embodiment of the present invention determines whether each node is a target filtering node by adopting a preface traversal method, records a parent node and an ancestor node of each node through an ancestor linked list, and sequentially performs constant replacement on each ancestor node of the node according to a sequence from back to front of each node recorded by the ancestor linked list when determining that a certain node is a target filtering node, so that the filtering node of each node can be quickly determined when performing constant replacement, the speed of replacing the constant is increased, the execution speed of the SQL statement is further increased, and the waiting time of a user is reduced.
EXAMPLE III
The third embodiment of the invention provides an execution device of an SQL statement. The device can be realized by software and/or hardware, can be generally integrated in computer equipment, is suitable for a scene of constant replacement of the SQL statement, and can replace the constant in the SQL statement by executing the execution method of the SQL statement provided by the embodiment of the invention. Fig. 7 is a block diagram of an execution apparatus of an SQL statement according to a third embodiment of the present invention, as shown in fig. 7, the apparatus includes an obtaining module 301, a deleting module 302, and an executing module 303, wherein,
an obtaining module 301, configured to obtain an original relational tree of an SQL statement;
a deleting module 302, configured to sequentially determine whether each original node in the original relationship tree is a target filtering node, and delete at least one target column name included in a first target operation condition of a first target node corresponding to the original node when determining that the original node is the target filtering node, so as to obtain a new relationship tree;
an executing module 303, configured to execute the SQL statement according to the new relation tree;
the filtering conditions of the target filtering nodes comprise constant quantum expressions and do not comprise OR operations and NOT operations; the first target node corresponding to the original node comprises a grouping node in an ancestor node of the original node, the first target operation condition comprises at least column names, target column names exist in the at least two column names, and the target column names are constant column names in the constant sub-expression or alias names of the constant column names.
In the execution device of the SQL statement provided in the third embodiment of the present invention, the original relational tree of the SQL statement is obtained by the obtaining module; sequentially judging whether each original node in the original relation tree is a target filtering node which contains a constant quantum expression in a filtering condition and does not contain an OR operation and a NOT operation through a deleting module, determining a grouping node which contains at least two column names and at least one column name as a target column name in an ancestor node of the original node as a first target node when judging that a certain original node is the target filtering node, deleting at least one target column name in the first target node to generate a new relation tree, and then executing the SQL statement according to the new relation tree through an executing module. By adopting the technical scheme, the constant packet items in the packet nodes are deleted, the times of the SQL statements for packet calculation can be reduced, and the query efficiency of the database is improved.
In the foregoing solution, the deleting module 301 may specifically be configured to: the system comprises a first target node, a second target node and a third target node, wherein the first target node is used for sequentially judging whether each original node in the original relation tree is a target filtering node or not, and aiming at each first target node corresponding to the original node when the original node is judged to be the target filtering node, if a first target operation condition of the first target node only comprises a target column name, one target column name is selected as a reserved column name, and a non-reserved column name in the first target operation condition is deleted; and if the first target operation condition of the first target node comprises non-target column names, deleting all target column names in the first target operation condition to obtain a new relation tree.
In the above solution, the deleting module 301 may be further configured to: before deleting at least one target column name included in a first target operation condition of a first target node corresponding to the original node, determining that a full external connection node does not exist between the original node and the first target node, and the original node is not a left external connection node or a right child of a half connection node.
In the above scheme, the deleting module 301 may further be configured to: when the original node is judged to be a target filtering node, replacing a target column name contained in a second target operation condition of a second target node corresponding to the original node by a constant in the constant sub-expression, wherein the second target node corresponding to the original node comprises the original node and at least one of a result set projection node, a filtering node, an inner connection node, a left outer connection node and a half connection node in an ancestor node of the original node; the second target operation condition includes the target column name.
In the foregoing solution, the deleting module 301 may include: an initialization unit to: initializing an ancestor linked list, and selecting a root node of the original relation tree as a current original node; the adding unit is used for adding the current original node to the tail of the ancestor linked list; the judging unit is used for judging whether the current original node is a target filtering node or not; a column name deleting unit, configured to delete at least one target column name included in a first target operation condition of a first target node corresponding to the current original node when the current original node is a target filter node, select a next original node according to a sequence traversal order, determine the next original node as the current original node, and return to perform an operation of adding the current original node to the end of the ancestor linked list until no next original node exists; and when the current original node is not the target filtering node, selecting a next original node according to the order of the precedence traversal, determining the next original node as the current original node, and returning to execute the operation of adding the current original node to the tail of the ancestor linked list until the next original node does not exist.
In the foregoing solution, the deleting module 301 may further include: a node deleting unit, configured to, before the determining of the next original node as the current original node, determine, if the current original node is not the parent node of the next original node, a nearest common ancestor node between the current original node and the next original node, and delete the current original node and other original nodes located between the current original node and the nearest common ancestor node stored in the ancestor linked list.
In the foregoing scheme, the deleting module 301 may further include: and a node determining unit, configured to determine, before deleting at least one target column name included in the operation condition of the first target node corresponding to the current original node, a grouping node, a sorting node, and a deduplication node, where the operation condition stored in the ancestor linked list includes the target column name, as the first target node corresponding to the current original node.
The SQL statement execution device provided by the third embodiment of the present invention is capable of executing the SQL statement execution method provided by any embodiment of the present invention, and has functional modules and beneficial effects corresponding to the SQL statement execution method. For details of the technique not described in detail in this embodiment, reference may be made to the execution method of the SQL statement provided in any embodiment of the present invention.
Example four
Fig. 8 is a schematic structural diagram of a computer device according to a fourth embodiment of the present invention, as shown in fig. 8, the computer device includes a processor 40 and a memory 41, and may further include an input device 42 and an output device 43; the number of processors 40 in the computer device may be one or more, and one processor 40 is taken as an example in fig. 8; the processor 40, the memory 41, the input device 42 and the output device 43 in the computer apparatus may be connected by a bus or other means, and the connection by the bus is exemplified in fig. 8.
The memory 41 serves as a computer-readable storage medium, and can be used for storing software programs, computer-executable programs, and modules, such as program instructions/modules corresponding to the execution method of the SQL statement in the embodiment of the present invention (for example, the acquisition module 301, the deletion module 302, and the execution module 303 in the execution device of the SQL statement). The processor 40 executes various functional applications and data processing of the computer device by executing software programs, instructions and modules stored in the memory 41, that is, implements the execution method of the SQL statement described above.
The memory 41 may mainly include a storage program area and a storage data area, wherein the storage program area may store an operating system, an application program required for at least one function; the storage data area may store data created according to the use of the terminal, and the like. Further, the memory 41 may include high speed random access memory, and may also include non-volatile memory, such as at least one magnetic disk storage device, flash memory device, or other non-volatile solid state storage device. In some examples, memory 41 may further include memory located remotely from processor 40, which may be connected to a computer device over a network. Examples of such networks include, but are not limited to, the internet, intranets, local area networks, mobile communication networks, and combinations thereof.
The input device 42 is operable to receive input numeric or character information and to generate key signal inputs relating to user settings and function controls of the computer apparatus. The output device 43 may include a display device such as a display screen.
A fourth embodiment of the present invention further provides a storage medium containing computer-executable instructions, where the computer-executable instructions, when executed by a computer processor, are configured to perform a method for executing an SQL statement, where the method includes:
acquiring an original relation tree of SQL sentences;
sequentially judging whether each original node in the original relationship tree is a target filtering node or not, and deleting at least one target column name contained in a first target operation condition of a first target node corresponding to the original node when the original node is judged to be the target filtering node so as to obtain a new relationship tree;
executing the SQL statement according to the new relation tree;
the filtering condition of the target filtering node comprises a constant quantum expression and does not comprise an OR operation and a NOT operation; the first target node corresponding to the original node comprises a grouping node in an ancestor node of the original node, the first target operation condition comprises at least column names, target column names exist in the at least two column names, and the target column names are constant column names in the constant sub-expression or alias names of the constant column names.
Of course, the storage medium provided by the embodiment of the present invention contains computer-executable instructions, and the computer-executable instructions are not limited to the method operations described above, and may also execute the relevant operations in the execution method of the SQL statement provided by any embodiment of the present invention.
From the above description of the embodiments, it is obvious for those skilled in the art that the present invention can be implemented by software and necessary general hardware, and certainly, can also be implemented by hardware, but the former is a better embodiment in many cases. Based on such understanding, the technical solutions of the present invention may be embodied in the form of a software product, which can be stored in a computer-readable storage medium, such as a floppy disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a FLASH Memory (FLASH), a hard disk or an optical disk of a computer, and includes several instructions for enabling a computer device (which may be a personal computer, a server, or a network device) to execute the methods according to the embodiments of the present invention.
It should be noted that, in the embodiment of the execution apparatus of the SQL statement, each unit and each module included in the execution apparatus are only divided according to functional logic, but are not limited to the above division, as long as the corresponding function can be implemented; in addition, specific names of the functional units are only for convenience of distinguishing from each other, and are not used for limiting the protection scope of the present invention.
It is to be noted that the foregoing is only illustrative of the preferred embodiments of the present invention and the technical principles employed. It will be understood by those skilled in the art that the present invention is not limited to the particular embodiments described herein, but is capable of various obvious changes, rearrangements and substitutions as will now become apparent to those skilled in the art without departing from the scope of the invention. Therefore, although the present invention has been described in greater detail by the above embodiments, the present invention is not limited to the above embodiments, and may include other equivalent embodiments without departing from the spirit of the present invention, and the scope of the present invention is determined by the scope of the appended claims.

Claims (7)

1. An execution method of an SQL statement is characterized by comprising the following steps:
acquiring an original relation tree of SQL sentences;
sequentially judging whether each original node in the original relationship tree is a target filtering node or not, and deleting at least one target column name contained in a first target operation condition of a first target node corresponding to the original node when the original node is judged to be the target filtering node so as to obtain a new relationship tree;
executing the SQL statement according to the new relation tree;
the filtering condition of the target filtering node comprises a constant quantum expression and does not comprise an OR operation and a NOT operation; the first target node corresponding to the original node comprises a grouping node in an ancestor node of the original node, the first target operation condition comprises at least column names, target column names exist in the at least two column names, and the target column names are constant column names in the constant sub-expression or alias names of the constant column names;
the deleting at least one target column name included in the first target operation condition of the first target node corresponding to the target filtering node includes:
for each first target node corresponding to the original node, if the first target operation condition of the first target node only contains a target column name, selecting one target column name as a reserved column name, and deleting a non-reserved column name in the first target operation condition; if the first target operation condition of the first target node comprises non-target column names, deleting all target column names in the first target operation condition;
before the deleting at least one target column name included in the first target operation condition of the first target node corresponding to the original node, the method further includes:
determining that there is no fully-external connection node between the original node and the first target node, and that the original node is not a left-external connection node or a right child of a semi-connection node;
when the original node is determined to be the target filtering node, the method further comprises:
replacing a target column name contained in a second target operation condition of a second target node corresponding to the original node with a constant in the constant sub-expression, wherein the second target node corresponding to the original node comprises the original node and at least one of a result set projection node, a filter node, an inner connection node, a left outer connection node and a semi-connection node in an ancestor node of the original node; the second target operation condition includes the target column name.
2. The method according to claim 1, wherein sequentially determining whether each original node in the original relationship tree is a target filter node, and deleting at least one target column name included in a first target operation condition of a first target node corresponding to the original node when determining that the original node is the target filter node, comprises:
initializing an ancestor linked list, and selecting a root node of the original relation tree as a current original node;
adding the current original node to the end of an ancestor linked list;
judging whether the current original node is a target filtering node or not;
if the current original node is a target filtering node, deleting at least one target column name contained in a first target operation condition of a first target node corresponding to the current original node, selecting a next original node according to a sequence traversal order, determining the next original node as the current original node, and returning to execute the operation of adding the current original node to the tail of an ancestor linked list until no next original node exists;
the method further comprises the following steps:
and if the current original node is not the target filtering node, selecting a next original node according to the order of the precedence traversal, determining the next original node as the current original node, and returning to execute the operation of adding the current original node to the tail of the ancestor linked list until the next original node does not exist.
3. The method of claim 2, further comprising, prior to said determining said next originating node as a current originating node:
and if the current original node is not the father node of the next original node, determining a nearest common ancestor node of the current original node and the next original node, and deleting the current original node and other original nodes between the current original node and the nearest common ancestor node stored in the ancestor linked list.
4. The method according to claim 3, further comprising, before the deleting at least one target column name included in the operation condition of the first target node corresponding to the current original node:
and determining the grouping node, the sorting node and the deduplication node of which the operation conditions stored in the ancestor linked list comprise the target column name as a first target node corresponding to the current original node.
5. An apparatus for executing an SQL statement, comprising:
the acquisition module is used for acquiring an original relation tree of the SQL statement;
a deleting module, configured to sequentially determine whether each original node in the original relationship tree is a target filtering node, and delete at least one target column name included in a first target operation condition of a first target node corresponding to the original node when the original node is determined to be the target filtering node, so as to obtain a new relationship tree;
the execution module is used for executing the SQL statement according to the new relation tree;
the filtering conditions of the target filtering nodes comprise constant quantum expressions and do not comprise OR operations and NOT operations; the first target node corresponding to the original node comprises a grouping node in an ancestor node of the original node, the first target operation condition comprises at least column names, target column names exist in the at least two column names, and the target column names are constant column names in the constant sub-expression or alias names of the constant column names;
the deletion module is specifically configured to: for each first target node corresponding to the original node, if the first target operation condition of the first target node only contains a target column name, selecting one target column name as a reserved column name, and deleting a non-reserved column name in the first target operation condition; if the first target operation condition of the first target node comprises non-target column names, deleting all target column names in the first target operation condition;
the deletion module is further configured to: before deleting at least one target column name included in a first target operation condition of a first target node corresponding to the original node, determining that a full external connection node does not exist between the original node and the first target node, and the original node is not a left external connection node or a right child of a half connection node; when the original node is judged to be a target filtering node, replacing a target column name contained in a second target operation condition of a second target node corresponding to the original node with a constant in the constant sub-expression, wherein the second target node corresponding to the original node comprises the original node and at least one of a result set projection node, a filtering node, an inner connection node, a left outer connection node and a semi-connection node in an ancestor node of the original node; the second target operation condition includes the target column name.
6. A computer device, comprising:
one or more processors;
a memory for storing one or more programs,
when executed by the one or more processors, cause the one or more processors to implement the method of execution of the SQL statement according to any of claims 1-4.
7. A computer-readable storage medium on which a computer program is stored, the program being executed by a processor to implement the execution method of the SQL statement according to any one of claims 1 to 4.
CN201911278107.1A 2019-12-12 2019-12-12 SQL statement execution method, device, equipment and storage medium Active CN110990423B (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN201911278107.1A CN110990423B (en) 2019-12-12 2019-12-12 SQL statement execution method, device, equipment and storage medium

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN201911278107.1A CN110990423B (en) 2019-12-12 2019-12-12 SQL statement execution method, device, equipment and storage medium

Publications (2)

Publication Number Publication Date
CN110990423A CN110990423A (en) 2020-04-10
CN110990423B true CN110990423B (en) 2023-04-18

Family

ID=70093022

Family Applications (1)

Application Number Title Priority Date Filing Date
CN201911278107.1A Active CN110990423B (en) 2019-12-12 2019-12-12 SQL statement execution method, device, equipment and storage medium

Country Status (1)

Country Link
CN (1) CN110990423B (en)

Families Citing this family (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN113032465B (en) * 2021-05-31 2021-09-10 北京谷数科技股份有限公司 Data query method and device, electronic equipment and storage medium
CN113641701B (en) 2021-10-13 2022-02-18 苏州浪潮智能科技有限公司 Data query method, system, heterogeneous acceleration platform and storage medium

Citations (6)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US6339770B1 (en) * 1999-08-12 2002-01-15 International Business Machines Corporation Query simplification and optimization involving eliminating grouping column from group by operation corresponds to group by item that is constant
CN101609473A (en) * 2009-07-30 2009-12-23 金蝶软件（中国）有限公司 A kind of method of Structured Query Language (SQL) of reconstruct report query and device
CN108304505A (en) * 2018-01-18 2018-07-20 上海达梦数据库有限公司 A kind of processing method of SQL statement, device, server and storage medium
CN108804554A (en) * 2018-05-22 2018-11-13 上海达梦数据库有限公司 A kind of data base query method, device, server and storage medium
CN109947791A (en) * 2019-03-27 2019-06-28 上海达梦数据库有限公司 A kind of database statement optimization method, device, equipment and storage medium
CN110515973A (en) * 2019-08-30 2019-11-29 上海达梦数据库有限公司 A kind of optimization method of data query, device, equipment and storage medium

Family Cites Families (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US8140558B2 (en) * 2009-05-22 2012-03-20 International Business Machines Corporation Generating structured query language/extensible markup language (SQL/XML) statements
US9317554B2 (en) * 2012-09-26 2016-04-19 Microsoft Technology Licensing, Llc SQL generation for assert, update and delete relational trees

Patent Citations (6)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US6339770B1 (en) * 1999-08-12 2002-01-15 International Business Machines Corporation Query simplification and optimization involving eliminating grouping column from group by operation corresponds to group by item that is constant
CN101609473A (en) * 2009-07-30 2009-12-23 金蝶软件（中国）有限公司 A kind of method of Structured Query Language (SQL) of reconstruct report query and device
CN108304505A (en) * 2018-01-18 2018-07-20 上海达梦数据库有限公司 A kind of processing method of SQL statement, device, server and storage medium
CN108804554A (en) * 2018-05-22 2018-11-13 上海达梦数据库有限公司 A kind of data base query method, device, server and storage medium
CN109947791A (en) * 2019-03-27 2019-06-28 上海达梦数据库有限公司 A kind of database statement optimization method, device, equipment and storage medium
CN110515973A (en) * 2019-08-30 2019-11-29 上海达梦数据库有限公司 A kind of optimization method of data query, device, equipment and storage medium

Non-Patent Citations (2)

* Cited by examiner, † Cited by third party
Title
蔡葵.浅议关系型数据库的查询优化问题.华南金融电脑.2004,(05),全文. *
陈佳 ; .基于SQL server数据库优化查询的分析.企业导报.2010,(08),全文. *

Also Published As

Publication number Publication date
CN110990423A (en) 2020-04-10

Similar Documents

Publication Publication Date Title
US10659467B1 (en) Distributed storage and distributed processing query statement reconstruction in accordance with a policy
US8332389B2 (en) Join order for a database query
CN110515973B (en) Data query optimization method, device, equipment and storage medium
Meimaris et al. Extended characteristic sets: graph indexing for SPARQL query optimization
US8924373B2 (en) Query plans with parameter markers in place of object identifiers
US8935233B2 (en) Approximate index in relational databases
US10565201B2 (en) Query processing management in a database management system
CN110909016B (en) Repeated association detection method, device, equipment and storage medium based on database
CN108681603B (en) Method for rapidly searching tree structure data in database and storage medium
US9218394B2 (en) Reading rows from memory prior to reading rows from secondary storage
CN110968593B (en) Database SQL statement optimization method, device, equipment and storage medium
CN110990423B (en) SQL statement execution method, device, equipment and storage medium
CN111666468A (en) Method for searching personalized influence community in social network based on cluster attributes
US11288266B2 (en) Candidate projection enumeration based query response generation
CN109885585A (en) Support the distributed data base system and method for storing process, trigger and view
JP5844824B2 (en) SPARQL query optimization method
US11573987B2 (en) System for detecting data relationships based on sample data
CN111078705A (en) Spark platform based data index establishing method and data query method
CN110704472A (en) Data query statistical method and device
RU2433467C1 (en) Method of forming aggregated data structure and method of searching for data through aggregated data structure in data base management system
CN108959584B (en) Community structure-based method and device for processing graph data
CN114637752A (en) Connection query statement processing method, device, equipment and storage medium
CN110162574B (en) Method and device for determining data redistribution mode, server and storage medium
CN113821514A (en) Data splitting method and device, electronic equipment and readable storage medium
JP6666312B2 (en) Multidimensional data management system and multidimensional data management method

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination
GR01 Patent grant
GR01 Patent grant