公开(公告)号：US07644063B2

公开(公告)日：2010-01-05

申请号：US11840703

申请日：2007-08-17

申请人： Patrick D. Bossman ,  Frank D. Bower ,  John J. Campbell ,  You-Chin Fuh ,  Adarsh R. Pannu ,  Terrence P. Purcell

发明人： Patrick D. Bossman ,  Frank D. Bower ,  John J. Campbell ,  You-Chin Fuh ,  Adarsh R. Pannu ,  Terrence P. Purcell

IPC分类号： G06F17/30

CPC分类号： G06F17/30474

摘要： An apparatus, system, and method are disclosed for ensuring query execution plan stability in a database management system. The present invention binds a static database query to a new query execution plan (QEP) that produces the same result set as an original QEP bound to the static database query. Next, the present invention identifies one of the original QEP and the new QEP as a primary QEP and automatically collects execution statistics for the original QEP and the new QEP. Finally, the present invention automatically selects one of the original QEP and the new QEP as the primary QEP in response to completion of the automatic collection of execution statistics, the primary QEP selected such that the automatically selected QEP provides optimal execution performance based on performance criteria in comparison to the automatically unselected QEP, the primary QEP selected based on the execution statistics.

摘要翻译： 公开了一种用于确保数据库管理系统中的查询执行计划稳定性的装置，系统和方法。 本发明将静态数据库查询绑定到新的查询执行计划（QEP），该计划产生与绑定到静态数据库查询的原始QEP相同的结果集。 接下来，本发明将原始QEP和新QEP中的一个标识为主QEP，并自动收集原始QEP和新QEP的执行统计信息。 最后，本发明响应于完成执行统计的自动收集而自动选择原始QEP和新QEP中的一个作为主QEP，选择主QEP使得自动选择的QEP基于性能标准提供最佳执行性能 与自动未选择的QEP相比，基于执行统计信息选择主要QEP。

公开(公告)号：US20090049012A1

公开(公告)日：2009-02-19

申请号：US11840703

申请日：2007-08-17

申请人： Patrick D. Bossman ,  Frank D. Bower ,  John J, Campbell ,  You-Chin Fuh ,  Adarsh R. Pannu ,  Terence P. Purcell

发明人： Patrick D. Bossman ,  Frank D. Bower ,  John J, Campbell ,  You-Chin Fuh ,  Adarsh R. Pannu ,  Terence P. Purcell

IPC分类号： G06F17/30

CPC分类号： G06F17/30474

摘要： An apparatus, system, and method are disclosed for ensuring query execution plan stability in a database management system. The present invention binds a static database query to a new query execution plan (QEP) that produces the same result set as an original QEP bound to the static database query. Next, the present invention identifies one of the original QEP and the new QEP as a primary QEP and automatically collects execution statistics for the original QEP and the new QEP. Finally, the present invention automatically selects one of the original QEP and the new QEP as the primary QEP in response to completion of the automatic collection of execution statistics, the primary QEP selected such that the automatically selected QEP provides optimal execution performance based on performance criteria in comparison to the automatically unselected QEP, the primary QEP selected based on the execution statistics.

摘要翻译： 公开了一种用于确保数据库管理系统中的查询执行计划稳定性的装置，系统和方法。 本发明将静态数据库查询绑定到新的查询执行计划（QEP），该计划产生与绑定到静态数据库查询的原始QEP相同的结果集。 接下来，本发明将原始QEP和新QEP中的一个标识为主QEP，并自动收集原始QEP和新QEP的执行统计信息。 最后，本发明响应于完成执行统计的自动收集而自动选择原始QEP和新QEP中的一个作为主QEP，选择主QEP使得自动选择的QEP基于性能标准提供最佳执行性能 与自动未选择的QEP相比，基于执行统计信息选择主要QEP。

公开(公告)号：US08060495B2

公开(公告)日：2011-11-15

申请号：US12255520

申请日：2008-10-21

申请人： Thomas A. Beavin ,  Patrick D. Bossman ,  You-Chin Fuh ,  Adarsh R. Pannu ,  Terence P. Purcell

发明人： Thomas A. Beavin ,  Patrick D. Bossman ,  You-Chin Fuh ,  Adarsh R. Pannu ,  Terence P. Purcell

IPC分类号： G06F17/30

CPC分类号： G06F17/30463

摘要： An apparatus, system, and method are disclosed for improving query execution plan efficiency in a database management system. The present invention binds both static database queries and dynamic database queries to new query execution plans (QEPs) that produce the same result set as a former QEP bound to the database queries. Next, the present invention identifies one of the former QEPs and the new QEP as an active QEP and automatically collects execution statistics for one of the former QEPs and the new QEP. Finally, the present invention automatically selects one of the former QEPs and the new QEP as the active QEP in response to completion of the automatic collection of execution statistics, the active QEP selected such that the automatically selected QEP provides optimal execution performance based on performance criteria in comparison to the automatically unselected QEP, the active QEP selected based on the execution statistics.

摘要翻译： 公开了一种用于提高数据库管理系统中的查询执行计划效率的装置，系统和方法。 本发明将静态数据库查询和动态数据库查询绑定到新的查询执行计划（QEP），该查询执行计划（QEP）产生与数据库查询绑定的前一个QEP相同的结果集。 接下来，本发明将前一个QEP和新的QEP之一识别为活动QEP，并自动收集前一个QEP和新的QEP之一的执行统计信息。 最后，本发明响应于完成执行统计的自动收集而自动选择前者QEP和新QEP中的一个作为活动QEP，选择活动QEP使得自动选择的QEP基于性能标准提供最佳执行性能 与自动未选择的QEP相比，基于执行统计信息选择活动QEP。

公开(公告)号：US20100114868A1

公开(公告)日：2010-05-06

申请号：US12255520

申请日：2008-10-21

申请人： Thomas A. Beavin ,  Patrick D. Bossman ,  You-Chin Fuh ,  Adarsh R. Pannu ,  Terence P. Purcell

发明人： Thomas A. Beavin ,  Patrick D. Bossman ,  You-Chin Fuh ,  Adarsh R. Pannu ,  Terence P. Purcell

IPC分类号： G06F17/30 ,  G06F7/00

CPC分类号： G06F17/30463

摘要： An apparatus, system, and method are disclosed for improving query execution plan efficiency in a database management system. The present invention binds both static database queries and dynamic database queries to new query execution plans (QEPs) that produce the same result set as a former QEP bound to the database queries. Next, the present invention identifies one of the former QEPs and the new QEP as an active QEP and automatically collects execution statistics for one of the former QEPs and the new QEP. Finally, the present invention automatically selects one of the former QEPs and the new QEP as the active QEP in response to completion of the automatic collection of execution statistics, the active QEP selected such that the automatically selected QEP provides optimal execution performance based on performance criteria in comparison to the automatically unselected QEP, the active QEP selected based on the execution statistics.

摘要翻译： 公开了一种用于提高数据库管理系统中的查询执行计划效率的装置，系统和方法。 本发明将静态数据库查询和动态数据库查询绑定到新的查询执行计划（QEP），该查询执行计划（QEP）产生与数据库查询绑定的前一个QEP相同的结果集。 接下来，本发明将前一个QEP和新的QEP之一识别为活动QEP，并自动收集前一个QEP和新的QEP之一的执行统计信息。 最后，本发明响应于完成执行统计的自动收集而自动选择前者QEP和新QEP中的一个作为活动QEP，选择活动QEP使得自动选择的QEP基于性能标准提供最佳执行性能 与自动未选择的QEP相比，基于执行统计信息选择活动QEP。

公开(公告)号：US07725448B2

公开(公告)日：2010-05-25

申请号：US11848868

申请日：2007-08-31

申请人： Thomas A. Beavin ,  Patrick D. Bossman ,  John J. Campbell ,  You-Chin Fuh ,  Terence P. Purcell

发明人： Thomas A. Beavin ,  Patrick D. Bossman ,  John J. Campbell ,  You-Chin Fuh ,  Terence P. Purcell

IPC分类号： G06F17/30

CPC分类号： G06F17/30454

摘要： A method for performing disjunctive single-index access on a database is disclosed. The method includes a query engine determining whether a first OR predicate and a second OR predicate map to a shared index. Responsive to the query engine determining that the first OR predicate and the second OR predicate map to the shared index, the first OR predicate and the second OR predicate are ordered in an ascending sequence. A first range of the first OR predicate is queued. A first row of the shared index is probed for the first range of the first OR predicate. Whether the first row is disqualified by the first OR predicate is determined. Responsive to determining that the first row is not disqualified by the first OR predicate, the row is reported in a result reporting structure. Responsive to determining that the row is disqualified by the first OR predicate, whether the first range of the first OR predicate overlaps a second range of the second OR predicate is determined, and, responsive to determining that the first range of the first OR predicate overlaps the second range of the second OR predicate, the row is checked against second range of the second or predicate.

摘要翻译： 公开了一种在数据库上执行分离单索引访问的方法。 该方法包括查询引擎，确定第一OR谓词和第二OR谓词映射到共享索引。 响应于查询引擎确定第一OR谓词和第二OR谓词映射到共享索引，第一OR谓词和第二OR谓词以升序排列。 第一个OR谓词的第一个范围排队。 共享索引的第一行被探测到第一个OR谓词的第一个范围。 确定第一行是否被第一个OR谓词取消资格。 响应于确定第一行不被第一个OR谓词取消资格，该行将在结果报告结构中报告。 响应于确定该行被第一OR谓词取消资格，确定第一OR谓词的第一范围是否与第二OR谓词的第二范围重叠，并且响应于确定第一OR谓词的第一范围重叠 第二个OR谓词的第二个范围，该行是针对第二个或谓词的第二个范围进行检查的。

公开(公告)号：US20090063397A1

公开(公告)日：2009-03-05

申请号：US11848868

申请日：2007-08-31

申请人： THOMAS A. BEAVIN ,  Patrick D. Bossman ,  John J. Campbell ,  You-Chin Fuh ,  Terence P. Purcell

发明人： THOMAS A. BEAVIN ,  Patrick D. Bossman ,  John J. Campbell ,  You-Chin Fuh ,  Terence P. Purcell

IPC分类号： G06F17/30

CPC分类号： G06F17/30454

摘要： A method for performing disjunctive single-index access on a database is disclosed. The method includes a query engine determining whether a first OR predicate and a second OR predicate map to a shared index. Responsive to the query engine determining that the first OR predicate and the second OR predicate map to the shared index, the first OR predicate and the second OR predicate are ordered in an ascending sequence. A first range of the first OR predicate is queued. A first row of the shared index is probed for the first range of the first OR predicate. Whether the first row is disqualified by the first OR predicate is determined. Responsive to determining that the first row is not disqualified by the first OR predicate, the row is reported in a result reporting structure. Responsive to determining that the row is disqualified by the first OR predicate, whether the first range of the first OR predicate overlaps a second range of the second OR predicate is determined, and, responsive to determining that the first range of the first OR predicate overlaps the second range of the second OR predicate, the row is checked against second range of the second or predicate.

摘要翻译： 公开了一种在数据库上执行分离单索引访问的方法。 该方法包括查询引擎，确定第一OR谓词和第二OR谓词映射到共享索引。 响应于查询引擎确定第一OR谓词和第二OR谓词映射到共享索引，第一OR谓词和第二OR谓词以升序排列。 第一个OR谓词的第一个范围排队。 共享索引的第一行被探测到第一个OR谓词的第一个范围。 确定第一行是否被第一个OR谓词取消资格。 响应于确定第一行不被第一个OR谓词取消资格，该行将在结果报告结构中报告。 响应于确定该行被第一OR谓词取消资格，确定第一OR谓词的第一范围是否与第二OR谓词的第二范围重叠，并且响应于确定第一OR谓词的第一范围重叠 第二个OR谓词的第二个范围，该行是针对第二个或谓词的第二个范围进行检查的。

公开(公告)号：US07181446B2

公开(公告)日：2007-02-20

申请号：US10764286

申请日：2004-01-23

申请人： Patrick D. Bossman ,  Lee-Chin Hsu Liu ,  Jerry Mukai ,  Yumi K. Tsuji

发明人： Patrick D. Bossman ,  Lee-Chin Hsu Liu ,  Jerry Mukai ,  Yumi K. Tsuji

IPC分类号： G06F17/30 ,  G06F7/00

CPC分类号： G06F17/30451 ,  Y10S707/99933 ,  Y10S707/99934 ,  Y10S707/99935

摘要： A method, apparatus, and article of manufacture for optimizing a query in a computer system, wherein the query is performed by the computer system to retrieve data from a database stored on the computer system. The optimization is performed by identifying a correlated subquery in the query that qualifies for transformation, transitively closing a local filtering predicate within the identified correlated subquery that is on a same column as a correlation join predicate within the identified correlated subquery, and pulling out or bubbling up the transitively closed predicate from the correlated subquery to a parent query block of the query.

摘要翻译： 一种用于优化计算机系统中的查询的方法，装置和制品，其中所述查询由所述计算机系统执行以从存储在所述计算机系统上的数据库中检索数据。 优化是通过识别查询中的相关子查询来进行的，该相关子查询符合转换规则，在识别的相关子查询中传递性地关闭与所识别的相关子查询中的关联连接谓词在同一列上的本地过滤谓词，并且拉出或冒泡 将关联子查询的过渡关闭谓词提升到查询的父查询块。

公开(公告)号：US20080126393A1

公开(公告)日：2008-05-29

申请号：US11564748

申请日：2006-11-29

申请人： Patrick D. Bossman ,  Chih Jieh Chang ,  Gene Fuh ,  Tracy Ho ,  Chan-hua Liu ,  Kevin M. McBride ,  Xinyu Wang

发明人： Patrick D. Bossman ,  Chih Jieh Chang ,  Gene Fuh ,  Tracy Ho ,  Chan-hua Liu ,  Kevin M. McBride ,  Xinyu Wang

IPC分类号： G06F17/30

CPC分类号： G06F16/2453 ,  G06F16/217

摘要： A computer program product and system are disclosed for parsing a problem SQL statement into query clauses, formatting these query clauses into a hierarchical tree structure, generating performance statistics for clauses that have associated performance statistics and displaying the formatted SQL statement with each clause contained on a separate display line annotated with its corresponding associated performance statistics on the same display line and with the hierarchical tree structure represented by indention levels of the separate display lines.

摘要翻译： 公开了一种计算机程序产品和系统，用于将问题SQL语句解析成查询子句，将这些查询子句格式化为分层树结构，为具有关联性能统计信息的子句生成性能统计信息，并显示格式化的SQL语句，其中每个子句包含在 在同一显示行上以对应关联的性能统计信息注明分隔的显示行，并以分开的显示行的缩进级别表示的分级树结构。