Oracle International Corporation

James Finnerty

Mohamed Ziauddin

Andrew Witkowski

Randall G Bello

William D Norcott

John Raitto

A method and system is provided for processing queries. Specifically, techniques are provided for handling a query that does not reference a particular materialized view, where the query requires access to values from a particular column not contained in the materialized view. A technique is also provided for processing a query that does not reference a particular materialized aggregate view, where the materialized aggregate view specifies an outer join between a child table and a parent table and the query specifies a particular type of join between the child table and the parent table, where the particular type of join is one of an inner join, an anti-join and a semi-join. The query is rewritten to produce a rewritten query that accesses the materialized aggregate view to produce data required by the query. A technique is also provided for processing a query that does not reference a particular materialized view and that specifies that results are to be grouped by a first set of one or more columns, where the materialized view reflects data that is grouped by a second set of one or more columns. If each column in the first set of columns either exactly matches a column in the second set of columns, is functionally dependent on another column in the first set of columns, or is functionally dependent on a column in the second set of columns, then the query is rewritten to produce a rewritten query that references the materialized view.

Rewriting a query in terms of a summary based on functional dependencies and join backs, and based on join derivability

A method and system are provided for processing queries. According to one aspect of the invention, a query that does not reference a particular materialized view is rewritten to reference the materialized view. In particular, upon receiving the query, it is determined whether the particular materialized view satisfies each condition in a set of conditions, where the set of conditions at least includes a condition that the materialized view reflects all rows that exist in a common section. The common section is a section of the query that is common to both the materialized view and the query. If the materialized view satisfies each condition in the set of conditions, then the query is rewritten to produce a rewritten query that references the materialized view. The materialized view may be a summary table that includes a summary column. The summary column contains values generated by aggregating values contained in rows produced by a one-to-many lossless join. The one-to-many lossless join is not in the common section. The query includes a cumulative aggregate function. Under these conditions, the method includes generating results of the cumulative aggregate function in the query by dividing values from the summary column by scaling factors.

Rewriting a query in terms of a summary based on one-to-one and one-to-many losslessness of joins

A method and apparatus are provided for performing deferred incremental refresh of summary tables that are derived from two or more base tables. Incremental refresh is performed by assigning a hypothetical load sequence to the base tables of the summary table. For each base table that contains new data that affects the summary table, a join is performed between (1) the new data in the base table, (2) the pre-update state of base tables that follow the base table in the hypothetical load sequence, and (3) the post-update state of base tables the precede the base table in the hypothetical load sequence. The results of the join are then merged with the existing summary table to refresh the summary table. According to one aspect of the invention, efficiency is improved by performing the joins for the various base tables in parallel.

Method and apparatus for incremental refresh of summary tables in a database system

A method and system are provided for processing queries, where the queries do not reference a particular materialized view. Specifically, techniques are provided for handling a query that specifies a first set of one or more aggregate functions, where the particular materialized view reflects a second set of one or more aggregate functions. Whether the query can be rewritten is determined based on the aggregate functions in the first and second sets, and the corresponding arguments. Techniques are also provided for processing a query that (1) does not reference a particular materialized view, (2) specifies a first set of one or more aggregate functions, where the particular materialized view reflects a second set of one or more aggregate functions. A technique is also provided for rewriting queries that specify an outer join that has a dimension table on the child-side of the outer join and a fact table on the parent-side of the outer join. The query is rewritten to produce a rewritten query by replacing references to the fact table in the query with references to a materialized view. The rewritten query specifies an outer join that has the dimension table on the child side and the materialized view on the parent side.

Growing community of inventors

Lexington, MA, United States of America

James Finnerty