Oracle International Corporation

Murali Thiyagarajan

Praveen Kumar

Praveen T J Kumar

Techniques for a database management system to predict when in the future a materialized view will be used for query rewrite. This is a followed by an approach that uses the quiet rewrite pattern prediction to determine an optimized schedule for refreshing the materialized view. The approach combines the query rewrite pattern prediction with a quiet period prediction for the materialized view and an estimated refresh duration for the materialized view to determine the optimized refresh schedule for the materialized view.

Predicting future query rewrite patterns for materialized views

Techniques for a database management system to predict when in the future a materialized view will have a quiet period during which the materialized view will not be stale. This is a followed by an approach that uses the quiet period prediction to determine an optimized schedule for refreshing the materialized view. The approach combines the quiet period prediction with a query rewrite pattern prediction for the materialized view and an estimated refresh duration for the materialized view to determine the optimized refresh schedule for the materialized view.

Predicting future quiet periods for materialized views

Techniques for a database management system to estimate a time needed to refresh a materialized view. This is a followed by an approach that uses estimated refresh duration to determine an optimized schedule for refreshing the materialized view. The approach combines the refresh duration estimate with a query rewrite pattern prediction for the materialized view and a quiet period prediction for the materialized view to determine the optimized refresh schedule for the materialized view.

Estimating materialized view refresh duration

A query, issued to a database server, is re-written with a nested materialized view. The selected materialized view is nested within a 'parent' materialized view that has one or more nesting levels for one or more nested materialized views. The selected nested materialized view has a cardinality associated therewith, which is lower than the cardinality associated with any of the other nested materialized views, and is nested at a final nesting level of the one or more nesting levels.

Query rewrite with a nested materialized view

A method, system, and computer program product for validating database table partitioning from partition advisors. The method commences by receiving a workload comprising a plurality of queries, then analyzes the queries to determine stratification buckets based on the usage of tables in the queries. Further analysis of the queries results in assigning the queries into one or more of the stratification buckets from which buckets a number n of queries (n being smaller than the total number of queries in the received workload) are drawn from the stratification buckets to form a representative workload having a confidence interval C and a margin of error M. Now, having a representative workload that is smaller, yet statistically representative of the received workload, a computer evaluates each of a plurality of partition schemes using the representative workload to determine an optimal partitioning scheme. The confidence interval C can be increased or decreased.

Validating database table partitioning schemes using stratified random sampling

A query statement, issued to a local database server, is re-written. The query references at least one of a first or a second object. The first object is remote with respect to the local database server, for accessing a first materialized view that is local or remote with respect to the local database server. The second object is local with respect to the local database server, to access a second materialized view that is remote with respect to the local database server. Rewriting the query can include dynamically tracking a staleness state associated with one or more of the materialized views.

Query rewrite with a remote object

In one embodiment, a plurality of materialized views is stored, where each view includes a set of aggregate expressions. Storing each materialized view comprises identifying one or more disjuncts in the set of aggregate expressions, generating a set of disjunct trees that represent the set of aggregate expressions, and storing the set of disjunct trees. When a query that requests aggregated data is received, one or more disjuncts are identified in an aggregate expression specified in the query. A disjunct tree for the disjuncts in the query is generated. Based on the disjunct tree for the query and on the set of disjunct trees for each materialized view, a particular materialized view is determined from the plurality of materialized views. The query is rewritten into an equivalent query that references the particular materialized view, where the equivalent query would retrieve the requested aggregated data from the particular materialized view.

Supporting aggregate expressions in query rewrite

A method allows a query, which contains an inline view, to be rewritten to use a materialized view. The materialized view has an inline view that is equivalent to the inline view of the query. However, the inline view of the materialized view varies textually from the inline view of the query.

Inline view query rewrite using a materialized view

Described herein are instance mapping techniques that facilitate the rewrite of queries to access materialized views that have multiple instances of the same table. Instance mapping techniques are processes for determining a mapping between equivalent instances in a query and materialized view definition. An instance in a query that is equivalent to an instance in a materialized view definition (or another query) is treated as if they are identical labels for the same table for the purposes of rewriting a query. To determine the mapping between instances of a table in a query and a materialized view definition, join predicates in the query and materialized view definition that involve instances of the table are compared.

Growing community of inventors

Concord, NH, United States of America

Murali Thiyagarajan