公开(公告)号：US07366716B2

公开(公告)日：2008-04-29

申请号：US11124021

申请日：2005-05-06

申请人： Sanjay Agrawal ,  Vivek R. Narasayya ,  Beverly Yang

发明人： Sanjay Agrawal ,  Vivek R. Narasayya ,  Beverly Yang

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

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

摘要： Systems and methodologies that split a table into a plurality of sub-tables, and vertical partitions. By analyzing an associated work load to determine frequently referenced columns, the subject invention supplies a compromise among various vertical partitioning strategies (e.g., candidate selection for table spilt) via a merging act, such that the table is split optimally for the work load taken as a whole Accordingly, an incoming query can optimally reference only required columns.

摘要翻译： 将表拆分成多个子表和垂直分区的系统和方法。 通过分析相关联的工作负载以确定频繁引用的列，本发明通过合并动作在各种垂直分区策略（例如，用于表溢出的候选者选择）之间提供妥协，使得该表被最优地分解为所采用的工作负载 一个整体因此，传入的查询可以最佳地仅引用必需的列。

公开(公告)号：US06356890B1

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

申请号：US09629353

申请日：2000-08-01

申请人： Sanjay Agrawal ,  Surajit Chaudhuri ,  Vivek R. Narasayya

发明人： Sanjay Agrawal ,  Surajit Chaudhuri ,  Vivek R. Narasayya

IPC分类号： G06F1730

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

摘要： An index and materialized view selection wizard produces a fast and reasonable recommendation for a configuration of indexes, materialized views, and indexes on materialized views which are beneficial given a specified workload for a given database and database server. Candidate materialized views and indexes are obtained, and a joint enumeration of the combined materialized views and indexes is performed to obtain a recommended configuration. The configuration includes indexes, materialized views and indexes on materialized views. Candidate materialized views are obtained by first determining subsets of tables are referenced in queries in the workload and then finding interesting table subsets. Next, interesting subsets are considered on a per query basis to determine which are syntactically relevant for a query. Materialized views which are likely to be used for the workload are then generated along with a set of merged materialized views. Clustered indexes and non-clustered indexes on materialized views are then generated. The indexes, materialized views and indexes on materialized views are then enumerated together to form the recommended configuration.

摘要翻译： 索引和物化视图选择向导可以为物理视图的索引，物化视图和索引配置提供快速合理的建议，这对给定数据库和数据库服务器的指定工作负载是有益的。 获取候选物化视图和索引，并执行组合实例化视图和索引的联合枚举，以获得推荐的配置。 配置包括物化视图的索引，物化视图和索引。 通过首先确定表中的子集在工作负载中的查询中引用并且然后找到有趣的表子集来获得候选物化视图。 接下来，在每个查询的基础上考虑有趣的子集，以确定哪个在查询语法上相关。 可能用于工作负载的物化视图随同一组合并物化视图一起生成。 然后生成物化视图上的聚簇索引和非聚集索引。 然后将物化视图的索引，物化视图和索引列在一起以形成推荐的配置。

公开(公告)号：US07580941B2

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

申请号：US11423924

申请日：2006-06-13

申请人： Vivek R. Narasayya ,  Sanjay Agrawal ,  Anish Das Sarma

发明人： Vivek R. Narasayya ,  Sanjay Agrawal ,  Anish Das Sarma

IPC分类号： G06F17/30

CPC分类号： G06F17/30306 ,  Y10S707/99942

摘要： The subject disclosure pertains to logical schema tuning for databases. Mechanisms are provided for automatically recommending one or more logical schema transformations to improve database performance with respect to workload and data characteristics. Furthermore, several potential schema transformations including desirable properties are presented including mapping tables, vertical partitioning, frequent value elimination and type and attribute conversion. Still further yet, disclosed is a mechanism for rewriting database operations (e.g. queries, updates . . . ) for an optimized schema to ensure correct evaluation.

摘要翻译： 主题公开涉及数据库的逻辑模式调整。 提供了用于自动推荐一个或多个逻辑模式转换以提高关于工作负载和数据特性的数据库性能的机制。 此外，提出了包括所需属性的几种潜在的模式转换，包括映射表，垂直分区，频繁值消除以及类型和属性转换。 更进一步地，公开了一种用于重写用于优化模式的数据库操作（例如，查询，更新...）以确保正确评估的机制。

公开(公告)号：US06513029B1

公开(公告)日：2003-01-28

申请号：US09629010

申请日：2000-08-01

申请人： Sanjay Agrawal ,  Surajit Chaudhuri ,  Vivek R. Narasayya

发明人： Sanjay Agrawal ,  Surajit Chaudhuri ,  Vivek R. Narasayya

IPC分类号： G06F1730

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

摘要： An index and materialized view selection wizard produces a fast and reasonable recommendation for a configuration of indexes, materialized views, and indexes on materialized views which are beneficial given a specified workload for a given database and database server. Candidate materialized views and indexes are obtained, and a joint enumeration of the combined materialized views and indexes is performed to obtain a recommended configuration. The configuration includes indexes, materialized views and indexes on materialized views. Candidate materialized views are obtained by first determining subsets of tables are referenced in queries in the workload and then finding interesting table subsets. Next, interesting subsets are considered on a per query basis to determine which are syntactically relevant for a query. Materialized views which are likely to be used for the workload are then generated along with a set of merged materialized views. Clustered indexes and non-clustered indexes on materialized views are then generated. The indexes, materialized views and indexes on materialized views are then enumerated together to form the recommended configuration.

摘要翻译： 索引和物化视图选择向导可以为物理视图的索引，物化视图和索引配置提供快速合理的建议，这对给定数据库和数据库服务器的指定工作负载是有益的。 获取候选物化视图和索引，并执行组合实例化视图和索引的联合枚举，以获得推荐的配置。 配置包括物化视图的索引，物化视图和索引。 通过首先确定表中的子集在工作负载中的查询中引用并且然后找到有趣的表子集来获得候选物化视图。 接下来，在每个查询的基础上考虑有趣的子集，以确定哪个在查询语法上相关。 可能用于工作负载的物化视图随同一组合并物化视图一起生成。 然后生成物化视图上的聚簇索引和非聚集索引。 然后将物化视图的索引，物化视图和索引列在一起以形成推荐的配置。

公开(公告)号：US07664774B2

公开(公告)日：2010-02-16

申请号：US11345593

申请日：2006-02-01

申请人： Sanjay Agrawal ,  Lik Han Chu ,  Vivek R. Narasayya

发明人： Sanjay Agrawal ,  Lik Han Chu ,  Vivek R. Narasayya

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

CPC分类号： G06F17/30587 ,  Y10S707/99943

摘要： The subject disclosure pertains to exploitation of sequence information in a workload for the purpose of performance tuning. An optimal or exhaustive approach to tune sequences by mapping to a shortest path problem is provided as well as powerful techniques that result in optimal or nearly optimal solutions. Also disclosed is greedy approach that is much more efficient than the optimal approach and yet also generates solutions of comparable quality. Further yet, systems and methods are provided that facilitate extraction of sequence information and implementation of recommendations.

摘要翻译： 本发明涉及为了进行性能调整而在工作负载中利用序列信息。 提供了通过映射到最短路径问题来优化序列的最佳或穷尽方法，以及导致最佳或接近最优解的强大技术。 还披露了比最佳方法更有效的贪心方法，但也产生了可比较质量的解决方案。 此外，还提供了有助于提取序列信息和实施建议的系统和方法。

公开(公告)号：US06366903B1

公开(公告)日：2002-04-02

申请号：US09553033

申请日：2000-04-20

申请人： Sanjay Agrawal ,  Surajit Chaudhuri ,  Vivek R. Narasayya

发明人： Sanjay Agrawal ,  Surajit Chaudhuri ,  Vivek R. Narasayya

IPC分类号： G06F1730

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

摘要： An index and materialized view selection wizard produces a fast and reasonable recommendation of indexes and materialized views which are beneficial given a specified workload for a given database and database server. Candidate materialized views and indexes are obtained, and a joint enumeration of the combined materialized views and indexes is performed to obtain a recommended configuration. The configuration includes both indexes and materialized views. Candidate materialized views are obtained by first determining subsets of tables are referenced in queries in the workload and then finding interesting table subsets. Next, interesting subsets are considered on a per query basis to determine which are syntactically relevant for a query. Materialized views which are likely to be used for the workload are then generated along with a set of merged materialized views.

摘要翻译： 索引和物化视图选择向导可以快速合理地推荐索引和物化视图，这对给定数据库和数据库服务器的指定工作负载是有益的。 获取候选物化视图和索引，并执行组合实例化视图和索引的联合枚举，以获得推荐的配置。 配置包括索引和物化视图。 通过首先确定表中的子集在工作负载中的查询中引用并且然后找到有趣的表子集来获得候选物化视图。 接下来，在每个查询的基础上考虑有趣的子集，以确定哪个在查询语法上相关。 可能用于工作负载的物化视图随同一组合并物化视图一起生成。

公开(公告)号：US20070288495A1

公开(公告)日：2007-12-13

申请号：US11423924

申请日：2006-06-13

申请人： Vivek R. Narasayya ,  Sanjay Agrawal ,  Anish Das Sarma

发明人： Vivek R. Narasayya ,  Sanjay Agrawal ,  Anish Das Sarma

IPC分类号： G06F7/00

CPC分类号： G06F17/30306 ,  Y10S707/99942

摘要： The subject disclosure pertains to logical schema tuning for databases. Mechanisms are provided for automatically recommending one or more logical schema transformations to improve database performance with respect to workload and data characteristics. Furthermore, several potential schema transformations including desirable properties are presented including mapping tables, vertical partitioning, frequent value elimination and type and attribute conversion. Still further yet, disclosed is a mechanism for rewriting database operations (e.g. queries, updates . . . ) for an optimized schema to ensure correct evaluation.

摘要翻译： 主题公开涉及数据库的逻辑模式调整。 提供了用于自动推荐一个或多个逻辑模式转换以提高关于工作负载和数据特性的数据库性能的机制。 此外，提出了包括所需属性的几种潜在的模式转换，包括映射表，垂直分区，频繁值消除以及类型和属性转换。 更进一步地，公开了一种用于重写用于优化模式的数据库操作（例如，查询，更新...）以确保正确评估的机制。

公开(公告)号：US06356891B1

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

申请号：US09629412

申请日：2000-08-01

申请人： Sanjay Agrawal ,  Surajit Chaudhuri ,  Vivek R. Narasayya

发明人： Sanjay Agrawal ,  Surajit Chaudhuri ,  Vivek R. Narasayya

IPC分类号： G06F1730

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

摘要： An index and materialized view selection wizard produces a fast and reasonable recommendation for a configuration of indexes, materialized views, and indexes on materialized views which are beneficial given a specified workload for a given database and database server. Candidate materialized views and indexes are obtained, and a joint enumeration of the combined materialized views and indexes is performed to obtain a recommended configuration. The configuration includes indexes, materialized views and indexes on materialized views. Candidate materialized views are obtained by first determining subsets of tables are referenced in queries in the workload and then finding interesting table subsets. Next, interesting subsets are considered on a per query basis to determine which are syntactically relevant for a query. Materialized views which are likely to be used for the workload are then generated along with a set of merged materialized views. Clustered indexes and non-clustered indexes on materialized views are then generated. The indexes, materialized views and indexes on materialized views are then enumerated together to form the recommended configuration.

摘要翻译： 索引和物化视图选择向导可以为物理视图的索引，物化视图和索引配置提供快速合理的建议，这对给定数据库和数据库服务器的指定工作负载是有益的。 获取候选物化视图和索引，并执行组合实例化视图和索引的联合枚举，以获得推荐的配置。 配置包括物化视图的索引，物化视图和索引。 通过首先确定表中的子集在工作负载中的查询中引用并且然后找到有趣的表子集来获得候选物化视图。 接下来，在每个查询的基础上考虑有趣的子集，以确定哪个在查询语法上相关。 可能用于工作负载的物化视图随同一组合并物化视图一起生成。 然后生成物化视图上的聚簇索引和非聚集索引。 然后将物化视图的索引，物化视图和索引列在一起以形成推荐的配置。

公开(公告)号：US06266658B1

公开(公告)日：2001-07-24

申请号：US09553070

申请日：2000-04-20

申请人： Atul Adya ,  Sanjay Agrawal ,  Surajit Chaudhuri ,  Vivek R. Narasayya

发明人： Atul Adya ,  Sanjay Agrawal ,  Surajit Chaudhuri ,  Vivek R. Narasayya

IPC分类号： G06F1730

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

摘要： An index tuning wizard produces a fast and reasonable recommendation identifying database indexes to use given a specified workload. A query optimizer is used to determine the expected usefulness of potential indexes for the specified workload by taking cost of queries in the workload into account. A cost based pruning of indexes is then performed to provide an intermediate set of proposed indexes. Indexes having most benefit based on storage constraints are then selected. The optimizer is then used again, and further pruning is done on a benefits basis. An index is not recommended unless it has a significant impact on the workload.

摘要翻译： 索引调整向导会产生一个快速合理的建议，用于标识在指定工作负载下使用的数据库索引。 查询优化器用于通过考虑工作负载中的查询成本来确定指定工作负载的潜在索引的预期有用性。 然后执行索引的基于成本的修剪以提供提出的索引的中间集合。 然后选择基于存储约束最有利的索引。 然后，优化器再次被使用，并且进一步修剪是在有益的基础上进行的。 不建议使用索引，除非它对工作负载有重大影响。

公开(公告)号：US20060253473A1

公开(公告)日：2006-11-09

申请号：US11124021

申请日：2005-05-06

申请人： Sanjay Agrawal ,  Vivek Narasayya ,  Beverly Yang

发明人： Sanjay Agrawal ,  Vivek Narasayya ,  Beverly Yang

IPC分类号： G06F7/00

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

摘要： Systems and methodologies that split a table into a plurality of sub-tables, and vertical partitions. By analyzing an associated work load to determine frequently referenced columns, the subject invention supplies a compromise among various vertical partitioning strategies (e.g., candidate selection for table spilt) via a merging act, such that the table is split optimally for the work load taken as a whole Accordingly, an incoming query can optimally reference only required columns.

摘要翻译： 将表拆分成多个子表和垂直分区的系统和方法。 通过分析相关联的工作负载以确定频繁引用的列，本发明通过合并动作在各种垂直分区策略（例如，用于表溢出的候选者选择）之间提供妥协，使得该表被最优地分解为所采用的工作负载 一个整体因此，传入的查询可以最佳地仅引用必需的列。