公开(公告)号：US06529896B1

公开(公告)日：2003-03-04

申请号：US09506471

申请日：2000-02-17

申请人： Ting Yu Leung ,  Tuong Chanh Truong ,  Monica Sachiye Urata

发明人： Ting Yu Leung ,  Tuong Chanh Truong ,  Monica Sachiye Urata

IPC分类号： G06F1730

CPC分类号： G06F17/30451 ,  G06F17/30463 ,  Y10S707/99932

摘要： An optimization technique for SQL queries, a program storage device storing the optimization program, and an apparatus for optimizing a query is provided. A query is analyzed to determine whether it includes the WHERE clause which contains an “EXISTS” subquery and a “NOT EXISTS” subquery, or EXISTS-equivalent subqueries, in Boolean factor. If so, the subsumption test is performed on two subqueries. Then, the compensation predicate is applied to one of them, to perform the QGM transformation of the query. One subquery block is stacked on top of the other subquery block, in order to eliminate one subquery. This procedure allows the transformed query to perform more efficiently than the original query while providing same results. The query is then executed in the computer to efficiently retrieve data from the relational database.

摘要翻译： 提供了一种用于SQL查询的优化技术，存储优化程序的程序存储设备以及用于优化查询的设备。 分析查询以确定它是否包含WHERE子句，其中包含“EXISTS”子查询和“NOT EXISTS”子查询，或EXISTS等效的子查询（Boolean factor）。 如果是这样，则在两个子查询上执行包含测试。 然后，将补偿谓词应用于其中一个，执行查询的QGM转换。 一个子查询块堆叠在另一个子查询块的顶部，以消除一个子查询。 此过程允许转换的查询执行比原始查询更高效，同时提供相同的结果。 然后在计算机中执行查询以有效地从关系数据库检索数据。

公开(公告)号：US6081801A

公开(公告)日：2000-06-27

申请号：US885479

申请日：1997-06-30

申请人： Roberta Jo Cochrane ,  George Lapis ,  Mir Hamid Pirahesh ,  Richard Sefton Sidle ,  David Everett Simmen ,  Tuong Chanh Truong ,  Monica Sachiye Urata

发明人： Roberta Jo Cochrane ,  George Lapis ,  Mir Hamid Pirahesh ,  Richard Sefton Sidle ,  David Everett Simmen ,  Tuong Chanh Truong ,  Monica Sachiye Urata

IPC分类号： G06F17/30 ,  G06Q10/04 ,  G06F17/00

CPC分类号： G06Q10/04 ,  G06F17/30433 ,  G06F17/30445 ,  Y10S707/99933 ,  Y10S707/99938 ,  Y10S707/99952

摘要： An automated methodology, and an apparatus for practicing the methodology, which enables the power and flexibility inherent in shared nothing parallel database systems (MPP) to be utilized on complex queries which have, heretofore, contained query elements requiring local computation or local coordination of data computation performed across the nodes of the distributed system. The present invention provides these features and advantages by identifying and marking the subgraphs containing these types of query elements as "no TQ zones" in the preparation phase prior to optimization. When the optimizer sees the markings, it builds a plan that will force the computation of the marked subgraphs to be in the same section. This preparation phase also provides the partitioning information for all inputs to the "no TQ zones". This allows the bottom-up optimizer to correctly plan the partitioning for the "no TQ zones". These partitionings can force the operation to a single-node, the coordinator node, the catalog node, or to a particular partition class on multiple nodes, or nodegroups.

摘要翻译： 一种自动化方法和一种用于实践该方法的装置，其使得无共享并行数据库系统（MPP）中固有的功能和灵活性能够用于复杂查询，其以前包含需要本地计算或数据的本地协调的查询元素 在分布式系统的节点上进行计算。 本发明通过在优化之前的准备阶段中将包含这些类型的查询元素的子图识别和标记为“无TQ区”来提供这些特征和优点。 当优化器看到标记时，它建立一个计划，强制计算标记的子图在同一部分。 此准备阶段还提供“无TQ区域”的所有输入的分区信息。 这允许自下而上优化器正确地计划“无TQ区域”的分区。 这些分区可以将操作强制到单节点，协调器节点，目录节点或多个节点或节点组上的特定分区类。

公开(公告)号：US5873075A

公开(公告)日：1999-02-16

申请号：US884832

申请日：1997-06-30

申请人： Roberta Jo Cochrane ,  George Lapis ,  Mir Hamid Pirahesh ,  Richard Sefton Sidle ,  David Everett Simmen ,  Tuong Chanh Truong ,  Monica Sachiye Urata

发明人： Roberta Jo Cochrane ,  George Lapis ,  Mir Hamid Pirahesh ,  Richard Sefton Sidle ,  David Everett Simmen ,  Tuong Chanh Truong ,  Monica Sachiye Urata

IPC分类号： G06F17/30

CPC分类号： G06F17/30427 ,  G06F17/30371 ,  G06F17/30436 ,  G06F17/30471 ,  G06F17/3051 ,  Y10S707/99932

摘要： An automated system for detecting and preventing mutating table violations of database integrity in a SQL query before generation and selection of an optimal query execution plan (QEP). This system modifies the query graph model (QGM) to restrict the choice of execution plans to those that avoid mutating table integrity (MTI) violations, thereby forcing database integrity during table mutation when executing the optimal QEP. Mutating table integrity violations are detected by evaluating the position in the QGM of each write-node referencing a particular base table with respect to each of the positions of all other read- and write-nodes referencing of the same base table. Every common-referencing node pair is tested for sequencing conflicts and a data-flow dam is inserted in the QGM where necessary or a poke query is inserted between the nodes to force the completion of the execution of one node before initiating execution of the other common-referencing node. The system of this invention allows processing of all non-cyclic and most cyclic SQL queries known to cause mutating table integrity violations, such as queries having searched and positioned inserts, deletes and updates, and row-level triggers.

摘要翻译： 一种用于在生成和选择最优查询执行计划（QEP）之前，在SQL查询中检测和防止突变表违反数据库完整性的自动化系统。 该系统修改查询图模型（QGM），将执行计划的选择限制为避免突变表完整性（MTI）违规的选择，从而在执行最佳QEP时强制表突变期间的数据库完整性。 通过评估参考相同基准表的所有其他读 - 写节点的每个位置的引用特定基表的每个写节点的QGM中的位置来检测突变表完整性违反。 每个公共参考节点对都进行排序冲突测试，并且数据流大坝插入到QGM中，如果需要，或者在节点之间插入一个戳查询，强制完成一个节点的执行，然后再启动另一个常见的执行 - 参考节点。 本发明的系统允许处理已知导致突变表完整性违例的所有非循环和大多数循环SQL查询，例如具有搜索和定位的插入，删除和更新的查询以及行级触发器。

公开(公告)号：US06460027B1

公开(公告)日：2002-10-01

申请号：US09152559

申请日：1998-09-14

申请人： Roberta Jo Cochrane ,  George Lapis ,  Ting Yu Leung ,  Mir Hamid Pirahesh ,  Monica Sachiye Urata

发明人： Roberta Jo Cochrane ,  George Lapis ,  Ting Yu Leung ,  Mir Hamid Pirahesh ,  Monica Sachiye Urata

IPC分类号： G06F1730

CPC分类号： G06F17/30457 ,  Y10S707/99932 ,  Y10S707/99933 ,  Y10S707/99934

摘要： A method, apparatus, and article of manufacture for optimizing database queries uses a derived summary table, wherein a definition of the summary table is based on a full select statement that is materialized in the table and describes how the summary table was derived. The summary table definition can be a complex query involving nested GROUP BY operations as well as complex HAVING clauses with subqueries or join operations. A query is analyzed using subsumption tests between the query and the definition of the summary table to determine whether expressions occurring anywhere in the query, but not in the summary table, can be subsumed in the summary table. In performing the query, an expression in the query is subsumed in the summary table when the expression can be re-derived from one or more of the columns of the summary table.

摘要翻译： 用于优化数据库查询的方法，装置和制品使用派生汇总表，其中汇总表的定义基于在表中实现的完整选择语句，并描述汇总表的衍生方式。 汇总表定义可以是涉及嵌套GROUP BY操作的复杂查询以及具有子查询或连接操作的复杂HAVING子句。 使用查询和摘要表定义之间的包含测试来分析查询，以确定在查询中的任何地方发生的表达式，但不在汇总表中是否可以包含在汇总表中。 在执行查询时，当表达式可以从汇总表的一个或多个列中重新导出时，查询中的表达式将被包含在汇总表中。

公开(公告)号：US06339769B1

公开(公告)日：2002-01-15

申请号：US09152551

申请日：1998-09-14

申请人： Roberta Jo Cochrane ,  George Lapis ,  Ting Yu Leung ,  Mir Hamid Pirahesh ,  Monica Sachiye Urata ,  Richard Sefton Sidle ,  David E. Simmen ,  Calisto Paul Zuzarte

发明人： Roberta Jo Cochrane ,  George Lapis ,  Ting Yu Leung ,  Mir Hamid Pirahesh ,  Monica Sachiye Urata ,  Richard Sefton Sidle ,  David E. Simmen ,  Calisto Paul Zuzarte

IPC分类号： G06F1730

CPC分类号： G06F17/30457 ,  Y10S707/99932 ,  Y10S707/99933 ,  Y10S707/99934 ,  Y10S707/99945

摘要： A method, apparatus, and article of manufacture for optimizing database queries using a materialized view for a table referenced in the query, wherein the materialized view has different properties than the referenced table. The materialized view may be replicated across multiple processors of the computer system, so that some or all of the query can be executed in a local fashion wherein there is no data movement required to perform the operations. The materialized view also may be partitioned across multiple processors of the computer system using a different partitioning key than the referenced table. The materialized view may be a vertical and/or horizontal subset of the table, so that only selected columns and/or tuples from the table are present therein. Columns may be added to the materialized view to contain pre-computed results of complex expressions, and indices may be created on the columns of the materialized view.

摘要翻译： 使用查询中引用的表的实例化视图优化数据库查询的方法，装置和制品，其中物化视图具有与所引用的表不同的属性。 实体化视图可以复制在计算机系统的多个处理器上，使得可以以本地方式执行部分或全部查询，其中不存在执行操作所需的数据移动。 实体化视图还可以使用与参考表不同的分区键在计算机系统的多个处理器上分区。 实例化视图可以是表的垂直和/或水平子集，使得仅在表中存在所选择的列和/或元组。 可以将列添加到物化视图以包含复合表达式的预计算结果，并且可以在物化视图的列上创建索引。

公开(公告)号：US06847962B1

公开(公告)日：2005-01-25

申请号：US09502821

申请日：2000-02-11

申请人： Roberta Jo Cochrane ,  George Lapis ,  Ting Yu Leung ,  Mark A. Paskin ,  Mir Hamid Pirahesh ,  Yang Sun ,  Monica Sachiye Urata ,  Markos Zaharioudakis

发明人： Roberta Jo Cochrane ,  George Lapis ,  Ting Yu Leung ,  Mark A. Paskin ,  Mir Hamid Pirahesh ,  Yang Sun ,  Monica Sachiye Urata ,  Markos Zaharioudakis

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

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

摘要： A method, apparatus, and article of manufacture for optimizing database queries using a derived summary table, wherein a definition of the summary table is based on a full select statement, including, but not limited to, a derived table involving nested GROUP BY operations and complex HAVING clauses with subqueries or joins, that is materialized in the table and describes how the summary table was derived. A query is analyzed using matching/compensation tests between the query and the definition of the summary table (that is, a query by itself) to determine whether expressions occurring anywhere in the query, but not in the summary table, can be derived using either the content in the summary table alone, or after combining (through some relational operator) the content of the summary table with other base tables, and hence the query is subsumed by or overlaps with the summary table definition.

摘要翻译： 一种用于使用导出的摘要表优化数据库查询的方法，装置和制品，其中所述汇总表的定义基于完整的选择语句，包括但不限于涉及嵌套的GROUP BY操作的导出表，以及 具有子查询或联接的复杂HAVING子句，在表中实现，并描述了汇总表的派生方式。 使用查询和汇总表的定义（即自身的查询）之间的匹配/补偿测试来分析查询，以确定查询中任何地方（而不是摘要表中）中发生的表达式是否可以使用 汇总表中的内容，或者通过一些关系运算符将汇总表的内容与其他基表合并，因此该查询被归纳或与汇总表定义重叠。

公开(公告)号：US06826562B1

公开(公告)日：2004-11-30

申请号：US09450795

申请日：1999-11-29

申请人： Ting Yu Leung ,  Monica Sachiye Urata ,  Swati Vora

发明人： Ting Yu Leung ,  Monica Sachiye Urata ,  Swati Vora

IPC分类号： G06F1730

CPC分类号： G06F17/30451 ,  Y10S707/99932 ,  Y10S707/99934

摘要： An optimization technique for SQL queries, a program storage device storing the optimization program, and an apparatus for optimizing a query is provided. A query is analyzed to determine whether it includes subselect expressions that return exactly or at most one tuple. If so, the quantifier merger or elimination is performed, if possible, to allow query to perform more efficiently than that of the original query while providing same results. The query is then executed in the computer to efficiently retrieve data from the database.

摘要翻译： 提供了一种用于SQL查询的优化技术，存储优化程序的程序存储设备以及用于优化查询的设备。 分析查询以确定它是否包括精确返回或最多返回一个元组的子选择表达式。 如果是这样，如果可能，执行量化器合并或消除，以允许查询比原始查询的执行效率更高，同时提供相同的结果。 然后在计算机中执行查询以有效地从数据库中检索数据。

公开(公告)号：US06345267B1

公开(公告)日：2002-02-05

申请号：US09517355

申请日：2000-03-02

申请人： Guy Maring Lohman ,  Eugene Jon Shekita ,  David E. Simmen ,  Monica Sachiye Urata

发明人： Guy Maring Lohman ,  Eugene Jon Shekita ,  David E. Simmen ,  Monica Sachiye Urata

IPC分类号： G06F1730

CPC分类号： G06F17/30463 ,  Y10S707/99932 ,  Y10S707/99933 ,  Y10S707/99934 ,  Y10S707/99935 ,  Y10S707/99944

摘要： A relational data base management system includes a query processor that uses a query operator partition property to perform QEP pruning and to ensure that data input to a query operator is partitioned appropriately for the operation. The partition property indicates the group of network nodes across which a table is distributed. The query processor also makes use of partition classes that are designated “interesting classes” to perform preoptimization planning and query pruning, and to perform look-ahead partitioning based on partition classes that are identified as being of interest to future operations, thereby more efficiently evaluating complex query statements in an MPP, shared-nothing environment.

摘要翻译： 关系数据库管理系统包括一个查询处理器，该查询处理器使用查询运算符分区属性执行QEP修剪，并确保输入到查询运算符的数据被适当地分配给操作。 分区属性指示分配表的网络节点组。 查询处理器还利用被指定为“有趣类”的分区类来执行优化前规划和查询修剪，并且基于被识别为未来操作感兴趣的分区类执行先行分区，从而更有效地评估 MPP中的复杂查询语句，无共享环境。

公开(公告)号：US6112198A

公开(公告)日：2000-08-29

申请号：US106473

申请日：1998-06-29

申请人： Guy Maring Lohman ,  Mir Hamid Pirahesh ,  Eugene Jon Shekita ,  David E. Simmen ,  Monica Sachiye Urata

发明人： Guy Maring Lohman ,  Mir Hamid Pirahesh ,  Eugene Jon Shekita ,  David E. Simmen ,  Monica Sachiye Urata

IPC分类号： G06F17/30

CPC分类号： G06F17/30445 ,  G06F17/30498 ,  Y10S707/99933

摘要： Query evaluation is optimized using parallel optimization techniques to make repartitioning more efficient. Efficiency is improved by recognizing the possible partitioning requirements for achieving parallelism for a query operation, and by recognizing when the partitioning property of data satisfies the partitioning requirements of a query operation. A data base management system in accordance with the invention uses parallel query processing techniques to optimize data repartitioning, or to avoid it altogether.

摘要翻译： 使用并行优化技术优化查询评估，使重新分区更有效率。 通过识别实现查询操作的并行性的可能的分区要求，以及通过识别数据的分区属性何时满足查询操作的分区要求，可以提高效率。 根据本发明的数据库管理系统使用并行查询处理技术来优化数据重新分区，或者完全避免它们。

公开(公告)号：US6092062A

公开(公告)日：2000-07-18

申请号：US885073

申请日：1997-06-30

申请人： Guy Maring Lohman ,  Eugene Jon Shekita ,  David E. Simmen ,  Monica Sachiye Urata

发明人： Guy Maring Lohman ,  Eugene Jon Shekita ,  David E. Simmen ,  Monica Sachiye Urata

IPC分类号： G06F17/30

CPC分类号： G06F17/30463 ,  Y10S707/99932 ,  Y10S707/99933 ,  Y10S707/99934 ,  Y10S707/99935 ,  Y10S707/99944

摘要： A relational data base management system includes a query processor that uses a query operator partition property to perform QEP pruning and to ensure that data input to a query operator is partitioned appropriately for the operation. The partition property indicates the group of network nodes across which a table is distributed. The query processor also makes use of partition classes that are designated "interesting classes" to perform preoptimization planning and query pruning, and to perform look-ahead partitioning based on partition classes that are identified as being of interest to future operations, thereby more efficiently evaluating complex query statements in an MPP, shared-nothing environment.

摘要翻译： 关系数据库管理系统包括一个查询处理器，该查询处理器使用查询运算符分区属性执行QEP修剪，并确保输入到查询运算符的数据被适当地分配给操作。 分区属性指示分配表的网络节点组。 查询处理器还利用被指定为“有趣类”的分区类来执行优化前规划和查询修剪，并且基于被识别为未来操作感兴趣的分区类执行先行分区，从而更有效地评估 MPP中的复杂查询语句，无共享环境。