Query method, device, equipment and storage medium

Abstract

The present invention discloses a query method, apparatus, device, and storage medium. The method includes: if a first query statement meets a preset condition, updating the first query statement based on an update rule to obtain a target query statement; and executing the target query statement to obtain a target query result. The technical solution of the present invention introduces a bitmap index during the query process, thereby improving query efficiency.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a query method, device, equipment and storage medium. Background Art

[0002] With the rapid development of computer information technology, more and more users are placing higher demands on the storage and retrieval of massive amounts of data. Bitmap indexes, due to their minimal storage space requirements, are widely used in various businesses. Bitmap indexes are a special type of index that uses bit arrays for storage and computation. Bitmap indexes correspond to index keys in the base table (the table where the bitmap index resides) and primarily use bitmaps or bit arrays to index data. In massive data scenarios, bitmap indexes are particularly widely used.

[0003] In the prior art, when performing a query, each row of data in the queried table needs to be queried one by one, that is, the entire queried table needs to be queried once before the final query result can be obtained, resulting in very low query efficiency. Summary of the Invention

[0004] The embodiments of the present invention provide a query method, apparatus, device and storage medium, which introduce bitmap indexes into the query process by updating and rewriting SQL statements that meet the conditions, thereby improving query efficiency.

[0005] According to one aspect of the present invention, a query method is provided, comprising:

[0006] If the first query statement meets the preset conditions, the first query statement is updated based on the update rule to obtain the target query statement;

[0007] Execute the target query statement to obtain the target query result.

[0008] According to another aspect of the present invention, a query device is provided, the device comprising:

[0009] An updating module, configured to update the first query statement based on an updating rule to obtain a target query statement if the first query statement meets a preset condition;

[0010] The target query result determination module is used to execute the target query statement and obtain the target query result.

[0011] According to another aspect of the present invention, an electronic device is provided, comprising:

[0012] at least one processor; and

[0013] a memory communicatively connected to the at least one processor; wherein,

[0014] The memory stores a computer program that can be executed by the at least one processor. The computer program is executed by the at least one processor to enable the at least one processor to perform the query method described in any embodiment of the present invention.

[0015] According to another aspect of the present invention, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement the query method described in any embodiment of the present invention when executed.

[0016] The embodiment of the present invention updates the first query statement based on the update rule when the first query statement meets the preset conditions, obtains the target query statement, executes this statement to obtain the target query result, and introduces the bitmap index in the query process by updating and rewriting the SQL statement that meets the conditions, thereby solving the problem of needing to query each row of data in the required query table one by one and improving the query efficiency.

[0017] It should be understood that the content described in this section is not intended to identify the key or important features of the embodiments of the present invention, nor is it intended to limit the scope of the present invention. Other features of the present invention will become readily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required for use in the embodiments. It should be understood that the following drawings only illustrate certain embodiments of the present invention and therefore should not be regarded as limiting the scope. For ordinary technicians in this field, other relevant drawings can be obtained based on these drawings without paying any creative work.

[0019] Figure 1 A flowchart of a query method provided in Example 1 of the present invention;

[0020] Figure 2 A flowchart of a query method provided in Example 2 of the present invention;

[0021] Figure 3 A flowchart of a query method provided in Example 3 of the present invention;

[0022] Figure 4 A schematic diagram of the structure of a query device provided in the fourth embodiment of the present invention;

[0023] Figure 5 This is a structural diagram of an electronic device provided in Example 5 of the present invention. DETAILED DESCRIPTION

[0024] In order to enable those skilled in the art to better understand the solutions of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the embodiments described are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts should fall within the scope of protection of the present invention.

[0025] It should be noted that the terms "first", "second", etc. in the description and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that the numbers used in this way can be interchanged where appropriate, so that the embodiments of the present invention described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions. For example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0026] Example 1

[0027] Figure 1 This is a flowchart of a query method provided in Example 1 of the present invention. This embodiment is applicable to situations where queries are performed based on query statements. The method can be executed by a query device, which can be implemented using hardware and / or software and can be integrated into an electronic device. The method specifically includes the following steps:

[0028] S110: If the first query statement meets a preset condition, the first query statement is updated based on an update rule to obtain a target query statement.

[0029] Specifically, the first query statement is a statement used for querying in a database statement. It should be noted that there are many syntaxes for query statements, including more complex subqueries, multi-table join queries, and commonly used statement queries.

[0030] The preset conditions are conditions that must be met by the first query statement. If the first query statement meets the preset conditions, it can be updated. If any of the preset conditions are not met, the first query statement performs the query operation according to the query operation method in the prior art. Exemplarily, the preset conditions include: the first query statement is a query statement for a single table, the set function in the first query statement is at least one of the COUNT set function, the SUM set function, the MAX set function and the MIN set function, and the parameter in the set function in the first query statement is a constant, a bitmap index column and a preset identifier. It should be noted that the first query statement may also include other functions besides the set function. The preset conditions may also be: the first query statement is a query statement for a single table, the set function in the first query statement is at least one of the COUNT set function, the SUM set function, the MAX set function and the MIN set function, there is a grouping clause in the first query statement, the grouping column in the first query statement is a bitmap index column, and the parameter in the set function in the first query statement is a constant, a bitmap index column and a preset identifier. The preset conditions can also be: the first query statement is a query statement for a single table, the set function in the first query statement is at least one of the COUNT set function, the SUM set function, the MAX set function and the MIN set function, there is no grouping clause in the first query statement, the parameter in the set function in the first query statement is a constant, a bitmap index column and any one of the preset identifiers.

[0031] The method of updating the first query statement based on the update rule can be: updating and rewriting the first query statement based on the update rule, and the update lays a foundation for subsequent query operations based on the bitmap index.

[0032] The update rule includes: updating COUNT(*) in the first query statement to SUM(MATCH_COUNT); updating COUNT(A) in the first query statement to SUM(MATCH_COUNT);

[0033] COUNT(COL)=>SUM(NVL2(COL,MATCH_COUNT,0));

[0034] SUM(A)=>A*SUM(MATCH_COUNT); (* here means multiplication)

[0035] SUM(COL)=>SUM(NVL(COL,0)*MATCH_COUNT); (* here means multiplication)

[0036] MAX(COL)=>MAX(COL);

[0037] MIN(COL)=>MIN(COL);

[0038] MAX(A)=>MAX(A);

[0039] MIN(A)=>MIN(A);

[0040] Optionally, if the first query statement meets a preset condition, the first query statement is updated based on an update rule to obtain a target query statement:

[0041] If the first query statement is a query statement for a single table, the set functions in the first query statement are all target set functions, and the parameters of the target set functions in the first query statement are any one of constants, bitmap index columns, and preset identifiers, then the first query statement is updated based on the update rule to obtain a target query statement, wherein the target set function includes: at least one of: COUNT set function, SUM set function, MAX set function, and MIN set function.

[0042] Specifically, a single-table query statement refers to a query statement that only involves one table. For example, if the query statement is SELECT A FROM T1, it is a single-table query on T1. If the query statement is SELECT T1.A, T2.B FROM T1, T2, it is a dual-table query on T1 and T2, and is not a single-table query statement.

[0043] A set function is a function with a set class as its domain. The set functions in the first query statement are all target set functions, and the target set functions include at least one of the COUNT set function, the SUM set function, the MAX set function, and the MIN set function. Among them, the COUNT set function means calculating the number of non-empty values ​​of the specified parameters in the group; the SUM set function means calculating the sum of the non-empty values ​​of the specified parameters in the group. For example: if grouping is performed based on the grouping clause, group 1, group 2, group 3, and group 4 are obtained. The sum S1 of the non-empty values ​​of the specified parameters in group 1, the sum S2 of the non-empty values ​​of the specified parameters in group 2, the sum S3 of the non-empty values ​​of the specified parameters in group 3, and the sum S4 of the non-empty values ​​of the specified parameters in group 4 are calculated; the MAX set function means calculating the maximum value of the non-empty values ​​of the specified parameters in the group; the MIN set function means calculating the minimum value of the non-empty values ​​of the specified parameters in the group.

[0044] The parameter of the target set function in the first query statement must be any one of a constant, a bitmap index column, and a preset identifier. A constant is an unchanging quantity that is not modified by the computer program during execution, such as the integer constant 25 or the character constant 'a'. A bitmap index column is a descriptive column in a table. For example, if a bitmap index I1 is created on column C2 of T1, column C2 becomes a bitmap index column. The preset identifier can be "*," such as COUNT(*), which indicates the number of rows in the calculation group.

[0045] Specifically, if the first query statement is a query statement for a single table, the set functions in the first query statement are all target set functions, and the parameters of the target set functions in the first query statement are any one of constants, bitmap index columns, and preset identifiers, then the first query statement is updated based on the update rule, and the target query statement is obtained in the following manner: If the first query statement is a query statement for a single table, the set functions in the first query statement are all target set functions, the first query statement does not include a grouping clause, and the parameters of the target set functions in the first query statement are any one of constants, bitmap index columns, and preset identifiers, then the first query statement is updated based on the update rule to obtain the target query statement.

[0046] Optionally, if the first query statement is a query statement for a single table, all set functions in the first query statement are target set functions, and parameters of the target set functions in the first query statement are any one of a constant, a bitmap index column, and a preset identifier, then updating the first query statement based on an update rule to obtain a target query statement includes:

[0047] If the first query statement is a query statement for a single table, the set functions in the first query statement are all target set functions, the first query statement has a grouping clause, the grouping column of the first query statement is a bitmap index column, and the parameter of the target set function in the first query statement is any one of a constant, a bitmap index column and a preset identifier, then the first query statement is updated based on the update rule to obtain the target query statement.

[0048] The grouping clause is an SQL statement that performs a grouping operation, such as a GROUP BY clause. When performing a grouped query, the result set is grouped according to one or more grouping columns. Note that grouping functions must be grouped before they can be used. GROUP BY also supports grouping multiple fields simultaneously, separated by "," between multiple fields. The grouping columns are the fields specified in the GROUP BY clause. Records in the result set with the same grouping columns are called a group. It should be noted that if a grouping clause exists in the first query statement, the preset conditions can only be met if the grouping columns are bitmap index columns.

[0049] Optionally, the update rule includes at least one of the following rules:

[0050] Update the COUNT set function whose parameter is a constant or a preset identifier in the first query statement to a SUM set function whose parameter is MATCH_COUNT;

[0051] Update the COUNT set function whose parameter is the bitmap index column in the first query statement to the SUM set function whose parameter is the first function, where the first function is a function that returns MATCH_COUNT when the value of the bitmap index column is not empty, and returns the first value when the value of the bitmap index column is empty;

[0052] Update the SUM function whose parameter is a constant in the first query statement to the product of the SUM function whose parameter is MATCH_COUNT and the constant;

[0053] Update the SUM function whose parameter is the bitmap index column in the first query statement to a SUM function whose parameter is the product of the second function and MATCH_COUNT. The second function returns the value of the bitmap index column when the value of the bitmap index column is not empty, and returns the first value when the value of the bitmap index column is empty.

[0054] Keep the MAX set function whose parameters in the first query statement are bitmap index columns or constants unchanged;

[0055] Keep the MIN set function whose parameters in the first query statement are bitmap index columns or constants unchanged.

[0056] Specifically, the update rules for set functions in SQL statements are as follows:

[0057] COUNT(*)=>SUM(MATCH_COUNT);

[0058] COUNT(A)=>SUM(MATCH_COUNT);

[0059] COUNT(COL)=>SUM(NVL2(COL,MATCH_COUNT,0));

[0060] SUM(A)=>A*SUM(MATCH_COUNT); (* here means multiplication)

[0061] SUM(COL)=>SUM(NVL(COL,0)*MATCH_COUNT); (* here means multiplication)

[0062] MAX(COL)=>MAX(COL);

[0063] MIN(COL)=>MIN(COL);

[0064] MAX(A)=>MAX(A);

[0065] MIN(A)=>MIN(A);

[0066] A represents a constant, and COL represents a column. The NVL(COL,0) function returns the COL value if it is not NULL, or 0 if it is NULL. The NVL2(COL,MATCH_COUNT,0) function returns MATCH_COUNT if it is not NULL, or 0 if it is NULL. The first value is a preset value, which can be 0.

[0067] Optionally, also include:

[0068] The target table name in the first query statement is updated to the bitmap index auxiliary table name.

[0069] The name of the bitmap index auxiliary table may be BM$+index name, or other forms, which is not limited in the embodiment of the present invention.

[0070] The target table name is the field corresponding to the FROM clause in the first query statement, for example, FROMT1, where T1 is the target table name.

[0071] Specifically, the target table name in the first query statement is updated to the bitmap index auxiliary table name. For example, FROM T1 may be updated to FROM BM$_I1, where T1 is the target table name and BM$_I1 represents the auxiliary table name corresponding to the bitmap index I1.

[0072] For example, create table T1: CREATE TABLE T1(C1 INT, C2 INT); create a bitmap index I1 on column C2 of T1, and the name of the bitmap index auxiliary table is BM$_I1; the first query statement is SELECT COUNT(5), SUM(C2), MAX(C2) FROM T1 GROUP BY C2; the first query statement is a query statement for table T1, that is, a query statement for a single table, the first query statement includes set functions: COUNT(5), SUM(C2), MAX(C2), COUNT(5), SUM(C2), MAX(C2) are all target set functions, the parameter of COUNT(5) is a constant, the parameter of SUM(C2) is a bitmap index column, and the parameter of MAX(C2) is a bitmap index column. The first query statement includes a grouping clause: GROUP BY C2, C2 is the grouping column. Since the bitmap index column is C2, the grouping column and the bitmap index column are the same. Therefore, the first query statement meets the preset conditions and can be updated according to the update rule. Update COUNT(5) in the first query statement to SUM(MATCH_COUNT), update SUM(C2) in the first query statement to SUM(NVL(C2,0)*MATCH_COUNT), keep MAX(C2) in the first query statement unchanged, and update table T1 in the first query statement to the name of the bitmap index auxiliary table corresponding to the bitmap index column. The updated target query statement is:

[0073] SELECT SUM(MATCH_COUNT),SUM(NVL(C2,0)*MATCH_COUNT),MAX(C2)FROM BM$_I1GROUP BY C2;

[0074] Among them, BM$_I1 represents the name of the auxiliary table corresponding to the bitmap index I1.

[0075] S120: Execute the target query statement to obtain the target query result.

[0076] The target query results above are obtained by executing the target query statement and querying the database. By directly executing the target query statement, the target query results can be obtained more quickly and conveniently using the bitmap index auxiliary table, improving query efficiency.

[0077] The technical method of this embodiment updates the first query statement based on the update rule if the first query statement meets the preset conditions, obtains the target query statement, executes the target query statement, obtains the target query result, and updates and rewrites the query statement that meets the preset conditions, thereby avoiding the need to query the queried table row by row during the query, thereby improving the query efficiency.

[0078] Example 2

[0079] Based on the above-mentioned embodiment 1, this embodiment provides a preferred implementation of a query method, which executes the target query statement to obtain a target query result, including: obtaining a bitmap index auxiliary table based on the bitmap index column; if the target query statement includes a grouping clause, grouping the bitmap index auxiliary table according to the grouping column; determining the target value corresponding to each group based on the target set function in the target query statement and the bitmap index auxiliary table; and determining the target value corresponding to each group as the target query result. Figure 2 This is a flowchart of a query method provided in the second embodiment of the present invention, such as Figure 2 As shown, the method specifically includes the following steps:

[0080] S210: If the first query statement meets a preset condition, the first query statement is updated based on an update rule to obtain a target query statement.

[0081] It should be noted that this step is the same as the first step in Example 1 and will not be described in detail here.

[0082] S220: Obtain a bitmap index auxiliary table according to the bitmap index column.

[0083] Specifically, a bitmap index auxiliary table is obtained based on a base table, where the base table is the table where the bitmap index is located. Exemplarily, the bitmap index auxiliary table may include: the value of the bitmap index column in the current data segment, the bit array corresponding to the current value, the number of 1 values ​​in the current bit array (MATCH_COUNT), and the starting row number corresponding to the current data segment.

[0084] For example, a table T1 (C1 int, C2 int) contains four rows of data: (1, 3), (2, 3), (3, 3), and (1, 4), with row numbers 1, 2, 3, and 4, respectively. A bitmap index is created on column C2 of T1. Due to the small amount of data, all data can be divided into a single data segment. Within this data segment, C2 can only have two values: 3 and 4. Therefore, the auxiliary table of the bitmap index will have two rows: one for C2 = 3 and one for C2 = 4. The bit array corresponding to C2 = 3 is 1110. A bit with a value of 1 in the array indicates that the value of column C2 in the corresponding row in the base table is equal to 3. 1110 indicates that the value of column C2 in the first, second, and third rows of the base table, starting from the starting row, is equal to 3. MATCH_COUNT records the number of values ​​1 in this bit array; in this example, MATCH_COUNT is 3.

[0085] S230: If the target query statement includes a grouping clause, the bitmap index auxiliary table is grouped according to the grouping columns.

[0086] If the target query statement contains a grouping clause, that is, if the GROUP BY clause exists, the bitmap index auxiliary table is grouped according to the grouping columns. For example, if the target query statement is GROUP BY C2, the bitmap index auxiliary table is grouped according to the different values ​​of the grouping column C2. For example, the rows in the bitmap index auxiliary table where C2 = 10 are grouped together, and the rows in the bitmap index auxiliary table where C2 = 20 are grouped together.

[0087] S240: Determine the target value corresponding to each group according to the target set function in the target query statement and the bitmap index auxiliary table.

[0088] Specifically, the method for determining the target value corresponding to each group based on the target set function in the target query statement and the bitmap index auxiliary table can be: substitute the MATCH_COUNT corresponding to each group in the bitmap index auxiliary table into the target query statement to obtain the target value corresponding to each group in the bitmap index auxiliary table.

[0089] Optionally, get the MATCH_COUNT corresponding to each group in the bitmap index auxiliary table;

[0090] The target value corresponding to each group is determined according to the MATCH_COUNT corresponding to each group in the bitmap index auxiliary table and the target set function in the target query statement.

[0091] Specifically, the method for obtaining the MATCH_COUNT corresponding to each group in the bitmap index auxiliary table can be: if the first target group in the bitmap index auxiliary table includes at least two rows of data, then obtain the MATCH_COUNT corresponding to each row, and determine the sum of the MATCH_COUNT corresponding to at least two rows as the MATCH_COUNT corresponding to the first target group in the bitmap index auxiliary table; if the second target group in the bitmap index auxiliary table is only one row of data, then determine the MATCH_COUNT corresponding to the one row as the MATCH_COUNT corresponding to the second target group in the bitmap index auxiliary table.

[0092] Specifically, the target value corresponding to each group can be determined based on the MATCH_COUNT corresponding to each group in the bitmap index auxiliary table and the target set function in the target query statement: if the set function in the target query statement is SUM(MATCH_COUNT), the MATCH_COUNT value corresponding to each group is determined as the target value corresponding to each group. If the set function in the target query statement is SUM(NVL(COL,0)*MATCH_COUNT) and COL is not null, the product of the value of COL and the MATCH_COUNT corresponding to each group is determined as the target value corresponding to each group. If COL is null, the target value corresponding to each group is 0. If the set function in the target query statement is SUM(NVL2(COL,MATCH_COUNT,0)) and COL is not null, the MATCH_COUNT value of each group is directly determined as the target value corresponding to each group. If COL is null, the target value corresponding to each group is 0. If the set function in the target query statement is A*SUM(MATCH_COUNT), the target value for each group is determined by multiplying the MATCH_COUNT value for each group by A. The MATCH_COUNT value for each group is the sum of the MATCH_COUNT values ​​for each row in the group.

[0093] For example, if the bitmap index auxiliary table includes group 1, group 2 and group 3, and if the set function in the target query statement is SUM(MATCH_COUNT), the MATCH_COUNT corresponding to group 1 is determined as the target value corresponding to group 1, the MATCH_COUNT corresponding to group 2 is determined as the target value corresponding to group 2, and the MATCH_COUNT corresponding to group 3 is determined as the target value corresponding to group 3. If the set function in the target query statement is SUM(NVL(C2,0)*MATCH_COUNT), and C2 is not empty, the product of the value of C2 and the MATCH_COUNT corresponding to group 1 is determined as the target value corresponding to group 1. If C2 is empty, the target value corresponding to group 1 is 0. If C2 is not empty, the product of the value of C2 and the MATCH_COUNT corresponding to group 2 is determined as the target value corresponding to group 2. If C2 is empty, the target value corresponding to group 2 is 0. If C2 is not empty, the product of the value of C2 and the MATCH_COUNT corresponding to group 3 is determined as the target value corresponding to group 3. If C2 is empty, the target value corresponding to group 3 is 0. If the set function in the target query statement is SUM(NVL2(C2,MATCH_COUNT,0)), and C2 is not empty, then the value of MATCH_COUNT for group 1 is directly determined to be the target value corresponding to group 1. If C2 is empty, the target value corresponding to group 1 is 0. If C2 is not empty, then the value of MATCH_COUNT for group 2 is directly determined to be the target value corresponding to group 2. If C2 is empty, the target value corresponding to group 2 is 0. If C2 is not empty, then the value of MATCH_COUNT for group 3 is directly determined to be the target value corresponding to group 3. If C2 is empty, the target value corresponding to group 3 is 0. If the set function in the target query statement is A*SUM(MATCH_COUNT), then the product of the MATCH_COUNT value corresponding to each group and A is determined as the target value corresponding to each group.

[0094] S250: Determine the target value corresponding to each group as the target query result.

[0095] Specifically, the target value corresponding to each group is determined as the target query result. For example, if the bitmap index auxiliary table includes group 1, group 2 and group 3, the target value corresponding to group 1 is X, the target value corresponding to group 2 is Y, and the target value corresponding to group 3 is Z, then X, Y, and Z are determined as the target query results.

[0096] The technical solution of this embodiment utilizes a bitmap index auxiliary table to group the bitmap index auxiliary table according to the grouping column, further determines the target value corresponding to each group based on the target set function in the target query statement and the bitmap index auxiliary table, and determines the target value corresponding to each group as the target query result, thereby avoiding the need to query every row of data in the table being queried during each query process, making it more convenient for users to perform group queries and improving query efficiency.

[0097] Example 3

[0098] Based on the above-mentioned embodiment 1, this embodiment provides a preferred implementation of a query method, which executes the target query statement to obtain the target query result, including: obtaining a bitmap index auxiliary table based on the bitmap index column; if the target query statement does not include a grouping clause, determining the target query result based on the MATCH_COUNT column in the bitmap index auxiliary table. Figure 3 This is a flowchart of a query method provided in Example 3 of the present invention, such as Figure 3 As shown, the method specifically includes the following steps:

[0099] S310: If the first query statement meets a preset condition, the first query statement is updated based on an update rule to obtain a target query statement.

[0100] It should be noted that this step is the same as the first step in Example 1 and will not be described in detail here.

[0101] S320: Obtain a bitmap index auxiliary table according to the bitmap index column.

[0102] Specifically, this step is the same as the second step in Example 2. First, a bitmap index auxiliary table is obtained according to the bitmap index column. The bitmap index auxiliary table serves as the query basis for subsequent target query statements and will not be described in detail here.

[0103] S330: If the target query statement does not include a grouping clause, the target query result is determined according to the MATCH_COUNT column in the bitmap index auxiliary table.

[0104] Specifically, if the target query statement does not include a grouping clause, the target query result may be determined based on the MATCH_COUNT column in the bitmap index auxiliary table by: when it is determined that the target query statement does not include a grouping clause, and the set function in the target query statement is SUM(MATCH_COUNT), the MATCH_COUNT column in the bitmap index auxiliary table is directly queried, and the sum of the MATCH_COUNTs in the queried MATCH_COUNT column is output as the target query result. If the target query statement does not include a grouping clause, the target query result may also be determined based on the MATCH_COUNT column in the bitmap index auxiliary table by: when it is determined that the target query statement does not include a grouping clause, and the set function in the target query statement is A*SUM(MATCH_COUNT), the sum of the MATCH_COUNTs in the queried MATCH_COUNT column and the product of A are output as the target query result. If the target query statement does not include a grouping clause, the target query result may be determined based on the MATCH_COUNT column in the bitmap index auxiliary table as follows: when it is determined that the target query statement does not include a grouping clause, and the set function in the target query statement is SUM(NVL(COL,0)*MATCH_COUNT), the product of the non-null value of COL in the bitmap index auxiliary table and the MATCH_COUNT value corresponding to the value is determined as the temporary target value corresponding to the value, and the sum of the temporary target values ​​corresponding to all non-null values ​​of COL in the bitmap index auxiliary table is output as the target query result. If the target query statement does not include a grouping clause, the target query result may be determined based on the MATCH_COUNT column in the bitmap index auxiliary table as follows: when it is determined that the target query statement does not include a grouping clause, and the set function in the target query statement is SUM(NVL2(COL,MATCH_COUNT,0)), the sum of the MATCH_COUNT values ​​corresponding to all non-null values ​​of COL in the bitmap index auxiliary table is output as the target query result. For example, when GROUP BY C2 appears in the query statement, all different values ​​of the C2 column need to be grouped before subsequent query output can be performed; if GROUP BY C2 does not appear in the query statement and the set function in the target query statement is SUM(MATCH_COUNT), the sum of the MATCH_COUNT in the MATCH_COUNT column is obtained and determined as the target query result.

[0105] The technical solution of this embodiment obtains a bitmap index auxiliary table, lists the situations where the target query statement does not contain a grouping clause, and uses a method of directly determining the final target query result based on the bitmap index auxiliary table, thereby avoiding rewriting the target query statement, directly obtaining the query result, and improving query efficiency.

[0106] Example 4

[0107] Figure 4 This is a schematic diagram of the structure of a query device provided by the fourth embodiment of the present invention. This embodiment is applicable to situations where query operations can be performed based on SQL statements. The device can be implemented in software and / or hardware. The device can be integrated into any device that provides query functions, such as Figure 4 As shown, the query device specifically includes: an updating module 410 and a target query result determining module 420.

[0108] The updating module 410 is configured to update the first query statement based on an updating rule to obtain a target query statement if the first query statement satisfies a preset condition;

[0109] The target query result determination module 420 is used to execute the target query statement to obtain the target query result.

[0110] Optionally, the update module is specifically configured to:

[0111] If the first query statement is a query statement for a single table, the set functions in the first query statement are all target set functions, and the parameters of the target set functions in the first query statement are any one of constants, bitmap index columns, and preset identifiers, then the first query statement is updated based on the update rule to obtain a target query statement, wherein the target set function includes: at least one of: COUNT set function, SUM set function, MAX set function, and MIN set function.

[0112] Optionally, the update module is specifically configured to:

[0113] If the first query statement is a query statement for a single table, the set functions in the first query statement are all target set functions, the first query statement has a grouping clause, the grouping column of the first query statement is a bitmap index column, and the parameter of the target set function in the first query statement is any one of a constant, a bitmap index column and a preset identifier, then the first query statement is updated based on the update rule to obtain the target query statement.

[0114] Optionally, the update rule includes at least one of the following rules:

[0115] Update the COUNT set function whose parameter is a constant or a preset identifier in the first query statement to a SUM set function whose parameter is MATCH_COUNT;

[0116] Update the COUNT set function whose parameter is the bitmap index column in the first query statement to a SUM set function whose parameter is the first function, wherein the first function is a function that returns MATCH_COUNT when the value of the bitmap index column is not empty, and returns the first value when the value of the bitmap index column is empty;

[0117] Update the SUM set function whose parameter is a constant in the first query statement to the product of the SUM set function whose parameter is MATCH_COUNT and the constant;

[0118] Update the SUM set function whose parameter is the bitmap index column in the first query statement to a SUM set function whose parameter is the product of the second function and MATCH_COUNT, wherein the second function is a function that returns the value of the bitmap index column when the value of the bitmap index column is not empty, and returns the first value when the value of the bitmap index column is empty;

[0119] Keep the MAX set function whose parameters in the first query statement are bitmap index columns or constants unchanged;

[0120] The MIN set function whose parameters in the first query statement are bitmap index columns or constants remains unchanged.

[0121] Optionally, the update rule further includes:

[0122] Update the target table name in the first query statement to the bitmap index auxiliary table name.

[0123] Optionally, the target query result determination module is specifically configured to:

[0124] Obtaining a bitmap index auxiliary table according to the bitmap index column;

[0125] If the target query statement includes a grouping clause, grouping the bitmap index auxiliary table according to the grouping column;

[0126] Determine the target value corresponding to each group according to the target set function in the target query statement and the bitmap index auxiliary table;

[0127] The target value corresponding to each group is determined as the target query result.

[0128] Optionally, the target query result determination module is specifically configured to:

[0129] Get the MATCH_COUNT corresponding to each group in the bitmap index auxiliary table;

[0130] The target value corresponding to each group is determined according to the MATCH_COUNT corresponding to each group in the bitmap index auxiliary table and the target set function in the target query statement.

[0131] Optionally, the target query result determination module is specifically configured to:

[0132] If the target query statement does not include a grouping clause, the target query result is determined according to the MATCH_COUNT column in the bitmap index auxiliary table.

[0133] The technical solution of this embodiment introduces a bitmap index during the query process by updating and rewriting the SQL statements that meet the conditions, thereby avoiding the complete query of every row of data in the queried table during each query process, and converting the query of the base table into a query of the bitmap index auxiliary table, making it more convenient for users to perform queries and improving query efficiency.

[0134] Example 5

[0135] Figure 5 Schematic diagram of the structure of an electronic device provided for embodiment five of the present invention. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device may also represent various forms of mobile devices, such as personal digital processing, cellular phones, smart phones, wearable devices (such as helmets, glasses, watches, etc.) and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the present invention described and / or required herein.

[0136] like Figure 5 As shown, the electronic device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12, a random access memory (RAM) 13, etc., which is communicatively connected to the at least one processor 11. The memory stores a computer program that can be executed by the at least one processor. The processor 11 can perform various appropriate actions and processes according to the computer program stored in the read-only memory (ROM) 12 or the computer program loaded from the storage unit 18 into the random access memory (RAM) 13. Various programs and data required for the operation of the electronic device 10 can also be stored in the RAM 13. The processor 11, ROM 12, and RAM 13 are connected to each other via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0137] Multiple components in the electronic device 10 are connected to the I / O interface 15, including an input unit 16, such as a keyboard, a mouse, etc.; an output unit 17, such as various types of displays, speakers, etc.; a storage unit 18, such as a magnetic disk, an optical disk, etc.; and a communication unit 19, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 19 allows the electronic device 10 to exchange information / data with other devices via a computer network such as the Internet and / or various telecommunication networks.

[0138] The processor 11 may be any general-purpose and / or specialized processing component with processing and computing capabilities. Some examples of the processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various specialized artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any appropriate processor, controller, microcontroller, etc. The processor 11 executes the various methods and processes described above, such as the query method.

[0139] In some embodiments, the query method can be implemented as a computer program that is tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program can be loaded and / or installed on electronic device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the query method described above can be performed. Alternatively, in other embodiments, processor 11 can be configured to perform the query method in any other appropriate manner (e.g., by means of firmware).

[0140] Various embodiments of the systems and techniques described herein can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), system-on-chip systems (SOCs), programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments can include being implemented in one or more computer programs that are executable and / or interpreted on a programmable system that includes at least one programmable processor, which can be a special purpose or general purpose programmable processor that can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.

[0141] Computer programs for implementing the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when the computer program is executed by the processor, the functions / operations specified in the flowcharts and / or block diagrams are implemented. The computer program may be executed entirely on the machine, partially on the machine, as a stand-alone software package, partially on the machine and partially on a remote machine, or entirely on a remote machine or server.

[0142] In the context of the present invention, computer-readable storage media can be tangible media that can contain or store a computer program for use with an instruction execution system, device or equipment or used in combination with an instruction execution system, device or equipment. Computer-readable storage media can include but are not limited to electronic, magnetic, optical, electromagnetic, infrared or semiconductor systems, devices or equipment, or any suitable combination of the foregoing. Alternatively, computer-readable storage media can be machine-readable signal media. More specific examples of machine-readable storage media can include electrical connections based on one or more lines, portable computer disks, hard disks, random access memories (RAM), read-only memories (ROM), erasable programmable read-only memories (EPROM or flash memory), optical fibers, portable compact disk read-only memories (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination of the foregoing.

[0143] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user can provide input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, the feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including acoustic input, voice input, or tactile input).

[0144] The systems and techniques described herein can be implemented in a computing system that includes back-end components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes front-end components (e.g., a user computer with a graphical user interface or web browser through which a user can interact with implementations of the systems and techniques described herein), or a computing system that includes any combination of such back-end components, middleware components, or front-end components. The components of the system can be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.

[0145] A computing system may include clients and servers. The clients and servers are typically remote from each other and typically interact via a communication network. This client-server relationship arises through computer programs running on the respective computers, creating a client-server relationship. The server may be a cloud server, also known as a cloud computing server or cloud host. This server is a hosting product within a cloud computing service ecosystem that addresses the management difficulties and limited scalability of traditional physical hosting and VPS services.

[0146] It should be understood that the various forms of the processes shown above can be used to reorder, add, or delete steps. For example, the steps described in the present invention can be performed in parallel, sequentially, or in a different order, as long as the desired results of the technical solution of the present invention can be achieved. This is not limited herein.

[0147] The above specific embodiments do not limit the scope of protection of the present invention. Those skilled in the art will appreciate that various modifications, combinations, sub-combinations, and substitutions may be made based on design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention are intended to be included within the scope of protection of the present invention.

Claims

1. A query method, characterized in that: include: If the first query statement meets the preset condition, the first query statement is updated based on the update rule to obtain a target query statement; Execute the target query statement to obtain the target query result; If the first query statement meets the preset condition, the first query statement is updated based on the update rule to obtain the target query statement, including: If the first query statement is a query statement for a single table, all set functions in the first query statement are target set functions, and parameters of the target set functions in the first query statement are any one of a constant, a bitmap index column, and a preset identifier, then the first query statement is updated based on an update rule to obtain a target query statement, wherein the target set function includes: at least one of a COUNT set function, a SUM set function, a MAX set function, and a MIN set function; The update rule includes at least one of the following rules: Update the COUNT set function whose parameter is a constant or a preset identifier in the first query statement to a SUM set function whose parameter is MATCH_COUNT; Update the COUNT set function whose parameter is the bitmap index column in the first query statement to a SUM set function whose parameter is the first function, wherein the first function is a function that returns MATCH_COUNT when the value of the bitmap index column is not empty, and returns the first value when the value of the bitmap index column is empty; Update the SUM set function whose parameter is a constant in the first query statement to the product of the SUM set function whose parameter is MATCH_COUNT and the constant; Update the SUM set function whose parameter is the bitmap index column in the first query statement to a SUM set function whose parameter is the product of the second function and MATCH_COUNT, wherein the second function is a function that returns the value of the bitmap index column when the value of the bitmap index column is not empty, and returns the first value when the value of the bitmap index column is empty; Keep the MAX set function whose parameters in the first query statement are bitmap index columns or constants unchanged; Keep the MIN set function whose parameters in the first query statement are bitmap index columns or constants unchanged; Execute the target query statement to obtain the target query result, including: Get the bitmap index auxiliary table based on the bitmap index column; If the target query statement includes a grouping clause, grouping the bitmap index auxiliary table according to the grouping column; Determine the target value corresponding to each group according to the target set function in the target query statement and the bitmap index auxiliary table; The target value corresponding to each group is determined as the target query result.

2. The method according to claim 1, characterized in that If the first query statement is a query statement for a single table, all set functions in the first query statement are target set functions, and parameters of the target set functions in the first query statement are any one of a constant, a bitmap index column, and a preset identifier, then updating the first query statement based on an update rule to obtain a target query statement includes: If the first query statement is a query statement for a single table, the set functions in the first query statement are all target set functions, the first query statement has a grouping clause, the grouping column of the first query statement is a bitmap index column, and the parameter of the target set function in the first query statement is any one of a constant, a bitmap index column and a preset identifier, then the first query statement is updated based on the update rule to obtain the target query statement.

3. The method according to claim 1, characterized in that The update rules also include: Update the target table name in the first query statement to the bitmap index auxiliary table name.

4. The method according to claim 1, wherein Determining a target value corresponding to each group according to the target set function in the target query statement and the bitmap index auxiliary table includes: Get the MATCH_COUNT corresponding to each group in the bitmap index auxiliary table; The target value corresponding to each group is determined according to the MATCH_COUNT corresponding to each group in the bitmap index auxiliary table and the target set function in the target query statement.

5. The method according to claim 1, wherein Execute the target query statement to obtain the target query result, including: Get the bitmap index auxiliary table based on the bitmap index column; If the target query statement does not include a grouping clause, the target query result is determined according to the MATCH_COUNT column in the bitmap index auxiliary table.

6. A query device, characterized in that: include: An updating module, configured to update the first query statement based on an updating rule to obtain a target query statement if the first query statement meets a preset condition; A target query result determination module is used to execute the target query statement to obtain the target query result; The update module is specifically used to update the first query statement based on the update rule to obtain a target query statement if the first query statement is a query statement for a single table, the set functions in the first query statement are all target set functions, and the parameters of the target set functions in the first query statement are any one of a constant, a bitmap index column, and a preset identifier, wherein the target set function includes: at least one of a COUNT set function, a SUM set function, a MAX set function, and a MIN set function; The update rule includes at least one of the following rules: Update the COUNT set function whose parameter is a constant or a preset identifier in the first query statement to a SUM set function whose parameter is MATCH_COUNT; Update the COUNT set function whose parameter is the bitmap index column in the first query statement to a SUM set function whose parameter is the first function, wherein the first function is a function that returns MATCH_COUNT when the value of the bitmap index column is not empty, and returns the first value when the value of the bitmap index column is empty; Update the SUM set function whose parameter is a constant in the first query statement to the product of the SUM set function whose parameter is MATCH_COUNT and the constant; Update the SUM set function whose parameter is the bitmap index column in the first query statement to a SUM set function whose parameter is the product of the second function and MATCH_COUNT, wherein the second function is a function that returns the value of the bitmap index column when the value of the bitmap index column is not empty, and returns the first value when the value of the bitmap index column is empty; Keep the MAX set function whose parameters in the first query statement are bitmap index columns or constants unchanged; Keep the MIN set function whose parameters in the first query statement are bitmap index columns or constants unchanged; The target query result determination module is specifically used to obtain a bitmap index auxiliary table based on a bitmap index column; if the target query statement includes a grouping clause, the bitmap index auxiliary table is grouped according to the grouping column; the target value corresponding to each group is determined based on the target set function in the target query statement and the bitmap index auxiliary table; and the target value corresponding to each group is determined as the target query result.

7. An electronic device, characterized in that: The electronic device comprises: at least one processor; and a memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor. The computer program is executed by the at least one processor to enable the at least one processor to perform the query method according to any one of claims 1 to 5.

8. A computer-readable storage medium, characterized in that The computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement the query method according to any one of claims 1 to 5 when executed.

Images