Dynamically selecting a data access path to improve query performance

Patent metadata

Field	Value
Publication number	US-11144546-B2
Application number	US-202016789999-A
Country	US
Kind code	B2
Filing date	Feb 13, 2020
Priority date	Feb 13, 2020
Publication date	Oct 12, 2021
Grant date	Oct 12, 2021

Abstract

An approach is provided for accessing data in a database. A query which accesses item(s) in the data is received. The query has multiple conditions whose execution produces a final result. Amounts of filtering of portions of the data caused by utilizing different types of indexes to filter the portions are determined. Greatest amounts of filtering for the portions are determined. Types of indexes are selected so that a utilization of a given type of index causes a greatest amount of filtering for a given portion. Data access methods for the portions of data are generated. During runtime, process tasks are generated for the multiple conditions in the query. An execution flow is controlled. The execution flow executes the process tasks by utilizing the data access methods to generate initial results. The final result of the query is generated by combining the initial results from the process tasks.

First claim

What is claimed is:

1. A method of accessing data in a database, the method comprising:
receiving, by one or more processors, a query which accesses one or more items included in data in a database, the query having multiple conditions whose execution produces a final result of the query;
determining, by the one or more processors, amounts of filtering of portions of the data in the database caused by utilizing different types of indexes to filter the portions, respectively;
determining, by the one or more processors, respective greatest amounts of filtering for the portions, the greatest amounts of filtering being included in the amounts of filtering;
selecting, by the one or more processors, respective types of indexes so that a utilization of a given type of index causes a greatest amount of filtering for a given portion;
based on the selected types of indexes, generating, by the one or more processors, respective data access methods for the portions of the data;
during runtime, generating, by the one or more processors, respective process tasks for the multiple conditions in the query;
controlling, by the one or more processors, an execution flow that executes the process tasks by utilizing the respective data access methods to generate respective initial results; and
generating, by the one or more processors, the final result of the query by combining the initial results from the process tasks.

2. The method of claim 1 , further comprising receiving, by the one or more processors, statistics of the database, wherein the generating the respective data access methods is further based on the received statistics.

3. The method of claim 1 , further comprising:
generating, by the one or more processors, access paths for the portions so that the access paths correspond to the greatest amounts of filtering for the portions and to the types of indexes; and
dynamically accessing, by the one or more processors, one of the generated access paths to execute the query, which improves a performance of the query.

4. The method of claim 1 , wherein the determining the amounts of filtering, the determining the respective greatest amounts of filtering, the selecting the respective types of indexes, the generating the respective data access methods, the generating the respective process tasks, the controlling the execution flow, and the generating the final result by combining the initial results reduce a usage of system resources by an execution of the query.

5. The method of claim 1 , further comprising:
dividing, by the one or more processors, the data in the database into partitions so that the partitions are non-overlapping, wherein the portions are the partitions; and
dividing, by the one or more processors, an originating task associated with the query into multiple process tasks so that each process task is associated with a corresponding partition included in the partitions.

6. The method of claim 1 , further comprising:
determining, by the one or more processors, that the indexes have identical prefixes;
in response to the determining that the indexes have the identical prefixes, dividing, by the one or more processors, the data in the database into non-overlapping key ranges; and
dividing, by the one or more processors, an originating task associated with the query into multiple process tasks so that each process task is associated with a corresponding key range included in the non-overlapping key ranges.

7. The method of claim 1 , further comprising:
generating, by a runtime generator, relationships between an originating task associated with the query and the process tasks; and
based on the relationships, generating, by the runtime generator, a runtime structure, wherein the controlling the execution flow is based on the runtime structure.

8. The method of claim 1 , further comprising:
providing at least one support service for at least one of creating, integrating, hosting, maintaining, and deploying computer readable program code in the computer, the program code being executed by a processor of the computer to implement the receiving the query, the determining the amounts of filtering of the portions of the data in the database, the determining the respective greatest amounts of filtering for the portions, the selecting the respective types of indexes, the generating the respective data access methods for the portions of the data, the generating the respective process tasks for the multiple conditions in the query, the controlling the execution flow, and the generating the final result of the query by combining the initial results from the process tasks.

9. A computer program product comprising:
a computer readable storage medium having computer readable program code stored on the computer readable storage medium, the computer readable program code being executed by a central processing unit (CPU) of a computer system to cause the computer system to perform a method comprising the steps of:
the computer system receiving a query which accesses one or more items included in data in a database, the query having multiple conditions whose execution produces a final result of the query;
the computer system determining amounts of filtering of portions of the data in the database caused by utilizing different types of indexes to filter the portions, respectively;
the computer system determining respective greatest amounts of filtering for the portions, the greatest amounts of filtering being included in the amounts of filtering;
the computer system selecting respective types of indexes so that a utilization of a given type of index causes a greatest amount of filtering for a given portion;
based on the selected types of indexes, the computer system generating respective data access methods for the portions of the data;
during runtime, the computer system generating respective process tasks for the multiple conditions in the query;
the computer system controlling an execution flow that executes the process tasks by utilizing the respective data access methods to generate respective initial results; and
the computer system generating the final result of the query by combining the initial results from the process tasks.

10. The computer program product of claim 9 , wherein the method further comprises the computer system receiving statistics of the database, wherein the generating the respective data access methods is further based on the received statistics.

11. The computer program product of claim 9 , wherein the method further comprises:
the computer system generating access paths for the portions so that the access paths correspond to the greatest amounts of filtering for the portions and to the types of indexes; and
the computer system dynamically accessing one of the generated access paths to execute the query, which improves a performance of the query.

12. The computer program product of claim 9 , wherein the determining the amounts of filtering, the determining the respective greatest amounts of filtering, the selecting the respective types of indexes, the generating the respective data access methods, the generating the respective process tasks, the controlling the execution flow, and the generating the final result by combining the initial results reduce a usage of system resources by an execution of the query.

13. The computer program product of claim 9 , wherein the method further comprises:
the computer system dividing the data in the database into partitions so that the partitions are non-overlapping, wherein the portions are the partitions; and
the computer system dividing an originating task associated with the q

Assignees

• IBM

Inventors

• Li Shuo
• Wang Xiaobo
• Sun Shengyan
• Jiang peng hui

Classifications

• G06F16/2228 Primary

  Indexing structures · CPC title

• G06F16/2455

  Query execution · CPC title

• G06F16/2453 Primary

  Query optimisation · CPC title

• G06F16/278

  Data partitioning, e.g. horizontal or vertical partitioning · CPC title

• G06F16/248

  Presentation of query results · CPC title