公开(公告)号：US20070073761A1

公开(公告)日：2007-03-29

申请号：US11239617

申请日：2005-09-29

申请人： Mark Anderson ,  Robert Bestgen ,  James Flanagan ,  Scott Forstie ,  Thomas Schreiber

发明人： Mark Anderson ,  Robert Bestgen ,  James Flanagan ,  Scott Forstie ,  Thomas Schreiber

IPC分类号： G06F7/00

CPC分类号： G06F16/217 ,  G06F16/22

摘要： Continual generation of index advice that includes generating an index advice for an index of a table in a computer database and recording values of statistical attributes of the index advice accumulated across repeated generations of the index advice. The generating and recording typically are carried out continually without user intervention. Embodiments include recording values of attributes of the index advice that specify an index. Typical embodiments also include, continually and without user intervention, recording values of attributes of the index advice that characterize usefulness of an index and recording values of attributes of the index advice that characterize cost of creating an index.

摘要翻译： 持续生成索引建议，包括为计算机数据库中的表的索引生成索引建议，并记录索引建议的重复次数累积的索引建议的统计属性的值。 生成和记录通常在没有用户干预的情况下持续进行。 实施例包括记录指定索引的索引建议的属性的值。 典型实施例还包括持续地且没有用户干预的记录索引建议的属性的值，其表征索引的有用性，并记录表征创建索引的成本的索引建议的属性的值。

公开(公告)号：US20070192372A1

公开(公告)日：2007-08-16

申请号：US11353515

申请日：2006-02-14

申请人： Robert Bestgen ,  James Flanagan ,  Michale Pfeifer

发明人： Robert Bestgen ,  James Flanagan ,  Michale Pfeifer

IPC分类号： G06F17/30

CPC分类号： G06F17/30289 ,  G06F17/30386

摘要： A database management system maintains a record of queries which can be executed against the database. For a given auxiliary database structure, an advisory function automatically reviews the query record and identifies those queries which might execute with greater efficiency using the auxiliary database structure, presenting analytical information to the user. Preferably, the auxiliary database structure is an index, and the information is used for determining whether to include the index in the database definition. Preferably, a query optimizer records potentially useful indexes when considering multiple alternative database optimizations, and this information is later retrieved to identify queries which would benefit from an index.

摘要翻译： 数据库管理系统维护可以针对数据库执行的查询记录。 对于给定的辅助数据库结构，咨询功能自动审查查询记录，并使用辅助数据库结构识别可能以更高效率执行的查询，向用户呈现分析信息。 优选地，辅助数据库结构是索引，并且该信息用于确定是否将数据包括在数据库定义中。 优选地，当考虑多个备选数据库优化时，查询优化器记录潜在有用的索引，并且稍后检索该信息以识别将从索引中受益的查询。

公开(公告)号：US20060271504A1

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

申请号：US11138832

申请日：2005-05-26

申请人： Mark Anderson ,  Robert Bestgen ,  Shantan Kethireddy ,  Jeffrey Tenner

发明人： Mark Anderson ,  Robert Bestgen ,  Shantan Kethireddy ,  Jeffrey Tenner

IPC分类号： G06F17/30

CPC分类号： G06F17/30312

摘要： A method, apparatus, system, and signal-bearing medium that, in an embodiment, determine performance data for query optimization of a query directed to a database table having multiple partitions. The performance data for the query optimization is presented in a user interface, and an option is provided in the user interface to create a non-spanning index for any of the partitions mentioned in the performance data. In various embodiments, determining the performance data may include: determining the partitions that were unexpanded by the query optimization, determining the partitions that had a runtime estimate that was different from an actual query runtime by a threshold, determining whether any of the partitions had expansion forced by a client request, determining whether the query performed partition elimination, determining whether any of the partitions has an associated index that is unused by the query and that also has a key that is not present in all of the partitions, and determining a constraint for partition elimination.

摘要翻译： 一种方法，装置，系统和信号承载介质，其在一个实施例中确定用于针对具有多个分区的数据库表的查询的查询优化的性能数据。 查询优化的性能数据在用户界面中呈现，并且在用户界面中提供了一个选项，以便为演奏数据中提到的任何分区创建非跨度索引。 在各种实施例中，确定性能数据可以包括：确定由查询优化未展开的分区，将具有与实际查询运行时间不同的运行时间估计的分区确定为阈值，确定是否有任何分区具有扩展 通过客户端请求强制，确定查询是否执行分区消除，确定任何分区是否具有由查询未使用的关联索引，并且还具有不存在于所有分区中的密钥，以及确定约束 用于分区消除。

公开(公告)号：US20060173852A1

公开(公告)日：2006-08-03

申请号：US11047533

申请日：2005-01-31

申请人： Robert Bestgen ,  Shantan Kethireddy

发明人： Robert Bestgen ,  Shantan Kethireddy

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

CPC分类号： G06F16/20

摘要： A visual query explain mechanism displays a query to the user in a graphical tree format. A user may execute a query, and the actual performance from executing the query is imported into the visual query explain mechanism. The visual query explain mechanism adds the actual performance to the nodes in the query tree, and compares the actual performance to the estimates of performance that were generated prior to executing the query. The visual query explain mechanism then looks at a predefined threshold value, and determines whether actual performance exceeds the estimated performance by the predefined threshold value. If so, the corresponding node in the query graph is highlighted in some way, thereby providing a visual indication to the user of problem areas in the query.

摘要翻译： 视觉查询解释机制以图形树形式向用户显示查询。 用户可以执行查询，并将执行查询的实际性能导入到视觉查询解释机制中。 视觉查询解释机制将实际性能添加到查询树中的节点，并将实际性能与执行查询之前生成的性能估计进行比较。 视觉查询解释机制然后查看预定义的阈值，并且确定实际性能是否超过预定阈值的估计性能。 如果是这样，查询图中的对应节点以某种方式突出显示，从而向用户提供查询中的问题区域的可视指示。

公开(公告)号：US20070016558A1

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

申请号：US11181713

申请日：2005-07-14

申请人： Robert Bestgen ,  Shantan Kethireddy ,  Jeffrey Tenner

发明人： Robert Bestgen ,  Shantan Kethireddy ,  Jeffrey Tenner

IPC分类号： G06F17/30

CPC分类号： G06F17/3053 ,  G06F17/30545

摘要： A query facility for database queries dynamically determines whether selective portions of a database table are likely to benefit from separate query execution strategies, and constructs an appropriate separate execution strategies accordingly. Preferably, the database contains at least one relatively large table comprising multiple partitions, each sharing the definitional structure of the table and containing a different respective discrete subset of the table records. The query facility compares metadata for different partitions to determine whether sufficiently large differences exist among the partitions, and in appropriate cases selects one or more partitions for separate execution strategies. Preferably, partitions are ranked for separate evaluation using a weighting formula which takes into account: (a) the number of indexes for the partition, (b) recency of change activity, and (c) the size of the partition.

摘要翻译： 用于数据库查询的查询工具动态地确定数据库表的选择性部分是否可能受益于单独的查询执行策略，并相应地构建适当的单独的执行策略。 优选地，数据库包含至少一个包括多个分区的相对较大的表，每个分区共享表的定义结构，并且包含表记录的不同相应离散子集。 查询设施比较不同分区的元数据，以确定分区之间是否存在足够大的差异，并且在适当的情况下选择一个或多个分区用于单独的执行策略。 优选地，使用考虑到的加权公式对分区进行分级评估：（a）分区的索引的数量，（b）更改活动的新近度，以及（c）分区的大小。

公开(公告)号：US20060224561A1

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

申请号：US11094655

申请日：2005-03-30

申请人： Robert Bestgen ,  Carol Ramler ,  Jeffrey Tenner

发明人： Robert Bestgen ,  Carol Ramler ,  Jeffrey Tenner

IPC分类号： G06F17/30

CPC分类号： G06F17/30457 ,  Y10S707/99932 ,  Y10S707/99933 ,  Y10S707/99943

摘要： A query facility for database queries saves and re-uses query execution strategies, and automatically determines whether a strategy can be re-used when an imported variable changes. The query facility automatically saves one or more logical conditions associated with the one or more imported variables in the query. When the query is reused, the logical conditions are evaluated using the imported variable values, and the previously saved execution strategy is used only if the condition or conditions are met. Preferably, the logical conditions express a range of imported variable values which are included in a database subset data structure used by the saved query execution strategy, such as a materialized query table or partitioned table. Logical conditions are preferably generated by negating the predicates from a logical expression defining the applicability of the execution strategy, inserting the negated predicates into the query, and performing a transitive closure.

摘要翻译： 数据库查询的查询功能可以保存并重新使用查询执行策略，并自动确定在导入的变量更改时是否可以重新使用策略。 查询设备自动保存与查询中的一个或多个导入的变量相关联的一个或多个逻辑条件。 当重新使用查询时，使用导入的变量值来评估逻辑条件，并且仅当满足条件或条件时才使用先前保存的执行策略。 优选地，逻辑条件表示包括在由保存的查询执行策略（例如物化查询表或分区表）使用的数据库子集数据结构中的导入变量值的范围。 逻辑条件优选地通过从定义执行策略的适用性，将否定的谓词插入到查询中并且执行传递闭包的逻辑表达式来取消谓词来产生。

公开(公告)号：US20050160102A1

公开(公告)日：2005-07-21

申请号：US10758485

申请日：2004-01-15

申请人： Abdo Abdo ,  Robert Bestgen ,  John Dietel

发明人： Abdo Abdo ,  Robert Bestgen ,  John Dietel

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

CPC分类号： G06F16/2456

摘要： A relational database system analyzes each potential join in a query, to determine whether a relation involved in the join is subject to a selection criterion, and evaluate whether that selection criterion or the join per se effects a join reduction. The computational expense of generating a look-ahead predicate comprising the tuples of the second relation matching any applicable selection criterion, is compared to the computational savings that result from the join reduction. The most beneficial look-ahead predicate among all potential joins of relations in the query is identified through iterative analysis of all possible joins. Thereafter, membership in the look-ahead predicate is added as a selection criterion on the first relation, and further iterative analysis is performed of all possible joins of the remaining relations and the look-ahead predicate, to iteratively identify additional joins in the query that benefit from the formation of the look-ahead predicate, and potentially form further look-ahead predicates.

摘要翻译： 关系数据库系统分析查询中的每个潜在连接，以确定连接中涉及的关系是否受制于选择标准，并评估该选择标准或连接本身是否影响连接缩减。 将包括与任何适用的选择标准相匹配的第二关系的元组的生成预先谓词的计算费用与由连接减少产生的计算节省进行比较。 通过对所有可能的连接进行迭代分析，可以确定查询中关系中所有潜在联系中最有利的预言谓词。 此后，将先行谓词的成员资格作为关于第一关系的选择标准被添加，并且对剩余关系和先行谓词的所有可能连接执行进一步的迭代分析，以迭代地识别查询中的附加联接 受益于先行谓词的形成，并可能形成进一步的预言谓词。

公开(公告)号：US20070250517A1

公开(公告)日：2007-10-25

申请号：US11379503

申请日：2006-04-20

申请人： Robert Bestgen ,  Curtis Boger ,  David Carlson ,  Robert Downer ,  Steven Hoeschen ,  Kevin Kathmann ,  Shantan Kethireddy

发明人： Robert Bestgen ,  Curtis Boger ,  David Carlson ,  Robert Downer ,  Steven Hoeschen ,  Kevin Kathmann ,  Shantan Kethireddy

IPC分类号： G06F7/00

CPC分类号： G06F16/24539 ,  G06F16/22 ,  G06F16/2454 ,  G06F16/24561

摘要： A latent auxiliary database structure constructed for executing a query is automatically saved for reuse in subsequent query execution instances. The latent auxiliary database structure is not maintained concurrently with changes to records in the database tables. If a query execution strategy is subsequently invoked which uses the latent auxiliary database structure, a determination is made dynamically whether to update the previously saved latent structure from database changes recorded in one or more change logs, or to rebuild the latent structure from scratch. Where the cost of updating from the change log is deemed less than the cost of a rebuild from scratch, the previously saved latent structure is updated from the log.

摘要翻译： 构建用于执行查询的潜在辅助数据库结构将自动保存，以便在后续查询执行实例中重用。 潜在辅助数据库结构与数据库表中记录的更改不同时维护。 如果随后调用使用潜在辅助数据库结构的查询执行策略，则动态确定是否从先前保存的潜在结构更新记录在一个或多个更改日志中的数据库更改，或从头重新构建潜在结构。 在从更改日志更新成本被认为小于从头开始重建的成本的情况下，先前保存的潜在结构将从日志中更新。

公开(公告)号：US20070226176A1

公开(公告)日：2007-09-27

申请号：US11388004

申请日：2006-03-23

申请人： Robert Bestgen ,  Robert Downer ,  Shantan Kethireddy

发明人： Robert Bestgen ,  Robert Downer ,  Shantan Kethireddy

IPC分类号： G06F17/30

CPC分类号： G06F16/2246

摘要： A query optimizer optimizes a query to a partitioned database table by determining common characteristics of the partitions and generating a virtual maintained temporary index that spans multiple partitions. Using the virtual maintained temporary index allows the query optimizer to generate an access plan based on the virtual maintained temporary index, which relieves the optimizer from having to individually optimize access to each partition for partitions that share common characteristics.

摘要翻译： 查询优化器通过确定分区的共同特征并生成跨多个分区的虚拟维护临时索引来优化对分区数据库表的查询。 使用虚拟维护的临时索引允许查询优化器基于虚拟维护的临时索引生成访问计划，这减轻了优化器必须单独优化对共享共同特征的分区的每个分区的访问。

公开(公告)号：US20060095406A1

公开(公告)日：2006-05-04

申请号：US10977801

申请日：2004-10-29

申请人： Robert Bestgen ,  Shantan Kethireddy ,  Michael Pfeifer

发明人： Robert Bestgen ,  Shantan Kethireddy ,  Michael Pfeifer

IPC分类号： G06F17/30

CPC分类号： G06F16/217 ,  G06F16/24547

摘要： Enabling a user to obtain and view graphic explain data with no need to activate a monitor and no need to wait for a job to complete before the explain data for a query may be viewed. Displaying explain data for an SQL query of a database that include executing an SQL query and displaying explain data for the SQL query while executing the SQL query. Displaying explain data includes identifying an access plan associated with the SQL query and generating explain data in dependence upon the access plan. Identifying an access plan may be carried out by retrieving an access plan identification from a cursor of a job control block and retrieving an access plan from an access plan cache in dependence upon the access plan identification.

摘要翻译： 允许用户获取和查看图形说明数据，无需激活监视器，无需等待作业完成，才能查看查询的说明数据。 显示数据库的SQL查询的解释数据，其中包括执行SQL查询并在执行SQL查询时显示SQL查询的说明数据。 显示说明数据包括识别与SQL查询相关联的访问计划，并根据访问计划生成说明数据。 识别访问计划可以通过从作业控制块的光标检索访问计划标识并且根据访问计划标识从访问计划高速缓存检索访问计划来执行访问计划。