公开(公告)号：US20140379690A1

公开(公告)日：2014-12-25

申请号：US13923216

申请日：2013-06-20

Applicant: Oracle International Corporation

Inventor： Rafi Ahmed ,  Mohamed Zait

IPC: G06F17/30

CPC classification number: G06F17/30451 ,  G06F17/30448 ,  G06F17/30454 ,  G06F17/30466

Abstract: A method, apparatus, and stored instructions are provided for transforming an initial query by eliminating a subquery nested within the initial query. The initial query may include an outer query that references a first instance of a particular data object. The initial query may also include set operator(s). At least a particular set operator may include a particular subquery that references a different instance of the particular data object. A query processor may transform the initial query to a transformed query that excludes the particular set operator and the particular subquery. The transformed query may instead include an added predicate that is based at least in part on the particular subquery. The added predicate may reference the first instance of the particular data object without referencing the different instance of the particular data object. The transformed query may be used for query execution instead of the initial query.

Abstract translation: 提供了一种方法，装置和存储的指令，用于通过消除嵌套在初始查询内的子查询来转换初始查询。 初始查询可以包括引用特定数据对象的第一实例的外部查询。 初始查询还可以包括集合运算符。 至少特定的集合运算符可以包括引用特定数据对象的不同实例的特定子查询。 查询处理器可以将初始查询转换为排除特定集合运算符和特定子查询的转换查询。 转换后的查询可以替代地包括至少部分基于特定子查询的添加谓词。 添加的谓词可以引用特定数据对象的第一个实例，而不引用特定数据对象的不同实例。 转换的查询可以用于查询执行，而不是初始查询。

公开(公告)号：US20230024553A1

公开(公告)日：2023-01-26

申请号：US17380905

申请日：2021-07-20

Applicant: ORACLE INTERNATIONAL CORPORATION

Inventor： Rafi Ahmed ,  Mohamed Ziauddin ,  Sankar Subramanian

IPC: G06F16/2453

Abstract: Techniques for subsumption of inline views and subqueries in a query are described. An optimization technique of subsumption is enabled by inline views having identical tables and identical join conditions and having aggregation functions but no group-by clauses. When subsumption takes place, a single query block replaces the inline views (or subqueries) with a single inline view query block. Subsumption reduces multiple access to the same table and multiple evaluations of the same join conditions required to evaluate the query. The single query block includes factored out filter predicates and unified predicates that originate from the subsumed inline views (or subqueries). Based on similarities among the aggregation functions and filter predicates in the subsumed inline views, pre-computation of common aggregates may be performed in a new group-by view in the subsuming view.

公开(公告)号：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.

公开(公告)号：US20240126757A1

公开(公告)日：2024-04-18

申请号：US17966036

申请日：2022-10-14

Applicant: Oracle International Corporation

Inventor： Rafi Ahmed ,  Angela Amor ,  Mohamed Ziauddin

IPC: G06F16/2453

CPC classification number: G06F16/24544 ,  G06F16/24535 ,  G06F16/24537

Abstract: A new type of table join operation, outer semi join (OSJ), is provided, which can be used by an optimizer layer and an execution layer of a database management system (DBMS). OSJ combines the semantics of both left outer-join and semi-join. The concept of an anti-join marker (AJM) is also introduced, which specifies whether a matching row was not found between joined tables for each result row in an OSJ operation. The OSJ operation supports unnesting of a class of disjunctive ANY, ALL, EXISTS, NOT EXISTS, IN, and NOT IN subqueries for execution plan optimization. The disjunction may contain filter predicates. For unnesting, OSJ avoids the need of using a distinct operator on the right table and also supports using inequality (e.g. >, >=,

公开(公告)号：US11714810B2

公开(公告)日：2023-08-01

申请号：US17213034

申请日：2021-03-25

Applicant: Oracle International Corporation

Inventor： Rafi Ahmed ,  Srikanth Bellamkonda

IPC: G06F16/2453

CPC classification number: G06F16/24535 ,  G06F16/24537

Abstract: Techniques are described herein for subquery removal given two set operation-based subqueries in a query, where one subquery contains the result of the other. The described optimization technique of subquery removal is enabled by join and set operation-based containment of the set operation-based subqueries where semantic equivalence can be established for a given pair of set operation-based subqueries when some table(s)—with associated join condition(s), correlation condition(s), and/or filter predicate(s)—in one subquery are not considered. Subquery removal reduces multiple access to the same table and multiple evaluations of the same join conditions required to evaluate the query. When a subquery is removed from a disjunction, this may lead to other optimizations such as subquery unnesting, e.g., when the original query configuration would not permit query unnesting and the rewritten query (with one or more removed subqueries) permits unnesting.

公开(公告)号：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.

公开(公告)号：US20190026332A1

公开(公告)日：2019-01-24

申请号：US15658249

申请日：2017-07-24

Applicant: Oracle International Corporation

Inventor： Rafi Ahmed ,  Andrew Witkowski ,  Sankar Subramanian

IPC: G06F17/30

Abstract: A method, apparatus, and stored instructions are provided for the removal of redundant GROUP BY and/or DISTINCT. Every table in the FROM clause of the query block must be a qualified table for the GROUP-BY clause or the DISTINCT keyword in the SELECT clause of the query block to be removed. A table Tx that satisfies at least one of the following two conditions is referred to as a qualified table: (1) Tx has a non-null unique column Tx.u that appears on the GROUP BY clause or the SELECT clause that contains a DISTINCT keyword and (2) There is a qualified table Ty and Ty has a filtering join with Tx.

公开(公告)号：US20180101573A1

公开(公告)日：2018-04-12

申请号：US15726030

申请日：2017-10-05

Applicant: Oracle International Corporation

Inventor： Lei Sheng ,  Rafi Ahmed ,  Andrew Witkowski ,  Sankar Subramanian

IPC: G06F17/30

CPC classification number: G06F16/24544 ,  G06F16/24537

Abstract: Techniques herein optimize sort-merge join method for a band join. In an embodiment, for a query comprising a query block specifying a join between a first table and a second table, a band join condition is detected between the first table and the second table. Once the band join condition in detected, an execution plan is generated and executed. The execution of the execution plan includes: for a first row of at least a subset of first sorted rows, scanning second rows from a set of second sorted rows, joining each of said second rows with said first row, and ceasing to scan when encountering a row from the second sorted rows that falls outside a bound of said band join condition. Techniques also include parallelizing a workload by overlapping the distribution of rows to the same slave process and computing cost and cardinality estimation for enhanced band join.

公开(公告)号：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.