CN115455057A - Execution method of database connection operation, storage medium and computer device - Google Patents
Execution method of database connection operation, storage medium and computer device Download PDFInfo
- Publication number
- CN115455057A CN115455057A CN202211152695.6A CN202211152695A CN115455057A CN 115455057 A CN115455057 A CN 115455057A CN 202211152695 A CN202211152695 A CN 202211152695A CN 115455057 A CN115455057 A CN 115455057A
- Authority
- CN
- China
- Prior art keywords
- sub
- connection
- database
- query
- execution plan
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
- G06F16/24553—Query execution of query operations
- G06F16/24558—Binary matching operations
- G06F16/2456—Join operations
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Mathematical Physics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides an execution method of database connection operation, a storage medium and computer equipment. Wherein the method comprises the following steps: determining an executor of a database to start executing a preselected actual execution plan, wherein the actual execution plan is used for defining a connection sequence and a connection mode of a connection operation, two sub-tables on two sides of the connection operation comprise a first sub-table and a second sub-table, and the second sub-table comprises sub-queries; scanning data of the first sub-table, and acquiring data returned by scanning to obtain a first result set; saving values in columns in the first sub-table related to the join operation as supplemental filter conditions; filtering the data tables participating in the sub-query of the second sub-table by using the supplementary filtering condition, and performing the sub-query by using the filtered data to obtain a second result set; and connecting the second result set with the second result set to obtain a connection result. According to the scheme of the invention, the execution time of the connection operation is saved by reducing the complexity of the sub-query.
Description
Technical Field
The present invention relates to database technologies, and in particular, to a method for executing database connection operations, a storage medium, and a computer device.
Background
Join queries are the most dominant queries in relational databases. Connection is a major feature of the relational database model and is a mark that distinguishes it from other types of database systems.
The connection of the database to the plurality of tables generally adopts a cost-based method to select the connection sequence and the connection mode. The accuracy of this cost-based selection depends on the algorithm that samples the data and calculates the cost. The connection algorithm commonly adopted by the existing database product comprises loop nesting, hash connection, merging and sequencing connection and the like. To improve the efficiency of the join, techniques such as bitmap filters are also available at the database executor to assist in the execution of these join algorithms.
However, the above connection method has a limited optimization effect for some specific usage scenarios, for example, the complex sub-query is a time-consuming operation, and the total SQL execution time is greatly affected by the complex sub-query Q. If the join operation involves a complex sub-query, join query execution time is greatly affected by the complex sub-query Q, and an execution plan selected by the existing join algorithm is generally low in efficiency, resulting in a decrease in overall execution efficiency.
Disclosure of Invention
An object of the present invention is to provide a method, a storage medium, and a computer device for performing a database join operation, which at least solve the technical problems of any of the above aspects.
A further object of the present invention is to achieve efficiency enhancement specifically for the scenario of join operations.
It is a further object of this invention to avoid the processing impact of sub-queries on the execution plan.
In particular, the present invention provides a method for performing a database join operation, which includes:
determining that an executor of a database starts to execute a preselected actual execution plan, wherein the actual execution plan is used for defining a connection sequence and a connection mode of connection operation, two side sub-tables of the connection operation comprise a first sub-table and a second sub-table, an estimated return row number of the first sub-table is smaller than a preset base number estimation threshold value, and the second sub-table contains sub-queries;
scanning data of the first sub-table, and acquiring data returned by scanning to obtain a first result set;
saving values in columns in the first sub-table associated with join operations as supplemental filter conditions;
filtering the data tables participating in the sub-query of the second sub-table by using the supplementary filtering condition, and performing the sub-query by using the filtered data to obtain a second result set;
and connecting the second result set with the second result set to obtain a connection result.
Optionally, the step of determining that the executor of the database starts executing the pre-selected actual execution plan comprises:
acquiring an execution identifier of an actual execution plan; when the execution flag is in the start state, it is determined that the execution of the actual execution plan is started by the actuator.
Alternatively, in the case of execution of the status identified as in progress, the step of filtering the data tables of the sub-queries participating in the second sub-table using the supplemental filter criteria is performed directly.
Optionally, the step of scanning the data of the first sub-table comprises:
sequentially scanning each piece of data of the first sub-table according to the query condition of the first sub-table; and is provided with
After the first set of results is obtained, the first set of results is cached.
Optionally, after the step of caching the first result set, the method further includes:
and modifying the execution identification of the actual execution plan into an ongoing state.
Optionally, the step of filtering the data tables participating in the sub-query of the second sub-table using the supplemental filter condition comprises:
reading records of the data tables participating in the sub-query of the second sub-table one by one;
determining whether the read record relates to a record having a column associated with a join operation;
if so, the filter conditions of the column are replaced with the supplemental filter conditions and filtering is performed.
Optionally, before the step of determining that the executor of the database starts executing the pre-selected actual execution plan, the method further includes:
initiating, by a database optimizer, optimization of a join operation;
judging whether the two side sub-tables participating in the connection operation meet preset optimization conditions or not;
if so, an actual execution plan is created.
Optionally, after the step of creating the actual execution plan, the method further includes:
and respectively calculating the connection cost of the alternative queues comprising the actual execution plan by the optimizer, and selecting the actual execution plan if the connection cost of the actual execution plan is minimum.
According to another aspect of the present invention, there is also provided a machine-readable storage medium having stored thereon a machine-executable program which, when executed by a processor, implements a method of performing any of the above-described database join operations.
According to yet another aspect of the present invention, there is also provided a computer device comprising a memory, a processor and a machine-executable program stored on the memory and running on the processor, and the processor, when executing the machine-executable program, implements the method of performing the database join operation of any of the above.
The execution method of the database connection operation aims at the connection operation of a first sub-table and a second sub-table containing sub-query, wherein the estimated return line number is smaller than the preset cardinal number estimation threshold value, the return result of the first sub-table is used as a constant parameter to replace the connection condition of the two tables, the connection condition is used as a filtering condition to be transmitted into the sub-query, so that the complexity of the sub-query is reduced, and the execution efficiency of the connection operation is improved by reducing the number of records participating in operation in the complex query.
Further, according to the method for executing the database connection operation, the optimization condition is that the estimated return line number of the first sub-table is smaller than the preset cardinal number estimation threshold value, the second sub-table contains the sub-query, and the estimated execution cost is larger than the preset execution cost threshold value, the specific scene of the connection operation of the database is identified, the efficiency is improved in a targeted manner, and the optimization of the database is realized.
The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments thereof taken in conjunction with the accompanying drawings.
Drawings
Some specific embodiments of the invention will be described in detail hereinafter, by way of illustration and not limitation, with reference to the accompanying drawings. The same reference numbers in the drawings identify the same or similar elements or components. Those skilled in the art will appreciate that the drawings are not necessarily drawn to scale. In the drawings:
FIG. 1 is a schematic diagram of a method of performing a database join operation according to one embodiment of the invention;
FIG. 2 is a diagram illustrating an execution plan data structure generated by a method for executing a database join operation according to one embodiment of the invention;
FIG. 3 is a diagram illustrating processes performed in a method for performing database join operations, in accordance with one embodiment of the present invention;
FIG. 4 is a flowchart illustrating the sub-table optimization in a method of performing database join operations, in accordance with one embodiment of the present invention;
FIG. 5 is a workflow diagram of an optimizer in a method of performing a database join operation according to one embodiment of the invention;
FIG. 6 is a schematic diagram of a machine-readable storage medium according to one embodiment of the invention; and
FIG. 7 is a schematic diagram of a computer device according to one embodiment of the invention.
Detailed Description
For multi-table join, the database optimizer may select different join algorithms based on different usage scenarios. The method of this embodiment is directed to a usage scenario in which a small table T and a complex sub-query Q are connected, where the small table is a data table with a small data size, and may refer to a node with a small data size, which is queried after filtering by a filtering condition, such as a data table with a small number, and also, for example, a result of connecting one sub-query or multiple tables. While the complex sub-query is a time-consuming operation, the total SQL join operation execution time is greatly affected by the complex sub-query Q.
The implementation principle of the embodiment is as follows: and creating an alternative execution plan, wherein the alternative execution plan takes the execution result of the small table as a constant parameter to replace the connection condition of the two tables, and the connection condition is taken as a filtering condition to be transmitted into the complex sub-query, so that the complexity of the complex sub-query is reduced, and the execution time of the sub-query is improved.
An SQL (Structured Query Language) statement is used as an example for introduction: select from a floor exists (select from b side select from c) v floor v.id = a.id.
For a particular test database, the v.id = a.id results scanned for table a have only 8 rows, whereas the scan results for tables B and C have 200 ten thousand rows, with the majority of SQL execution being used on a merge (APPEND) deduplication (HashAggregate) operation on the 200 ten thousand data. If the a.id in the connection condition v.id = a.id is first queried and then converted into the values of the id columns in the 8 rows of data, then the values of the id columns in the 8 rows of data are passed into the sub-query and finally pushed down to b.id in (8 id values in a), c.id in (8 id values in a), the number of records scanned from tables B and C will be small.
In fact, queries that ultimately satisfy select from B w here in (select from A) from C w here in (select from A) will return only one row of results.
The execution method of the database join operation of the embodiment is used for optimizing a join algorithm at the level of a database optimizer. When an execution plan is generated for the SQL query statement connected by the two tables, a new connection algorithm is generated.
FIG. 1 is a diagram illustrating a method for performing database join operations according to one embodiment of the present invention
The method of performing database join operations may generally include:
step S102, determining that an executor of a database starts to execute a preselected actual execution plan, wherein the actual execution plan is used for defining a connection sequence and a connection mode of connection operation, two side sub-tables of the connection operation comprise a first sub-table and a second sub-table, an estimated return line number of the first sub-table is smaller than a preset cardinality estimation threshold value, and the second sub-table comprises sub-queries. That is, the two sub-tables participating in the join operation may be denoted as a first sub-table (small table T) and a second sub-table (complex sub-query Q), and the preset optimization conditions may include: the estimated return line number of the first sub-table is smaller than a preset cardinality estimation threshold value, and the second sub-table contains sub-queries and the estimated execution cost is larger than a preset execution cost threshold value.
Step S104, scanning the data of the first sub-table, and acquiring the data returned by scanning to obtain a first result set;
step S106, saving the value in the column related to the connection operation in the first sub-table as a supplementary filtering condition;
step S108, filtering the data table participating in the sub-query of the second sub-table by using the supplementary filtering condition, and performing the sub-query by using the filtered data to obtain a second result set;
and step S110, connecting the second result set with the second result set to obtain a connection result.
According to the method, aiming at the connection operation of the first sub-table with the estimated return row number smaller than the preset base number estimation threshold value and the second sub-table containing the sub-query, the return result of the first sub-table is used as a constant parameter to replace the connection condition of the two tables, the connection condition is used as a filtering condition to be transmitted into the sub-query, so that the complexity of the sub-query is reduced, and the execution efficiency of the connection operation is improved by reducing the number of records participating in operation in the complex query.
When the step of determining that the executor of the database starts executing the pre-selected actual execution plan is executed, an execution identifier (start) of the actual execution plan may be first obtained; when the execution flag is in the start state (start = true), it is determined that the executor starts executing the actual execution plan. The execution state is indicated by executing a setting that identifies a start. In case the execution is identified as the ongoing state (start = false), the step of filtering the data table participating in the sub-query of the second sub-table using the supplemental filtering condition may then be directly performed, that is, in case the first sub-table has been scanned completely, step S108 is directly performed.
The step of scanning the data of the first sub table at step S104 may include: sequentially scanning each piece of data of the first sub-table according to the query condition of the first sub-table; and after obtaining the first set of results, caching the first set of results. After the step of caching the first result set, the execution identifier of the actual execution plan is modified to be in an ongoing state, that is, start = false is set.
The step of filtering the data tables participating in the sub-query of the second sub-table using the supplementary filtering condition at step S108 may include: reading records of the data tables participating in the sub-query of the second sub-table one by one; determining whether the read record relates to a record having a column associated with a join operation; if so, the filter conditions of the column are replaced with the supplemental filter conditions and filtering is performed.
FIG. 2 is a diagram illustrating an execution plan data structure generated by an execution method of a database join operation according to an embodiment of the present invention. The join plan has two child nodes, the left node is the execution plan of the sub-query v, the right node is the execution plan of the small table A (here, it is assumed that the full-table scan SeqScan is used).
Fig. 3 is a schematic diagram of an execution process in an execution method of a database join operation according to an embodiment of the present invention, and fig. 4 is a flowchart diagram of sub-table optimization in the execution method of the database join operation according to an embodiment of the present invention. The execution process of the actuator comprises the following steps:
step S402, the executor starts to execute an execution plan;
step S404, determining whether the execution is the first execution, by determining an execution identifier start of the FilterJoin, and if start = true, that is, when the execution identifier indicates a start state, determining that the executor starts executing the actual execution plan.
Step S406, if the execution is the first time, scanning each record of the small table A in sequence, executing a filtering condition, and caching the obtained result; that is, the executor first executes the scan execution plan filterjoin- > righttree circularly in the small table a. One record is returned per execution. For each record, the returned record is first stored in list X.
Step S408, storing the values in the small table A and the related column of the connection condition in a list in a memory; for example, the number of ID columns stored in the execution plan filejoin- > inerrcols list according to the connection condition a.id = v.id is pushed down to the lower layer of the sub-plan filejoin- > lefttree in the form of "ID = value" of the value of the ID column of the record for use in the scanning operation for B, C. And circulating execution until all records in the A return, and then setting start = false, namely modifying the execution identifier into an on state. The execution state is indicated by executing a setting that identifies a start.
Step S410, executing the execution plan of the sub-query B to obtain a filtered record;
and step S420, connecting the records obtained by the sub-query B according to the connection condition and each piece of cached data in the table A, and if the connection completion condition is met, returning a connection result.
The execution process of step S410 is shown in fig. 4, and may include:
step S412, starting to execute the execution plan of the sub-query V;
step S414, reading records of the data tables participating in the sub-query of the second sub-table one by one;
step S416, filtering the read record, if the column of the data table relates to the connection condition, replacing the column name of the table A in the connection condition with the value of the column in the A in the cache, judging whether the read record meets the condition, and if not, reading the next record of the table and continuing to judge;
in step S418, after the records read from each data table satisfy the connection condition, the records are used to perform other operations of the sub-query. The execution plan is an Append merge that is performed by first reading the records of table B, then reading the records of table C, and finally merging the two. Other operations may still follow the original logic.
In reading each record of table B or table C, a number of filter terms added as a result of the first step are added in addition to the original filter terms. These filter conditions will reduce the number of records returned by scanning tables B, C, thereby further reducing the number of records participating in the appendix operation.
That is, the above-mentioned execution process may be: scanning the data of the first sub-table to obtain the data returned by scanning to obtain a first result set (namely a set of records returned by scanning of the small table A); saving the values in the column of the first sub-table related to the join operation as a supplementary filtering condition; filtering the data tables participating in the sub-query of the second sub-table by using a supplementary filtering condition, and performing the sub-query by using filtered data (namely, replacing the column name of the table A in the connection condition with the value of the column in the A in the cache and then judging the record) to obtain a second result set; and connecting the second result set with the second result set to obtain a connection result.
When the connection is executed, the query of the small table is executed firstly, the query result is cached, then the connection condition is converted into the query condition of the large table to filter the large table, and finally the query result of the large table is connected with the cached query result of the small table, so that the number of records participating in operation in complex query can be reduced, the execution efficiency of SQL is greatly improved, and the execution time of SQL query is reduced.
Optimization of the join operation may also be initiated by the database optimizer before the executor begins executing the pre-selected steps of the actual execution plan, adding the join algorithm provided by the present embodiment to the alternative queue for selection.
That is, before the step of determining that the executor of the database starts executing the pre-selected actual execution plan, the method further comprises: initiating, by a database optimizer, optimization of a join operation; judging whether the two side sub-tables participating in the connection operation meet preset optimization conditions or not; if so, an actual execution plan is created. After the step of creating the actual execution plan, the optimizer may further calculate connection costs for the candidate queues including the actual execution plan, respectively, and if the connection cost of the actual execution plan is the minimum, the actual execution plan is selected.
FIG. 5 is a workflow diagram of an optimizer in a method of performing a database join operation, according to one embodiment of the invention. Taking the database as an example of executing the connection a join B, the physical optimization module of the optimizer performs radix estimation and cost estimation on two sub-tables (table a and table B) participating in the connection, enumerates the supported connection algorithms (including a loop nesting algorithm, hash connection, sort merge connection, and the like) based on the estimated data, calculates the execution cost of each connection algorithm, and finally selects the algorithm with the optimal cost. One of the main improvements of this embodiment is: when the optimizer enumerates the connection algorithm, if it is determined that the two tables participating in the connection satisfy the following conditions, the improved connection algorithm provided by this embodiment is added when the connection algorithm is enumerated. The optimizer execution of the database may include the following steps:
and step S502, entering an optimizer, and starting to optimize the connection operation.
Step S504, judge whether the two side sub-tables participating in the connection operation meet the preset optimization condition; optimizing condition requirements: the estimated return row number of the sub-table A is smaller than a radix estimation threshold value row _ threshold; the sub-table B is a sub-query, and the estimated execution cost is greater than the execution cost threshold value, cost _ threshold.
In the case where the cardinality estimation threshold value row _ threshold is set to 10 and the execution cost threshold value cost _ threshold is set to 1, if it occurs: table a, which estimates row number row =8 knot row _threshold; and for the pair sub-query, the estimated cost =7> -cost_threshold; the above-described optimization condition is considered to be satisfied.
In step S506, a connection plan (named FilterJoinPath) is created, and its execution cost is calculated. The execution cost comprises the sum of the following partial costs:
(1) Cost of scanning table A
(2) And (4) materializing the data returned by the table A and establishing the cost of cache. The cost of this step is the extra cost compared with the existing connection algorithm, and the cost of this step can be denoted as cost X.
(3) And pushing the return data in the A as a filtering condition down to the sub-query B, and then calculating the cost of B execution. Because an additional filtering condition is introduced, most records can be filtered out by scanning the simple table in B, so that the query efficiency of B is improved, and the saved execution cost is recorded as Y. When Y is greater than X, the method of this embodiment will greatly improve the execution efficiency of the SQL join operation, and along with the degree that Y is greater than X, the execution efficiency is gradually improved.
(4) And taking the returned data of the B as an outer table and the cached data of the A as an inner table, and performing the execution cost of loop nested connection.
And step S508, adding the FilterJoinPath into an alternative queue, comparing the alternative queue with the execution cost of other plans, and selecting the execution plan with the minimum cost. The pseudo code of this step is:
add _ path (joint _ neighbor _ path (a, b)); increasing a loop nesting algorithm execution plan;
add _ path (join _ create _ hash _ path (a, b)); adding a Hash connection algorithm execution plan;
add _ path (join _ create _ mergejoin _ path (a, b)); increasing a sequencing merging connection algorithm execution plan;
add _ path (join _ create _ filejoin _ path (a, b)); adding the connection algorithm provided by the method of the embodiment;
set _ cheapest (joinrel); selecting the optimal algorithm from the above 4 connection algorithms as the final execution plan.
In step S510, the process proceeds to the executor.
When the optimizer selects the alternative queue provided by the embodiment as the final connection algorithm according to the cost, a final execution plan data structure is generated.
The embodiment also provides a machine-readable storage medium and a computer device. Fig. 6 is a schematic diagram of a machine-readable storage medium 60 according to one embodiment of the invention, and fig. 7 is a schematic diagram of a computer device 70 according to one embodiment of the invention.
The machine-readable storage medium 60 has stored thereon a machine-executable program 61, and the machine-executable program 61 implements an execution method of the database connection operation of any of the above embodiments when executed by a processor.
The computer device 70 may include a memory 720, a processor 710, and a machine-executable program 61 stored on the memory 720 and running on the processor 710, and the processor 710 when executing the machine-executable program 61 implements the execution method of the database connection operation of any of the embodiments described above.
It should be noted that the logic and/or steps shown in the flowcharts or otherwise described herein, such as an ordered listing of executable instructions that can be considered to implement logical functions, can be embodied in any machine-readable storage medium for use by or in connection with an instruction execution system, apparatus, or device, such as a computer-based system, processor-containing system, or other system that can fetch the instructions from the instruction execution system, apparatus, or device and execute the instructions.
For the purposes of this description, a machine-readable storage medium 60 can be any means that can contain, store, communicate, propagate, or transport the program for use by or in connection with the instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of the computer-readable medium would include the following: an electrical connection (electronic device) having one or more wires, a portable computer diskette (magnetic device), a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber device, and a portable compact disc read-only memory (CDROM). Additionally, the computer-readable medium 40 may even be paper or another suitable medium upon which the program is printed, as the program can be electronically captured, via for instance optical scanning of the paper or other medium, then compiled, interpreted or otherwise processed in a suitable manner if necessary, and then stored in a computer memory.
It should be understood that portions of the present invention may be implemented in hardware, software, firmware, or a combination thereof. In the above embodiments, various steps or methods may be implemented in software or firmware stored in a memory and executed by a suitable instruction execution system.
The computer device 70 may be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smartphone. In some examples, computer device 70 may be a cloud computing node. Computer device 70 may be described in the general context of computer system-executable instructions, such as program modules, being executed by a computer system. Generally, program modules may include routines, programs, objects, components, logic, data structures, etc. that perform particular tasks or implement particular abstract data types. Computer device 70 may be practiced in distributed cloud computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed cloud computing environment, program modules may be located in both local and remote computer system storage media including memory storage devices.
Computer device 70 may include a processor 710 adapted to execute stored instructions, a memory 720 that provides temporary storage for the operation of the instructions during operation. Processor 710 may be a single core processor, a multi-core processor, a computing cluster, or any number of other configurations. Memory 720 may include Random Access Memory (RAM), read only memory, flash memory, or any other suitable storage system.
Processor 710 may be connected via a system interconnect (e.g., PCI-Express, etc.) to an I/O interface (input/output interface) suitable for connecting computer device 70 to one or more I/O devices (input/output devices). The I/O devices may include, for example, a keyboard and a pointing device, wherein the pointing device may include a touchpad or a touchscreen, among others. The I/O devices may be built-in components of the computing device 70 or may be devices that are externally connected to the computing device.
Processor 710 may also be linked through a system interconnect to a display interface suitable for connecting computer device 70 to a display device. The display device may include a display screen as a built-in component of the computer device 70. The display device may also include a computer monitor, television, or projector, etc. externally connected to the computer device 70. In addition, a Network Interface Controller (NIC) may be adapted to connect computer device 70 to a network via a system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as an internet small computer system interface, etc.) to transfer data. The network may be a cellular network, a radio network, a Wide Area Network (WAN), a Local Area Network (LAN), the internet, or the like. The remote device may be connected to the computing device through a network.
The flowcharts provided in this example are not intended to indicate that the operations of the method are to be performed in any particular order, or that all of the operations of the method are to be included in each case. Further, the method may include additional operations. Additional variations on the above-described method are possible within the scope of the technical ideas provided by the method of this embodiment.
Thus, it should be appreciated by those skilled in the art that while a number of exemplary embodiments of the invention have been illustrated and described in detail herein, many other variations or modifications consistent with the principles of the invention may be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Accordingly, the scope of the invention should be understood and interpreted to cover all such other variations or modifications.
Claims (10)
1. A method for executing database connection operation comprises the following steps:
determining that an executor of the database starts to execute a preselected actual execution plan, wherein the actual execution plan is used for defining a connection sequence and a connection mode of the connection operation, two side sub-tables of the connection operation comprise a first sub-table and a second sub-table, an estimated return line number of the first sub-table is smaller than a preset cardinality estimation threshold value, and the second sub-table contains a sub-query;
scanning the data of the first sub-table, and acquiring the data returned by scanning to obtain a first result set;
saving values in columns in the first sub-table related to the join operation as supplemental filter conditions;
filtering the data tables participating in the sub-query of the second sub-table by using the supplementary filtering condition, and performing the sub-query by using the filtered data to obtain a second result set;
and connecting the second result set with the second result set to obtain a connection result.
2. The method for performing a database join operation according to claim 1, wherein the step of determining that the executor of the database starts executing the pre-selected actual execution plan comprises:
acquiring an execution identifier of the actual execution plan; and when the execution identifier is in a starting state, determining that the actuator starts executing the actual execution plan.
3. The method of claim 2, wherein the database join operation is performed in a distributed manner
And if the execution identifier is in an on-going state, directly executing the step of filtering the data tables participating in the sub-query of the second sub-table by using the supplementary filtering condition.
4. The method of performing a database join operation of claim 1, wherein the step of scanning the data of the first sub-table comprises:
sequentially scanning each piece of data of the first sub-table according to the query condition of the first sub-table; and is provided with
After the first result set is obtained, caching the first result set.
5. The method of claim 4, wherein the step of caching the first result set further comprises:
and modifying the execution identification of the actual execution plan into an ongoing state.
6. The method of claim 1, wherein the step of filtering the data tables participating in the sub-query of the second sub-table using the supplemental filter criteria comprises:
reading records item by item for the data tables participating in the sub-query of the second sub-table;
determining whether the read record relates to a record having a column associated with the join operation;
and if so, replacing the filter conditions of the column by the supplementary filter conditions and filtering.
7. The method of claim 6, wherein prior to the step of determining that the database executor begins executing the pre-selected actual execution plan, further comprising:
initiating, by the database optimizer, optimization of join operations;
judging whether the two side sub-tables participating in the connection operation meet preset optimization conditions or not;
and if so, creating the actual execution plan.
8. The method of performing a database join operation according to claim 7, further comprising, after the step of creating the actual execution plan:
and respectively calculating connection costs for the alternative queues comprising the actual execution plan by the optimizer, and selecting the actual execution plan if the connection cost of the actual execution plan is minimum.
9. A machine-readable storage medium having stored thereon a machine-executable program which, when executed by a processor, implements a method of performing a database connection operation according to any one of claims 1 to 8.
10. A computer device comprising a memory, a processor and a machine-executable program stored on the memory and running on the processor, and the processor when executing the machine-executable program implements a method of performing the database join operation of any of claims 1 to 8.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211152695.6A CN115455057A (en) | 2022-09-21 | 2022-09-21 | Execution method of database connection operation, storage medium and computer device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211152695.6A CN115455057A (en) | 2022-09-21 | 2022-09-21 | Execution method of database connection operation, storage medium and computer device |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN115455057A true CN115455057A (en) | 2022-12-09 |
Family
ID=84304630
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202211152695.6A Pending CN115455057A (en) | 2022-09-21 | 2022-09-21 | Execution method of database connection operation, storage medium and computer device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN115455057A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116401266A (en) * | 2023-06-08 | 2023-07-07 | 北京四维纵横数据技术有限公司 | Dynamic filtering method and device for distributed database, computer equipment and medium |
-
2022
- 2022-09-21 CN CN202211152695.6A patent/CN115455057A/en active Pending
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116401266A (en) * | 2023-06-08 | 2023-07-07 | 北京四维纵横数据技术有限公司 | Dynamic filtering method and device for distributed database, computer equipment and medium |
| CN116401266B (en) * | 2023-06-08 | 2023-09-05 | 北京四维纵横数据技术有限公司 | Dynamic filtering method and device for distributed database, computer equipment and medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10025822B2 (en) | Optimizing execution plans for in-memory-aware joins | |
| US10817514B2 (en) | Splitting of a join operation to allow parallelization | |
| US7676450B2 (en) | Null aware anti-join | |
| US7111025B2 (en) | Information retrieval system and method using index ANDing for improving performance | |
| US7814091B2 (en) | Multi-tiered query processing techniques for minus and intersect operators | |
| CN108804554B (en) | Database query method, database query device, server and storage medium | |
| US8812492B2 (en) | Automatic and dynamic design of cache groups | |
| US20060020579A1 (en) | System and method for graceful degradation of a database query | |
| US9836505B2 (en) | Star and snowflake join query performance | |
| CN102541960A (en) | Method and device of fuzzy retrieval | |
| CN109299101B (en) | Data retrieval method, device, server and storage medium | |
| CN115455057A (en) | Execution method of database connection operation, storage medium and computer device | |
| US20130144815A1 (en) | Making predictions regarding evaluation of functions for a database environment | |
| CN108549666B (en) | Data table sorting method, device, equipment and storage medium | |
| CN106844415B (en) | Data processing method and device in spark SQL system | |
| CN116595044A (en) | Optimization method, storage medium and equipment for database selectivity calculation | |
| CN116467310A (en) | Lock-free marking method for invalid index, storage medium and computer equipment | |
| CN114969046A (en) | Hash connection processing method, storage medium and equipment | |
| CN115481148A (en) | Optimization method of database connection operation, storage medium and computer equipment | |
| CN107562872B (en) | SQL-based query method and device for measuring spatial data similarity | |
| CN115048409A (en) | Execution method of database connection operation, storage medium and computer device | |
| EP3951609A1 (en) | Query optimization method and apparatus | |
| CN114153874A (en) | Radix estimation method, device, electronic equipment and storage medium | |
| US6421657B1 (en) | Method and system for determining the lowest cost permutation for joining relational database tables | |
| CN117093611B (en) | Database combined index suggestion processing method, storage medium and computer device |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |