利索-全球专利检索

  1. 登录
  2. 注册

搜索结果

23 条记录 (耗时 0.025 秒)

    Exploitation of uniqueness properties using a 1-tuple condition for the optimization of SQL queries
    1.
    发明授权
    Exploitation of uniqueness properties using a 1-tuple condition for the optimization of SQL queries 失效
    使用1元组条件来挖掘唯一性属性来优化SQL查询

    公开(公告)号：US5615361A

    公开(公告)日：1997-03-25

    申请号：US384759

    申请日：1995-02-07

    申请人： Ting Y. Leung ,  Mir H. Pirahesh ,  David E. Simmen ,  Lori G. Strain ,  Sanjai Tiwari

    发明人： Ting Y. Leung ,  Mir H. Pirahesh ,  David E. Simmen ,  Lori G. Strain ,  Sanjai Tiwari

    IPC分类号： G06F17/30

    CPC分类号： G06F17/30436 ,  G06F17/30451 ,  G06F17/30454 ,  Y10S707/954 ,  Y10S707/99933

    摘要： The present invention optimizes SQL queries by exploiting uniqueness properties. In identifying whether the generalized 1-tuple condition exists, the query is first analyzed to determine whether any columns referenced in a predicate of the query are bound. According to the present invention, columns may be bound to constant values or correlated columns or columns that are already bound. The bound columns, if any, are then analyzed to determine whether any of the bound columns comprise a key for its associated table. If these conditions exist, then the query satisfies the 1-tuple condition, in that it returns at most one tuple. Once the generalized 1-tuple condition has been identified to exist for the query, important query transformations can be performed for optimization purposes. These query transformations comprise the transformation of scalar subqueries into joins, or the elimination of distinctiveness requirements (i.e., DISTINCT keywords) from SELECT clauses.

    摘要翻译： 本发明通过利用唯一性来优化SQL查询。 在识别广义1元组条件是否存在时，首先分析查询以确定查询谓词中引用的任何列是否被绑定。 根据本发明，列可以绑定到已经结合的常数值或相关的列或列。 然后分析绑定的列（如果有的话），以确定任何绑定列是否包含其关联表的键。 如果这些条件存在，则查询满足1元组条件，因为它最多返回一个元组。 一旦确定了查询的广义1元组条件，就可以执行重要的查询转换来进行优化。 这些查询转换包括将标量子查询转换为联接，或从SELECT子句中消除明确性要求（即DISTINCT关键字）。

    Optimization of SQL queries using universal quantifiers, set intersection, and max/min aggregation in the presence of nullable columns
    2.
    发明授权
    Optimization of SQL queries using universal quantifiers, set intersection, and max/min aggregation in the presence of nullable columns 失效
    在存在可空列的情况下，使用通用量词，设置交集和最大/最小聚合优化SQL查询

    公开(公告)号：US5590324A

    公开(公告)日：1996-12-31

    申请号：US384925

    申请日：1995-02-07

    申请人： Ting Y. Leung ,  Mir H. Pirahesh ,  Michelle M. Jou ,  David E. Simmen

    发明人： Ting Y. Leung ,  Mir H. Pirahesh ,  Michelle M. Jou ,  David E. Simmen

    IPC分类号： G06F17/30

    CPC分类号： G06F17/30454 ,  G06F17/30463 ,  Y10S707/99935

    摘要： A method and apparatus for optimizing SQL queries by propagating and exploiting column nullability. Column nullability is identified and propagated using a three-valued logic, wherein a column of a table can be identified nullability information is exploited to optimize query operations through transformations. In one aspect of the present invention, quantified predicates (such as ">ALL") are transformed into simple predicates involving singleton subqueries so that indexing can be exploited. In another aspect of the present invention, "is not null" predicates are generated and pushed for certain aggregate queries. In still another aspect of the present invention, intersect operations are transformed into joins. The end result is that the present invention can significantly enhance the performance of the queries.

    摘要翻译： 通过传播和利用列可空性来优化SQL查询的方法和装置。 使用三值逻辑来识别和传播列可空性，其中可以识别表的列可利用性来利用信息来优化通过转换的查询操作。 在本发明的一个方面，量化谓词（例如“> ALL”）被转换成涉及单例子查询的简单谓词，以便可以利用索引。 在本发明的另一方面，“不为空”的谓词被生成并被推送用于某些聚合查询。 在本发明的另一方面，相交操作被转换为联接。 最终结果是本发明可以显着提高查询的性能。

    Optimization of SQL queries using early-out join transformations of column-bound relational tables
    3.
    发明授权
    Optimization of SQL queries using early-out join transformations of column-bound relational tables 失效
    使用列关联表的早期连接变换优化SQL查询

    公开(公告)号：US5548758A

    公开(公告)日：1996-08-20

    申请号：US463968

    申请日：1995-06-05

    申请人： Mir H. Pirahesh ,  Ting Y. Leung ,  Guy M. Lohman ,  Eugene J. Shekita ,  David E. Simmen

    发明人： Mir H. Pirahesh ,  Ting Y. Leung ,  Guy M. Lohman ,  Eugene J. Shekita ,  David E. Simmen

    IPC分类号： G06F17/30

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

    摘要： A method and apparatus for optimizing SQL queries in a relational database management system uses early-out join transformations. An early-out join comprises a many-to-one existential join, wherein the join scans an inner table for a match for each row of the outer table and terminates the scan for each row of the outer table when a single match is found in the inner table. To transform a many-to-many join to an early-out join, the query must include a requirement for distinctiveness, either explicitly or implicitly, in one or more result columns for the join operation. Distinctiveness can be specified using the DISTINCT keyword in the SELECT clause or can be implied from the predicates present in the query. The early-out join transformation also requires that no columns of the inner table be referenced after the join, or if an inner table column is referenced after the join, that each referenced column be "bound". A referenced column can be bound in one of three ways: (1) an inner table column can be bound to a constant through an equality predicate, (2) an inner table column can be bound to an outer table column, or (3) an inner table column can be bound to a correlated value, wherein the correlated value originates outside the query block. In all three cases, an inner table column can be bound through the transitivity of equality predicates.

    摘要翻译： 用于优化关系数据库管理系统中的SQL查询的方法和装置使用早期连接变换。 早期连接包括多对一存在连接，其中，连接扫描内部表以获取外部表的每一行的匹配，并且当找到单个匹配时终止外部表的每一行的扫描 内表。 要将多对多连接转换为早期连接，查询必须在连接操作的一个或多个结果列中明确或隐式地包含独特性的要求。 可以使用SELECT子句中的DISTINCT关键字指定不同之处，也可以从查询中存在的谓词中暗示。 早期连接转换还要求在连接之后不引用内部列的列，或者如果在连接之后引用了内部列列，则每个引用的列都将被“绑定”。 引用的列可以通过三种方式之一来绑定：（1）内表列可以通过相等谓词绑定到常量，（2）内表列可绑定到外表列，或（3） 可以将内表列绑定到相关值，其中相关值起始于查询块之外。 在所有三种情况下，内表列可以通过相等谓词的传递性来约束。

    Optimization of SQL queries using early-out join transformations
    4.
    发明授权
    Optimization of SQL queries using early-out join transformations 失效
    使用Early-out连接变换优化SQL查询

    公开(公告)号：US5548754A

    公开(公告)日：1996-08-20

    申请号：US385177

    申请日：1995-02-07

    申请人： Mir H. Pirahesh ,  Ting Y. Leung ,  Guy M. Lohman ,  Eugene J. Shekita ,  David E. Simmen

    发明人： Mir H. Pirahesh ,  Ting Y. Leung ,  Guy M. Lohman ,  Eugene J. Shekita ,  David E. Simmen

    IPC分类号： G06F17/30

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

    摘要： A method and apparatus for optimizing SQL queries in a relational database management system uses early-out join transformations. An early-out join comprises a many-to-one existential join, wherein the join scans an inner table for a match for each row of the outer table and terminates the scan for each row of the outer table when a single match is found in the inner table. To transform a many-to-many join to an early-out join, the query must include a requirement for distinctiveness, either explicitly or implicitly, in one or more result columns for the join operation. Distinctiveness can be specified using the DISTINCT keyword in the SELECT clause or can be implied from the predicates present in the query. The early-out join transformation also requires that no columns of the inner table be referenced after the join, or if an inner table column is referenced after the join, that each referenced column be "bound". A referenced column can be bound in one of three ways: (1) an inner table column can be bound to a constant through an equality predicate, (2) an inner table column can be bound to an outer table column, or (3) an inner table column can be bound to a correlated value, wherein the correlated value originates outside the query block. In all three cases, an inner table column can be bound through the transitivity of equality predicates.

    摘要翻译： 用于优化关系数据库管理系统中的SQL查询的方法和装置使用早期连接变换。 早期连接包括多对一存在连接，其中，连接扫描内部表以获取外部表的每一行的匹配，并且当找到单个匹配时终止外部表的每一行的扫描 内表。 要将多对多连接转换为早期连接，查询必须在连接操作的一个或多个结果列中明确或隐式地包含独特性的要求。 可以使用SELECT子句中的DISTINCT关键字指定不同之处，也可以从查询中存在的谓词中暗示。 早期连接转换还要求在连接之后不引用内部列的列，或者如果在连接之后引用了内部列列，则每个引用的列都将被“绑定”。 引用的列可以通过三种方式之一来绑定：（1）内表列可以通过相等谓词绑定到常量，（2）内表列可绑定到外表列，或（3） 可以将内表列绑定到相关值，其中相关值起始于查询块之外。 在所有三种情况下，内表列可以通过相等谓词的传递性来约束。

    Query optimization by transparently altering properties of relational tables using materialized views
    6.
    发明授权
    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.

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

    Relational database query optimization to perform query evaluation plan, pruning based on the partition properties
    7.
    发明授权
    Relational database query optimization to perform query evaluation plan, pruning based on the partition properties 失效
    关系数据库查询优化执行查询评估计划，根据分区属性进行修剪

    公开(公告)号：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中的复杂查询语句，无共享环境。

    Using data in materialized query tables as a source for query optimization statistics
    8.
    发明授权
    Using data in materialized query tables as a source for query optimization statistics 有权
    将物化查询表中的数据用作查询优化统计的来源

    公开(公告)号：US07783625B2

    公开(公告)日：2010-08-24

    申请号：US11256440

    申请日：2005-10-21

    申请人： David E. Simmen

    发明人： David E. Simmen

    IPC分类号： G06F7/00

    CPC分类号： G06F17/30463

    摘要： Data in materialized query tables (MQTs) are used as statistics for determining the optimal execution plan for a query. When an MQT is defined, it is examined to determine whether its data provides statistics for determining an optimal execution plan for a query. If so, then the MQT is identified, in the RDBMS, as a source for statistics. Information needed to exploit the MQT data as statistics is cataloged in the RDBMS. This information includes a characterization of the type of statistics provided by the MQT, the table and column distributions represented by those statistics, and a query for later retrieving relevant data from the MQT during the query optimization process. When a query is accepted for execution, the cataloged relevant information about MQTs is examined to determine whether an MQT exists that provides statistics relevant to optimization of the query. If such an MQT exists, then the relevant data is retrieved from the MQT using the cataloged query. Using the retrieved statistics, an optimal execution plan may be determined for the query.

    摘要翻译： 物化查询表（MQT）中的数据用作确定查询的最佳执行计划的统计信息。 当定义MQT时，将对其进行检查以确定其数据是否提供用于确定查询的最佳执行计划的统计信息。 如果是这样，则在RDBMS中将MQT标识为统计信息源。 在RDBMS中编制统计数据来利用MQT数据所需的信息。 该信息包括由MQT提供的统计信息类型的表征，由这些统计信息表示的表和列分布以及用于稍后在查询优化过程中从MQT检索相关数据的查询。 当查询被接受执行时，将检查有关MQT的编目相关信息，以确定是否存在提供与查询优化相关的统计信息的MQT。 如果存在这样的MQT，则使用编目查询从MQT检索相关数据。 使用检索的统计信息，可以为查询确定最佳执行计划。

    Query optimization method for incrementally estimating the cardinality of a derived relation when statistically correlated predicates are applied
    9.
    发明授权
    Query optimization method for incrementally estimating the cardinality of a derived relation when statistically correlated predicates are applied 失效
    当应用统计相关谓词时，查询优化方法用于递增地估计派生关系的基数

    公开(公告)号：US06738755B1

    公开(公告)日：2004-05-18

    申请号：US09516446

    申请日：2000-02-29

    申请人： Johann Christoph Freytag ,  Guy Maring Lohman ,  David E. Simmen ,  Calisto Paul Zuzarte

    发明人： Johann Christoph Freytag ,  Guy Maring Lohman ,  David E. Simmen ,  Calisto Paul Zuzarte

    IPC分类号： G06F1730

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

    摘要： A method, apparatus, and article of manufacture for incrementally estimating the cardinality of a derived relation when statistically correlated predicates are applied. A plurality of query execution plans (QEPs) are generated for the query. During the generation of the QEPs, a cardinality is computed for any of the QEPs in which two or more predicates are correlated to each other. The cardinality comprises a number of rows expected to be returned by the QEP and is computed in an incremental fashion for each operator of the QEP. The computations include calculations that may be done prior to the generation of the QEPs and calculations that are necessarily done as each operator of a QEP is added to that QEP. Thereafter, one of the QEPs is chosen to satisfy the query in a manner that minimizes an estimated cost metric, wherein the cost metric is computed using the cardinality.

    摘要翻译： 当应用统计相关谓词时，用于递增地估计派生关系的基数的方法，装置和制品。 为查询生成多个查询执行计划（QEP）。 在QEP的生成期间，对于其中两个或更多个谓词彼此相关联的任何QEP计算基数。 基数包括预期由QEP返回的多行，并且以递增的方式对QEP的每个运算符进行计算。 计算包括在生成QEP之前可以进行的计算以及当QEP的每个运算符被添加到该QEP时必须完成的计算。 此后，选择一个QEP以使估计成本度量最小化的方式满足查询，其中使用基数来计算成本度量。