Oracle International Corporation

Lei Sheng

Andrew Witkowski

Hassan Chafi

Zhen Hua Liu

Sungpack Hong

Laurent Daynes

Vlad Ioan Haprian

Marco Arnaboldi

Hugo Kapp

Sankar Subramanian

Jean-Pierre Lozi

Atif Chaudhry

Abhinav Gupta

Rafi Ahmed

Srikanth Bellamkonda

Chandrasekharan Iyer

Nathan K Folkert

Shashaanka Agrawal

Agrawal Shashaanka

Disclosed is an approach to define non-SQL functions as a SQL macro, which is then processed inline with SQL statements. By inlining the macro into SQL, this approach avoids cross-engine context switches as well as making it possible for a SQL query optimizer to generate better plans that execute more efficiently.

Method and system to implement SQL macros

Techniques support graph pattern matching queries inside a relational database management system (RDBMS) that supports SQL execution. The techniques compile a graph pattern matching query that includes a bounded recursive pattern query into a SQL query that can then be executed by the relational engine. As a result, techniques enable execution of graph pattern matching queries that include bounded recursive patterns on top of the relational engine by avoiding any change in the existing SQL engine.

Efficient compilation of bounded recursive graph queries on top of SQL based relational engine

Techniques support graph pattern matching queries inside a relational database management system (RDBMS) that supports SQL execution. The techniques compile a graph pattern matching query into a SQL query that can then be executed by the relational engine. As a result, techniques enable execution of graph pattern matching queries on top of the relational engine by avoiding any change in the existing SQL engine.

Efficient compilation of graph queries including complex expressions on top of sql based relational engine

Efficient compilation of graph queries on top of SQL based relational engine

Efficient compilation of graph queries involving long graph query patterns on top of SQL based relational engine

Described is an approach for performing an efficient processing of table functions/polymorphic table functions. The approach does not create additional cursors for processing a table or polymorphic table function invoked within a SQL statement. Instead, the approach handles the SQL statement that invokes the table/polymorphic table function as a single cursor.

Method and system for efficient processing of polymorphic table functions

Techniques herein optimize sort-merge join method for a band join. In an embodiment, for a query comprising a query block specifying a join between a first table and a second table, a band join condition is detected between the first table and the second table. Once the band join condition in detected, an execution plan is generated and executed. The execution of the execution plan includes: for a first row of at least a subset of first sorted rows, scanning second rows from a set of second sorted rows, joining each of said second rows with said first row, and ceasing to scan when encountering a row from the second sorted rows that falls outside a bound of said band join condition. Techniques also include parallelizing a workload by overlapping the distribution of rows to the same slave process and computing cost and cardinality estimation for enhanced band join.

Sort-merge band join optimization

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.

Efficient evaluation of SQL pivot operations

Query rewrite techniques are provided for rewriting an aggregate query with multiple groupings using multiple summary tables. The rewrite is accomplished by expanding the aggregate query into an equivalent UNION ALL query with multiple subqueries, each containing a single grouping. One or more of the subqueries is then rewritten to reference a summary table. Thus, a grouping of an aggregate query is derived by executing a subquery that either scans the summary table for the grouping, like in the exact-match mode, or by performing a roll-up operation, like in the roll-up mode. In addition, if the grouping of some of the subqueries cannot be derived from a summary table, subqueries are combined into another subquery that computes groupings from the base tables of the aggregate query.

Union all rewrite for aggregate queries with grouping sets

A method and apparatus for densification of data is provided. A method and apparatus for including a construct for partitioning data within a DML statement is provided. The construct for partitioning data may not necessarily perform a densification of data, and the densification of data may not necessarily include a construct for partitioning data. In an embodiment, the syntax of an OUTER JOIN is extended to include a PARTITION BY construct that may be used for data densification.

Growing community of inventors

Foster City, CA, United States of America

Lei Sheng