Oracle International Corporation

Huagang Li

Sankar Subramanian

Andrew Witkowski

Srikanth Bellamkonda

Angela Amor

Qingyuan Kong

Hassan Chafi

Sungpack Hong

Mohamed Ziauddin

Laurent Daynes

Sabina Petride

Vlad Ioan Haprian

Hugo Kapp

Praveen T J Kumar

Vinita Subramanian

Ioannis Alagiannis

Chun-Chieh Lin

Venkatesh Sakamuri

A breadth first search (BFS) algorithm is provided that uses out-of-core external storage in a memory constrained system. Memory resources are used as long as they are available and external storage is used when necessary due to memory pressure. The BFS algorithm uses a disk-spilling hash-table (DSH) as the visited set and disk-spilling queues (DSQs) as the BFS frontier queue. To get the most out of the DSH, subsequent inserts and lookups must happen in the same DSH partition. To ensure that consecutive lookups happen in the same DSH partition, the BFS frontier queue is partitioned in a manner similar to the DSH partitions.

Out-of-core BFS for shortest path graph queries

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.

Bitmap-based count distinct query rewrite in a relational SQL algebra

Techniques are described for parallel processing of database queries with an inverse distribution function by a database management system (DBMS). To improve the execution time of a query with an inverse distribution function, the data set referenced in the inverse distribution function is range distributed among parallel processes that are spawned and managed by a query execution coordinator process (QC), in an embodiment. The parallel executing processes sort each range of the data set in parallel, while the QC determines the location(s) of inverse distribution function values based on the count of values in each range of the data set. The QC requests the parallel processes to produce to the next stage of parallel processes the values at the location(s) in the sorted ranges. The next stage of parallel processes computes the inverse distribution function based on the produced values. Techniques are also described for parallel executing of queries that may additionally include another inverse distribution function, one or more non-distinct aggregate functions and one or more distinct aggregate functions.

Parallel processing of queries with inverse distribution function

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.

Scalable multistage processing of queries with percentile functions

Techniques are described for leveraging column dictionaries of tables for join, group-by and expression evaluation operations. In an embodiment, a table is stored in one or more data units, each data unit's metadata containing dictionaries for stored columns. Rather than storing unencoded column values, the data units may store columns as column vectors of dictionary-encoded values, in an embodiment. When performing a join operation, a matching of values may be performed on the build-side table using the unencoded, unencoded, values stored in the join-key dictionary(s) of the probe-side table, thus, significantly reducing the number of searching and matching operations. In an embodiment, a group-by operation may be executed by performing partial aggregations based on unique group-by key values as stored in the one or more group-by key dictionaries. For an expression evaluation, only a single evaluation may be performed for each unique combination of expression-key values in a data unit by leveraging the one or more expression-key dictionaries.

Leveraging columnar encoding for query operations

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

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.

Growing community of inventors

San Jose, CA, United States of America

Huagang Li