Data hierarchical query method and device

Abstract

The present invention provides a data hierarchical query method and device, comprising: when an equivalence condition exists in a conditional expression of a connect-by statement, and the types of the prior attribute and the non-prior attribute in the equivalence condition are the same, constructing a hash table of the prior attribute based on the prior value of the prior attribute in the equivalence condition in the data to be queried; constructing a hash table of the non-prior attribute based on the non-prior value of the non-prior attribute in the equivalence condition in the data to be queried; and querying the hash table of the prior attribute and the hash table of the non-prior attribute based on the parent row to be queried to obtain a query result. The present invention improves query efficiency for large-scale data.

Description

Technical Field

[0001] The present invention relates to the technical field of hierarchical query, and in particular to a data hierarchical query method and device. Background Art

[0002] Hierarchical query is a special syntax of Oracle database. By using hierarchical query syntax, you can quickly query data with a hierarchical structure.

[0003] The syntax for a hierarchical query is as follows:

[0004] select * from table

[0005] [start with conditional expression 1]

[0006] connect by [nocycle] conditional expression 2

[0007] [order siblings by expression 3]

[0008] The "start with" keyword specifies the starting row for the hierarchical query. If the SQL (Structured Query Language) does not contain the "start with" keyword, all rows of the table are used as the starting rows for the hierarchical query.

[0009] connect by specifies the conditions for hierarchical queries. Conditional expression 2 can contain the prior keyword, in which case it indicates the parent-child relationship of the hierarchical query; it can also not contain the prior keyword, in which case it only needs to meet this condition; it can also contain the level keyword, in which case it limits the number of output levels.

[0010] The nocycle keyword is optional. When it exists, the output result must not contain a loop. The pseudo column connect_by_iscycle and nocycle must both exist. If the SQL statement does not contain this keyword and the search result contains a loop, the error "Connect by loop in user data" is reported.

[0011] order siblings by is an optional keyword. When it exists, all sibling nodes of the same parent node are sorted according to expression 3.

[0012] The current hierarchical query method is to pass the query data and the parsed connect by condition expression to the LLVM filter function. The LLVM filter function performs data filtering and returns all child rows that meet the current parent row condition. The hierarchical query XO then performs recursive query based on the returned child rows. Figure 1As shown in the figure, the overall process of hierarchical query includes:

[0013] 1. All the data to be queried is filtered by conditional expression 1 following StartWith to generate a StartWith row list.

[0014] 2. Traverse each StartWith line and process each StartWith line. The traversal process is as follows: Figure 2 As shown. Figure 3 As shown, the traversal search process for a single StartWith line is as follows:

[0015] 2.1. Get a row of data, the first time is the StartWith row.

[0016] 2.2. Using the StartWith row as the parent row, search for all rows that meet condition expression 2 in the entire query data set. The query results are the child rows of this row, and are added to the query rows. The query rows are temporary data stored when traversing a single StartWith row, not the entire query data set.

[0017] 2.3. Repeat steps 2.1 and 2.2 for all child rows in depth traversal order until all qualifying rows are found and output to the result.

[0018] like Figure 1 As shown, all StartWith rows, i.e., rows 1 to M, are traversed, with StartWith row 1 being the first to be traversed. At this point, the row to be queried has the data 1. 1 is retrieved, and using the ConnectBy condition, child rows 2, 7, and 9 are found. Child rows 2, 7, and 9 are saved in the row to be queried. The row to be queried has the data 2, 7, and 9. 2 is retrieved, and by searching row 2, child rows 3 and 8 are found. 3 and 8 are saved in the row to be queried, and rows 3 and 8 are traversed from the row to be queried, in turn. Since neither 3 nor 8 has child rows, row 7 is retrieved from the row to be searched, and so on. During the search, all result rows that meet the conditions are output to the result. After completing the search for all StartWith rows, subsequent StartWith rows are traversed.

[0019] Existing hierarchical query methods slow down when the amount of data to be queried is large. This is because each search for a child row through a parent row requires a complete traversal of the data to be queried. Search time increases with the size of the data to be searched. Furthermore, each search requires multiple calls to the LLVM filter function because the LLVM filter function only receives a fixed packet size of 8192 rows at a time. Assuming the LLVM filter function search complexity is P, if the search data is m rows and the number of search rows is n, the complexity is O(n*(m / 8192)*P). Hierarchical queries of large-scale data are particularly time-consuming. Summary of the Invention

[0020] The present invention provides a data hierarchical query method and device, which are used to solve the defect of low efficiency of hierarchical query in the prior art and improve the efficiency of hierarchical query under large-scale data.

[0021] The present invention provides a data hierarchical query method, comprising:

[0022] If there is an equal value condition in the conditional expression of the connect by clause in the hierarchical query statement, and the types of the prior attribute and the non-prior attribute in the equal value condition are the same, a hash table of the prior attribute is constructed according to the prior value of the prior attribute in the data to be queried in the equal value condition;

[0023] Constructing a hash table of the non-prior attributes according to the non-prior values ​​of the non-prior attributes in the equivalence condition in the data to be queried;

[0024] The hash table of the prior attribute and the hash table of the non-prior attribute are queried according to the parent row to obtain the query result.

[0025] According to a data hierarchical query method provided by the present invention, constructing a hash table of the prior attribute according to the prior value of the prior attribute in the data to be queried in the equal value condition includes:

[0026] Using the row number of the row where the prior attribute is located in the data to be queried as the key value and the prior value of the prior attribute in the data to be queried as the value value, a hash table of the prior attribute is constructed;

[0027] The step of constructing a hash table of the non-prior attributes according to the non-prior values ​​of the non-prior attributes in the data to be queried in the equivalence condition includes:

[0028] The non-prior value of the non-prior attribute in the data to be queried is used as the key value, and the row number of the row where the non-prior attribute in the data to be queried is used as the value value to construct a hash table of the non-prior attribute.

[0029] According to a data hierarchical query method provided by the present invention, querying the hash table of the prior attribute and the hash table of the non-prior attribute according to the parent row to be queried to obtain the query result includes:

[0030] Using the row number of the parent row to be queried as the index, search in the hash table of the prior attribute to obtain the prior value corresponding to the row number of the parent row to be queried;

[0031] Use the prior value corresponding to the row number of the parent row to be queried as an index to search in the hash table of the non-prior attribute, obtain the non-prior value that is the same as the prior value corresponding to the row number of the parent row to be queried, and take the row number of the row where the same non-prior value is located in the data to be queried as the query result.

[0032] According to a data hierarchical query method provided by the present invention, constructing a hash table of the prior attribute according to the prior value of the prior attribute in the data to be queried in the equal value condition includes:

[0033] Splitting the conditional expression in the connect by statement into multiple sub-expressions according to the "and" connector in the connect by statement;

[0034] According to the sub-expressions that do not contain the prior keyword and the level keyword, filtering the data to be queried based on a non-hierarchical query method;

[0035] According to the prior value of the prior attribute in the filtered data to be queried in the equal value condition, a hash table of the prior attribute is constructed.

[0036] A data hierarchical query method provided by the present invention further includes:

[0037] If there is no equality condition in the conditional expression of the connect by statement, if the row number of the parent row to be queried in the data to be queried is the same as the row number of the parent row already queried in the data to be queried, and the level number of the parent row to be queried in the query result is the same as the level number of the parent row already queried in the query result, then the child rows of the parent row already queried are used as the child rows of the parent row to be queried;

[0038] If the row number of the parent row to be queried is different from the row number of the parent row that has been queried, the prior value in the parent row to be queried exists in the prior value in the parent row that has been queried, and the number of levels corresponding to the parent row to be queried is the same as the number of levels corresponding to the parent row that has been queried, then the child rows of the parent row that has been queried will be used as the child rows of the parent row to be queried.

[0039] According to a data hierarchical query method provided by the present invention, a hash table of the prior attribute and a hash table of the non-prior attribute are queried according to the parent row to be queried to obtain the query result, including:

[0040] If the prior value in the parent row to be queried is not empty, query the hash table of the prior attribute and the hash table of the non-prior attribute according to the parent row to be queried to obtain the query result;

[0041] If the conditional expression satisfies the preset condition, the maximum value among the non-prior values ​​is counted;

[0042] The preset conditions include a first preset condition, a second preset condition and a third preset condition;

[0043] The first preset condition is an equivalent condition containing the prior keyword in the conditional expression;

[0044] The second preset condition is that the equivalent condition does not contain a canse when statement, a level condition, a varchar to char cast function, an LTRIM function, an RTRIM function, and an or connector;

[0045] The third preset condition is that the number of rows queried from the data to be queried using the start with statement in the hierarchical query statement is greater than a preset threshold;

[0046] If the prior value in the parent row to be queried is less than or equal to the maximum value, query the hash table of the prior attribute and the hash table of the non-prior attribute according to the parent row to be queried to obtain the query result;

[0047] If the level of the parent row to be queried in the query result satisfies the level condition in the conditional expression, the hash table of the prior attribute and the hash table of the non-prior attribute are queried according to the parent row to obtain the query result.

[0048] The present invention also provides a data hierarchical query device, comprising:

[0049] The first construction module is configured to, when an equal value condition exists in a conditional expression of a connect by statement in a hierarchical query statement and the types of the prior attribute and the non-prior attribute in the equal value condition are the same, construct a hash table of the prior attribute according to the prior value of the prior attribute in the equal value condition in the data to be queried;

[0050] A second construction module is configured to construct a hash table of the non-prior attributes according to the non-prior values ​​of the non-prior attributes in the data to be queried in the equivalence condition;

[0051] The query module is used to query the hash table of the prior attribute and the hash table of the non-prior attribute according to the parent row to be queried, and obtain the query result.

[0052] The present invention also provides an electronic device comprising a memory, a processor and a computer program stored in the memory and executable on the processor, wherein when the processor executes the program, the steps of any of the above-described data hierarchical query methods are implemented.

[0053] The present invention also provides a non-transitory computer-readable storage medium having a computer program stored thereon, which implements the steps of any of the above-mentioned data hierarchical query methods when executed by a processor.

[0054] The present invention also provides a computer program product, comprising a computer program, wherein when the computer program is executed by a processor, the steps of any of the above-mentioned data hierarchical query methods are implemented.

[0055] The data hierarchical query method and device provided by the present invention determine whether a connect by expression satisfies an equal value search condition. If so, a hash table of the attribute columns to be searched is constructed, and matching results are directly found through a hash algorithm, thereby improving query efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0056] In order to more clearly illustrate the technical solutions in the present invention or the prior art, a brief introduction is given below to the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0057] Figure 1 It is a flowchart of the data hierarchical query method provided by the prior art;

[0058] Figure 2This is a schematic diagram of the traversal process of the StartWith line in the data hierarchical query method provided by the prior art;

[0059] Figure 3 This is a schematic diagram of the traversal search process of a single StartWith row in the data hierarchical query method provided by the prior art;

[0060] Figure 4 This is one of the flow charts of the data hierarchical query method provided by the present invention;

[0061] Figure 5 This is one of the flow charts of expression processing in the data hierarchical query method provided by the present invention;

[0062] Figure 6 This is a schematic diagram of the construction process of the hash table in the data hierarchical query method provided by the present invention;

[0063] Figure 7 This is one of the schematic diagrams of the traversal search process of a single StartWith row in the data hierarchical query method provided by the present invention;

[0064] Figure 8 This is the second schematic diagram of the expression processing flow in the data hierarchical query method provided by the present invention;

[0065] Figure 9 This is a schematic diagram of the preprocessing process of the data to be queried in the data hierarchical query method provided by the present invention;

[0066] Figure 10 This is the second schematic diagram of the traversal search process of a single StartWith row in the data hierarchical query method provided by the present invention;

[0067] Figure 11 This is the third schematic diagram of the traversal search process of a single StartWith row in the data hierarchical query method provided by the present invention;

[0068] Figure 12 This is the fourth schematic diagram of the traversal search process of a single StartWith row in the data hierarchical query method provided by the present invention;

[0069] Figure 13 It is a structural diagram of the data hierarchical query device provided by the present invention;

[0070] Figure 14 It is a structural schematic diagram of the electronic device provided by the present invention. DETAILED DESCRIPTION

[0071] To make the objectives, technical solutions, and advantages of the present invention more clear, the technical solutions of the present invention will be clearly and completely described below in conjunction with the accompanying drawings. Obviously, the embodiments described are only some of the embodiments of the present invention, not all of them. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts shall fall within the scope of protection of the present invention.

[0072] The following combination Figure 4 A data hierarchical query method of the present invention is described, comprising: step 401, when an equivalence condition exists in a conditional expression of a connect by clause in a hierarchical query statement, and when a prior attribute and a non-prior attribute in the equivalence condition are of the same type, constructing a hash table of the prior attribute according to the prior value of the prior attribute in the equivalence condition in the data to be queried;

[0073] The connect-by clause of a hierarchical query can contain multiple conditional expressions. For equality conditions, expressions connected by equal signs, such as prior c1 = c2, searches based on the same attribute column type (i.e., c1 and c2) are called equality searches. This embodiment uses the equality hash search method for equality searches.

[0074] Step 402: construct a hash table of the non-prior attributes according to the non-prior values ​​of the non-prior attributes in the equivalence condition in the data to be queried;

[0075] During the data reading process, a hash table is constructed. For example, if prior c1 = c2, the hash table for the prior attribute is called parent_map, and the hash table for the non-prior attribute is called child_map.

[0076] Step 403: query the hash table of the prior attribute and the hash table of the non-prior attribute according to the parent row to be queried, and obtain the query result.

[0077] like Figure 5 As shown, when processing the result of the connect by expression, it is determined whether the equal value search condition is met. If the equal value search condition is met, then Figure 6 As shown, a hash table of the attribute columns to be searched is constructed, and matching results are directly found through the hash algorithm to improve query efficiency. In this case, the traversal search process of a single StartWith row is as follows Figure 7 shown.

[0078] This embodiment determines whether the connect by expression satisfies the equal value search condition. If so, a hash table of the attribute columns to be searched is constructed, and matching results are directly found through a hash algorithm, thereby improving query efficiency.

[0079] Based on the above embodiment, the present embodiment constructs a hash table of the prior attribute according to the prior value of the prior attribute in the data to be queried in the equal value condition, including: using the row number of the row where the prior attribute is located in the data to be queried as the key value, and using the prior value of the prior attribute in the data to be queried as the value value, to construct the hash table of the prior attribute;

[0080] The key value in parent_map is the row number of the prior value of the prior attribute, and the value value is the prior value of the prior attribute, that is, the value of the c1 column.

[0081] The method of constructing a hash table of the non-prior attribute based on the non-prior value of the non-prior attribute in the data to be queried in the equivalence condition includes: using the non-prior value of the non-prior attribute in the data to be queried as the key value, and using the row number of the row where the non-prior attribute is located in the data to be queried as the value value, to construct the hash table of the non-prior attribute.

[0082] The key value in the child_map table is the non-prior value of the non-prior attribute, that is, the value of column c2. The value value is the row number of the non-prior value of the non-prior attribute.

[0083] Based on the above embodiment, the query result is obtained by querying the hash table of the prior attribute and the hash table of the non-prior attribute according to the parent row to be queried, including: searching the hash table of the prior attribute with the row number of the parent row to be queried as the index to obtain the prior value corresponding to the row number of the parent row to be queried;

[0084] During the hash search, each row is read from the query row as the parent row to be queried. The row number of the parent row to be queried is used as the index to search in parent_map to obtain the prior value of the prior attribute of the parent row to be queried.

[0085] Use the prior value corresponding to the row number of the parent row to be queried as an index to search in the hash table of the non-prior attribute, obtain the non-prior value that is the same as the prior value corresponding to the row number of the parent row to be queried, and take the row number of the row where the same non-prior value is located in the data to be queried as the query result.

[0086] Then use this prior value as the index to find the non-prior value equal to this prior value in child_map, return a range iterator that satisfies the prior value equal to the non-prior value, and use this range iterator to read the child row number.

[0087] Based on the above embodiments, the present embodiment constructs a hash table of the prior attribute according to the prior value of the prior attribute in the data to be queried in the equality condition, including: splitting the conditional expression in the connect by statement into multiple sub-expressions according to the "and" connector in the connect by statement;

[0088] To improve query efficiency for hierarchical queries on large-scale data, a pruning optimization algorithm is used to optimize the query data before performing hierarchical queries. This algorithm parses the conditional expressions in the connect-by clause to obtain conditional expressions that do not contain either the "prior" or "level" keyword. This algorithm then filters out data that meets these conditional expressions during the source data reading process, reducing the data size required for subsequent hierarchical queries.

[0089] It should be noted that ordinary filtering conditions connected by or in connect by will not be filtered in advance. Only conditions connected by and can be filtered in advance.

[0090] For example, in connect by prior c1=c2 or c1<10, the two expressions c1=c2 and c1<10 are in a parallel relationship. Filtering in advance will not reduce the amount of data for hierarchical queries, so filtering is not performed in advance.

[0091] Connect by prior c1=c2 and c1<10. In this case, the two expressions c1=c2 and c1<10 are in a progressive relationship. Filtering in advance can reduce the amount of data for hierarchical queries, so filtering can be done in advance.

[0092] During expression parsing, the conditional expression in the connect by statement is split according to the and connector, and the part between two adjacent and connectors is regarded as a sub-expression.

[0093] If the conditional expression is connect by prior c1=c2 and c1<10, the "and" connector is used to split the expression list into a list of 2. The first sub-expression is prior c1=c2, and the second sub-expression is c1<10.

[0094] If the conditional expression is connect by prior c1=c2 or c1<10, there is no and connector, so the size of the expression list after splitting is 1, and the first sub-expression is prior c1=c2 or c1<10.

[0095] According to the sub-expressions that do not contain the prior keyword and the level keyword, filtering the data to be queried based on a non-hierarchical query method;

[0096] The syntax of hierarchical query is connect by conditional expression, where the conditional expression can be of any form, including the prior keyword, the level keyword, or neither the prior nor the level keyword.

[0097] For sub-expressions that do not contain either prior or level in the split sub-expressions, just like the common where filter conditions, the query data can be filtered once before the search, retaining only the data that meets this sub-expression, which is the pruning optimization algorithm.

[0098] During the expression processing, the expression parsing results are analyzed and the corresponding LLVM filter function is generated according to the parsing results.

[0099] For example, when the expression is connect by prior c1=c2 and c1<10, only one LLVM filter function will be generated before optimization to find the child rows. After optimization, due to the existence of the common filter condition c1<10, an additional LLVM filter function will be generated to filter the data to be queried, such as Figure 8 shown.

[0100] like Figure 9 As shown in the figure, when there is an LLVM filter function with common filtering conditions, the query data will be filtered to generate new query data. Based on the new query data, hierarchical query is performed to reduce the amount of query data and improve query efficiency.

[0101] According to the prior value of the prior attribute in the filtered data to be queried in the equal value condition, a hash table of the prior attribute is constructed.

[0102] As shown in Table 1, the data table to be queried has 1000 rows, and the expression is connect by prior c1=c2 and c1<10.

[0103] Table 1 Example of data to be queried

[0104]

[0105]

[0106] Before optimization, the query data was 1000 rows, and each LLVM search had to search within these 1000 rows. After optimization, the query data was filtered using the standard filter condition c1 < 10, reducing the number of rows to search to 9. Each LLVM search only had to search within these 9 rows.

[0107] This embodiment splits the connect by conditional expression into multiple sub-expressions by using the "and" connector in the connect by conditional expression, uses common sub-expressions that do not contain the prior keyword and the level keyword to filter the query data at one time to generate new query data, and performs an equivalent condition search based on the new query data, thereby reducing the amount of data for the equivalent condition search and improving the query efficiency under large-scale data.

[0108] Based on the above embodiment, this embodiment further includes: when there is no equivalent condition in the conditional expression of the connect by statement, if the row number of the parent row to be queried in the data to be queried is the same as the row number of the parent row already queried in the data to be queried, and the level number of the parent row to be queried in the query result is the same as the level number of the parent row already queried in the query result, then the child row of the parent row already queried is used as the child row of the parent row to be queried;

[0109] After each hierarchical query, an unordered_multimap table is defined to save the information of the queried parent row and the child rows of the queried parent row. Figure 10 As shown, hierarchical query may encounter the situation of searching based on the same row or rows with the same prior value. First, the unordered_multimap table is searched. If the information of the parent row to be queried is the same as the information of the parent row that has been queried, there is no need to call LLVM search again. The child rows of the queried parent row are directly used as the child rows of the parent row to be queried, thereby reducing the number of calls to the LLVM filter function and improving query efficiency.

[0110] Parent row information includes the row number of the queried parent row and the level number in the query results. Because the results for searching the same row at different levels may differ when a join by clause contains a level condition, the level is used to distinguish search results at different levels.

[0111] If the row number of the parent row to be queried is different from the row number of the parent row that has been queried, the prior value in the parent row to be queried exists in the prior value in the parent row that has been queried, and the number of levels corresponding to the parent row to be queried is the same as the number of levels corresponding to the parent row that has been queried, then the child rows of the parent row that has been queried will be used as the child rows of the parent row to be queried.

[0112] To summarize, when determining whether the parent row to be queried is the same as the parent row that has been queried, any of the following conditions must be met:

[0113] 1. If the row numbers are the same and the level is the same, the parent row to be queried is considered the same as the parent row already queried; otherwise, they are not the same.

[0114] 2. When the row numbers are different, the parent row to be queried must have the same value of the prior field and the same level hierarchy. Otherwise, they are considered different.

[0115] When the parent row to be queried is the same as the parent row that has been queried, the child rows of the queried parent row are directly used as the child rows of the parent row to be queried, thereby reducing the number of calls to the LLVM filter function and improving query efficiency.

[0116] If the equal search condition is not met, the information of the queried parent row and the child rows of the queried parent row are saved. If the parent row to be queried is the same as the queried parent row, the child rows of the queried parent row are directly used as the child rows of the parent row to be queried, thereby reducing the number of calls to the LLVM filter function and improving query efficiency.

[0117] Based on the above embodiment, the present embodiment queries the hash table of the prior attribute and the hash table of the non-prior attribute according to the parent row to be queried to obtain the query result, including: if the prior value in the parent row to be queried is not empty, querying the hash table of the prior attribute and the hash table of the non-prior attribute according to the parent row to be queried to obtain the query result;

[0118] Before querying each parent row to be queried, a pre-judgment is performed. Only when the corresponding conditions are met will the parent row to be queried be queried; otherwise, neither LLVM search nor hash value search is required for the parent row to be queried. The parent row to be queried is now a leaf node in the query result. By adding a pre-judgment step before the search, if it is determined that the parent row to be queried has no child rows, no further search is required and the next node can be processed. For example, Figure 11 As shown, the number of queries is reduced and the query efficiency is improved.

[0119] The pre-judgment includes prior null value judgment, maximum value judgment and level hierarchy judgment. As long as one of the judgment conditions is not met, the call to the search function can be skipped.

[0120] In the prior null value judgment, if the prior value in the parent row to be queried is null, the parent row to be queried will no longer be searched.

[0121] If the conditional expression satisfies the preset condition, the maximum value among the non-prior values ​​is counted;

[0122] The preset conditions include a first preset condition, a second preset condition and a third preset condition;

[0123] The first preset condition is an equivalent condition containing the prior keyword in the conditional expression;

[0124] The second preset condition is that the equivalent condition does not contain a canse when statement, a level condition, a varchar to char cast function, an LTRIM function, an RTRIM function, and an or connector;

[0125] The third preset condition is that the number of rows queried from the data to be queried using the start with statement in the hierarchical query statement is greater than a preset threshold;

[0126] If the prior value in the parent row to be queried is less than or equal to the maximum value, query the hash table of the prior attribute and the hash table of the non-prior attribute according to the parent row to be queried to obtain the query result;

[0127] If the prior value in the parent row being queried is greater than the maximum non-prior value, the parent row is not searched again. For example, if prior c1 = c2, the maximum value is the maximum value in the c2 column. A preset threshold of 1024 is recommended.

[0128] If the level at which the parent row to be queried is located in the query result meets the level condition in the conditional expression, query the hash table of the prior attribute and the hash table of the non-prior attribute based on the parent row to be queried to obtain the query result.

[0129] If the level of the parent row to be queried does not meet the level condition, no further query is performed on the parent row to be queried, and only the start with row is output. It is required that there is only one level condition in the conditional expression, such as the Level condition being level >= value, value <= level (value >= 3), level > value or value < level (value >= 2).

[0130] For the case of level < value or level <= value, calculate the upper limit value of level according to the level condition, and judge whether the current level exceeds the upper limit value before each search. The complete process of traversing and searching a single StartWith row is as Figure 12 shown.

[0131] The data hierarchical query device provided by the present invention will be described below. The data hierarchical query device described below can be correspondingly referred to the data hierarchical query method described above.

[0132] As Figure 13 shown, the device includes a first construction module 1301, a second construction module 1302 and a query module 1303; where:

[0133] The first construction module 1301 is used to construct the hash table of the prior attribute according to the prior value of the prior attribute in the data to be queried when there is an equivalent condition in the conditional expression of the connect by statement in the hierarchical query statement and the types of the prior attribute and the non-prior attribute in the equivalent condition are the same;

[0134] The second construction module 1302 is used to construct the hash table of the non-prior attribute according to the non-prior value of the non-prior attribute in the data to be queried;

[0135] The query module 1303 is used to query the hash table of the prior attribute and the hash table of the non-prior attribute according to the parent row to be queried to obtain the query result.

[0136] This embodiment determines whether the connect by expression satisfies the equal value search condition. If so, a hash table of the attribute columns to be searched is constructed, and matching results are directly found through a hash algorithm, thereby improving query efficiency.

[0137] Figure 14 An example of a physical structure diagram of an electronic device is shown below. Figure 14 As shown, the electronic device may include: a processor 1410, a communication interface 1420, a memory 1430, and a communication bus 1440, wherein the processor 1410, the communication interface 1420, and the memory 1430 communicate with each other via the communication bus 1440. The processor 1410 may call logic instructions in the memory 1430 to execute a data hierarchical query method, the method comprising: when there is an equality condition in a conditional expression of a connect by statement, and the types of the prior attribute and the non-prior attribute in the equality condition are the same, constructing a hash table of the prior attribute based on the prior value of the prior attribute in the equality condition in the data to be queried; constructing a hash table of the non-prior attribute based on the non-prior value of the non-prior attribute in the equality condition in the data to be queried; and querying the hash table of the prior attribute and the hash table of the non-prior attribute based on the parent row to be queried to obtain a query result.

[0138] In addition, the logic instructions in the above-mentioned memory 1430 can be implemented in the form of a software functional unit and can be stored in a computer-readable storage medium when sold or used as an independent product. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art, or the part of the technical solution, can be embodied in the form of a software product. The computer software product is stored in a storage medium and includes several instructions for enabling a computer device (which can be a personal computer, server, or network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes: various media that can store program codes, such as a USB flash drive, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk.

[0139] On the other hand, the present invention also provides a computer program product, which includes a computer program, which can be stored on a non-transitory computer-readable storage medium. When the computer program is executed by a processor, the computer can execute the data hierarchical query method provided by the above methods, which includes: when there is an equal condition in the conditional expression of the connect by statement, and the types of the priority attribute and the non-prior attribute in the equal condition are the same, constructing a hash table of the priority attribute according to the prior value of the priority attribute in the equal condition in the data to be queried; constructing a hash table of the non-prior attribute according to the non-prior value of the non-prior attribute in the equal condition in the data to be queried; querying the hash table of the priority attribute and the hash table of the non-prior attribute according to the parent row to be queried to obtain the query result.

[0140] On the other hand, the present invention also provides a non-transitory computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, is implemented to execute the data hierarchical query method provided by the above-mentioned methods, the method comprising: when there is an equivalent condition in the conditional expression of the connect by statement, and the types of the priority attribute and the non-prior attribute in the equivalent condition are the same, constructing a hash table of the priority attribute according to the prior value of the priority attribute in the equivalent condition in the data to be queried; constructing a hash table of the non-prior attribute according to the non-prior value of the non-prior attribute in the equivalent condition in the data to be queried; querying the hash table of the priority attribute and the hash table of the non-prior attribute according to the parent row to be queried to obtain the query result.

[0141] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical units, i.e., they may be located in one location or distributed across multiple network units. Some or all of the modules may be selected based on actual needs to achieve the objectives of the present embodiment. Persons of ordinary skill in the art will be able to understand and implement the present invention without inventive effort.

[0142] Through the description of the above embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus a necessary general hardware platform, or of course, by hardware. Based on this understanding, the essence of the above technical solution or the part that contributes to the existing technology can be embodied in the form of a software product. The computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, a magnetic disk, an optical disk, etc., and includes a number of instructions for enabling a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in each embodiment or certain parts of the embodiments.

[0143] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention 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. However, 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 various embodiments of the present invention.

Claims

1. A data hierarchical query method, characterized in that: include: If there is an equal value condition in the conditional expression of the connect by clause in the hierarchical query statement, and the types of the prior attribute and the non-prior attribute in the equal value condition are the same, a hash table of the prior attribute is constructed according to the prior value of the prior attribute in the data to be queried in the equal value condition; Constructing a hash table of the non-prior attributes according to the non-prior values ​​of the non-prior attributes in the equivalence condition in the data to be queried; Query the hash table of the prior attribute and the hash table of the non-prior attribute according to the parent row to be queried, and obtain the query result; Query the hash table of the prior attribute and the hash table of the non-prior attribute according to the parent row to be queried, and obtain the query results, including: If the prior value in the parent row to be queried is not empty, query the hash table of the prior attribute and the hash table of the non-prior attribute according to the parent row to be queried to obtain the query result; If the conditional expression satisfies the preset condition, the maximum value among the non-prior values ​​is counted; The preset conditions include a first preset condition, a second preset condition and a third preset condition; The first preset condition is an equivalent condition containing the prior keyword in the conditional expression; The second preset condition is that the equivalent condition does not contain a case when statement, a level condition, a varchar to char cast function, an LTRIM function, an RTRIM function, and an or connector; The third preset condition is that the number of rows queried from the data to be queried using the start with statement in the hierarchical query statement is greater than a preset threshold; If the prior value in the parent row to be queried is less than or equal to the maximum value, query the hash table of the prior attribute and the hash table of the non-prior attribute according to the parent row to be queried to obtain the query result; If the level of the parent row to be queried in the query result satisfies the level condition in the conditional expression, the hash table of the prior attribute and the hash table of the non-prior attribute are queried according to the parent row to obtain the query result.

2. The data hierarchical query method according to claim 1, characterized in that: The step of constructing a hash table of the prior attribute according to the prior value of the prior attribute in the data to be queried in the equal value condition includes: Using the row number of the row where the prior attribute is located in the data to be queried as the key value and the prior value of the prior attribute in the data to be queried as the value value, a hash table of the prior attribute is constructed; The step of constructing a hash table of the non-prior attributes according to the non-prior values ​​of the non-prior attributes in the data to be queried in the equivalence condition includes: The non-prior value of the non-prior attribute in the data to be queried is used as the key value, and the row number of the row where the non-prior attribute in the data to be queried is used as the value value to construct a hash table of the non-prior attribute.

3. The data hierarchical query method according to claim 2, characterized in that: The querying of the hash table of the prior attribute and the hash table of the non-prior attribute according to the parent row to be queried to obtain the query result includes: Using the row number of the parent row to be queried as the index, search in the hash table of the prior attribute to obtain the prior value corresponding to the row number of the parent row to be queried; Use the prior value corresponding to the row number of the parent row to be queried as an index to search in the hash table of the non-prior attribute, obtain the non-prior value that is the same as the prior value corresponding to the row number of the parent row to be queried, and take the row number of the row where the same non-prior value is located in the data to be queried as the query result.

4. The data hierarchical query method according to any one of claims 1 to 3, characterized in that: The step of constructing a hash table of the prior attribute according to the prior value of the prior attribute in the data to be queried in the equal value condition includes: Splitting the conditional expression in the connect by statement into multiple sub-expressions according to the "and" connector in the connect by statement; According to the sub-expressions that do not contain the prior keyword and the level keyword, filtering the data to be queried based on a non-hierarchical query method; According to the prior value of the prior attribute in the filtered data to be queried in the equal value condition, a hash table of the prior attribute is constructed.

5. The data hierarchical query method according to claim 4, characterized in that: Also includes: If there is no equality condition in the conditional expression of the connect by statement, if the row number of the parent row to be queried in the data to be queried is the same as the row number of the parent row already queried in the data to be queried, and the level number of the parent row to be queried in the query result is the same as the level number of the parent row already queried in the query result, then the child rows of the parent row already queried are used as the child rows of the parent row to be queried; If the row number of the parent row to be queried is different from the row number of the parent row that has been queried, the prior value in the parent row to be queried exists in the prior value in the parent row that has been queried, and the number of levels corresponding to the parent row to be queried is the same as the number of levels corresponding to the parent row that has been queried, then the child rows of the parent row that has been queried will be used as the child rows of the parent row to be queried.

6. A data hierarchical query device, characterized in that: include: The first construction module is configured to, when an equal value condition exists in a conditional expression of a connect by statement in a hierarchical query statement and the types of the prior attribute and the non-prior attribute in the equal value condition are the same, construct a hash table of the prior attribute according to the prior value of the prior attribute in the equal value condition in the data to be queried; A second construction module is configured to construct a hash table of the non-prior attributes according to the non-prior values ​​of the non-prior attributes in the data to be queried in the equivalence condition; A query module, configured to query the hash table of the prior attribute and the hash table of the non-prior attribute according to the parent row to be queried, and obtain query results; Query module, specifically used for: If the prior value in the parent row to be queried is not empty, query the hash table of the prior attribute and the hash table of the non-prior attribute according to the parent row to be queried to obtain the query result; If the conditional expression satisfies the preset condition, the maximum value among the non-prior values ​​is counted; The preset conditions include a first preset condition, a second preset condition and a third preset condition; The first preset condition is an equivalent condition containing the prior keyword in the conditional expression; The second preset condition is that the equivalent condition does not contain a case when statement, a level condition, a varchar to char cast function, an LTRIM function, an RTRIM function, and an or connector; The third preset condition is that the number of rows queried from the data to be queried using the start with statement in the hierarchical query statement is greater than a preset threshold; If the prior value in the parent row to be queried is less than or equal to the maximum value, query the hash table of the prior attribute and the hash table of the non-prior attribute according to the parent row to be queried to obtain the query result; If the level of the parent row to be queried in the query result satisfies the level condition in the conditional expression, the hash table of the prior attribute and the hash table of the non-prior attribute are queried according to the parent row to obtain the query result.

7. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein: When the processor executes the program, the steps of the data hierarchical query method according to any one of claims 1 to 5 are implemented.

8. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the data hierarchical query method according to any one of claims 1 to 5 are implemented.

9. A computer program product comprising a computer program, characterized in that When the computer program is executed by a processor, the steps of the data hierarchical query method according to any one of claims 1 to 5 are implemented.

Images