公开(公告)号：US06477525B1

公开(公告)日：2002-11-05

申请号：US09221363

申请日：1998-12-28

申请人： Randall Bello ,  James Finnerty ,  Mohamed Ziauddin ,  Andrew Witkowski

发明人： Randall Bello ,  James Finnerty ,  Mohamed Ziauddin ,  Andrew Witkowski

IPC分类号： G06F1700

CPC分类号： G06F17/30457 ,  G06F17/30489 ,  Y10S707/99933 ,  Y10S707/99934

摘要： A method and system are provided for processing queries. According to one aspect of the invention, a query that does not reference a particular materialized view is rewritten to reference the materialized view. In particular, upon receiving the query, it is determined whether the particular materialized view satisfies each condition in a set of conditions, where the set of conditions at least includes a condition that the materialized view reflects all rows that exist in a common section. The common section is a section of the query that is common to both the materialized view and the query. If the materialized view satisfies each condition in the set of conditions, then the query is rewritten to produce a rewritten query that references the materialized view. The materialized view may be a summary table that includes a summary column. The summary column contains values generated by aggregating values contained in rows produced by a one-to-many lossless join. The one-to-many lossless join is not in the common section. The query includes a cumulative aggregate function. Under these conditions, the method includes generating results of the cumulative aggregate function in the query by dividing values from the summary column by scaling factors.

摘要翻译： 提供了一种用于处理查询的方法和系统。 根据本发明的一个方面，重写未参考特定实例化视图的查询以引用实例化视图。 具体地，在接收到查询后，确定特定实体化视图是否满足条件集合中的每个条件，其中条件集至少包括物化视图反映存在于公共部分中的所有行的条件。 公共部分是查询的一部分，它与物化视图和查询都是共同的。 如果物化视图满足条件集中的每个条件，则重写查询以产生引用物化视图的重写查询。 实例化视图可以是包含摘要列的摘要表。 摘要列包含通过聚合由一对多无损连接生成的行中包含的值生成的值。 一对多无损连接不在共同的部分。 该查询包括累积聚合函数。 在这些条件下，该方法包括通过将摘要列中的值除以缩放因子来生成查询中的累积聚合函数的结果。

公开(公告)号：US06029163A

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

申请号：US164400

申请日：1998-09-30

申请人： Mohamed Ziauddin

发明人： Mohamed Ziauddin

IPC分类号： G06F17/30

CPC分类号： G06F17/30595 ,  G06F17/30463 ,  G06F17/30536 ,  Y10S707/99932 ,  Y10S707/99933

摘要： Methods for collecting query workload based statistics within a relational database management system (RDBMS) and for identifying columns for which statistics collection is to be performed. The novel system collects workload statistics that are dependent on multiple columns, rather than merely single columns. Multi-column statistic generation provides more accurate results for columns having correlated data, and therefore leads to better estimated cost analysis by an RDBMS optimizer. In one embodiment, a column duplicity factor is based on an analysis of distinct data rows, e.g., combinations of values within multiple columns, rather than rows of single columns. The novel system also collects separate statistics regarding the presence of null data within the rows of a column group. Separate null data statistics improve the determined result carnality used by the RDBMS optimizer because the cardinality of a relational operation's result is generally determined by the number of input rows with non-null data. The novel system includes an RDBMS optimizer that automatically identifies column groups and column groups on which workload statistics are to be generated. The parameters within a query (e.g., equi-joins, equi-selections, and projections) are analyzed by the optimizer to automatically identify the column groups. The identified columns are then registered within in a system catalog. The registered column groups are read by statistics generation procedures to identify those column groups for which workload statistics are to be collected.

摘要翻译： 在关系数据库管理系统（RDBMS）中收集基于查询工作负载的统计信息的方法，以及用于标识要执行统计信息收集的列。 小说系统收集依赖于多个列的工作负载统计信息，而不是单个列。 多列统计生成为具有相关数据的列提供更准确的结果，因此导致RDBMS优化器的更好的估计成本分析。 在一个实施例中，列重复因子基于对不同数据行的分析，例如多列中的值的组合，而不是单列的行的组合。 该小说系统还收集关于列组中的行内的空数据的存在的单独统计。 单独的空数据统计可以提高RDBMS优化器使用的确定结果的正确性，因为关系运算结果的基数通常由具有非空数据的输入行的数量决定。 该小说系统包括一个RDBMS优化器，可自动标识要在其上生成工作负载统计信息的列组和列组。 优化器分析查询中的参数（例如，等连接，等效选择和投影）以自动识别列组。 然后将标识的列登记在系统目录中。 通过统计生成过程读取注册的列组，以识别要收集工作负载统计信息的列组。

公开(公告)号：US07213012B2

公开(公告)日：2007-05-01

申请号：US10435228

申请日：2003-05-09

申请人： Hakan Jakobsson ,  William McKenna ,  Mohamed Ziauddin

发明人： Hakan Jakobsson ,  William McKenna ,  Mohamed Ziauddin

IPC分类号： G06F17/30

CPC分类号： G06F17/30463 ,  G06F17/30474 ,  G06F17/30536 ,  Y10S707/99932 ,  Y10S707/99933

摘要： Described herein are approaches to implementing dynamic sampling in a way that lessens or eliminates the additional overhead incurred to perform dynamic sampling. Also described are techniques for determining characteristics about predicates not previously determined by conventional techniques for dynamic sampling. Dynamic sampling is used by a query optimizer to dynamically estimate predicate selectivities and statistics. When a database statement is received by a database server, an initial analysis of the database statement is made to determine the efficacy of dynamic sampling, that is, to determine whether optimization of the query would benefit from dynamic sampling and whether performance is not excessively impacted by the dynamic sampling process. If this analysis determines dynamic sampling should be used, then dynamic sampling is undertaken.

摘要翻译： 这里描述的是以减少或消除进行动态采样所引起的额外开销的方式实现动态采样的方法。 还描述了用于确定先前未被动态采样的常规技术确定的谓词的特性的技术。 动态抽样由查询优化器用于动态估计谓词选择性和统计量。 当数据库服务器接收到数据库语句时，将对数据库语句进行初始分析，以确定动态抽样的有效性，即确定查询的优化是否将受益于动态抽样以及性能是否受到过度影响 通过动态采样过程。 如果该分析确定应采用动态抽样，则进行动态抽样。

公开(公告)号：US20050187917A1

公开(公告)日：2005-08-25

申请号：US10936469

申请日：2004-09-07

申请人： Shilpa Lawande ,  Mohamed Ziauddin ,  Gregory Smith

发明人： Shilpa Lawande ,  Mohamed Ziauddin ,  Gregory Smith

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

CPC分类号： G06F17/30474 ,  G06F17/30306 ,  Y10S707/99932 ,  Y10S707/99934 ,  Y10S707/99944

摘要： An access structure analysis method is interspersed with the query optimization process. The method can determine the ideal combination of access structures, including both materialized views and indexes, for a given database workload. The interspersed structure analysis method can include advanced transformations like view merging, star transformation, bitmap access plans, query rewrite using materialized views, for example. The method may be performed using the query optimizer's rules as heuristics to guide the index candidate generation process.

摘要翻译： 访问结构分析方法散布着查询优化过程。 该方法可以确定给定数据库工作负载的访问结构（包括物化视图和索引）的理想组合。 散布的结构分析方法可以包括例如视图合并，星型转换，位图访问计划，使用物化视图的查询重写等高级转换。 该方法可以使用查询优化器的规则作为启发式来引导索引候选者生成过程。

公开(公告)号：US06493708B1

公开(公告)日：2002-12-10

申请号：US09517361

申请日：2000-03-02

申请人： Mohamed Ziauddin ,  Andrew Witkowski

发明人： Mohamed Ziauddin ,  Andrew Witkowski

IPC分类号： G06F1730

CPC分类号： G06F17/30383 ,  Y10S707/99933

摘要： Techniques are described which allow function-defined hierarchies to be registered with a database server. The information provided to the server during the registration process is used by the server to determine how to roll up data that has been aggregated at one level of a function-defined hierarchy to another level of the function-defined hierarchy. Techniques are also provided to perform rollup from one level of a function-defined hierarchy to another level of the function-defined hierarchy on data stored in a materialized view. Further, techniques are provided for rewriting queries that require aggregation at one level of a function-defined hierarchy to cause them to access data from a materialized view that stores data at a different level of the function-defined hierarchy.

摘要翻译： 描述了允许将功能定义的层次结构注册到数据库服务器的技术。 在注册过程中提供给服务器的信息由服务器使用，以确定如何将已经在功能定义层次结构的一个级别聚合的数据汇总到功能定义层次结构的另一个级别。 还提供技术来从存储在物化视图中的数据执行从功能定义层级的一个级别到功能定义层次结构的另一级别的汇总。 此外，提供了用于重写需要在功能定义层次结构的一个级别进行聚合的查询的技术，以使得它们从存储在功能定义层次结构的不同级别的数据的物化视图访问数据。

公开(公告)号：US08825629B2

公开(公告)日：2014-09-02

申请号：US10936469

申请日：2004-09-07

申请人： Shilpa Lawande ,  Mohamed Ziauddin ,  Gregory Scott Smith

发明人： Shilpa Lawande ,  Mohamed Ziauddin ,  Gregory Scott Smith

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

CPC分类号： G06F17/30474 ,  G06F17/30306 ,  Y10S707/99932 ,  Y10S707/99934 ,  Y10S707/99944

摘要： An access structure analysis method is interspersed with the query optimization process. The method can determine the ideal combination of access structures, including both materialized views and indexes, for a given database workload. The interspersed structure analysis method can include advanced transformations like view merging, star transformation, bitmap access plans, query rewrite using materialized views, for example. The method may be performed using the query optimizer's rules as heuristics to guide the index candidate generation process.

摘要翻译： 访问结构分析方法散布着查询优化过程。 该方法可以确定给定数据库工作负载的访问结构（包括物化视图和索引）的理想组合。 散布的结构分析方法可以包括例如视图合并，星型转换，位图访问计划，使用物化视图的查询重写等高级转换。 该方法可以使用查询优化器的规则作为启发式来引导索引候选者生成过程。

公开(公告)号：US20110137875A1

公开(公告)日：2011-06-09

申请号：US12634641

申请日：2009-12-09

申请人： Mohamed Ziauddin ,  Andrew Witkowski

发明人： Mohamed Ziauddin ,  Andrew Witkowski

IPC分类号： G06F17/00

CPC分类号： G06F16/2393

摘要： An incremental refresh of a materialized view may be simplified, and therefore made more cost efficient, by reducing the number of DML operations being merged with the materialized view during the incremental refresh. Specifically, subsequences of sequences of data manipulation language operations that have been recorded for a particular row of a base table may be inspected to determine whether the subsequences conform to particular patterns of data manipulation language operator types. If a subsequence conforms to one of the particular patterns, the subsequence may be replaced with a single substitute: either a single data manipulation language operation, or null. Refresh operations that are generated based on the simplified sequences of data manipulation language operations are more simple, and therefore, less costly to perform.

摘要翻译： 通过减少在增量刷新期间与物化视图合并的DML操作的数量，可以简化实例化视图的增量刷新，从而实现更具成本效益。 具体地，可以检查已经为基表的特定行记录的数据操作语言操作的序列的子序列，以确定子序列是否符合数据操作语言操作符类型的特定模式。 如果子序列符合特定模式之一，则子序列可以替换为单个替代：单个数据操作语言操作或空值。 基于简化的数据操作语言操作序列生成的刷新操作更简单，因此执行成本较低。

公开(公告)号：US20090106306A1

公开(公告)日：2009-04-23

申请号：US12188678

申请日：2008-08-08

申请人： Dinesh Das ,  Cetin Ozbutun ,  Hong Su ,  Mohamed Zait ,  Yali Zhu ,  Mohamed Ziauddin ,  Peter Belknap ,  Khaled Yagoub

发明人： Dinesh Das ,  Cetin Ozbutun ,  Hong Su ,  Mohamed Zait ,  Yali Zhu ,  Mohamed Ziauddin ,  Peter Belknap ,  Khaled Yagoub

IPC分类号： G06F17/30

CPC分类号： G06F17/30469 ,  G06F17/30463

摘要： Approaches, techniques, and mechanisms are disclosed for maintaining a set of baseline query plans for a database command. Except in rare circumstances, a database server may only execute a command according to a baseline plan, even if the database server predicts that a different plan has a lower cost. The set of baseline plans are plans that, for one reason or another, have been determined to provide acceptable actual performance in at least one execution context. When the database server receives a request to execute a particular command, the database server, if possible, always executes the command according to the lowest predicted cost baseline plan. The database server may evolve the plan baseline to include additional plans by generating and testing new plans in response to new requests to execute the database command, or as part of a query optimization or tuning process.

摘要翻译： 披露了用于维护数据库命令的一组基线查询计划的方法，技术和机制。 除极少数情况下，数据库服务器只能根据基准线执行命令，即使数据库服务器预测不同的计划成本较低。 一组基准计划是由于某种原因决定在至少一个执行环境中提供可接受的实际绩效的计划。 当数据库服务器接收到执行特定命令的请求时，如果可能，数据库服务器总是根据最低预测成本基线计划执行该命令。 数据库服务器可以通过生成和测试新计划以响应执行数据库命令的新请求，或作为查询优化或调整过程的一部分，来演进计划基准以包括额外的计划。

公开(公告)号：US07233939B1

公开(公告)日：2007-06-19

申请号：US10137235

申请日：2002-04-30

申请人： Mohamed Ziauddin

发明人： Mohamed Ziauddin

IPC分类号： G06F17/30

CPC分类号： G06F17/30442 ,  Y10S707/99932 ,  Y10S707/99933

摘要： Queries are optimized according to a first optimization mode by generating execution plans and selecting the lowest cost plan. Inputs optimized according to the first optimization mode, to database operations with input parameters that are inconsistent with the first optimization mode, are replaced with equivalent inputs optimized according to a second optimization mode, the second optimization mode being consistent with the input parameter. Blocking operations are eliminated from queries using a cost-based approach.

摘要翻译： 通过生成执行计划并选择最低成本计划，根据第一优化模式优化查询。 根据第一优化模式优化的输入到具有与第一优化模式不一致的输入参数的数据库操作被替换为根据第二优化模式优化的等效输入，第二优化模式与输入参数一致。 使用基于成本的方法从查询中消除了阻塞操作。

公开(公告)号：US06334128B1

公开(公告)日：2001-12-25

申请号：US09224063

申请日：1998-12-28

申请人： William D. Norcott ,  Mohamed Ziauddin

发明人： William D. Norcott ,  Mohamed Ziauddin

IPC分类号： G06F700

CPC分类号： G06F17/30383 ,  Y10S707/99935 ,  Y10S707/99938 ,  Y10S707/99943

摘要： A method and apparatus for refreshing stale materialized views is provided. Prior to executing a query to refresh a materialized view from data in the base tables of the materialized view, the query is rewritten to refresh the materialized view from data in one or more other materialized views. To take advantage of the efficiency gained by refreshing a materialized view based on another materialized view, a refresh sequence is established based on the dependencies between materialized views in the database system. The dependencies indicate which materialized views can be refreshed from which other materialized views. When a materialized view can be refreshed based on any one of a number of eligible materialized views, the refresh sequence may additionally take into account the relative benefit associated with refreshing the materialized view with each of the eligible materialized views.

摘要翻译： 提供了一种用于刷新陈旧的物化视图的方法和装置。 在执行查询以从物化视图的基表中的数据中刷新物化视图之前，将重写该查询以从一个或多个其他物化视图中的数据刷新物化视图。 为了利用基于另一个物化视图刷新物化视图获得的效率，基于数据库系统中物化视图之间的依赖关系建立刷新序列。 依赖关系表明哪些物化视图可以从其他物化视图中刷新。 当物质化视图可以基于多个合格物化视图中的任何一个刷新时，刷新序列可另外考虑与每个合格实体化视图刷新物化视图相关联的相关利益。