公开(公告)号：US06625593B1

公开(公告)日：2003-09-23

申请号：US09395771

申请日：1999-09-13

申请人： Ting Yu Leung ,  Mir Hamid Pirahesh ,  David E. Simmen ,  Tuong Chanh Truong

发明人： Ting Yu Leung ,  Mir Hamid Pirahesh ,  David E. Simmen ,  Tuong Chanh Truong

IPC分类号： G06F1730

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

摘要： A method, apparatus, and article of manufacture for optimizing database queries. The query is analyzed to determine whether at least a portion of the query can be evaluated using a plurality of parallel operations without data redistribution. If so, then the most efficient query execution plan that uses these parallel operations is constructed and executed.

摘要翻译： 用于优化数据库查询的方法，装置和制品。 分析查询以确定是否可以使用多个并行操作来评估查询的至少一部分而没有数据重新分配。 如果是这样，则构建并执行使用这些并行操作的最有效的查询执行计划。

公开(公告)号：US6032143A

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

申请号：US206194

申请日：1998-12-04

申请人： Ting Yu Leung ,  Alex Chak-tsang Lui ,  Mir Hamid Pirahesh ,  Tuong Chanh Truong

发明人： Ting Yu Leung ,  Alex Chak-tsang Lui ,  Mir Hamid Pirahesh ,  Tuong Chanh Truong

IPC分类号： G06F17/30

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

摘要： A relational data base management system includes a query processor that permits consideration of alternative query plans by the query optimizer so one table can be sent to a selected network location for subquery evaluation in consideration of maximum processing efficiency. Subqueries are converted from "predicate push-down" form to scalar subqueries, enabling upper tables to be sent to nodes of lower tables and vice versa, thereby permitting selection of the node direction depending on the least cost alternative. The optimizer of the query processor is presented with rewritten query code that permits more than one alternative for sending tables for evaluation. The optimizer evaluates the alternatives permitted by the rewritten code, determines the optimal plan for each alternative, and selects the least-cost plan from among the plans evaluated. Thus, the optimizer can decide to send an outer table to where a subquery table is located, or can decide to send a subquery table to where an outer table is located, depending on which is more efficient.

摘要翻译： 关系数据库管理系统包括查询处理器，其允许由查询优化器考虑替代查询计划，因此考虑到最大处理效率，可以将一个表发送到所选网络位置进行子查询评估。 子查询从“谓词下推”形式转换为标量子查询，使得上表可以发送到下表的节点，反之亦然，从而允许根据最低成本替代方案选择节点方向。 查询处理器的优化器被呈现为重写的查询代码，其允许用于发送表进行评估的多个替代方案。 优化器评估重写代码允许的替代方案，确定每个替代方案的最佳计划，并从评估的计划中选择最低成本计划。 因此，优化器可以决定将外部表发送到子查询表所在的位置，或者可以决定将子查询表发送到外部表所在的位置，具体取决于哪个更有效。

公开(公告)号：US5864840A

公开(公告)日：1999-01-26

申请号：US884697

申请日：1997-06-30

申请人： Ting Yu Leung ,  Alex Chak-tsang Lui ,  Mir Hamid Pirahesh ,  Tuong Chanh Truong

发明人： Ting Yu Leung ,  Alex Chak-tsang Lui ,  Mir Hamid Pirahesh ,  Tuong Chanh Truong

IPC分类号： G06F17/30

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

摘要： A relational data base management system includes a query processor that permits consideration of alternative query plans by the query optimizer so one table can be sent to a selected network location for subquery evaluation in consideration of maximum processing efficiency. Subqueries are converted from "predicate push-down" form to scalar subqueries, enabling upper tables to be sent to nodes of lower tables and vice versa, thereby permitting selection of the node direction depending on the least cost alternative. The optimizer of the query processor is presented with rewritten query code that permits more than one alternative for sending tables for evaluation. The optimizer evaluates the alternatives permitted by the rewritten code, determines the optimal plan for each alternative, and selects the least-cost plan from among the plans evaluated. Thus, the optimizer can decide to send an outer table to where a subquery table is located, or can decide to send a subquery table to where an outer table is located, depending on which is more efficient.

公开(公告)号：US6085189A

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

申请号：US304024

申请日：1999-05-03

申请人： Mir Hamid Pirahesh ,  David E. Simmen ,  Tuong Chanh Truong

发明人： Mir Hamid Pirahesh ,  David E. Simmen ,  Tuong Chanh Truong

IPC分类号： G06F17/30

CPC分类号： G06F17/3041 ,  G06F17/30365 ,  G06F17/30424 ,  Y10S707/99931 ,  Y10S707/99932 ,  Y10S707/99933 ,  Y10S707/99935

摘要： A database system and method provide support for a positioned data set modification operation on a collection of data sets involved in a data set query operation, in which the data set modification operation is based on the position of a query execution iterator. At query compile time, the database system generates an iterator position retrieval function and a data set modification function for each data set involved in the query operation. At query execution time, the database system invokes each of the iterator retrieval functions until the iterator position retrieval function for a current open data set returns the current the position of the query execution iterator on that data set. The database system then invokes each of the data set modification functions until the data set modification function for the current open data set performs a data set modification operation at the current position of the query execution iterator specified by the iterator position retrieval function. The system and method may be implemented on single node systems and on multi-node shared-nothing parallelism architectures.

摘要翻译： 数据库系统和方法提供了对数据集查询操作中涉及的数据集合的定位数据集修改操作的支持，其中数据集修改操作基于查询执行迭代器的位置。 在查询编译时，数据库系统为查询操作中涉及的每个数据集生成迭代器位置检索功能和数据集修改功能。 在查询执行时，数据库系统调用每个迭代器检索函数，直到当前打开的数据集的迭代器位置检索函数返回当前查询执行迭代器在该数据集上的位置。 数据库系统然后调用每个数据集修改功能，直到当前打开的数据集的数据集修改功能在迭代器位置检索功能指定的查询执行迭代器的当前位置执行数据集修改操作。 系统和方法可以在单节点系统和多节点无共享平行架构上实现。

公开(公告)号：US5960426A

公开(公告)日：1999-09-28

申请号：US884246

申请日：1997-06-30

申请人： Mir Hamid Pirahesh ,  David E. Simmen ,  Tuong Chanh Truong

发明人： Mir Hamid Pirahesh ,  David E. Simmen ,  Tuong Chanh Truong

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

CPC分类号： G06F17/3041 ,  G06F17/30365 ,  G06F17/30424 ,  Y10S707/99931 ,  Y10S707/99932 ,  Y10S707/99933 ,  Y10S707/99935

摘要： A database system and method provide support for a positioned data set modification operation on a collection of data sets involved in a data set query operation, in which the data set modification operation is based on the position of a query execution iterator. At query compile time, the database system generates an iterator position retrieval function and a data set modification function for each data set involved in the query operation. At query execution time, the database system invokes each of the iterator retrieval functions until the iterator position retrieval function for a current open data set returns the current the position of the query execution iterator on that data set. The database system then invokes each of the data set modification functions until the data set modification function for the current open data set performs a data set modification operation at the current position of the query execution iterator specified by the iterator position retrieval function. The system and method may be implemented on single node systems and on multi-node shared-nothing parallelism architectures.

摘要翻译： 数据库系统和方法提供了对数据集查询操作中涉及的数据集合的定位数据集修改操作的支持，其中数据集修改操作基于查询执行迭代器的位置。 在查询编译时，数据库系统为查询操作中涉及的每个数据集生成迭代器位置检索功能和数据集修改功能。 在查询执行时，数据库系统调用每个迭代器检索函数，直到当前打开的数据集的迭代器位置检索函数返回当前查询执行迭代器在该数据集上的位置。 数据库系统然后调用每个数据集修改功能，直到当前打开的数据集的数据集修改功能在迭代器位置检索功能指定的查询执行迭代器的当前位置执行数据集修改操作。 系统和方法可以在单节点系统和多节点无共享平行架构上实现。

公开(公告)号：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区域”的分区。 这些分区可以将操作强制到单节点，协调器节点，目录节点或多个节点或节点组上的特定分区类。

公开(公告)号：US06453314B1

公开(公告)日：2002-09-17

申请号：US09364753

申请日：1999-07-30

申请人： Petrus Kai Chung Chan ,  Roberta Jo Cochrane ,  Sam Sampson Lightstone ,  Mir Hamid Pirahesh ,  Richard Sefton Sidle ,  Tuong Chanh Truong ,  Michael J. Winer ,  Calisto Paul Zuzarte

发明人： Petrus Kai Chung Chan ,  Roberta Jo Cochrane ,  Sam Sampson Lightstone ,  Mir Hamid Pirahesh ,  Richard Sefton Sidle ,  Tuong Chanh Truong ,  Michael J. Winer ,  Calisto Paul Zuzarte

IPC分类号： G06F1730

CPC分类号： G06F17/30371 ,  Y10S707/99933 ,  Y10S707/99952

摘要： The invention provides method and apparatus for use in a database management system for managing a database containing data, where the database has storage for storing data in the database, and has an instruction processor for processing instructions for managing data stored in the database. The database has a constraint manager for managing constraints on said data stored in the database. The invention provides efficient method and means for deferred checking of data after bulk loading into said database for violation of constraints by determining an appropriate procedure for constraint checking by determining whether constraint checking is required; and if it is required, determining whether to implement full, or incremental checking for compliance with said constraints; and then checking the data in the database in accordance with the determined constraint checking procedure.

摘要翻译： 本发明提供了一种在数据库管理系统中使用的方法和装置，用于管理包含数据的数据库，其中数据库具有用于在数据库中存储数据的存储，并且具有用于处理用于管理存储在数据库中的数据的指令的指令处理器。 数据库具有约束管理器，用于管理存储在数据库中的所述数据的约束。 本发明提供了通过确定是否需要约束检查来确定用于约束检查的适当过程来批量加载到所述数据库中用于违反约束的数据的延迟检查的有效方法和手段; 如果需要，确定是否对所述约束执行全面或增量检查; 然后根据确定的约束检查程序检查数据库中的数据。

公开(公告)号：US5930795A

公开(公告)日：1999-07-27

申请号：US786603

申请日：1997-01-21

申请人： Yao-Ching Stephen Chen ,  Jyh-Herng Chow ,  Roberta Jo Cochrane ,  Gene Y.C. Fuh ,  Nelson Mendonca Mattos ,  Mir Hamid Pirahesh ,  Jeffrey D. Richey ,  Brian Thinh-Vinh Tran ,  Tuong Chanh Truong

发明人： Yao-Ching Stephen Chen ,  Jyh-Herng Chow ,  Roberta Jo Cochrane ,  Gene Y.C. Fuh ,  Nelson Mendonca Mattos ,  Mir Hamid Pirahesh ,  Jeffrey D. Richey ,  Brian Thinh-Vinh Tran ,  Tuong Chanh Truong

IPC分类号： G06F17/30

CPC分类号： G06F17/30421 ,  G06F17/30474

摘要： A framework for a query compiler and run-time environment for resolving a table reference to a dynamic table that is first identified at run-time but is initially unknown at compile-time. A parser parses the table reference and creates a parsed representation for the table that identifies the type of dynamic table. A code generator creates executable plans containing run-time table object representations (TAOB), from the parsed representations, that contain the type of dynamic table. The TAOB is also extended to provide for parameters that are definable at run-time, including a table ID of the actual table entity being referenced. A routine is embedded in a run-time routine that checks the type of dynamic table in the TAOB and dispatches control to one of a plurality of corresponding routines, depending upon the type of dynamic table. The corresponding routines use the extended TAOB to dynamically link the table reference to the dynamic table, depending upon the type of dynamic table. Once the dynamic table has been linked to the table reference, query processing continues as if that table were a base or derived table.

摘要翻译： 用于查询编译器和运行时环境的框架，用于解析对运行时首先标识但最初在编译时未知的动态表的表引用。 解析器解析表引用，并创建用于标识动态表类型的表的解析表示。 代码生成器从解析的表示形式创建包含运行时表对象表示（TAOB）的可执行计划，其中包含动态表的类型。 TAOB还被扩展以提供在运行时可定义的参数，包括被引用的实际表实体的表ID。 例程被嵌入到运行时程序中，该运行时程序检查TAOB中的动态表的类型，并根据动态表的类型将控制调度到多个相应的例程之一。 相应的例程使用扩展TAOB来动态地将表引用动态链接到动态表，这取决于动态表的类型。 一旦将动态表链接到表引用，查询处理将继续进行，就像该表是基类或派生表一样。

公开(公告)号：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查询，例如具有搜索和定位的插入，删除和更新的查询以及行级触发器。

公开(公告)号：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转换。 一个子查询块堆叠在另一个子查询块的顶部，以消除一个子查询。 此过程允许转换的查询执行比原始查询更高效，同时提供相同的结果。 然后在计算机中执行查询以有效地从关系数据库检索数据。