Database query statement processing method, storage medium and device

Abstract

This invention provides a method, storage medium, and computer device for processing database query statements. The method includes: obtaining a query statement containing the keyword "UNION ALL"; determining whether the column attributes of the data tables involved in the "UNION ALL" keyword are consistent; if not, converting the column attributes of the data tables to be consistent; and continuing to execute the query statement. This database query statement processing method converts the column attributes of the two query statements involving the "UNION ALL" keyword in a database query statement to the same data type, thereby eliminating the subquery of "UNION ALL," thus reducing database query time, improving database response speed, and significantly enhancing database performance.

Description

Technical Field

[0001] This invention relates to database technology, and in particular to a method, storage medium, and device for processing database query statements. Background Technology

[0002] In some database use cases, it's necessary to merge information from multiple tables. A common approach is to use the `UNION ALL` keyword in the query statement to combine two result sets. However, the use of the `UNION ALL` keyword has several limitations: the `SELECT` statements within `UNION ALL` must have the same number of columns, and these columns must have similar data types. When the columns in the `SELECT` statements involved in `UNION ALL` only have similar data types, the internal execution of `UNION ALL` will consume a lot of time, leading to performance degradation and slow database response. Summary of the Invention

[0003] One objective of this invention is to improve database performance.

[0004] A further objective of this invention is to improve the response speed of the database.

[0005] A further objective of this invention is that the database can autonomously convert the column attributes involved in UNION ALL to the same type.

[0006] Specifically, the present invention provides a method for processing database query statements, comprising:

[0007] Retrieve query statements containing the keyword UNION ALL;

[0008] Determine if the column attributes of the data tables involved in the UNION ALL keyword are consistent;

[0009] If not, convert the column attributes of the data table to be consistent;

[0010] Continue executing the query statement.

[0011] Optionally, the step of determining whether the column attributes of the data tables involved in the UNION ALL keyword are consistent includes:

[0012] Retrieve the column attribute ID of the data table respectively;

[0013] Determine if the ID column attribute of the data table is equal.

[0014] Optionally, the step of determining whether the column attributes of the data tables involved in the UNION ALL keyword are consistent includes:

[0015] If so, no action will be taken on the column attributes of the data table, and the query statement will continue to be executed.

[0016] Optionally, the steps to convert the column attributes of the data table to be consistent include:

[0017] Retrieve the database's preset transformation rules;

[0018] Determine whether the transformation rules can transform the column attributes of the data table;

[0019] If so, convert the column attributes of the data table according to the conversion rules.

[0020] Optionally, the steps of obtaining the database's preset conversion rules include: obtaining the system table corresponding to the conversion rules in the database;

[0021] The steps to determine whether a conversion rule can convert the column attributes of a data table include: querying other types that the column attributes of the data table can be converted to in the system tables, and generating a set of column attribute types; taking the intersection of the column attribute type sets of the data tables involved in the UNION ALL keyword; and determining whether type information exists in the intersection.

[0022] Optionally, the type set includes: other broader types that the column attributes of the data table can be converted to, and the types of the column attributes themselves.

[0023] Optionally, after determining whether the transformation rule can transform the column attributes of the data table, the step may also include:

[0024] If not, no conversion will be performed, and the query operation will continue.

[0025] Optionally, the step of transforming the column attributes of the data table according to the transformation rules includes:

[0026] Generate a new target column based on the transformed column attributes;

[0027] Eliminate subqueries of UNION ALL.

[0028] According to another aspect of the present invention, a machine-readable storage medium is also provided, on which a machine-executable program is stored, wherein the machine-executable program, when executed by a processor, implements the processing method of the database query statement described above.

[0029] According to another aspect of the present invention, a computer device is also provided, including a memory, a processor, and a machine-executable program stored in the memory and running on the processor, wherein the processor executes the machine-executable program to implement the processing method of any of the above-described database query statements.

[0030] The database query processing method of the present invention first obtains the database query statement containing the UNION ALL keyword, then determines whether the column attributes of the data tables involved in each UNION ALL keyword are consistent; if not, the column attributes of the data tables are converted to be consistent; then, a new target column is generated according to the type, and the original UNION ALL subquery is eliminated, and the query statement continues to be executed. This method enables the conversion of the column attributes of the data tables involved in each UNION ALL keyword to the same type when executing a database query statement containing the UNION ALL keyword, thereby eliminating UNION ALL subqueries, improving the response speed of database queries, and enhancing database performance.

[0031] The database query processing method of this invention uses the following conversion rule for column attributes of data tables: When the database determines that the column attributes of the data tables involved in the UNION ALL keyword are inconsistent, it queries a pre-established system table in the database to find other data types with a wider range that the column attribute can be converted to, and generates a corresponding set. Then, it takes the intersection of the column attribute sets of each data table involved in the UNION ALL keyword. If data type information exists in the intersection, it selects that data type information for conversion. This method converts the column attributes involved in UNION ALL to the same type. This conversion rule allows the database to automatically convert the column attributes involved in UNION ALL to the same type during operation. Furthermore, the system table has pre-defined default content, which users can also define or modify, improving the convenience of database use.

[0032] The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments of the invention in conjunction with the accompanying drawings. Attached Figure Description

[0033] The following sections will describe some specific embodiments of the invention in detail by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings:

[0034] Figure 1 This is a flowchart illustrating a method for processing database query statements according to an embodiment of the present invention;

[0035] Figure 2 This is a flowchart illustrating a method for processing database query statements according to another embodiment of the present invention;

[0036] Figure 3This is a schematic diagram of a machine-readable storage medium in a database query statement processing method according to an embodiment of the present invention; and

[0037] Figure 4 This is a schematic diagram of a computer device in a database query statement processing method according to an embodiment of the present invention. Detailed Implementation

[0038] Those skilled in the art should understand that the embodiments described below are merely a part of the embodiments of the present invention, and not all of the embodiments of the present invention. These partial embodiments are intended to explain the technical principles of the present invention and are not intended to limit the scope of protection of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by those skilled in the art without creative effort should still fall within the scope of protection of the present invention.

[0039] In some database use cases, it's necessary to merge information from multiple tables. Users often choose to use the UNION ALL keyword in their query statements to combine two result sets. However, the use of the UNION ALL keyword has several limitations: the SELECT statements within UNION ALL must have the same number of columns, and the columns must have similar data types. When the columns in the SELECT statements involved in UNION ALL have similar but not identical data types, the internal execution of UNION ALL will consume a lot of time, leading to performance degradation and slow database response.

[0040] The method in this embodiment unifies the column attributes of the data tables involved in the UNION ALL keyword in the query statement, thereby changing the column attributes of the database involved in UNION ALL to a consistent data type without losing data precision, thus eliminating the subquery of UNION ALL. This improves the database's response speed to query operations and enhances database performance.

[0041] Figure 1 This is a flowchart illustrating a method for processing database query statements according to an embodiment of the present invention. The method generally includes the following steps:

[0042] Step S101: Obtain the query statement containing the keyword UNION ALL. This step includes: obtaining the query statement passed to the database, and determining whether the query statement contains the keyword UNION ALL. If so, continue with the subsequent steps.

[0043] Step S102: Determine if the column attributes of the data tables involved in the UNION ALL keyword are consistent. This step includes: obtaining the column attribute IDs of the data tables respectively; then determining if the column attribute IDs of the data tables are equal; if they are equal, it means that the column attributes of the data tables involved in the UNION ALL keyword are consistent, so the column attribute conversion step can be skipped directly, and the query statement can continue to be executed normally.

[0044] The column attribute ID refers to the ID that each data type has within the database system.

[0045] Step S103: If the determination in step S102 is negative, convert the column attributes of the data table to be consistent. This step includes: obtaining the database's preset conversion rules; determining whether the conversion rules can convert the column attributes of the data table; if so, converting the column attributes of the data table involved in the UNION ALL keyword to be consistent according to the conversion rules. The database's preset conversion rules are a system table preset in the database. This system table records other data types that can be converted from different data types, and users can update and modify this system table according to their actual needs. A specific example of this system table is shown in Table 1:

[0046] Table 1

[0047]

[0048]

[0049]

[0050] The steps for determining whether a conversion rule can convert the column attributes of the data table include: querying the system tables for other data types that the column attributes of the data table can be converted to, and generating a set of data types for the column attributes; this set of types includes other types with a wider range that the column attributes of the data table can be converted to, as well as the type of the column attributes themselves. Next, the intersection of the data type sets of the column attributes of the data tables involved in the UNION ALL keyword is taken; it is then determined whether type information exists in the intersection. If it exists, it means that the data types in the column attributes of the data tables involved in the UNION ALL keyword can be converted to the same data type, and the conversion is then performed.

[0051] A concrete example is when the column attributes of the tables involved in the two queries related to the UNION ALL keyword are INT2 (2 bytes) and INT (4 bytes). In this case, the column attributes of the two tables are similar but not identical, so conversion is necessary. After applying this method, the set generated for INT2 in the system table (Table 1) contains INT4, INT8, TEXT, FLOAT4, FLOAT8, NUMERIC, and itself (INT2); while the set generated for INT4 in the system table (Table 1) contains BOOL, INT8, TEXT, FLOAT4, FLOAT8, NUMERIC, and itself (INT4). After taking the intersection, the intersection of the two sets is INT4, INT8, TEXT, FLOAT4, FLOAT8, and NUMERIC. The database can then select the data type to be converted based on the actual situation to convert the column attributes of the two tables.

[0052] It should be noted that Table 1 is only a specific example to better explain this method. The contents of the table can be set by professionals in the field according to the actual situation, and users can also modify this table when using the database.

[0053] Step S104: Continue executing the query statement. Generate a new target column for the data table after transforming column attributes in step S103, then eliminate the subquery of UNION ALL, and then continue executing this query statement.

[0054] The database query statement processing method in this embodiment converts the column attributes in the two query statements involved by the UNION ALL keyword in the database query statement to the same data type, thereby eliminating the subquery of UNION ALL, thereby reducing the database query time, improving the database response speed, and enhancing the efficiency and performance of database query execution.

[0055] Figure 2 This is a flowchart illustrating a method for processing database query statements according to another embodiment of the present invention. The method generally includes the following steps:

[0056] Step S201: Obtain the database query statement.

[0057] Step S202: Determine if a subquery containing UNION ALL exists. That is, determine if the database query statement contains the UNION ALL keyword.

[0058] Step S203: If the condition in step S202 is met, determine whether the column attributes corresponding to the query statements involving the UNION ALL keyword are consistent. Internally, each data type has a corresponding ID. This step determines whether the data type IDs of the column attributes corresponding to the query statements involving the UNION ALL keyword are consistent in the database.

[0059] Step S204: If the determination in step S203 is negative, query the system table to check whether different types can be converted. This step includes: obtaining the database's preset conversion rules; determining whether the conversion rules can convert the column attributes of the data table; wherein the database's preset conversion rules are a preset system table in the database. This system table records other data types that can be converted from different data types, and users can also update and modify this system table according to actual needs.

[0060] The steps for determining whether a conversion rule can convert the column attributes of the data table include: querying the system tables for other data types that the column attributes of the data table can be converted to, and generating a set of data types for the column attributes; this set of types includes other types with a wider range that the column attributes of the data table can be converted to, as well as the type of the column attributes themselves. Next, the intersection of the data type sets of the column attributes of the data tables involved in the UNION ALL keyword is taken; it is then determined whether type information exists in the intersection. If it exists, it means that the data types in the column attributes of the data tables involved in the UNION ALL keyword can be converted to the same data type.

[0061] Step S205: If the determination in step S204 is yes, a new target column is generated for the SELECT subquery. When the determination result in step S204 is yes, it means that the data type of the column attributes of the data table involved in the UNION ALL keyword can be converted to the same data type. Therefore, the column attributes of the data table involved in the SELECT subquery, i.e., the UNION ALL keyword, are converted and a new target column is generated.

[0062] Step S206: Eliminate the UNION ALL subquery. This means that after generating a new target column for the SELECT subquery, the original UNION ALL subquery is deleted.

[0063] Step S207: When any of the following conditions occur: the result of step S202 is negative, the result of step S203 is positive, the result of step S204 is negative, or step S206 has been completed, the query operation is executed. Specifically, if the result of step S202 is negative, it means the query statement does not contain the `UNION ALL` keyword; in this case, no further judgment is needed, and the query operation is performed directly. If the result of step S203 is positive, it means the query statement contains the `UNION ALL` keyword, but the column attributes in the query statements involving the `UNION ALL` keyword are the same; in this case, no conversion is needed, and the query operation is performed. If the result of step S204 is negative, it means the query statement contains the `UNION ALL` keyword, and the column attributes in the query statements involving the `UNION ALL` keyword are different, but the column attributes in the database's system tables do not contain data types that can be converted; in this case, the conversion step is skipped, and the query operation is performed.

[0064] The database query processing method in this embodiment converts the column attributes in the two query statements involved by the UNION ALL keyword in the database query statement to the same data type, thereby eliminating the subquery of UNION ALL, thereby reducing the database query time, improving the database response speed, and greatly improving the database performance.

[0065] A specific example of this method is as follows:

[0066] First, create tables t1, t2, and t3, where the attributes of the name1 column are different in t1 and t2:

[0067] create table t1(id1 integer,seq1 integer,name1 integer);

[0068] create table t2(id2 integer,seq2 integer,name2 bigint);

[0069] create table t3(id3 integer,seq3 integer,name3 text);

[0070] Create an index:

[0071] create index ind_t1_id1 on t1(id1);

[0072] create index ind_t2_id1 on t2(id2);

[0073] create index ind_t3_id1 on t3(seq3);

[0074] Insert 1 million data entries:

[0075] insert into t1 select generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000);

[0076] insert into t2 select generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000);

[0077] insert into t3 select generate_series(1,1000000),generate_series(1,1000000),repeat('c',500);

[0078] Execute statements t1 and t2 UNION ALL, then join them with t3:

[0079] Explain analysis with t as(

[0080] select id1 as id,seq1 as seq,name1 as name from t1

[0081] union all

[0082] select id2 as id,seq2 as seq,name2 as name from t2 )

[0084] select count(*)from t,t3 where t.id=t3.id3 and t3.seq3=12345;

[0085] After executing the above statement, if the database system does not use this solution, the execution result shows that the Subquery Scan of table t1 in UNION ALL has not been eliminated, and the execution time of this query statement is 641.257ms.

[0086] After the database system adopted this solution, the Subquery Scan of table t1 was eliminated, and index scans were used for tables t1 and t2, reducing the execution time from 641.257ms to 0.083ms.

[0087] Therefore, the database's response speed and performance are greatly improved.

[0088] It should be noted that this example is merely a test to better explain this method and does not represent the entirety of this method.

[0089] This embodiment also provides a machine-readable storage medium and a computer device. Figure 3 This is a schematic diagram of a machine-readable storage medium in a database query statement processing method according to an embodiment of the present invention; Figure 4 This is a schematic diagram of a computer device in a database query statement processing method according to an embodiment of the present invention.

[0090] The machine-readable storage medium 301 stores a machine-executable program 302 thereon, which, when executed by a processor, implements the database query statement processing method of any of the above embodiments.

[0091] Computer device 403 may include memory 401, processor 402, and machine-executable program 302 stored on memory 401 and running on processor 402. When processor 402 executes machine-executable program 302, it implements the database query statement processing method of any of the above embodiments.

[0092] It should be noted that the logic and / or steps represented in the flowchart or otherwise described herein, such as data type conversion, may be specifically implemented in any machine-readable storage medium for use by, or in conjunction with, an instruction execution system, apparatus or device (such as a computer-based system, a processor-based system or other system that can fetch and execute instructions from, an instruction execution system, apparatus or device).

[0093] For the purposes of this embodiment, the machine-readable storage medium 301 can be any means capable of containing, storing, communicating, propagating, or transmitting a program for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of the machine-readable storage medium 301 include: an electrical connection (electronic device) having one or more wires, a portable computer disk drive (magnetic device), random access memory (RAM), read-only memory (ROM), erasable and editable read-only memory (EPROM or flash memory), fiber optic devices, and portable optical disc read-only memory (CDROM). Furthermore, the machine-readable storage medium 301 can even be paper or other suitable media on which the program can be printed, since the program can be obtained electronically, for example, by optically scanning the paper or other medium, followed by editing, interpreting, or otherwise processing as necessary, and then stored in a computer memory.

[0094] It should be understood that various parts of the present invention can be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented using software or firmware stored in memory and executed by a suitable instruction execution system.

[0095] Computer device 403 can be, for example, a server, desktop computer, laptop computer, tablet computer, or smartphone. In some examples, computer device 403 can be a cloud computing node. Computer device 403 can be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, program modules can include routines, programs, object programs, components, logic, data structures, etc., that perform specific tasks or implement specific abstract data types. Computer device 403 can be implemented in a distributed cloud computing environment where tasks are performed by remote processing devices linked through a communication network. In a distributed cloud computing environment, program modules can reside on local or remote computing system storage media, including storage devices.

[0096] Computer device 403 may include a processor 402 adapted to execute stored instructions and a memory 401 that provides temporary storage space for the operation of said instructions during operation. The processor 402 may be a single-core processor, a multi-core processor, a computing cluster, or any other configuration. The memory 401 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0097] The processor 402 can be connected via a system interconnect (e.g., PCI, PCI-Express, etc.) to an I / O interface (input / output interface) suitable for connecting the computer device 403 to one or more I / O devices (input / output devices). I / O devices may include, for example, a keyboard and indicating devices, where indicating devices may include a touchpad or touchscreen, etc. I / O devices may be built into the computer device 403 or may be external devices connected to the computing device.

[0098] The processor 402 may also be linked via a system interconnect to a display interface suitable for connecting the computer device 403 to a display device. The display device may include a display screen as a built-in component of the computer device 403. The display device may also include a computer monitor, television, or projector, etc., externally connected to the computer device 403. Furthermore, a network interface controller (NIC) may be adapted to connect the computer device 403 to a network via a system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as an Internet Minicomputer System Interface) to transmit data. The network may be a cellular network, a radio network, a wide area network (WAN), a local area network (LAN), or the Internet, etc. Remote devices may connect to the computing device via the network.

[0099] The flowchart provided in this embodiment is not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in every case. Furthermore, the method may include additional operations. Within the scope of the technical concept provided by the method in this embodiment, additional variations can be made to the above method.

[0100] Therefore, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the present invention can be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Thus, the scope of the present invention should be understood and construed as covering all such other variations or modifications.

Claims

1. A method for processing database query statements, comprising: Retrieve query statements containing the keyword UNION ALL; Determine whether the column attributes of the data tables involved in the UNION ALL keyword are consistent; If not, convert the column attributes of the data table to be consistent; Continue executing the query statement; The step of converting the column attributes of the data table to be consistent includes: obtaining the preset conversion rules of the database; determining whether the conversion rules can convert the column attributes of the data table; if so, converting the column attributes of the data table according to the conversion rules. The step of obtaining the preset conversion rules of the database includes: obtaining the system table corresponding to the conversion rules in the database; The step of determining whether the conversion rule can convert the column attributes of the data table includes: querying the system table for other types that the column attributes of the data table can be converted to, and generating a set of column attribute types; taking the intersection of the column attribute type sets of the data tables involved in the UNION ALL keyword; and determining whether type information exists in the intersection.

2. The method for processing database query statements according to claim 1, wherein, The step of determining whether the column attributes of the data tables involved in the UNION ALL keyword are consistent includes: Obtain the column attribute IDs of the data tables respectively; Determine whether the column attribute ID of the data table is equal.

3. The method for processing database query statements according to claim 1, wherein, The step of determining whether the column attributes of the data tables involved in the UNION ALL keyword are consistent includes: If so, the column attributes of the data table will not be processed, and the query statement will continue to be executed.

4. The method for processing database query statements according to claim 1, wherein, The set of types includes: other broader types that the column attributes of the data table can be converted to, and the types of the column attributes themselves.

5. The method for processing database query statements according to claim 1, wherein, The step of determining whether the conversion rule can convert the column attributes of the data table further includes: If not, no conversion will be performed, and the query operation will continue.

6. The method for processing database query statements according to claim 1, wherein, The step of converting the column attributes of the data table according to the conversion rules includes: Generate a new target column based on the transformed column attributes; Eliminate the subquery of the UNION ALL statement.

7. A machine-readable storage medium having a machine-executable program stored thereon, wherein the machine-executable program, when executed by a processor, implements the method for processing database query statements according to any one of claims 1 to 6.

8. A computer device comprising a memory, a processor, and a machine-executable program stored in the memory and running on the processor, wherein the processor, when executing the machine-executable program, implements a method for processing a database query statement according to any one of claims 1 to 6.

Images