Teradata Us, Inc.

Ncr Corporation

Grace K Au

Hong Gui

Haiyan Chen

Bhashyam Ramesh

Curt J Ellmann

Sanjay Nair

Bibhu Ranjan Priyadarshi

Carlos Bouloy

Paul Laurence Sinclair

Ahmad Said Ghazal

Olli Pekka Kostamaa

Rama Krishna Korlapati

Kuorong Chiang

Michael A Gibas

James Ronald Carlson

Ramakrishna Korlapati

Phaneendra Bhattiprolu

David O Craig

A data store system may include an array of persistent storage devices configured to store a plurality of data store tables. The data store system may further include a processor in communication with the storage device. The processor may receive a query containing a non-equality join condition on a first column from a first data store table and a second column on a second data store table. The processor may generate a bitmap based on the join condition. The bitmap indicate respective matches between the first column and second column in accordance with the non-equality join condition. The bitmap may also be used each time the non-equality join condition is present in another received query. A method and computer-readable medium may also be implemented.

Join index bitmap for non-equality query conditions

A database system may include a storage device to store a plurality of database tables. At least a portion of the database tables may be column-partitioned. The database system may also include a processor in communication with the storage device and a row-column subsystem executable by the processor to receive a request to locate a row of a column-partitioned database table. The row in the request may be used to provide a response to a query. The row-column subsystem may be further executable to determine if referenced column values of the requested row are stored in a cache associated with the row-column subsystem. The row-column subsystem may be further executable to retrieve the referenced column values of the row in the request from the cache in response to the determination that the column values are in the cache. The row-column subsystem may be further executable to provide the referenced column values for evaluation with respect to query conditions of the query. A method and computer-readable medium may also implement the row-column subsystem.

Processing column-partitioned data for row-based operations in a database system

A method for estimating the selectivity of a database base table predicate, the cardinality of a join, and the cardinality of an aggregation. The method includes receiving a database query, the query comprising one or more query predicates and referencing one or more database tables. One or more join indexes are identified, the join index(es) defined on respective database tables referenced by the database query. The join index(es) comprises one or more join index predicates, and includes one or more join columns in its select list. The row count selected by the query predicates is calculated at least partly using the row count or statistics of the one or more join indexes. The selectivity of the base table predicate is calculated at least partly from the calculated row count. The cardinality of the join is estimated at least partly from the row count and statistics of the identified join index(es).

Cardinality and selectivity estimation using a single table join index

A database system includes database software to receive a query specifying a join of plural base tables, and to determine whether a foreign key and primary key relationship exists between join columns specified by the query and a materialized view definition. Based on the existence of the foreign key and the primary key relationship between join columns specified by the query and the materialized view definition, the database software determines that a result for the received query is to be computed from the materialized view.

Determining materialized view coverage for join transactions

A system and method include defining a snapshot join index using a sparse condition in a join index definition. A new sparse condition of the snapshot join index is compared with an old sparse condition. Rows in a base table are identified as a function of the comparing, and the join index table is updated using the identified rows.

Deferred maintenance of sparse join indexes

A SQL query that includes an IN-List is optimized by utilizing an array insert to insert the IN-List into a spool and piggybacking IN-List rows into a join step.

Optimizing the processing of in-list rows

A system and method of rewriting a database query where the query contains an aggregate includes the following. If one or more aggregate materialized views are considered, rewriting the query using an aggregate materialized view. If one or more non-aggregate multi-table materialized views are considered, the query is rewritten using a multi-table materialized view. A join cost is determined for each such non-aggregate multi-table materialized view. If one or more non-aggregate single table materialized views are considered, the query is rewritten using the single table materialized view. A join cost is determined for each such non-aggregate single table materialized view. Finally, a current total cost is determined for use of various materialized views as a function of join cost, aggregation cost and spool cost to select a rewritten query.

Cost-based query rewrite using materialized views

A system and method include obtaining a query and identifying an aggregate join index (AJI) at a high level of aggregation. The dimension table may be rolled-up with the grouping key being the union of the grouping key in the AJI and the grouping key of the query. The identified AJI is joined with the rolled-up dimension table to obtain columns in the query that are not in the identified AJI. The joined AJI and rolled-up dimension table are then rolled up to answer the query.

Aggregate join index utilization in query processing

A SQL query that includes an IN-List is optimized by (1) performing an evaluation to determine whether access to a table can be performed as a join operation, (2) converting the IN-List to an IN-LIST relation, and (3) joining the IN-List relation with the table to access the data in the table.

Optimizing search trees by increasing success size parameter

The invention relates to a method of determining the performance of an optimizer program arranged to generate a plan. The method generally comprises the steps of executing the optimizer utilising at least one test case to derive a pre-variation plan; executing the pre-variation plan to generate a first output; varying a strategy of the optimizer program; executing the varied optimizer program utilising the at least one test case to derive a post-variation plan; and executing the post-variation plan to generate a second output. The outputs are then grouped according to at least one element of the plans to assess the performance of the optimizer.

Database optimizer plan validation and characterizations

A database query is optimized for a star database schema that includes at least one fact table and at least one dimension table, where the database query includes an IN-List condition. During optimization, the IN-List is transformed into a relation and the relation is joined with the dimension table to produce a join result. The join result is then joined to the fact table.

Optimizing access to a database by utilizing a star join

Deriving join cost demographics for a binary join, i.e., a join between two relations each having a join column, includes using a correlation factor related to the ratio of unique join column values stored in each of a plurality of units of storage to the total number of unique values in the join column.

Using a correlation factor to derive join costing statistics

A database system is able to perform efficient execution of insert-select, join-delete, or join-update requests. The database system has plural access modules to manage access of respective portions of data in a storage of the database system. If certain predefined criteria are satisfied, the insert-select, join-delete, or join-update request can be executed by performing a single-access module merge and/or merge-delete step on a single one of the access modules with row-level locks (e.g., row-hash locks).

Execution of requests in a parallel database system

A method and apparatus determines (1) if a materialized view can be used to respond to a query for data stored in one or more base tables of a database system; and/or (2) if the materialized view needs to be updated in response to an update query. A query expression tree represents the query, and a materialized view expression tree represents the materialized view. The components of the query expression tree are compared with components of the materialized view expression tree to determine if the materialized view covers the query that is, the tuples in the result of the query expression are a subset of the tuples in the result of the materialized view expression.

Growing community of inventors

Rancho Palos Verdes, CA, United States of America

Grace K Au