CN115269611B

CN115269611B - Method, device, equipment and readable medium for connecting multiple tables of database

Info

Publication number: CN115269611B
Application number: CN202211172823.3A
Authority: CN
Inventors: 熊仲健; 王国平; 郑振国
Original assignee: Beijing Oceanbase Technology Co Ltd
Current assignee: Beijing Oceanbase Technology Co Ltd
Priority date: 2022-09-26
Filing date: 2022-09-26
Publication date: 2022-12-27
Anticipated expiration: 2042-09-26
Also published as: CN115269611A

Abstract

The embodiment of the specification discloses a method, a device, equipment and a readable medium for connecting a plurality of tables of a database. The scheme comprises the following steps: acquiring a plurality of tables to be connected; enumerating the connection paths of the tables by using a preset initial step length as a current step length based on an iterative dynamic programming enumeration algorithm to obtain the real-time connection path enumeration number of the current layer of the current wheel; determining interval identification information of a plurality of preset control intervals in which the real-time connection path enumeration number falls; determining a step length adjusting mode for adjusting the current step length based on the interval identification information, and adjusting the current step length according to the step length adjusting mode to obtain the adjusted step length; continuously performing iterative dynamic planning enumeration on the lower layer or the lower wheel of the current layer of the current wheel based on the adjusted step length until a complete connection sequence of the tables is obtained; concatenating the plurality of tables based on the complete concatenation order.

Description

Method, device, equipment and readable medium for connecting multiple tables of database

Technical Field

The present application relates to the field of computer technologies, and in particular, to a method, an apparatus, a device, and a readable medium for connecting multiple tables of a database.

Background

When the table in the database is subjected to the connection operation, the overall execution time of the connection operation comprises the time for planning and obtaining the connection sequence of the database table and the time for performing the specific connection operation on the table according to the connection sequence.

For example, although the dynamic planning method can theoretically generate the connection sequence with the minimum time spent on specific connection operations and has high planning quality, because the dynamic planning method enumerates all possible connection sequences and then selects the connection sequence with the minimum time spent on the specific connection operations, the planning enumeration time of the dynamic planning method is possibly long but the specific planning execution time is very short, so that the overall execution time of the dynamic planning method is possibly long; although the heuristic algorithm can generate the connection order of the table faster, it often cannot generate a better connection order, so that although the plan enumeration time is short, the time taken to perform a specific connection operation according to the generated connection order is long, the plan quality is low, and the overall execution time may also be long.

Therefore, it is necessary to provide a method for determining the connection order of a plurality of tables in a database, which is efficient from the viewpoint of the overall execution time, and which is compatible with the plan enumeration time and the plan generation quality.

Disclosure of Invention

The embodiment of the specification provides a method, a device, equipment and a readable medium for connecting a plurality of tables in a database, and provides a method for determining a connection sequence of the plurality of tables in the database with high efficiency from the perspective of overall execution time.

In order to solve the above technical problem, the embodiments of the present specification are implemented as follows:

an embodiment of the present specification provides a method for linking multiple tables in a database, including:

acquiring a plurality of tables to be connected;

enumerating the connection paths of the tables by using a preset initial step length as a current step length based on an iterative dynamic programming enumeration algorithm to obtain the real-time connection path enumeration number of the current layer of the current wheel;

determining interval identification information of a plurality of preset control intervals in which the real-time connection path enumeration number falls;

determining a step length adjusting mode for adjusting the current step length based on the interval identification information, and adjusting the current step length according to the step length adjusting mode to obtain the adjusted step length;

continuously performing iterative dynamic planning enumeration on the lower layer or the lower wheel of the current layer of the current wheel based on the adjusted step length until a complete connection sequence of the tables is obtained;

concatenating the plurality of tables based on the complete concatenation order.

An apparatus for connecting a plurality of tables in a database provided in an embodiment of the present specification includes:

the table acquisition module is used for acquiring a plurality of tables to be connected;

a connection path number obtaining module, configured to enumerate connection paths of the multiple tables based on an iterative dynamic programming enumeration algorithm by using a preset initial step length as a current step length, and obtain a real-time connection path enumeration number of a current layer of a current round;

the interval identification information determining module is used for determining the interval identification information of which the real-time connection path enumeration number falls into a plurality of preset control intervals;

the step length adjusting module is used for determining a step length adjusting mode for adjusting the current step length based on the interval identification information, and adjusting the current step length according to the step length adjusting mode to obtain the adjusted step length;

an enumeration module, configured to continue iterative dynamic planning enumeration on a lower layer or a lower round of the current layer of the current round based on the adjusted step length until a complete connection order of the multiple tables is obtained;

a table connection module to connect the plurality of tables based on the complete connection order.

at least one processor; and (c) a second step of,

a memory communicatively coupled to the at least one processor; wherein, the first and the second end of the pipe are connected with each other,

the memory stores instructions executable by the at least one processor to cause the at least one processor to:

acquiring a plurality of tables to be connected;

Embodiments of the present specification provide a computer readable medium having stored thereon computer readable instructions executable by a processor to implement a method of concatenating a plurality of tables of a database.

One embodiment of the present description can achieve at least the following advantages:

the technical scheme of the embodiment of the specification adopts a plurality of control intervals to control the adjustment mode of the step length, and the step length can be adaptively adjusted according to the number of the connection paths of the current layer of the current round enumerated in real time, so that on one hand, the generation time of the whole plan can be prevented from being influenced by the severe expansion of the number of the connection paths caused by the large-scale complete iteration of a single layer, and on the other hand, the quality reduction of the generated connection plan can be prevented from being influenced by the large-scale pruning operation, and therefore, the two factors of the generation time and the generation quality of the multi-table connection plan are considered, and the method for determining the connection sequence of the multiple tables in the database with higher overall efficiency from the overall execution time perspective is provided.

Drawings

In order to more clearly illustrate the embodiments of the present disclosure or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly described below, it is obvious that the drawings in the following description are only some embodiments described in the present application, and for those skilled in the art, other drawings can be obtained according to the drawings without any creative effort.

FIG. 1 is a flow chart of a method for linking a plurality of tables of a database according to an embodiment of the present disclosure;

fig. 2 is a schematic diagram of an iterative process of concatenating multiple tables based on an iterative dynamic programming enumeration algorithm IDP in a method for concatenating multiple tables in a database according to an embodiment of the present disclosure;

FIG. 3 is a schematic overall flowchart of a method for linking multiple tables in a database according to an embodiment of the present disclosure;

FIG. 4 is a schematic structural diagram of an apparatus for linking a plurality of tables in a database according to an embodiment of the present disclosure;

fig. 5 is a schematic structural diagram of an apparatus for connecting multiple tables of a database, which corresponds to fig. 1 and is provided in an embodiment of the present disclosure.

Detailed Description

To make the objects, technical solutions and advantages of one or more embodiments of the present disclosure more apparent, the technical solutions of one or more embodiments of the present disclosure will be described in detail and completely with reference to the specific embodiments of the present disclosure and the accompanying drawings. It is to be understood that the embodiments described are only a few embodiments of the present disclosure, and not all embodiments. All other embodiments obtained by a person of ordinary skill in the art based on the embodiments in the present specification without any creative effort belong to the protection scope of one or more embodiments in the present specification.

It should be understood that although the terms first, second, third, etc. may be used herein to describe various information, these information should not be limited to these terms. These terms are only used to distinguish one type of information from another.

In the following, technical terms related to technical solutions in embodiments of the present specification are collectively described to more clearly describe the technical solutions in embodiments of the present specification.

Table: tables, which are objects in a database used to store data and which may also be referred to as relationships, are collections of structured data, similar to spreadsheets, where data is organized in a format of rows and columns, where each column in a table is designed to store a certain type of information, and where there may be several controls (constraints, rules, defaults, and custom user data types) to ensure the validity of the data. Specifically, as shown in table 1, table 1 is an example of a table in a relational database, the name of the table is "family", in the "family" table, the column names of the columns are "husband", "wife" and "child", respectively, and each column may be also referred to as "field", "attribute" and "data item". The row in which the column name of each column is located may be referred to as the "heading" or "mode" of the table. Each row in the table, except the row where the column name is located, may be referred to as a "row", "tuple", or "record", and a particular value below a column in the table may be referred to as a "column value". It should be noted that different databases have different extensions to this kind of structure, for example, the extension of the Access database is mdb, the extension of the MSSql database is mdf, the extension of the paramox database is DB, the extension of the Oracle database is DBF, the extension of the dBase database is DBF, the extension of the FoxPro database is DBF, and the extension of the Works database is wdb.

Husband	Wife (wife)	Child and woman
			Lie somewhere	King of a certain	Something in plum
Zhao (a kind of food)	Liu Yi (Chinese character)	Zhao something
			Sun someone	Money certain	A certain sun

TABLE 1 household watch

Connection operation of the table: when the database is used for storing data, in order to efficiently store the data, the data is stored in different tables according to the design of the logic database, wherein each table has a main code for uniquely identifying a record in a certain table. After a user writes a database query statement for querying a database system by using a database query language, because data may be stored in different tables, an execution engine of the database system needs to splice a plurality of tables involved when specifically executing the database query statement written by the user, which is the operation of table connection.

Evaluation function: when a plurality of tables in the database are connected during the connection operation, the equivalent candidate execution plans can be connected, that is, the equivalent candidate execution plans are connected in different orders and algorithms, a method for comparing which of the equivalent candidate execution plans is better can be called as an evaluation function, and the evaluation function can use cost, intermediate result set scale and the like as measurement indexes when evaluating which of the equivalent candidate execution plans is better.

Dynamic programming algorithm of table join: in a broad sense, the Dynamic Programming (DP) method is a method for decomposing a complex problem into simple sub-problems, and is suitable for the problem with overlapping sub-problems and optimal sub-structure properties. Based on the method, the dynamic planning method for table connection is a method for solving the problem of connection of a plurality of tables in the database by adopting the dynamic planning method.

When the table in the database is subjected to connection operation, the overall execution time of the connection operation comprises the time for planning and obtaining the connection sequence of the database table and the time for carrying out specific connection operation on the table according to the connection sequence.

It should be noted that, when connecting a plurality of tables in a database, a connection order of the tables is constructed first, and then the tables are connected according to the constructed connection order, and a preferred method for connecting a plurality of tables in a database should be described in the following, after the meanings of technical terms used in the embodiments of the present specification are introduced in a unified manner, advantages and disadvantages when connecting a plurality of tables in a database in the prior art are described first.

First, for the DP algorithm for dynamically planning table join, the method enumerates all join orders of tables by an enumeration method and obtains an optimal join order by an evaluation function, but since the method obtains all join orders of tables by an enumeration method, there are disadvantages of large planning space and long enumeration time, and the time for generating the final join order of tables is long, so that it is not practical due to the long time for generating the optimal join order in a large-scale join scenario, such as plan enumeration of 10 or more tables.

For the heuristic algorithm of table join, the algorithm performs a large-range pruning operation in the enumeration process based on heuristic rules, and table join sequences with high execution efficiency are likely to be discarded in the pruning operation process, so that although the result of some table join sequences can be generated quickly, the execution efficiency of the join sequences may be low.

For an Iterative Dynamic Programming enumeration algorithm (IDP) for table connection, the algorithm improves a DP for table connection, and the method adopts a fixed or simple iteration step length to perform multiple rounds of iteration enumeration, which is an enumeration method for better balancing plan quality and plan generation time, but the scheme of the fixed step length or the simple iteration step length adopted has obvious defects when dealing with scenes with different connection characteristics and scales. For example, for a star-connected scenario, in which 100 database tables need to be connected, assuming that the preset fixed step size is 4, the first iteration of a process including 4 nodes from 100 candidate nodes will already generate over millions of connection paths, and the enumeration process is too long to be acceptable. For another example, for the chain connection, considering the chain connection of one 100 tables, unlike the star connection, the number of effective connection paths of the chain connection is limited, even if the step size is set to be large, such as K =50, the total enumeration time is small, and a fixed step size that is set to be too small may have an influence on the enumeration optimal plan. Therefore, the requirement of different connection characteristics and different connection scenes cannot be met by simply setting a static fixed step length. In the simple iteration step size scheme, the limited step size must be an even number so as to be capable of generating a dense tree, and the iteration step size does not exceed 1/2 of the remaining connection size by rounding, but because a feedback mechanism for real-time enumeration of process data is not available and real-time dynamic adjustment is not available, the method has no substantial significance for optimizing the enumeration process and obtaining the optimal result. Therefore, for the iterative dynamic planning enumeration algorithm of table connection, no matter a fixed or simple iteration step scheme, the effect of iterative evolution of the IDP cannot be effectively controlled, and adaptive dynamic management of plan generation in each scene cannot be realized, so that the expected effect of plan quality and time in each scene cannot be achieved.

The technical scheme of the specification provides a method capable of connecting a plurality of tables in a database, improves an IDP (inverse discrete point) method for connecting a plurality of tables in the database in the prior art, and can avoid the problems of rapid expansion and rapid reduction of the number of connection paths possibly occurring in the running process of an IDP (inverse discrete point) algorithm and influence on the generation time of an overall plan by setting a multi-stage control interval, namely, provides a method for determining the connection sequence of a plurality of tables in the database, which is better from the perspective of overall execution time.

The technical solutions provided by the embodiments of the present description are described in detail below with reference to the accompanying drawings.

In order to solve the defects in the prior art, the scheme provides the following embodiments:

fig. 1 is a schematic flowchart of an overall scheme of a method for connecting multiple tables of a database in an embodiment of the present specification. From the viewpoint of the program, the execution subject of the flow may be a program installed in an application server or an application terminal.

As shown in fig. 2, the process may include the following steps:

step 102: a plurality of tables to be connected are obtained.

In the embodiment of the present specification, a table is a data structure for storing data in a relational database in a form of rows and columns, and the specific meaning of the table has been described above, and the meaning of the join operation of the table has also been described above, and is not described herein again.

In this step, a specific manner of obtaining the multiple tables to be subjected to the join operation is not limited, and any manner related to the manner in which the database execution engine needs to perform the join operation on the multiple tables may be regarded as obtaining the multiple tables to be subjected to the join operation. For example, when a common user uses a database system to perform query operation, the common user manually inputs a database query statement written by using a database query language, and a database execution engine needs to connect a plurality of relevant tables when executing the database query statement; alternatively, when a certain program specifically executes a logical operation of the program, it may be necessary to perform a join operation on a plurality of tables.

When a plurality of tables need to be connected locally, the tables needing to be connected can be stored in a local storage device or a storage device at other places, and when a plurality of tables need to be connected, the tables participating in the connection operation can be transmitted to the local storage device through a network transmission mode and the like.

Step 104: and enumerating the connection paths of the tables by using a preset initial step length as a current step length based on an iterative dynamic programming enumeration algorithm to obtain the real-time connection path enumeration number of the current layer of the current wheel.

In this step, an Iterative Dynamic Programming enumeration algorithm (IDP) for table connection in the prior art is used, and a local whole node is generated after an iteration round is finished, where the local whole node may refer to a table set of a determined connection order generated in an iteration process of connecting a plurality of tables by using the IDP algorithm, and may be regarded as an integral node participating in a next iteration round, and the integral node includes a plurality of sub-nodes, and the connection order of the sub-nodes included in the integral node is determined to be not changed any more.

The content of the algorithm is explained first below, and the iterative dynamic programming enumeration algorithm IDP is a method for splitting the dynamic enumeration process in the dynamic programming method into a plurality of rounds for iteration by setting a suspension condition in order to reduce the algorithm time complexity of the dynamic programming algorithm. The table-bound IDP method is explained in detail with reference to fig. 2.

As shown in fig. 2, fig. 2 relates to the operation of linking the tables R1, R2, R3, R4, R5, R6, and R7, according to the IDP method, an initial step K needs to be preset, which can be set to 4, in this scenario, the IDP method first performs a first iteration to select which table of the 7 tables has the best linking efficiency according to the evaluation function, and then the linking order of the 4 tables is fixed and is linked again with the rest tables as a local whole node in the next iteration. Specifically, in the first round of first layer enumeration process, an access manner to each table may be first determined, and a plurality of manners for connecting any two tables in the seven tables are generated in the first round of second layer enumeration process, for example, as shown in fig. 2, the first round of second layer enumeration generates a plurality of manners for connecting two tables, such as { R1, R2}, { R1, R3}, { R2, R7} and the like, the manners for connecting two tables are used as an integral node to participate in the first round of third layer enumeration process, the manners for connecting three tables generated in the first round of third layer enumeration process continue to participate in the first round of fourth layer enumeration process as an integral node, as shown in fig. 2, and generating a plurality of connection modes of four tables in the fourth-layer enumeration process of the first round, for example, including { R1, R2, R3, R4}, { R2, R7, R3, R5}, at which time the first round of iteration is finished, assuming that the connection efficiency of { R2, R7, R3, R5} is selected to be the highest in the fourth-layer enumeration result of the first round according to the evaluation function, then { R2, R7, R3, R5} participates in the next round of iteration process as a whole node BO, and participates in the subsequent second round of iteration process of the IDP algorithm together with the remaining R1, R4, R6 to complete 4-layer enumeration of the second round, and finally selects the optimal complete connection sequence, for example, in one possible result, the optimal complete connection sequence is { R2, R7, R3, R5, R6, R1, R4}.

In summary, for the connection of multiple tables, only one initial step size needs to be preset by using the iterative dynamic programming IDP method, and the method can perform multiple iterations according to the initial step size to finally generate a complete connection sequence of the multiple tables. The IDP method involves multiple iterations, each iteration involves several layers of enumeration processes, each layer of enumeration processes generates some intermediate results, each intermediate result involves generating some local whole nodes that can be regarded as no longer changing subsequently, for example, in the aforementioned example, { R1, R2, R3, R4}, { R2, R7, R3, R5} is generated in the first and fourth layers of enumeration processes.

Based on the foregoing, in the embodiment of the present specification, the preset initial step size may also be understood as a variable that is used to start the IDP algorithm to perform tentative connection on the multiple tables in step 102, and may further be adaptively adjusted according to the real-time enumeration process and the corresponding control interval policy. It should be noted that, a complete connection sequence may be generated for a plurality of tables by using an IDP algorithm based on a preset initial step, but in the present invention, instead of performing an iterative dynamic programming enumeration IDP method to generate a complete connection sequence for a plurality of tables based on the preset initial step as a subsequent static step that is not changed any more, an iterative enumeration process of the IDP algorithm is used to collect, in real time, the number of connection paths generated by each layer in the intermediate processes, such as the first layer, the third layer, the 8230, the second layer, the third layer, the 8230, and so on, and then the step size is adaptively adjusted according to a corresponding control interval policy until a complete connection sequence of a plurality of tables to be connected is obtained, which has a frame difference and improvement from the conventional IDP process.

In the embodiment of the present specification, the specific numerical value of the initial step may be set in a preset manner, or may be set according to a specific use scenario of the method in the embodiment of the present specification.

Step 106: and determining interval identification information of the real-time connection path enumeration number falling into a plurality of preset control intervals.

In the embodiment of the present disclosure, the control interval may refer to a preset value interval for determining whether the number of connection paths of the single-round monolayer determined in step 104 can be used to stop the complete iteration process of the IDP method. In the scheme, a plurality of control intervals can be set, so that which control interval of the plurality of control intervals falls into can be specifically determined according to the number of the connection paths, a corresponding step length adjusting mode can be adopted in a self-adaptive mode, and different control intervals can be identified by using different interval identification information.

In this step, the setting mode of the control interval may be a preset mode, and specifically, the number of tables that need to be connected in different scenes in the technical solution of the embodiment of the present specification may be counted in an early stage, and the statistical information such as the initial path number information when the tables of different number scales are connected is obtained according to the IDP algorithm described in step 104, so that a value interval that is reasonable for stopping the complete iteration process of the IDP method is obtained according to the empirical data of the actual scene.

Step 108: and determining a step length adjusting mode for adjusting the current step length based on the interval identification information, and adjusting the current step length according to the step length adjusting mode to obtain the adjusted step length.

In this embodiment of the present specification, after the control interval in which the number of connection paths in step 106 falls is determined, the current step length may be adaptively adjusted according to the step length adjustment manner of the control interval, to obtain an adjusted step length, that is, each control interval is preset with a corresponding step length adjustment manner, and when the number of connection paths enumerated in real time falls into a specific control interval, the step length is adaptively adjusted according to the step length adjustment manner of the specific control interval.

Step 110: and continuously performing iterative dynamic planning enumeration on the lower layer or the lower wheel of the current layer of the current wheel based on the adjusted step length until the complete connection sequence of the tables is obtained.

In the previous step, based on an iterative dynamic programming enumeration algorithm, a preset initial step length is adjusted once according to a control interval to which the number of connection paths of the current layer of the current round enumerated in real time belongs, and an adjusted step length is obtained.

Step 112: concatenating the plurality of tables based on the complete concatenation order.

In this step, after the complete connection sequence for connecting the plurality of tables is obtained, the plurality of tables may be connected based on the complete connection sequence.

The technical scheme of fig. 1 adopts a plurality of control intervals to control a manner of adjusting the step length, the step length can be adaptively adjusted according to the number of connection paths of the current layer of the current round enumerated in real time, an iterative dynamic programming enumeration algorithm in the prior art is improved, and the iterative dynamic programming enumeration algorithm can be called as an adaptive iterative dynamic programming enumeration algorithm.

Based on the method of fig. 1, the embodiments of the present specification also provide some specific implementations of the method, which are described below.

In the technical solution of the optional embodiment, before performing a specific join operation on a plurality of tables to be joined, a constant table may be identified from the tables, the identified constant table is arranged in front, and then the remaining tables are sorted, where the sorting criterion may be to arrange a table with a small number of tuples in front.

In the technical solution of the optional embodiment, the preset control intervals include a first control interval, a second control interval and a third control interval; the left end point of the second control interval is a first control parameter, and the right end point of the second control interval is a second control parameter; the left end point of the first control interval is zero, and the right end point of the first control interval is the first control parameter; and the left end point of the third control interval is the second control parameter.

It should be noted that, in the embodiment, setting the number of the control sections to three is a preferred embodiment, and from the perspective of the overall execution time, when a person skilled in the art designs a solution to the technical problem of efficiently connecting a plurality of tables in a database, the number of the control sections and the step size adjustment mode corresponding to each control section can be flexibly set according to the actual situation based on the design idea of the embodiment of the present specification, and the setting of the number of the control sections to three in the embodiment does not limit the technical solution of the present invention.

In the technical solution of the optional embodiment, before determining that the enumerated number of the real-time connection paths falls into the interval identification information of the preset control intervals, the method includes:

configuring first control section identification information for the first control section in advance, configuring second control section identification information for the second control section, and configuring third control section identification information for the third control section.

In the technical solution of the optional embodiment, the first control parameter has a size equal to a preset path number control factor, and the second control parameter has a size equal to a plurality of times of the path number control factor.

In the technical solution of the optional embodiment, the first control interval corresponds to a first step length increasing manner, the second control interval corresponds to a first step length decreasing manner, and the third control interval corresponds to a second step length decreasing manner; and the step descending speed corresponding to the second step descending mode is greater than the step descending speed corresponding to the first step descending mode.

In an alternative embodiment, the first step-length decreasing manner includes a linear decreasing manner, and the second step-length decreasing manner includes an exponential decreasing manner.

In the scheme, the index decreasing mode can refer to that the first step length is decreased in an index mode, namely, an index factor is preset, and the original step length is divided by the index factor to obtain the decreased step length. For example, if the first step size is 20, the first step size is decreased by an exponential factor of 2 to 20/2, i.e., 10. In the scheme, the initial step length can be quickly adjusted by the exponential decreasing mode, so that the number of paths corresponding to the adjusted step length falls into the target interval as soon as possible.

The linear descending manner may refer to descending with a fixed constant, and if the current step size is 15 and the fixed constant is 4, the step size after one descending operation is performed on the current step size with the fixed step size is 15-4, that is, the new step size after descending is 11. The linear descending mode is a slower descending operation mode for the step length, and can avoid that the plan quality of the local integral node generated in the iterative process is influenced by quickly descending the step length.

In an alternative embodiment, the first step growth manner includes a linear growth manner.

The meaning of the linear decreasing manner is introduced in the foregoing, the linear increasing manner may be understood as performing an increasing operation on the step length by using a certain fixed constant, and if the current step length is 18 and the fixed constant is 3, the step length after performing an increasing operation on the current step length by using the fixed step length is 18+3, that is, the new step length after the full length is 21.

In an optional embodiment, the performing iterative dynamic planning enumeration on the lower layer or the lower round of the current layer of the current round based on the adjusted step size until obtaining a complete connection sequence of the tables includes:

determining a current connected table set corresponding to the current step length and a connection sequence corresponding to the current connected table set;

and saving the connection sequence corresponding to the current connected table set.

For example, in the step 102, the tables to be subjected to the join operation are R1, R2, \8230;, and R20, and in the process of joining the tables by using the preset initial step size based on the iterative dynamic programming enumeration algorithm, if the join order of a local whole node has been determined, for example, the join order of the four tables of R2, R3, R5, and R7 is determined to be R2, R7, R3, and R5, the join order may be fixed at this time to be used as a table set, and subsequent join operation is performed with the remaining tables. More specifically, the set of tables is { R2, R7, R3, R5}, which is then treated as a whole node, and the whole node is connected to the remaining tables R1, R4, R6, R8, R9, R10, \ 8230, R20 based on the adaptive iterative dynamic programming enumeration algorithm.

In an alternative embodiment, the first step growth manner includes a linear increase manner. The linear increment mode is a mode of slowly increasing the step size, and can avoid that the step size is rapidly decreased to influence the quality of a result generated in the intermediate process, such as adding a fixed constant each time.

The technical solution of the present invention is explained in a complete embodiment based on fig. 3.

Taking as an example that 20 tables R1.. And R20 in the database are connected, the preset initial step size may be set to be the size of the connection scale, that is, the initial step size IK is 20, and the size of the current step size CK is set to be a value corresponding to the initial step size IK. In the scheme, 3 control intervals can be set, namely [0, F ] [ FM ] [ F ] [ M ] [ F- ], and respectively represent a normal interval, a linear step-down interval and an exponential step-down interval. In an alternative, the value of M may be set to 2, the value of f may be set to 5000, while a global minimum step size MinK and an exponential factor are also set, which in one possible scenario is set to 2, and the exponential factor for the exponential down operation of the step size is set to 2. After setting these initial parameters, the iterative operation of the IDP algorithm of the first round is started first, and assuming that when iterating to the third layer of the first round, the number of generated connection paths exceeds M × F, i.e. 2 × 5000, the present solution immediately stops the enumeration process of the current round, and sets the next round iteration step to 20 divided by 2 at an exponential decreasing speed (for example), i.e. sets the next round iteration step to 10. Meanwhile, the enumeration result of the first round is converged, the optimal connection sequence in the intermediate process, namely Best Order, such as { R1, R2, R3} is selected according to the evaluation function, R1, R2, R3 are respectively removed from the remaining connection LIST TODO _ LIST, and { R1, R2, R3} is added as an integral node to the unconnected LIST sequence of the next round for iterative operation. After entering the second round of iteration, assuming that when the second round of the third layer finds that the number of the generated connection paths is smaller than M × F but exceeds F, the present embodiment may stop the enumeration formation of the present round, and adjust the step length K at the linear decreasing speed, where if the linear decreasing step length is 2, the decreased step length is 10 "2, that is, 8. Therefore, after the above rounds of dynamic feedback and adaptive convergence, the enumeration process is well controlled, and severe expansion of the number of connection paths due to single-layer large-scale complete iteration is avoided, so that the generation time of the whole plan is not influenced. Meanwhile, in the scheme, the complete enumeration process is still kept in the wheel internal layering, and the overall connection path still keeps a larger base number, so that the possibility of finally enumerating a better plan is still maintained at a controllable higher level. Next, after entering a subsequent round with a smaller effective connection path space, especially, if the decreased step length is reused, since the connection sequence of some nodes is fixed too early, enumeration of the optimal candidate plan is limited, so that according to the current enumeration situation, if the current enumeration path number is found to be within the range of F, the current step length can be linearly increased to appropriately expand the enumeration space, and the possibility of enumerating the optimal or better final plan is improved. The subsequent iteration process continues until the final join order is generated.

The technical scheme of the invention collects plan enumeration data in real time and adaptively dynamically adjusts the IDP iteration step size and the iteration process. Ending the iteration of the current round in advance when the connection path enumerates and expands rapidly, and decreasing the current step length according to the condition index or linearity; and dynamically increasing the current step length when the generation of the connection path is controllable, properly expanding the enumeration space of the lower round, and promoting the possibility of the optimal or better final plan. The scheme improves the defect that the original IDP algorithm is fixed or a simple iteration step length scheme cannot adjust the enumeration process according to real-time feedback and dynamic, can carry out self-adaptive control on the whole enumeration process, and is a plan enumeration framework capable of effectively balancing plan quality and generation time in various scenes.

It should be understood that in the method described in one or more embodiments of the present specification, the order of some steps may be adjusted according to actual needs, or some steps may be omitted.

Based on the same idea, the embodiment of the present specification further provides a device corresponding to the method. Fig. 4 is a schematic structural diagram of an apparatus for connecting multiple tables of a database, corresponding to fig. 1, provided in an embodiment of this specification. As shown in fig. 4, the apparatus may include:

a table obtaining module 402, configured to obtain a plurality of tables to be subjected to a connection operation;

a connection path number obtaining module 404, configured to enumerate connection paths of the multiple tables based on an iterative dynamic programming enumeration algorithm by using a preset initial step length as a current step length, so as to obtain a real-time connection path enumeration number of a current layer of a current round;

an interval identifier determining module 406, configured to determine interval identifier information that the enumerated number of the real-time connection paths falls into a plurality of preset control intervals;

a step size adjusting module 408, configured to determine a step size adjusting manner for adjusting the current step size based on the interval identification information, and adjust the current step size according to the step size adjusting manner to obtain an adjusted step size;

an enumeration module 410, configured to continue iterative dynamic planning enumeration on a lower layer or a lower round of the current layer of the current round based on the adjusted step length until a complete connection order of the multiple tables is obtained;

a table join module 412 to join the plurality of tables based on the complete join order.

It will be appreciated that the modules described above refer to computer programs or program segments for performing a certain function or functions. In addition, the aforementioned distinction of the modules does not mean that the actual program code must be separated.

Based on the same idea, the embodiment of the present specification further provides a device corresponding to the method.

Fig. 5 is a schematic structural diagram of an apparatus for connecting multiple tables of a database, corresponding to fig. 1, according to an embodiment of the present disclosure. As shown in fig. 5, the apparatus 500 may include:

at least one processor 510; and the number of the first and second groups,

a memory 530 communicatively coupled to the at least one processor; wherein the content of the first and second substances,

the memory 530 stores instructions 520 executable by the at least one processor 510 to enable the at least one processor 510 to:

acquiring a plurality of tables to be connected;

enumerating the connection paths of the tables by using a preset initial step as a current step based on an iterative dynamic programming enumeration algorithm to obtain the real-time connection path enumeration number of the current layer of the current wheel;

Based on the same idea, the embodiment of the present specification further provides a computer-readable medium corresponding to the above method. The computer readable medium has computer readable instructions stored thereon that are executable by a processor to implement the method of:

acquiring a plurality of tables to be connected;

While particular embodiments of the present specification have been described above, in some cases, the actions or steps recited in the claims may be performed in a different order than in the embodiments and still achieve desirable results. In addition, the processes depicted in the accompanying figures do not necessarily require the particular order shown, or sequential order, to achieve desirable results. In some embodiments, multitasking and parallel processing may also be possible or may be advantageous.

The embodiments in this specification are described in a progressive manner, and the same and similar parts among the embodiments can be referred to each other.

The apparatus, the device, and the method provided in the embodiments of the present specification are corresponding, and therefore, the apparatus and the device also have beneficial technical effects similar to those of the corresponding method, and since the beneficial technical effects of the method have been described in detail above, the beneficial technical effects of the corresponding apparatus and device are not described again here.

In the 90 s of the 20 th century, improvements in a technology could clearly distinguish between improvements in hardware (e.g., improvements in circuit structures such as diodes, transistors, switches, etc.) and improvements in software (improvements in process flow). However, as technology advances, many of today's process flow improvements have been seen as direct improvements in hardware circuit architecture. Designers almost always obtain a corresponding hardware circuit structure by programming an improved method flow into the hardware circuit. Thus, it cannot be said that an improvement in the process flow cannot be realized by hardware physical blocks. For example, a Programmable Logic Device (PLD), such as a Field Programmable Gate Array (FPGA), is an integrated circuit whose Logic functions are determined by programming the Device by a user. A digital character system is "integrated" on a PLD by the designer's own programming without requiring the chip manufacturer to design and fabricate a dedicated integrated circuit chip. Furthermore, nowadays, instead of manually manufacturing an Integrated Circuit chip, such Programming is often implemented by "logic compiler" software, which is similar to a software compiler used in program development and writing, but the original code before compiling is also written by a specific Programming Language, which is called Hardware Description Language (HDL), and HDL is not only one but many, such as ABEL (Advanced Boolean Expression Language), AHDL (alternate Hardware Description Language), traffic, CUPL (core universal Programming Language), HDCal, jhddl (Java Hardware Description Language), lava, lola, HDL, PALASM, rhyd (Hardware Description Language), and vhigh-Language (Hardware Description Language), which is currently used in most popular applications. It will also be apparent to those skilled in the art that hardware circuitry for implementing the logical method flows can be readily obtained by a mere need to program the method flows with some of the hardware description languages described above and into an integrated circuit.

The controller may be implemented in any suitable manner, for example, the controller may take the form of, for example, a microprocessor or processor and a computer-readable medium storing computer-readable program code (e.g., software or firmware) executable by the (micro) processor, logic gates, switches, an Application Specific Integrated Circuit (ASIC), a programmable logic controller, and an embedded microcontroller, examples of which include, but are not limited to, the following microcontrollers: ARC 625D, atmel AT91SAM, microchip PIC18F26K20, and Silicone Labs C8051F320, the memory controller may also be implemented as part of the control logic for the memory. Those skilled in the art will also appreciate that, in addition to implementing the controller in purely computer readable program code means, the same functionality can be implemented by logically programming method steps such that the controller is in the form of logic gates, switches, application specific integrated circuits, programmable logic controllers, embedded microcontrollers and the like. Such a controller may thus be considered a hardware component, and the means included therein for performing the various functions may also be considered as a structure within the hardware component. Or even means for performing the functions may be regarded as being both a software module for performing the method and a structure within a hardware component.

The systems, apparatuses, modules or units described in the above embodiments may be specifically implemented by a computer chip or an entity, or implemented by a product with certain functions. One typical implementation device is a computer. In particular, the computer may be, for example, a personal computer, a laptop computer, a cellular telephone, a camera phone, a smartphone, a personal digital assistant, a media player, a navigation device, an email device, a game console, a tablet computer, a wearable device, or a combination of any of these devices.

For convenience of description, the above devices are described as being divided into various units by function, respectively. Of course, the functionality of the units may be implemented in one or more software and/or hardware when implementing the present application.

As will be appreciated by one skilled in the art, embodiments of the present invention may be provided as a method, system, or computer program product. Accordingly, the present invention may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present invention may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.

The present invention is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each flow and/or block of the flowchart illustrations and/or block diagrams, and combinations of flows and/or blocks in the flowchart illustrations and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.

In a typical configuration, a computing device includes one or more processors (CPUs), input/output interfaces, network interfaces, and memory.

The memory may include forms of volatile memory in a computer readable medium, random Access Memory (RAM) and/or non-volatile memory, such as Read Only Memory (ROM) or flash memory (flash RAM). Memory is an example of a computer-readable medium.

Computer-readable media, including both permanent and non-permanent, removable and non-removable media, may implement the information storage by any method or technology. The information may be computer readable instructions, data structures, modules of a program, or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static Random Access Memory (SRAM), dynamic Random Access Memory (DRAM), other types of Random Access Memory (RAM), read Only Memory (ROM), electrically Erasable Programmable Read Only Memory (EEPROM), flash memory or other memory technology, compact disc read only memory (CD-ROM), digital Versatile Disks (DVD) or other optical storage, magnetic cassettes, magnetic tape storage or other magnetic storage devices, or any other non-transmission medium, which can be used to store information and/or data which can be accessed by a computing device. As defined herein, a computer readable medium does not include a transitory computer readable medium such as a modulated data signal and a carrier wave.

It should also be noted that the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrases "comprising one of 8230; \8230;" 8230; "does not exclude the presence of additional like elements in a process, method, article, or apparatus that comprises that element.

The application may be described in the general context of computer-executable instructions, such as program modules, being executed by a computer. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform particular tasks or implement particular abstract data types. The application may also be practiced in distributed computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed computing environment, program modules may be located in both local and remote computer storage media including memory storage devices.

The above description is only an example of the present application and is not intended to limit the present application. Various modifications and changes may occur to those skilled in the art to which the present application pertains. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application should be included in the scope of the claims of the present application.

Claims

1. A method of concatenating a plurality of tables of a database, comprising:

acquiring a plurality of tables to be connected;

2. The method of claim 1, wherein the preset control intervals comprise a first control interval, a second control interval and a third control interval; the left end point of the second control interval is a first control parameter, and the right end point of the second control interval is a second control parameter; the left end point of the first control interval is zero, and the right end point of the first control interval is the first control parameter; and the left end point of the third control interval is the second control parameter.

3. The method of claim 2, wherein determining the real-time connection path enumeration number to fall within interval identification information of a number of preset control intervals comprises:

4. The method of claim 2, the first control parameter having a magnitude of a preset path number control factor, the second control parameter having a magnitude of several times the path number control factor.

5. The method of claim 2, wherein the first control interval corresponds to a first step size increasing mode, the second control interval corresponds to a first step size decreasing mode, and the third control interval corresponds to a second step size decreasing mode; and the step descending speed corresponding to the second step descending mode is greater than the step descending speed corresponding to the first step descending mode.

6. The method of claim 5, the first step-down pattern comprising a linear step-down pattern, the second step-down pattern comprising an exponential step-down pattern.

7. The method of claim 5, wherein the first step growth regime comprises a linear growth regime.

8. The method of claim 5, wherein the continuing iterative dynamic programming enumeration for a lower layer or a lower round of the current layer based on the adjusted step size until a complete join order of the plurality of tables is obtained comprises:

and storing the connection sequence corresponding to the current connected table set.

9. An apparatus for joining a plurality of tables of a database, comprising:

an enumeration module, configured to continue iterative dynamic planning and enumeration for a lower layer or a lower round of the current layer of the current round based on the adjusted step size until a complete connection order of the multiple tables is obtained;

10. An apparatus for connecting a plurality of tables of a database, comprising:

at least one processor; and the number of the first and second groups,

a memory communicatively coupled to the at least one processor; wherein the content of the first and second substances,

the memory stores instructions executable by the at least one processor to enable the at least one processor to:

acquiring a plurality of tables to be connected;

11. A computer readable medium having stored thereon computer readable instructions executable by a processor to implement the method of concatenating a plurality of tables of a database of any of claims 1 to 8.