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

公开(公告)号：US20210109930A1

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

申请号：US16653639

申请日：2019-10-15

Applicant: Oracle International Corporation

Inventor： Sabina Petride ,  Mohamed Ziauddin ,  Praveen T.J. Kumar ,  Huagang Li ,  Andrew Witkowski ,  Sankar Subramanian

IPC: G06F16/2453 ,  G06F16/22 ,  G06F16/242 ,  G06F16/23

Abstract: Techniques are described for storing and maintaining, in a materialized view, bitmap data that represents a bitmap of each possible distinct value of an expression and rewriting a query for a count of distinct values of the expression using the materialized view. The materialized view contains bitmap data that represents a bitmap of each possible distinct value of a first expression, and aggregate values of additional expressions, and is stored in memory or on disk by a database system. The database system receives a query that requests a number of distinct values, of the first expression, and an aggregate value for an additional expression. In response, the database system, rewrites the query to: compute the number of distinct values by counting the bits in the bitmap data of the materialized view that are set to the first value, and obtains the aggregate value for the additional expression in the materialized view.

公开(公告)号：US10719516B2

公开(公告)日：2020-07-21

申请号：US16113633

申请日：2018-08-27

Applicant: Oracle International Corporation

Inventor： Venkatesh Sakamuri ,  Huagang Li ,  Sankar Subramanian ,  Andrew Witkowski

IPC: G06F16/00 ,  G06F16/2455 ,  G06F7/24

Abstract: A method and system for processing database queries containing aggregate functions. The query may specify fewer groups than there are processes available to process the queries. Further, the queries may target a set of rows and specify a sort-by key and a group-by key. The method and system further includes determining that the queries specify application of the aggregate function to each of a plurality of groups that may correspond to a plurality of distinct values of the group-by key and determining that plurality of processes are available to process the queries. The method and system also includes determining the plurality of ranges of a composite key that may be formed by combining the group-by key and the sort-by key and assigning each range of the plurality ranges to a corresponding process to calculate the aggregate function.

公开(公告)号：US20170116266A1

公开(公告)日：2017-04-27

申请号：US15268519

申请日：2016-09-16

Applicant: Oracle International Corporation

Inventor： Janaki Latha Lahorani ,  You Jung Kim ,  Andrew Witkowski ,  Sankar Subramanian

IPC: G06F17/30

CPC classification number: G06F16/24532 ,  G06F16/24539 ,  G06F16/24561

Abstract: Techniques are provided herein for processing a query using in-memory cursor duration temporary tables. The techniques involve storing a part of the temporary table in memory of nodes in a database cluster. A part of the temporary table may be stored in disk segments of nodes in the database cluster. Writer threads running on a particular node writes data for the temporary table to the memory of the particular node. Excess data may be written to the disk segment of the particular node. Reader threads running on the particular node reads data for the temporary table from the memory of the particular node and the disk segment of the particular node.

公开(公告)号：US20200065413A1

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

申请号：US16113633

申请日：2018-08-27

Applicant: Oracle International Corporation

Inventor： Venkatesh Sakamuri ,  Huagang Li ,  Sankar Subramanian ,  Andrew Witkowski

IPC: G06F17/30 ,  G06F7/24

Abstract: A method and system for processing database queries containing aggregate functions. The query may specify fewer groups than there are processes available to process the queries. Further, the queries may target a set of rows and specify a sort-by key and a group-by key. The method and system further includes determining that the queries specify application of the aggregate function to each of a plurality of groups that may correspond to a plurality of distinct values of the group-by key and determining that plurality of processes are available to process the queries. The method and system also includes determining the plurality of ranges of a composite key that may be formed by combining the group-by key and the sort-by key and assigning each range of the plurality ranges to a corresponding process to calculate the aggregate function.

公开(公告)号：US09390129B2

公开(公告)日：2016-07-12

申请号：US13754687

申请日：2013-01-30

Applicant: Oracle International Corporation

Inventor： Huagang Li ,  Srikanth Bellamkonda ,  Sankar Subramanian ,  Andrew Witkowski

IPC: G06F7/00 ,  G06F17/30

CPC classification number: G06F17/30433 ,  G06F17/30442 ,  G06F17/30471

Abstract: According to one aspect of the invention, for a database statement that specifies evaluating reporting window functions, a computation-pushdown execution strategy may be used for the database statement. The computation-pushdown execution plan includes producer operators and consolidation operators. Each producer operator computes a respective partial aggregation for each reporting window function based on a subset of rows, and broadcasts the respective partial aggregation. Each consolidation operator fully aggregates all partial aggregations broadcasted from the producer operators. Alternatively, an extended-data-distribution-key execution plan may be used. Each producer operator sends rows based on hash keys to sort operators for computing partial aggregations for at least one reporting window function based on a subset of rows. Each consolidation operator receives and fully aggregates all partial aggregations broadcasted from the sort operators.

Abstract translation: 根据本发明的一个方面，对于指定评估报告窗口功能的数据库语句，可以对数据库语句使用计算下推执行策略。 计算下推执行计划包括生产者运营商和合并运营商。 每个生成者操作者基于行的子集来计算每个报告窗口函数的相应部分聚合，并广播相应的部分聚合。 每个合并运营商完全聚合从生产者运营商广播的所有部分聚合。 或者，可以使用扩展数据分发密钥执行计划。 每个制作者操作员根据散列键发送行，对运算符进行排序，以便根据行的子集为至少一个报告窗口函数计算部分聚合。 每个合并运算符接收并完全聚合从排序运算符广播的所有部分聚合。

公开(公告)号：US20140214754A1

公开(公告)日：2014-07-31

申请号：US13754687

申请日：2013-01-30

Applicant: ORACLE INTERNATIONAL CORPORATION

Inventor： Huagang Li ,  Srikanth Bellamkonda ,  Sankar Subramanian ,  Andrew Witkowski

IPC: G06F17/30

CPC classification number: G06F17/30433 ,  G06F17/30442 ,  G06F17/30471

Abstract: According to one aspect of the invention, for a database statement that specifies evaluating reporting window functions, a computation-pushdown execution strategy may be used for the database statement. The computation-pushdown execution plan includes producer operators and consolidation operators. Each producer operator computes a respective partial aggregation for each reporting window function based on a subset of rows, and broadcasts the respective partial aggregation. Each consolidation operator fully aggregates all partial aggregations broadcasted from the producer operators. Alternatively, an extended-data-distribution-key execution plan may be used. Each producer operator sends rows based on hash keys to sort operators for computing partial aggregations for at least one reporting window function based on a subset of rows. Each consolidation operator receives and fully aggregates all partial aggregations broadcasted from the sort operators.

Abstract translation: 根据本发明的一个方面，对于指定评估报告窗口功能的数据库语句，可以对数据库语句使用计算下推执行策略。 计算下推执行计划包括生产者运营商和合并运营商。 每个生成者操作者基于行的子集来计算每个报告窗口函数的相应部分聚合，并广播相应的部分聚合。 每个合并运营商完全聚合从生产者运营商广播的所有部分聚合。 或者，可以使用扩展数据分发密钥执行计划。 每个制作者操作员根据散列键发送行，对运算符进行排序，以便根据行的子集为至少一个报告窗口函数计算部分聚合。 每个合并运算符接收并完全聚合从排序运算符广播的所有部分聚合。

公开(公告)号：US11468099B2

公开(公告)日：2022-10-11

申请号：US17068357

申请日：2020-10-12

Applicant: Oracle International Corporation

Inventor： Mohamed Ziauddin ,  You Jung Kim ,  Venkatesh Sakamuri ,  Sankar Subramanian

IPC: G06F16/28 ,  G06F16/22

Abstract: Techniques for the automatic creation and maintenance of zone maps are provided. In one technique, a set of data sets is identified. For each data set, a data set width is determined based on a maximum value in the data set and a minimum value in the data set. One or more zones within the data set are identified. For each zone, a zone width is determined based on a difference between a maximum value in that zone and a minimum value in that zone. An aggregate zone width is generated that is based on the zone width of each zone. Based on the data set width and the aggregate zone width, it is determined whether to automatically generate a zone map for the data set.

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