International Business Machines Corporation

Ying Zeng

Terence Patrick Purcell

Fen-Ling Lin

Thomas Abel Beavin

Yao-Ching Stephen Chen

Mengchu Cai

Andrei Fedorovich Lurie

Jerry Mukai

Li Jun Xia

Irene Ching-Hua Liu

Yoichi Tsuji

Hsiuying Yen Cheng

Demai Ni

You-Chin Gene Fuh

Ying-Lin Chen

In an index-only multi-index access in a database, when a query to be executed requires multiple indexes, a query optimizer determines whether columns are needed for a query result, subquery, or inner join result. If not, then the query result is resolved using qualified record identifiers (RIDs) from the multiple indexes without accessing the data pages. When the columns are needed and can be resolved using input values of the query, then the query result is resolved using the qualified RIDs from the multiple indexes and substituting column values with the input values of the query. When the columns are needed and cannot be resolved using the input values of the query, the query optimizer may store one or more index keys with its corresponding qualified RIDs from the multiple indexes and may resolve the query result using the stored index keys without accessing the data pages of the database.

Index-only multi-index access

A method, computer program product, and system for managing row identifier (RID) list processing on an index are provided. The method, computer program product, and system provide for accessing one or more key values in the index based on one or more keys specified in a query, retrieving a plurality of row identifiers corresponding to the one or more key values from the index, and predicting an actual number of row identifiers to be retrieved from the index based on the one or more key values accessed and the plurality of row identifiers retrieved.

Row-identifier list processing management

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

Growing community of inventors

Santa Clara, CA, United States of America

Ying Zeng