Shared nothing parallel execution of procedural constructs in SQL
    1.
    发明授权
    Shared nothing parallel execution of procedural constructs in SQL 失效
    在SQL中并不执行任何程序结构

    公开(公告)号:US6081801A

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

    申请号:US885479

    申请日:1997-06-30

    IPC分类号: G06F17/30 G06Q10/04 G06F17/00

    摘要: 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区域”的分区。 这些分区可以将操作强制到单节点,协调器节点,目录节点或多个节点或节点组上的特定分区类。

    Synchronization of SQL actions in a relational database system
    2.
    发明授权
    Synchronization of SQL actions in a relational database system 失效
    在关系数据库系统中同步SQL操作

    公开(公告)号:US5873075A

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

    申请号:US884832

    申请日:1997-06-30

    IPC分类号: G06F17/30

    摘要: 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查询,例如具有搜索和定位的插入,删除和更新的查询以及行级触发器。

    Query optimization by transparently altering properties of relational tables using materialized views
    3.
    发明授权
    Query optimization by transparently altering properties of relational tables using materialized views 有权
    通过使用实例化视图透明地更改关系表的属性来进行查询优化

    公开(公告)号:US06339769B1

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

    申请号:US09152551

    申请日:1998-09-14

    IPC分类号: G06F1730

    摘要: 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.

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

    Automatic recognition and rerouting of queries for optimal performance
    4.
    发明授权
    Automatic recognition and rerouting of queries for optimal performance 有权
    自动识别和重新路由查询以获得最佳性能

    公开(公告)号:US06460027B1

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

    申请号:US09152559

    申请日:1998-09-14

    IPC分类号: G06F1730

    摘要: 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子句。 使用查询和摘要表定义之间的包含测试来分析查询,以确定在查询中的任何地方发生的表达式,但不在汇总表中是否可以包含在汇总表中。 在执行查询时,当表达式可以从汇总表的一个或多个列中重新导出时,查询中的表达式将被包含在汇总表中。

    Cube indices for relational database management systems
    7.
    发明授权
    Cube indices for relational database management systems 有权
    关系数据库管理系统的多维数据集索引

    公开(公告)号:US06560594B2

    公开(公告)日:2003-05-06

    申请号:US09401474

    申请日:1999-09-22

    IPC分类号: G06F700

    摘要: A method, apparatus, and article of manufacture for optimizing database queries using subsumption tests between the query and at least one summary table that comprises a cube index to determine whether an expression in the query can be subsumed in the summary table. The summary table stores at least one materialized view involving at least one GROUP BY operation that computes at least one of the following: (1) a cube, (2) a rollup, (3) a grouping set, and (4) a concatenation of cubes, rollups, grouping sets, and one or more grouping items. When the expression in the query can be subsumed in the summary table, the query is rewritten to use the summary table.

    摘要翻译: 一种使用查询和包括多维数据集索引的至少一个汇总表之间的包含测试优化数据库查询的方法,装置和制品,用于确定查询中的表达式是否可以包含在汇总表中。 汇总表存储至少一个涉及至少一个GROUP BY操作的物化视图,该至少一个GROUP BY操作计算以下中的至少一个:(1)立方体,(2)汇总,(3)分组集合,以及(4)连接 多维数据集,汇总,分组集和一个或多个分组项目。 当查询中的表达式可以包含在汇总表中时,将重写查询以使用汇总表。

    Query processing system that computes GROUPING SETS, ROLLUP, and CUBE
with a reduced number of GROUP BYs in a query graph model
    8.
    发明授权
    Query processing system that computes GROUPING SETS, ROLLUP, and CUBE with a reduced number of GROUP BYs in a query graph model 失效
    在查询图模型中使用减少数量的GROUP BY来计算GROUPING SETS,ROLLUP和CUBE的查询处理系统

    公开(公告)号:US5963936A

    公开(公告)日:1999-10-05

    申请号:US885485

    申请日:1997-06-30

    IPC分类号: G06F17/30

    摘要: Method and apparatus for detecting and stacking grouping sets to support GROUP BY operations with GROUPING SETS, ROLLUP and CUBE extensions in relational database management systems, with greatly reduced numbers of grouping sets. A first GROUP BY (element-list1) is input to a second GROUP BY (element-list2), resulting in the GROUP BY of the intersection of the two lists. This intersection property is then useable to reduce the number of GROUP BYs required to implement the grouping by GROUPING SETS, ROLLUPs, and CUBEs required for the online analytical processing of data contained in the database.

    摘要翻译: 用于检测和堆叠分组集的方法和装置,以便在关系数据库管理系统中使用GROUPING SETS,ROLLUP和CUBE扩展来支持GROUP BY操作,大大减少了分组集合的数量。 第一个GROUP BY(element-list1)被输入到第二个GROUP BY(element-list2),导致GROUP BY两个列表的交集。 然后,该交集属性可用于减少通过GROUPING SETS,ROLLUP和CUBE实现分组所需的GROUP BY数,用于数据库中包含的数据的在线分析处理。

    Efficient implementation of typed view hierarchies for ORDBMS
    9.
    发明授权
    Efficient implementation of typed view hierarchies for ORDBMS 有权
    有效实现ORDBMS的类型化视图层次结构

    公开(公告)号:US06421658B1

    公开(公告)日:2002-07-16

    申请号:US09365590

    申请日:1999-07-30

    IPC分类号: G06F1730

    摘要: A method for typed view hierarchies for an object-relational database management (OPDBMS). The views in the hierarchy are merged into a unified hierarchy view through case expressions for mapping and wrapping of overloaded columns. During the merge, the underlying logical tables are eliminated which results in a simplified query graph. The transformation is a non-loss operation which condenses the type information within the case expressions. This allows update, delete and insert operations to be resolved on the query graph on a local level. Using query rewrite rules, the case expressions may be simplified further.

    摘要翻译: 用于对象关系数据库管理(OPDBMS)的类型化视图层次结构的方法。 层次结构中的视图通过用于映射和包装重载列的案例表达式合并到统一层次结构视图中。 在合并期间,消除了基础逻辑表,从而产生简化的查询图。 该转换是一种非损失操作,它将表达式中的类型信息进行聚合。 这允许在本地级别的查询图上解决更新,删除和插入操作。 使用查询重写规则,可以进一步简化案例表达。