Teradata Us, Inc.

Ncr Corporation

Hong Gui

Grace K Au

Curt J Ellmann

Carlos Bouloy

Ambuj Shatdal

Ahmad Said Ghazal

Kuorong Chiang

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 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 database system includes a storage to store a view containing results of a cube-based operation on at least one base table, with the view containing a first result set for a group-by on a first grouping set, and a second result set for a group-by on a second grouping set. In response to a change to the at least one base table, a controller updates the first result set by computing a change to the first result set based on a change in the at least one base table, and updates the second result set by computing a change to the second result set based on the change to the first result set.

Maintaining views of cube-based operations in a database system

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 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

Madison, WI, United States of America

Hong Gui