CN116257551A

CN116257551A - Processing method, storage medium and device for database query statement

Info

Publication number: CN116257551A
Application number: CN202310065655.6A
Authority: CN
Inventors: 范国腾; 张敬涛
Original assignee: Beijing Kingbase Information Technologies Co Ltd
Current assignee: Beijing Kingbase Information Technologies Co Ltd
Priority date: 2023-01-13
Filing date: 2023-01-13
Publication date: 2023-06-13

Abstract

The invention provides a processing method of database query sentences, a storage medium and computer equipment. Wherein the method comprises the following steps: acquiring a query sentence containing UNION ALL keywords; judging whether column properties of a data table related to the UNION ALL keywords are consistent or not; if not, converting the column properties of the data table to be consistent; the query statement continues to be executed. According to the processing method of the database query statement, column attributes in two query statements related to the UNION ALL keywords in the database query statement are converted into the same data type, so that sub-queries of the UNION ALL are eliminated, the time for querying the database is shortened, the response speed of the database is improved, and the performance of the database can be greatly improved.

Description

Processing method, storage medium and device for database query statement

Technical Field

The present invention relates to database technologies, and in particular, to a method, a storage medium, and an apparatus for processing a database query statement.

Background

In some database usage scenarios, it is desirable to merge information in multiple data tables, and a general method of choice is to merge two result sets using a UNION ALL key in a query statement. But the use of the UNION ALL key has several constraints: the SELECT statement inside the UNION ALL must have the same number of columns and the columns must also have similar data types. When the corresponding column attributes in the SELECT statement to which the UNION ALL relates are only similar data types, the internal execution of the UNION ALL can consume a lot of time, resulting in performance degradation and slow database response.

Disclosure of Invention

It is an object of the invention to improve the performance of a database.

It is a further object of the invention to provide a response speed of the database.

It is a further object of the present invention that the database be able to autonomously convert the column attributes to which the UNION ALL relates to the same type.

In particular, the invention provides a processing method of database query sentences, which comprises the following steps:

acquiring a query sentence containing UNION ALL keywords;

judging whether column properties of a data table related to the UNION ALL keywords are consistent or not;

if not, converting the column properties of the data table to be consistent;

the query statement continues to be executed.

Optionally, the step of determining whether column properties of the data table related to the UNION ALL keyword are consistent includes:

respectively acquiring column attribute IDs of a data table;

it is determined whether column attribute IDs of the data table are equal.

Optionally, the step of determining whether column properties of the data table related to the UNION ALL keywords are consistent includes:

if yes, the column attributes of the data table are not processed, and the query statement is continuously executed.

Optionally, the step of reconciling column properties of the data table comprises:

acquiring a preset conversion rule of a database;

judging whether the conversion rule can convert the column attribute of the data table;

if yes, converting the column attribute of the data table according to the conversion rule.

Optionally, the step of obtaining a conversion rule preset in the database includes: acquiring a system table corresponding to the conversion rule from a database;

the step of determining whether the conversion rule can convert the column attribute of the data table includes: querying other types which can be converted by the column attribute of the data table in the system table, and generating a type set of the column attribute; intersection is taken for a column attribute type set of a data table related to the UNION ALL keyword; and judging whether the type information exists in the intersection.

Optionally, the set of types includes: other types of the data table that are more extensive in which column attributes can be converted and the types of the column attributes themselves.

Optionally, the step of determining whether the conversion rule can convert the column attribute of the data table further includes:

if not, the conversion processing is not performed, and the query operation is continuously executed.

Optionally, the step of converting the column properties of the data table according to the conversion rule comprises:

generating a new target column according to the converted column attribute;

sub-queries of UNION ALL are eliminated.

According to another aspect of the present invention, there is also provided a machine-readable storage medium having stored thereon a machine-executable program which when executed by a processor implements a method of processing a database query statement of any of the above.

According to still another aspect of the present invention, there is also provided a computer device including a memory, a processor, and a machine executable program stored on the memory and running on the processor, and the processor implementing a method of processing a database query statement of any of the above when executing the machine executable program.

The processing method of the database query statement comprises the steps of firstly obtaining the database query statement with the UNION ALL keywords, and then judging whether the column properties of a data table related to each UNION ALL keyword are consistent; if not, converting the column properties of the data table to be consistent; then generating new target columns according to the types, eliminating the sub-queries of the original UNION ALL, and continuing to execute the query statement. By the method, when the database executes the database query statement with the UNION ALL keywords, the column attribute of the data table related to each UNION ALL keyword is converted into the same type, so that sub-query of the UNION ALL can be eliminated, the response speed of the database query is improved, and the performance of the database is improved.

The processing method of the database query statement of the invention has the following conversion rules of column attributes of the data table: when the database judges that the column attributes of the data tables related to the UNION ALL keywords are inconsistent, other data types with larger ranges which can be converted by the column attributes are queried in a system table preset in the database, a corresponding set is generated, then an intersection of the column attribute sets of the data tables related to each UNION ALL keyword is taken, if data type information exists in the intersection, the data type information is selected for conversion, and the column attributes related to the UNION ALL are converted into the same type by the method. Through the conversion rule, the column attribute related to the UNION ALL can be autonomously converted into the same type in the operation process of the database, the system table has default content which is preset, and a user can also autonomously define or modify the system table, so that the convenience of using the database is improved.

The above, as well as additional objectives, advantages, and features of the present invention will become apparent to those skilled in the art from the following detailed description of a specific embodiment of the present invention when read in conjunction with the accompanying drawings.

Drawings

Some specific embodiments of the invention will be described in detail hereinafter by way of example and not by way of limitation with reference to the accompanying drawings. The same reference numbers will be used throughout the drawings to refer to the same or like parts or portions. It will be appreciated by those skilled in the art that the drawings are not necessarily drawn to scale. In the accompanying drawings:

FIG. 1 is a flow diagram of a method of processing a database query statement according to one embodiment of the invention;

FIG. 2 is a flow diagram of a method of processing a database query statement according to another embodiment of the invention;

FIG. 3 is a schematic diagram of a machine-readable storage medium in a method of processing a database query statement according to one embodiment of the invention; and

FIG. 4 is a schematic diagram of a computer device in a method of processing a database query statement according to one embodiment of the invention.

Detailed Description

It should be understood by those skilled in the art that the embodiments described below are only some embodiments of the present invention, but not all embodiments of the present invention, and the some embodiments are intended to explain the technical principles of the present invention and are not intended to limit the scope of the present invention. All other embodiments, which can be obtained by a person skilled in the art without any inventive effort, based on the embodiments provided by the present invention, shall still fall within the scope of protection of the present invention.

In some database usage scenarios, where it is desirable to merge information in multiple data tables, a user may choose to merge two result sets using a UNION ALL key in a query statement. But the use of the UNION ALL key has several constraints: the SELECT statement inside the UNION ALL must have the same number of columns and the columns must also have similar data types. When the corresponding column attributes in the SELECT statement to which the UNION ALL relates are only similar but not identical data types, the internal execution of the UNION ALL can consume significant time, resulting in reduced performance and slow database response.

According to the method, column properties of the database related to the UNION ALL are changed into consistent data types on the premise of not losing data precision by uniformly converting column properties of the data table related to the UNION ALL keywords in the query statement, so that sub-queries of the UNION ALL are eliminated. Therefore, the response speed of the database to the query operation is improved, and the performance of the database is improved.

FIG. 1 is a flow diagram of a method of processing a database query statement according to one embodiment of the invention. The method generally comprises the following steps:

step S101, a query sentence containing UNION ALL keywords is acquired. The method comprises the following steps: and acquiring the query statement of the input database, judging whether a UNION ALL keyword exists in the query statement, and if so, continuing to execute the subsequent steps.

Step S102, judging whether the column properties of the data table related to the UNION ALL keywords are consistent. The method comprises the following steps: respectively acquiring column attribute IDs of a data table; then judging whether column attribute IDs of the data table are equal; if the list attributes of the data tables related to the UNION ALL keywords are identical, the step of converting the list attributes can be directly skipped, and the query statement can be continuously executed normally.

Wherein the column attribute ID refers to: inside the database system, each data type has an ID corresponding to it.

Step S103, if step S102 determines no, the column attributes of the data table are converted to match. The method comprises the following steps: acquiring a preset conversion rule of a database; judging whether a conversion rule can convert column attributes of the data table; if yes, converting column properties of the data table related to the UNION ALL keywords according to the conversion rule. Wherein the conversion rule preset in the database is a system table preset in the database. Other data types which can be converted by different data types are recorded in the system table, and a user can update and modify the system table according to actual requirements. One specific example of this system table is shown in table 1:

TABLE 1

/>

/>

The step of determining whether the conversion rule can convert the column attribute of the data table comprises: querying a system table for other data types which can be converted by the column attribute of the data table, and generating a data type set of the column attribute; the set of types includes other types of greater scope that the column attributes of the data table can convert to and the types of the column attributes themselves. Next, intersection is taken for the data type set of the column attribute of the data table related to the UNION ALL keyword; and judging whether the type information exists in the intersection. If so, the data types in the column attributes of the data table related to the UNION ALL keyword can be converted into the same data types, and then the conversion is performed.

Specific examples are: when the column attributes of the data tables involved in the two query sentences involved in the UNION ALL keyword are INT2 (2 bytes) and INT (4 bytes); at this time, the column properties of the two data tables are only similar, but not identical, and therefore require conversion. INT2, after applying the method, has INT4, INT8, TEXT, FLOAT4, FLOAT8, NUMERIC and self INT2 in the collection generated in the system Table (Table 1); whereas INT4 has BOOL, INT8, TEXT, FLOAT4, FLOAT8, NUMERIC and self INT4 in the collection generated in the systems Table (Table 1); after the intersection is taken, the intersection of the two sets is INT4, INT8, TEXT, FLOAT4, FLOAT8 and NUMERIC, so that the database can select the data type to be converted according to the actual situation to convert the column attributes of the two data tables.

It should be noted that, table 1 is only a specific example for better explaining the method, and the contents of the table may be set by a person skilled in the art according to the actual situation, and the user may also modify the table when using the database.

Step S104, the query sentence is continuously executed. A new target column is generated for the data table after converting the column attributes in step S103, then the sub-query of the UNION ALL is eliminated, and then the query statement is continued to be executed.

According to the processing method of the database query statement, column attributes in two query statements related to the UNION ALL keywords in the database query statement are converted into the same data type, so that sub-queries of the UNION ALL are eliminated, the time of database query is shortened, the response speed of the database is improved, and the efficiency and performance of the database execution query are improved.

Fig. 2 is a flow chart of a method for processing a database query statement according to another embodiment of the invention. The method generally comprises the following steps:

step S201, a database query statement is acquired.

Step S202, judging whether there is sub-query of UNION ALL. I.e., determining whether a UNION ALL key exists in the database query statement.

In step S203, if the determination in step S202 is yes, it is determined whether or not the column attributes corresponding to the query term related to the UNION ALL keyword match. Inside the database system, each data type has an ID corresponding to it. The step is to judge whether the data type ID of the column attribute corresponding to the query statement related to the UNION ALL keyword is consistent in the database.

Step S204, in the case where the determination in step S203 is no, the system table is queried to check whether the different types can be converted. The method comprises the following steps: acquiring a preset conversion rule of a database; judging whether a conversion rule can convert column attributes of the data table; wherein the conversion rule preset in the database is a system table preset in the database. Other data types which can be converted by different data types are recorded in the system table, and a user can update and modify the system table according to actual requirements.

The step of determining whether the conversion rule can convert the column attribute of the data table comprises: querying a system table for other data types which can be converted by the column attribute of the data table, and generating a data type set of the column attribute; the set of types includes other types of greater scope that the column attributes of the data table can convert to and the types of the column attributes themselves. Next, intersection is taken for the data type set of the column attribute of the data table related to the UNION ALL keyword; and judging whether the type information exists in the intersection. If so, the data types in the column attributes of the data table related to the UNION ALL keyword can be converted into the same data types.

In step S205, if yes in step S204, a new target column is generated for the SELECT sub-query. When the determination result in step S204 is yes, the data type representing the column attribute of the data table related to the UNION ALL keyword may be converted into the same data type, and therefore, the column attribute of the data table related to the SELECT sub-query, i.e., the UNION ALL keyword, is converted and a new target column is generated.

In step S206, the sub-query of UNION ALL is eliminated. I.e., after generating a new target column for the SELECT subquery, the subquery of the original UNION ALL is deleted.

Step S207, when any one of the following occurs: and step S202, step S203, step S204 and step S206 are performed, and the query operation is performed. If the step S202 determines that no unicon ALL keywords exist in the query statement, the query operation is directly performed without executing subsequent determination; if the step S203 determines that the query statement contains a UNION ALL keyword, but the column attributes in the query statement related to the UNION ALL keyword are the same, no conversion is required, and a query operation is executed; if step S204 determines that no, it indicates that there is a UNION ALL keyword in the query sentence, and the column attributes of the query sentence related to the UNION ALL keyword are different, but the column attributes of the query sentence do not have a data type capable of being converted in the system table of the database, the conversion step is skipped, and the query operation is performed.

According to the processing method of the database query statement, column attributes in two query statements related to the UNION ALL keywords in the database query statement are converted into the same data type, so that sub-queries of the UNION ALL are eliminated, the time for querying the database is shortened, the response speed of the database is improved, and the performance of the database can be greatly improved.

A specific example of the method is as follows:

first, create tables t1, t2, t3, where the name1 column attributes of t1, t2 are not identical:

create table t1(id1 integer,seq1 integer,name1 integer)；

create table t2(id2 integer,seq2 integer,name2 bigint)；

create table t3(id3 integer,seq3 integer,name3 text)；

creating an index:

create index ind_t1_id1 on t1(id1)；

create index ind_t2_id1 on t2(id2)；

create index ind_t3_id1 on t3(seq3)；

100 ten thousand pieces of data are inserted:

insert into t1 select generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000)；

insert into t2 select generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000)；

insert into t3 select generate_series(1,1000000),generate_series(1,1000000),repeat('c',500)；

executing the statements t1 and t2 UNION ALL, and then connecting with t 3:

explain analyze with t as(

select id1 as id,seq1 as seq,name1 as name from t1

union all

select id2 as id,seq2 as seq,name2 as name from t2

)

select count(*)from t,t3 where t.id＝t3.id3 and t3.seq3＝12345；

when the scheme is not used in the database system after executing the statement, the sub query Scan (sub query) of the t1 table in the UNION ALL is displayed in the execution result and is not eliminated, and the time after executing the query statement is 641.257ms;

when the database system uses this scheme and the sub-query Scan of the t1 table is eliminated, the t1 and t2 tables are scanned with indexes, and the execution time is increased from 641.257ms to 0.083ms.

The response speed and performance of the database are greatly improved.

It should be noted that this example is merely a test performed to better explain the method, and does not represent the whole content of the method.

The present embodiment also provides a machine-readable storage medium and a computer device. FIG. 3 is a schematic diagram of a machine-readable storage medium in a method of processing a database query statement according to one embodiment of the invention; FIG. 4 is a schematic diagram of a computer device in a method of processing a database query statement according to one embodiment of the invention.

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

The computer device 403 may include a memory 401, a processor 402, and a machine executable program 302 stored on the memory 401 and running on the processor 402, and the processor 402 implements the method of processing database query statements of any of the embodiments described above when executing the machine executable program 302.

It should be noted that the logic and/or steps represented in the flow diagrams or otherwise described herein, e.g., converting data types, may be embodied in any machine-readable storage medium for use by or in connection with an instruction execution system, apparatus, or device, such as a computer-based system, processor-containing system, or other system that can fetch the instructions from the instruction execution system, apparatus, or device and execute the instructions.

For the purposes of this description of embodiments, a machine-readable storage medium 301 can be any means that can contain, store, communicate, propagate, or transport the program for use by or in connection with the instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of the machine-readable storage medium 301 include the following: an electrical connection (electronic device) having one or more wires, a portable computer diskette (magnetic device), a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber device, and a portable compact disc read-only memory (CDROM). Additionally, the machine-readable storage medium 301 may even be paper or other suitable medium upon which the program is printed, as the program may be electronically captured, via, for instance, optical scanning of the paper or other medium, then compiled, interpreted, or otherwise processed in a suitable manner if necessary, and then stored in a computer memory.

It is to be understood that portions of the present invention may be implemented in hardware, software, firmware, or a combination thereof. In the above-described embodiments, the various steps or methods may be implemented in software or firmware stored in a memory and executed by a suitable instruction execution system.

The computer device 403 may be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smartphone. In some examples, computer device 403 may be a cloud computing node. Computer device 403 may be described in the general context of computer system-executable instructions, such as program modules, being executed by a computer system. Generally, program modules may include routines, programs, objects, components, logic, data structures, etc. that perform particular tasks or implement particular abstract data types. Computer device 403 may be implemented in a distributed cloud computing environment in which remote processing devices that are linked through a communications network perform tasks. In a distributed cloud computing environment, program modules may be located in both local and remote computing system storage media including memory storage devices.

Computer device 403 may include a processor 402 adapted to execute stored instructions, a memory 401 providing temporary storage space for the operation of the instructions during operation. Processor 402 may be a single-core processor, a multi-core processor, a computing cluster, or any number of other configurations. Memory 401 may include Random Access Memory (RAM), read only memory, flash memory, or any other suitable storage system.

The processor 402 may be connected via a system interconnect (e.g., PCI-Express, etc.) to an I/O interface (input/output interface) adapted to connect the computer device 403 to one or more I/O devices (input/output devices). The I/O devices may include, for example, a keyboard and a pointing device, which may include a touch pad or touch screen, among others. The I/O device may be a built-in component of the computer device 403 or may be a device externally connected to the computing device.

The processor 402 may also be linked through a system interconnect to a display interface adapted to connect 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, projector, or the like, which is externally connected to the computer device 403. Further, a network interface controller (network interface controller, NIC) may be adapted to connect the computer device 403 to a network through a system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as an internet small computer system interface, etc.) to transfer data. The network may be a cellular network, a radio network, a Wide Area Network (WAN), a Local Area Network (LAN), or the internet, among others. The remote device may be connected to the computing device through a network.

The flowcharts provided by this embodiment are not intended to indicate that the operations of the method are to be performed in any particular order, or that all of the operations of the method are included in all of each case. Furthermore, the method may include additional operations. Additional variations may be made to the above-described methods within the scope of the technical ideas provided by the methods of the present embodiments.

By now it should be appreciated by those skilled in the art that while a number of exemplary embodiments of the invention have been shown and described herein in detail, many other variations or modifications of the invention consistent with the principles of the invention may be directly ascertained or inferred from the present disclosure without departing from the spirit and scope of the invention. Accordingly, the scope of the present invention should be understood and deemed to cover all such other variations or modifications.

Claims

1. A method of processing database query statements, comprising:

acquiring a query sentence containing UNION ALL keywords;

judging whether the column properties of the data table related to the UNION ALL keywords are consistent;

if not, converting the column properties of the data table to be consistent;

continuing to execute the query statement.

2. The method for processing a database query statement as claimed in claim 1, wherein,

the step of judging whether the column properties of the data table related to the UNION ALL keyword are consistent comprises the following steps:

respectively acquiring column attribute IDs of the data tables;

and judging whether column attribute IDs of the data tables are equal or not.

3. The method for processing a database query statement as claimed in claim 1, wherein,

4. The method for processing a database query statement as claimed in claim 1, wherein,

the step of reconciling column properties of the data table comprises:

acquiring a preset conversion rule of the database;

5. The method for processing a database query statement as claimed in claim 4, wherein,

the step of obtaining the preset conversion rule of the database comprises the following steps: acquiring a system table corresponding to the conversion rule from a database;

the step of judging whether the conversion rule can convert the column attribute of the data table includes: querying the system table for other types which can be converted by the column attribute of the data table, and generating a type set of the column attribute; an intersection is taken from a column attribute type set of a data table related to the UNION ALL keyword; and judging whether the type information exists in the intersection.

6. The method for processing a database query statement as claimed in claim 5, wherein,

the set of types includes: other types of larger scope that the column attributes of the data table can translate and the types of the column attributes themselves.

7. The method for processing a database query statement as claimed in claim 4, wherein,

the step of determining whether the conversion rule can convert the column attribute of the data table further includes:

8. The method for processing a database query statement as claimed in claim 4, wherein,

the step of converting the column attributes of the data table according to the conversion rule includes:

generating a new target column according to the converted column attribute;

and eliminating the sub-query of the UNION ALL.

9. A machine-readable storage medium having stored thereon a machine-executable program which when executed by a processor implements a method of processing a database query statement as claimed in any one of claims 1 to 8.

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