CN104504114A - Multi-hash table-based relational operation optimization method, device and system - Google Patents
Multi-hash table-based relational operation optimization method, device and system Download PDFInfo
- Publication number
- CN104504114A CN104504114A CN201410844188.8A CN201410844188A CN104504114A CN 104504114 A CN104504114 A CN 104504114A CN 201410844188 A CN201410844188 A CN 201410844188A CN 104504114 A CN104504114 A CN 104504114A
- Authority
- CN
- China
- Prior art keywords
- hash function
- doha
- node
- condition
- wished
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/90—Details of database functions independent of the retrieved data types
- G06F16/901—Indexing; Data structures therefor; Storage structures
- G06F16/9014—Indexing; Data structures therefor; Storage structures hash tables
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Software Systems (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The embodiment of the invention provides a multi-hash table-based relational operation optimization method, a multi-hash table-based relational operation optimization device and a multi-hash table-based relational operation optimization system. The method comprises the following steps of receiving a query sentence from a client, and analyzing the query sentence to judge whether a connecting condition exists in the query sentence or not; if the connecting condition exists, selecting N tables from relational tables as structure tables according to the connecting condition, and selecting at least one hash function for each structure table in the N structure tables; for each hash function, scanning the corresponding structure table to acquire a multi-hash node corresponding to the hash table; for each hash function, querying whether a record of a connecting condition matched with a detection table exists or not in the multi-hash node corresponding to the hash function, and if the record exists, transmitting the record to the client. According to the multi-hash table-based relational operation optimization method, the multi-hash table-based relational operation optimization device and the multi-hash table-based relational operation optimization system, query performance is improved.
Description
Technical field
The embodiment of the present invention relates to data query technique, particularly relates to a kind of relational operation optimization method based on many Hash tables, device and system.
Background technology
In relevant database, OR and UNION is two important relational operation operators, wherein, OR is usually used in the querying condition connected in multiple condition inquiry when relating to multiple relation table, and UNION carries out union operation to the query results relating to multiple table.
In prior art, when multiple table connects, if between condition of contact be the relation of OR, the mode of the nested circulation of usual employing connects, or in the compound query comprising multiple subquery, if the relation table that these subqueries relate to is identical, but when filtercondition is different, when carrying out OR operation to subquery, usually adopting and the relation table related in subquery is carried out Multiple-Scan filtration, or adopt the mode being optimized query statement and merging to carry out computing.In addition, show and after other multiple list catenation, if carry out UNION operation, usually adopt Hash to connect (Hash Join) to each connection, then each result set is carried out order and add merging, finally carry out duplicate removal and operate for one.
But, in the prior art, for the related operation of OR and UNION operator, when connecting multilist, need to carry out Multiple-Scan to the relation table related to, and adopt the mode of nested circulation to carry out attended operation, make query performance poor.
Summary of the invention
The embodiment of the present invention provides a kind of relational operation optimization method based on many Hash tables, device and system, improves query performance.
First aspect, the invention provides a kind of relational operation optimization method based on many Hash tables, comprising:
Receive the query statement that client sends, resolve described query statement to judge whether there is condition of contact in described query statement; Wherein, described condition of contact comprises OR or UNION;
If there is described condition of contact, then according to described condition of contact, in relation table, select N number of table as structure table, and at least one hash function selected by each the structure table be respectively in described N number of structure table; Wherein, N is integer, and is greater than 0;
For each hash function, corresponding structure table is scanned, wish node to obtain the Doha corresponding with described Hash table;
For each hash function, according to described hash function, wish in node in the described Doha corresponding with described hash function, whether inquiry exists the record of the condition of contact mated with detection table, if exist, then described record is sent to described client; Wherein, described detection table is other relation table except structure table.
In conjunction with first aspect, in the first possible implementation of first aspect, described for each hash function, scan corresponding structure table, to obtain after the Doha corresponding with described Hash table wish node, described method also comprises:
Described detection table is scanned, obtains the scan node corresponding with described detection table;
For each hash function, the Doha that described scan node is corresponding with described hash function is wished node and carry out that Doha is uncommon to be connected, connected node is wished in the Doha generating correspondence;
For each hash function, judge that Doha that described hash function is corresponding is wished execution route corresponding to connected node and whether met querying condition;
Then described according to described hash function, wish in node in the described Doha corresponding with described hash function, whether inquiry exists the record of the condition of contact mated with detection table, specifically comprises:
The Doha that described hash function is corresponding if judge is wished execution route corresponding to connected node and is met described querying condition, then according to described hash function, wish in node in the described Doha corresponding with described hash function, whether inquiry exists the record of the condition of contact mated with detection table.
In conjunction with the first possible implementation of first aspect, in the implementation that the second of first aspect is possible, described for each hash function, judge that Doha that described hash function is corresponding is wished execution route corresponding to connected node and whether met querying condition, specifically comprise:
For each hash function, determine that execution route corresponding to connected node is wished in the Doha that described hash function is corresponding;
The weights of connected node are wished in each Doha obtained on described execution route, and are added by each described weights, obtain the Executing Cost of described execution route; Wherein, described weights are for representing that corresponding Doha is wished Doha that connected node is adjacent and wished Executing Cost between connected node;
Whether judge the described Executing Cost of the execution route that described hash function is corresponding, be the minimum value in the Executing Cost of all execution route; If so, the execution route that connected node is wished corresponding in the Doha that then described hash function is corresponding meets querying condition.
Second aspect, the invention provides a kind of relational operation optimization device based on many Hash tables, comprising:
Transceiver module, for receiving the query statement that client sends;
Judge module, for resolving described query statement to judge whether there is condition of contact in described query statement; Wherein, described condition of contact comprises OR or UNION;
Processing module, for when described judge module judges to there is described condition of contact, according to described condition of contact, selects N number of table as structure table in relation table, and at least one hash function selected by each the structure table be respectively in described N number of structure table; Wherein, N is integer, and is greater than 0;
Scan module, for for each hash function, scans corresponding structure table, wishes node to obtain the Doha corresponding with described Hash table;
Described judge module, also for for each hash function, according to described hash function, is wished in node in the described Doha corresponding with described hash function, and whether inquiry exists the record of the condition of contact mated with detection table; Described transceiver module is also for judging the record that there is the condition of contact mated with detection table during at described judge module, described record is sent to described client; Wherein, described detection table is other relation table except structure table.
In conjunction with second aspect, in the first possible implementation of second aspect, described scan module, also for scanning described detection table, obtains the scan node corresponding with described detection table;
Described processing module is also for for each hash function, and the Doha that described scan node is corresponding with described hash function is wished node and carry out that Doha is uncommon to be connected, connected node is wished in the Doha generating correspondence;
Described judge module, also for for each hash function, judges that Doha that described hash function is corresponding is wished execution route corresponding to connected node and whether met querying condition;
If then described judge module is wished execution route corresponding to connected node specifically for the Doha that described hash function is corresponding and is met described querying condition, then according to described hash function, wish in node in the described Doha corresponding with described hash function, whether inquiry exists the record of the condition of contact mated with detection table.
In conjunction with the first possible implementation of second aspect, in the implementation that the second of second aspect is possible, described judge module also comprises: determining unit, acquiring unit and judging unit;
Wherein, described determining unit is used for for each hash function, determines that execution route corresponding to connected node is wished in the Doha that described hash function is corresponding;
Described acquiring unit, wishes the weights of connected node for each Doha obtained on described execution route, and is added by each described weights, obtains the Executing Cost of described execution route; Wherein, described weights are for representing that corresponding Doha is wished Doha that connected node is adjacent and wished Executing Cost between connected node;
Whether described judging unit, for judging the described Executing Cost of the execution route that described hash function is corresponding, is the minimum value in the Executing Cost of all execution route; If so, the execution route that connected node is wished corresponding in the Doha that then described hash function is corresponding meets querying condition.
The third aspect, the invention provides a kind of relational operation optimization system based on many Hash tables, comprising: any one relational operation optimization device based on many Hash tables of the first the second to second aspect of client and second aspect, second aspect.
The relational operation optimization method based on many Hash tables that the embodiment of the present invention provides, device and system, by receiving the query statement that client sends, resolve query statement to judge whether there is condition of contact in this query statement; Wherein, condition of contact comprises OR or UNION; If there is condition of contact, then according to condition of contact, in relation table, select N number of table as structure table, and at least one hash function selected by each the structure table be respectively in N number of structure table, for each hash function, corresponding structure table is scanned, wishes node to obtain the Doha corresponding with Hash table; For each hash function, according to hash function, wish in node in the Doha corresponding with hash function, whether inquiry exists the record of the condition of contact mated with detection table, if exist, then record is sent to client; Wherein, detection table is other relation table except structure table.Due in the condition of contact of OR or UNION, according to condition of contact, at least one hash function is selected to structure table, node is wished to generate the Doha corresponding with hash function, and according to hash function, wish in node in the Doha corresponding with hash function, whether inquiry exists the record of the condition of contact mated with detection table, avoids in prior art and carries out Multiple-Scan to the relation table related to, and adopt the mode of nested circulation to carry out the mode of attended operation, improve the performance of inquiry.
Accompanying drawing explanation
In order to be illustrated more clearly in the embodiment of the present invention or technical scheme of the prior art, be briefly described to the accompanying drawing used required in embodiment or description of the prior art below, apparently, accompanying drawing in the following describes is some embodiments of the present invention, for those of ordinary skill in the art, under the prerequisite not paying creative work, other accompanying drawing can also be obtained according to these accompanying drawings.
Fig. 1 is the schematic flow sheet of the relational operation optimization method embodiment one that the present invention is based on many Hash tables;
Fig. 2 is the schematic flow sheet of the relational operation optimization method embodiment two that the present invention is based on many Hash tables;
Fig. 3 is the structural representation of the relational operation optimization device embodiment one that the present invention is based on many Hash tables;
Fig. 4 is the structural representation of the relational operation optimization device embodiment two that the present invention is based on many Hash tables;
Fig. 5 is the structural representation of server example one provided by the invention.
Embodiment
For making the object of the embodiment of the present invention, technical scheme and advantage clearly, below in conjunction with the accompanying drawing in the embodiment of the present invention, technical scheme in the embodiment of the present invention is clearly and completely described, obviously, described embodiment is the present invention's part embodiment, instead of whole embodiments.Based on the embodiment in the present invention, those of ordinary skill in the art, not making the every other embodiment obtained under creative work prerequisite, belong to the scope of protection of the invention.
Fig. 1 is the schematic flow sheet of the relational operation optimization method embodiment one that the present invention is based on many Hash tables.Embodiments provide a kind of relational operation optimization method based on many Hash tables, the method can be performed by the device performed arbitrarily based on the relational operation optimization method of many Hash tables, and this device can pass through software and/or hardware implementing.In the present embodiment, this device can be integrated in database server.As shown in Figure 1, the method for the present embodiment can comprise:
The query statement that step 101, reception client send, resolves query statement to judge whether there is condition of contact in query statement; Wherein, condition of contact comprises OR or UNION.
In the present embodiment, user can send query statement by client to database server, to ask to carry out corresponding query manipulation to database.Alternatively, after database server receives the query statement of client transmission, first check that whether the grammer of query statement is correct, if grammer is correct, again this query statement is resolved, judge whether include OR or UNION operator in query statement according to analysis result, if query statement grammer is incorrect, then carry out the prompting of grammar mistake.Certainly, also the grammer of default query statement correctly and not syntax check can be carried out.
If step 102 exists condition of contact, then according to condition of contact, in relation table, select N number of table as structure table, and at least one hash function selected by each the structure table be respectively in N number of structure table; Wherein, N is integer, and is greater than 0.
In the present embodiment, if include condition of contact in query statement, database server can, according to the difference of condition of contact, select at least one table as structure table in condition of contact in the relation table related to.In concrete implementation procedure, owing to being scanned by structure table, after generating corresponding Hash table, be stored in internal memory by Hash table, therefore, in order to save memory headroom, the less table in general choice relation table is as structure table.In addition, at least one hash function selected by each structure table that database server is respectively in these structure tables, to scan structure table.Such as: when querying condition is: t1.c1=t2.c1or t1.c2=t2.c2, during the data item that namely the first row c1 of question blank t1 the second row c2 that is equal with the first row c1 of table t2 or that show t1 is equal with the second row c2 showing t2, suppose that the data item shown in t2 is less, that is, table t2 is less table, then will show t2 as structure table, and select two hash functions for showing t2, scan with his-and-hers watches t2.
Step 103, for each hash function, corresponding structure table to be scanned, wish node to obtain the Doha corresponding with Hash table.
In the present embodiment, utilize the hash function on the row that to do between relation table and connect, generate Doha and wish node.Particularly, according to the difference of condition of contact, for each structure table, likely only have a hash function, also likely have multiple hash function, for each hash function, all scan according to the structure table of hash function to response, thus node is wished in the Doha obtaining correspondence.Such as: for table is after t2 selects two hash functions, for each hash function, equal his-and-hers watches t2 scans, node is wished to obtain the Doha corresponding with Hash table, therefore, after his-and-hers watches t2 scans, two different Hash tables can be obtained, and node is wished in multiple Doha corresponding to two different Hash tables.
Step 104, for each hash function, according to hash function, wish in node in the Doha corresponding with hash function, whether inquiry exists the record of condition of contact mate with detection table, if existence, then record is sent to client; Wherein, detection table is other relation table except structure table.
In the present embodiment, according to hash function, corresponding structure table is scanned, after node is wished in the Doha that generation Hash table is corresponding, database server starts to read detection table, for each data in detection table, all use hash function doing on the row connected, to locate corresponding Hash node, after finding corresponding Hash node, just carry out inquiring about the record that whether there is the condition of contact mated with detection table.Meet the relative recording of condition of contact if inquire, then this record is sent to client, checks for user.
For example, the query statement when condition of contact includes OR operator is:
“select*from t1,t2where t1.c1＝t2.c1or t1.c2＝t2.c2”
Concrete, in the prior art, a kind of specific implementation that the mode usually adopting nested query to connect to this query statement is carried out is:
As can be seen here, in the prior art, need his-and-hers watches t1 and table t2 to carry out Multiple-Scan, make query performance lower.And in the present embodiment, t2 will be shown as structure table, according to condition of contact t1.c1=t2.c1 ort1.c2=t2.c2, for structure table t2 selects two hash functions, to generate two corresponding Hash tables, and t1 will be shown as detection table, according to hash function, detection table t1 is carried out in two Hash tables generated the coupling of data, wherein, to the specific implementation of a kind of executive plan of this query statement is:
As can be seen here, his-and-hers watches t1 and table t2 is not needed to carry out Multiple-Scan in the present embodiment, only need his-and-hers watches t2 order run-down, and wish node for table t2 generates Doha, his-and-hers watches t1 order run-down again, generate after scan node, node is wished to Doha and to do with scan node that Doha is uncommon to be connected, greatly can improve query performance.
Query statement when condition of contact includes UNION operator is:
“select*from a join b on a.c1＝b.c1 union select*from a join c ona.c2＝c.c2”
Concrete, in the prior art, usually need his-and-hers watches a to do twice sweep, after completing Hash connection, also need each result set to be carried out order and add merging, finally carry out duplicate removal operation, wherein, to the specific implementation of a kind of executive plan of this query statement be:
As can be seen here, in the prior art, the computing for the condition of contact including UNION operator is comparatively complicated, makes query performance lower.And in the present embodiment, only need his-and-hers watches a to carry out single pass, and perform that Doha is uncommon to be connected, wherein, to the specific implementation of a kind of executive plan of this query statement be:
As can be seen here, pass through sequentially scan table b and table c in the present embodiment, and according to hash function his-and-hers watches b and the Hash table showing c generation correspondence, carry out order scanning by his-and-hers watches a, and carry out the uncommon connection in Doha, greatly can improve query performance thus.
The relational operation optimization method based on many Hash tables that the embodiment of the present invention provides, by receiving the query statement that client sends, resolves query statement to judge whether there is condition of contact in this query statement; Wherein, condition of contact comprises OR or UNION; If there is condition of contact, then according to condition of contact, in relation table, select N number of table as structure table, and at least one hash function selected by each the structure table be respectively in N number of structure table, for each hash function, corresponding structure table is scanned, wishes node to obtain the Doha corresponding with Hash table; For each hash function, according to hash function, wish in node in the Doha corresponding with hash function, whether inquiry exists the record of the condition of contact mated with detection table, if exist, then record is sent to client; Wherein, detection table is other relation table except structure table.Due in the condition of contact of OR or UNION, according to condition of contact, at least one hash function is selected to structure table, node is wished to generate the Doha corresponding with hash function, and according to hash function, wish in node in the Doha corresponding with hash function, whether inquiry exists the record of the condition of contact mated with detection table, avoids in prior art and carries out Multiple-Scan to the relation table related to, and adopt the mode of nested circulation to carry out the mode of attended operation, improve the performance of inquiry.
Fig. 2 is the schematic flow sheet of the relational operation optimization method embodiment two that the present invention is based on many Hash tables, and the present embodiment, on the basis of the relational operation optimization method embodiment one based on many Hash tables, to the embodiment choosing optimal path, elaborates.As shown in Figure 2, the method for the present embodiment can comprise:
Step 201, detection table to be scanned, obtain the scan node corresponding with detection table.
In the present embodiment, scan structure table according to hash function, obtain after the Doha corresponding with hash function wish node, database server also will scan detection table, to obtain scan node.
Step 202, for each hash function, Doha corresponding with hash function for scan node is wished node and carry out that Doha is uncommon to be connected, generate corresponding Doha and wish connected node.
In the present embodiment, because database server is that at least one hash function selected by each structure table in N number of structure table, therefore, for each hash function, database server scans detection table according to this hash function, after obtaining the scan node corresponding with detection table, the Doha that scan node is corresponding with this hash function is wished node and carry out that Doha is uncommon to be connected, wish connected node with the Doha generating correspondence.
Step 203, for each hash function, judge that Doha that hash function is corresponding is wished execution route corresponding to connected node and whether met querying condition.If meet, then perform step 204, otherwise, perform step 205.
In the present embodiment, because execution route has many, such as: path etc. is wished in loop nesting path, Doha, wherein, the make due to hash function has multiple, and therefore, path is wished in Doha also can have many.In concrete implementation procedure, by judging the Executing Cost in all paths, the minimum paths of Executing Cost can be obtained as optimal path by comparing.Particularly, can by determining that execution route corresponding to connected node is wished in the Doha that hash function is corresponding, the weights of connected node are wished by each Doha obtained on execution route, and each weights are added, to obtain the Executing Cost of execution route, wherein, these weights are for representing that the Executing Cost between connected node is wished in two adjacent Doha, by judging that whether the Executing Cost of the execution route that this hash function is corresponding is the minimum value in the Executing Cost of all execution route, if, the execution route that connected node is wished corresponding in the Doha that then hash function is corresponding meets querying condition, and execution route corresponding to this hash function is the optimal path in all execution routes.Such as: in loop nesting path, the weights of first node are 3, the weights of second node are 4.5, it is 1 that the weights of wishing node in Doha in path 1 are wished in Doha, the weights that connected node is wished in Doha are 2.5, it is 1.5 that the weights of wishing node in Doha in path 2 are wished in Doha, and the weights that connected node is wished in Doha are 3, then carry out optimal path relatively after, know that Doha is wished path 1 and met querying condition, be optimal path.For the mode obtaining optimal path, the present embodiment is not particularly limited at this.
Step 204, according to hash function, wish in node in the Doha corresponding with hash function, whether inquiry exists the record of the condition of contact mated with detection table.
In the present embodiment, when judging that Doha that hash function is corresponding is wished execution route corresponding to connected node and met querying condition and know that Doha is wished after path is optimal path, can then according to this hash function, wish in node by utilizing detection table in the Doha corresponding with hash function, the record of the condition of contact met is mated in inquiry with detection table, if inquire, then and the record this being met condition of contact is sent to client by selecting the optimal path determined.
Step 205, according to other execution route, in relation table, inquire about the record whether existing and meet condition of contact.
In the present embodiment, if the execution route that hash function is corresponding is not optimal path, can by other execution route, it can be such as loop nesting path etc., the record whether existing and meet condition of contact is inquired about in relation table, if exist, then the record this being met condition of contact is sent to client by the path selected.
The relational operation optimization method based on many Hash tables that the embodiment of the present invention provides, by receiving the query statement that client sends, resolves query statement to judge whether there is condition of contact in this query statement; Wherein, condition of contact comprises OR or UNION; If there is condition of contact, then according to condition of contact, in relation table, select N number of table as structure table, and at least one hash function selected by each the structure table be respectively in N number of structure table, for each hash function, corresponding structure table is scanned, wishes node to obtain the Doha corresponding with Hash table; For each hash function, according to hash function, wish in node in the Doha corresponding with hash function, whether inquiry exists the record of the condition of contact mated with detection table, if exist, then record is sent to client; Wherein, detection table is other relation table except structure table.Due in the condition of contact of OR or UNION, according to condition of contact, at least one hash function is selected to structure table, node is wished to generate the Doha corresponding with hash function, and according to hash function, wish in node in the Doha corresponding with hash function, whether inquiry exists the record of the condition of contact mated with detection table, avoids in prior art and carries out Multiple-Scan to the relation table related to, and adopt the mode of nested circulation to carry out the mode of attended operation, improve the performance of inquiry.In addition, by selecting an optimal path to inquire about in mulitpath, search efficiency and query performance can greatly be improved.
Fig. 3 is the structural representation of the relational operation optimization device embodiment one that the present invention is based on many Hash tables, as shown in Figure 3, the relational operation optimization device based on many Hash tables that the embodiment of the present invention provides comprises transceiver module 11, judge module 12, processing module 13 and scan module 14.
Wherein, transceiver module 11 is for receiving the query statement of client transmission; Judge module 12 is for resolving described query statement to judge whether there is condition of contact in described query statement; Wherein, described condition of contact comprises OR or UNION; Processing module 13 is for when described judge module 12 judges to there is described condition of contact, according to described condition of contact, in relation table, select N number of table as structure table, and at least one hash function selected by each the structure table be respectively in described N number of structure table; Wherein, N is integer, and is greater than 0; Scan module 14, for for each hash function, scans corresponding structure table, wishes node to obtain the Doha corresponding with described Hash table; Described judge module 12, also for for each hash function, according to described hash function, is wished in node in the described Doha corresponding with described hash function, and whether inquiry exists the record of the condition of contact mated with detection table; Described transceiver module 11 is also for judging the record that there is the condition of contact mated with detection table during at described judge module 12, described record is sent to described client; Wherein, described detection table is other relation table except structure table.
The relational operation optimization device based on many Hash tables that the embodiment of the present invention provides, by receiving the query statement that client sends, resolves query statement to judge whether there is condition of contact in this query statement; Wherein, condition of contact comprises OR or UNION; If there is condition of contact, then according to condition of contact, in relation table, select N number of table as structure table, and at least one hash function selected by each the structure table be respectively in N number of structure table, for each hash function, corresponding structure table is scanned, wishes node to obtain the Doha corresponding with Hash table; For each hash function, according to hash function, wish in node in the Doha corresponding with hash function, whether inquiry exists the record of the condition of contact mated with detection table, if exist, then record is sent to client; Wherein, detection table is other relation table except structure table.Due in the condition of contact of OR or UNION, according to condition of contact, at least one hash function is selected to structure table, node is wished to generate the Doha corresponding with hash function, and according to hash function, wish in node in the Doha corresponding with hash function, whether inquiry exists the record of the condition of contact mated with detection table, avoids in prior art and carries out Multiple-Scan to the relation table related to, and adopt the mode of nested circulation to carry out the mode of attended operation, improve the performance of inquiry.
Alternatively, described scan module 14, also for scanning described detection table, obtains the scan node corresponding with described detection table; Described processing module 13 is also for for each hash function, and the Doha that described scan node is corresponding with described hash function is wished node and carry out that Doha is uncommon to be connected, connected node is wished in the Doha generating correspondence; Described judge module 12, also for for each hash function, judges that Doha that described hash function is corresponding is wished execution route corresponding to connected node and whether met querying condition; If then described judge module 12 is wished execution route corresponding to connected node specifically for the Doha that described hash function is corresponding and is met described querying condition, then according to described hash function, wish in node in the described Doha corresponding with described hash function, whether inquiry exists the record of the condition of contact mated with detection table.
The relational operation optimization device based on many Hash tables of the present embodiment, may be used for the technical scheme performing the relational operation optimization method based on many Hash tables that any embodiment of the present invention provides, it realizes principle and technique effect is similar, repeats no more herein.
Fig. 4 is the structural representation of the relational operation optimization device embodiment two that the present invention is based on many Hash tables, as shown in Figure 4, the present embodiment is on basis embodiment illustrated in fig. 3, and described judge module also comprises: determining unit 121, acquiring unit 122 and judging unit 123.
Wherein, described determining unit 121, for for each hash function, determines that execution route corresponding to connected node is wished in the Doha that described hash function is corresponding; Described acquiring unit 122 wishes the weights of connected node for each Doha obtained on described execution route, and is added by each described weights, obtains the Executing Cost of described execution route; Wherein, described weights are for representing that corresponding Doha is wished Doha that connected node is adjacent and wished Executing Cost between connected node; Whether described judging unit 123, for judging the described Executing Cost of the execution route that described hash function is corresponding, is the minimum value in the Executing Cost of all execution route; If so, the execution route that connected node is wished corresponding in the Doha that then described hash function is corresponding meets querying condition.
The relational operation optimization device based on many Hash tables of the present embodiment, may be used for the technical scheme performing the relational operation optimization method based on many Hash tables that any embodiment of the present invention provides, it realizes principle and technique effect is similar, repeats no more herein.
The present invention also provides a kind of relational operation optimization system based on many Hash tables, comprise: client and the relational operation optimization device based on many Hash tables, wherein, relational operation optimization device based on many Hash tables can adopt the device provided in the apparatus for forwarding message embodiment shown in Fig. 3 or Fig. 4, and its concrete structure and function repeat no more herein.
Fig. 5 is the structural representation of server example one provided by the invention.As shown in Figure 5, the server that the embodiment of the present invention provides comprises receiver 21, processor 22 and transmitter 23.
Wherein, receiver 21 is for receiving the query statement of client transmission; Processor 22 is for resolving described query statement to judge whether there is condition of contact in described query statement; Wherein, described condition of contact comprises OR or UNION; If described processor 22 is also for existing described condition of contact, then according to described condition of contact, in relation table, select N number of table as structure table, and at least one hash function selected by each the structure table be respectively in described N number of structure table; Wherein, N is integer, and is greater than 0; Described processor 22 also for for each hash function, scans corresponding structure table, wishes node to obtain the Doha corresponding with described Hash table; Described processor 22 is also for for each hash function, according to described hash function, wish in node in the described Doha corresponding with described hash function, whether inquiry exists the record of the condition of contact mated with detection table, described transmitter 23 is for judging the record that there is the condition of contact mated with detection table during at described processor 22, described record is sent to described client; Wherein, described detection table is other relation table except structure table.
The server of the present embodiment, may be used for the technical scheme performing the relational operation optimization method based on many Hash tables that any embodiment of the present invention provides, it realizes principle and technique effect is similar, repeats no more herein.
Alternatively, described processor 22, also for scanning described detection table, obtains the scan node corresponding with described detection table; Described processor 22 is also for for each hash function, and the Doha that described scan node is corresponding with described hash function is wished node and carry out that Doha is uncommon to be connected, connected node is wished in the Doha generating correspondence; Described processor 22, also for for each hash function, judges that Doha that described hash function is corresponding is wished execution route corresponding to connected node and whether met querying condition; If described processor 22 is also for judging that Doha that described hash function is corresponding is wished execution route corresponding to connected node and met described querying condition, then according to described hash function, wish in node in the described Doha corresponding with described hash function, whether inquiry exists the record of the condition of contact mated with detection table.
Alternatively, described processor 22 also for for each hash function, determines that execution route corresponding to connected node is wished in the Doha that described hash function is corresponding; The weights of connected node also wished by described processor 22 for each Doha obtained on described execution route, and are added by each described weights, obtain the Executing Cost of described execution route; Wherein, described weights are for representing that corresponding Doha is wished Doha that connected node is adjacent and wished Executing Cost between connected node; Whether described processor 22, also for judging the described Executing Cost of the execution route that described hash function is corresponding, is the minimum value in the Executing Cost of all execution route; If so, the execution route that connected node is wished corresponding in the Doha that then described hash function is corresponding meets querying condition.
The server of the present embodiment, may be used for the technical scheme performing the relational operation optimization method based on many Hash tables that any embodiment of the present invention provides, it realizes principle and technique effect is similar, repeats no more herein.
One of ordinary skill in the art will appreciate that: all or part of step realizing above-mentioned each embodiment of the method can have been come by the hardware that programmed instruction is relevant.Aforesaid program can be stored in a computer read/write memory medium.This program, when performing, performs the step comprising above-mentioned each embodiment of the method; And aforesaid storage medium comprises: ROM, RAM, magnetic disc or CD etc. various can be program code stored medium.
Last it is noted that above each embodiment is only in order to illustrate technical scheme of the present invention, be not intended to limit; Although with reference to foregoing embodiments to invention has been detailed description, those of ordinary skill in the art is to be understood that: it still can be modified to the technical scheme described in foregoing embodiments, or carries out equivalent replacement to wherein some or all of technical characteristic; And these amendments or replacement, do not make the essence of appropriate technical solution depart from the scope of various embodiments of the present invention technical scheme.
Claims (7)
1., based on a relational operation optimization method for many Hash tables, it is characterized in that, comprising:
Receive the query statement that client sends, resolve described query statement to judge whether there is condition of contact in described query statement; Wherein, described condition of contact comprises OR or UNION;
If there is described condition of contact, then according to described condition of contact, in relation table, select N number of table as structure table, and at least one hash function selected by each the structure table be respectively in described N number of structure table; Wherein, N is integer, and is greater than 0;
For each hash function, corresponding structure table is scanned, wish node to obtain the Doha corresponding with described Hash table;
For each hash function, according to described hash function, wish in node in the described Doha corresponding with described hash function, whether inquiry exists the record of the condition of contact mated with detection table, if exist, then described record is sent to described client; Wherein, described detection table is other relation table except structure table.
2. method according to claim 1, is characterized in that, described for each hash function, scans corresponding structure table, and to obtain after the Doha corresponding with described Hash table wish node, described method also comprises:
Described detection table is scanned, obtains the scan node corresponding with described detection table;
For each hash function, the Doha that described scan node is corresponding with described hash function is wished node and carry out that Doha is uncommon to be connected, connected node is wished in the Doha generating correspondence;
For each hash function, judge that Doha that described hash function is corresponding is wished execution route corresponding to connected node and whether met querying condition;
Then described according to described hash function, wish in node in the described Doha corresponding with described hash function, whether inquiry exists the record of the condition of contact mated with detection table, specifically comprises:
The Doha that described hash function is corresponding if judge is wished execution route corresponding to connected node and is met described querying condition, then according to described hash function, wish in node in the described Doha corresponding with described hash function, whether inquiry exists the record of the condition of contact mated with detection table.
3. method according to claim 2, is characterized in that, described for each hash function, judges that Doha that described hash function is corresponding is wished execution route corresponding to connected node and whether met querying condition, specifically comprises:
For each hash function, determine that execution route corresponding to connected node is wished in the Doha that described hash function is corresponding;
The weights of connected node are wished in each Doha obtained on described execution route, and are added by each described weights, obtain the Executing Cost of described execution route; Wherein, described weights are for representing that corresponding Doha is wished Doha that connected node is adjacent and wished Executing Cost between connected node;
Whether judge the described Executing Cost of the execution route that described hash function is corresponding, be the minimum value in the Executing Cost of all execution route; If so, the execution route that connected node is wished corresponding in the Doha that then described hash function is corresponding meets querying condition.
4., based on a relational operation optimization device for many Hash tables, it is characterized in that, comprising:
Transceiver module, for receiving the query statement that client sends;
Judge module, for resolving described query statement to judge whether there is condition of contact in described query statement; Wherein, described condition of contact comprises OR or UNION;
Processing module, for when described judge module judges to there is described condition of contact, according to described condition of contact, selects N number of table as structure table in relation table, and at least one hash function selected by each the structure table be respectively in described N number of structure table; Wherein, N is integer, and is greater than 0;
Scan module, for for each hash function, scans corresponding structure table, wishes node to obtain the Doha corresponding with described Hash table;
Described judge module, also for for each hash function, according to described hash function, is wished in node in the described Doha corresponding with described hash function, and whether inquiry exists the record of the condition of contact mated with detection table;
Described transceiver module is also for judging the record that there is the condition of contact mated with detection table during at described judge module, described record is sent to described client; Wherein, described detection table is other relation table except structure table.
5. device according to claim 4, is characterized in that,
Described scan module, also for scanning described detection table, obtains the scan node corresponding with described detection table;
Described processing module is also for for each hash function, and the Doha that described scan node is corresponding with described hash function is wished node and carry out that Doha is uncommon to be connected, connected node is wished in the Doha generating correspondence;
Described judge module, also for for each hash function, judges that Doha that described hash function is corresponding is wished execution route corresponding to connected node and whether met querying condition;
If then described judge module is wished execution route corresponding to connected node specifically for the Doha that described hash function is corresponding and is met described querying condition, then according to described hash function, wish in node in the described Doha corresponding with described hash function, whether inquiry exists the record of the condition of contact mated with detection table.
6. device according to claim 5, is characterized in that, described judge module also comprises: determining unit, acquiring unit and judging unit;
Wherein, described determining unit is used for for each hash function, determines that execution route corresponding to connected node is wished in the Doha that described hash function is corresponding;
Described acquiring unit, wishes the weights of connected node for each Doha obtained on described execution route, and is added by each described weights, obtains the Executing Cost of described execution route; Wherein, described weights are for representing that corresponding Doha is wished Doha that connected node is adjacent and wished Executing Cost between connected node;
Whether described judging unit, for judging the described Executing Cost of the execution route that described hash function is corresponding, is the minimum value in the Executing Cost of all execution route; If so, the execution route that connected node is wished corresponding in the Doha that then described hash function is corresponding meets querying condition.
7. based on a relational operation optimization system for many Hash tables, it is characterized in that, comprising: client and the relational operation optimization device based on many Hash tables as described in any one of claim 4-6.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201410844188.8A CN104504114B (en) | 2014-12-30 | 2014-12-30 | Relational operation optimization method, device and system based on more Hash tables |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201410844188.8A CN104504114B (en) | 2014-12-30 | 2014-12-30 | Relational operation optimization method, device and system based on more Hash tables |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN104504114A true CN104504114A (en) | 2015-04-08 |
| CN104504114B CN104504114B (en) | 2018-05-04 |
Family
ID=52945511
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201410844188.8A Active CN104504114B (en) | 2014-12-30 | 2014-12-30 | Relational operation optimization method, device and system based on more Hash tables |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN104504114B (en) |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO2017148297A1 (en) * | 2016-03-02 | 2017-09-08 | 阿里巴巴集团控股有限公司 | Method and device for joining tables |
| CN107229692A (en) * | 2017-05-19 | 2017-10-03 | 哈工大大数据产业有限公司 | A kind of distributed multi-table connecting method and system based on streamline |
| CN109710635A (en) * | 2018-12-29 | 2019-05-03 | 联想(北京)有限公司 | For the processing method of database, processing system and server group |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP2350879A1 (en) * | 2008-09-19 | 2011-08-03 | Oracle International Corporation | Hash join using collaborative parallel filtering in intelligent storage with offloaded bloom filters |
| US20130013585A1 (en) * | 2011-07-08 | 2013-01-10 | Goetz Graefe | Hash join and hash aggregation integration system |
| CN103823834A (en) * | 2013-12-03 | 2014-05-28 | 华为技术有限公司 | Device and method for data transmission among Hash join operators |
| CN103942343A (en) * | 2014-05-12 | 2014-07-23 | 中国人民大学 | Data storage optimization method for hash joint |
-
2014
- 2014-12-30 CN CN201410844188.8A patent/CN104504114B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP2350879A1 (en) * | 2008-09-19 | 2011-08-03 | Oracle International Corporation | Hash join using collaborative parallel filtering in intelligent storage with offloaded bloom filters |
| US20130013585A1 (en) * | 2011-07-08 | 2013-01-10 | Goetz Graefe | Hash join and hash aggregation integration system |
| CN103823834A (en) * | 2013-12-03 | 2014-05-28 | 华为技术有限公司 | Device and method for data transmission among Hash join operators |
| CN103942343A (en) * | 2014-05-12 | 2014-07-23 | 中国人民大学 | Data storage optimization method for hash joint |
Cited By (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO2017148297A1 (en) * | 2016-03-02 | 2017-09-08 | 阿里巴巴集团控股有限公司 | Method and device for joining tables |
| CN107153643A (en) * | 2016-03-02 | 2017-09-12 | 阿里巴巴集团控股有限公司 | Tables of data connection method and device |
| CN107153643B (en) * | 2016-03-02 | 2021-02-19 | 阿里巴巴集团控股有限公司 | Data table connection method and device |
| TWI746511B (en) * | 2016-03-02 | 2021-11-21 | 香港商阿里巴巴集團服務有限公司 | Data table connection method and device |
| CN107229692A (en) * | 2017-05-19 | 2017-10-03 | 哈工大大数据产业有限公司 | A kind of distributed multi-table connecting method and system based on streamline |
| CN107229692B (en) * | 2017-05-19 | 2018-05-01 | 哈工大大数据产业有限公司 | A kind of distributed multi-table connecting method and system based on assembly line |
| CN109710635A (en) * | 2018-12-29 | 2019-05-03 | 联想(北京)有限公司 | For the processing method of database, processing system and server group |
| CN109710635B (en) * | 2018-12-29 | 2021-03-19 | 联想(北京)有限公司 | Processing method and processing system for database and server group |
Also Published As
| Publication number | Publication date |
|---|---|
| CN104504114B (en) | 2018-05-04 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US8799258B2 (en) | Automated searching for solutions to support self-diagnostic operations of web-enabled devices | |
| US20200356586A1 (en) | Intelligent question and answer method and device | |
| CN111078551B (en) | Full-link testing method, device and system and computer readable storage medium | |
| US20200065160A1 (en) | Automated api evaluation based on api parameter resolution | |
| JP2014519097A (en) | Method and system for recommending items | |
| CN103729471A (en) | Method and device for database query | |
| CN104504114A (en) | Multi-hash table-based relational operation optimization method, device and system | |
| CN111027074B (en) | Vulnerability automatic utilization method and system | |
| US20130159347A1 (en) | Automatic and dynamic design of cache groups | |
| US10288731B2 (en) | Distance detection method and distance detection device using the same | |
| US20180018385A1 (en) | System, data combining method, integration server, data combining program, database system ,database system cooperation method, and database system cooperation program | |
| US10606568B2 (en) | Method and apparatus for compiling computer language | |
| CN108874950B (en) | Data distribution storage method and device based on ER relationship | |
| US10552411B2 (en) | Email service adapter | |
| CN112685319A (en) | Automatic testing method, device, medium, electronic equipment and system | |
| CN111198727B (en) | Micro-service interface data aggregation system and method | |
| CN104050297A (en) | Inquiry transaction distribution method and device | |
| CN103488712B (en) | A kind of automated testing method and system | |
| CN106777072B (en) | A method, device and system for providing presentation information | |
| WO2021128165A1 (en) | Formal verification method and device, information identification method and device, and storage medium | |
| KR102045367B1 (en) | System and method for feed verification task automation | |
| CN113468446B (en) | Method, system and equipment for supporting identification of third party two-dimensional code data | |
| CN104598384A (en) | Code detection method and device | |
| TWI604321B (en) | Information querying method based on user location, device to device relay gateway system and controller | |
| WO2016082432A1 (en) | Data query method and device |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20200415 Address after: 518129 Bantian HUAWEI headquarters office building, Longgang District, Guangdong, Shenzhen Patentee after: HUAWEI TECHNOLOGIES Co.,Ltd. Address before: 301, A building, room 3, building 301, foreshore Road, No. 310053, Binjiang District, Zhejiang, Hangzhou Patentee before: Huawei Technologies Co.,Ltd. |
|
| TR01 | Transfer of patent right |