公开(公告)号：US20240054131A1

公开(公告)日：2024-02-15

申请号：US17884917

申请日：2022-08-10

Applicant: Oracle International Corporation

Inventor： Rafi Ahmed ,  Randall Bello ,  Andrew Witkowski

IPC: G06F16/2455 ,  G06F16/2453

CPC classification number: G06F16/24554 ,  G06F16/24539

Abstract: Techniques for automatically partitioning materialized views are provided. In one technique, a definition of a materialized view is identified. Based on the definition, multiple candidate partitioning schemes are identified. A query is generated that indicates one or more of the candidate partitioning schemes. The query is then executed, where executing the query results in one or more partition counts, each corresponding to a different candidate partitioning scheme of the one or more candidate partitioning schemes. Based on the one or more partition counts, a candidate partitioning scheme is selected from among the plurality of candidate partitioning schemes. The materialized view is automatically partitioned based on the candidate partitioning scheme.

公开(公告)号：US20210026847A1

公开(公告)日：2021-01-28

申请号：US16523872

申请日：2019-07-26

Applicant: Oracle International Corporation

Inventor： Rafi Ahmed ,  Randall Bello ,  Andrew Witkowski

IPC: G06F16/248 ,  G06F16/2453 ,  G06F16/242

Abstract: Definitions of material views are automatically generated. In general, Automated MV generation identifies a set of candidates MVs by examining a working set of query blocks. Once the candidates are formed, the candidate MVs are further evaluated to calculate a benefit to the candidate MVs. An improved approach for generating a candidate set of MVs is described herein. The improved approach is referred to as the extended covering subexpression technique (ECSE). Under ECSE, various relationships between join sets other than strict equivalence are used to generate new resultant join sets. Such relationships include subset, intersection, superset, and union, which shall be described in further detail below. In some cases, relationships among resultant join sets and initial join sets are considered to generate new resultant join sets. The final resultant join sets are then used to form a candidate set of MVs.

公开(公告)号：US12135719B2

公开(公告)日：2024-11-05

申请号：US17884917

申请日：2022-08-10

Applicant: Oracle International Corporation

Inventor： Rafi Ahmed ,  Randall Bello ,  Andrew Witkowski

IPC: G06F16/245 ,  G06F16/2453 ,  G06F16/2455

Abstract: In one technique, a definition of a materialized view is identified. Based on the definition, multiple candidate partitioning schemes are identified. A query is generated that indicates one or more of the candidate partitioning schemes. The query is then executed, where executing the query results in one or more partition counts, each corresponding to a different candidate partitioning scheme of the one or more candidate partitioning schemes. Based on the one or more partition counts, a candidate partitioning scheme is selected from among the plurality of candidate partitioning schemes. The materialized view is automatically partitioned based on the candidate partitioning scheme.

公开(公告)号：US11086868B2

公开(公告)日：2021-08-10

申请号：US16667527

申请日：2019-10-29

Applicant: Oracle International Corporation

Inventor： Rafi Ahmed ,  Randall Bello ,  Andrew Witkowski

IPC: G06F16/245 ,  G06F16/2453

Abstract: A one-sided outer-join-based query is rewritten using a materialized view (MV), the definition of which includes the join but does not include a filter predicate from the query. The rewritten query nullifies data from the include-matching table, included in the MV, that does not satisfy the filter predicate. To improve accuracy of the query results, certain rows are removed from intermediate results of the query. To facilitate revising the query results for accuracy, the MV includes a unique column from the include-all table and also an indicator column that indicates whether a given row of the MV is an inner-join-type row or an anti-join-type row. The rewritten query adjusts the indicator values, in the indicator column, of MV rows that do not satisfy the filter to reflect a modified anti-join-type indicator value. Based on the modified indicator values and unique columns from include-all tables, accuracy of query results is attained.

公开(公告)号：US20210124742A1

公开(公告)日：2021-04-29

申请号：US16667527

申请日：2019-10-29

Applicant: Oracle International Corporation

Inventor： Rafi Ahmed ,  Randall Bello ,  Andrew Witkowski

IPC: G06F16/2453

Abstract: A one-sided outer-join-based query is rewritten using a materialized view (MV), the definition of which includes the join but does not include a filter predicate from the query. The rewritten query nullifies data from the include-matching table, included in the MV, that does not satisfy the filter predicate. To improve accuracy of the query results, certain rows are removed from intermediate results of the query. To facilitate revising the query results for accuracy, the MV includes a unique column from the include-all table and also an indicator column that indicates whether a given row of the MV is an inner-join-type row or an anti-join-type row. The rewritten query adjusts the indicator values, in the indicator column, of MV rows that do not satisfy the filter to reflect a modified anti-join-type indicator value. Based on the modified indicator values and unique columns from include-all tables, accuracy of query results is attained.

公开(公告)号：US11615107B2

公开(公告)日：2023-03-28

申请号：US16523872

申请日：2019-07-26

Applicant: Oracle International Corporation

Inventor： Rafi Ahmed ,  Randall Bello ,  Andrew Witkowski

IPC: G06F16/00 ,  G06F16/248 ,  G06F16/2453 ,  G06F16/242

Abstract: Definitions of material views are automatically generated. In general, Automated MV generation identifies a set of candidates MVs by examining a working set of query blocks. Once the candidates are formed, the candidate MVs are further evaluated to calculate a benefit to the candidate MVs. An improved approach for generating a candidate set of MVs is described herein. The improved approach is referred to as the extended covering subexpression technique (ECSE). Under ECSE, various relationships between join sets other than strict equivalence are used to generate new resultant join sets. Such relationships include subset, intersection, superset, and union, which shall be described in further detail below. In some cases, relationships among resultant join sets and initial join sets are considered to generate new resultant join sets. The final resultant join sets are then used to form a candidate set of MVs.

公开(公告)号：US20220309063A1

公开(公告)日：2022-09-29

申请号：US17214298

申请日：2021-03-26

Applicant: Oracle International Corporation

Inventor： Binglu Du ,  Murali Thiyagarajah ,  Praveen Tupati Jaganath Kumar ,  Randall Bello

IPC: G06F16/2453 ,  G06F16/2455

Abstract: Using Logical Partition Change Tracking (LPCT), a database system is able track the staleness of a materialized view at the level of logical partitions of a base database object, in addition to or instead of tracking the staleness of a materialized view at the level of physical partitions of the base database object. When the base database object is logically partitioned, it is possible using LPCT for the system to identify the records of the materialized view that correspond to changed logical partitions of the base database object. The records of the materialized view corresponding to the changed logical partitions become stale while other records of the materialized view corresponding to unchanged logical partitions remain fresh. The ability to identify which records of a materialized view are fresh and which are stale at the level of logical partitions of the base database object allows the system to rewrite user queries to use those records of the materialized view that are fresh.