Self-connection elimination method and device for query statement, equipment and storage medium

By eliminating the self-connection operation in the query statement according to the type of the self-connection operation, and generating a new query statement, the problem of large calculation and low efficiency of the self-connection operation in the prior art is solved, and the effect of improving the execution efficiency of the query statement is achieved.

CN118939685APending Publication Date: 2024-11-12CETC JINCANG (BEIJING) TECH CO LTD

Patent Information

Application Number
CN202411311624.5
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-09-19
Publication Date
2024-11-12

AI Technical Summary

Technical Problem

The prior art includes the problem of large calculation volume and low calculation efficiency of query statements that self-connection operations.

Method used

By obtaining the query statement and eliminating the self-connection operation according to the connection type of the self-connection operation, a new query statement is generated, thereby reducing redundant calculations and improving query efficiency.

Benefits of technology

By eliminating the self-join operation, comparative calculations between different tables are reduced, the complexity and calculation amount of query statements are reduced, and the execution efficiency of query statements is improved.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118939685A_ABST
    Figure CN118939685A_ABST
Patent Text Reader

Abstract

The invention provides a query statement self-connection elimination method and device, equipment and a storage medium, after query equipment obtains a first query statement, when the first query statement comprises a self-connection operation of a first table and a second table, and the self-connection operation comprises an equivalent connection of the first table and the second table, the first table and the second table are connected; according to different connection types of the self-connection operation, the self-connection operation in the first query statement is eliminated to obtain the second query statement, and finally the second query statement is executed. Therefore, the calculation amount required for executing the query statement is reduced, the complexity is reduced, and the calculation efficiency of executing the query statement including the self-connection operation is improved.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a method, device, equipment and storage medium for eliminating self-joins of query statements. Background Art

[0002] The query statement of the database includes various query conditions. When the query device executes the query statement, it can judge whether the data in the query table meets the query conditions, and finally query to obtain the data that meets the conditions.

[0003] In the prior art, query conditions in some query statements include comparing data in a table to be queried with data values ​​in other tables. The connection relationship between the table to be queried and other tables established in such a query statement is called a self-join operation.

[0004] With the prior art, when a query device executes a query statement including a self-join operation, a large amount of calculations between different tables need to be performed, which is highly complex, resulting in low calculation efficiency when executing the query statement including a self-join operation. Summary of the invention

[0005] The present application provides a method, device, equipment and storage medium for eliminating self-join of a query statement, so as to solve the technical problem in the prior art that the query statement including self-join operation has large computation amount and low computation efficiency.

[0006] A first aspect of the present application provides a method for eliminating self-joins of query statements, comprising: obtaining a first query statement; when the first query statement includes a self-join operation of a first table and a second table, and the self-join operation includes an equi-join between the first table and the second table, eliminating the self-join operation in the first query statement according to the connection type of the self-join operation to obtain a second query statement; and executing the second query statement.

[0007] In an embodiment of the first aspect of the present application, the self-join operation in the first query statement is eliminated according to the connection type of the self-join operation to obtain a second query statement, including: when the connection type is an inner join and the equi-join operation is implemented through a unique index, the self-join operation in the first query statement is eliminated to obtain the second query statement.

[0008] In an embodiment of the first aspect of the present application, the method of eliminating the self-join operation in the first query statement according to the connection type of the self-join operation to obtain the second query statement includes: when the connection type is an outer join, eliminating the self-join operation in the first query statement according to whether the first query statement includes a deduplication operation to obtain the second query statement.

[0009] In an embodiment of the first aspect of the present application, when the connection type is an outer join, the self-join operation in the first query statement is eliminated to obtain a second query statement according to whether the first query statement includes a deduplication operation, including: when the connection type is an outer join, the first query statement does not include a deduplication operation, the query result of the first query statement only comes from the first table and the external table in the second table, and the equi-join operation is implemented through a unique index, the self-join operation in the first query statement is eliminated to obtain the second query statement; when the connection type is an outer join, the first query statement includes a deduplication operation, and the query result of the first query statement only comes from the external table in the first table and the second table, the self-join operation in the first query statement is eliminated to obtain the second query statement.

[0010] In an embodiment of the first aspect of the present application, eliminating the self-join operation in the first query statement according to the connection type of the self-join operation to obtain the second query statement includes: when the connection type is a semi-join or an anti-join, eliminating the self-join operation in the first query statement to obtain the second query statement.

[0011] In an embodiment of the first aspect of the present application, the elimination of the self-join operation in the first query statement to obtain the second query statement includes: determining the table to be eliminated among the first table and the second table; modifying the statement corresponding to the table to be eliminated in the first query statement to a statement corresponding to another table; modifying the statement corresponding to the table to be eliminated in the first query statement to a statement corresponding to another table, to obtain the second query statement.

[0012] The second aspect of the present application provides a self-join elimination device for a query statement, comprising: an acquisition module, used to obtain a first query statement; a processing module, used to eliminate the self-join operation in the first query statement to obtain a second query statement according to the connection type of the self-join operation when the first query statement includes a self-join operation of a first table and a second table, and the self-join operation includes an equi-join between the first table and the second table; and an execution module, used to execute the second query statement.

[0013] The third aspect of the present application provides a self-connection elimination device for query statements, comprising: a memory and a processor; the memory stores computer-executable instructions; the processor executes the computer-executable instructions stored in the memory, so that the data query device executes the self-connection elimination method for query statements as described in any one of the first aspects of the present application.

[0014] A fourth aspect of the present application provides a computer-readable storage medium storing computer-executable instructions, which, when executed, implement the method for eliminating self-joins of query statements as described in any one of the first aspects of the present application.

[0015] A fifth aspect of the present application provides a computer program product, including a computer program, which, when executed, implements the method for eliminating self-joins of query statements as described in any one of the first aspects of the present application.

[0016] In summary, the method, apparatus, device and storage medium for eliminating self-joins of query statements provided by the present application, after obtaining the first query statement, when the first query statement includes a self-join operation of the first table and the second table, and the self-join operation includes an equi-join of the first table and the second table, the query device further eliminates the self-join operation in the first query statement according to different connection types of the self-join operation to obtain the second query statement, and finally executes the second query statement. Since the self-join operation is eliminated in the second query statement, especially in the scenario when the self-join operation includes an equi-join of the first table and the second table, the self-join in the query statement can be deleted, and the redundant self-join operation is deleted. Therefore, when the query device executes the second query statement, it is not necessary to perform comparison calculations between different tables, thereby reducing the amount of calculations required to execute the query statement, reducing the complexity, and thus improving the calculation efficiency when executing the query statement including the self-join operation. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.

[0018] Figure 1 A schematic diagram of the application scenario of this application;

[0019] Figure 2 A flowchart of an embodiment of a method for eliminating self-joins of a query statement provided by the present application;

[0020] Figure 3 A schematic diagram for determining whether to eliminate self-connection provided by this application;

[0021] Figure 4 A flowchart of an embodiment of a method for eliminating self-joins of query statements provided by the present application;

[0022] Figure 5 A flowchart of an embodiment of a method for eliminating self-joins of query statements provided by the present application;

[0023] Figure 6 A flowchart of an embodiment of a method for eliminating self-joins of query statements provided by the present application;

[0024] Figure 7 A schematic diagram of a query statement self-connection elimination device provided by the present application;

[0025] Figure 8 A structural schematic diagram of a query statement self-connection elimination device provided in this application. DETAILED DESCRIPTION

[0026] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0027] The terms "first", "second", "third", "fourth", etc. (if any) in the specification and claims of the present application and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchangeable where appropriate, so that the embodiments of the present application described herein can be implemented in an order other than those illustrated or described herein, for example. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device comprising a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0028] Figure 1 This is a schematic diagram of the application scenario of this application, such as Figure 1 As shown, the present application is applied in the technical field of databases. The database 20 may be a device that can be used to store data, such as an SQL database, an Oracle database, a Kingbase database, etc., and the present application embodiment does not limit the type of the database 20. The electronic device 10 may be a computer, a server, a workstation, etc., and the electronic device 10 may communicate with the database 20.

[0029] In one application scenario, a user may send a data query statement to the electronic device 10, and the data query statement is used to query the database 20 for data that meets the conditions. The electronic device 10 then obtains data from the database 20 according to the data query statement and returns the data to the user who needs to query the data.

[0030] In recent years, with the development of new database application scenarios such as mobile Internet, big data, artificial intelligence, and new database hardware technologies, the scale and complexity of databases have increased significantly, the amount of data that needs to be processed has become larger and larger, and the efficiency of devices in database management systems when processing query statements has become more and more serious.

[0031] The inventor of the present application found that when the electronic device 10 executes some query statements including self-join operations, it is necessary to process data between multiple tables stored in the database 20 at the same time. For example, when the electronic device 10 executes the query statement select a.*, b.* from t1 a join t1 b on a.pk=b.pk, it is necessary to compare each value a.pk of the pk column in the first table a with each value b.pk of the pk column in the second table b, so as to query and obtain data a.* and b.* that satisfy a.pk=b.pk. Obviously, when the electronic device 10 executes the query statement including the self-join operation, since it involves repeated comparison calculations between two more different tables in the database 20, based on the basic principle that the calculations performed between different tables are larger and more complex than those performed within a single table, the execution efficiency of the query statement including the self-join operation is low.

[0032] Based on this, in response to the problems existing in the above-mentioned application scenarios, the present application provides a method for eliminating self-joins in query statements, which can eliminate self-joins in query statements when processing query statements of a database, especially for different types of self-join operations and other conditions, and determine to eliminate self-joins in different query statements in different scenarios, so as to reduce the computational amount and complexity of query statements, thereby improving the execution efficiency of query statements.

[0033] The technical solution of the present application is described in detail with specific embodiments below. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be described in detail in some embodiments. Figure 2 A flowchart of an embodiment of a method for eliminating self-joins of a query statement provided by the present application is shown in FIG. Figure 2 The method shown can be applied to Figure 1 In the scenario shown, it is executed by an electronic device. Specifically, Figure 2 The self-join elimination methods for the query statement shown include:

[0034] S101: Obtain a first query statement from a database. The electronic device may receive a query statement indicated by a user, or the electronic device may receive a query statement sent by another device. In this embodiment, the query statement received by the electronic device is recorded as the first query statement. In one embodiment, the query statement may be a Structured Query Language (SQL) statement.

[0035] S102: When the first query statement includes a self-join operation of the first table and the second table, and the self-join operation includes an equi-join of the first table and the second table, the self-join operation in the first query statement obtained in S101 is eliminated according to the connection type of the self-join operation to obtain a second query statement.

[0036] In one embodiment, after obtaining the first query statement, the processing of the first query statement may include at least the following stages: first, the first query statement is parsed, then the first query statement is logically and physically optimized by the optimizer to obtain the second query statement, and finally the optimized second query statement is executed by the executor. Then the above step S102 of the present application corresponds to the stage in which the optimizer optimizes the first query statement to obtain the second query statement after obtaining the first query statement and before the executor executes the second query statement.

[0037] In one embodiment, the first query statement may be select a.*,b.*from t1 a join t1 b ona.pk=b.pk. In the query statement, a self-join operation between the first table a and the second table b is included, and the self-join operation includes an equi-join relationship of a.pk=b.pk. When the electronic device processes the first query statement in S102, it determines to eliminate the self-join in the first query statement based on the fact that the first query statement includes both a self-join operation and an equi-join relationship.

[0038] In one embodiment, the equivalent connection relationship may be after a preset character, for example, in the first query statement above, the preset character is on, then the equivalent connection relationship included in the self-join operation is a.pk=b.pk after the character on. In other embodiments, the preset character may also be where, etc., and the present application does not limit the specific implementation of such preset characters.

[0039] Specifically, in S102, the electronic device further eliminates the self-connection operation in the first query statement according to the connection type of the self-connection operation. That is, the electronic device uses different judgment methods to determine whether to eliminate the self-connection operation in the first query statement according to different connection types of the self-connection operation.

[0040] For example, Figure 3 is a schematic diagram provided for this application to determine whether self-join is eliminated. As Figure 3 shown, the join types for the self-join operation of the first query statement include: 1. Inner join, 2. Outer join, 3. Semi-join / anti-join. Then, in S102, when the join type is an inner join, it is determined to eliminate the self-join operation in the first query statement. When the join type is an outer join, it is further determined whether to eliminate the self-join operation in the first query statement according to whether the first query statement includes a distinct operation. When the join type is a semi-join or an anti-join, it is determined to eliminate the self-join operation in the first query statement.

[0041] In one embodiment, the query statement described in this application includes a distinct operation, specifically referring to that the query statement includes a distinct distinct operation, a unique distinct operation, or a group by grouping operation, or it can also refer to the query block in non-liftable keyword subqueries such as [not]in, any, all, [not]exists, some, etc., such as: select id from t1 where id<all(select id from t2). When the query statement meets these conditions, it is considered that the query statement includes a distinct operation; correspondingly, when the query statement does not meet these conditions, it is considered that the query statement does not include a distinct operation.

[0042] In one embodiment, eliminating the self-join operation in the first query statement to obtain a second query statement specifically includes: determining the redundant tables to be eliminated in the first table and the second table, and then modifying the statements in the first query statement that include the redundant table to the statements corresponding to another remaining table, and modifying the statements in the first query statement that reference the redundant table to the statements corresponding to the reference remaining table.

[0043] S103: Execute the second query statement. Specifically, specifically, after the optimizer completes the processing of the first query statement to obtain the second query statement in S102, the executor can execute the second query statement optimized by the optimizer.

[0044] In summary, the method for eliminating self-joins in query statements provided in this embodiment is that after the electronic device obtains the first query statement, it eliminates the self-join operation in the first query statement according to different connection types of the self-join operation to obtain the second query statement, and finally executes the second query statement. Since the self-join operation is eliminated in the second query statement, especially in the scenario where the self-join operation includes an equi-join of the first table and the second table, the self-join in the query statement can be deleted, and the redundant self-join operation is deleted. Therefore, when the query device executes the second query statement, it is not necessary to perform comparison calculations between different tables. Since the calculation within a table is smaller and less complex than that between different tables, the amount of calculation required for executing the query statement is reduced, the complexity is reduced, and the calculation efficiency when executing the query statement including the self-join operation is improved.

[0045] Combine the following Figure 4-Figure 6 , the processing of eliminating self-join operations on the first query statements of three different join types provided by the present application is described in detail.

[0046] Figure 4 A flowchart of an embodiment of a method for eliminating self-joins of query statements provided by the present application is shown in FIG. Figure 4 A specific implementation of the processing flow of eliminating the self-join in the first query statement to obtain the second query statement in S102 is shown when the join type is an inner join.

[0047] Specifically, when it is determined through S1021 that the connection type of the first query statement is an inner join, S1022 is continued to determine whether the equivalent join operation is implemented through a unique index, and when S1022 determines to be yes, S1023 is executed to eliminate the self-join operation in the first query statement to obtain the second query statement. If S1022 determines to be no, S1024 is executed without determining whether to eliminate the self-join according to the process of this application, and other subsequent processes are executed.

[0048] For example, take the first query statement select a.*,b.*from t1 a join t1 b on a.pk=b.pk as an example. The connection type of the first query statement is inner connection, and the unique index is used to perform equal connection in the self-connection operation of the first query statement. The unique index here includes the primary key index, the unique index, etc. Under the unique constraint and the restrictions of the same table and the same column, the first query statement can be understood as using the same record for connection. Under the constraint of the unique index, the connection will not increase the number of rows in the result set. Then, the first query statement can eliminate the self-connection to obtain the second query statement select a.*,a.*from t1 a where a.pk is not null.

[0049] For another example, taking the first query statement select a.*,b.*from t1 a join t1 b on a.uni1=b.uni1and a.uni2 and b.uni2 as an example, the connection type of the first query statement is inner join, and the self-join operation of the first query statement performs an equi-join through the unique indexes of multiple columns. Then, after eliminating the self-join, the first query statement can obtain the second query statement select a.*,a.*from t1 a where a.uni1 is not null and b.uni2is not null.

[0050] Figure 5 A flowchart of an embodiment of a method for eliminating self-joins of query statements provided by the present application is shown in FIG. Figure 5 The figure shows a specific implementation of the processing flow of eliminating the self-join operation in the first query statement to obtain the second query statement in S102 when the join type is an outer join and when the first query statement includes a deduplication operation or does not include a deduplication operation. It should be noted that when the type of the self-join operation is an outer join, it is also necessary to satisfy that the query result of the first query statement only comes from the outer table in the first table and the second table, that is, Figure 5 The premise of the judgment process shown is that the target column does not contain the columns of the inner table, ensuring that only the data of the outer table is output.

[0051] Specifically, when it is determined through S1027 that the connection type of the first query statement is an outer connection, it is continued through S1028 to determine whether the first query statement includes a deduplication operation. When S1028 determines that the first query statement does not include a deduplication operation, S1029 is executed to eliminate the self-connection operation in the first query statement to obtain a second query statement.

[0052] For example, taking the first query statement select distinct a.*from t1 a left join t1 b ona.id=b.id as an example, the connection type of the first query statement is outer join and includes deduplication operation, and performs equal join of the same column through ordinary columns. This connection may cause duplicate data in the outer table output in the result set. The result set is deduplicated through distinct, but the content of the outer table is output, and the data is finally deduplicated, which will not affect the result set. The first query statement can eliminate the self-join and obtain the second query statement select distinct a.*from t1 a.

[0053] When S1028 determines that the first query statement includes a deduplication operation, S10210 is used to determine whether the connection condition is implemented through a unique index. When S10210 determines that it is yes, S1029 is executed to eliminate the self-join operation in the first query statement to obtain the second query statement. If S10210 determines that it is no, S10211 is executed without determining whether to eliminate the self-join according to the process of this application, and other subsequent processes are executed.

[0054] For example, taking the first query statement select a.*from t1 a left join t1 b on a.pk=b.pk as an example, the connection type of the first query statement is outer join and does not include deduplication operation. The connection is equal-joined through the primary key column or unique index. Any data in the outer table is connected with the inner table with one and only one result, which will not cause duplicate data to be generated. The result set still contains all the data in the outer table. Then, the first query statement can eliminate the self-join to obtain the second query statement select a.*from t1 a.

[0055] Figure 6 A flowchart of an embodiment of a method for eliminating self-joins of query statements provided by the present application is shown in FIG. Figure 6 The figure shows the processing flow of eliminating the self-join operation in the first query statement to obtain the second query statement when the join type is a semi-join or an anti-join. It should be noted that when the type of the self-join operation is a semi-join or an anti-join, the first query statement can be obtained by converting the in, any, exists, some, all keyword sub-joins to semi / anti joins after promotion.

[0056] Specifically, when it is determined through S10212 that the connection type of the first query statement is a semi-join or an anti-join, S10213 is continued to determine whether the first query statement includes an equijoin of the same column. When S10213 determines that the first query statement includes an equijoin of the same column, S10214 is executed to eliminate the self-join operation in the first query statement to obtain the second query statement. If S102113 determines that the first query statement does not include an equijoin of the same column, S10215 is executed without determining whether to eliminate the self-join according to the process of this application, and other subsequent processes are executed.

[0057] For example, taking the first query statement select a.*from t1 a semi join t1 b on a.id=b.id as an example, the first query statement can be obtained by lifting and transforming select a.*from t1 a where id in (select id from t1b). The connection type of the first query statement is semi-join, and the return value of the sub-join is true or false, and it has nothing to do with whether the data in the sub-join has duplicate values, so both ordinary columns and unique index columns can be used for equal joins. In the end, the columns of the inner table in semi / anti will not appear in the target column. Then the first query statement can eliminate the self-join to obtain the second query statement select a.*from t1 a where a.id is not null.

[0058] In the aforementioned embodiments of the present application, the query statement processing method provided by the embodiments of the present application is introduced, and in order to realize the functions in the methods provided by the above embodiments of the present application, the electronic device as the execution subject may include a hardware structure and / or a software module, and the above functions are realized in the form of a hardware structure, a software module, or a hardware structure plus a software module. Whether one of the above functions is executed in the form of a hardware structure, a software module, or a hardware structure plus a software module depends on the specific application and design constraints of the technical solution.

[0059] Figure 7 A schematic diagram of a query statement self-join elimination device provided in the present application, such as Figure 7 The device shown can be used to perform Figure 2-Figure 6 A method for eliminating self-joins in a query statement provided by any one of the embodiments. In one embodiment, Figure 7 The device 1000 shown includes: an acquisition module 1001, a determination module 1002 and an execution module 1003. The acquisition module 1001 is used to acquire a first query statement of a database; the processing module 1002 is used to eliminate the self-join operation in the first query statement according to the connection type of the self-join operation to obtain a second query statement when the first query statement includes a self-join operation of a first table and a second table, and the self-join operation includes an equi-join between the first table and the second table; and the execution module 1003 is used to execute the second query statement.

[0060] In an embodiment of the present application, the processing module 1002 is specifically configured to eliminate the self-join operation in the first query statement to obtain the second query statement when the join type is an inner join and the equijoin operation is implemented through a unique index.

[0061] In an embodiment of the present application, the processing module 1002 is specifically configured to, when the connection type is an outer join, eliminate the self-join operation in the first query statement according to whether the first query statement includes a deduplication operation to obtain the second query statement.

[0062] In one embodiment of the present application, the processing module 1002 is specifically used to eliminate the self-join operation in the first query statement to obtain the second query statement when the connection type is an outer join, the first query statement does not include a deduplication operation, the query result of the first query statement comes only from the external tables in the first table and the second table, and the equi-join operation is implemented through a unique index; when the connection type is an outer join, the first query statement includes a deduplication operation, and the query result of the first query statement comes only from the external tables in the first table and the second table, eliminate the self-join operation in the first query statement to obtain the second query statement.

[0063] In one embodiment of the present application, the processing module 1002 is specifically used to eliminate the self-join operation in the first query statement to obtain the second query statement according to the connection type of the self-join operation, including: when the connection type is semi-join or anti-join, eliminating the self-join operation in the first query statement to obtain the second query statement.

[0064] In one embodiment of the present application, the processing module 1002 is specifically used to determine the table to be eliminated from the first table and the second table; modify the statement corresponding to the table to be eliminated in the first query statement to a statement corresponding to another table; modify the statement corresponding to the table to be eliminated in the first query statement to a statement corresponding to another table, so as to obtain a second query statement.

[0065] The specific implementation method and principle of the query statement self-connection elimination device of the query statement mentioned above refer to the description of the query statement self-connection elimination method of the query statement mentioned above, which will not be repeated here.

[0066] It should be noted that it should be understood that the division of the various modules of the above device is only a division of logical functions. In actual implementation, they can be fully or partially integrated into one physical entity, or they can be physically separated. And these modules can all be implemented in the form of software called by processing elements; they can also be all implemented in the form of hardware; some modules can also be implemented in the form of software called by processing elements, and some modules can be implemented in the form of hardware. For example, the processing module can be a separately established processing element, or it can be integrated in a chip of the above device. In addition, it can also be stored in the memory of the above device in the form of program code, and called and executed by a processing element of the above device. The function of the above-mentioned module is determined. The implementation of other modules is similar. In addition, these modules can be fully or partially integrated together, or they can be implemented independently. The processing element described here can be an integrated circuit with signal processing capabilities. In the implementation process, each step of the above method or each module above can be completed by an integrated logic circuit of hardware in the processor element or instructions in the form of software.

[0067] For example, the above modules may be one or more integrated circuits configured to implement the above methods, such as one or more application specific integrated circuits (ASIC), or one or more microprocessors (digital signal processors, DSP), or one or more field programmable gate arrays (FPGA), etc. For another example, when a module above is implemented in the form of a processing element scheduling program code, the processing element may be a general-purpose processor, such as a central processing unit (CPU) or other processor that can call program code. For another example, these modules may be integrated together and implemented in the form of a system-on-a-chip (SOC).

[0068] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware or any combination thereof. When implemented using software, it can 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. When the computer program instructions are loaded and executed on a computer, the process or function described in the embodiment of the present application is generated in whole or in part. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions may be stored in a computer-readable storage medium, or transmitted from one computer-readable storage medium to another computer-readable storage medium, for example, the computer instructions may be transmitted from a website site, computer, server or data center by wired (e.g., coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) mode to another website site, computer, server or data center. The computer-readable storage medium may be any available medium that a computer can access or a data storage device such as a server or data center that includes one or more available media integrated. The available medium may be a magnetic medium, (e.g., a floppy disk, a hard disk, a tape), an optical medium (e.g., a DVD), or a semiconductor medium (e.g., a solid state disk (SSD)), etc.

[0069] For example, Figure 8 A structural diagram of a query statement self-connection elimination device provided by this application, such as Figure 8 The device shown can be used to perform Figure 2-Figure 6 A method for eliminating self-joins of a query statement as shown in any one of the items. In one embodiment, Figure 8 The query statement processing device 2000 shown includes one or more processors 2001 and a memory 2002; wherein the memory 2002 is used to store computer executable instructions, and the processor 2001 can execute the computer executable instructions stored in the memory 2002. When the computer executable instructions are executed by the processor 2001, the processor 2001 implements the self-join elimination method of any query statement in the aforementioned embodiments of the present application.

[0070] In one embodiment, if Figure 8 The query statement self-connection elimination device 2000 shown also includes a communication interface 2003 , wherein the processor 2001 can communicate with other devices through the communication interface 2003 , for example, the processor 2001 obtains the first query statement through the communication interface 2003 .

[0071] The present application also provides a computer-readable storage medium, which stores computer-executable instructions. When the computer-executable instructions are executed, they can be used to implement a method for eliminating self-joins of any query statement in the aforementioned embodiments of the present application.

[0072] An embodiment of the present application also provides a chip for executing instructions, wherein the chip is used to execute a method for eliminating self-joins of any query statement as described above in the present application.

[0073] An embodiment of the present application also provides a computer program product, including a computer program, which, when executed, implements the self-join elimination method of any query statement described above in the present application.

[0074] Those skilled in the art can understand that all or part of the steps of implementing the above-mentioned method embodiments can be completed by hardware related to program instructions. The aforementioned program can be stored in a computer-readable storage medium. When the program is executed, the steps of the above-mentioned method embodiments are executed; and the aforementioned storage medium includes: ROM, RAM, disk or optical disk and other media that can store program codes.

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

Claims

1. A method for eliminating self-joins of query statements, characterized in that: include: Get the first query statement of the database; When the first query statement includes a self-join operation of a first table and a second table, and the self-join operation includes an equi-join between the first table and the second table, eliminating the self-join operation in the first query statement according to the connection type of the self-join operation to obtain a second query statement; Execute the second query statement.

2. The method according to claim 1, characterized in that: The step of eliminating the self-join operation in the first query statement according to the join type of the self-join operation to obtain the second query statement includes: When the connection type is an inner connection and the equi-connection operation is implemented through a unique index, the self-connection operation in the first query statement is eliminated to obtain a second query statement.

3. The method according to claim 1, characterized in that The step of eliminating the self-join operation in the first query statement according to the join type of the self-join operation to obtain the second query statement includes: When the connection type is an outer connection, the self-connection operation in the first query statement is eliminated according to whether the first query statement includes a deduplication operation to obtain a second query statement.

4. The method according to claim 3, characterized in that When the connection type is an outer connection, eliminating the self-connection operation in the first query statement according to whether the first query statement includes a deduplication operation to obtain a second query statement includes: When the connection type is an outer connection, the first query statement does not include a deduplication operation, the query results of the first query statement come only from the first table and the outer table in the second table, and the equi-connection operation is implemented through a unique index, eliminating the self-connection operation in the first query statement to obtain a second query statement; When the connection type is an outer connection, the first query statement includes a deduplication operation, and the query results of the first query statement only come from the outer tables in the first table and the second table, the self-connection operation in the first query statement is eliminated to obtain the second query statement.

5. The method according to claim 1, characterized in that The step of eliminating the self-join operation in the first query statement according to the join type of the self-join operation to obtain the second query statement includes: When the connection type is a semi-join or an anti-join, the self-join operation in the first query statement is eliminated to obtain a second query statement.

6. The method according to any one of claims 1 to 5, characterized in that: Eliminating the self-join operation in the first query statement to obtain a second query statement includes: Determine a table to be deleted from the first table and the second table; Modify the statement corresponding to the table to be deleted in the first query statement into a statement corresponding to another table; The statement in the first query statement that references the table to be deleted is modified into a statement that references another table, to obtain the second query statement.

7. A query statement self-join elimination device, characterized in that: include: An acquisition module, used to acquire a first query statement from a database; a processing module, configured to, when the first query statement includes a self-join operation of a first table and a second table, and the self-join operation includes an equi-join between the first table and the second table, eliminate the self-join operation in the first query statement according to a join type of the self-join operation to obtain a second query statement; An execution module is used to execute the second query statement.

8. A query statement self-join elimination device, characterized in that: include: Memory and processor; The memory stores computer executable instructions; The processor executes the computer executable instructions stored in the memory, so that the data query device executes the self-join elimination method of the query statement according to any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that: Computer executable instructions are stored, and when the computer executable instructions are executed, the method for eliminating self-joins of query statements according to any one of claims 1 to 6 is implemented.

10. A computer program product, characterized in that It comprises a computer program, which, when executed, implements the method for eliminating self-joins of query statements as described in any one of claims 1 to 6.

Citation Information

Patent Citations

  • Structured query statement optimization method and device, equipment and storage medium

    CN113204568A

  • SQL (Structured Query Language) statement optimization method and device, equipment, medium and program product

    CN114297226A

  • Connection query statement processing method and device, equipment and storage medium

    CN114637752A

  • Data query method and device, storage medium and electronic equipment

    CN115617856A

  • Table elimination optimizations

    US20090070315A1

Cited By

  • A database outer join elimination method, apparatus, device, medium and product

    CN122795942A