Oracle International Corporation

Srikanth Bellamkonda

Andrew Witkowski

Sankar Subramanian

Abhinav Gupta

Rafi Ahmed

Tolga Bozkaya

Huagang Li

Nathan K Folkert

Madhuri Kandepi

Benoit Dageville

Yi Pan

Bhaskar Ghosh

Lei Sheng

Manish Pratap Singh

Yu Su

Shuo Chen

Kavya Shankar

Yash Shah

Jianhua Liang

Skew handling techniques are provided in parallel execution for even load balancing and scaling. In a compile-time solution, a dynamic sampling query is issued to detect partition skew. The compile-time solution determines the number of skewed partitions and uses a hybrid distribution scheme where skewed partitions use a random distribution and non-skewed partitions use the original server mapping. In a runtime solution, producer server processes create partition mapping vectors that contain partition mapping information. Each producer server process sends its partition mapping vector to the query coordinator (QC). The QC receives the partition mapping vectors from the producer server processes, merges the vectors, and determines a skew result based on the merged mapping vectors and sends the skew result to the producer server processes. The producer server process can alter distribution of skewed partitions based on the skew result.

Handling data skew for parallel execution with server mapping plans

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)&#x2014;with associated join condition(s), correlation condition(s), and/or filter predicate(s)&#x2014;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.

Join-based containment for set operation-based sub query removal

Disclosed is a system, method, and computer program product to efficiently process multi-set operations in a database system. An approach is described to perform a group-by operation with a counter to efficiently process such queries. Techniques are described to optimize multi-set operations into regular-set operations.

Efficient optimization of SQL queries having set operators with a multi-set semantic

An approach for implementing function semantic based partition-wise SQL execution and partition pruning in a data processing system is provided. The system receives a query directed to a range-partitioned table and determines if operation key(s) of the query include(s) function(s) over the table partitioning key(s). If so, the system obtains a set of values corresponding to each partition by evaluating the function(s) on a low bound and/or a high bound table partitioning key value corresponding to the partition. The system may then compare the sets of values corresponding to different partitions and determine whether to aggregate results obtained by executing the query over the partitions based on the comparison. The system may also determine whether to prune any partitions from processing based on a set of correlations between the set of values for each partition and predicate(s) of the query including function(s) over the table partitioning key(s).

Function semantic based partition-wise SQL execution and partition pruning

Execution plans generated for multiple analytic queries incorporate two new kinds of plan operators, a partition creator and partition iterator. The partition creator and partition iterator operate as a pair. A partition creator operator creates partitions of rows and a partitioning descriptor describing the partitions created. A partition iterator iterates through the partitions based on the partitioning descriptor. For each partition, multiple analytic operators are executed serially, one after the other, on the same rows in the partition. According to an embodiment, partitioning is based on a common grouping or subgrouping of the multiple analytic functions or operators. Columns in the grouping or subgrouping may be ignored when executing each of the multiple analytic operators. Forming execution plans that include partition creator and partition iterator in this way is referred to herein as partitioning injection.

Efficient execution of a sequence of SQL operations using runtime partition injection and iterative execution

Techniques herein improve computational efficiency for parallel queries with run-time data pruning by using adaptive granule generation. In an embodiment, an execution plan is generated for a query to be executed by a plurality of slave processes, the execution plan comprising a plurality of plan operators. For a first plan operator of the plurality of plan operators, a first set of work granules is generated, and for a second plan operator of the plurality of plan operators, a second set of work granules is generated. A first subset of slave processes of the plurality of slave processes is assigned the first set of work granules. Based on the execution of the first set of work granules by the first subset of slave processes, a bloom filter is generated that specifies for which of said first set of work granules no output rows were generated. Based on the bloom filter, the second set of work granules is modified and the modified second set of work granules is assigned to a second subset of slave processes and executed.

Adaptive granule generation for parallel queries with run-time data pruning

Techniques herein generate a query plan that combines a global reporting aggregate calculation and an organizing operation. A method detects an organizing operation, a group aggregate function, and a global aggregate function within a database statement. The organizing operation specifies organizational activities such as grouping, joining, or sorting rows. The method generates an execution plan that specifies calculating all values in a single pass. For each row, the single pass applies the organizing operation and updates an access structure. The pass updates one of multiple cumulative group calculations based on the group aggregate function and updates a cumulative global calculation based on the global aggregate function. Each cumulative group calculation is associated with some of the access structure. Based on the access structure, result rows that satisfy the database statement are generated. Result rows contain a final result of each group calculation and a final result of the global calculation.

Fusing global reporting aggregate computation with the underlying operation in the query tree for efficient evaluation

According to embodiments, a multi-node database management system allows consumer processes ('consumers') implementing a portion of a distributed data-combination operation to independently send a STOP notification to corresponding producer processes ('producers'). Upon a given consumer determining that the consumer requires no further information from corresponding producers, the consumer sends a STOP notification to the producers. When a given consumer sends out a STOP notification, the producers drop any data destined for the given consumer and also stops preparing data for and sending rows to the given consumer. Furthermore, once the producers receive STOP notifications from all of the consumers corresponding to the producers, the producers stop the current sub plan execution immediately without requiring completion of the sub plan. Thus, embodiments significantly improve the query execution performance by avoiding scanning and distributing data that is not needed for execution of the distributed operation.

Optimized execution of queries involving early terminable database operators

Techniques are described herein for efficient set operation execution. According to some embodiments, a request is received to perform a set operation with respect to a first data set and a second data set. The request may identify the first data set, the second data set, and a type of set operation to perform. In response to receiving the request, a hash table is generated in memory from a first set of records in the first data set, and a second set of records from the second data set is probed against the hash table. Based on probing the hash table and the type of set operation identified in the request, records that satisfy the set operation are identified and output from the hash table.

Efficient set operation execution using a single group-by operation

A method, apparatus, and system for dynamic parallel aggregation with hybrid batch flushing are provided. Record sources of an aggregation operator in a query execution plan may dynamically aggregate using the same aggregation operator. The dynamic aggregation creates a batch of aggregation records from an input source, which are then used to aggregate further records from the input source. If a record from the input source is not matched to an aggregation record in the batch, then the record is passed to the next operator. In this manner, records are aggregated ahead of time at a record source to reduce the number of records passed between operators, reducing the impact of network I/O between nodes of a parallel processing system. By adjusting the contents of the batch according to aggregation performance monitored during run-time, hybrid batch flushing can be implemented to adapt to changing data patterns and skewed values.

Dynamic parallel aggregation with hybrid batch flushing

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.

Scalable and adaptive evaluation of reporting window functions

Techniques are described herein for efficiently evaluating database queries that include hierarchical cube computations. During second and subsequent evaluation phases (if any), a database server does not re-determine groups (nor re-aggregate within such groups) that have already been determined in a previous evaluation phase. Instead, according to a technique described herein, whenever an evaluation phase subsequent to the first evaluation phase is performed, the database server immediately outputs or otherwise returns certain groups and aggregate results that were determined based on certain grouping column sets that were generated in the previous evaluation phase. The database server does not aggregate within these certain groups when performing aggregation in the current evaluation phase, thereby avoiding the duplication of work already performed during previous evaluation phases.

Efficient evaluation of hierarchical cubes by non-blocking rollups and skipping levels

According to one aspect of the invention, for a database statement that specifies rollup operations, a data distribution key may be selected among a plurality of candidate keys. Numbers of distinct values of the candidate keys may be monitored with respect to a particular set of rows. Hash values may also be generated by column values in the candidate keys. The data distribution key may be determined based on results of monitoring the numbers of distinct values of the candidate keys as well as the frequencies of hash values computed based on column values of the candidate keys. Rollup operations may be shared between different stages of parallel executing processes and data may be distributed between the different stages of parallel executing processes based on the selected data distribution key.

Data-aware scalable parallel execution of rollup operations

According to one aspect of the invention, for a database statement that specifies evaluating ranking or cumulative window functions, an execution strategy based on an extended data distribution key may be used for the database statement. In the execution strategy, each sort operator of multiple parallel processing sort operators computes locally evaluated results of a ranking or cumulative window function based on a subset of rows in all rows used to evaluate the database statement, and sends the first and last rows' locally evaluated results to a query coordinator. The query coordinator consolidates the locally evaluated results received from the multiple parallel processing sort operators and sends consolidated results to the sort operators based on their respective demographics. Each sort operator completes full evaluation of the ranking or cumulative window functions based at least in part on one or more of the consolidated results provided by the query coordinator.

Enhancing parallelism in evaluation ranking/cumulative window functions

An improved approach is described for handling parallelization of window functions, particularly window functions that do not contain partition keys or which has low cardinality for the partition keys. The approach is highly scalable and can be used to greatly improve query processing. A two stage evaluation approach is employed to parallelize the processing of window functions. In the first stage, which is highly parallel, the majority of the computation of window function is done by all available processes. In this way, the entire computing power of the database server is utilized. The second stage, which is serial but is likely to be very short, all processes involved in first stage synchronize and complete the window function evaluation.

Growing community of inventors

Mountain View, CA, United States of America

Srikanth Bellamkonda