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).
-
Publication number: 20200073986Abstract: 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: ApplicationFiled: August 30, 2018Publication date: March 5, 2020Inventors: Terence P. Purcell, Thomas A. Beavin, Martin Dinh, Brian L. Baggett
-
Patent number: 10545972Abstract: According to an embodiment of the present invention, methods, systems, and computer readable media are provided for collecting statistics for use in optimizing a database query. Collected statistics for the database are compared to corresponding predetermined states of conditions for optimizing the database query. One or more collected statistics in compliance with the corresponding predetermined states of conditions are identified based on the comparison. Collection of the one or more identified statistics for the query optimization is disabled.Type: GrantFiled: January 6, 2017Date of Patent: January 28, 2020Assignee: International Business Machines CorporationInventors: Jason L. Alpers, Patrick D. Bossman, Terence P. Purcell
-
Patent number: 10459933Abstract: According to an embodiment of the present invention, methods, systems, and computer readable media are provided for collecting statistics for use in optimizing a database query. Collected statistics for the database are compared to corresponding predetermined states of conditions for optimizing the database query. One or more collected statistics in compliance with the corresponding predetermined states of conditions are identified based on the comparison. Collection of the one or more identified statistics for the query optimization is disabled.Type: GrantFiled: September 16, 2015Date of Patent: October 29, 2019Assignee: International Business Machines CorporationInventors: Jason L. Alpers, Patrick D. Bossman, Terence P. Purcell
-
Publication number: 20190324965Abstract: 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: ApplicationFiled: June 20, 2019Publication date: October 24, 2019Inventors: Terence P. Purcell, Thomas A. Beavin
-
Publication number: 20190278772Abstract: 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: ApplicationFiled: May 24, 2019Publication date: September 12, 2019Inventors: Andrei F. Lurie, Terence P. Purcell, Martina Simova, Jonathan Sloan
-
Patent number: 10394812Abstract: 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: December 6, 2016Date of Patent: August 27, 2019Assignee: International Business Machines CorporationInventors: Andrei F. Lurie, Terence P. Purcell, Martina Simova, Jonathan Sloan
-
Publication number: 20190258633Abstract: 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: ApplicationFiled: May 6, 2019Publication date: August 22, 2019Inventors: Brian L. Baggett, Thomas A. Beavin, Oliver Draese, Shuanglin Guo, Andrei F. Lurie, Terence P. Purcell, Shengxi Suo
-
Publication number: 20190258732Abstract: 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: February 20, 2018Publication date: August 22, 2019Inventors: Al Chakra, Ruiping Li, Terence P. Purcell, Lily Zhou
-
Publication number: 20190251195Abstract: 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: ApplicationFiled: February 13, 2018Publication date: August 15, 2019Inventors: Terence P. Purcell, Thomas A. Beavin
-
Publication number: 20190197175Abstract: 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: ApplicationFiled: December 27, 2017Publication date: June 27, 2019Inventors: Terence P. Purcell, Thomas A. Beavin, Christopher J. Crone, Andrei F. Lurie
-
Publication number: 20190129999Abstract: 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: ApplicationFiled: October 31, 2017Publication date: May 2, 2019Inventors: Brian L. Baggett, Thomas A. Beavin, Oliver Draese, Shuanglin Guo, Andrei F. Lurie, Terence P. Purcell, Shengxi Suo
-
Publication number: 20190087460Abstract: 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: November 20, 2018Publication date: March 21, 2019Inventors: Thomas A. Beavin, Andrei F. Lurie, Terence P. Purcell
-
Patent number: 10210210Abstract: 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: October 21, 2015Date of Patent: February 19, 2019Assignee: International Business Machines CorporationInventors: Brian L. Baggett, Patrick D. Bossman, Shuanglin Guo, Andrei F. Lurie, Terence P. Purcell, Martina Simova
-
Publication number: 20180285415Abstract: 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: March 28, 2017Publication date: October 4, 2018Inventors: Thomas A. Beavin, Andrei F. Lurie, Terence P. Purcell
-
Publication number: 20180285428Abstract: 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: ApplicationFiled: March 31, 2017Publication date: October 4, 2018Inventors: Brian L. Baggett, Allan B. Lebovitz, Terence P. Purcell
-
Publication number: 20180157707Abstract: 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: ApplicationFiled: December 6, 2016Publication date: June 7, 2018Inventors: Andrei F. Lurie, Terence P. Purcell, Martina Simova, Jonathan Sloan
-
Patent number: 9940359Abstract: Provided are techniques for a Data-Partitioned Secondary Index (DPSI) partition level join. While using a Data-Partitioned Secondary Index (DPSI) to perform a join of an outer table and an inner table, a different task from multiple tasks is assigned to each partition of the inner table. With each task, a join is performed of the outer table and the assigned partition of the inner table using the DPSI to generate results. The results from each different task are merged.Type: GrantFiled: May 23, 2014Date of Patent: April 10, 2018Assignee: International Business Machines CorporationInventors: Brian L. Baggett, Michael A. Chang, Shuanglin Guo, Ou Jin, Terence P. Purcell
-
Patent number: 9928274Abstract: Embodiments presented herein provide techniques for setting different methods of skipping duplicate values when executing a query statement in a relational database. A distance between a two distinct keys in an index, a current index key and a next distinct index key, are estimated. Based on the estimated distance, an appropriate duplicate-skipping method is determined. If the proximity between the distinct keys is relatively far apart (e.g., the keys reside in index pages that are at least an index page apart), then a “big skip” method is performed. Otherwise, if the proximity between the distinct keys is relatively near (e.g., the keys reside in the same index page), then a “little skip” method is performed.Type: GrantFiled: December 1, 2014Date of Patent: March 27, 2018Assignee: INTERNATIONAL BUSINESS MACHINES CORPORATIONInventors: Terence P. Purcell, Thomas A. Beavin, Li Xia, Michael R. Shadduck, Paramesh S. Desai, Qianyang Yu
-
Patent number: 9892158Abstract: Embodiments presented herein provide techniques for setting different methods of skipping duplicate values when executing a query statement in a relational database. A distance between a two distinct keys in an index, a current index key and a next distinct index key, are estimated. Based on the estimated distance, an appropriate duplicate-skipping method is determined. If the proximity between the distinct keys is relatively far apart (e.g., the keys reside in index pages that are at least an index page apart), then a “big skip” method is performed. Otherwise, if the proximity between the distinct keys is relatively near (e.g., the keys reside in the same index page), then a “little skip” method is performed.Type: GrantFiled: January 31, 2014Date of Patent: February 13, 2018Assignee: INTERNATIONAL BUSINESS MACHINES CORPORATIONInventors: Terence P. Purcell, Thomas A. Beavin, Li Xia, Michael R. Shadduck, Paramesh S. Desai, Qianyang Yu
-
Patent number: 9846713Abstract: In an index-only multi-index access in a database, when a query to be executed requires multiple indexes, a query optimizer determines whether columns are needed for a query result, subquery, or inner join result. If not, then the query result is resolved using qualified record identifiers (RIDs) from the multiple indexes without accessing the data pages. When the columns are needed and can be resolved using input values of the query, then the query result is resolved using the qualified RIDs from the multiple indexes and substituting column values with the input values of the query. When the columns are needed and cannot be resolved using the input values of the query, the query optimizer may store one or more index keys with its corresponding qualified RIDs from the multiple indexes and may resolve the query result using the stored index keys without accessing the data pages of the database.Type: GrantFiled: July 5, 2016Date of Patent: December 19, 2017Assignee: International Business Machines CorporationInventors: Andrei F. Lurie, Terence P. Purcell, Ying Zeng