Adaptive materialization method and apparatus for database, and electronic device

Abstract

An adaptive materialization method for a database, wherein the method is executed by an electronic device. The method comprises: determining a data connection execution plan for a database, wherein the data connection execution plan is used for performing data connection on a driving table and a driven table in the database, the data connection execution plan comprises execution sub-plans sequentially executed for each data record in the driving table, and the execution sub-plan executed each time corresponds to one data record in the driving table (101); selecting one data record from the driving table, executing, for the selected data record and on the driven table, an execution sub-plan for the data connection, and detecting execution information during the execution of the execution sub-plan (102); on the basis of the execution information, performing a materialization operation on the driven table, so as to obtain a materialized table (103); and performing data connection on the driving table and the materialized table, so as to replace the data connection execution plan (104).

Description

Database adaptive materialization methods, devices and electronic equipment

[0001] Related applications

[0002] This application claims priority to Chinese patent application filed on July 11, 2024, with application number 202410928973.5, entitled "Database Adaptive Materialization Method, Apparatus and Electronic Device", the entire contents of which are incorporated herein by reference. Technical Field

[0003] This application relates to database technology, and more particularly to a database adaptive materialization method, apparatus, electronic device, computer-readable storage medium, and computer program product. Background Technology

[0004] A Database Management System (DBMS) is a software system used to create, maintain, manage, and use databases. Users can access and manipulate the data stored in the database through the DBMS. The optimizer and executor are key components of a DBMS. The optimizer is responsible for generating an efficient execution plan based on the query instructions, and the executor is responsible for executing the execution plan generated by the optimizer.

[0005] In the solutions provided by related technologies, materialization operators are fixed in the execution plan by the optimizer. When the optimizer estimates that materialization is not worthwhile, it will not fix the materialization operator in the execution plan. However, due to various factors (such as statistical information, cost models, and various assumptions), the optimization problem of estimation bias is unavoidable. This may lead to the execution plan not increasing the materialization operator when it should, resulting in performance degradation. Summary of the Invention

[0006] This application provides a database adaptive materialization method, apparatus, electronic device, computer-readable storage medium, and computer program product.

[0007] A database adaptive materialization method, executed by an electronic device, includes:

[0008] Determine a data connection execution plan for the database; wherein, the data connection execution plan is used to connect the driving table and the driven table in the database, and the data connection execution plan includes a sub-execution plan that is executed sequentially for each data record in the driving table, and each sub-execution plan corresponds to one data record in the driving table;

[0009] Select a data record from the driving table, execute a sub-execution plan for data connection on the driven table for the selected data record, and detect execution information during the execution of the sub-execution plan.

[0010] Based on the execution information, materialize the driven table to obtain a materialized table; and

[0011] The driving table is joined with the materialized table to replace the data join execution plan.

[0012] A database adaptive materialization device, comprising:

[0013] The determination module is used to determine a data connection execution plan for the database; wherein, the data connection execution plan is used to connect the driving table and the driven table in the database, and the data connection execution plan includes a sub-execution plan that is executed sequentially for each data record in the driving table, and each executed sub-execution plan corresponds to one data record in the driving table;

[0014] The first execution module is used to select a data record from the driving table, execute a sub-execution plan for data connection on the driven table for the selected data record, and detect execution information during the execution of the sub-execution plan.

[0015] The materialization module is used to perform materialization operations on the driven table according to the execution information to obtain a materialized table; and

[0016] The second execution module is used to perform a data connection between the driving table and the materialized table, in order to replace the data connection execution plan.

[0017] This application provides an electronic device, including:

[0018] Memory, used to store executable instructions;

[0019] The processor, when executing executable instructions stored in the memory, implements the database adaptive materialization method provided in this application.

[0020] This application provides a computer-readable storage medium storing executable instructions for inducing a processor to execute and implement the database adaptive materialization method provided in this application.

[0021] This application provides a computer program product including executable instructions for inducing a processor to execute and implement the database adaptive materialization method provided in this application.

[0022] Details of one or more embodiments of this application are set forth in the following drawings and description. Other features, objects, and advantages of this application will become apparent from the specification, drawings, and claims. Attached Figure Description

[0023] To more clearly illustrate the technical solutions in the embodiments of this application, the drawings used in the embodiments will be briefly introduced below. Obviously, the drawings described below are only embodiments of this application. For those skilled in the art, other drawings can be obtained based on the published drawings without creative effort.

[0024] Figure 1 is a schematic diagram of the architecture of a database adaptive materialization system provided in an embodiment of this application;

[0025] Figure 2 is a schematic diagram of a server provided in an embodiment of this application;

[0026] Figure 3A is a schematic diagram of the first process of the database adaptive materialization method provided in the embodiment of this application;

[0027] Figure 3B is a schematic diagram of the second process of the database adaptive materialization method provided in the embodiment of this application;

[0028] Figure 3C is a schematic diagram of the third process of the database adaptive materialization method provided in the embodiments of this application;

[0029] Figure 3D is a schematic diagram of the fourth process of the database adaptive materialization method provided in the embodiments of this application;

[0030] Figure 4 is a first schematic diagram of the data table provided in an embodiment of this application;

[0031] Figure 5 is a second schematic diagram of the data table provided in an embodiment of this application;

[0032] Figure 6 is a third schematic diagram of the data table provided in an embodiment of this application;

[0033] Figure 7 is a fourth schematic diagram of the data table provided in the embodiments of this application;

[0034] Figure 8 is a fifth schematic diagram of the data table provided in the embodiments of this application;

[0035] Figure 9 is a schematic diagram of the execution plan determined by the optimizer of the present application embodiment for unfixed materialization operators;

[0036] Figure 10 is a schematic diagram of actuator adaptive materialization provided in an embodiment of this application;

[0037] Figure 11 is a schematic diagram of implementing nested loop connections without materialization according to an embodiment of this application;

[0038] Figure 12 is a schematic diagram of implementing nested loop connections in the case of materialization provided by an embodiment of this application. Detailed Implementation

[0039] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0040] In the following description, references to "some embodiments" describe a subset of all possible embodiments; however, it is understood that "some embodiments" may be the same subset or different subsets of all possible embodiments and may be combined with each other without conflict. In the following description, the term "a plurality of" means at least two.

[0041] In the following description, the terms "first, second, third" are used merely to distinguish similar objects and do not represent a specific ordering of objects. It is understood that "first, second, third" may be interchanged in a specific order or sequence where permitted, so that the embodiments of this application described herein can be implemented in an order other than that illustrated or described herein.

[0042] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this application belongs. The terminology used herein is for the purpose of describing embodiments of this application only and is not intended to limit this application.

[0043] In this application embodiment, the terms "module" or "unit" refer to a computer program or part of a computer program that has a predetermined function and works with other related parts to achieve a predetermined goal, and can be implemented wholly or partially using software, hardware (such as processing circuitry or memory), or a combination thereof. Similarly, a processor (or multiple processors or memory) can be used to implement one or more modules or units. Furthermore, each module or unit can be part of an overall module or unit that includes the functionality of that module or unit.

[0044] Before providing a further detailed description of the embodiments of this application, the nouns and terms involved in the embodiments of this application will be explained, and the nouns and terms involved in the embodiments of this application shall be interpreted as follows.

[0045] 1) Database: Simply put, a database can be viewed as an electronic filing cabinet—a place to store electronic files, where users can perform operations such as adding, querying, updating, and deleting data. A "database" is a collection of data stored together in a certain way, which can be shared by multiple users, has minimal redundancy, and is independent of application programs.

[0046] A Database Management System (DBMS) is a computer software system designed to manage databases, generally possessing basic functions such as storage, retrieval, security, and backup. DBMSs can be classified according to the database model they support, such as relational or extensible markup language (XML); or according to the type of computer they support, such as server clusters or mobile devices; or according to the query language used, such as Structured Query Language (SQL) or XQuery; or according to performance priorities, such as maximum scale or maximum operating speed; or other classification methods. Regardless of the classification method used, some DBMSs can cross categories, for example, supporting multiple query languages ​​simultaneously.

[0047] 2) Materialization: This refers to the process of loading all or part of the data records from a data table into a temporary file, which can be called a materialized table or materialized view. For example, the executor in a DBMS can implement materialization operations through the temporary tuple storage module.

[0048] Compared to the original data table, data joins based on materialized tables offer higher performance. However, materialization operations themselves incur additional overhead. If the performance improvement brought by materialized tables is insufficient to offset the overhead of materialization operations, then it is not worthwhile to perform materialization operations.

[0049] 3) Optimizer: A key component in a DBMS, responsible for receiving data query instructions and generating an efficient execution plan. In this embodiment, the optimizer needs to evaluate whether to fix materialization operators in the execution plan based on the actual situation. Materialization operators are operators used to trigger materialization operations.

[0050] 4) Executor: A key component in DBMS, responsible for executing the execution plan generated by the optimizer. Specifically, it reads data from the database, performs necessary calculations and related operations, and finally returns the results.

[0051] 5) Execution Plan: This is an internal data access strategy generated by the optimizer that describes how the database engine will execute query operations to obtain the desired data. The execution plan includes, but is not limited to, the following: ① Data read order: the order in which data tables and indexes are accessed; ② Operation type: the specific methods of operations such as full table scan, index scan, sorting, and aggregation; ③ Join method: used in multi-table query scenarios to describe how data join operations are performed between different data tables; ④ Index usage: which indexes will actually be used to speed up queries, or whether a full table scan is necessary for various reasons.

[0052] In the multi-table query scenario of this application embodiment, the data join execution plan does not have a fixed materialized operator. The data join execution plan includes a sub-plan that is executed sequentially for each data record of the driving table. Each execution of the sub-plan corresponds to one data record in the driving table. That is, the number of times the sub-plan is repeatedly executed is equal to the number of rows in the driving table (i.e., the number of data records).

[0053] A sub-plan is a plan that executes sequentially for each data record in the driving table within a data join execution plan. Each execution of the sub-plan corresponds to one data record in the driving table and is used to join one data record in the driving table with all data records in the driven table.

[0054] 6) Data Join: Also known as a data join, this refers to the operation of combining rows (data records) from multiple data tables, which typically share common columns or fields. Data join methods include, but are not limited to, nested loop join, hash join, and merge-sort join. Nested loop join has two loop structures: the outer loop iterates through the driving table, and the inner loop iterates through the driven table, comparing corresponding fields in the two tables to find matching data records. Hash join includes a build phase and a probe phase. In the build phase, a hash table is calculated based on the driving table, and in the probe phase, data records in the driven table are traversed and hash values ​​are calculated to match the hash table. Merge-sort join is used to efficiently perform join operations on multiple sorted or sortable data tables. For example, for multiple sorted data tables, the values ​​on the join columns are compared one by one to generate the final join result.

[0055] 7) Driving Table: Also known as the outer table or outer loop, this is the data table used as the outer loop in data join operations. The driving table drives the query process, using all its data records (or a portion of the data records that meet specific data filtering conditions) as the basis for the loop, and then querying the driven table for matching data records based on each of these data records.

[0056] 8) Driven Table: Also known as inner table or inner layer table, it refers to the data table that is relative to the driving table in data join operations, serving as the inner loop or the data table that needs to be matched and probed.

[0057] 9) Data record: also known as a data row, refers to a row of data in a data table, which contains the values ​​corresponding to all columns (fields) in the data table.

[0058] This application provides a database adaptive materialization method, apparatus, electronic device, computer-readable storage medium, and computer program product, which can adaptively perform materialization during the execution of a data connection execution plan, thereby improving database performance. The following describes exemplary applications of the electronic device provided in this application, which can be implemented as a server.

[0059] Referring to Figure 1, which is a schematic diagram of the architecture of the database adaptive materialization system 100 provided in an embodiment of this application, the terminal device 400 is connected to the server 200 via the network 300, and the server 200 is connected to the database 500. The network 300 can be a wide area network (WAN), a local area network (LAN), or a combination of both. It is worth noting that the server 200 provides the hardware and software environment for the database 500, handles requests and responses to the database 500, controls access permissions to the database 500, and maintains data security and integrity. The database 500, on the other hand, is responsible for storing and managing data, implementing its functions through the server 200.

[0060] In some embodiments, the terminal device 400 sends a data query instruction to the server 200 for the database 500; the server 200 determines a data connection execution plan for the database 500 based on the received data query instruction; wherein, the data connection execution plan is used to connect the driving table and the driven table in the database 500, and the data connection execution plan includes a sub-execution plan executed sequentially for each data record in the driving table, with each sub-execution plan corresponding to one data record in the driving table; the server 200 selects a data record from the driving table, executes the sub-execution plan for data connection on the driven table for the selected data record, and detects execution information during the execution of the sub-execution plan; the server 200 performs a materialization operation on the driven table based on the execution information to obtain a materialized table; the server 200 performs a data connection between the driving table and the materialized table to replace the data connection execution plan; the server 200 sends the data query result to the terminal device 400 as a response to the data query instruction, wherein the data query result is obtained through the data connection operation executed by the server 200. It is worth noting that the operations performed by server 200 in the above process can be implemented through the database management system in server 200.

[0061] In some embodiments, the server 200 can implement the database adaptive materialization method provided in this application embodiment by running a computer program. For example, the computer program can be a native program or software module in an operating system; it can be a native application (APP), i.e., a program that needs to be installed in the operating system to run; it can also be a small program, i.e., a program that only needs to be downloaded to a browser environment to run; or it can be a small program that can be embedded in any APP, and the small program can be controlled by the user to run or close. In short, the above-mentioned computer program can be any form of application, module or plugin.

[0062] In some embodiments, server 200 may be a standalone physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server providing basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, content delivery networks (CDNs), and big data and artificial intelligence platforms. Terminal device 400 may be a smartphone, tablet, laptop, desktop computer, smart speaker, smartwatch, in-vehicle terminal, etc., but is not limited to these. Terminal device 400 and server 200 may be directly or indirectly connected via wired or wireless communication, and this embodiment does not impose any restrictions.

[0063] Referring to Figure 2, which is a schematic diagram of the structure of a server 200 provided in an embodiment of this application, the server 200 shown in Figure 2 includes at least one processor 210, a memory 250, and at least one network interface 220. The various components in the server 200 are coupled together via a bus system 240. It is understood that the bus system 240 is used to implement communication between these components. In addition to a data bus, the bus system 240 also includes a power bus, a control bus, and a status signal bus. However, for clarity, all buses are labeled as bus system 240 in Figure 2.

[0064] The processor 210 can be an integrated circuit chip with signal processing capabilities, such as a general-purpose processor, a digital signal processor (DSP), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The general-purpose processor can be a microprocessor or any conventional processor, etc.

[0065] The memory 250 may be removable, non-removable, or a combination thereof. Exemplary hardware devices include solid-state storage, hard disk drives, optical disk drives, etc. The memory 250 may optionally include one or more storage devices physically located away from the processor 210.

[0066] The memory 250 may include volatile memory or non-volatile memory, or both. The non-volatile memory may be read-only memory (ROM), and the volatile memory may be random access memory (RAM). The memory 250 described in this application embodiment is intended to include any suitable type of memory.

[0067] In some embodiments, memory 250 is capable of storing data to support various operations, examples of which include programs, modules, and data structures or subsets or supersets thereof, as illustrated below.

[0068] Operating system 251 includes system programs for handling various basic system services and performing hardware-related tasks, such as the framework layer, core library layer, driver layer, etc., for implementing various basic business functions and handling hardware-based tasks;

[0069] The network communication module 252 is used to reach other computing devices via one or more (wired or wireless) network interfaces 220, exemplary network interfaces 220 including: Bluetooth, WiFi, and Universal Serial Bus (USB), etc.

[0070] In some embodiments, the database adaptive materialization apparatus provided in this application can be implemented in software. Figure 2 shows a database adaptive materialization apparatus 255 stored in memory 250, which can be software in the form of programs and plug-ins, including the following software modules: a determination module 2551, a first execution module 2552, a materialization module 2553, and a second execution module 2554. These modules are logically related and can therefore be arbitrarily combined or further split according to the functions they implement. The functions of each module will be described below.

[0071] The database adaptive materialization method provided in this application will be described in conjunction with exemplary applications and implementations of the electronic devices provided in the embodiments of this application.

[0072] Referring to Figure 3A, which is a schematic flowchart of a database adaptive materialization method provided in an embodiment of this application, the database adaptive materialization method provided in this application can be implemented by an electronic device, such as a server, and will be described in conjunction with the steps shown in Figure 3A.

[0073] In step 101, a data connection execution plan for the database is determined; wherein, the data connection execution plan is used to connect the driving table and the driven table in the database, and the data connection execution plan includes a sub-execution plan that is executed sequentially for each data record in the driving table, and each sub-execution plan corresponds to one data record in the driving table.

[0074] For example, when a data query instruction for a database is received, a data connection execution plan for the database is determined based on the data query instruction to meet the data query requirements of the data query instruction.

[0075] In this embodiment, the data query instruction is related to multiple data tables in the database. These tables contain the data the data query instruction wants to retrieve. Therefore, the data query scenario involved in this embodiment can also be called a multi-table query scenario. For example, the data query instruction is used to query the score of each student ID in each course, the student name corresponding to each student ID, and the student gender corresponding to each student ID in a class. The database includes a score table and a student table. The fields in the score table include student ID, course number, and score, while the fields in the student table include student ID, student name, and student gender. Both the score table and the student table are related to the data query instruction.

[0076] The aforementioned data join execution plan is used to join the driving table and the driven table in the database. The data join execution plan includes sub-execution plans that are executed sequentially for each data record in the driving table. Each execution of a sub-execution plan corresponds to one data record in the driving table; that is, the sub-execution plan is used to join one data record in the driving table with all data records in the driven table. Both the driving table and the driven table are data tables related to the data query command. How to distinguish between the driving table and the driven table is part of the data join execution plan. Taking the score table and student table as examples, the score table can be designated as the driving table and the student table as the driven table; alternatively, the student table can also be designated as the driving table and the score table as the driven table. The number of driven tables can be one or more, without limitation. It is worth noting that when determining the data join execution plan, the number of data records in the driving table is not yet determined, therefore the number of times the sub-execution plan is repeated is also uncertain.

[0077] It's worth noting that the essence of data joining is matching data records in the driving table with data records in the driven table, and then combining the two matching records to obtain the target data record. In practice, matching can be achieved as follows: Assuming the driving and driven tables are stored in a relational database, when querying using SQL statements, the JOIN operation combined with the ON clause can be used to determine if the target field is equal. For example, for a score table (driving table) and a student table (driven table), with the target field being student ID, the SQL statement can be written as SELECT * FROM score_table JOIN student_table ON score_table.student_id = student_table.student_id. The database engine will automatically perform matching based on this condition. Regarding combination processing, in SQL, the fields to be combined can be specified using the SELECT clause, such as SELECT score_table.student_id, score_table.score, student_table.student_name FROM score_table JOIN student_table ON score_table.student_id = student_table.student_id. The matching process refers to determining whether the data records in the driven table and the data records in the driving table have the same value in the target field (the common field they share). If the values ​​are the same, the match is successful; if the values ​​are different, the match fails. In addition, the combination process can be implemented for all or part of the fields in the two successfully matched data records, depending on the needs of the actual application scenario.

[0078] For example, the score table is the driving table, and the student table is the driven table. A certain data record in the driving table (for ease of explanation, let's call it data record 1) is "student ID-2, course ID-1, score-60", where "student ID-2" refers to the field, and "2" refers to the value under the field, and so on. The driven table includes data record 2 and data record 3, where data record 2 is "student ID-3, student name-Li Si, student gender-female", and data record 3 is "student ID-2, student name-Wang Wu, student gender-male". Then, data record 1 and data record 2 can be joined. During the joining process, data record 1 and data record 2 are matched. Since the values ​​of the target field "student ID" are different between data record 1 and data record 2, the match between data record 1 and data record 2 is considered to have failed. Then, data record 1 and data record 3 can be joined. During the joining process, data record 1 and data record 3 are matched. Since the values ​​of the target field "student ID" are the same between data record 1 and data record 3, the match between data record 1 and data record 3 is considered to have succeeded. Then, data record 1 and data record 3 are combined to obtain the target data record. For example, the target data record can be "student ID-2, course number-1, score-60, student name-Wang Wu, student gender-male". Of course, the fields in the target data record depend on the data query instruction and are not limited to this example. For example, the target data record can also be "student ID-2, course number-1, score-60, student name-Wang Wu". If a data record in the driving table fails to match any data record in the driven table, a target data record may not be generated, or it may be generated only based on that specific data record in the driving table, with missing values ​​marked. For example, the target data record could be "Student ID-2, Course ID-1, Score-60, Student Name-empty, Student Gender-empty". This application does not limit the data connection method used in the data connection execution plan; for example, it could be nested loop join, hash join, sort-merge join, etc.

[0079] In some embodiments, step 101 may be implemented by an optimizer in a database management system.

[0080] In step 102, a data record is selected from the driving table, and a sub-execution plan for data connection is executed on the driven table for the selected data record, and execution information is detected during the execution of the sub-execution plan.

[0081] The data connection execution plan in step 101 is based on estimation and does not have a fixed materialization operator, meaning the data connection execution plan is expected to execute without materialization. However, due to various factors (such as statistical information, cost models, and various assumptions), estimation bias is unavoidable, which may result in the data connection execution plan not having an added materialization operator instead. Therefore, in this embodiment, adaptive materialization is performed based on the execution status of the data connection execution plan.

[0082] For example, a data record is selected from the driving table, and a sub-execution plan for data joining is executed on the driven table for the selected data record. During the execution of this sub-execution plan, execution information is monitored. The cost of executing one sub-execution plan is within an acceptable range, while also providing accurate and fast execution information. Execution information is an indicator related to the driving table and / or the driven table, used to reflect whether the data join execution plan suffers performance degradation due to the lack of fixed materialized operators. For example, execution information may include at least one of the number of data records in the driving table and the percentage of valid data records in the driven table. The number of data records in the driving table is the same as the number of times the sub-execution plan is repeated; therefore, the more data records in the driving table, the higher the execution cost of the data join execution plan. Conversely, the smaller the percentage of valid data records in the driven table, the higher the percentage of meaningless operations during the execution of the data join execution plan (because a larger percentage of invalid data records need to be scanned), and the higher the execution cost of the data join execution plan.

[0083] As an example, this application embodiment provides a schematic diagram of the data table shown in Figure 4. In Figure 4, the driving table is the score table and the driven table is the student table. In step 102, the first data record in the score table can be connected with all data records in the student table.

[0084] In step 103, the driven table is materialized according to the execution information to obtain the materialized table.

[0085] Execution information can accurately reflect whether the data connection execution plan has degraded performance due to the lack of fixed materialized operators. Therefore, when performance degradation is detected, materialization operations can be performed on the driven table to obtain a materialized table. Specifically, materialization operations can be performed on at least some data records in the driven table to obtain a materialized table. Figure 4 takes the materialization operation performed on all data records in the student table as an example to obtain a materialized table.

[0086] In some embodiments, the materialization operation on the driven table based on execution information, as described above, can be implemented to obtain a materialized table as follows: When the number of data records in the driving table is greater than a preset threshold (e.g., 1000 records) and the percentage of valid data records in the driven table is less than a preset percentage threshold (e.g., 20%), the data records in the driven table are preferentially stored in memory in binary format. Memory allocation functions (e.g., malloc) are used to allocate memory space for the data records, and the data records are written sequentially to the allocated memory area to obtain the materialized table in memory. During the storage process, the remaining storage space in memory is obtained through the memory management interface provided by the operating system (e.g., getrusage). Meanwhile, it is assumed that the average size of the remaining data records to be stored in the driven table is S. avg The quantity is N remain Then the remaining storage space S required for the data records to be stored need It can be obtained through formula S need =S avg ×N remain The estimated value is obtained. When the remaining memory storage space is less than the estimated required storage space, the data records in the driven table that have not yet been stored in memory are stored on the disk in the form of files. File operation functions (such as fopen, fwrite) are used to create and write the data to the disk file, thus obtaining the materialized table on the disk.

[0087] Here, considering that memory is more efficient than disk in data retrieval, the data records in the driven table (referring to the data records to be materialized) can be stored in memory first, resulting in a materialized table in memory. When memory storage space is insufficient, the data records in the driven table that have not yet been stored in memory are then stored on disk, resulting in a materialized table on disk.

[0088] If the above materialization operation only involves memory, then the driving table will be joined with the materialized table in memory in the subsequent process; if the above materialization operation involves both memory and disk, then the driving table will be joined with both the materialized table in memory and the materialized table on disk in the subsequent process.

[0089] The above method prioritizes storing data records from the driven table in memory. Since memory is more efficient than disk in data retrieval, it can effectively improve the efficiency of subsequent data connections.

[0090] In step 104, the driving table is data joined with the materialized table to replace the data join execution plan.

[0091] Here, a data join is performed between the driving table and the materialized table to replace the data join execution plan. This allows direct access to data records in the materialized table during the data join process, avoiding multiple scans of the driven table and effectively improving data join efficiency.

[0092] It is worth noting that for a data record in the driving table that has already been data-joined in step 102, there is no need to repeat the data join in step 104. This avoids unnecessary resource waste and improves data join efficiency. As shown in Figure 4, each remaining data record in the fraction table (i.e., each data record except the first data record) is data-joined with all data records in the materialized table. A target data table (i.e., the target data table includes multiple target data records) can be constructed based on the target data records obtained through data join in step 102 and step 104 to serve as a response to data query instructions. For example, the target data table can be used as the data query result and sent to the initiator of the data query instruction.

[0093] In some embodiments, steps 102 to 104 may be implemented by an executor in the database management system.

[0094] In some embodiments, the data join execution plan includes data filtering conditions corresponding to the driven table; the above-mentioned selection of a data record from the driving table and execution of a sub-execution plan for data join on the driven table for the selected data record can be achieved in the following manner: filtering multiple data records in the driven table according to the data filtering conditions corresponding to the driven table to obtain at least one valid data record in the driven table; and performing a data join between one data record in the driving table and all valid data records in the driven table; the above-mentioned materialization operation on the driven table based on execution information to obtain a materialized table can be achieved in the following manner: performing a materialization operation on all valid data records in the driven table based on execution information to obtain a materialized table.

[0095] Data filtering conditions are extracted from data query commands and used to filter data records in a data table. Based on these conditions, data records in the table can be divided into valid and invalid records, and can be set according to the actual application scenario.

[0096] A valid data record is a data record in a data table that satisfies the corresponding data filtering conditions. If the data join execution plan includes data filtering conditions corresponding to the driven table, then the valid data records in the driven table are those that satisfy the filtering conditions; if the data join execution plan includes data filtering conditions corresponding to the driving table, then the valid data records in the driving table are those that satisfy the filtering conditions.

[0097] Here, the data filtering conditions corresponding to the driven table are extracted from the data query command and can be set according to the actual application scenario.

[0098] Next, assuming the data join execution plan includes data filtering conditions corresponding to the driven table, the execution process of one sub-execution plan is described. First, multiple data records in the driven table are filtered according to the data filtering conditions to obtain valid data records in the driven table; that is, valid data records in the driven table satisfy the data filtering conditions, and there must be at least one valid data record in the driven table. Then, a data join is performed between one data record from the driving table (any data record, such as the first data record) and all valid data records in the driven table.

[0099] Since invalid data records in the driven table do not meet the corresponding data filtering conditions and are not the data required by the data query command, materialization is performed only on all valid data records in the driven table to obtain a materialized table. Invalid data records are those that differ from valid data records. This reduces the overhead of the materialization process and the cost of subsequent scanning of the materialized table. Based on the obtained materialized table, each remaining data record in the driving table is joined with all data records in the materialized table. Invalid data records are those in the data table that differ from valid data records, i.e., those that do not meet the corresponding data filtering conditions.

[0100] As an example, this application provides a schematic diagram of the data table shown in Figure 5. Taking the data filtering condition corresponding to the driven table (student table) as male as an example, the filtered student table (containing only valid data records) and the final target data table are shown. It is worth noting that target data records with missing values ​​may also be excluded from the target data table.

[0101] By using the above method, given that the data filtering conditions corresponding to the driven table are clearly defined, multiple data records in the driven table are filtered to obtain at least one valid data record in the driven table. This reduces the amount of data to be processed subsequently, avoids unnecessary resource consumption, and improves processing efficiency.

[0102] In some embodiments, the data join execution plan includes data filtering conditions corresponding to the driving table; the above-mentioned selection of a data record from the driving table and execution of a sub-execution plan for data join on the driven table for the selected data record can be achieved in the following manner: filtering multiple data records in the driving table according to the data filtering conditions corresponding to the driving table to obtain multiple valid data records in the driving table; and joining a valid data record in the driving table with at least a portion of the data records in the driven table; the above-mentioned data join between the driving table and the materialized table can be achieved in the following manner: joining each remaining valid data record in the driving table with all data records in the materialized table.

[0103] Here, the data filtering conditions corresponding to the driving table are extracted from the data query command and can be set according to the actual application scenario.

[0104] Next, assuming the data join execution plan includes data filtering conditions corresponding to the driving table, the execution process of one sub-execution plan is described. First, multiple data records in the driving table are filtered according to the data filtering conditions, resulting in multiple valid data records in the driving table. Then, a valid data record from the driving table (any valid data record, such as the first valid data record) is joined with at least a portion of the data records in the driven table. In the subsequent materialization operation, at least a portion of the data records in the driven table are materialized to obtain a materialized table. Specifically, if the data join execution plan includes data filtering conditions corresponding to the driven table, then at least a portion of the data records in the driven table refers to all valid data records in the driven table; if the data join execution plan does not include data filtering conditions corresponding to the driven table, then at least a portion of the data records in the driven table refers to all data records in the driven table.

[0105] Since invalid data records in the driving table do not meet the corresponding data filtering conditions and are not the data required by the data query command, based on the materialized table, each remaining valid data record in the driving table (i.e., each valid data record that has not been joined) is joined with all data records in the materialized table. Invalid data records refer to data records that are distinct from valid data records.

[0106] As an example, this application provides a schematic diagram of the data table shown in Figure 6. Taking the data filtering condition corresponding to the driving table (score table) as being that the score is greater than or equal to 60 as an example, the filtered score table (including only valid data records) and the final target data table are shown.

[0107] By using the above method, given that the data filtering conditions corresponding to the driving table are clearly defined, multiple data records in the driving table are filtered according to the data filtering conditions to obtain multiple valid data records in the driving table. Each valid data record in the driving table is then used for data connection, thereby improving the accuracy and necessity of data connection and avoiding unnecessary resource consumption.

[0108] In some embodiments, the data join execution plan includes data filtering conditions corresponding to the driving table and data filtering conditions corresponding to the driven table. The above-mentioned selection of a data record from the driving table and execution of a sub-execution plan for data join on the driven table for the selected data record can be achieved in the following manner: filtering multiple data records in the driving table according to the data filtering conditions corresponding to the driving table to obtain multiple valid data records in the driving table; filtering multiple data records in the driven table according to the data filtering conditions corresponding to the driven table to obtain at least one valid data record in the driven table; and joining one valid data record in the driving table with all valid data records in the driven table. The above-mentioned materialization operation on the driven table based on execution information to obtain a materialized table can be achieved in the following manner: materializing all valid data records in the driven table based on execution information to obtain a materialized table. The above-mentioned data join between the driving table and the materialized table can be achieved in the following manner: joining each remaining valid data record in the driving table with all data records in the materialized table.

[0109] Here, if the data connection execution plan includes both the data filtering conditions for the driving table and the data filtering conditions for the driven table, filtering can be performed on the driving table and the driven table separately before subsequent processing.

[0110] As an example, this application provides a schematic diagram of the data table shown in Figure 7. Taking the data filtering condition corresponding to the driven table (student table) as male and the data filtering condition corresponding to the driving table (score table) as a score greater than or equal to 60 as an example, the filtered student table (containing only valid data records), the filtered score table (containing only valid data records), and the final target data table are shown.

[0111] As shown in Figure 3A, in this embodiment, where the materialization operator is not fixed for the database join execution plan, a data record is selected from the driving table. A sub-execution plan for data join is executed once on the driven table for the selected data record. During the execution of this sub-execution plan, execution information is monitored. Executing a sub-execution plan once allows for quick and accurate acquisition of execution information, with the cost remaining within acceptable limits. The obtained execution information accurately reflects whether the data join execution plan has suffered performance degradation due to the lack of a fixed materialization operator. Then, when performance degradation occurs, a materialization operation is performed on the driven table to obtain a materialized table. The driving table and the materialized table are then joined to replace the original data join execution plan. This is equivalent to adaptively adding a materialization operator to the data join execution plan without a fixed materialization operator, thereby compensating for performance degradation, effectively improving database performance, and achieving rapid response.

[0112] In some embodiments, referring to FIG3B, FIG3B is a schematic flowchart of a database adaptive materialization method provided in the embodiments of this application. Step 103 shown in FIG3A can be updated to step 201. In step 201, when the execution information meets the execution pressure condition, the driven table is materialized to obtain a materialized table.

[0113] Here, execution information refers to metrics related to the driving table and / or the driven table. Execution pressure conditions can be pre-set for the execution information. When the execution information meets the execution pressure conditions, it proves that the data connection execution plan has suffered performance degradation due to the lack of fixed materialization operators. The performance optimization brought by materialization can compensate for the materialization overhead. Therefore, materialization operations are performed on the driven table to obtain the materialized table.

[0114] In some embodiments, the execution information includes at least one of the following: the number of data records in the driving table; the percentage of valid data records in the driven table; and the execution pressure conditions include at least one of the following: the number of data records in the driving table is greater than or equal to a quantity threshold; and the percentage of valid data records in the driven table is less than a percentage threshold.

[0115] The quantity threshold is a pre-defined value used to determine whether the number of data records in the driving table has reached a certain scale. When the number of data records in the driving table is greater than or equal to this quantity threshold, the data join execution plan is considered to have performance degradation, and materialization operations may be required to optimize performance. This value can be set based on the database performance and historical query data.

[0116] The percentage threshold is a pre-set percentage value used to determine whether the percentage of valid data records in the driven table is too low. When the percentage of valid data records in the driven table is less than this percentage threshold, it can be considered that the data connection execution plan has performance degradation, and materialization operations may be required to achieve performance optimization. This value can be adjusted according to the actual application scenario.

[0117] Here, the execution information may include at least one of the following: the number of data records in the driving table and the percentage of valid data records in the driven table. The number of data records in the driving table is the same as the number of times the sub-execution plan is repeated. Therefore, the more data records in the driving table, the higher the execution cost of the data join execution plan. Conversely, the smaller the percentage of valid data records in the driven table, the higher the percentage of meaningless operations during the execution of the data join execution plan (because it requires scanning a larger percentage of invalid data records), thus increasing the execution cost of the data join execution plan.

[0118] Based on this, the execution pressure conditions include at least one of the following: the number of data records in the driving table is greater than or equal to a quantity threshold; the proportion of valid data records in the driven table is less than a proportion threshold. Specifically, when the number of data records in the driving table is greater than or equal to the quantity threshold, the sub-execution plan needs to be executed too many times, indicating performance degradation in the data join execution plan. Therefore, materialization is used to optimize performance. When the proportion of valid data records in the driven table is less than the proportion threshold, it is necessary to repeatedly scan the invalid data records that constitute an excessive proportion in the driven table, indicating performance degradation in the data join execution plan. Therefore, materialization is used to optimize performance. It is worth noting that if there is a data filtering condition corresponding to the driving table, then the "number of data records in the driving table" in the execution information can be the "number of valid data records in the driving table," and the "number of data records in the driving table is greater than or equal to the quantity threshold" in the execution pressure conditions can be the "number of valid data records in the driving table is greater than or equal to the quantity threshold."

[0119] In the above approach, the performance degradation of the data join execution plan is measured from the perspectives of both the driving table and the driven table, which ensures the necessity of materialized operations, that is, materialized operations can bring about tangible performance optimization. At the same time, the execution information and execution pressure conditions can be set according to the focus of the actual application scenario, which provides greater flexibility and freedom.

[0120] In Figure 3B, after step 102 shown in Figure 3A, in step 202, when the execution information does not meet the execution pressure conditions, starting from the next unprocessed data record of the driving table, the sub-execution plan is executed one by one for the driving table and the driven table until the data records of the driving table are processed.

[0121] When the execution information does not meet the execution pressure conditions, it proves that the data connection execution plan has not suffered performance degradation due to the lack of fixed materialized operators. The performance optimization brought by materialization may not be able to make up for the consumption of materialization. Therefore, starting from the next unprocessed data record of the driving table, the sub-execution plan is executed for the driving table and the driven table one by one until the data records of the driving table are processed, and until the sub-execution plan has been executed for each data record (or each valid data record) in the driving table. At this time, the data connection execution plan is completed.

[0122] As an example, this application provides a schematic diagram of the data table shown in Figure 8, illustrating the process of repeatedly executing sub-execution plans and the final target data table.

[0123] As shown in Figure 3B, this embodiment compares the detected execution information with the execution pressure conditions to assess whether the data connection execution plan suffers performance degradation due to the lack of fixed materialized operators. When the data connection execution plan suffers performance degradation due to the lack of fixed materialized operators, performance optimization is achieved through materialization. When the data connection execution plan does not suffer performance degradation due to the lack of fixed materialized operators, considering that the performance optimization brought by materialization may not necessarily compensate for the materialization cost, the sub-execution plan is executed one by one for the driving table and the driven table, starting from the next unprocessed data record of the driving table, until the data records of the driving table are processed, ensuring that the data connection execution plan is successfully completed.

[0124] In some embodiments, referring to FIG3C, FIG3C is a schematic flowchart of a database adaptive materialization method provided in the embodiments of this application. Step 101 shown in FIG3A can be implemented by steps 301 to 303, which will be described in conjunction with each step.

[0125] In step 301, a data query instruction for the database is received.

[0126] Here, we receive data query commands for the database, which are related to multiple data tables in the database.

[0127] In step 302, the estimated execution information of the database in response to the data query command is estimated.

[0128] Here, estimations are performed on multiple data tables related to the data query command in the database to estimate the database's execution information when responding to the data query command (i.e., performing a data join operation). Estimated execution information refers to the relevant information obtained by estimating the database's response to the data query command (i.e., performing a data join operation) based on the multiple data tables related to the data query command. Specific details can be found in the execution information section, such as the number of data records in the driving table and the percentage of valid data records in the driven table. However, due to estimation bias, this information may not be accurate.

[0129] In step 303, a data connection execution plan for the database is determined based on the estimated execution information.

[0130] Based on the estimated execution information, the data connection execution plan for the database can be determined. It is worth noting that the data connection execution plan determined in step 303 refers to the execution plan without fixed materialized operators, that is, it includes the sub-execution plan that is executed sequentially for each data record of the driving table.

[0131] In some embodiments, steps 302 and 303 can be implemented using the cost system of the optimizer.

[0132] In some embodiments, the above-mentioned determination of the data join execution plan for the database based on the estimated execution information can be achieved in the following manner: when the estimated execution information does not meet the execution pressure condition, the execution plan using nested loop joins and without fixed materialization operators is determined as the data join execution plan for the database; after estimating the estimated execution information of the database in response to data query instructions, the database adaptive materialization method further includes: when the estimated execution information meets the execution pressure condition, the execution plan using nested loop joins and with fixed materialization operators is determined as the alternative execution plan for the database, or the execution plan using hash joins or sort-merge joins is determined as the alternative execution plan for the database.

[0133] The database adaptive materialization method provided in this application embodiment can be specifically used for nested loop joins to solve the problem of high algorithm complexity when nested loop joins deal with large amounts of data. For example, when the estimated execution information does not meet the execution pressure condition, it means that the amount of data to be processed by the data join is small. The data join algorithm can adopt a nested loop join suitable for processing small amounts of data. At the same time, it means that the execution plan will not suffer performance degradation due to the lack of a fixed materialization operator. Therefore, it is not necessary to fix the materialization operator in the execution plan. Based on this, the execution plan that adopts a nested loop join and does not fix the materialization operator is determined as the data join execution plan for the database, and adaptive materialization is performed through subsequent steps 102 to 104.

[0134] When the estimated execution information meets the execution pressure condition, it indicates that the amount of data to be processed by the data join is large. Therefore, the execution plan using nested loop joins and fixed materialization operators can be determined as an alternative execution plan for the database. This enhances the algorithmic performance of nested loop joins when dealing with large amounts of data through materialization operators. For example, this alternative execution plan includes a sub-execution plan that only needs to be executed once. During the execution of this alternative execution plan, a data record is first selected from the driving table. The sub-execution plan for data join is then executed once on the driven table for the selected data record. Then, a materialization operation is performed on the driven table to obtain a materialized table. Finally, the driving table and the materialized table are joined. It is worth noting that the process of joining the driving table and the materialized table here is also part of this alternative execution plan and is different from step 104 mentioned above.

[0135] When the estimated execution information meets the execution pressure conditions, the execution plan using hash join or sort-merge join can be determined as an alternative execution plan for the database. Compared with nested loop join, hash join and sort-merge join have better performance when dealing with large amounts of data. The alternative execution plan determined in this way can be executed normally.

[0136] In the above approach, for cases where materialization operators should have been added to the execution plan of nested loop joins but were not, performance optimization is achieved through adaptive materialization, thereby solving the problem of high algorithm complexity of nested loop joins when dealing with large amounts of data.

[0137] In some embodiments, the estimation of database execution information in response to data query commands can be achieved as follows: Statistical information of multiple data tables related to the data query command in the database is determined. This statistical information includes the total number of data records in the data tables, high-frequency values, histograms, and correlation coefficients. For high-frequency values, the values ​​of each field in the data tables are traversed, and the frequency of each value is counted. Values ​​with a frequency greater than a preset frequency threshold (e.g., 10% of the total number of records) are considered high-frequency values. For histograms, the value range of a field is divided into several intervals, and the number of data records in each interval is counted to form a histogram. For correlation coefficients, the Pearson correlation coefficient formula is used to calculate the correlation between fields. The Pearson correlation coefficient is equal to the numerator calculated by multiplying the value of the first field minus its mean by the value of the second field minus its mean in each observation from the 1st to the nth observation, and then summing these products. The denominator is the square root of the sum of squares ... For the driving table, analyze the fields involved in the filtering conditions according to the data query command. If the fields involved in the filtering conditions have high-frequency values, count the number of times the high-frequency values ​​appear within the range of the filtering conditions. Assuming that the total number of data records in the driving table statistics is N, the number of times the high-frequency values ​​appear within the range of the filtering conditions is n, and the distribution of the high-frequency values ​​can represent the overall data distribution, then the estimated number of data records in the driving table is N×n / N. 高频值总数 , where N 高频值总数 This represents the total number of occurrences of high-frequency values. For the driven table, the fields involved in the filtering conditions are determined based on the data filtering conditions corresponding to the driven table in the data query command. Using the histogram in the driven table's statistics, the percentage of data records within the filtering condition range is calculated. Simultaneously, this percentage is adjusted based on the correlation coefficient. Assuming the percentage obtained from the histogram is p1 and the correlation coefficient is r, the estimated percentage of valid data records in the driven table is p = p1 × r. The estimated number of data records in the driving table and the percentage of valid data records in the driven table are used as the database's estimated execution information when responding to data query commands.

[0138] Here, the statistical information of multiple data tables related to the data query command in the database is first determined. The specific content of the statistical information is not limited. For example, it can include high-frequency values ​​(values ​​that occur more frequently than a frequency threshold), histograms, and correlation coefficients. Similarly, the timing of the statistical information is not limited. For example, the statistical information can be obtained by periodically processing the data tables in the database, or it can be triggered when the data reaches certain conditions (such as data updates) to ensure that the statistical information is synchronized with the actual situation as much as possible.

[0139] Then, estimation processing is performed based on the data query command and statistical information from multiple data tables to obtain the estimated execution information of the database in response to the data query command. For example, the driving table is determined from multiple data tables based on the data query command, and the number of data records in the driving table is obtained from the statistical information of the driving table as the estimated execution information; and / or, the driven table is determined from multiple data tables based on the data query command, and the proportion of valid data records in the driven table is determined based on the data filtering conditions corresponding to the driven table in the data query command and the statistical information of the driven table as the estimated execution information.

[0140] In the above method, by combining the data query command with the statistical information obtained through statistical processing, the execution status when responding to the data query command can be estimated, thereby making it easier to determine a suitable data connection execution plan.

[0141] As shown in Figure 3C, this embodiment of the application receives a data query instruction for a database, estimates the estimated execution information of the database when responding to the data query instruction, and determines a data connection execution plan for the database based on the estimated execution information. This explains the origin of the data connection execution plan. Since estimation bias is inevitable in the process of determining the data connection execution plan, this embodiment of the application overcomes the estimation bias problem through adaptive materialization to achieve performance optimization.

[0142] In some embodiments, referring to FIG3D, FIG3D is a schematic flowchart of a database adaptive materialization method provided in an embodiment of the present application. After step 103 shown in FIG3A, the materialized table can be updated in step 401 when the update condition is met; wherein, the update condition includes any one of the following: the driven table is detected to be updated; the update cycle has arrived.

[0143] Here, the data join execution plan may need to be executed multiple times. For example, in some scenarios, data needs to be continuously queried from the database. However, the driven table in the database may not be static; for instance, it may be updated at any time, such as adding, deleting, or modifying data records. If the initially obtained materialized table is always used for data joins, the data query results will be inaccurate. To address this, the materialized table is updated when the update conditions are met to ensure the accuracy of the data records in the materialized table. Then, the driving table can be joined with the updated materialized table to obtain the data query results.

[0144] The update condition can be the detection of an update in the driven table. If the data join execution plan includes data filtering conditions corresponding to the driven table, the update condition can also be the detection of an update of a valid data record in the driven table (i.e., a data record that satisfies the data filtering conditions corresponding to the driven table). This increases the necessity of updating the materialized table and avoids unnecessary resource consumption.

[0145] The update condition can also be the arrival of the update cycle, for example, the materialized table can be set to be updated once every 10 seconds.

[0146] In some embodiments, the above-described materialized table update can be achieved by performing any of the following processes: re-materializing the driven table to perform a full update of the materialized table; comparing the driven table and the materialized table to obtain updated data records, and incrementally updating the materialized table based on the updated data records. For a full update, all data records in the original materialized table are cleared. Following the previous materialization operation process, the data records in the driven table (if data filtering conditions exist, they are valid data records) are re-stored in memory or disk, overwriting the original materialized table, thus completing the full update. For incremental updates, a hash table is used for comparison. First, hash tables are constructed using the primary keys of the driven table and the materialized table as keys and the data records as values. Then, the hash table of the driven table is traversed. If the primary key does not exist in the hash table of the materialized table, the data record is identified as the update data record to be added; if the primary key exists but the values ​​of other fields are different, the data record in the driven table is identified as the update data record to be updated. Next, the hash table of the materialized table is traversed. If the primary key does not exist in the hash table of the driven table, the data record is identified as an update record to be deleted. Based on these update records, corresponding delete, add, and update operations are performed on the materialized table to achieve incremental updates. For update records to be deleted, a data record with the same primary key is found in the materialized table and deleted; for update records to be added, they are inserted into the materialized table; for update records to be updated, a data record with the same primary key is found in the materialized table, and the values ​​of its other fields are updated to be the same as the corresponding data record in the driven table.

[0147] Re-materialize the driven table to perform a full update. The specific steps are as follows: Clear all data records in the original materialized table. Following the previous materialization operation procedure, re-store the data records in the driven table (those with data filtering conditions are considered valid data records) to memory or disk, overwriting the original materialized table, thus completing the full update. A full update refers to a method of updating a materialized table, specifically by clearing all data records in the original materialized table, and following the previous materialization operation procedure to re-store the data records in the driven table (those with data filtering conditions are considered valid data records) to memory or disk, overwriting the original materialized table.

[0148] The driven table and the materialized table are compared to obtain updated data records. Incremental updates are then performed on the materialized table based on these updated data records. Specifically, the comparison process involves comparing each row of data records in both tables, using the primary keys as the basis. If a record in the materialized table has a primary key that does not exist in the driven table, that record is identified as a record to be deleted. If a record in the driven table has a primary key that does not exist in the materialized table, that record is identified as a record to be added. If records with the same primary key in both tables differ in other fields, that record in the driven table is identified as a record to be updated. Based on these updated data records, corresponding deletion, addition, and update operations are performed on the materialized table to achieve incremental updates. For updated data records to be deleted, a record with the same primary key is found in the materialized table and deleted. For updated data records to be added, they are inserted into the materialized table. For updated data records to be updated, a record with the same primary key is found in the materialized table, and the values ​​of its other fields are updated to match the corresponding records in the driven table.

[0149] This provides two ways to update the materialized table. The first way is to re-materialize the driven table and overwrite the original materialized table with the latest materialized table, thus achieving a full update of the materialized table.

[0150] The second approach involves comparing the driven table with the materialized table to obtain updated data records, and then incrementally updating the materialized table based on these updated data records. Incremental updates refer to a method of updating the materialized table, specifically by comparing the driven table with the materialized table to obtain updated data records, and then performing corresponding deletion, addition, and update operations on the materialized table based on these updated data records, updating only the portion of the materialized table containing the updated data records. For example, after comparing the driven table and the materialized table, if the materialized table is missing a data record compared to the driven table, this missing record is identified as the update record and added to the materialized table. Similarly, if the materialized table has an added data record compared to the driven table, this added record is identified as the update record and deleted from the materialized table. Furthermore, if after comparing the driven table and the materialized table, two data records with the same value in the target field (for clarity, the record belonging to the driven table is referred to as record A, and the record belonging to the materialized table as record B) have different values ​​in other fields, record A is identified as the update record, and the value in record B is updated to match the value in the update record. Incremental updates improve update efficiency while reducing computational load, thus contributing to improved data query efficiency.

[0151] As shown in Figure 3D, this embodiment updates the materialized table when the update conditions are met, ensuring that the data records in the materialized table are the same as the data records in the driven table, thereby ensuring the accuracy of the data query results. This is applicable to scenarios where the data connection execution plan needs to be executed multiple times.

[0152] The following will describe an exemplary application of the embodiments of this application in a real-world application scenario.

[0153] First, let's introduce data join. When performing data join, databases typically offer three join physical operators to choose from: Nested Loop Join, Hash Join, and Merge-Sort Join. This application's embodiment can optimize the performance of Nested Loop Join.

[0154] The execution process of Nested Loop Join is shown in Figure 8. As can be seen from Figure 8, for each row of data in the driving table (score table), a data scan of the driven table (student table) is required. If the number of rows in the driving table is large, the driven table needs to be scanned multiple times. In this case, materialization (caching) of the driven table can be considered to directly access the data in the materialized table and avoid scanning the driven table multiple times.

[0155] Without materialization, the time complexity of Nested Loop Join is O(MN), where M represents the number of rows in the driving table and N represents the number of rows in the driven table. With the materialization operator, the time complexity of Nested Loop Join is O(N+(M-1)K)+O(K), where K represents the cost of scanning the materialized table, and O(K) is the time cost of materializing K records in the driven table.

[0156] Let's take a scenario where both the driving table and the driven table have data filtering conditions as an example. For the driving table, if the number of rows M remaining after filtering is small, materialization is unnecessary; if the number of rows M remaining after filtering is large, materialization can be considered. For the driven table, assuming it originally has 10,000 records, if 9,000 records are filtered out using the corresponding data filtering conditions, leaving 1,000 records (i.e., valid records), then only these 1,000 records are actually valid when scanning the driven table. Therefore, materializing these 1,000 records can be considered. If 1,000 records are filtered out using the corresponding data filtering conditions, leaving 9,000 records, then materialization is not necessary because the overhead of directly accessing the 10,000 records in the driven table is roughly the same as accessing the 9,000 records in the materialized table.

[0157] In related technical solutions, the optimizer typically determines whether to perform materialization operations on the driven table through estimation. If materialization is required, the materialization operator is fixed in the execution plan for the executor to execute. However, this method often selects an incorrect execution plan due to estimation errors; for example, the execution plan that should have added a materialization operator may not have it, leading to performance degradation. To address this, the embodiments of this application improve performance through adaptive materialization.

[0158] Adaptive materialization is achieved through the cooperation of the optimizer and the executor. Given that the optimizer has determined the execution plan for the unmaterialized operators, the executor can determine whether to adaptively materialize based on the actual execution information of the execution plan. As an example, this application provides a schematic diagram of the optimizer determining the execution plan for the unmaterialized operators, as shown in Figure 9, and a schematic diagram of the executor adaptively materializing, as shown in Figure 10.

[0159] Next, the adaptive materialization method will be explained in detail. As an example, this application embodiment provides a schematic diagram of nested loop connections as shown in Figure 11. For execution plans without fixed materialization operators, the sub-plans may be executed repeatedly, and the cost of executing the sub-plans can be very high. As shown in Figure 11, the filtered driving table includes 10,000 valid data records, and the driven table includes 1,000,000 data records, requiring 10,000 sub-plans to be executed. In each execution of a sub-plan, the driven table needs to be scanned once, that is, the driven table needs to be scanned 10,000 times, which leads to very high consumption. However, the filtered driven table only includes 10 valid data records. If the materialization operation is performed on these 10 valid data records to obtain a materialized table, then there is no need to repeatedly execute the sub-plans, but only to directly access the materialized table multiple times, which can greatly reduce the consumption, as shown in Figure 12.

[0160] Adaptive materialization can be achieved using an adaptive materialization operator (or Virtual Materialization operator). Compared to traditional fixed materialization operators, adaptive materialization operators can determine whether materialization operations are needed based on actual execution information, and perform materialization operations to obtain a materialization table when necessary. The adaptive materialization operator (Virtual Materialization operator) is an operator created by the optimizer that can determine whether materialization operations are needed based on actual execution information, and perform materialization operations to obtain a materialization table when necessary. Compared to traditional fixed materialization operators, it can overcome the estimation bias problem faced by the optimizer.

[0161] Adaptive materialization operators are created by the optimizer, which can determine whether to create them through parameter control. Specific parameters may include those related to execution stress conditions, such as thresholds for the number of valid data records in the driving table and the percentage of valid data records in the driven table. Upon receiving a data query command, the optimizer first estimates the database's execution information in response, including the number of valid data records in the driving table and the percentage of valid data records in the driven table. If the estimated number of valid data records in the driving table is greater than or equal to a preset threshold, or the percentage of valid data records in the driven table is less than a preset percentage threshold, the execution stress condition is considered met, and the optimizer creates the adaptive materialization operator. For example, the threshold can be set based on database performance and historical query data. First, the average number of valid data records in the driving table in historical queries is calculated. The average is equal to the sum of the number of valid data records in the driving table from the 1st to the nth table, divided by n. Then, the standard deviation is calculated. The standard deviation is equal to the sum of the squares of the sum of the squares of the number of valid data records in the driving table from the 1st to the nth table, divided by n minus 1, and then the square root is taken. Set the quantity threshold to the average plus twice the standard deviation; the percentage threshold can be set to 50%, or it can be adjusted according to the actual application scenario.

[0162] For example, an execution pressure condition can be set, which includes at least one of the following: 1) the number of valid data records in the driving table is greater than or equal to a quantity threshold; 2) the percentage of valid data records in the driven table is less than a percentage threshold. The quantity threshold can be set based on database performance and historical query data. For example, by statistically analyzing the average and standard deviation of the number of valid data records in the driving table in historical queries, the quantity threshold can be set to the average plus twice the standard deviation, such as a quantity threshold of 1000 calculated from the statistical results. The percentage threshold can be set to 50%, or it can be adjusted according to the actual application scenario. When the optimizer receives a data query instruction and the estimated execution information does not meet the execution pressure condition, the optimizer concludes that it is not worthwhile to perform materialization operations on the driven table. However, since the optimizer inevitably has estimation bias, to avoid incorrect conclusions due to estimation bias, the optimizer creates an adaptive materialization operator in the execution plan and hands the execution plan over to the executor for execution.

[0163] The executor receives an execution plan including adaptive materialization operators and determines whether to perform adaptive materialization under the influence of these operators. For example, the executor first executes a sub-plan from the execution plan on both the driving table and the driven table, and checks the execution information during this sub-plan execution. Then, the executor determines whether the execution information meets the execution pressure condition. If the execution information meets the execution pressure condition, the executor performs materialization operations on all valid data records in the driven table to obtain a materialized table, and performs a data join between each remaining valid data record in the driving table and all valid data records in the materialized table. If the execution information does not meet the execution pressure condition, the executor continues to repeat the sub-plan execution.

[0164] This application's embodiments overcome the estimation bias problem faced by the optimizer through adaptive materialization, effectively improving the performance of Nested Loop Join, especially when dealing with large amounts of data, enabling fast processing, rapid response to data query commands, and improved user experience.

[0165] The following continues to describe an exemplary structure of the database adaptive materialization device 255 provided in this application embodiment as a software module. In some embodiments, as shown in FIG2, the software module stored in the database adaptive materialization device 255 in the memory 250 may include: a determining module 2551, used to determine a data connection execution plan for the database; wherein, the data connection execution plan is used to connect the driving table and the driven table in the database, and the data connection execution plan includes a sub-execution plan executed sequentially for each data record of the driving table, and each executed sub-execution plan corresponds to a data record in the driving table; a first execution module 2552, used to select a data record from the driving table, execute the sub-execution plan for data connection on the driven table once for the selected data record, and detect execution information during the execution of the sub-execution plan; a materialization module 2553, used to perform a materialization operation on the driven table according to the execution information to obtain a materialized table; and a second execution module 2554, used to connect the driving table and the materialized table to replace the data connection execution plan.

[0166] In some embodiments, the materialization module 2553 is further configured to: when the execution information meets the execution pressure conditions, perform materialization operations on the driven table to obtain a materialized table; the database adaptive materialization device 255 further includes a third execution module, configured to: when the execution information does not meet the execution pressure conditions, start from the next unprocessed data record of the driving table, and execute sub-execution plans for the driving table and the driven table one by one until the data records of the driving table are processed.

[0167] In some embodiments, the execution information includes at least one of the following: the number of data records in the driving table; the percentage of valid data records in the driven table; and the execution pressure conditions include at least one of the following: the number of data records in the driving table is greater than or equal to a quantity threshold; and the percentage of valid data records in the driven table is less than a percentage threshold.

[0168] In some embodiments, the determining module 2551 is further configured to: receive a data query instruction for the database; estimate the estimated execution information of the database in response to the data query instruction; and determine a data connection execution plan for the database based on the estimated execution information.

[0169] In some embodiments, the determining module 2551 is further configured to: when the estimated execution information does not meet the execution pressure conditions, determine the execution plan that uses nested loop connections and has no fixed materialization operator as the data connection execution plan for the database; the database adaptive materialization device 255 further includes a non-target module, configured to: when the estimated execution information meets the execution pressure conditions, determine the execution plan that uses nested loop connections and has a fixed materialization operator as the alternative execution plan for the database, or determine the execution plan that uses hash connections or sort-merge connections as the alternative execution plan for the database.

[0170] In some embodiments, the determining module 2551 is further configured to: determine the statistical information of multiple data tables in the database related to the data query instruction; perform estimation processing based on the data query instruction and the statistical information of the multiple data tables to obtain the estimated execution information of the database in response to the data query instruction.

[0171] In some embodiments, the data connection execution plan includes data filtering conditions corresponding to the driven table; the first execution module 2552 is further configured to: filter multiple data records in the driven table according to the data filtering conditions corresponding to the driven table to obtain at least one valid data record in the driven table; and perform a data connection between one data record in the driving table and all valid data records in the driven table; the materialization module 2553 is further configured to: perform materialization operations on all valid data records in the driven table according to the execution information to obtain a materialized table.

[0172] In some embodiments, the data connection execution plan includes data filtering conditions corresponding to the driving table; the first execution module 2552 is further configured to: filter multiple data records in the driving table according to the data filtering conditions corresponding to the driving table to obtain multiple valid data records in the driving table; and perform data connection between one valid data record in the driving table and at least some data records in the driven table; the second execution module 2554 is further configured to: perform data connection between each remaining valid data record in the driving table and all data records in the materialized table.

[0173] In some embodiments, the database adaptive materialization apparatus 255 further includes an update module for: updating the materialized table when update conditions are met; wherein the update conditions include any one of the following: detecting an update of the driven table; or the update cycle has arrived.

[0174] In some embodiments, the update module is further configured to: perform any of the following processes: re-materialize the driven table to perform a full update of the materialized table; compare the driven table with the materialized table to obtain updated data records, and perform incremental updates of the materialized table based on the updated data records.

[0175] In some embodiments, the materialization module 2553 is further configured to: store the data records in the driven table into memory according to the execution information to obtain a materialized table in memory; when the memory storage space is insufficient, store the data records in the driven table that have not yet been stored into memory into the disk to obtain a materialized table on the disk.

[0176] This application provides a computer program product or computer program that includes executable instructions stored in a computer-readable storage medium. A processor of an electronic device reads the executable instructions from the computer-readable storage medium and executes the executable instructions, causing the electronic device to perform the database adaptive materialization method described above in this application.

[0177] This application provides a computer-readable storage medium storing executable instructions, wherein the executable instructions are stored and, when executed by a processor, will cause the processor to execute the database adaptive materialization method provided in this application.

[0178] In some embodiments, the computer-readable storage medium may be a memory such as FRAM, ROM, PROM, EPROM, EEPROM, flash memory, magnetic surface memory, optical disk, or CD-ROM; or it may be a variety of devices including one or any combination of the above-mentioned memories.

[0179] In some embodiments, executable instructions may take the form of a program, software, software module, script, or code, written in any form of programming language (including compiled or interpreted languages, or declarative or procedural languages), and may be deployed in any form, including as a standalone program or as a module, component, subroutine, or other unit suitable for use in a computing environment.

[0180] As an example, executable instructions may, but do not necessarily, correspond to files in a file system. They may be stored as part of a file that holds other programs or data, for example, in one or more scripts in a Hyper Text Markup Language (HTML) document, in a single file dedicated to the program in question, or in multiple collaborating files (e.g., a file that stores one or more modules, subroutines, or code sections).

[0181] As an example, executable instructions can be deployed to execute on a single electronic device, or on multiple electronic devices located in one location, or on multiple electronic devices distributed across multiple locations and interconnected via a communication network.

[0182] In summary, this application provides a database adaptive materialization method, apparatus, electronic device, computer-readable storage medium, and computer program product. The electronic device first determines a data join execution plan for the database. This plan is used to join a driving table and a driven table in the database, and includes a sub-execution plan executed sequentially for each data record in the driving table, with each sub-execution plan corresponding to one data record in the driving table. Next, a data record is selected from the driving table, and the sub-execution plan for data joining is executed once on the driven table for that record, while monitoring execution information during execution. Then, based on the execution information, a materialization operation is performed on the driven table to obtain a materialized table. Finally, the driving table and the materialized table are joined to replace the original data join execution plan. By executing a sub-execution plan once, execution information can be obtained quickly and accurately with low resource consumption. This information can accurately reflect whether the original execution plan suffered performance degradation due to the lack of a fixed materialization operator. When performance degradation occurs, materialization operations are performed and the driving table and the materialized table are joined. This is equivalent to dynamically adding materialization operators to the original execution plan, which effectively compensates for performance degradation, significantly improves the efficiency of database data connection processing, reduces query response time, and improves resource utilization.

[0183] Furthermore, when materializing the driven table to obtain a materialized table based on execution information, the materialization operation is only performed on the driven table when the execution information meets the execution pressure condition. This mechanism makes the materialization operation more targeted, avoiding the resource waste caused by indiscriminate materialization. Materialization is only triggered when the execution information shows that the original execution plan has performance degradation and the materialization operation can bring significant performance optimization. From a technical perspective, this precise materialization decision can improve the effective utilization of system resources, reduce unnecessary computation and storage overhead, and thus improve the overall operating efficiency of the database system.

[0184] When the execution information does not meet the execution pressure conditions, the sub-execution plan is executed one by one for the driving table and the driven table, starting from the next unprocessed data record in the driving table, until all data records in the driving table have been processed. This approach avoids unnecessary materialization when it does not bring performance improvements, ensuring that data joins are completed in the most optimized way under different execution conditions. From a technical perspective, it reduces system overhead, improves resource utilization efficiency, guarantees the smooth completion of the execution plan, and avoids resource waste and performance degradation.

[0185] Execution pressure conditions include at least one of the following: the number of data records in the driving table is greater than or equal to a quantity threshold, or the percentage of valid data records in the driven table is less than a percentage threshold. Measuring the performance degradation of the data join execution plan from both the driving and driven table perspectives allows for a comprehensive and accurate assessment of the necessity of materialized operations. When the number of data records in the driving table is too large, the number of repeated executions of the sub-execution plan increases, leading to a significant increase in execution costs. Conversely, if the percentage of valid data records in the driven table is too small, a large number of meaningless scan operations will occur during the data join process, increasing execution costs. By setting reasonable quantity and percentage thresholds, it is possible to ensure that materialized operations bring tangible performance optimizations. Furthermore, these thresholds can be flexibly adjusted according to actual application scenarios, improving the system's adaptability and flexibility.

[0186] Execution information includes at least one of the following: the number of data records in the driving table, or the percentage of valid data records in the driven table. This execution information accurately reflects the execution cost of the data join execution plan. The more data records in the driving table, the more times the sub-execution plan is executed, and the higher the execution cost of the data join execution plan. Conversely, the smaller the percentage of valid data records in the driven table, the more invalid data records need to be scanned during the data join process, further increasing the execution cost. By detecting this key execution information, it is possible to better determine whether materialization operations are needed, providing accurate decision-making basis for adaptive materialization and improving the system's intelligence level.

[0187] When determining the execution plan for a database connection, the process first involves receiving the database query command, then estimating the database's execution information in response to the query command, and finally determining the execution plan based on this estimated information. Combining the query command and the estimated execution information to determine the execution plan allows for prediction of the execution behavior in response to the query command, facilitating the selection of a suitable execution plan. However, estimation bias is unavoidable due to the potential influence of various factors such as statistical information and cost models. Adaptive materialization can effectively overcome this estimation bias, ensuring performance optimization under different conditions and improving system stability and reliability.

[0188] When determining the data join execution plan for the database based on estimated execution information, if the estimated execution information does not meet the execution pressure conditions, the execution plan using nested loop joins without fixed materialization operators will be selected as the data join execution plan for the database. This approach is suitable for scenarios where the data join requires processing a small amount of data. Using nested loop joins without fixed materialization operators can reduce unnecessary overhead. Subsequent adaptive materialization optimizes performance, avoiding fixing materialization operators unnecessarily, thus improving the flexibility and adaptability of the execution plan. In scenarios with small data volumes, nested loop joins can complete the data join at a lower cost, while adaptive materialization provides a remedy for potential performance degradation, ensuring that the system can operate efficiently under different data volumes.

[0189] When the estimated execution information meets the execution pressure conditions, the execution plan using nested loop joins and fixed materialization operators is determined as the alternative execution plan for the database. With large data volumes, nested loop joins have high algorithmic complexity, significantly impacting performance. By using fixed materialization operators, the algorithmic performance of nested loop joins under large data volumes can be improved. Materialization operations can cache data in the driven table, avoiding multiple scans of the driven table, thereby significantly reducing the execution cost of data joins, improving data join efficiency under large data volumes, and enhancing the overall system performance.

[0190] Execution plans employing hash joins or sort-merge joins can also be identified as alternative execution plans for the database. Hash joins and sort-merge joins offer superior performance when dealing with large datasets. Hash joins accelerate data matching by building hash tables, while sort-merge joins improve join efficiency by sorting the data. Selecting an appropriate execution plan based on estimated execution information fully leverages the advantages of different join algorithms, improves data join performance under varying data volumes, and allows the system to flexibly adjust execution strategies according to actual conditions, enhancing the system's adaptability and scalability.

[0191] When estimating the database's execution information in response to a data query command, the statistical information of multiple data tables related to the query command is first determined. Then, estimation processing is performed based on the query command and the statistical information of these tables to obtain the estimated execution information. Combining the query command and statistical information allows for a more accurate prediction of the execution situation. Statistical information, such as the total number of data records in a data table, high-frequency values, histograms, and correlation coefficients, reflects the characteristics and data distribution of the data tables. Analyzing and processing this information allows for a more precise estimation of the number of data records in the driving table and the proportion of effective data records in the driven table, providing a reliable basis for determining an appropriate execution plan and improving the system's decision-making accuracy.

[0192] When the data join execution plan includes data filtering conditions corresponding to the driven table, a data record is selected from the driving table. For this selected record, a sub-execution plan for data joining is executed on the driven table. First, multiple data records in the driven table are filtered according to the filtering conditions to obtain at least one valid data record. Then, this data record from the driving table is joined with all valid data records in the driven table. Finally, materialization operations are performed on all valid data records in the driven table based on the execution information to obtain a materialized table. This approach reduces the amount of data processed subsequently, avoids processing invalid data records, and thus reduces the overhead of materialization operations and the cost of scanning the materialized table. From a technical perspective, it improves the targeting and efficiency of data processing, reduces the waste of system resources, and enhances overall processing efficiency.

[0193] When the data join execution plan includes data filtering conditions corresponding to the driving table, a data record is selected from the driving table. For this selected record, a sub-execution plan for data joining is executed on the driven table. First, multiple data records in the driving table are filtered according to the filtering conditions, resulting in multiple valid data records. Then, one valid data record in the driving table is joined with at least a portion of the data records in the driven table. Finally, each remaining valid data record in the driving table is joined with all data records in the materialized table. By filtering the driving table and performing join operations only on valid data records, the accuracy and necessity of data joins are improved, avoiding resource waste caused by joining invalid data records. This improves the quality and efficiency of data joins, reduces unnecessary computational and storage overhead, and enables the system to process data more efficiently.

[0194] When update conditions are met, the materialized table is updated. These conditions include either detecting an update in the driven table or the update cycle arriving. In scenarios where the data join execution plan needs to be executed multiple times, the driven table may be updated. If the initially obtained materialized table is consistently used for data joins, inaccurate data query results will result. By setting update conditions and updating the materialized table promptly, the accuracy of data records in the materialized table can be ensured, guaranteeing the reliability of data query results. This improves the system's real-time performance and data consistency, enhancing its adaptability to dynamic data environments.

[0195] When updating a materialized table, any of the following processes can be performed: re-materialize the driven table to perform a full update of the materialized table; or compare the driven table and the materialized table to obtain updated data records, and then incrementally update the materialized table based on these updated data records. A full update ensures the integrity and accuracy of the materialized table data and is suitable for situations where the driven table data changes significantly. An incremental update, by comparing the driven table and the materialized table, only updates the changed data records, improving update efficiency and reducing computational load. Choosing the appropriate update method based on the actual situation helps improve data query efficiency, enabling the system to efficiently update the materialized table under different data change conditions, ensuring data timeliness and accuracy.

[0196] When materializing the driven table based on execution information, the data records in the driven table are stored in memory, resulting in a materialized table in memory. If memory is insufficient, the data records in the driven table not yet stored in memory are stored on disk, resulting in a materialized table on disk. Since memory is far more efficient than disk in data retrieval, prioritizing storage in memory effectively improves the efficiency of subsequent data joins. Using disk storage when memory is insufficient ensures the smooth execution of materialization operations and avoids operation failures due to insufficient memory. This hybrid storage approach fully utilizes the advantages of both memory and disk, improving the system's storage efficiency and data processing capabilities.

[0197] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0198] The embodiments described above are merely illustrative of several implementation methods of this application, and while the descriptions are relatively specific and detailed, they should not be construed as limiting the scope of the invention patent. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this patent application should be determined by the appended claims.

Claims

1. A database adaptive materialization method, executed by an electronic device, comprising: Determine a data connection execution plan for the database; wherein, the data connection execution plan is used to connect the driving table and the driven table in the database, and the data connection execution plan includes a sub-execution plan that is executed sequentially for each data record in the driving table, and each sub-execution plan corresponds to one data record in the driving table; Select a data record from the driving table, execute a sub-execution plan for data connection on the driven table for the selected data record, and detect execution information during the execution of the sub-execution plan. Based on the execution information, materialize the driven table to obtain a materialized table; and The driving table is joined with the materialized table to replace the data join execution plan.

2. The method according to claim 1, wherein performing a materialization operation on the driven table based on the execution information to obtain a materialized table comprises: When the execution information meets the execution pressure conditions, a materialization operation is performed on the driven table to obtain a materialized table.

3. The method according to claim 2, further comprising: When the execution information does not meet the execution pressure condition, starting from the next unprocessed data record in the driving table, the sub-execution plan is executed one by one for the driving table and the driven table until the data records in the driving table are processed.

4. The method according to claim 2 or 3, wherein the execution pressure conditions include: The number of data records in the driving table is greater than or equal to a quantity threshold, or the proportion of valid data records in the driven table is less than a proportion threshold, at least one of these conditions must be met.

5. The method according to any one of claims 1 to 4, wherein the execution information includes: The number of data records in the driving table, or the percentage of valid data records in the driven table, are at least one of the following:

6. The method according to any one of claims 1 to 4, wherein determining the data connection execution plan for the database comprises: Receive a data query instruction for the database; Estimate the estimated execution information of the database when responding to the data query command; Based on the estimated execution information, a data connection execution plan for the database is determined.

7. The method according to claim 6, wherein determining the data connection execution plan for the database based on the estimated execution information comprises: When the estimated execution information does not meet the execution pressure conditions, the execution plan that uses nested loop connections and does not have fixed materialized operators will be determined as the data connection execution plan for the database.

8. The method according to claim 6 or 7, further comprising: When the estimated execution information meets the execution pressure condition, the execution plan with nested loop connections and fixed materialization operators will be determined as the alternative execution plan for the database.

9. The method according to claim 6 or 7, further comprising: The execution plans that employ hash joins or sort-merge joins are identified as alternative execution plans for the database.

10. The method according to any one of claims 6 to 9, wherein estimating the database's estimated execution information in response to the data query instruction comprises: Determine the statistical information of multiple data tables in the database that are related to the data query instruction; Based on the data query command and the statistical information of the multiple data tables, estimation processing is performed to obtain the estimated execution information of the database when responding to the data query command.

11. The method according to any one of claims 1 to 10, wherein the data connection execution plan includes data filtering conditions corresponding to the driven table; The step of selecting a data record from the driving table and executing a sub-execution plan for data joining on the driven table for the selected data record includes: Based on the data filtering conditions corresponding to the driven table, multiple data records in the driven table are filtered to obtain at least one valid data record in the driven table. Connect one data record in the driving table with all valid data records in the driven table; The step of performing a materialization operation on the driven table based on the execution information to obtain a materialized table includes: Based on the execution information, materialize all valid data records in the driven table to obtain a materialized table.

12. The method according to any one of claims 1 to 10, wherein the data connection execution plan includes data filtering conditions corresponding to the driving table; The step of selecting a data record from the driving table and executing a sub-execution plan for data joining on the driven table for the selected data record includes: Based on the data filtering conditions corresponding to the driving table, multiple data records in the driving table are filtered to obtain multiple valid data records in the driving table; Connect a valid data record in the driving table to at least a portion of the data records in the driven table; The step of connecting the driving table and the materialized table includes: Connect each remaining valid data record in the driving table to all data records in the materialized table.

13. The method according to any one of claims 1 to 12, wherein the method further comprises: When the update conditions are met, the materialization table is updated; The update conditions include either detecting an update to the driven table or the arrival of an update cycle.

14. The method of claim 13, wherein updating the materialization table comprises: Perform any of the following processes: The driven table is re-materialized to perform a full update on the materialized table; The driven table is compared with the materialized table to obtain updated data records, and the materialized table is incrementally updated based on the updated data records.

15. The method according to any one of claims 1 to 14, wherein performing a materialization operation on the driven table based on the execution information to obtain a materialized table comprises: Based on the execution information, the data records in the driven table are stored in memory to obtain a materialized table in memory; When the memory storage space is insufficient, the data records in the driven table that have not yet been stored in memory are stored in the disk to obtain the materialized table in the disk.

16. A database adaptive materialization device, comprising: The determination module is used to determine a data connection execution plan for the database; wherein, the data connection execution plan is used to connect the driving table and the driven table in the database, and the data connection execution plan includes a sub-execution plan that is executed sequentially for each data record in the driving table, and each executed sub-execution plan corresponds to one data record in the driving table; The first execution module is used to select a data record from the driving table, execute a sub-execution plan for data connection on the driven table for the selected data record, and detect execution information during the execution of the sub-execution plan. The materialization module is used to perform materialization operations on the driven table according to the execution information to obtain a materialized table; and The second execution module is used to perform a data connection between the driving table and the materialized table, in order to replace the data connection execution plan.

17. An electronic device comprising: Memory, used to store executable instructions; A processor, when executing executable instructions stored in the memory, implements the method according to any one of claims 1 to 15.

18. A computer-readable storage medium storing executable instructions for implementing the method of any one of claims 1 to 15 when executed by a processor.

19. A computer program product comprising executable instructions, which, when executed by a processor, implement the method of any one of claims 1 to 15.

Images