International Business Machines Corporation

Ting Yu Leung

Mir Hamid Pirahesh

David Everett Simmen

Roberta Jo Cochrane

Tuong Chanh Truong

Monica Sachiye Urata

Daniel Costante Zilio

Calisto Paul Zuzarte

George Lapis

Guy Maring Lohman

Yang Sun

Fred Koo

Markos Zaharioudakis

Richard Sefton Sidle

Eugene Jon Shekita

James C Kleewein

Xin Wu

John Frederick Hornibrook

Qi Cheng

Thanh Tan Pham

Michelle Meichiou Jou

Chi Man J Sizto

Kun Peng Ren

Steven M Chamberlin

Kevin H Low

Alex Chak-tsang Lui

Chandrasekaran Mohan

Kevin Scott Beyer

Haixun Wang

Josephine Miu Cheng

Jyh-Herng Chow

Andreas Neumann

Gary Valentin

Shivakumar Venkataraman

Serge Philippe Rielau

Narayana Iyer Subramanian

Hamid Pirahesh

Lori G Strain

Pradhan Pattanayak

Narayana Subramanina

Shivkumar Venkataraman

Praveen Seshadri

Sanjai Tiwari

Kit Man Cheung

Samuel S Lightstone

Kar Ming Tang

Mark A Paskin

Swati Vora

Techniques are disclosed for generating statistical views in a database system. In one embodiment, a request is received to execute a database workload. One or more constraints pertaining to executing the database workload is retrieved. The database workload is evaluated to generate multiple statistical view candidates. The statistical view candidates are refined based on the one or more constraints. One or more statistical views are then generated based on the refined statistical view candidates.

Generating statistical views in a database system

Tuning a production database system through the use of a remote mimic. In response to receipt of a query tuning request against a database system, information about that system is obtained and a mimic of the system is set up in a remote system environment. The mimic aims to imitate the database system in all relevant ways with respect to the tuning request. A tuning analysis is then performed on this mimic system such that there is substantially no impact to operations of the original database system. Tuning results are then applied to the original database system. The entire process takes place with little or no human intervention.

Query tuning in the cloud

Method, system, and computer program product for query processing are provided. In one implementation, the method comprises receiving a query that includes a full text portion. An invariant part of the full text portion is then compiled and stored. Additional queries received are executed by reusing the compiled invariant part.

Optimizing scalar search in a text indexing and search server

A method, computer readable medium, and system for optimizing a query in a relational database processing system is disclosed. The present invention relates to a query rewrite optimization method for eliminating a redundant join and equivalent subquery in an SQL query before generation and selection of the optimal query execution plan. The method of the present invention includes evaluating the query to identify a join predicate joining a sub-expression of the query to itself, and determining whether a row set producible from a first set of references of the query to the sub-expression is subsumed by a row set producible from a second set of references of the query to the sub-expression. Based on such evaluation and determination, the query may be reformed to eliminate the join predicate and the second quantifier. A further determination of the removability of the second quantifier may be required such as by evaluating a cardinality constraint when query output cardinality is material.

Redundant join elimination and sub-query elimination using subsumption

A method, apparatus, and article of manufacture for optimizing database queries using automatic summary tables. Query execution plans derived from an automatic summary table can be used to generate results for the query if a comparison of the query requirements with an automatic summary table definition determines that the automatic summary table overlaps the query, and if an optimization process determines that using the summary table will lower the cost of the query. The optimization process involves enumerating a plurality of query execution plans for the query, wherein the query execution plans enumerated include those that access combinations of query and summary tables. Each such query execution plan is assigned a cost representing an estimation of its execution characteristics, and the least costly query execution plan is selected for the query.

Optimizing database queries using query execution plans derived from automatic summary table determining cost based queries

The invention herein provides method and apparatus, including software for determining a set of materialized views or indices of the contents or a subset of the contents of a database in a data processing system to be created for one or more users of the database. The method and apparatus provide method and means for evaluating a workload presented by a user to the database; evaluating the data processing system characteristics; evaluating the database characteristics; and, using the above evaluations for recommending a set of suitable materialized views or indices to the user. Another aspect of the invention, which may be used for a workload presented by a user of a database in a data processing system, provides method and apparatus, including software for determining a set of materialized views or indices of the contents or a subset of the contents of the database, by: generating a plurality of materialized view candidates from evaluation of the workload, data processing system characteristics and database characteristics; estimating statistics for the materialized view candidates such as the number of rows, row size, and column statistics; generating a plurality of potential index candidates by evaluating the workload, data processing system characteristics, database characteristics and the materialized view candidates; and, from the materialized view candidates and index candidates selecting a set of suitable materialized views and/or indices for submission to the user.

Method for recommending indexes and materialized views for a database workload

An optimization technique for SQL queries, a program storage device storing the optimization program, and an apparatus for optimizing a query is provided. A query is analyzed to determine whether it includes a predicate for matching nullable operands and, if so, it is transformed to return TRUE when all operands are NULLs. If the DBMS supports this new function, the predicate is marked. If not, the predicate is re-written into a CASE expression having two SELECT clauses. The query is then executed in the computer to efficiently retrieve data from the relational database.

Method of optimizing SQL queries where a predicate matches nullable operands

An index structure for use by a database management system comprises a variant of a Patricia tree, wherein each node stores a column value that identifies a specified column of the constructed n-column search key that is used to determine a next path taken through the tree, a null value N that specifies whether a null value or an actual data value of the specified column of the constructed n-column search key is used to determine the next path taken through the tree, and a position value that specifies a portion of the specified column from the constructed n-column search key to be used to determine the next path taken through the tree when the actual data value of the specified column of the constructed n-column search key is used to determine the next path taken through the tree.

Efficient implementation of an index structure for multi-column bi-directional searches

Provided is a method, system, and program for accessing rows of data from a base table satisfying a search criteria. An initial request to access rows in the base table is received, including the search criteria specifying predicates for one or more columns in the base table. A data structure is generated to include information on qualifying base table rows that satisfy the search criteria. Notification is provided that the qualifying base table rows are accessible before information on all the qualifying base table rows is included in the data structure. A request is then received for one qualifying base table row. A determination is made as to whether the data structure includes information on the requested qualifying base table row. If so, the information in the data structure is used to provide access to the requested qualifying base table row.

Method, system, and program for accessing rows in one or more tables satisfying a search criteria

A method, apparatus, and article of manufacture for optimizing database queries using a derived summary table, wherein a definition of the summary table is based on a full select statement, including, but not limited to, a derived table involving nested GROUP BY operations and complex HAVING clauses with subqueries or joins, that is materialized in the table and describes how the summary table was derived. A query is analyzed using matching/compensation tests between the query and the definition of the summary table (that is, a query by itself) to determine whether expressions occurring anywhere in the query, but not in the summary table, can be derived using either the content in the summary table alone, or after combining (through some relational operator) the content of the summary table with other base tables, and hence the query is subsumed by or overlaps with the summary table definition.

Analyzing, optimizing and rewriting queries using matching and compensation between query and automatic summary tables

An optimization technique for SQL queries, a program storage device storing the optimization program, and an apparatus for optimizing a query is provided. A query is analyzed to determine whether it includes subselect expressions that return exactly or at most one tuple. If so, the quantifier merger or elimination is performed, if possible, to allow query to perform more efficiently than that of the original query while providing same results. The query is then executed in the computer to efficiently retrieve data from the database.

Method of simplifying and optimizing scalar subqueries and derived tables that return exactly or at most one tuple

A mechanism for supporting generated materialized columns without explicitly adding triggers and a check constraint in a database management system (DBMS). When a column affecting the generated materialized column is updated, a &ldquo;before-update&rdquo; trigger is internally generated in the DBMS and added as the last trigger before the update occurs. Similarly, when an insert operation is performed, a &ldquo;before-insert&rdquo; trigger is generated internally by the DBMS and added as the last trigger before the insert operation occurs. Since the triggers are generated internally in the DBMS, a constraint is not needed for either the update or the insert operations.

Method for providing a system maintained materialized functionally dependent column for a database management system

A method, apparatus, and article of manufacture for optimizing database queries. The query is analyzed to determine whether at least a portion of the query can be evaluated using a plurality of parallel operations without data redistribution. If so, then the most efficient query execution plan that uses these parallel operations is constructed and executed.

Parallel query optimization strategies for replicated and partitioned tables

Growing community of inventors

San Jose, CA, United States of America

Ting Yu Leung