Oracle International Corporation

Benjamin Schlegel

Pit Fender

Matthias Brantner

Hassan Chafi

Nipun Agarwal

Harshad Kasture

Alexander Ulrich

Laurent Daynes

Felix Schmidt

Bastian Hossbach

Sam Idicula

Martin Sevenich

Eric Sedlar

Sabina Petride

Seema Sundara

Cagri Balkesen

Anantha Kiran Kandukuri

Andrea Di Blas

Kantikiran Pasupuleti

Lucas Braun

Juergen Christ

Harshard Kasture

Approaches herein transparently delegate data access from a relational database management system (RDBMS) onto an offload engine (OE). The RDBMS receives a database statement referencing a user defined function (UDF). In an execution plan, the RDBMS replaces the UDF reference with an invocation of a relational operator in the OE. Execution invokes the relational operator in the OE to obtain a result based on data in the OE. Thus, the UDF is bound to the OE, and almost all of the RDBMS avoids specially handling the UDF. The UDF may be a table function that offloads a relational table for processing. User defined objects such as functions and types provide metadata about the table. Multiple tables can be offloaded and processed together, such that some or all offloaded tables are not materialized in the RDBMS. Offloaded tables may participate in standard relational algebra such as in a database statement.

Non-disruptive dynamic ad-hoc database catalog services

Techniques described herein propose a new RIDDecode operator in a QEP that uses ROWID lookup and fetch, instead of dictionary decoding, to retrieve decoded values, in order to reduce memory pressure and speed up processing.

ROWID decoding: an alternative to dictionary decoding

Unsorted sparse dictionary encodings are transformed into unsorted-dense or sorted-dense dictionary encodings. Sparse domain codes have large gaps between codes that are adjacent in order. Unlike spare codes, dense codes have smaller gaps between adjacent codes; consecutive codes are dense codes that have no gaps between adjacent codes. The techniques described herein are relational approaches that may be used to generate sparse composite codes and sorted codes.

Relational method for transforming unsorted sparse dictionary encodings into unsorted-dense or sorted-dense dictionary encodings

Techniques described herein propose a ROWID Elimination Rewrite that uses functional dependencies to substitute and/or eliminate ROWID pseudo-columns referenced in a query in order to reduce memory pressure and speed up processing.

ROWID elimination rewrite

The present invention relates to execution optimization of database queries. Herein are techniques for optimal execution based on query interpretation by translation to a domain specific language (DSL), with optimizations such as partial evaluation, abstract syntax tree (AST) rewriting, just in time (JIT) compilation, dynamic profiling, speculative logic, and Futamura projection. In an embodiment, a database management system (DBMS) that is hosted on a computer generates a query tree that represents a database query that contains an expression that is represented by a subtree of the query tree. The DBMS generates a sequence of DSL instructions that represents the subtree. The sequence of DSL instructions is executed to evaluate the expression during execution of the database query. In an embodiment, an AST is generated from the sequence of DSL instructions. In an embodiment, the DSL AST is optimally rewritten based on a runtime feedback loop that includes dynamic profiling information.

First Futamura projection in the context of SQL expression evaluation

A partial group by operator is a group by operator that implements a fallback mechanism. The fallback mechanism is triggered whenever memory pressure reaches a certain threshold. When the fallback mechanism is triggered, a row is included in an output of the partial group by operator without including an aggregation value for a grouping value for the row to an aggregation data structure. A final group by operator computes a final aggregate value of all results, including pre-grouped results and passed through results, from the partial group by operator.

Partial group by for eager group by placement query plans

Techniques are described for a vectorized queue, which implements a vectorized 'contains' function that determines whether a value is in the queue. A three-phase vectorized shortest-path graph search splits each expanding and probing iteration into three phases that utilize vectorized instructions: (1) The neighbors of nodes that are in a next queue are fetched and written into a current queue. (2) It is determined whether the destination node is among the fetched neighbor nodes in the current queue. (3) The fetched neighbor nodes that have not yet been visited are put into the next queue. According to an embodiment, a vectorized copy operation performs vector-based data copying using vectorized load and store instructions. Specifically, vectors of data are copied from a source to a destination. Any invalid data copied to the destination is overwritten, either with a vector of additional valid data or with a vector of nonce data.

Vectorized queues for shortest-path graph searches

Techniques are provided for lazy push optimization, allowing for constant time push operations. A d-heap is used as the underlying data structure for indexing values being inserted. The d-heap is vectorized by storing values in a contiguous memory array. Heapify operations are delayed until a retrieve operation occurs, improving insert performance of vectorized d-heaps that use horizontal aggregation SIMD instructions at the cost of slightly lower retrieve performance.

Lazy push strategies for vectorized D-Heaps

Herein are techniques that extend a software system to embed new guest programing languages (GPLs) that interoperate in a transparent, modular, and configurable way. In embodiments, a computer inserts an implementation of a GPL into a deployment of the system. A command registers the GPL, define subroutines for the GPL, generates a guest virtual environment, and adds a binding of a dependency to a guest module. In an embodiment, a native programing language invokes a guest programing language to cause importing intra- or inter-language dependencies. An embodiment defines a guest object that is implemented in a first GPL and accessed from a second GPL. In an embodiment, dependencies are retrieved from a virtual file system having several alternative implementation mechanisms that include: an archive file or an actual file system, and a memory buffer or a column of a database table.

Database modularization of pluggable guest languages

Techniques are provided for vectorizing Heapsort. A K-heap is used as the underlying data structure for indexing values being sorted. The K-heap is vectorized by storing values in a contiguous memory array containing a beginning-most side and end-most side. The vectorized Heapsort utilizes horizontal aggregation SIMD instructions for comparisons, shuffling, and moving data. Thus, the number of comparisons required in order to find the maximum or minimum key value within a single node of the K-heap is reduced resulting in faster retrieval operations.

Method for vectorizing heapsort using horizontal aggregation SIMD instructions

The manner in which tables are joined can affect the outcome of the query and database performance. Example types of join operations include semi-join and inner-join. The techniques described herein are approaches that may be used to substitute a semi-join operator with an inner-join operator and may be used to transform and optimize representations of queries.

Methods for substituting a semi-join operator with alternative execution strategies and selection methods to choose the most efficient solution under different plans

First futamura projection in the context of SQL expression evaluation

According to an embodiment, a method includes rewriting a particular query to generate a rewritten query. The particular query specifies a window function operator, a particular input to the window function operator, and an analytical function. Rewriting the particular query includes assigning the particular input to an intermediate relation and replacing the window function operator with a replacement operator. The method further includes assigning to the replacement operator an aggregate function corresponding to the analytical function, and the intermediate relation. In this embodiment, the method also includes placing a join operator that joins the intermediate relation with an output of the replacement operator.

Driving massive scale out through rewrites of analytical functions

Rowid decoding: an alternative to dictionary decoding

Herein are machine learning (ML) feature processing and analytic techniques to detect anomalies in parse trees of logic statements, database queries, logic scripts, compilation units of general-purpose programing language, extensible markup language (XML), JAVASCRIPT object notation (JSON), and document object models (DOM). In an embodiment, a computer identifies an operational trace that contains multiple parse trees. Values of explicit features are generated from a single respective parse tree of the multiple parse trees of the operational trace. Values of implicit features are generated from more than one respective parse tree of the multiple parse trees of the operational trace. The explicit and implicit features are stored into a same feature vector. With the feature vector as input, an ML model detects whether or not the operational trace is anomalous, based on the explicit features of each parse tree of the operational trace and the implicit features of multiple parse trees of the operational trace.

Growing community of inventors

Merced, CA, United States of America

Benjamin Schlegel