专利快速检索-快速检索全球专利，免费商用专利数据库-IPRDB

1. 发明授权

US06460027B1 Automatic recognition and rerouting of queries for optimal performance 有权
标题翻译：自动识别和重新路由查询以获得最佳性能
公开(公告)号：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子句。使用查询和摘要表定义之间的包含测试来分析查询，以确定在查询中的任何地方发生的表达式，但不在汇总表中是否可以包含在汇总表中。在执行查询时，当表达式可以从汇总表的一个或多个列中重新导出时，查询中的表达式将被包含在汇总表中。

2. 发明授权

US06339769B1 Query optimization by transparently altering properties of relational tables using materialized views 有权
标题翻译：通过使用实例化视图透明地更改关系表的属性来进行查询优化
公开(公告)号：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.
摘要翻译：使用查询中引用的表的实例化视图优化数据库查询的方法，装置和制品，其中物化视图具有与所引用的表不同的属性。实体化视图可以复制在计算机系统的多个处理器上，使得可以以本地方式执行部分或全部查询，其中不存在执行操作所需的数据移动。实体化视图还可以使用与参考表不同的分区键在计算机系统的多个处理器上分区。实例化视图可以是表的垂直和/或水平子集，使得仅在表中存在所选择的列和/或元组。可以将列添加到物化视图以包含复合表达式的预计算结果，并且可以在物化视图的列上创建索引。

3. 发明授权

US06847962B1 Analyzing, optimizing and rewriting queries using matching and compensation between query and automatic summary tables 有权
标题翻译：使用查询和自动汇总表之间的匹配和补偿来分析，优化和重写查询
公开(公告)号：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子句，在表中实现，并描述了汇总表的派生方式。使用查询和汇总表的定义（即自身的查询）之间的匹配/补偿测试来分析查询，以确定查询中任何地方（而不是摘要表中）中发生的表达式是否可以使用汇总表中的内容，或者通过一些关系运算符将汇总表的内容与其他基表合并，因此该查询被归纳或与汇总表定义重叠。

4. 发明授权

US6081801A Shared nothing parallel execution of procedural constructs in SQL 失效
标题翻译：在SQL中并不执行任何程序结构
公开(公告)号：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区域”的分区。这些分区可以将操作强制到单节点，协调器节点，目录节点或多个节点或节点组上的特定分区类。

5. 发明授权

US5873075A Synchronization of SQL actions in a relational database system 失效
标题翻译：在关系数据库系统中同步SQL操作
公开(公告)号：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查询，例如具有搜索和定位的插入，删除和更新的查询以及行级触发器。

6. 发明授权

US6112198A Optimization of data repartitioning during parallel query optimization 失效
标题翻译：并行查询优化中数据重分区的优化
公开(公告)号：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.
摘要翻译：使用并行优化技术优化查询评估，使重新分区更有效率。通过识别实现查询操作的并行性的可能的分区要求，以及通过识别数据的分区属性何时满足查询操作的分区要求，可以提高效率。根据本发明的数据库管理系统使用并行查询处理技术来优化数据重新分区，或者完全避免它们。

7. 发明授权

US5960428A Star/join query optimization 失效
标题翻译：星号/加入查询优化
公开(公告)号：US5960428A
公开(公告)日：1999-09-28
申请号：US919739
申请日：1997-08-28
申请人： Bruce Gilbert Lindsay , Guy Maring Lohman , Mir Hamid Pirahesh , Eugene Jon Shekita , David Everett Simmen , Monica Sachiye Urata
发明人： Bruce Gilbert Lindsay , Guy Maring Lohman , Mir Hamid Pirahesh , Eugene Jon Shekita , David Everett Simmen , Monica Sachiye Urata
IPC分类号： G06F17/30
CPC分类号： G06F17/30466 , Y10S707/99932 , Y10S707/99933 , Y10S707/99934
摘要： Unwieldy star/join queries are performed more efficiently using a filtered fact table. Suitable queries include star/join queries with a large fact table joined with multiple subsidiary dimension tables, where indices exist over fact table join columns. The query is analyzed to prepare a query plan for the dimension table accesses. This plan is supplemented by adding nested loop join operations, where the inner table is a dimension table plan and the outer table is an index scan performed over a fact table index of the join column with the dimension table. The plan is also supplemented by filtering records resulting from the nested loop joins using a sequence of dynamic bit vectors, ultimately yielding a list of probable fact table records. The plan is further supplemented by fetching these records to construct a distilled fact which is used, instead of the large original table, to execute the query in considerably less time. If desired, the supplemented query plan and other competing approaches may studied to provide cost estimates, with the least costly approach being actually implemented.
摘要翻译：使用已过滤的事实表，更有效地执行无故障的星号/连接查询。合适的查询包括带有多个附属维度表的大型事实表的星号/连接查询，其中索引存在于事实表连接列上。分析查询以准备维度表访问的查询计划。此计划通过添加嵌套循环连接操作来补充，其中内部表是维度表计划，外部表是在具有维度表的连接列的事实表索引上执行的索引扫描。该计划还补充了使用一系列动态位向量的嵌套循环连接过滤记录，最终产生可能的事实表记录的列表。该计划进一步补充了通过获取这些记录来构建一个使用的蒸馏事实，而不是大的原始表，在相当短的时间内执行查询。如果需要，可以研究补充的查询计划和其他竞争方法，以提供成本估算，最低成本的方法正在实际实施。

8. 发明授权

US06529896B1 Method of optimizing a query having an existi subquery and a not-exists subquery 失效
标题翻译：优化具有exists子查询和不存在子查询的查询的方法
公开(公告)号：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转换。一个子查询块堆叠在另一个子查询块的顶部，以消除一个子查询。此过程允许转换的查询执行比原始查询更高效，同时提供相同的结果。然后在计算机中执行查询以有效地从关系数据库检索数据。

9. 发明授权

US06826562B1 Method of simplifying and optimizing scalar subqueries and derived tables that return exactly or at most one tuple 失效
标题翻译：简化和优化标量子查询和派生表的方法，它们完全返回或最多返回一个元组
公开(公告)号：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查询的优化技术，存储优化程序的程序存储设备以及用于优化查询的设备。分析查询以确定它是否包括精确返回或最多返回一个元组的子选择表达式。如果是这样，如果可能，执行量化器合并或消除，以允许查询比原始查询的执行效率更高，同时提供相同的结果。然后在计算机中执行查询以有效地从数据库中检索数据。

10. 发明授权

US06345267B1 Method and system for look ahead query evaluation planning based on interesting partition properties 失效
标题翻译：基于有趣的分区属性的方法和系统，用于提前查询评估计划
公开(公告)号：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中的复杂查询语句，无共享环境。

你已经成功收藏专利！

检索式保存成功!

IPRDB

热门服务

关于我们

友情链接

联系方式