A merge join cost evaluation method, device, equipment and medium

Abstract

The present invention relates to the field of data query technology, and in particular to a merge join cost evaluation method, device, equipment and medium. Determine the associated fields in the left table and the right table in the query statement, obtain the histogram of the corresponding column in the associated field in the right table, and determine the first capture cost of the to-be-matched value in each row of the corresponding column of the associated field in the left table as the termination point, the second capture cost of the to-be-matched value that satisfies the join result, and the third capture cost of the number of to-be-matched values ​​less than the target value according to the target value and the histogram in each row in the corresponding column of the associated field in the left table, determine the target capture cost in the left table and the right table, and calculate the cost evaluation value of the query statement according to the basic cost and the target capture cost. In the present application, the target capture cost in the left table and the right table is calculated using the histogram, and according to the execution principle of merge join, the merge join cost evaluation is converted into a statistical problem based on column statistical information, thereby improving the cost evaluation accuracy of merge join.

Description

Technical Field

[0001] The present invention relates to the technical field of data query, and in particular to a merge join cost evaluation method, device, equipment and medium. Background Art

[0002] Currently, most of the structured query execution engines of database management systems provide the merge join method. Compared with other connection methods, the advantages of merge join are high performance, no additional memory overhead, and support for more association conditions. However, the optimizers of these database management systems cannot provide sufficiently accurate cost evaluation for merge join, which results in the inability to select merge join in some scenarios where merge join is the optimal method. Therefore, in executing merge join query statements, how to improve the evaluation accuracy of merge join has become an urgent problem to be solved. Summary of the invention

[0003] In view of this, embodiments of the present application provide a merge join cost evaluation method, apparatus, device, and medium to solve the problem of low merge join evaluation accuracy in executing a merge join query statement.

[0004] In a first aspect, an embodiment of the present application provides a merge join cost evaluation method, the cost evaluation method comprising:

[0005] Obtain a query statement for executing merge join, determine the associated fields in the left table and the right table in the query statement, and obtain a histogram of a corresponding column in the associated field in the right table according to the associated field;

[0006] According to the target value in each row in the column corresponding to the associated field in the left table and the histogram, determine a first capture cost when the to-be-matched value in each row in the column corresponding to the associated field in the right table is a termination point, a second capture cost when the to-be-matched value satisfies the join result, and a third capture cost when the number of to-be-matched values ​​is less than the target value;

[0007] Determine the target capture price in the left table and the right table according to the first capture price, the second capture price, and the third capture price;

[0008] A basic cost of the query statement is obtained, and a cost evaluation value of the query statement is calculated based on the basic cost and the target capture cost.

[0009] In a second aspect, an embodiment of the present application provides a merge join cost evaluation device, the cost evaluation device comprising:

[0010] An acquisition module is used to acquire a query statement for executing merge join, determine the associated fields in the left table and the right table in the query statement, and acquire a histogram of a corresponding column in the associated field in the right table according to the associated field;

[0011] A first determination module is used to determine, according to the target value in each row in the column corresponding to the associated field in the left table and the histogram, a first capture value that the to-be-matched value in each row in the column corresponding to the associated field in the right table is a termination point, a second capture value that the to-be-matched value satisfies a join result, and a third capture value that the number of to-be-matched values ​​is less than the target value;

[0012] A second determination module is used to determine the target capture price in the left table and the right table according to the first capture price, the second capture price and the third capture price;

[0013] The calculation module is used to obtain the basic cost of the query statement, and calculate the cost evaluation value of the query statement according to the basic cost and the target capture cost.

[0014] In a third aspect, an embodiment of the present application provides a computer device, comprising a processor, a memory, and a computer program stored in the memory and executable on the processor, wherein the processor implements the cost evaluation method described in the first aspect when executing the computer program.

[0015] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the cost evaluation method as described in the first aspect is implemented.

[0016] Compared with the prior art, the present invention has the following beneficial effects:

[0017] Get the query statement for executing merge join, determine the associated fields in the left table and the right table in the query statement, obtain the histogram of the corresponding column in the associated field in the right table according to the associated fields, determine the first capture substitution value of the to-be-matched value in each row of the corresponding column of the associated field in the left table as the termination point, the second capture substitution value of the to-be-matched value satisfying the join result, and the third capture substitution value of the number of to-be-matched values ​​less than the target value according to the first capture substitution value, the second capture substitution value and the third capture substitution value, determine the target capture substitution value in the left table and the right table according to the first capture substitution value, the second capture substitution value and the third capture substitution value, obtain the basic cost of the query statement, and calculate the cost evaluation value of the query statement according to the basic cost and the target capture substitution value. In the present application, the target capture substitution value in the left table and the right table is calculated using the histogram, and according to the execution principle of merge join, the merge join cost evaluation is converted into a statistical problem based on column statistical information, thereby improving the cost evaluation accuracy of merge join. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0019] Figure 1 This is a schematic diagram of an application environment of a merge join cost evaluation method provided in the first embodiment of the present application;

[0020] Figure 2 It is a flow chart of a merge join cost evaluation method provided in the second embodiment of the present application;

[0021] Figure 3 It is a flowchart of a merge join cost evaluation method provided in Embodiment 3 of the present application;

[0022] Figure 4 It is a flowchart of a merge join cost evaluation method provided in Embodiment 4 of the present application;

[0023] Figure 5 It is a flowchart of a merge join cost evaluation method provided in Embodiment 5 of the present application;

[0024] Figure 6 It is a structural schematic diagram of a merge join cost evaluation device provided in Embodiment 6 of the present application;

[0025] Figure 7 It is a structural diagram of a computer device provided in Example 7 of the present application. DETAILED DESCRIPTION

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

[0027] It should be understood that when used in the present specification and the appended claims, the term "comprising" indicates the presence of described features, integers, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, integers, steps, operations, elements, components and / or combinations thereof.

[0028] It should also be understood that the term “and / or” used in the specification and appended claims refers to any and all possible combinations of one or more of the associated listed items, and includes these combinations.

[0029] As used in the specification and appended claims of this application, the term "if" can be interpreted as "when" or "uponce" or "in response to determining" or "in response to detecting", depending on the context. Similarly, the phrase "if it is determined" or "if [described condition or event] is detected" can be interpreted as meaning "uponce it is determined" or "in response to determining" or "uponce [described condition or event] is detected" or "in response to detecting [described condition or event]", depending on the context.

[0030] In addition, in the description of the present application specification and the appended claims, the terms "first", "second", "third", etc. are only used to distinguish the descriptions and cannot be understood as indicating or implying relative importance.

[0031] References to "one embodiment" or "some embodiments" etc. described in the specification of this application mean that one or more embodiments of the present application include specific features, structures or characteristics described in conjunction with the embodiment. Therefore, the statements "in one embodiment", "in some embodiments", "in some other embodiments", "in some other embodiments", etc. that appear in different places in this specification do not necessarily refer to the same embodiment, but mean "one or more but not all embodiments", unless otherwise specifically emphasized in other ways. The terms "including", "comprising", "having" and their variations all mean "including but not limited to", unless otherwise specifically emphasized in other ways.

[0032] It should be understood that the size of the serial numbers of the steps in the following embodiments does not mean the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of the present application.

[0033] In order to illustrate the technical solution of the present application, a specific embodiment is provided below for illustration.

[0034] A merge join cost evaluation method provided in the first embodiment of the present application can be applied in the following cases: Figure 1 In the application environment, the client includes but is not limited to PDA, desktop computer, notebook computer, ultra-mobile personal computer (UMPC), netbook, cloud network equipment, personal digital assistant (PDA) and other devices. The computer device corresponding to the server can be implemented by an independent server or a server cluster composed of multiple servers.

[0035] See also Figure 2 , is a flow chart of a merge join cost evaluation method provided in Example 2 of the present application. The merge join cost evaluation method is applied to Figure 1 The server in Figure 2 As shown, the cost evaluation method of the merge join may include the following steps:

[0036] S201: Obtain a query statement for executing merge join, determine the associated fields in the left table and the right table in the query statement, and obtain a histogram of a corresponding column in the associated field in the right table according to the associated field.

[0037] In step S201, a query statement for executing merge join is obtained. When a query operation needs to be performed on the database, the client terminal sends a query request to the database in the server, and the query request includes a merge join query statement. In the merge join query statement, the associated fields in the left table and the right table in the query statement are determined, wherein the left table is a driving table in the corresponding query statement, the right table is a driven table in the corresponding query statement, and the associated fields are column fields that need to be connected. According to the associated fields, a histogram of the corresponding column in the associated fields in the right table is obtained.

[0038] In this embodiment, a query statement for executing merge join is obtained, wherein the query statement may be a structured query language (SQL) statement, and the associated fields in the left table and the right table in the query statement are determined according to the query statement, wherein the left table is a driving table, i.e., a data table that is read first during the execution of the query statement, and the right table is a driven table, i.e., a data table that is read subsequently during the execution of the query statement. The associated fields are the column fields for multi-table connection in the merge join query statement.

[0039] According to the associated field, the column in the right table, that is, the corresponding column in the driven table, is determined, the field value in the corresponding column is obtained, and a histogram is obtained according to the field value, wherein the horizontal axis of the histogram is the field value, and the vertical axis is the frequency of occurrence of the field value. It can also be other histograms, which are not limited in this embodiment. It should be noted that if it is other histograms, the calculation method based on the histogram will also change accordingly.

[0040] See also Figure 3 , is a flow chart of a merge join cost evaluation method provided in Example 3 of the present application. Figure 3 As shown, in step S201, obtaining the histogram of the corresponding column in the associated field in the right table according to the associated field may include the following steps:

[0041] S301: According to the associated field, determine the value in each row of the corresponding column of the associated field in the right table;

[0042] S302: construct a histogram of a corresponding column in the associated field according to the value in each row.

[0043] In this embodiment, according to the associated field, a histogram of the corresponding column of the right table in the associated field is obtained, wherein the values ​​in the histogram include the corresponding values ​​in each row, that is, the values ​​of the horizontal coordinates in the histogram are the values ​​in the corresponding rows in sequence. For example, if the values ​​in each row of the corresponding column in the associated field in the right table are 2, 3, 6, 3, 7, 4, 7, etc., respectively, the right table is scanned, the buckets and the frequencies corresponding to each bucket are counted, and then the values ​​corresponding to the buckets are sorted to obtain the corresponding histogram.

[0044] S202: According to the target value and histogram in each row in the corresponding column of the associated field in the left table, determine the first capture value when the to-be-matched value in each row in the corresponding column of the associated field in the right table is the termination point, the second capture value when the to-be-matched value satisfies the join result, and the third capture value when the number of to-be-matched values ​​is less than the target value.

[0045] In step S202, according to the associated field, the target value in each row in the corresponding column of the associated field in the left table is determined, and according to the target value and the histogram, the first capture value when the to-be-matched value in each row in the corresponding column of the associated field in the right table is determined as the termination point, the second capture value when the to-be-matched value satisfies the join result, and the third capture value when the number of to-be-matched values ​​is less than the target value is determined, wherein the first capture value, the second capture value and the third capture value are the execution costs when executing the connection, wherein the termination point is the position of the to-be-matched value at the end of each target value query when executing merge join, that is, the position of the right table pointer or EOF (End of File) when each target value in the left table is matched, wherein EOF is the result after the target value in the left table has no corresponding to-be-matched value in the right table, and the sign symbol at the end.

[0046] In this embodiment, when the tables are connected in order, the data in the left and right tables are already ordered by the associated field columns, which are assumed to be in ascending order. The search starts from the first row in the left table, that is, the row corresponding to the smallest target value, and the right table is searched for a matching value that is equal to the first target value. When a matching value greater than the first target value is found, the search for the first target value is stopped, and the position of the matching value is determined as the end point. If no matching value equal to the first target value is found, all values ​​in the right table are searched once, and the search ends, generating an end symbol EOF.

[0047] It should be noted that when executing a merge join, the corresponding execution cost can be the cost of reading the left table, the cost of reading the right table, the cost of comparing the associated field values ​​of the left table and the right table, the cost of rolling back the right table pointer, the cost of filtering the connection result according to the predicate, and the cost of assembling the connection result according to the matching data.

[0048] When searching for any target value in the left table, it is necessary to determine the row where the right table checkpoint pointer and the right table pointer are located in the right table, wherein the right table checkpoint pointer is used as a mark to start searching for any target value in the left table in the right table. For example, if the right table checkpoint pointer is in the second row, when searching the right table for the corresponding target value, the search starts from the second row of the right table to be matched, and the right table pointer is the row where the previous target value ends when searching. The right table checkpoint pointer and the right table pointer may be in the same row in the right table, or in different rows in the right table. For example, if the target value in the left table is equal to the value to be matched in the right table, the right table pointer is moved one row down, and the right table checkpoint pointer is not moved, to determine whether the value to be matched in the row where the right table pointer is located matches the target matching value; if the target value in the left table is greater than the value to be matched in the right table, the right table pointer is moved one row down, and the right table checkpoint pointer is moved to the row where the right table pointer is located, to determine whether the value to be matched in the row where the right table pointer is located matches the target matching value; if the target value in the left table is less than the value to be matched in the right table, the current target value matching is completed, and the right table pointer rolls back to the previous row in the right table.

[0049] Therefore, the cost of reading the right table can be divided into the first capture cost of the determined end point, the second capture cost when the to-be-matched value satisfies the join result, and the third capture cost when the to-be-matched value is less than the number of target values. The calculation formula for the cost of reading the right table is as follows:

[0050]

[0051] Among them, rightROwFetch is the cost of reading the right table, fetchEOF is the number of EOF, the value of fetchEOF is 1 or 0, fetchGreat is the number of fetches where the value to be matched in the right table is greater than the target value in the left table, fetchEqual is the number of fetches where the value to be matched in the right table is equal to the target value in the left table, and fetchLess is the number of fetches where the value to be matched in the right table is less than the target value in the left table. The corresponding cost is the corresponding execution cost, i is the number of corresponding rows in the left table, leftRows is the total number of rows in the left table, and h() is a linear function.

[0052] See also Figure 4 , is a flow chart of a merge join cost evaluation method provided in Embodiment 4 of the present application. Figure 4As shown, in step S202, according to the target value in each row in the column corresponding to the associated field in the left table and the histogram, determining the first capture value that the to-be-matched value in each row in the column corresponding to the associated field in the right table is the termination point, the second capture value that the to-be-matched value satisfies the join result, and the third capture value that the to-be-matched value is less than the number of target values, may include the following steps:

[0053] S401: Obtain the number of rows in the left table, and determine the first capture cost of the end point where the to-be-matched value is the end point according to the number of rows in the left table;

[0054] S402: Determine the second capture price according to the number of rows in the right table whose to-be-matched value is equal to the target value;

[0055] S403: Calculate a third capture cost for the number of to-be-matched values ​​less than the target value according to the frequency of each to-be-matched value in the histogram.

[0056] In this embodiment, i.e., the inner join with the association condition of filter equal, the first capture cost of the determined termination point includes the cost of the termination point corresponding to the last to-be-matched value in the right table and the cost of the termination point where the to-be-matched value in the right table is greater than the target value in the left table, wherein the termination point corresponding to the last to-be-matched value in the right table indicates that the maximum to-be-matched value in the corresponding column in the right table is less than the target value, and for any target value in the left table, there must be one to-be-matched value in the right table as the termination point, therefore, the first capture cost is the total number of rows in the left table. The calculation formula for the first capture cost is as follows:

[0057]

[0058] Among them, fetchEOF is the number of EOF, the value of fetchEOF is 1 or 0, fetchGreat is the number of fetches of the endpoint where the to-be-matched value in the right table is greater than the target value in the left table, that is, the number of rows where the to-be-matched value in the right table is greater than the target value in the left table, i is the corresponding number of rows in the left table, h() is a linear function, leftRows is the total number of rows in the left table, and h(leftRows) is the first fetch cost.

[0059] The second capture cost is determined according to the number of rows in the right table where the to-be-matched value is equal to the target value. The calculation formula for the second capture cost is as follows:

[0060]

[0061] Among them, fetchEqual is the number of fetches where the to-be-matched value in the right table is equal to the target value in the left table, that is, the number of rows where the to-be-matched value in the right table is equal to the target value in the left table, i is the corresponding number of rows in the left table, leftRows is the total number of rows in the left table, h() is a linear function, joinRows is the number of rows where the to-be-matched value in the right table is equal to the target value in the left table, and h(joinRows) is the second fetch cost, that is, the matching cost.

[0062] According to the histogram, the frequency difference between adjacent to-be-matched values ​​is determined in the histogram, and the third capture cost of the number of to-be-matched values ​​being less than the target value is calculated according to the frequency difference.

[0063] See also Figure 5 , is a flow chart of a merge join cost evaluation method provided in Embodiment 5 of the present application. Figure 5 As shown, in step 1, according to the frequency of each to-be-matched value in the histogram, calculating the third capture cost of the number of to-be-matched values ​​less than the target value may include the following steps:

[0064] S501: Obtain the maximum target value with the largest target value in the left table;

[0065] S502: Determine, according to the maximum target value, a value to be matched in the right table that is smaller than the maximum target value;

[0066] S503: Calculate the number of corresponding to-be-matched values ​​in the histogram that are less than the maximum target value according to the frequency of each to-be-matched value in the histogram and the to-be-matched values ​​in the right table that are less than the maximum target value;

[0067] S504: Determine the third grab price according to the number.

[0068] In this implementation, the maximum target value in the left table is obtained, and based on the maximum target value, the to-be-matched value in the right table that is less than the maximum target value is determined, and based on the histogram on the associated column of the right table, the number of rows in the right table whose corresponding to-be-matched value is less than the maximum target value in the left table is calculated, and the third capture price is determined based on the number of rows. The calculation formula for the third capture price is as follows:

[0069]

[0070] Among them, fetchLess is the number of fetches when the value to be matched in the right table is less than the target value in the left table, that is, the number of rows in which the value to be matched in the right table is less than the target value in the left table, less(i) is the number of fetches when the value to be matched in the right table is less than the target value in the left table when the i-th target value is obtained, that is, the number of rows in which the value to be matched in the right table is less than the target value in the left table when the i-th target value is obtained, h() is a linear function, less(leftRows) is the third fetch count, h(less(leftRows)) is the third fetch cost, i is the corresponding number of rows in the left table, and leftRows is the total number of rows in the left table.

[0071] S203: Determine the target grabbing prices in the left table and the right table according to the first grabbing price, the second grabbing price and the third grabbing price.

[0072] In step S203, the corresponding first capture cost, second capture cost and third capture cost are added to obtain the target capture cost in the left table and the right table, wherein the target capture cost is the cost of capturing the to-be-matched value in the right table.

[0073] In this embodiment, the target capture cost is calculated according to the first capture cost, the second capture cost and the third capture cost, and the target capture cost is equivalent to the statistical problem of the size of the table in the left table and the right table.

[0074] Optionally, the first grab price, the second grab price, and the third grab price are added to obtain the target grab price in the left table and the right table.

[0075] In this embodiment, the first capture price, the second capture price and the third capture price are added together to obtain a target capture price. The calculation formula of the target capture price is as follows:

[0076] rightRowFetch=h(leftRows+joinRows+less(leftRows))

[0077] Among them, rightRowFetch is the cost of reading the right table, that is, the target grab cost, h() is a linear function, h(leftRows) is the first grab cost, h(joinRows) is the second grab cost, and h(less(leftRows)) is the third grab cost.

[0078] In another embodiment, i.e., inner join with filter great as the association condition, the cost of reading the right table can be divided into the fourth capture cost of determining the end point and the fifth capture cost of the number of values ​​to be matched that are less than the target value. According to the association condition, the data with values ​​to be matched that are less than the target value is the result set of the join.

[0079] The calculation formula for the fourth-level substitution price is as follows:

[0080]

[0081] Among them, fetchEOF is the number of EOF, and the value of fetchEOF is 1 or 0. fetchGreat is the number of fetches where the end point of the to-be-matched value in the right table is greater than the target value in the left table, that is, the number of rows where the to-be-matched value in the right table is greater than the target value in the left table. fetchEqual is the number of fetches where the to-be-matched value in the right table is equal to the target value in the left table, that is, the number of rows where the to-be-matched value in the right table is equal to the target value in the left table. i is the corresponding number of rows in the left table, h() is a linear function, leftRows is the total number of rows in the left table, and h(leftRows) is the fourth fetch cost.

[0082] The calculation formula for the fifth grab price is as follows:

[0083]

[0084] Among them, fetchLess is the number of fetches when the to-be-matched value in the right table is less than the target value in the left table, i is the corresponding number of rows in the left table, h() is a linear function, leftRows is the total number of rows in the left table, joinRows is the number of rows in which the to-be-matched value in the right table is less than the target value in the left table, and h(joinRows) is the fifth fetch cost.

[0085] In this embodiment, the fourth capture price is added to the fifth capture price to obtain a target capture price. The calculation formula of the target capture price is as follows:

[0086] rightRowFetch=h(leftRows+joinRows)

[0087] In another embodiment, i.e., inner join with filter less as the association condition, the cost of reading the right table can be divided into the sixth capture cost of determining the end point and the seventh capture cost of the number of values ​​to be matched greater than the target value. According to the association condition, the data with values ​​to be matched greater than the target value is the result set of the join.

[0088] The calculation formula for the sixth catch price is as follows:

[0089]

[0090] Among them, fetchEOF is the number of EOF, and the value of fetchEOF is 1 or 0. fetchLess is the number of fetches when the to-be-matched value in the right table is less than the target value in the left table, that is, the number of rows in which the to-be-matched value in the right table is less than the target value in the left table. fetchEqual is the number of fetches when the to-be-matched value in the right table is equal to the target value in the left table, that is, the number of rows in which the to-be-matched value in the right table is equal to the target value in the left table. i is the corresponding number of rows in the left table, h() is a linear function, leftRows is the total number of rows in the left table, and h(leftRows) is the sixth fetch cost.

[0091] The calculation formula for the seventh catch price is as follows:

[0092]

[0093] Among them, fetchGreat is the number of fetches of the termination point where the to-be-matched value in the right table is greater than the target value in the left table, that is, the number of rows where the to-be-matched value in the right table is greater than the target value in the left table, i is the corresponding number of rows in the left table, h() is a linear function, joinRows is the number of rows where the to-be-matched value in the right table is greater than the target value in the left table, and h(joinRows) is the seventh fetch cost.

[0094] In this embodiment, the sixth capture price is added to the seventh capture price to obtain a target capture price. The calculation formula of the target capture price is as follows:

[0095] rightRowFetch=h(leftRows+joinRows)

[0096] S204: Obtain a basic cost of the query statement, and calculate a cost evaluation value of the query statement based on the basic cost and the target capture cost.

[0097] In step S204, the basic cost of the query statement is the cost of reading the left table, the cost of comparing the associated field values ​​of the left table and the right table, the cost of rolling back the right table pointer, the cost of filtering the connection result according to the predicate, and the cost of assembling the connection result according to the matching data. The cost evaluation value of the query statement is calculated based on the basic cost and the target capture cost.

[0098] In this embodiment, the basic cost is added to the target capture cost to obtain the cost evaluation value of the query statement.

[0099] Get the query statement for executing merge join, determine the associated fields in the left table and the right table in the query statement, obtain the histogram of the corresponding column in the associated field in the right table according to the associated fields, determine the first capture substitution value of the to-be-matched value in each row of the corresponding column of the associated field in the left table as the termination point, the second capture substitution value of the to-be-matched value satisfying the join result, and the third capture substitution value of the number of to-be-matched values ​​less than the target value according to the first capture substitution value, the second capture substitution value and the third capture substitution value, determine the target capture substitution value in the left table and the right table according to the first capture substitution value, the second capture substitution value and the third capture substitution value, obtain the basic cost of the query statement, and calculate the cost evaluation value of the query statement according to the basic cost and the target capture substitution value. In the present application, the target capture substitution value in the left table and the right table is calculated using the histogram, and according to the execution principle of merge join, the merge join cost evaluation is converted into a statistical problem based on column statistical information, thereby improving the cost evaluation accuracy of merge join.

[0100] See also Figure 6 , Figure 6 This is a schematic diagram of the structure of a merge join cost evaluation device provided by Embodiment 6 of the present invention. Figure 2-Figure 5 as well as Figure 2-Figure 5 For the convenience of explanation, only the parts related to this embodiment are shown. Figure 6 As shown, the cost evaluation device 60 includes: an acquisition module 61 , a first determination module 62 , a second determination module 63 , and a calculation module 64 .

[0101] The acquisition module 61 is used to acquire a query statement for executing merge join, determine the associated fields in the left table and the right table in the query statement, and acquire a histogram of a corresponding column in the associated field in the right table according to the associated field.

[0102] The first determination module 62 is used to determine, based on the target value and the histogram in each row in the corresponding column of the associated field in the left table, the first capture value when the to-be-matched value in each row in the corresponding column of the associated field in the right table is the termination point, the second capture value when the to-be-matched value satisfies the join result, and the third capture value when the number of to-be-matched values ​​is less than the target value.

[0103] The second determining module 63 is used to determine the target capture price in the left table and the right table according to the first capture price, the second capture price and the third capture price.

[0104] The calculation module 64 is used to obtain the basic cost of the query statement, and calculate the cost evaluation value of the query statement according to the basic cost and the target capture cost.

[0105] Optionally, the acquisition module 61 includes:

[0106] The determination unit is used to determine the value in each row of the corresponding column in the associated field in the right table based on the associated field.

[0107] The construction unit is used to construct a histogram of the corresponding column in the associated field based on the values ​​in each row.

[0108] Optionally, the first determining module 62 includes:

[0109] A first determination unit is used to obtain the number of rows in the left table, and determine the first capture cost of the to-be-matched value as the termination point according to the number of rows in the left table;

[0110] A second determination unit, used to determine a second capture price according to the number of rows in the right table whose to-be-matched value is equal to the target value;

[0111] The third determining unit is used to calculate a third capture cost for the number of to-be-matched values ​​being less than the target value according to the frequency of each to-be-matched value in the histogram.

[0112] Optionally, the third determining unit includes:

[0113] The first acquisition subunit is used to acquire the maximum target value whose target value in the left table is the largest;

[0114] The second acquisition subunit is used to determine the to-be-matched value in the right table that is smaller than the maximum target value according to the maximum target value;

[0115] The first calculation subunit is used to calculate the number of corresponding to-be-matched values ​​in the histogram that are less than the maximum target value according to the frequency of each to-be-matched value in the histogram and the to-be-matched values ​​that are less than the maximum target value in the right table;

[0116] The second calculation subunit is used to determine the third capture price according to the number.

[0117] Optionally, the second determining module 63 includes:

[0118] The adding unit is used to add the first grabbing price, the second grabbing price and the third grabbing price to obtain the target grabbing price in the left table and the right table.

[0119] It should be noted that the information interaction, execution process and other contents between the above-mentioned modules are based on the same concept as the method embodiment of the present application. Their specific functions and technical effects can be found in the method embodiment part and will not be repeated here.

[0120] Figure 7 This is a schematic diagram of the structure of a computer device provided in Example 7 of this application. Figure 7 As shown, the computer device of this embodiment includes: at least one processor ( Figure 7Only one is shown in the figure), a memory, and a computer program stored in the memory and executable on at least one processor, wherein when the processor executes the computer program, the steps of any of the above-mentioned merge join cost evaluation method embodiments are implemented.

[0121] The computer device may include, but is not limited to, a processor and a memory. Those skilled in the art will appreciate that Figure 7 This is merely an example of a computer device and does not constitute a limitation on the computer device. The computer device may include more or fewer components than those shown in the figure, or a combination of certain components, or different components, for example, it may also include a network interface, etc.

[0122] The processor may be a CPU, or other general-purpose processors, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. A general-purpose processor may be a microprocessor or any conventional processor, etc.

[0123] The memory includes a readable storage medium, an internal memory, etc., wherein the internal memory may be the memory of a computer device, and the internal memory provides an environment for the operation of an operating system and computer-readable instructions in the readable storage medium. The readable storage medium may be a hard disk of a computer device, and in other embodiments may also be an external storage device of a computer device, for example, a plug-in hard disk, a smart memory card (Smart Media Card, SMC), a secure digital (Secure Digital, SD) card, a flash card (Flash Card), etc. equipped on a computer device. Further, the memory may also include both an internal storage unit of a computer device and an external storage device. The memory is used to store an operating system, an application program, a boot loader (BootLoader), data, and other programs, such as program codes of computer programs, etc. The memory may also be used to temporarily store data that has been output or is to be output.

[0124] The technicians in the relevant field can clearly understand that for the convenience and simplicity of description, only the division of the above-mentioned functional units and modules is used as an example. In practical applications, the above-mentioned function allocation can be completed by different functional units and modules as needed, that is, the internal structure of the device is divided into different functional units or modules to complete all or part of the functions described above. The functional units and modules in the embodiment can be integrated into a processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The above-mentioned integrated unit can be implemented in the form of hardware or in the form of software functional units. In addition, the specific names of the functional units and modules are only for the convenience of distinguishing each other, and are not used to limit the scope of protection of this application. The specific working process of the units and modules in the above-mentioned device can refer to the corresponding process in the aforementioned method embodiment, which will not be repeated here. If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on such understanding, the present application implements all or part of the processes in the above-mentioned embodiment method, which can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a computer-readable storage medium, and the computer program can implement the steps of the above-mentioned method embodiment when executed by the processor. Among them, the computer program includes computer program code, and the computer program code can be in source code form, object code form, executable file or some intermediate form. The computer-readable medium may at least include: any entity or device capable of carrying computer program code, recording medium, computer memory, read-only memory (ROM), random access memory (RAM), electric carrier signal, telecommunication signal and software distribution medium. For example, a USB flash drive, a mobile hard disk, a magnetic disk or an optical disk. In some jurisdictions, according to legislation and patent practice, computer-readable media cannot be electric carrier signals and telecommunication signals.

[0125] The present application implements all or part of the processes in the above-mentioned embodiment method, and may also be completed through a computer program product. When the computer program product runs on a computer device, the computer device can implement the steps in the above-mentioned method embodiment when executing the computer program product.

[0126] In the above embodiments, the description of each embodiment has its own emphasis. For parts that are not described or recorded in detail in a certain embodiment, reference can be made to the relevant descriptions of other embodiments.

[0127] Those of ordinary skill in the art will appreciate that the units and algorithm steps of each example described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professional and technical personnel can use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of this application.

[0128] In the embodiments provided in the present application, it should be understood that the disclosed devices / computer equipment and methods can be implemented in other ways. For example, the device / computer equipment embodiments described above are only schematic, for example, the division of modules or units is only a logical function division, and there may be other division methods in actual implementation, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of devices or units, which can be electrical, mechanical or other forms.

[0129] The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0130] The above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit them. Although the present application has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. These modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present application, and should all be included in the protection scope of the present application.

Claims

1. A merge join cost evaluation method, characterized in that: The cost assessment method includes: Obtain a query statement for executing merge join, determine the associated fields in the left table and the right table in the query statement, and obtain a histogram of a corresponding column in the associated field in the right table according to the associated field; According to the target value in each row in the column corresponding to the associated field in the left table and the histogram, determine a first capture cost when the to-be-matched value in each row in the column corresponding to the associated field in the right table is a termination point, a second capture cost when the to-be-matched value satisfies the join result, and a third capture cost when the number of to-be-matched values ​​is less than the target value; Determine the target capture price in the left table and the right table according to the first capture price, the second capture price, and the third capture price; A basic cost of the query statement is obtained, and a cost evaluation value of the query statement is calculated based on the basic cost and the target capture cost.

2. The cost evaluation method according to claim 1, characterized in that: The step of obtaining a histogram of a corresponding column in the associated field in the right table according to the associated field includes: According to the associated field, determine the value in each row of the corresponding column in the associated field in the right table; Based on the values ​​in each row, build a histogram for the corresponding column in the associated field.

3. The cost evaluation method according to claim 1, characterized in that: The method of determining, according to the target value in each row in the column corresponding to the associated field in the left table and the histogram, a first capture cost in which the to-be-matched value in each row in the column corresponding to the associated field in the right table is a termination point, a second capture cost in which the to-be-matched value satisfies a join result, and a third capture cost in which the to-be-matched value is less than the number of the target values, includes: Obtain the number of rows in the left table, and determine the first capture cost of the to-be-matched value as the termination point according to the number of rows in the left table; Determine the second capture price according to the number of rows in the right table whose to-be-matched value is equal to the target value; A third capture cost is calculated according to the frequency of each to-be-matched value in the histogram, where the to-be-matched value is less than the target value.

4. The cost evaluation method according to claim 3, characterized in that: The calculating, according to the frequency of each to-be-matched value in the histogram, a third capture cost of the number of the to-be-matched values ​​being less than the target value comprises: Get the maximum target value with the largest target value in the left table; According to the maximum target value, determine the value to be matched in the right table that is smaller than the maximum target value; According to the frequency of each to-be-matched value in the histogram and the to-be-matched value in the right table that is less than the maximum target value, calculate the number of corresponding to-be-matched values ​​in the histogram that are less than the maximum target value; According to the number, the third grab price is determined.

5. The cost evaluation method according to claim 1, characterized in that: The determining the target capture prices in the left table and the right table according to the first capture price, the second capture price, and the third capture price includes: The first capture price, the second capture price, and the third capture price are added together to obtain the target capture prices in the left table and the right table.

6. A merge join cost evaluation device, characterized in that: The cost evaluation device comprises: An acquisition module is used to acquire a query statement for executing merge join, determine the associated fields in the left table and the right table in the query statement, and acquire a histogram of a corresponding column in the associated field in the right table according to the associated field; A first determination module is used to determine, according to the target value in each row in the column corresponding to the associated field in the left table and the histogram, a first capture value that the to-be-matched value in each row in the column corresponding to the associated field in the right table is a termination point, a second capture value that the to-be-matched value satisfies a join result, and a third capture value that the number of to-be-matched values ​​is less than the target value; A second determination module is used to determine the target capture price in the left table and the right table according to the first capture price, the second capture price and the third capture price; The calculation module is used to obtain the basic cost of the query statement, and calculate the cost evaluation value of the query statement according to the basic cost and the target capture cost.

7. The merge join cost evaluation device according to claim 6, wherein: The first determining module comprises: A first determining unit is used to obtain the number of rows in the left table, and determine a first capture cost of the to-be-matched value as a termination point according to the number of rows in the left table; A second determination unit, configured to determine a second capture price according to the number of rows in the right table whose to-be-matched value is equal to the target value; The third determining unit is used to calculate a third capture cost for the number of the to-be-matched values ​​being less than the target value according to the frequency of each to-be-matched value in the histogram.

8. The merge join cost evaluation device according to claim 7, wherein: The third determining unit includes: Get subunit, used to get the maximum target value with the largest target value in the left table; A first determining subunit is used to determine, according to the maximum target value, a to-be-matched value in the right table that is smaller than the maximum target value; A calculation subunit, configured to calculate the number of corresponding to-be-matched values ​​in the histogram that are less than the maximum target value according to the frequency of each to-be-matched value in the histogram and the to-be-matched values ​​in the right table that are less than the maximum target value; The second determining subunit is used to determine a third capture price according to the number.

9. A computer device, characterized in that: The computer device includes a processor, a memory, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, the cost evaluation method according to any one of claims 1 to 5 is implemented.

10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the cost evaluation method according to any one of claims 1 to 5 is implemented.

Images