Method and storage medium for supporting like operation in partition pruning in SQL

Abstract

The application provides a method and a storage medium for supporting a like operation in SQL partition pruning. The method comprises the following steps: when a like operator in SQL contains a partition key of a partition table, a first string contained in the like operator is acquired; it is judged whether the first string contains a wildcard; if yes, a partition type of the partition table is acquired; and the first string is processed in a manner corresponding to the partition type, so that the partition table supports the like operation. The optimizer can apply a filtering condition containing the like operator in partition pruning, the adaptability of the filtering condition containing the like operator is improved, and the query efficiency is further improved.

Description

Technical Field

[0001] This invention relates to database technology, and in particular to a method and storage medium for supporting LIKE operations in SQL partition pruning. Background Technology

[0002] Currently, the concept of partitioned tables has been introduced into databases. Specifically, a table can be partitioned, resulting in multiple partitioned tables. Logically, each partitioned table remains a complete table; however, the data is physically stored across multiple tablespaces (physical files). This approach avoids scanning the entire table every time data is retrieved, improving query efficiency. However, in current query schemes for partitioned tables, the optimizer cannot apply filtering conditions containing the `LIKE` operator to partition pruning. Filters containing the `LIKE` operator have low adaptability, preventing further improvements in query efficiency. Summary of the Invention

[0003] One objective of this invention is to process the first string in a manner corresponding to the partition type of the partition table, so as to apply the filtering conditions containing the like operator in partition pruning, thereby improving the adaptability of the filtering bars containing the like operator and further improving query efficiency.

[0004] Specifically, this invention provides a method for supporting LIKE operations in SQL partition pruning, including:

[0005] When the LIKE operator in the SQL statement contains the partition key of a partitioned table, retrieve the first string contained in the LIKE operator.

[0006] Determine whether the first string contains a wildcard character;

[0007] If so, obtain the partition type of the partition table;

[0008] The first string is processed in a manner corresponding to the partition type so that the partition table supports the like operation.

[0009] Optionally, when the partition type is a range partition, processing the first string in a manner corresponding to the partition type includes:

[0010] Get the second string from the beginning of the first string, before the first wildcard character;

[0011] Create a third string, wherein the third string is greater than the second string;

[0012] Create filtering conditions based on the second string and the third string.

[0013] Optionally, creating filtering conditions based on the second string and the third string includes:

[0014] The partition key is made to be greater than or equal to the second string and less than the third string.

[0015] Optionally, the creation of the third string includes:

[0016] The third string is obtained by adding 1 to the ASCII value of the last character of the second string.

[0017] Optionally, when the partition type is a list partition, processing the first string in a manner corresponding to the partition type includes:

[0018] Get the second string from the beginning of the first string, before the first wildcard character;

[0019] Determine whether the first string contains the "%" wildcard character;

[0020] If not, determine whether the partition key contains the second string and whether the length of the partition key is equal to the length of the first string;

[0021] If the partition key contains the second string, and the length of the partition key is equal to the length of the first string, the partition table is retained.

[0022] Optionally, if the first string contains the "%" wildcard, determine whether the partition key contains the second string;

[0023] If so, retain the partition table.

[0024] Optionally, if the first string does not contain wildcards, the like operator is converted to an operator supported by the partition table.

[0025] Optionally, converting the like operator to operators supported by the partition table includes:

[0026] Convert the like operator to the "=" operator.

[0027] According to another aspect of the present invention, a machine-readable storage medium is also provided, on which a machine-executable program is stored, which, when executed by a processor, implements the method of partition pruning supporting the like operation in SQL as described above.

[0028] 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, when executing the machine-executable program, implements the method of partition pruning supporting the like operation in SQL as described above.

[0029] In the method for supporting LIKE operations in SQL partition pruning of the present invention, when the LIKE operator in the SQL contains the partition key of the partition table, the first string contained in the LIKE operator is obtained, and it is determined whether the first string contains a wildcard. If so, the partition type of the partition table is obtained, and the first string is processed in a way corresponding to the partition type so that the partition table supports LIKE operations. This enables the optimizer to apply the filtering conditions containing the LIKE operator to partition pruning, improves the adaptability of the filtering bars containing the LIKE operator, and further improves the operation efficiency.

[0030] Furthermore, suppose the first string S1 is 1234%567%. The second string can be defined as S2. The second string S2 before the first wildcard in the first string S1 is 1234. This can be understood as starting from the beginning of the first string S1 and continuing until the second string before the first wildcard is obtained. The third string is defined as S3. The third string S3 is greater than the second string S2, for example, the third string S3 is 1235. By creating a third string where the third string is greater than the second string, and creating filtering conditions based on the second and third strings, the concept is very novel and ingenious, improving the adaptability of filter bars containing the LIKE operator.

[0031] 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

[0032] 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:

[0033] Figure 1 This is a schematic flowchart of a method for supporting LIKE operations in SQL partition pruning according to an embodiment of the present invention;

[0034] Figure 2 This is a schematic flowchart of a method for supporting LIKE operations in SQL partition pruning according to another embodiment of the present invention;

[0035] Figure 3This is a schematic flowchart of a method for supporting LIKE operations in SQL partition pruning according to another embodiment of the present invention;

[0036] Figure 4 This is a schematic diagram of a machine-readable storage medium according to an embodiment of the present invention;

[0037] Figure 5 This is a schematic diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

[0038] Figure 1 This is a schematic flowchart illustrating a method for supporting the LIKE operation in SQL partition pruning according to an embodiment of the present invention. The method for supporting the LIKE operation in SQL partition pruning generally includes:

[0039] Step S102: When the like operator in the SQL contains the partition key of the partitioned table, retrieve the first string contained in the like operator.

[0040] In this step, because the goal of this scheme is to enable partition pruning to support the LIKE operation (i.e., support LIKE filtering conditions), it is only necessary to execute when the partition key of the partition table is included. If the LIKE operator does not include the partition key of the partition table, this scheme can be skipped. The first string can be defined as S1. The partition key can be defined as P1.

[0041] Step S104: Determine whether the first string contains wildcard characters. Wildcard characters can generally include % and underscores, etc.

[0042] If so, proceed to step S106: Obtain the partition type of the partition table. The partition type of a partition table can generally include range partitioning, list partitioning, and hash partitioning, etc.

[0043] Step S108: Process the first string using a method corresponding to the partition type to enable the partition table to support the LIKE operation. In this step, different partition types can correspond to different processing methods. Process the first string using a method corresponding to the partition type to obtain new filtering conditions that can be applied to partition pruning to enable the partition table to support the LIKE operation.

[0044] In this embodiment, when the LIKE operator in Structured Query Language (SQL) contains the partition key of a partitioned table, the first string contained in the LIKE operator is retrieved. It is then determined whether the first string contains a wildcard. If so, the partition type of the partitioned table is obtained, and the first string is processed in a manner corresponding to the partition type to obtain new filtering conditions. This enables the partitioned table to support the LIKE operation, allowing the optimizer to apply filtering conditions containing the LIKE operator to partition pruning. This improves the adaptability of filtering bars containing the LIKE operator and further enhances the efficiency of operations (such as query operations). For example, for a data table with 5 partitions, the execution time before applying this solution was 54ms, while the execution time after applying this solution is 0.02ms, representing a significant improvement.

[0045] In one embodiment of the present invention, when the partition type is a range partition, processing the first string in a manner corresponding to the partition type may include:

[0046] Get the second string from the beginning of the first string, before the first wildcard character;

[0047] Create a third string that is greater than the second string;

[0048] Create filter conditions based on the second and third strings.

[0049] In this embodiment, assume the first string S1 is 1234%567%. The second string can be defined as S2. The second string S2, from the beginning of the first string S1 to before the first wildcard, is 1234. This can be understood as starting from the beginning of the first string S1 and continuing until the second string before the first wildcard is obtained. The third string is defined as S3. The third string S3 is greater than the second string S2; for example, the third string S3 is 1235. By creating a third string where the third string is greater than the second string, and creating filtering conditions based on the second and third strings, the concept is novel and ingenious, improving the adaptability of filter bars containing the LIKE operator. Furthermore, assuming the first string S1 is %1234%567%, the length of the second string S2 is 0, which cannot be used for partitioning and pruning.

[0050] In one embodiment of the present invention, creating filtering conditions based on the second string and the third string may include:

[0051] Make the partition key greater than or equal to the second string and less than the third string.

[0052] In this embodiment, the partition key is greater than or equal to the second string and less than the third string, that is, "P1 >= S2 and P1 < S3".

[0053] In this embodiment, the filtering condition created according to the second string and the third string is "P1 >= S2 and P1 < S3", which can better conform to the original filtering condition in the like operation and has high rationality.

[0054] In an embodiment of the present invention, creating the third string may include:

[0055] The ASCII value of the last digit of the second string can be incremented by 1 to obtain the third string.

[0056] In this embodiment, assuming that the second string S2 is 1234, the ASCII value of the last digit of the second string is incremented by 1 to obtain the third string 1235. The third string S3 is obtained by incrementing the ASCII value of the last digit of the second string by 1. The third string obtained in this regular way can avoid being too large compared to the second string, which is beneficial to ensuring the rationality of the obtained filtering condition (such as "P1 >= S2 and P1 < S3"). Of course, the ASCII value of the last digit of the second string can also be incremented by 2 or 3 or 4, etc., that is, by a preset threshold.

[0057] In an embodiment of the present invention, when the partition type is list partitioning, the processing of the first string in a manner corresponding to the partition type may include:

[0058] Obtain the second string before the first wildcard character in the first string from the start of the first string;

[0059] Determine whether the first string contains the "%" wildcard character;

[0060] If not, determine whether the partition key contains the second string and whether the length of the partition key is equal to the length of the first string;

[0061] If the partition key contains the second string and the length of the partition key is equal to the length of the first string, retain the partition table.

[0062] In this embodiment, when the first string does not contain the "%" wildcard character, if the partition key contains the second string and the length of the partition key is equal to the length of the first string, it means that the filtering condition can be applied to partition pruning, and the partition table is retained, improving the adaptability of the filtering condition including the like operator, otherwise it is filtered.

[0063] In an embodiment of the present invention, if the first string contains the "%" wildcard character, determine whether the partition key contains the second string;

[0064] If so, retain the partition table.

[0065] In this embodiment, if the first string contains the wildcard "%", it is determined whether the partition key contains the second string; if so, it means that the filtering condition can be applied to partition pruning, preserving the partition table and improving the adaptability of filter bars containing the LIKE operator; otherwise, it is filtered. Additionally, when the partition type is a hash partition, if the first string contains a wildcard, this filtering condition cannot be used for partition pruning.

[0066] In one embodiment of the present invention, if the first string does not contain a wildcard, the like operator is converted to an operator supported by the partition table.

[0067] In this embodiment, if the first string does not contain wildcards, the like operator is converted to an operator supported by the partition table. This method can be applied to various partition types, such as range partitions, list partitions, and hash partitions, and has high adaptability, thus improving the adaptability of filter bars containing like operators.

[0068] In one embodiment of the present invention, converting the like operator to an operator supported by a partitioned table includes:

[0069] The like operator can be converted to the "=" operator.

[0070] The first string S1 is processed in a manner corresponding to the partition type so that the partition table supports the LIKE operation.

[0071] Figure 2 This is a schematic flowchart of a method for supporting LIKE operations in SQL partition pruning according to another embodiment of the present invention, which may include:

[0072] Step S202: When the like operator in the SQL contains the partition key P1 of the partitioned table, retrieve the first string S1 contained in the like operator.

[0073] Step S204: Determine whether the first string S1 contains a wildcard.

[0074] If yes, proceed to step S206; otherwise, proceed to step S212.

[0075] Step S206: When the partition type is range partition, obtain the second string S2 before the first wildcard in the first string S1 from the beginning of the first string S1.

[0076] Step S208: If the second string S2 is greater than 0, create a third string S3, where the third string S3 is greater than the second string S2.

[0077] Step S210: Make the partition key P1 greater than or equal to the second string S2 and less than the third string S3 as a filtering condition so that the partition table supports the like operation.

[0078] Step S212: Convert the like operator to the "=" operator as a filter condition so that the partitioned table supports the like operation.

[0079] Figure 3 This is a schematic flowchart of a method for supporting LIKE operations in SQL partition pruning according to another embodiment of the present invention, which may include:

[0080] Step 302: When the like operator in the SQL contains the partition key P1 of the partitioned table, retrieve the first string S1 contained in the like operator.

[0081] Step S304: Determine whether the first string S1 contains a wildcard.

[0082] If yes, proceed to step S306; otherwise, proceed to step S314.

[0083] Step S306: When the partition type is list partition, obtain the second string S2 before the first wildcard in the first string S1 from the beginning of the first string S1.

[0084] Step S308: Determine whether the first string S1 contains the wildcard "%".

[0085] If not, proceed to step S310; if yes, proceed to step S312.

[0086] Step S310: If partition key P1 contains the second string S2, and the length of partition key P1 is equal to the length of the first string S1, then use it as a filtering condition to enable the partition table to support the LIKE operation.

[0087] Step S312: If partition key P1 contains the second string S2, then use it as a filter condition to enable the partition table to support the LIKE operation.

[0088] Step S314: Convert the like operator to the "=" operator.

[0089] The above embodiments can be combined arbitrarily. Based on any one preferred embodiment or a combination of multiple preferred embodiments, the embodiments of the present invention can achieve the following beneficial effects:

[0090] In the method for supporting LIKE operations in SQL partition pruning of the present invention, when the LIKE operator in Structured Query Language (SQL) contains the partition key of the partitioned table, the first string contained in the LIKE operator is obtained, and it is determined whether the first string contains a wildcard. If so, the partition type of the partitioned table is obtained, and the first string is processed in a manner corresponding to the partition type so that the partitioned table supports LIKE operations. This enables the optimizer to apply filter conditions containing LIKE operators to partition pruning, improves the adaptability of filter bars containing LIKE operators, and further improves query efficiency.

[0091] This embodiment also provides a machine-readable storage medium and a computer device. Figure 4 This is a schematic diagram of a machine-readable storage medium 830 according to an embodiment of the present invention; Figure 5 This is a schematic diagram of a computer device 900 according to an embodiment of the present invention. A machine-readable storage medium 830 stores a machine-executable program 840 thereon, which, when executed by a processor, implements a method for calculating the number of selected rows in a table in the structured query language of the database of any of the above embodiments.

[0092] Computer device 900 may include memory 920, processor 910, and machine-executable program 840 stored on memory 920 and running on processor 910. When processor 910 executes machine-executable program 840, it implements the method for calculating the number of selected rows of a table in the structured query language of the database in any of the above embodiments.

[0093] It should be noted that the logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can 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).

[0094] For the purposes of this embodiment, the machine-readable storage medium 830 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 computer-readable media 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 computer-readable medium 40 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.

[0095] 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.

[0096] Computer device 900 can be, for example, a server, desktop computer, laptop computer, tablet computer, or smartphone. In some examples, computer device 900 can be a cloud computing node. Computer device 900 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 900 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.

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

[0098] The processor 910 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 900 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 900 or may be external devices connected to the computing device.

[0099] The processor 910 can also be linked via a system interconnect to a display interface suitable for connecting the computer device 900 to a display device. The display device may include a display screen as a built-in component of the computer device 900. The display device may also include an external computer monitor, television, or projector connected to the computer device 900. Furthermore, a network interface controller (NIC) may be adapted to connect the computer device 900 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 can connect to the computing device via the network.

[0100] 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.

[0101] 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 supporting LIKE operations in SQL partition pruning, comprising: When the LIKE operator in the SQL statement contains the partition key of a partitioned table, retrieve the first string contained in the LIKE operator. Determine whether the first string contains a wildcard character; If so, obtain the partition type of the partition table; The first string is processed in a manner corresponding to the partition type so that the partition table supports the like operation; When the partition type is a range partition, processing the first string in a manner corresponding to the partition type includes: Get the second string from the beginning of the first string, before the first wildcard character; Create a third string, wherein the third string is greater than the second string; Create filtering conditions based on the second string and the third string.

2. The method for supporting LIKE operations in SQL partition pruning according to claim 1, wherein, The step of creating filtering conditions based on the second string and the third string includes: The partition key is made to be greater than or equal to the second string and less than the third string.

3. The method for supporting LIKE operations in SQL partition pruning according to claim 1, wherein, The creation of the third string includes: The third string is obtained by adding 1 to the ASCII value of the last character of the second string.

4. The method for supporting LIKE operations in SQL partition pruning according to claim 1, wherein, When the partition type is a list partition, processing the first string in a manner corresponding to the partition type includes: Get the second string from the beginning of the first string, before the first wildcard character; Determine whether the first string contains the "%" wildcard character; If not, determine whether the partition key contains the second string and whether the length of the partition key is equal to the length of the first string; If the partition key contains the second string, and the length of the partition key is equal to the length of the first string, the partition table is retained.

5. The method for supporting LIKE operations in SQL partition pruning according to claim 4, wherein, If the first string contains the "%" wildcard, determine whether the partition key contains the second string; If so, retain the partition table.

6. The method for supporting LIKE operations in SQL partition pruning according to claim 1, wherein, If the first string does not contain wildcards, the like operator is converted to an operator supported by the partition table.

7. The method for supporting LIKE operations in SQL partition pruning according to claim 6, wherein, The step of converting the like operator to an operator supported by the partition table includes: Convert the like operator to the "=" operator.

8. A machine-readable storage medium having a machine-executable program stored thereon, the machine-executable program, when executed by a processor, implementing the method for supporting LIKE operations in partition pruning in SQL according to any one of claims 1 to 7.

9. A computer device comprising a memory, a processor, and a machine-executable program stored on the memory and running on the processor, wherein the processor, when executing the machine-executable program, implements the method for supporting LIKE operations in partition pruning in SQL according to any one of claims 1 to 7.

Images