Oracle International Corporation

Memsql, Inc.

Rajkumar Sen

Nipun Agarwal

Sam Idicula

Sabina Petride

Khai Tran

Samir Jindel

Jack Chen

Techniques for performing database operations using vectorized instructions are provided. In one technique, it is determined whether to perform a database operation using one or more vectorized instructions or without using any vectorized instructions. This determination may comprise estimating a first cost of performing the database operation using one or more vectorized instructions and estimating a second cost of performing the database operation without using any vectorized instructions. Multiple factors that may be used to determine which approach to follow, such as the number of data elements that may fit into a SIMD register, a number of vectorized instructions in the vectorized approach, a number of data movement instructions that involve moving data from a SIMD register to a non-SIMD register and/or vice versa, a size of a cache, and a projected size of a hash table.

Performing database operations using a vectorized approach or a non-vectorized approach

A received query is analyzed to provide a set of potential bushy tree operation plans that include the creation of one or more 'sub-joins' that provide intermediate resultant 'sub-tables', and using these sub-tables as operands in the other predicates of the query. The received query is analyzed to identify &#x201c;satellite&#x201d; tables and &#x201c;seed&#x201d; tables. A satellite table is a table of the database that is filtered by a selective predicate, and is connected to only one other table in the database via at least one linking predicate, and a seed table is a table of the database that is connected to a satellite table and at least one other table. Multiple candidate operation plans are formulated as bushy trees that include a sub-join of each seed table and its connected satellite table. A least cost operation plan is selected for execution from among these candidate operation plans and the conventional least cost linear operation plan based on the costs associated with each operation plan.

Bushy joins to improve computer efficiency executing queries

Techniques are described to evaluate an operation from an execution plan of a query to offload the operation to another database management system for less costly execution. In an embodiment, the execution plan is determined based on characteristics of the database management system that received the query for execution. One or more operations in the execution plan are then evaluated for offloading to another heterogeneous database management system. In a related embodiment, the offloading cost for each operation may also include communication cost between the database management systems. The operations that are estimated to be less costly to execute on the other database management system are then identified for offloading to the other database management system. In an alternative embodiment, the database management system generates permutations of execution plans for the same query, and similarly evaluates each permutation of the execution plans for offloading its one or more operations. Based on the total cost of each permutation, which may include offloading cost for one or more operations to another database management system, the least costly plan is selected for the query execution.

Multi-system query execution plan

Techniques for performing database operations using vectorized instructions are provided. In one technique, a hash table build phase involves executing vectorized instructions to determine whether a bucket in a hash table includes a free slot for inserting a key. A number of data elements from the bucket are loaded in a register. A vectorized instruction is executed against the register may be used to determine a position, within the register, that contains the 'smallest' data element. If the data element at that position is zero (or negative), then it is determined that the corresponding position in the bucket is an available slot for inserting a key and corresponding data value.

Building a hash table

Techniques for performing database operations using vectorized instructions are provided. In one technique, a hash table probe phase involves executing vectorized instructions to determine where in a bucket a particular key is located. This determination may be preceded by one or more vectorized instructions that are used to determine whether the bucket contains the particular key.

Probing a hash table using vectorized instructions

Techniques for performing database operations using vectorized instructions are provided. In one technique, data compaction is performed using vectorized instructions to identify a shuffle mask based on matching bits and update an output array based on the shuffle mask and an input array. In a related technique, a hash table probe involves using vectorized instructions to determine whether each key in one or more hash buckets matches a particular input key.

Data compaction using vectorized instructions

Estimating a cost of performing database operations using vectorized instructions

Techniques for performing database operations using vectorized instructions are provided. In one technique, an aggregation operation involves executing vectorized instructions to update a data value that corresponds to a particular key. The aggregation operation may be one of count, sum, minimum, maximum, or average.

Performing an aggregation operation using vectorized instructions

Building a hash table using vectorized instructions

A system and method for performing a query operation on a pair of relations in a database system coupled to a heterogeneous system (HS) is disclosed. Assuming that that pair of relations is partitioned and already loaded into the HS, the database system receives a query on the pair of relations and based on the type of query operation computes the cost of performing the query operation on the database alone or the costs of performing the query operation with the assistance of the HS, each of the costs corresponding to a particular algorithm. If the costs indicate that the HS improves the performance of the query operation, then the HS computes portions of the operation, and returns the results back to the database system. If any parts of the relation are out of sync with the database system, the database system performs operations to maintain transactional consistency.

Growing community of inventors

Foster City, CA, United States of America

Rajkumar Sen