Method for optimizing cte expression based on gbase8c distributed database

Abstract

The invention provides a cte expression optimization method based on a gbase8c distributed database, and the method is characterized in that a cte push-down mode is set, an execution plan containing a cte expression is pushed down to a dn node, and when the dn node receives the execution plan containing the cte expression, an actuator realizes a producer and consumer model related to cte push-down. The main innovation of the method is embodied in that: 1) a database kernel automatically judges a cte processing mode by judging conditions such as complexity of a current cte expression and size of a result set, and also can automatically determine the cte processing mode through a switch state specified by a user and related keywords; the method has the advantages that the cte expression can be pushed down in a distributed database scene, the cte expression can be pushed down in the distributed database scene, the cte result set can be shared by a plurality of threads (cte producer-consumer model) after the cte expression is read and cached once after the cte expression is pushed down, and parallel execution of the cte expression after the cte expression is pushed down is realized.

Description

Technical Field

[0001] The present invention belongs to the field of database and data processing technology, and more specifically relates to a distributed database processing complex query scenarios containing CTE expressions, and more specifically to a CTE expression optimization method based on a gbase8c distributed database. Background Art

[0002] Query is one of the most commonly used and important transactions in a database. In large-scale distributed database systems, how to improve query efficiency and enhance user query experience has always been a research hotspot in this field. When a distributed database system executes a query SQL (Structured Query Language) statement (or command) sent by a user, the CN node (Coordinator Node) selects different query methods based on conditions such as the number of tables involved in the query and the complexity of the query. Common Table Expression (CTE) is a temporary named result set defined in the SQL standard. It can be referenced multiple times within the execution scope of a single query. It is mainly used to simplify complex queries and improve code readability. CTE is only valid during the execution of the query that defines it and is automatically destroyed after the query ends. It is similar to a temporary view, but does not need to be explicitly created or deleted. It is defined through the WITH keyword. The basic syntax is as follows: WITH cte_name (column1, column2) AS ( SELECT column1, column2 FROM table WHERE condition ) SELECT * FROM cte_name; In most distributed databases, CTE processing is generally divided into the following two types: CTE reuse and CTE expansion. CTE expansion can extend each CTE part to the main query for more optimization. The benefit of CTE reuse is to reduce duplicate data calculation and I / O.

[0003] Currently, gbase8c processes complex queries containing CTE expressions in the following ways: 1. CTE reuse: This can be summarized as generating the same data upstream and consuming it multiple times downstream. However, in the CTE reuse scenario with gbase8c, temporary CTE data can only be generated on the CN node. The CTE can only retrieve the original data in the table from the DN node. Other CTE query operations (such as aggregation, joins, and window functions) can only be executed on the CN node and cannot be run in parallel, which is very time-consuming. 2. CTE expansion: During the plan generation phase, gbase8c expands the CTE expression into the main query according to the subquery promotion rules. This allows more rules to be applied for optimization. However, if the query within the CTE is too complex, calling the CTE expression multiple times will increase the calculation of duplicate data and I / O. Summary of the Invention

[0004] The present invention aims to address the issue of two illogical processing methods for CTE expressions and provides a CTE expression optimization method based on the gbase8c distributed database. This method enables CTE expression reuse and pushdown to DN nodes for execution, and enables parallel execution within CTE pushdowns and overall query execution. It also implements a CTE producer-consumer model, enabling single-time CTE data caching and multiple reuse at the DN node.

[0005] The present invention is achieved through the following technical solutions: A CTE expression optimization method based on the gbase8c distributed database includes: Set the CTE pushdown mode. In this mode, when parsing the query statement in the CTE expression, the distribution characteristics and distribution key values of the CTE result set are recorded. When the CTE query is run in parallel, a local_gather operator is added to the top-level node to cache the result set of the CTE expression on the DN node and push the execution plan containing the CTE expression down to the DN node. When a DN node receives an execution plan containing a CTE expression, the executor implements the producer-consumer model related to CTE pushdown. It implements a CTE producer thread, which is responsible for caching the CTE query result set. The consumer thread reads the CTE cache and implements blocking and synchronization between the producer and consumer threads.

[0006] In the above technical solution, the user decides whether to start the CTE push-down mode by adding a specified keyword in the CTE expression.

[0007] In the above technical solution, the designated keyword is "MATERIALIZED".

[0008] In the above technical solution, a switch unit is provided in the database system. The user-specified state of the switch unit and whether the user has added a specified keyword to the CTE expression jointly determine whether to enable the CTE pushdown mode. The switch unit has two states: on and off. In the off state, the CTE pushdown mode is disabled; in the on state, the CTE pushdown mode is enabled. When executing a query SQL statement containing a CTE expression sent by the user, the distributed database system determines the execution mode of the CTE expression by analyzing whether the specified keyword is added to the CTE expression: if the CTE expression contains the specified keyword, the CTE expression is pushed down; if the CTE expression does not contain the specified keyword, the CTE expression is expanded.

[0009] In the above technical solution, the database system automatically decides whether to push down the CTE expression by judging whether the complexity of the current CTE expression and the size of the result set meet the set conditions.

[0010] In the above technical solution, a switch unit is set in the database system. Whether to enable the CTE pushdown mode is determined by the user's specified state of the switch unit, whether the user adds specified keywords in the CTE expression, and the rules for automatically determining the CTE processing mode. The switch unit has three states: on, off, and auto. The switch unit is in the off state: the cte push-down mode is turned off; When the switch unit is in the on state, CTE pushdown is enabled. When the distributed database system executes a query SQL statement containing a CTE expression sent by the user, it analyzes whether the CTE expression contains the specified keyword to determine the execution mode of the CTE expression: if the CTE expression contains the specified keyword, the CTE expression is pushed down; if the CTE expression does not contain the specified keyword, the CTE expression is expanded. When the switch unit is in the auto state, the database system automatically determines whether to push down the CTE expression by judging whether the complexity of the current CTE expression and the size of the result set meet the set conditions. If the set conditions are met, the CTE expression is pushed down; otherwise, the CTE is expanded into the main query.

[0011] In the above technical solution, the following are the steps performed by the actuator: 1) After receiving the execution plan containing the CTE expression, the executor traverses each CTE expression and initializes the producer thread for each CTE expression: For each CTE expression, a CTE cache block belonging to the expression is created. A thread lock, two conditional variables, and a reference count are initialized in the cache block. The first conditional variable controls whether to block the CTE consumer thread from consuming data, and the second conditional variable controls whether to block the CTE producer thread from destroying data. The CTE query plan corresponding to the CTE expression is added to the CTE producer thread. The main query plan is traversed. Whenever a CTescan operator is found that needs to consume the CTE expression cache result set, the reference count in the corresponding CTE cache block is incremented by 1, and the cache block pointer is added to the CTescan operator. 2) Before the main query begins executing, all CTE producer threads are started, and then the execution plan of the main query is executed. When the main query calls the ctescan operator, the pthread_cond_wait function is called, and the CTE consumer thread is blocked from consuming data through the first condition until the CTE producer thread calls the pthread_cond_broadcast function to notify all consumers that the CTE subquery has completed. The process of consuming the result set in the CTE cache block is then continued. After the ctescan operator completes, the reference count in the CTE cache block is decremented by 1 through an atomic operation. When the reference count in the CTE cache block is reduced to 0, all CTE consumers have completed execution, and the pthread_cond_signal function is called to notify the CTE producer thread to destroy the data through the second condition. 3) CTE producer execution process: The CTE producer thread executes the corresponding CTE query plan and adds the returned result set to the CTE cache block. After the query is completed and the result set is added to the cache block, the pthread_cond_broadcast function is called to notify all CTE consumers to read the cache block. Subsequently, it is determined whether the reference count is greater than 0. If it is greater than 0, the pthread_cond_wait function is called to block the CTE producer thread through the second condition to destroy the data and wait for all CTE consumers to complete the execution. After all CTE consumers have completed the execution, the last consumer to complete calls the pthread_cond_signal function to notify consumers to call the CTE cache block destruction process to reclaim memory.

[0012] Another aspect of the present invention provides an electronic device, comprising: one or more processors; and a memory for storing one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors implement the above-mentioned method.

[0013] Another aspect of the present invention provides a computer-readable storage medium storing computer-executable instructions, which are used to implement the above method when executed.

[0014] Another aspect of the present invention provides a computer program product, which includes computer executable instructions. When the instructions are executed, they are used to implement the above method.

[0015] The advantages and beneficial effects of the present invention are: This invention provides a method for optimizing CTE expressions in a gbase8c distributed database. This method is applied to optimizing databases for complex query scenarios involving CTE expressions, resolving the current shortcomings of databases' single CTE processing method and long execution times. The technical value of this method lies in: 1) providing more solutions for databases' CTE processing; 2) improving the database's execution efficiency for CTE queries, enhancing the user experience. The main innovations of this technology are: 1) the database kernel independently determines the CTE processing method based on conditions such as the complexity of the current CTE expression and the size of the result set. It can also determine the CTE processing method based on user-specified switch states and related keywords; 2) implementing the pushdown of CTE expressions in distributed database scenarios; and 3) implementing a process whereby pushed-down CTE expressions are read once, cached, and multiple threads share a CTE result set (a CTE producer-consumer model), enabling parallel execution of pushed-down CTE expressions. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] Figure 1 This is an application scenario diagram of the CTE expression optimization method based on the gbase8c distributed database of the present invention.

[0017] Figure 2 This is a flowchart of the CTE expression optimization method based on the gbase8c distributed database of the present invention.

[0018] Figure 3 Execute the graph for the reused way the database executes CTE before optimization.

[0019] Figure 4 Execution graph for how the database executes CTE before optimization.

[0020] Figure 5 Execute the push-down graph for executing CTE in the optimized database.

[0021] For ordinary technicians in this field, other relevant drawings can be obtained based on the above drawings without any creative work. DETAILED DESCRIPTION

[0022] Hereinafter, embodiments of the present invention will be described with reference to the accompanying drawings. However, it should be understood that these descriptions are exemplary only and are not intended to limit the scope of the present invention. In the following detailed description, for ease of explanation, many specific details are set forth to provide a comprehensive understanding of embodiments of the present invention. However, it is apparent that one or more embodiments may also be implemented without these specific details. In addition, in the following description, descriptions of known structures and technologies are omitted to avoid unnecessary confusion of the concept of the present invention.

[0023] The terms used herein are only for describing specific embodiments and are not intended to limit the present invention. The terms "comprise", "include", etc. used herein indicate the presence of the features, steps, operations and / or components, but do not exclude the presence or addition of one or more other features, steps, operations or components.

[0024] All terms used herein (including technical and scientific terms) have the meanings commonly understood by those skilled in the art unless otherwise defined. It should be noted that the terms used herein should be interpreted as having a meaning consistent with the context of this specification and should not be interpreted in an idealized or overly rigid manner.

[0025] In the technical solution of the present invention, the user information involved (including but not limited to user personal information, user image information, user device information, such as location information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) are all information and data authorized by the user or fully authorized by all parties, and the collection, storage, use, processing, transmission, provision, invention and application of the relevant data comply with relevant laws, regulations and standards, take necessary confidentiality measures, do not violate public order and good morals, and provide corresponding operation entrances for users to choose to authorize or refuse.

[0026] In scenarios where personal information is used for automated decision-making, the methods, devices, and systems provided by embodiments of the present invention provide users with corresponding operational portals, allowing them to choose to agree or reject the automated decision-making results; if the user chooses to reject, the expert decision-making process will be entered. The term "automated decision-making" herein refers to the activity of automatically analyzing and evaluating an individual's behavioral habits, interests, or economic, health, or credit status through computer programs and making decisions. The term "expert decision-making" herein refers to the activity of decision-making by individuals who specialize in a particular field, possess specialized experience, knowledge, and skills, and have reached a certain level of professional expertise.

[0027] In the gbase8c distributed database system, complex queries containing CTE expressions can be processed using two methods: CTE reuse and CTE expansion. In the CTE reuse scenario, temporary CTE data can only be generated on the CN node, and the CTE itself can only retrieve raw table data from the DN node. Other CTE query operations (such as aggregations, joins, and window functions) can only be executed on the CN node and cannot be run in parallel, which is very time-consuming. In the CTE expansion scenario, gbase8c expands the CTE expression into the main query according to the subquery promotion rules during the plan generation phase. This allows for more optimization rules to be applied. However, if the query within the CTE is too complex, calling the CTE expression multiple times will increase the calculation of duplicate data and increase I / O.

[0028] In view of this, an embodiment of the present invention provides a CTE expression optimization method based on the gbase8c distributed database: setting a CTE push-down mode. In the CTE push-down mode: when parsing the query statement in the CTE expression, the characteristics and distribution key values of the CTE result set distribution are recorded. When the CTE query is parallel, a local_gather operator is added to the top-level node to cache the result set of the CTE expression in the DN node and push down the execution plan containing the CTE expression to the DN node. When the DN node receives the execution plan containing the CTE expression, the executor implements the producer-consumer model related to the CTE push-down. The CTE producer thread is implemented to cache the CTE query result set, the consumer thread reads the CTE cache, and the blocking and synchronization between the producer thread and the consumer thread are realized.

[0029] It should be noted that the CTE expression optimization method based on the gbase8c distributed database of the present invention can be used in the field of computer technology. In database task processing scenarios, it can also be used in any field other than the field of computer technology, such as the field of artificial intelligence technology.

[0030] Figure 1 The following schematically illustrates an application scenario diagram of a CTE expression optimization method based on a gbase8c distributed database according to an embodiment of the present invention.

[0031] like Figure 1 As shown, the application scenario 100 according to this embodiment may include a first terminal device 101, a second terminal device 102, a third terminal device 103, a network 104, and a cluster server 105. The network 104 is used as a medium for providing a communication link between the first terminal device 101, the second terminal device 102, the third terminal device 103, and the cluster server 105. The network 104 may include various connection types, such as wired or wireless communication links or optical fiber cables.

[0032] A user may use a first terminal device 101, a second terminal device 102, or a third terminal device 103 to interact with a cluster server 105 via a network 104 to receive or send messages, etc. Various communication client applications may be installed on the first terminal device 101, the second terminal device 102, or the third terminal device 103, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, social platform software, etc. (for example only).

[0033] The first terminal device 101 , the second terminal device 102 , and the third terminal device 103 may be various electronic devices having display screens and supporting web browsing, including but not limited to smart phones, tablet computers, laptop computers, desktop computers, and the like.

[0034] The cluster server 105 may be a server that provides various services, such as a background management server that provides support for websites browsed by users using the first terminal device 101, the second terminal device 102, and the third terminal device 103 (only for Example). The distributed database is deployed on the cluster server 105, which includes multiple server nodes that can analyze and process received user requests and other data, and feed back the processing results (such as web pages, information, or data obtained or generated according to user requests) to the terminal device.

[0035] It should be noted that the CTE expression optimization method based on the gbase8c distributed database provided in the embodiment of the present invention is generally executed by the cluster server 105.

[0036] It should be understood that Figure 1 The number of terminal devices, networks and servers in the embodiment is merely illustrative. Any number of terminal devices, networks and servers may be provided as required.

[0037] It should be noted that the sequence numbers of the operations in the following method are only used to indicate the operation for the purpose of description, and should not be regarded as indicating the order in which the operations should be performed. Unless explicitly stated, the method does not need to be performed in the order shown.

[0038] See below for details. Figure 2 , a detailed description of the CTE expression optimization method based on the gbase8c distributed database according to an embodiment of the present invention is given. The method includes the following steps: Step S1: Set the CTE pushdown mode. In this mode, when parsing the query statement in the CTE expression, the distribution characteristics and distribution key values of the CTE result set are recorded. When the CTE query is run in parallel, the local_gather operator needs to be added to the top-level node to cache the result set of the CTE expression on the DN node and push the execution plan containing the CTE expression down to the DN node.

[0039] Furthermore, set the activation rules of the CTE push mode (i.e. whether to enable the CTE push mode). The activation rules of the CTE push mode include the following: 1) For example, the user can add a specified keyword to the CTE expression (that is, the with clause that defines the CTE) to determine whether to enable the CTE pushdown mode.

[0040] Specifically, in this embodiment, the specified keyword is "MATERIALIZED". When the distributed database system executes a query SQL statement (or command) containing a CTE expression sent by a user, it determines how to execute the CTE expression by analyzing whether the specified keyword "MATERIALIZED" is added to the CTE expression. If the CTE expression contains the specified keyword "MATERIALIZED", the CTE expression is pushed down; if the CTE expression does not contain the specified keyword "MATERIALIZED", the CTE expression is expanded.

[0041] 2) For example, a switch unit is set (defined) in the database system, and the user specifies the state of the switch unit and adds a specified keyword in the CTE expression to jointly determine whether to start the CTE pushdown mode.

[0042] Specifically, the switch unit includes two states: on state (start state) and off state (closed state); the specified keyword is "MATERIALIZED".

[0043] The switch unit is in the off state: CTE push-down mode is disabled, and the database processes CTEs in the same way as before; The switch unit is in the on state: CTE pushdown mode is allowed. In this state, when the distributed database system executes a query SQL statement (or command) containing a CTE expression sent by the user, it determines the execution mode of the CTE expression by analyzing whether the specified keyword "MATERIALIZED" is added to the CTE expression: if the specified keyword "MATERIALIZED" is included in the CTE expression, the CTE expression is pushed down; if the specified keyword "MATERIALIZED" is not included in the CTE expression, the CTE expression is expanded.

[0044] 3) For example, a rule for automatically determining the CTE processing method is set (defined) in the database system to automatically determine whether the CTE expression is pushed down.

[0045] Specifically, the database system automatically determines whether to push down a CTE expression by evaluating the complexity of the current CTE expression and whether the result set size meets the specified conditions. If so, the CTE expression is pushed down; otherwise, the CTE is expanded into the main query. Furthermore, the system automatically determines whether to push down a CTE expression based on multiple factors, including the total cost of the query in the CTE, the number of rows in the CTE subquery result set, the number of times the CTE is referenced, and whether the CTE expression can be promoted according to subquery promotion rules.

[0046] 4) For example, a switch unit is set (defined) in the database system. Whether to start the CTE pushdown mode is determined by the user specifying the state of the switch unit, whether the user adds specified keywords in the CTE expression, and the rules for automatically determining the CTE processing method.

[0047] Specifically, the switch unit includes three states: on state (start state), off state (closed state) and auto state (automatic state).

[0048] The switch unit is in the off state: CTE push-down mode is disabled, and the database processes CTEs in the same way as before; The switch unit is in the on state: CTE pushdown mode is allowed. In this state, when the distributed database system executes a query SQL statement (or command) containing a CTE expression sent by the user, it determines the execution mode of the CTE expression by analyzing whether the specified keyword "MATERIALIZED" is added to the CTE expression: if the specified keyword "MATERIALIZED" is included in the CTE expression, the CTE expression is pushed down; if the specified keyword "MATERIALIZED" is not included in the CTE expression, the CTE expression is expanded.

[0049] When the switch unit is in the auto state, the database system no longer considers the influence of the specified "MATERIALIZED" keyword. In this state, the database system automatically determines whether to push down the CTE expression by evaluating the complexity of the current CTE expression and whether the result set size meets the specified conditions. If the specified conditions are met, the CTE expression is pushed down; otherwise, the CTE is expanded into the main query. Furthermore, the database system automatically determines whether to push down the CTE expression based on multiple factors, including the total cost of the query in the CTE, the number of rows in the CTE subquery result set, the number of times the CTE is referenced, and whether the CTE expression can be promoted according to the subquery promotion rules.

[0050] Step S2: When the DN node receives an execution plan containing a CTE expression, the executor implements the producer-consumer model related to CTE pushdown. The following are the steps executed by the executor: 1) After receiving the execution plan containing the CTE expression, the executor traverses each CTE expression and initializes a producer thread for each CTE expression; Specifically, a CTE cache block is created for each CTE expression. A thread lock, two conditions (the first condition controls whether to block the CTE consumer thread from consuming data, and the second condition controls whether to block the CTE producer thread from destroying data), and a reference count member variable are initialized in the cache block. The CTE query plan (CTESubplan) corresponding to the CTE expression is added to the CTE producer thread. When traversing the main query plan, each time a CTEscan operator is found that needs to consume the CTE expression's cached result set, the reference count in the corresponding CTE cache block is incremented by 1, and the cache block pointer is added to the CTEscan operator. At this point, the CTE producer thread is initialized. 2) Before the main query begins executing, all CTE producer threads are started, and then the execution plan of the main query is executed. When the main query calls the cTescan operator, the pthread_cond_wait function is called. The first condition is used to block the CTE consumer thread from consuming data until the CTE producer thread calls the pthread_cond_broadcast function to notify all consumers that the CTE subquery has completed. The process of consuming the result set in the CTE cache block then continues. Because the consumption process is read-only, the consumer threads do not block each other and only need to maintain the read pointer (readptr) in their respective cTescan operators. After the cTescan operator completes, the reference count in the cTe cache block is decremented by 1 through an atomic operation. When the reference count in the cTe cache block is reduced to 0, all cTe consumers have completed execution. The consumers call the pthread_cond_signal function and, using the second condition, notify the cTe producer thread to destroy the data. 3) CTE Producer Execution Process: The CTE producer thread calls cteSubplan corresponding to the CTE expression, executes the corresponding CTE query plan, and adds the returned result set to the CTE cache block. After the query is completed and the result set is added to the cache block, the pthread_cond_broadcast function is called to notify all CTE consumers that they can read the cache block. Subsequently, the reference count is checked to see if it is greater than 0. If so, the pthread_cond_wait function is called. Using the second condition, the CTE producer thread is blocked to destroy the data and wait for all CTE consumers to complete execution. After all CTE consumers have completed execution, the last consumer to complete calls the pthread_cond_signal function to notify consumers to call the CTE cache block destruction process and reclaim memory. At this point, the execution of a CTE producer-consumer model is complete.

[0051] The expected effects achieved by the present invention are as follows: Before optimization, the database executes CTE in a reused and expanded manner. See the attached Figure 3 and attached Figure 4 , and the cte push-down execution mode optimized by the optimization method of the present invention is shown in the attached Figure 5 , it can be seen that after the CTE expression is pushed down, the HashJoin operator can be executed on the DN node, and the CTE cached result set can be reused in the UNION ALL, avoiding multiple executions of the same HashJoin operation, which greatly saves time.

[0052] The present invention also provides a computer-readable storage medium, which carries one or more programs. When the one or more programs are executed, the method according to the embodiment of the present invention is implemented.

[0053] According to embodiments of the present invention, a computer-readable storage medium may be a non-volatile computer-readable storage medium. Examples include, but are not limited to, a portable computer disk, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination thereof. In the present invention, a computer-readable storage medium may be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, apparatus, or device.

[0054] An embodiment of the present invention also includes a computer program product, which includes a computer program, which contains program code for executing the method provided by the embodiment of the present invention. When the computer program product is run on an electronic device, the program code is used to enable the electronic device to implement the CTE expression optimization method based on the gbase8c distributed database provided by the embodiment of the present invention.

[0055] In one embodiment, the computer program may be stored on a tangible storage medium, such as an optical storage device or a magnetic storage device. In another embodiment, the computer program may be transmitted and distributed in the form of a signal over a network medium, downloaded and installed via a communication component, and / or installed from a removable medium. The program code contained in the computer program may be transmitted using any suitable network medium, including but not limited to wireless, wired, or any suitable combination thereof.

[0056] According to an embodiment of the present invention, the program code for executing the computer program provided by the embodiment of the present invention can be written in any combination of one or more programming languages. Specifically, these computer programs can be implemented using high-level procedural and / or object-oriented programming languages, and / or assembly / machine languages. Programming languages include, but are not limited to, languages such as Java, C++, Python, "C" or similar programming languages. The program code can be executed entirely on the user computing device, partially on the user device, partially on a remote computing device, or entirely on a remote computing device or server. In the case of a remote computing device, the remote computing device can be connected to the user computing device through any type of network, including a local area network (LAN) or a wide area network (WAN), or can be connected to an external computing device (for example, using an Internet service provider to connect via the Internet).

[0057] The flowcharts and block diagrams in the accompanying drawings illustrate the possible architectures, functions, and operations of the systems, methods, and computer program products according to various embodiments of the present invention. In this regard, each box in the flowchart or block diagram may represent a module, program segment, or portion of code, which contains one or more executable instructions for implementing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the boxes may occur in an order different from that marked in the accompanying drawings. For example, two boxes shown in succession may actually be executed substantially in parallel, or they may sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flowchart, as well as the combination of boxes in the block diagram or flowchart, may be implemented using a dedicated hardware-based system that performs the specified function or operation, or may be implemented using a combination of dedicated hardware and computer instructions. It will be understood by those skilled in the art that the features described in the various embodiments of the present invention may be combined and / or coupled in various ways, even if such combinations or couplings are not explicitly described in the present invention. In particular, the features described in the various embodiments of the present invention may be combined and / or coupled in various ways without departing from the spirit and teachings of the present invention. All such combinations and / or combinations fall within the scope of the present invention.

[0058] The above describes embodiments of the present invention. However, these embodiments are for illustrative purposes only and are not intended to limit the scope of the present invention. Although each embodiment has been described separately above, this does not mean that the measures in each embodiment cannot be advantageously used in combination. Without departing from the scope of the present invention, those skilled in the art may make various substitutions and modifications, which should all fall within the scope of the present invention.

Claims

1. A CTE expression optimization method based on the gbase8c distributed database, characterized in that: include: Set the CTE pushdown mode. In this mode, when parsing the query statement in the CTE expression, the distribution characteristics and distribution key values of the CTE result set are recorded. When the CTE query is run in parallel, a local_gather operator is added to the top-level node to cache the result set of the CTE expression on the DN node and push the execution plan containing the CTE expression down to the DN node. When a DN node receives an execution plan containing a CTE expression, the executor implements the producer-consumer model related to CTE pushdown. It implements a CTE producer thread, which is responsible for caching the CTE query result set. The consumer thread reads the CTE cache and implements blocking and synchronization between the producer and consumer threads.

2. The CTE expression optimization method based on the gbase8c distributed database according to claim 1 is characterized in that: The user adds a specified keyword to the CTE expression to determine whether to enable the CTE pushdown mode.

3. The CTE expression optimization method based on the gbase8c distributed database according to claim 1 is characterized in that: The specified keyword is MATERIALIZED.

4. The CTE expression optimization method based on the gbase8c distributed database according to claim 1 is characterized in that: A switch unit is set in the database system. The user specifies the state of the switch unit and whether the user adds a specified keyword in the CTE expression to jointly determine whether to start the CTE pushdown mode. The switch unit has two states: on and off. In the off state, the CTE pushdown mode is disabled; in the on state, the CTE pushdown mode is enabled. When the distributed database system executes a query SQL statement containing a CTE expression sent by the user, it analyzes whether the specified keyword is added to the CTE expression to determine the execution mode of the CTE expression: if the CTE expression contains the specified keyword, the CTE expression is pushed down; if the CTE expression does not contain the specified keyword, the CTE expression is expanded.

5. The CTE expression optimization method based on the gbase8c distributed database according to claim 1 is characterized in that: The database system automatically decides whether to push down the CTE expression by judging whether the complexity of the current CTE expression and the size of the result set meet the set conditions.

6. The CTE expression optimization method based on the gbase8c distributed database according to claim 1 is characterized in that: A switch unit is set up in the database system. Whether to enable CTE pushdown is determined by the user-specified state of the switch unit, whether the user adds specified keywords in the CTE expression, and the rules for automatically determining the CTE processing method. The switch unit has three states: on, off, and auto. The switch unit is in the off state: the cte push-down mode is turned off; When the switch unit is in the on state, CTE pushdown is enabled. When the distributed database system executes a query SQL statement containing a CTE expression sent by the user, it analyzes whether the CTE expression contains the specified keyword to determine the execution mode of the CTE expression: if the CTE expression contains the specified keyword, the CTE expression is pushed down; if the CTE expression does not contain the specified keyword, the CTE expression is expanded. When the switch unit is in the auto state, the database system automatically determines whether to push down the CTE expression by judging whether the complexity of the current CTE expression and the size of the result set meet the set conditions. If the set conditions are met, the CTE expression is pushed down; otherwise, the CTE is expanded into the main query.

7. The CTE expression optimization method based on the gbase8c distributed database according to claim 1 is characterized in that: The executor performs the following steps: 1) After receiving an execution plan containing a CTE expression, the executor traverses each CTE expression and initializes a producer thread for each CTE expression. It creates a CTE cache block for each CTE expression and initializes a thread lock, two condition variables, and a reference count in the cache block. The first condition controls whether to block the CTE consumer thread from consuming data, and the second condition controls whether to block the CTE producer thread from destroying data. The executor then adds the CTE query plan corresponding to the CTE expression to the CTE producer thread. It traverses the main query plan and, whenever a CTescan operator is found that needs to consume the result set cached by the CTE expression, it increments the reference count in the corresponding CTE cache block by 1 and adds the cache block pointer to the CTescan operator. 2) Before the main query begins executing, all CTE producer threads are started, and then the execution plan of the main query is executed. When the main query calls the ctescan operator, the pthread_cond_wait function is called, and the CTE consumer thread is blocked from consuming data through the first condition until the CTE producer thread calls the pthread_cond_broadcast function to notify all consumers that the CTE subquery has completed. The process of consuming the result set in the CTE cache block is then continued. After the ctescan operator completes, the reference count in the CTE cache block is decremented by 1 through an atomic operation. When the reference count in the CTE cache block is reduced to 0, all CTE consumers have completed execution, and the pthread_cond_signal function is called to notify the CTE producer thread to destroy the data through the second condition. 3) CTE producer execution process: The CTE producer thread executes the corresponding CTE query plan and adds the returned result set to the CTE cache block. After the query is completed and the result set is added to the cache block, the pthread_cond_broadcast function is called to notify all CTE consumers to read the cache block. Subsequently, it is determined whether the reference count is greater than 0. If it is greater than 0, the pthread_cond_wait function is called to block the CTE producer thread through the second condition to destroy the data and wait for all CTE consumers to complete the execution. After all CTE consumers have completed the execution, the last consumer to complete calls the pthread_cond_signal function to notify consumers to call the CTE cache block destruction process to reclaim memory.

8. An electronic device, characterized in that: include: one or more processors; A memory for storing one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors implement the CTE expression optimization method based on the gbase8c distributed database according to claim 1.

9. A computer-readable storage medium, characterized in that: Computer executable instructions are stored, and when the instructions are executed, they are used to implement the CTE expression optimization method based on the gbase8c distributed database as claimed in claim 1.

10. A computer program product, characterized in that: The computer program product includes computer executable instructions, which are used to implement the CTE expression optimization method based on the gbase8c distributed database as claimed in claim 1 when executed.

Images