International Business Machines Corporation

Jerry Mukai

Fen-Ling Lin

Hong Sang Tie

Yao-Ching Stephen Chen

Balakrishna Raghavendra Iyer

Yun Yu Wang

Thomas Abel Beavin

Kalpana Shyam

Randy Mitchell Nakagawa

Robert William Lyle

Guogen Zhang

Julie Ann Watts

Mengchu Cai

Piyush Goel

Donald J Haderle

Gautam Bhargava

Jay A Yothers

Craig Alan Friske

Li Jun Xia

Paramesh Sampatrai Desai

Regina J Liu

Charles H Lin

Yoichi Tsuji

Binghui Zhong

Peter Hoa

Irene Ching-Hua Liu

Hsiuying Yen Cheng

Ying Zeng

Cherri M Vidmar

Patrick Michael Malone

Demai Ni

William Samuel Perlman

James Zu-Chia Teng

Patrick Dooling Bossman

Lee-Chin Hsu Liu

Jeffrey Ralph Ruggles

Yumi Kimura Tsuji

Stephen Yao Ching Chen

Desai Paramesh Sampatrai

Embodiments provide an approach for efficiently searching a temporal key index as well as enforcing key distinctiveness property in a database table corresponding to the index. For example, by addressing time range overlaps in an index having a sort order for temporal keys, a database management tool of a server system may efficiently process client requests while enforcing a temporal key property in the database table.

Managing a temporal key property in a database management system

Dropping of columns from a table with data availability, where the columns in the table are each associated with a column number, includes: executing a statement to drop a given column in the table and deferring an application of the statement until a reorganization of a current data set including the table. When the reorganization of the current data set is performed, the reorganization includes: updating column numbers for columns in each row of the table using a mapping data structure to remove the given column; loading the plurality of data rows with the updated column numbers into a shadow data set; applying to the shadow data set any changes to the table that are concurrent with the reorganization; updating a schema definition of the table with the updated column numbers; and switching the current data set to the shadow data set.

Dropping columns from a table with minimized unavailability

A method and system for providing a scalable storage scheme for native hierarchically structured data of relational tables, includes a base table with indicator columns with information pertaining to hierarchically structured data of a document, data tables for storing the hierarchically structured data corresponding to the indicator columns, and node identifier indexes corresponding to the data tables for mapping between the indicator columns and the hierarchically structured data in the data tables. In an embodiment, actual data for each hierarchically structured data (such as XML) column is stored in a separate data table, and each data table has a separate node identifier index. The node identifier index is searched with a key containing the document identifier and a logical node identifier is used, and a record identifier of a record in the data table containing the node assigned the logical node identifier is retrieved.

Scalable storage schemes for native XML column data of relational tables

Techniques are provided that determine that a query includes at least one subquery that may be correlated or decorrelated. An internal representation of the query that represents the subquery in both correlated and decorrelated form is generated, wherein the internal representation includes at least one virtual table.

Cost-based subquery correlation and decorrelation

A method, apparatus, and article of manufacture for optimizing a query in a computer system, wherein the query is performed by the computer system to retrieve data from a database stored on the computer system. The optimization is performed by identifying a correlated subquery in the query that qualifies for transformation, transitively closing a local filtering predicate within the identified correlated subquery that is on a same column as a correlation join predicate within the identified correlated subquery, and pulling out or bubbling up the transitively closed predicate from the correlated subquery to a parent query block of the query.

Query transformation for queries involving correlated subqueries having correlation join predicates with local filtering predicates involving predicate transitive closure and predicate pull-out

A technique for optimizing execution of a query that accesses data stored on a data store connected to a computer. Query predicates involving different data types are identified. A second query predicate is generated which includes matching data types, and if necessary, converted operators. The second query predicate with the matching data types and/or converted operators is executed to obtain a subset of records using an index manager or data manager. The subset of records is converted to the data types of the remaining query predicates. The first query predicate is applied against the subset of records to obtain a result, and the remaining query predicates of the original query are applied to the result of the first query predicate and any other additional converted query predicates to obtain a final result.

Optimization of SQL queries using filtering predicates

A method, apparatus, and article of manufacture for analyzing a query and extending an access path selection for the query, in order to optimize joined table expressions and complex join operations in the query. The extension of the access path selection occurs in two phases: (1) an access path selection router analyzes and divides the query into portions, so that the overall access path selection is divided into smaller access path selections for processing by a join enumerator; and (2) the join enumerator is provided to handle joined table expressions and complex join operators, wherein the join enumerator can be continuously enhanced as more powerful query optimization techniques become available.

Optimization of joined table expressions by extended access path selection

A query optimizer analyzes a query and triggers a sequence of transformations to achieve optimal performance for joined table expressions. The transformations include pushing down predicates for joined table expressions and pushing down non-null predicates to simplify join operators for joined table expressions.

Joined table expression optimization by query transformation

Disclosed is a method, system, and program for performing a join operation on a multi-column table and at least two satellite tables having a join condition. Each satellite table is comprised of multiple rows and at least one join column. The multi-column table is comprised of multiple rows and at least one column corresponding to the join column in each satellite table. A join operation is performed on the rows of the satellite tables to generate concatenated rows of the satellite tables. One of the concatenated rows is joined to the multi-column table and a returned entry from the multi-column table is received. A determination is then made as to whether the returned entry matches the search criteria. If so, a determination is made as to whether one of the satellite tables has duplicates of values in the join column of the returned matching entry or the multi-column table has duplicate entries in the join columns. Returned matching entries are generated for each duplicate value in the satellite tables and duplicate entry in the multi-column table.

Method, system, and program for a join operation on a multi-column table and satellite tables including duplicate values

A method, apparatus, and article of manufacture for a computer implemented query compiler. A query is executed in a computer, the query being performed by the computer to retrieve data from a database stored on a data storage device connected to the computer. The query is received containing at least one variable. At execution time, a value is determined for each variable in the query. A filter factor is estimated using the determined value for each variable. Then, an access path is determined for the query using the estimated filter factor.

Access path selection for SQL with variables in a RDBMS

Determining the optimal access path for a query at execution time using

A method and apparatus for generating static, dynamic and hybrid sparse indices for use with workfiles used by SQL queries in a relational database management system.

Method and apparatus for generating dynamic and hybrid sparse indices

Growing community of inventors

San Jose, CA, United States of America

Jerry Mukai