Abstract: 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.

Abstract: 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.

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: 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.

Abstract: 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.

Abstract: 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.

Abstract: 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.

Abstract: Methods for transforming queries that contain EXISTS and NOT EXISTS subqueries are provided. The methods provided transform original queries that contain EXISTS and/or NOT EXISTS subqueries in conjunctive or disjunctive combinations into transformed queries that coalesce the original EXISTS and/or NOT EXISTS subqueries into a reduced number of EXISTS and/or NOT EXISTS subqueries, thereby eliminating duplicative table accesses and join operations.

Abstract: 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.

Abstract: Techniques are described herein for supporting efficient evaluation of pivot operations. In an embodiment, a pivoting query on a data table is received. The query identifies pivot columns, measure columns, and grouping columns. In response to receiving the query, a staging table that comprises new pivot columns, aggregated columns, and new grouping columns may be created. For each particular unique combination of values of the grouping columns and the pivot columns in the data table, stored values in the measure columns in the data table are aggregated for certain rows in the plurality of rows in the data table. Each row of the certain rows comprises the each particular unique combination of values of the grouping columns and the pivot columns in the data table. Results of aggregating are stored in the staging table. Data that has been populated in the staging table is transposed into a result set.

Abstract: Methods for transforming a query to remove redundant subqueries in HAVING clauses are provided. The methods provided transform queries that contain subqueries in HAVING clauses with tables and join conditions and filter conditions equal to tables, join conditions and filter conditions in outer query to queries that eliminate the original subquery and retain the original outer query with a single inline view using window functions. Whether this transformation can be performed depends on which tables and join and filter conditions are in the outer query and the subquery. The transformation eliminates duplicative table accesses and join operations from queries.

Abstract: Described herein are optimizations and execution strategies for spreadsheet extensions to SQL. The partitioning of data, as specified in a spreadsheet clause, provides a way to parallelize the computation of spreadsheet and to provide and improve scalability. Even if the partitioning is not explicitly specified in the spreadsheet clause, the database optimizer can automatically infer the partitioning in some cases. Efficient hash based access structures on relations can be used for symbolic array addressing, enabling fast computation of formulas. When rewriting SQL statements, formulas whose results are not referenced in outer blocks can be removed from the spreadsheet clause, thus removing unnecessary computations. The predicates from other query blocks can be moved inside query blocks with spreadsheets clauses, thus considerably reducing the amount of data to be processed. Conditions for validity of this transformation are given.

Abstract: Described herein are optimizations and execution strategies for spreadsheet extensions to SQL. The partitioning of data, as specified in a spreadsheet clause, provides a way to parallelize the computation of spreadsheet and to provide and improve scalability. Even if the partitioning is not explicitly specified in the spreadsheet clause, the database optimizer can automatically infer the partitioning in some cases. Efficient hash based access structures on relations can be used for symbolic array addressing, enabling fast computation of formulas. When rewriting SQL statements, formulas whose results are not referenced in outer blocks can be removed from the spreadsheet clause, thus removing unnecessary computations. The predicates from other query blocks can be moved inside query blocks with spreadsheets clauses, thus considerably reducing the amount of data to be processed. Conditions for validity of this transformation are given.

Abstract: In a database system, a full outer join is computed using a hash-based join.

Abstract: Methods for transforming queries that contain EXISTS and NOT EXISTS subqueries are provided. The methods provided transform original queries that contain EXISTS and/or NOT EXISTS subqueries in conjunctive or disjunctive combinations into transformed queries that coalesce the original EXISTS and/or NOT EXISTS subqueries into a reduced number of EXISTS and/or NOT EXISTS subqueries, thereby eliminating duplicative table accesses and join operations.

Abstract: Techniques are described herein for supporting efficient evaluation of pivot operations. In an embodiment, a pivoting query on a data table is received. The query identifies pivot columns, measure columns, and grouping columns. In response to receiving the query, a staging table that comprises new pivot columns, aggregated columns, and new grouping columns may be created. For each particular unique combination of values of the grouping columns and the pivot columns in the data table, stored values in the measure columns in the data table are aggregated for certain rows in the plurality of rows in the data table. Each row of the certain rows comprises the each particular unique combination of values of the grouping columns and the pivot columns in the data table. Results of aggregating are stored in the staging table. Data that has been populated in the staging table is transposed into a result set.

Abstract: Methods for transforming a query to remove redundant subqueries in HAVING clauses are provided. The methods provided transform queries that contain subqueries in HAVING clauses with tables and join conditions and filter conditions equal to tables, join conditions and filter conditions in outer query to queries that eliminate the original subquery and retain the original outer query with a single inline view using window functions. Whether this transformation can be performed depends on which tables and join and filter conditions are in the outer query and the subquery. The transformation eliminates duplicative table accesses and join operations from queries.

Abstract: In a database system, a full outer join is computed using a hash-based join.

Abstract: 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.

Abstract: Hash aggregation operates in two phases. In the first phase, data read from underlying row source is partitioned on GROUP BY keys with on-the-fly aggregation. It is determined into which partition an entry corresponding to the incoming record should be grouped, and whether a matching entry is already present in the partition, using a hash function and a hash table built for the partition. For aggregation operations, if a match is found, then a measure value from the incoming record is aggregated with a measure value for the entry existing in the partition. If there is not enough system volatile memory to complete the first phase, then a partition is selected for storing persistently. Thus, only one partition at a time is stored persistently, as necessary. In the second phase, one persistently stored partition is processed at a time, whereby aggregation of the data in that partition is completed and results are returned.