公开(公告)号：US20220309062A1

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

申请号：US17213034

申请日：2021-03-25

Applicant: Oracle International Corporation

Inventor： Rafi Ahmed ,  Srikanth Bellamkonda

IPC: G06F16/2453

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.

公开(公告)号：US10133776B2

公开(公告)日：2018-11-20

申请号：US13923216

申请日：2013-06-20

Applicant: Oracle International Corporation

Inventor： Rafi Ahmed ,  Mohamed Zait

IPC: G06F17/30

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.

公开(公告)号：US20150339349A1

公开(公告)日：2015-11-26

申请号：US14284248

申请日：2014-05-21

Applicant: Oracle International Corporation

Inventor： Rafi Ahmed ,  Gautam Kumar Parai

IPC: G06F17/30

CPC classification number: G06F17/30489 ,  G06F17/30466

Abstract: A method and apparatus for outer-joined and/or cross-joined table elimination for duplicate-insignificant queries is provided. A query block of a query specifies a join between a first table and a second table. The join is one of an outer join and a cross join. A first determination is made that the query block contains no references to the second table to be processed after the join. A second determination is made that the query block is duplicate-insignificant. In response to making the first determination and the second determination, the query block is transformed into a transformed query, where the second table is eliminated from a corresponding query block in the transformed query.

Abstract translation: 提供了用于重复查询的外连接和/或交叉连接表消除的方法和装置。 查询的查询块指定第一个表和第二个表之间的连接。 连接是外连接和交叉连接之一。 首先确定查询块不包含在连接之后要处理的第二个表的引用。 第二个确定是查询块是重复的 - 重要的。 响应于进行第一确定和第二确定，将查询块变换为经变换的查询，其中从转换查询中的相应查询块中删除第二表。

公开(公告)号：US12197439B2

公开(公告)日：2025-01-14

申请号：US17966036

申请日：2022-10-14

Applicant: Oracle International Corporation

Inventor： Rafi Ahmed ,  Angela Amor ,  Mohamed Ziauddin

IPC: G06F16/2453

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. >, >=,

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

公开(公告)号：US10572484B2

公开(公告)日：2020-02-25

申请号：US15454739

申请日：2017-03-09

Applicant: Oracle International Corporation

Inventor： Srikanth Bondalapati ,  Rafi Ahmed ,  Sankar Subramanian

IPC: G06F16/00 ,  G06F16/2455 ,  G06F16/2453

Abstract: Attributes and semantics of duplicate insignificance that are inherent or inferred in a database language statement are detected. Also, a join operation that is inherent or inferred in the database language statement is detected and examined for join semantics. The join semantics specifies or refers to a driving table to be subjected to a hash join operation that may populate one or more hash buckets. The optimizer and the execution layers may use cost estimation or heuristics to assign the left and right table roles to the tables involved in the join. The hash join operation removes left table duplicates during population of the hash buckets, resulting in full or partial duplicate elimination that occurs during the hash join operation.

公开(公告)号：US10558660B2

公开(公告)日：2020-02-11

申请号：US15658249

申请日：2017-07-24

Applicant: Oracle International Corporation

Inventor： Rafi Ahmed ,  Andrew Witkowski ,  Sankar Subramanian

IPC: G06F16/2453

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.

公开(公告)号：US09965516B2

公开(公告)日：2018-05-08

申请号：US14284248

申请日：2014-05-21

Applicant: Oracle International Corporation

Inventor： Rafi Ahmed ,  Gautam Kumar Parai

IPC: G06F17/30

CPC classification number: G06F17/30489 ,  G06F17/30466

Abstract: A method and apparatus for outer-joined and/or cross-joined table elimination for duplicate-insignificant queries is provided. A query block of a query specifies a join between a first table and a second table. The join is one of an outer join and a cross join. A first determination is made that the query block contains no references to the second table to be processed after the join. A second determination is made that the query block is duplicate-insignificant. In response to making the first determination and the second determination, the query block is transformed into a transformed query, where the second table is eliminated from a corresponding query block in the transformed query.

公开(公告)号：US09870390B2

公开(公告)日：2018-01-16

申请号：US14183409

申请日：2014-02-18

Applicant: Oracle International Corporation

Inventor： Rafi Ahmed ,  Nirav Vyas ,  Mohamed Zait

IPC: G06F17/30

CPC classification number: G06F17/30395 ,  G06F17/30451

Abstract: Methods, stored instructions, and computer systems are described for selecting an OR-expansion state of a query. A query processor accesses a certain query and a control parameter. The certain query comprises disjunctive predicates, and the control parameter indicates a certain procedure of a plurality of alternative stored procedures for expanding queries comprising disjunctive predicates into semantically equivalent candidate queries comprising combinations of subqueries. Based at least in part on the control parameter, the query processor selects the certain procedure to expand the certain query into one or more certain semantically equivalent candidate queries, each comprising a different combination of two or more subqueries. The query processor causes cost-based evaluation of subject queries, including the certain query and the one or more certain semantically equivalent candidate queries. The query processor then selects a query of the subject queries for execution or for further evaluation or transformation.