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: 9846712Abstract: 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: April 25, 2015Date of Patent: December 19, 2017Assignee: International Business Machines CorporationInventors: Andrei F. Lurie, Terence P. Purcell, Ying Zeng
-
Publication number: 20170300517Abstract: 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: ApplicationFiled: April 14, 2016Publication date: October 19, 2017Inventors: Saghi Amirsoleymani, John J. Campbell, Andrei F. Lurie, Terence P. Purcell
-
Publication number: 20170249360Abstract: A query optimizer receives a relational database management system (RDBMS) query having a join predicate with a join between a first and a second table. The query optimizer determines a high skew value for a first variable joining the first and second tables at columns per the join predicate. A count query on one of the first and second tables is constructed and run only using the high skew value as a substitution for the first variable. A quantity of records for the join of the first and second tables is estimated using results of the count query. Different access paths (e.g., query plans) are used by the query optimizer depending on whether the estimated quantity of records exceeds a previously determined threshold or not.Type: ApplicationFiled: February 26, 2016Publication date: August 31, 2017Inventors: JASON L. ALPERS, TERENCE P. PURCELL, Patrick D. Bossman
-
Patent number: 9652497Abstract: Embodiments of techniques related to optimizing and processing queries are disclosed. In one embodiment, a query to access data of one or more databases is received, and a hybrid access path for accessing the data is generated. Generating the hybrid access path may include determining a plurality of candidate access paths and respective path cost values and selecting, based on the respective path cost values, two or more candidate access paths from the plurality of candidate access paths. Generating the hybrid access path may include generating, based on the two or more candidate access paths, the hybrid access path, the hybrid access path including at least one decision point and a set of options. The at least one decision point of the hybrid access path may indicate a difference between two or more candidate sequences of operations of candidate access paths from the two or more candidate access paths.Type: GrantFiled: April 23, 2014Date of Patent: May 16, 2017Assignee: International Business Machines CorporationInventors: Thomas A. Beavin, Andrei F. Lurie, Terence P. Purcell
-
Patent number: 9652498Abstract: Embodiments of techniques related to optimizing and processing queries are disclosed. In one embodiment, a query to access data of one or more databases is received, and a hybrid access path for accessing the data is generated. Generating the hybrid access path may include determining a plurality of candidate access paths and respective path cost values and selecting, based on the respective path cost values, two or more candidate access paths from the plurality of candidate access paths. Generating the hybrid access path may include generating, based on the two or more candidate access paths, the hybrid access path, the hybrid access path including at least one decision point and a set of options. The at least one decision point of the hybrid access path may indicate a difference between two or more candidate sequences of operations of candidate access paths from the two or more candidate access paths.Type: GrantFiled: March 21, 2015Date of Patent: May 16, 2017Assignee: International Business Machines CorporationInventors: Thomas A. Beavin, Andrei F. Lurie, Terence P. Purcell
-
Publication number: 20170116275Abstract: 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: ApplicationFiled: October 21, 2015Publication date: April 27, 2017Inventors: Brian L. Baggett, Patrick D. Bossman, Shuanglin Guo, Andrei F. Lurie, Terence P. Purcell, Martina Simova
-
Publication number: 20170116264Abstract: 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: ApplicationFiled: January 6, 2017Publication date: April 27, 2017Inventors: Brian L. Baggett, Patrick D. Bossman, Shuanglin Guo, Andrei F. Lurie, Terence P. Purcell, Martina Simova
-
Publication number: 20170116288Abstract: 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: ApplicationFiled: January 6, 2017Publication date: April 27, 2017Inventors: Jason L. Alpers, Patrick D. Bossman, Terence P. Purcell
-
Publication number: 20170075954Abstract: 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: ApplicationFiled: September 16, 2015Publication date: March 16, 2017Inventors: Jason L. Alpers, Patrick D. Bossman, Terence P. Purcell
-
Patent number: 9582540Abstract: Methods, systems, and computer readable media of executing a database query are provided. One or more predicates are generated based on implicit filtering present within the database query, wherein each predicate specifies a condition with respect to a predicate value. An access path is selected for the database query based on the one or more predicates and the one or more predicates are integrated within the selected access plan. The database query is executed in accordance with the selected access plan. At least one predicate value based on data accessed is updated, as the database query is being executed, to increase filtering of data during remaining execution of the database query.Type: GrantFiled: August 21, 2014Date of Patent: February 28, 2017Assignee: INTERNATIONAL BUSINESS MACHINES CORPORATIONInventors: Thomas A. Beavin, Garth A. Dickie, Ian R. Finlay, Terence P. Purcell
-
Patent number: 9569495Abstract: Methods, systems, and computer readable media of executing a database query are provided. One or more predicates are generated based on implicit filtering present within the database query, wherein each predicate specifies a condition with respect to a predicate value. An access path is selected for the database query based on the one or more predicates and the one or more predicates are integrated within the selected access plan. The database query is executed in accordance with the selected access plan. At least one predicate value based on data accessed is updated, as the database query is being executed, to increase filtering of data during remaining execution of the database query.Type: GrantFiled: April 7, 2015Date of Patent: February 14, 2017Assignee: INTERNATIONAL BUSINESS MACHINES CORPORATIONInventors: Thomas A. Beavin, Garth A. Dickie, Ian R. Finlay, Terence P. Purcell
-
Patent number: 9524305Abstract: System, method, and computer program product to modify a received query, the received query including a first predicate specifying a comparison operator and a second predicate specifying an IS NULL operator, by replacing the comparison operator of the first predicate with an extended comparison operator, and removing the IS NULL operator from the received query, the extended comparison operator configured to support searching of NULL values in a database management system (DBMS), wherein the extended comparison operator is configured to return rows having column data matching a value predefined to represent NULL values.Type: GrantFiled: June 25, 2014Date of Patent: December 20, 2016Assignee: INTERNATIONAL BUSINESS MACHINES CORPORATIONInventors: Thomas A. Beavin, Mengchu Cai, Dengfeng Gao, Jae H. Lee, Li-Mey P. Lee, Terence P. Purcell, Shengxi Suo
-
Patent number: 9519662Abstract: System, method, and computer program product to modify a received query, the received query including a first predicate specifying a comparison operator and a second predicate specifying an IS NULL operator, by replacing the comparison operator of the first predicate with an extended comparison operator, and removing the IS NULL operator from the received query, the extended comparison operator configured to support searching of NULL values in a database management system (DBMS), wherein the extended comparison operator is configured to return rows having column data matching a value predefined to represent NULL values.Type: GrantFiled: September 10, 2013Date of Patent: December 13, 2016Assignee: INTERNATIONAL BUSINESS MACHINES CORPORATIONInventors: Thomas A. Beavin, Mengchu Cai, Dengfeng Gao, Jae H. Lee, Li-Mey P. Lee, Terence P. Purcell, Shengxi Suo
-
Publication number: 20160314171Abstract: 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: ApplicationFiled: July 5, 2016Publication date: October 27, 2016Inventors: Andrei F. LURIE, Terence P. PURCELL, Ying ZENG
-
Publication number: 20160314154Abstract: 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: ApplicationFiled: April 25, 2015Publication date: October 27, 2016Inventors: Andrei F. LURIE, Terence P. PURCELL, Ying ZENG
-
Publication number: 20160055208Abstract: According to present invention embodiments, methods, systems, and computer readable media of executing a database query are provided. One or more predicates are generated based on implicit filtering present within the database query, wherein each predicate specifies a condition with respect to a predicate value. An access path is selected for the database query based on the one or more predicates and the one or more predicates are integrated within the selected access plan. The database query is executed in accordance with the selected access plan. At least one predicate value based on data accessed is updated, as the database query is being executed, to increase filtering of data during remaining execution of the database query.Type: ApplicationFiled: April 7, 2015Publication date: February 25, 2016Inventors: Thomas A. Beavin, Garth A. Dickie, Ian R. Finlay, Terence P. Purcell
-
Publication number: 20160055210Abstract: According to present invention embodiments, methods, systems, and computer readable media of executing a database query are provided. One or more predicates are generated based on implicit filtering present within the database query, wherein each predicate specifies a condition with respect to a predicate value. An access path is selected for the database query based on the one or more predicates and the one or more predicates are integrated within the selected access plan. The database query is executed in accordance with the selected access plan. At least one predicate value based on data accessed is updated, as the database query is being executed, to increase filtering of data during remaining execution of the database query.Type: ApplicationFiled: August 21, 2014Publication date: February 25, 2016Inventors: Thomas A. Beavin, Garth A. Dickie, Ian R. Finlay, Terence P. Purcell
-
Patent number: 9256643Abstract: A technique for factoring uncertainty into cost-based query optimization includes: determining the degree of uncertainty involved in the cost estimates for the query, determining the degree of sensitivity the query has to that uncertainty, and determining if there is an access path that performs well across the range of possible conditions that could occur at execution time, reducing the risk of performance spikes and performance volatility. If such an access path exists, select that access path; if not, perform parametric query optimization or query re-optimization.Type: GrantFiled: March 27, 2014Date of Patent: February 9, 2016Assignee: International Business Machines CorporationInventors: Jason L. Alpers, Thomas A. Beavin, Patrick D. Bossman, Shuanglin Guo, Terence P. Purcell
-
Patent number: 9208198Abstract: A technique for factoring uncertainty into cost-based query optimization includes: determining the degree of uncertainty involved in the cost estimates for the query, determining the degree of sensitivity the query has to that uncertainty, and determining if there is an access path that performs well across the range of possible conditions that could occur at execution time, reducing the risk of performance spikes and performance volatility. If such an access path exists, select that access path; if not, perform parametric query optimization or query re-optimization.Type: GrantFiled: October 17, 2012Date of Patent: December 8, 2015Assignee: International Business Machines CorporationInventors: Jason L. Alpers, Thomas A. Beavin, Patrick D. Bossman, Shuanglin Guo, Terence P. Purcell
-
Publication number: 20150339350Abstract: 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: ApplicationFiled: May 23, 2014Publication date: November 26, 2015Applicant: International Business Machines CorporationInventors: Brian L. Baggett, Michael A. Chang, Shuanglin Guo, Ou Jin, Terence P. Purcell