International Business Machines Corporation

Eileen Tien Lin

Yun Yu Wang

James C Kleewein

Tina Louise Mukai

Lan Huang

Shivakumar Venkataraman

Guy Maring Lohman

William B Ackerman

David L Meyers

Hemant Maheshwari

Shivin S Misra

Michael R Zabka

Yang Sun

Jun Rao

Tian Zhang

Steven John Watts

Amit Radheshyam Somani

Dengfeng Gao

Ihab F Ilyas

Daniel T Huang

Daniele Costante Zilio

John Frederick Hornibrook

Jacques Labrie

Anjali Betawadkar-Norwood

Dimple Bhatia

Ivan Popivanov

Tam Minh Dai Tran

Tu-An Cheng

Stefan Korn

Harold Hotelling Lee

Provided are techniques for processing window partitioning and ordering for OLAP functions. A prior compare operation is performed by: receiving an input vector for each of one or more attributes of input data that represent one of a partition-by column and an order-by column in a database query; generating a per-attribute comparison vector for each input vector; and producing a single output vector using each per-attribute comparison vector, wherein each value of the single output vector is a Boolean attribute whose value for a given tuple is true if a current value and a most recent prior value of any of the one or more attributes are different.

Processing window partitioning and ordering for on-line analytical processing (OLAP) functions

A method and system for collecting statistics on database tables. In one embodiment, the method includes identifying a plurality of tables for statistics collection, wherein each table of the plurality of tables are remote tables; examining at least one attribute for each table identified for statistics collection; prioritizing the tables for statistics collection based on the at least one attribute; and collecting statistics on the identified tables in a priority sequence.

Statistics collection for database tables

Techniques are described for optimizing a multi-fact star schema query to retrieve data from two or more fact tables of a database. The optimization techniques may include internally transforming a received multi-fact star schema query into a group of single-fact sub-queries, thereby reducing or eliminating certain constraints during query processing. The group of sub-queries may be enumerated to determine a query execution plan. The multi-fact star schema query may be executed according to the query execution plan to retrieve the data from the two or more fact tables of the database.

Multi-fact query processing in data processing system

An apparatus and computer program product determine a target data type in an environment having a plurality of levels. The plurality of levels comprise a source level, zero or more intermediate levels, and a target level. The zero or more intermediate levels are between the source level and the target level. A target data type representing a transformation of a data type of an associated datum as the datum passes from the source level through the zero or more intermediate levels to the target level is generated. The target data type comprises a sequence of nested transformation functions having an innermost transformation function and an outermost transformation function.

Determining a target data type in a heterogeneous multi-level environment

A compilation time estimator provides a quantified estimate of the optimizer compilation time for a given query optimizer. The estimator automates the optimizer to choose the right level of optimization in commercial database systems. The estimator reuses an optimizer's join enumerator to obtain actual number of joins, but bypasses plan generation to save estimation overhead, and maintains a small number of interesting physical properties to estimate the number of plans by using a linear regression model. The estimator uses the number of generated plans to estimate query compilation time.

Estimating the compilation time of a query optimizer

An inexact query transformation system selects a query element from a query statement. If an inexact transformation improves the query performance, the system prioritizes the candidate transformation rules applicable to the selected query element. The system applies the candidate transformation rules to the selected query element to generate a transformed query element that includes a transformed remote query element. If the transformed remote query element is pushdownable, the system adds the transformed remote query element to a transformed inexact query output. Otherwise, the system applies additional candidate transformation rules to the selected query element to generate the transformed inexact query output. The system applies the transformed inexact query output to a heterogeneous datastore to provide an output query result.

System and computer program product for performing an inexact query transformation in a heterogeneous environment

A system, method, and program product are provided that identifies a remote stored procedure stored in a remote database management system, and automatically generates a local stored procedure stored in a local database management system. To automatically generate the local stored procedure, local and remote metadata are gathered corresponding, respectively, to a local database management system and a remote database management system. The remote metadata is used to create a call statement to the remote stored procedure. The created invocation method maps input values, input to the local stored procedure, to input parameters of the remote stored procedure. Results in the local stored procedure are set by mapping the data returned from the remote stored procedure to the corresponding return values (e.g., parameters and result sets) in the local stored procedure values.

System and method for virtualization of relational stored procedures in non-native relational database systems

Method for performing an inexact query transformation in a heterogeneous environment

A method of determining a target data type in an environment having a plurality of levels is provided. Zero or more intermediate levels are between the source level and the target level. A target data type representing the transformation of a data type of an associated datum as the datum passes from the source level through the zero or more intermediate levels to the target level is generated. In another aspect of the invention, the datum is assigned to the target level in accordance with the target data type. In yet another aspect of the invention, the target data type is passed with the associated datum, via the zero or more intermediate levels to the target level.

Technique for determining a target data type in a heterogeneous multi-level environment

A robust way is described for optimizing complex data base queries while retaining the optimization speed of heuristic methods. The heuristic join-sequencing algorithm is modified to permit any of, or a combination of: (1) multiple passes of the heuristic algorithm, each with a different metric, producing multiple plans; (2) complex combinations of the criteria by which such heuristics make their choices; and/or (3) backtracking to consider alternatives to any particular decision in the sequence.

Fast and robust optimization of complex database queries

A method, computer product, and system for pushdown analysis during query plan generation is provided. Rather than do pushdown analysis for portions of a query that are query execution plan independent, the present invention provides pushdown analysis for features of a query that are plan dependent. This is done by evaluating a portion of the query plan for pushdownability to a remote database system; and if pushdownable to the remote database system, generating cost parameters for the portion of the plan if executed at a local database system and if executed at the remote database system. With both sets of cost parameters, the query optimizer has added information on which to select an optimal query plan.

Method, computer program product, and system for pushdown analysis during query plan generation

A method, computer product, and system for rewriting database without decreasing pushdownability is provided. First, a pushdown analysis of the query in its entirety is performed prior to the application of any query rewrite rules in order to establish a baseline on pushdownability for the query. The results of this analysis is stored with the internal query representation. After each rule is applied to rewrite a portion of a query, that rewritten portion is analyzed again for pushdownability. If pushdownability is not decreased, then the rewritten query remains and the internal representation of the query is updated to reflect the pushdownability of that portion. If pushdownability is decreased, then an undo operation is applied to the rewritten portion of the query to back out the effects of the rule and leave the query in the same state as before the rewrite. Finally, additional rewrite rules that are directed to the heterogeneous database environment are also provided.

Growing community of inventors

San Jose, CA, United States of America

Eileen Tien Lin