公开(公告)号：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.

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

公开(公告)号：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.

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

公开(公告)号：US5963936A

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

申请号：US885485

申请日：1997-06-30

申请人： Roberta Jo Cochrane ,  Michelle Meichiou Jou ,  George Lapis ,  Mir Hamid Pirahesh

发明人： Roberta Jo Cochrane ,  Michelle Meichiou Jou ,  George Lapis ,  Mir Hamid Pirahesh

IPC分类号： G06F17/30

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

摘要： 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数，用于数据库中包含的数据的在线分析处理。

公开(公告)号：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.

公开(公告)号：US5797000A

公开(公告)日：1998-08-18

申请号：US667056

申请日：1996-06-20

申请人： Partha Pratim Bhattacharya ,  Jen-Yao Chung ,  Mir Hamid Pirahesh ,  Patricia G. Selinger ,  Marisa S. Viveros ,  Yun Wang ,  Lawrence Peter Zaino

发明人： Partha Pratim Bhattacharya ,  Jen-Yao Chung ,  Mir Hamid Pirahesh ,  Patricia G. Selinger ,  Marisa S. Viveros ,  Yun Wang ,  Lawrence Peter Zaino

IPC分类号： G06F17/30

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

摘要： A method of performing a parallel join operation on a pair of relations R1 and R2 in a system containing P processors organized into Q clusters of P/Q processors each. The system contains disk storage for each cluster, shared by the processors of that cluster, together with a shared intermediate memory (SIM) accessible by all processors. The relations R1 and R2 to be joined are first sorted on the join column. The underlying domain of the join column is then partitioned into P ranges of equal size. Each range is further divided into M subranges of progressively decreasing size to create MP tasks T.sub.m,p, the subranges of a given range being so sized relative to one another that the estimated completion time for task T.sub.m,p is a predetermined fraction that of task T.sub.m-1,p. Tasks T.sub.m,p with larger time estimates are assigned (and the corresponding tuples shipped) to the cluster to which processor p belongs, while tasks with smaller time estimates are assigned to the SIM, which is regarded as a universal cluster (cluster 0). The actual task-to-processor assignments are determined dynamically during the join phase in accordance with the dynamic longest processing time first (DLPT) algorithm. Each processor within a cluster picks its next task at any given decision point to be the one with the largest time estimate which is owned by that cluster or by cluster 0.

摘要翻译： 在包含P个处理器的系统中对一对关系R1和R2执行并行连接操作的方法，所述P个处理器分组为Q个P / Q处理器群。 该系统包含每个集群的磁盘存储，由该集群的处理器共享，以及可由所有处理器访问的共享中间存储器（SIM）。 要连接的关系R1和R2首先在连接列上排序。 然后将连接列的基础域分成相同大小的P范围。 每个范围进一步分为逐渐减小的M个子范围以创建MP任务Tm，p，给定范围的子范围相对于彼此大小相对于任务Tm的估计完成时间，p是任务的预定分数 Tm-1，p。 将具有较大时间估计值的任务Tm，p（和相应的元组发货）分配给处理器p所属的集群，而具有较小时间估计的任务被分配给被认为是通用集群（集群0）的SIM。 根据动态最长处理时间优先（DLPT）算法，在连接阶段期间动态确定实际的任务到处理器分配。 集群内的每个处理器在任何给定的决策点选择其下一个任务，作为拥有该集群或集群0的最大时间估计值。

公开(公告)号：US5742810A

公开(公告)日：1998-04-21

申请号：US521710

申请日：1995-08-31

申请人： John Shek-Luen Ng ,  Frederick Thomas Sharp ,  Mir Hamid Pirahesh ,  I-Shin Andy Wang ,  Gerald Herman Roth ,  Tak Ming Lo

发明人： John Shek-Luen Ng ,  Frederick Thomas Sharp ,  Mir Hamid Pirahesh ,  I-Shin Andy Wang ,  Gerald Herman Roth ,  Tak Ming Lo

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

CPC分类号： G06F17/30415 ,  Y10S707/99934 ,  Y10S707/99942

摘要： A system and method for passing arrays and structures as host variables to a stored procedure. An application program packs an array or structure into a binary large object (BLOB) and sends the BLOB to a database management system. An adapter called by the database management system, and coupled to the stored procedure, passes a reference to the data area in the BLOB to the stored procedure. After completion of the stored procedure, the BLOB is returned to the application program. Finally, upon the return from the stored procedure call, the application program unpacks the BLOB back into the array or structure.

摘要翻译： 将数组和结构作为主机变量传递到存储过程的系统和方法。 应用程序将数组或结构打包成二进制大对象（BLOB），并将BLOB发送到数据库管理系统。 由数据库管理系统调用并耦合到存储过程的适配器将对BLOB中的数据区域的引用传递给存储过程。 在完成存储过程之后，BLOB返回到应用程序。 最后，从存储过程调用返回时，应用程序将BLOB分解回数组或结构。