Patents by Inventor Terence P. Purcell
Terence P. Purcell has filed for patents to protect the following inventions. This listing includes patent applications that are pending as well as patents that have already been granted by the United States Patent and Trademark Office (USPTO).
-
Patent number: 11907220Abstract: A method, system and computer program product for processing queries in a hybrid database system. A database management system of the hybrid database system obtains an estimated execution time of a query by an online transaction processing engine. The database management system further obtains an estimated execution time of the query by a database accelerator. Furthermore, the database management system determines a wait time (which could be zero) for the database accelerator to process the query. The database management system then determines whether the online transaction processing engine or the database accelerator will process the query using the estimated execution time of the query by the online transaction processing engine, the estimated execution time of the query by the database accelerator and the wait time (which could be zero) for the database accelerator to process the query. In this manner, the processing of queries in hybrid database systems is optimized.Type: GrantFiled: December 18, 2020Date of Patent: February 20, 2024Assignee: International Business Machines CorporationInventors: Al Chakra, Ruiping Li, Terence P. Purcell, Lily Zhou
-
Publication number: 20230401207Abstract: According to an aspect, a computer-implemented method for improving query performance in a databased management system (DBMS) includes receiving, by a query optimizer of the DMBS, a query for execution. The method also includes creating, by the query optimizer, an initial access path for the query based on state and executing the query based on the initial access path. The method further includes observing, by a query agent of the DBMS, the execution of the query and modifying at least one of the state based on a determination by the query agent that a change to the initial access path would improve the execution of the query.Type: ApplicationFiled: September 13, 2022Publication date: December 14, 2023Inventors: Thomas A. Beavin, Shuanglin Guo, Brandon Jabr, Terence P. Purcell
-
Patent number: 11556534Abstract: Provided are techniques for efficient subquery predicate generation to reduce processing in a multi-table join. It is determined that a query is to be rewritten, wherein the query includes a predicate for a first column of a first table and a second column of a second table. The query is rewritten with generated subquery predicates for the first table that are to be applied to the second table and with generated subquery predicates for the second table that are to be applied to the first table. Minimum and maximum ranges for columns of the generated subquery predicates at a cursor open are determined. The minimum and maximum ranges are merged to determine inclusive ranges that exist in the first table and the second table. The generated subquery predicates are rewritten as derived local subquery predicates with the inclusive ranges. The query with the derived local subquery predicates is executed.Type: GrantFiled: April 30, 2021Date of Patent: January 17, 2023Assignee: INTERNATIONAL BUSINESS MACHINES CORPORATIONInventors: Thomas A. Beavin, Andrei F. Lurie, Terence P. Purcell
-
Patent number: 11386086Abstract: A DBMS query-optimization module receives a corpus of training data that contains data-access requests, such as SQL queries. Each request specifies data to be accessed but does not specify a query plan that the database should use to generate the requested data. The module identifies, in each received request, parameters, such as join methods and access methods, that can vary among query plans; and variables that cannot be assigned values until the query is actually processed. The system generates a set of queries, each of which implements a distinct query plan, that specify every viable permutation of values of the parameters and of the variables. The generated queries are added to the received corpus, which is forwarded to a machine-learning module in order to train the query-optimizer to select query plans that incur the lowest resource costs when servicing a particular type of query.Type: GrantFiled: August 30, 2018Date of Patent: July 12, 2022Assignee: International Business Machines CorporationInventors: Terence P. Purcell, Thomas A. Beavin, Martin Dinh, Brian L. Baggett
-
Patent number: 11222015Abstract: A method, system and computer program product are provided. A first index scan of records of a database table is performed. A first index used by the first index scan is specified in a DBMS query statement as being ordered by one or more field values of one or more fields of the first index. A second scan of the database table is performed to determine whether each respective record of the second scan is qualified based on one or more fields of the each respective record of the second scan and a predicate. A lookup data structure is built indicating whether each of the respective records of the second scan is qualified. The lookup data structure is used to determine whether a record of the first index scan is qualified when the lookup data structure becomes available.Type: GrantFiled: May 6, 2019Date of Patent: January 11, 2022Assignee: International Business Machines CorporationInventors: Brian L. Baggett, Thomas A. Beavin, Oliver Draese, Shuanglin Guo, Andrei F. Lurie, Terence P. Purcell, Shengxi Suo
-
Patent number: 11182386Abstract: Methods and systems for generating database statistics. Table statistics in a metadata catalog of a source database system are observed, statistics generation costs utilizing a target database system are estimated, and source statistics generation costs utilizing a source database system are estimated. The statistics generation costs are compared and statistics generation queries by the target database system are triggered in response to the statistics generation costs utilizing the target database system having a predefined relationship with the source statistics generation costs utilizing the source database system. The statistics generation queries are performed by the target database system in response to the triggering by the source database system. The generated statistics are sent from the target database system to the source database system, the table statistics in a metadata catalog are updated based on the generated statistics, and the updated table statistics are used to optimize a query plan.Type: GrantFiled: March 24, 2020Date of Patent: November 23, 2021Assignee: International Business Machines CorporationInventors: Dennis Butterstein, Oliver Benke, Tobias Ulrich Bergmann, Felix Beier, Terence P. Purcell
-
Patent number: 11144547Abstract: A method, a computer program product and a system are provided. A case expression is analyzed for redundant expressions, patterns equivalent to a built-in function, and specific functions to form a simplified expression, wherein the case expression is written in a language supported by a database management system. The simplified expression is optimized based on a cost benefit evaluation of no optimization, native code generation, hashing inputs, and a results caching algorithm to form an execution plan. The execution plan is utilized in the database management system for the case expression.Type: GrantFiled: May 24, 2019Date of Patent: October 12, 2021Assignee: International Business Machines CorporationInventors: Andrei F. Lurie, Terence P. Purcell, Martina Simova, Jonathan Sloan
-
Publication number: 20210303575Abstract: Methods and systems for generating database statistics. Table statistics in a metadata catalog of a source database system are observed, statistics generation costs utilizing a target database system are estimated, and source statistics generation costs utilizing a source database system are estimated. The statistics generation costs are compared and statistics generation queries by the target database system are triggered in response to the statistics generation costs utilizing the target database system having a predefined relationship with the source statistics generation costs utilizing the source database system. The statistics generation queries are performed by the target database system in response to the triggering by the source database system. The generated statistics are sent from the target database system to the source database system, the table statistics in a metadata catalog are updated based on the generated statistics, and the updated table statistics are used to optimize a query plan.Type: ApplicationFiled: March 24, 2020Publication date: September 30, 2021Inventors: Dennis Butterstein, Oliver Benke, Tobias Ulrich Bergmann, Felix Beier, Terence P. Purcell
-
Publication number: 20210256023Abstract: Provided are techniques for efficient subquery predicate generation to reduce processing in a multi-table join. It is determined that a query is to be rewritten, wherein the query includes a predicate for a first column of a first table and a second column of a second table. The query is rewritten with generated subquery predicates for the first table that are to be applied to the second table and with generated subquery predicates for the second table that are to be applied to the first table. Minimum and maximum ranges for columns of the generated subquery predicates at a cursor open are determined. The minimum and maximum ranges are merged to determine inclusive ranges that exist in the first table and the second table. The generated subquery predicates are rewritten as derived local subquery predicates with the inclusive ranges. The query with the derived local subquery predicates is executed.Type: ApplicationFiled: April 30, 2021Publication date: August 19, 2021Inventors: Thomas A. Beavin, Andrei F. Lurie, Terence P. Purcell
-
Patent number: 11048703Abstract: Provided are techniques for minimizing processing using an index when non-leading columns match an aggregation key. A query that includes a clause and an aggregation key is received. It is determined that a non-leading key column of an index matches a column of the aggregation key comprising any of: a leading column of an ORDER BY aggregation key, any column of a duplicate removal aggregation key, a MIN aggregation key, and a MAX aggregation key. The clause is processed using an order of the aggregation key to generate a first result set. Then, the clause is processed using the non-leading key column of the index that matched the column of the aggregation key and the first result set to generate a second result set. The second result set is returned.Type: GrantFiled: June 20, 2019Date of Patent: June 29, 2021Assignee: International Business Machines CorporationInventors: Terence P. Purcell, Thomas A. Beavin
-
Publication number: 20210191943Abstract: A method, system and computer program product for processing queries in a hybrid database system. A database management system of the hybrid database system obtains an estimated execution time of a query by an online transaction processing engine. The database management system further obtains an estimated execution time of the query by a database accelerator. Furthermore, the database management system determines a wait time (which could be zero) for the database accelerator to process the query. The database management system then determines whether the online transaction processing engine or the database accelerator will process the query using the estimated execution time of the query by the online transaction processing engine, the estimated execution time of the query by the database accelerator and the wait time (which could be zero) for the database accelerator to process the query. In this manner, the processing of queries in hybrid database systems is optimized.Type: ApplicationFiled: December 18, 2020Publication date: June 24, 2021Inventors: Al Chakra, Ruiping Li, Terence P. Purcell, Lily Zhou
-
Patent number: 11036732Abstract: Provided are techniques for efficient subquery predicate generation to reduce processing in a multi-table join. It is determined that a query is to be rewritten, wherein the query includes a predicate for a first column of a first table and a second column of a second table. The query is rewritten with generated subquery predicates for the first table that are to be applied to the second table and with generated subquery predicates for the second table that are to be applied to the first table. Minimum and maximum ranges for columns of the generated subquery predicates at a cursor open are determined. The minimum and maximum ranges are merged to determine inclusive ranges that exist in the first table and the second table. The generated subquery predicates are rewritten as derived local subquery predicates with the inclusive ranges. The query with the derived local subquery predicates is executed.Type: GrantFiled: March 28, 2017Date of Patent: June 15, 2021Assignee: International Business Machines CorporationInventors: Thomas A. Beavin, Andrei F. Lurie, Terence P. Purcell
-
Patent number: 11030193Abstract: Provided are techniques for efficient subquery predicate generation to reduce processing in a multi-table join. It is determined that a query is to be rewritten, wherein the query includes a predicate for a first column of a first table and a second column of a second table. The query is rewritten with generated subquery predicates for the first table that are to be applied to the second table and with generated subquery predicates for the second table that are to be applied to the first table. Minimum and maximum ranges for columns of the generated subquery predicates at a cursor open are determined. The minimum and maximum ranges are merged to determine inclusive ranges that exist in the first table and the second table. The generated subquery predicates are rewritten as derived local subquery predicates with the inclusive ranges. The query with the derived local subquery predicates is executed.Type: GrantFiled: November 20, 2018Date of Patent: June 8, 2021Assignee: International Business Machines CorporationInventors: Thomas A. Beavin, Andrei F. Lurie, Terence P. Purcell
-
Patent number: 11010380Abstract: Provided are techniques for minimizing processing using an index when non-leading columns match an aggregation key. A query that includes a clause and an aggregation key is received. It is determined that a non-leading key column of an index matches a column of the aggregation key comprising any of: a leading column of an ORDER BY aggregation key, any column of a duplicate removal aggregation key, a MIN aggregation key, and a MAX aggregation key. The clause is processed using an order of the aggregation key to generate a first result set. Then, the clause is processed using the non-leading key column of the index that matched the column of the aggregation key and the first result set to generate a second result set. The second result set is returned.Type: GrantFiled: February 13, 2018Date of Patent: May 18, 2021Assignee: International Business Machines CorporationInventors: Terence P. Purcell, Thomas A. Beavin
-
Patent number: 10936625Abstract: Provided are techniques for progressive optimization for implicit CAST predicates. In response to receiving a query with an original predicate comparing a character value of a character data type to a literal value of a numeric data type, a number of iterations is set to a length of the character data type. For each of the number of iterations, prefix predicates are generated based on that iteration and processed to identify a qualified index key in an index. Once the iterations are completed, the character value and the literal value are normalized. The original predicate with the normalized character value and the normalized literal value is applied starting at the qualified index key identified in a last iteration to generate a result set for the query. The result set is returned.Type: GrantFiled: December 27, 2017Date of Patent: March 2, 2021Assignee: International Business Machines CorporationInventors: Terence P. Purcell, Thomas A. Beavin, Christopher J. Crone, Andrei F. Lurie
-
Patent number: 10929370Abstract: Disclosed aspects relate to index maintenance management of a relational database management system having a set of indexes. A subset of the set of indexes, which is indicated as a non-critical index subset, is detected with respect to the set of indexes. Related to maintenance of the subset of the set of indexes, a determination is made to perform an index maintenance impact action. Corresponding to the subset of the set of indexes, the index maintenance impact action is performed.Type: GrantFiled: April 14, 2016Date of Patent: February 23, 2021Assignee: International Business Machines CorporationInventors: Saghi Amirsoleymani, John J. Campbell, Andrei F. Lurie, Terence P. Purcell
-
Patent number: 10909116Abstract: A method, system and computer program product for processing queries in a hybrid database system. A database management system of the hybrid database system obtains an estimated execution time of a query by an online transaction processing engine. The database management system further obtains an estimated execution time of the query by a database accelerator. Furthermore, the database management system determines a wait time (which could be zero) for the database accelerator to process the query. The database management system then determines whether the online transaction processing engine or the database accelerator will process the query using the estimated execution time of the query by the online transaction processing engine, the estimated execution time of the query by the database accelerator and the wait time (which could be zero) for the database accelerator to process the query. In this manner, the processing of queries in hybrid database systems is optimized.Type: GrantFiled: February 20, 2018Date of Patent: February 2, 2021Assignee: International Business Machines CorporationInventors: Al Chakra, Ruiping Li, Terence P. Purcell, Lily Zhou
-
Patent number: 10754858Abstract: An access plan including multiple indexes is selected for accessing data of a database system. A filtering status of each index within the selected access plan is determined based on information available at execution of the selected access plan. The order of the indexes within the selected access plan is modified based on the filtering status of the indexes, and the selected access plan is executed with the modified order of the indexes. The execution of the selected access plan is interrupted in response to accessing a threshold amount of data and the filtering of the indexes of the selected access plan is estimated. A processing order for the indexes is determined based on the estimated filtering. The execution of the selected access plan resumes based on the determined processing order.Type: GrantFiled: January 6, 2017Date of Patent: August 25, 2020Assignee: International Business Machines CorporationInventors: Brian L. Baggett, Patrick D. Bossman, Shuanglin Guo, Andrei F. Lurie, Terence P. Purcell, Martina Simova
-
Patent number: 10691690Abstract: A method, system and computer program product are provided. A first index scan of records of a database table is performed. A first index used by the first index scan is specified in a DBMS query statement as being ordered by one or more field values of one or more fields of the first index. A second scan of the database table is performed to determine whether each respective record of the second scan is qualified based on one or more fields of the each respective record of the second scan and a predicate. A lookup data structure is built indicating whether each of the respective records of the second scan is qualified. The lookup data structure is used to determine whether a record of the first index scan is qualified when the lookup data structure becomes available.Type: GrantFiled: October 31, 2017Date of Patent: June 23, 2020Assignee: International Business Machines CorporationInventors: Brian L. Baggett, Thomas A. Beavin, Oliver Draese, Shuanglin Guo, Andrei F. Lurie, Terence P. Purcell, Shengxi Suo
-
Patent number: 10671587Abstract: Provided are techniques for reduced fixed length sort of variable length columns. A fixed length sort is performed to sort a table having rows for a query, wherein the fixed length sort is based on a sort key comprised of a variable length column. A pre-determined number of the rows are read to identify the variable length column as a candidate for truncation. A length for truncating the variable length column is determined based on database statistics and based on the reading of the pre-determined number of the rows. The variable length column is truncated to the determined length to output a truncated variable length column for the sort key. The rows are sorted on the sort key having the truncated variable length column. Results are generated using the sorted rows and returned for the query.Type: GrantFiled: March 31, 2017Date of Patent: June 2, 2020Assignee: International Business Machines CorporationInventors: Brian L. Baggett, Allan B. Lebovitz, Terence P. Purcell