Techniques for Heterogeneous Hardware Execution of SQL Analytical Queries for High-Volume Data Processing

Abstract

The present invention relates to optimized access to databases. This document is about techniques for accelerating the execution of any combination of self-organizing queries, heterogeneous hardware, and fluctuating workloads. In an embodiment, a computer receives a data access request for a data tuple and compiles the data access request into relational operators. A particular implementation of a particular relational operator is dynamically selected from a plurality of interchangeable implementations. Each interchangeable implementation includes a corresponding physical operator. A particular hardware operator for a particular physical operator is selected from a plurality of interchangeable hardware operators, including: a first hardware operator executed on a first processing hardware, and a second hardware operator executed on a second processing hardware, where the second processing hardware is functionally different from the first processing hardware. A response to the data access request is generated based on: the data tuple, the particular implementation of the particular relational operator, and the particular hardware operator.

Description

Technical Field

[0001] The present invention relates to optimized access to databases. This document is about techniques for accelerating the execution of any combination of self-organizing queries, heterogeneous hardware, and fluctuating workloads. Background Art

[0002] In a relational database system, a query can be defined using relational algebra, and a query plan can be compiled and represented as a binary tree composed of relational operators. Subsequently, at the execution stage, database data is traversed through different relational operators to calculate the result. At runtime, a single row in a set of rows (such as a relational table) is separately fetched from the sub-relational operators in the tree and returned to the parent relational operator. In some cases, rows are transmitted between relational operators in batches of several hundred rows.

[0003] Relational operators are coarse-grained logical units that should be prepared to handle hundreds of millions of sub-use cases to support all potential use cases that are combinatorially possible due to relational schema changes and data value distributions. The decision of which of many control flow branches to take for a specific row in millions of rows is made partly at compile time using static plan optimization and partly at runtime using a self-organizing data-driven part. The technical problem is that the algorithms of relational operators must fit all use cases, including those that do not occur for the current query on the current data and those that never occur in a given database server. Whether using batch processing or not, the relational operator tree method results in inefficient data processing for large amounts of data.

[0004] Existing database systems can support structured query language (SQL) analytical queries on big data by vertically partitioning the data, such as in columnar databases that can encode or not encode and compress columns of relational tables. Thus, thousands or millions of rows of data in the same column are stored together and are ready to benefit from vectorized processing. However, the relational operator tree method in the SQL execution engine only has data with column-uniform encoding in the base table scan, and then the relational operator of the table scan eagerly and completely decodes this data and converts the columns back to row-major data for analysis. This is at best adapting the old-fashioned row processing model to modern hardware. Generally speaking, such methods involve data structures, formats, and algorithms designed for outdated computing styles and hardware. Brief Description of the Drawings

[0005] In the drawings:

[0006] Figure 1is a block diagram depicting an example database management system (DBMS) that dynamically selects a specialized implementation from several interchangeable implementations of the same relational operator based on available hardware and fluctuating conditions to be invoked during the execution of a database query;

[0007] Figure 2 is a flowchart depicting an example query compilation process that dynamically selects a specialized implementation from several interchangeable implementations of the same relational operator based on available hardware and fluctuating conditions to be invoked during the execution of a database query;

[0008] Figure 3 is a block diagram depicting an example relational algebra analysis tree and two alternative example result sets for the same example Structured Query Language (SQL) query.

[0009] Figure 4 is a flowchart depicting an example query executed by the DBMS, which includes an example transpose operator that applies matrix transpose to a relational table or other set of rows.

[0010] Figure 5 is a block diagram of an example directed acyclic graph (DAG) of physical operators for an example physical plan of a query, where the physical operators are easy to port to different hardware architectures and can be opportunistically offloaded to different hardware (such as one or more coprocessors).

[0011] Figure 6 is a flowchart depicting an example process that the DBMS can use to plan and optimize a data flow including data structures such as hash tables for hash joins.

[0012] Figure 7 is a flowchart depicting an example DAG optimization process that the DBMS can use to plan, optimize, and execute a DAG of physical operators and / or a DAG of hardware operators, such as for executing data access requests.

[0013] Figure 8 is a block diagram depicting three types of indirect gather specialized for corresponding scenarios.

[0014] Figure 9 is a block diagram depicting two types of segmented gather.

[0015] Figure 10 is a block diagram illustrating a computer system on which embodiments of the present invention can be implemented;

[0016] Figure 11 is a block diagram illustrating a basic software system that can be used to control the operation of a computing system. Detailed Description

[0017] In the following description, for purposes of explanation, numerous specific details are set forth in order to provide a thorough understanding of the present invention. It will be apparent, however, that the present invention may be practiced without these specific details. In other instances, well-known structures and devices are shown in block diagram form in order to avoid unnecessarily obscuring the present invention.

[0018] General Overview

[0019] This document describes novel execution techniques for relational queries, including a set of hardware-friendly operations, herein referred to as physical operators, which are more fine-grained than known relational operators but are hardware-independent and can accept columnar data to increase throughput. Each relational operator can be represented as a data flow network of physical operators. Each physical operator represents the smallest beneficial unit of work that can be easily mapped to hardware for processing, as described herein.

[0020] Important characteristics of physical operators include the following design dimensions that affect query planning and optimization, as discussed herein.

[0021] · Based on how physical operators are scheduled, there are four types: blocking, pipelined, cached, and fusible;

[0022] · Based on space consumption, some operators increase memory requirements, such as when the output size is much larger than the input, and some operators instead free memory for recycling, such as by discarding intermediate data that is no longer needed;

[0023] · Based on the main resource requirements of physical operators, such as compute-bound versus bus / memory bandwidth-bound.

[0024] Optimization requires tuning the above dimensions to balance the above issues based on the presence of heterogeneous hardware and dynamic conditions such as fluctuating workloads and data value distributions. This optimization and how the methods herein decompose each relational operator fundamentally change how a database management system (DBMS) processes queries to promote increased efficiency of the DBMS computer itself and other performance improvements, including the following benefits.

[0025] By decomposing relational operators into physical operators, the query plan is transformed into a directed acyclic graph (DAG) with more fine-grained operators and more data flow path interconnections between operators. This exposes more optimization opportunities, including opportunities within and between operators.

[0026] Each physical operator is further tuned for each hardware platform by mapping to hardware operators that execute directly on the hardware to facilitate complex optimizations, such as the following artificial intelligence (AI) optimization activities.

[0027] · Offline collect characteristics and metrics to model different workload scenarios, and use historical data to train a machine learning (ML) model for optimal tuning of operator configuration settings (such as batch size);

[0028] · At runtime, the ML model observes the current workload and predicts the optimal values for the configuration settings of each physical operator;

[0029] · The results of the current run will be used to further train the offline model.

[0030] Because a query is first decomposed into physical operators as smaller work units, and depending on the characteristics of the interconnected physical operators (such as blocking, pipelining, caching, and fusibility discussed herein), there is a huge solution space for the possible execution graph for which the optimizer / scheduler can be resource-aware, such as according to the fluctuating conditions and heterogeneous types, amounts, and capacities of the available hardware.

[0031] As described herein, awareness of the fluctuating conditions and the diversity of the hardware promotes highly opportunistic optimization. When executed on different platforms, even during the same execution of a query, different optimization techniques herein can be used for the same physical operator. The uniformity of the physical operator interface promotes reusable optimization heuristics even if the DBMS has heterogeneous hardware, thus encouraging various levels and / or pipelining parallelism, such as offloading operators to different coprocessors.

[0032] For example, known SQL processing is only decomposed into relational operators. Thus, regardless of how the compiler chooses, the entire operator and / or operator tree is optimized purely for the central processing unit (CPU). The fine-grained physical operators herein are individually optimized for heterogeneous methods, such as single instruction multiple data (SIMD) instructions and / or coprocessor offloading, especially for columnar data.

[0033] For example, in this document, a join relational operator can be decomposed into multiple finer-grained operations, such as hashing, building a hash table, building a Bloom filter, probing the hash table, and / or aggregating fields from the hash table (such as for projection from the hash table). Some of these operations can be further decomposed. For example, the building of the hash table is further decomposed into decoding of join key values, partitioning of rows based on join key values, inserting join key values and accompanying other values into the buckets of the hash table, and handling of overflows. The physical operators for all those activities can be interconnected to achieve pipelining parallelism and / or horizontal scaling, such as with symmetric multiprocessing (SMP). The fine-grained operators provide more decomposition, more decoupling, and thus more asynchrony for load balancing and offloading, thereby increasing throughput.

[0034] In an embodiment, a computer receives a data access request for a data tuple and compiles the data access request into a relational operator. A particular implementation of the particular relational operator is dynamically selected from a plurality of interchangeable implementations. Each interchangeable implementation includes a corresponding physical operator. A particular hardware operator for the particular physical operator is selected from a plurality of interchangeable hardware operators, including: a first hardware operator executed on a first processing hardware, and a second hardware operator executed on a second processing hardware that is functionally different from the first processing hardware. A response to the data access request is generated based on: the data tuple, the particular implementation of the particular relational operator, and the particular hardware operator.

[0035] 1.0 Example Computer

[0036] Figure 1 is a block diagram depicting an example database management system (DBMS) 100 in an embodiment. The DBMS 100 dynamically selects a specialized implementation from a plurality of interchangeable implementations of the same relational operator based on available hardware and fluctuating conditions to be invoked during the execution of a database query. The DBMS 100 can be hosted on one or more computers, such as rack servers (such as blade servers), personal computers, mainframes, virtual computers, or other computing devices. When hosted by multiple computers, the computers are interconnected via a communication network.

[0037] In various embodiments, the DBMS 100 stores and provides access to a large-capacity data repository, such as a relational database, a graph database, a NoSQL database, a column data repository, a tuple data repository (such as a Resource Description Framework (RDF) triple store), a key-value data repository, or a document data repository (such as for documents containing JavaScript Object Notation (JSON) or Extensible Markup Language (XML)). In any case, the data repository can be managed by software such as an application or middleware such as the DBMS 100. The data stored in the data repository can reside in volatile and / or non-volatile storage devices.

[0038] In this example, the data is organized as multi-field tuples 121 - 122. In this example, the fields can have a logical and / or physical tabular arrangement such that the fields are arranged with one record per row and one field per column. For example, each of the tuples 121 - 122 can be a row in a relational table, a column family, or an internal row set such as an intermediate result.

[0039] In operation, the DBMS 100 receives or generates a data access request 110 to read and / or write data in a data repository. In an embodiment, the data access request 110 is expressed as a data manipulation language (DML), such as a create read update delete (CRUD) statement or a query by example (QBE). For example, the data access request 110 can be a structured query language (SQL) DML statement, such as a query. In an embodiment, the data access request 110 is received via an open database connection (ODBC).

[0040] The DBMS 100 compiles or otherwise interprets the data access request 110 based on generalized operators that separately examine, rearrange, modify, transmit, or otherwise process large chunks of data such as tuples 121 - 122 or derived data such as intermediate results below. In an embodiment, the DBMS 100 compiles the data access request 110 into a query plan that is arranged as a logical tree (not shown) of relational operators 131 - 132.

[0041] In an embodiment, the logical tree is derived by decorating or otherwise transforming a parse tree generated from the data access request 110. In a decorating embodiment, each node of the parse tree can be bound to a corresponding relational operator. In an embodiment, the data access request 110 is executed by interpreting or otherwise executing the generated query plan.

[0042] In an embodiment, the relational operators 131 - 132 are generalized logical operators that are more or less directly related to the operations specified by the data access request 110. For example, the relational operators 131 - 132 can be relational algebra operators. For example, the relational operator 131 can represent a relational table scan or a relational join of two relational tables.

[0043] According to the techniques herein, the relational operators 131 - 132 are generalized logical operators, each logical operator being composed of one or more physical operators that are more fine - grained than relational algebra. For example, the relational operator 131 includes physical operators 161 - 162 that are used in combination to provide a relational algebra operation, such as a join. For example, a relational join can be decomposed into a physical operator 161 as a build operation and a physical operator 162 as a probe operation, as discussed later herein.

[0044] In other words, a compiled query plan based on multiple physical operators for each of the relational operators 131-132 can have more nodes and be more complex than a relational algebra analysis tree. For example, as explained later in this document, a query plan based on many physical operators of the relational operators 131-132 can be a directed acyclic graph (DAG) rather than a tree. The complexity resulting from more, finer-grained, and more interconnected operators provides more opportunities for query plan optimization compared to a relational algebra parse tree, as discussed later in this document. For example, a more efficient data flow can be based on copying less data and / or fusing operators, as discussed later in this document.

[0045] The relational operators 131-132 are generalized and can be somewhat abstract, such that provision of the relational operators 131-132 may require or benefit from specialized implementations, such as vector acceleration using special hardware such as a graphics processing unit (GPU). For example, the relational operator 131 can be equipped with any one of interchangeable implementations 131A-B, which can be specialized differently for different conditions, such as: a) the fluctuating workload of the DBMS 100, b) the available hardware, and / or c) the schematic details of the tuples 121-122 and / or the data value distribution.

[0046] Each of the relational operators 131-132 is composed of the same or different amounts of physical operator(s). None, some, or all of the physical operators of one relational operator can be the same as those of another relational operator. For example, both implementations 131A-B include the physical operator 161, which means that the physical operator 161 has two events or instances that can be the same or slightly different in configuration.

[0047] An important configuration of a physical operator is which hardware operator will execute for the physical operator. That is, each instance of the same physical operator has a corresponding hardware operator, and different instances of the same physical operator can have the same or different hardware operators. For example, the two shown instances of the physical operator 161 can have corresponding instances of the same hardware operator 161B. Alternatively, one instance of the physical operator 161 can have the hardware operator 161B as shown, while another instance of the physical operator 161 can instead have the hardware operator 161A.

[0048] Unlike relational or physical operators (both of which deal with generalization), hardware operators are actually executable, but only on specific hardware. For example, hardware operator 161B can contain machine instructions that can only run on a central processing unit (CPU) that supports a specific instruction set architecture (ISA). For example, hardware operators 161A - B can be executed on a GPU and a CPU respectively. If the DBMS 100 lacks a GPU, then hardware operator 161A will not be available in that case, causing the DBMS 100 not to use hardware operator 161A for any physical operator and not to generate or otherwise select any implementation that includes hardware operator 161A.

[0049] Query plan optimization in this document includes dynamically selecting specialized implementations from several interchangeable implementations of the same relational operator based on fluctuating conditions. For example, the DBMS 100 should dynamically select any one of the most efficient implementations 131A - B for relational operator 131. In the embodiments discussed later in this document, this dynamic selection is cost - based according to various computer resources such as processing hardware, processing time, and / or memory space.

[0050] Relational operator 132 also has interchangeable implementations 132A - B. Thus, as discussed later in this document: a) the more relational operators appear in the initial query plan, b) the more interchangeable implementations each operator has, and c) the more alternative processing hardware is available, then the more comparable query plans there are due to combinatorics. For example, the techniques in this document can facilitate thousands or millions of different but equivalent query plans for the same data access request 110.

[0051] As explained later in this document, cost calculation can help (a) avoid the generation and / or consideration of inefficient query plans and / or (b) select the best plan among many candidate query plans. Implementations 131B and 132A and hardware operator 161A are shown in dashed lines to indicate that they are not generated or otherwise selected in this example. That is, in this example, implementations 131B and 132A, physical operators 161 - 162, and hardware operator 161B are selected and used in the optimized query plan for actually executing data access request 110.

[0052] All relational operators, their implementations (such as 131AB and 132A-B), physical operators, and hardware operators can be reused directly or through instantiation (such as from a template) and can thus be incorporated into different execution plans for the same or different queries. For example, the DBMS 100 can have a library of predefined hardware operators, predefined physical operators, declarations of which hardware operators can be used for which physical operators, and / or relational operators and their implementations (such as 131A-B and 132A-B). In an embodiment, predefined relational operator implementations such as 131A-B and 132A-B include a predefined binding of a hardware operator to each instance of a physical operator.

[0053] An embodiment can have templatized predefined relational operator implementations that contain physical operators but not hardware operators, such that the DBMS 100 eagerly or lazily generates exact relational operator implementations (such as 131A-B and 132A-B) by assigning hardware operators to instances of physical operators. In that way, the templatized predefined relational operator implementations and their physical operators are hardware independent and fully portable, such as across different instruction set architectures and processing methods, such as GPUs versus CPUs.

[0054] In other words, when the DBMS 100 has heterogeneous hardware such as a mix of GPUs and CPUs, such operator components herein are readily adaptable to new hardware to accommodate the future and seamlessly take advantage of hardware diversity. Thus, such operator components herein can use distributed processing to increase processing bandwidth and throughput through horizontal and / or vertical scaling and / or offloading (such as pushing filtering down to a storage computer providing data persistence, such as with intelligent scans).

[0055] Because the query plan of the DBMS 100 includes a dynamic selection of relational operator implementations such as 131A based on fluctuating conditions, including a dynamic selection of hardware operators, the DBMS 100 can perform load balancing. For example, a GPU is typically the fastest way to execute physical operator 161, but if the GPU is currently too busy, then the DBMS 100 can instead use the CPU by selecting a relational operator implementation for physical operator 161 that uses a CPU hardware operator. For example, relational operator implementation 132A-B can have exactly the same set of physical operators and differ only in one, some, or all of the hardware operators.

[0056] In fact, the ultimate difference between one relational operator implementation and all other implementations of the same and different relational operators is that each relational operator implementation has a different set of hardware operators, or at least a different hardware operator directed acyclic graph. In any case, any relational operator, their implementations (such as 131A-B and 132A-B), physical operators, and hardware operators can accept configuration settings and data inputs to facilitate or tune operator execution.

[0057] As discussed later herein, operators are interconnected in a data flow graph such that data such as columnar data flows from the data output of an upstream operator to the data input of a connected downstream operator. Thus, each operator has (one or more) inputs and (one or more) outputs of data.

[0058] Each operator instance also has configuration settings that are set when the operator instance is generated and generally not adjusted subsequently. Topological details such as which upstream operators provide which inputs and which downstream operators receive the outputs can be configuration settings of the operator instance. Additional configuration settings affect the semantics or efficiency of the operator instance. For example, the buffer size can be a configuration setting, and the buffer memory address can be a data input. Thus, even instances of the same operator can be distinguished by their configuration settings, the data they receive, and their position within a query plan (such as within the DAG of the operator).

[0059] After query compilation and planning is the actual execution of the data access request 110. In an embodiment, the query plan and optimization can ultimately generate or select a DAG consisting only of hardware operators that specify all the activities required to fully execute the data access request 110. For example, instances of relational operators and their implementations (such as 131A), while important for the query plan, can be discarded, or if predefined, saved for reuse when the optimized DAG of hardware operators is selected for actual execution.

[0060] For example, the query plan may need to generate a parse tree of relational operators 131-132, a DAG of physical operators, and a DAG of hardware operators. While the actual execution only requires the DAG of hardware operators, which can be discarded or saved for reuse after the actual execution of the data access request 110. In any case, this optimized preparation and subsequent execution (including data flow and control flow) from the data access request 110 and tuples 121-122 to the response 150 through hardware operators is as follows, including the interpretation of the response 150.

[0061] 2.0 Example Query Compilation Process

[0062] Figure 2is a flowchart depicting an example query compilation process that the DBMS 100 executes to dynamically select a specialized implementation from several interchangeable implementations of the same relational operator based on fluctuating conditions to be invoked during database query execution.

[0063] Step 201 receives a data access request 110 for tuples 121 - 122, such as via an SQL query like ODBC discussed earlier in this document.

[0064] Step 202 compiles the data access request 110 into relational operators 131 - 132, such as by parsing the data access request 110 into a parse tree according to the relational algebra discussed earlier in this document.

[0065] Step 203 dynamically selects a specific implementation 131A of the relational operator 131 from multiple interchangeable implementations 131A - B. As discussed earlier in this document, each of the implementations 131A - B contains some of the physical operators 161 - 164 independent of the hardware architecture. The implementations 131A - B can be predefined and / or templated as discussed earlier in this document. The DBMS 100 can dynamically generate or otherwise dynamically select the implementation 131A according to fluctuating conditions such as resource availability and / or the expected resource consumption of the physical operators 161 - 164, as discussed earlier in this document, such as by cost calculation.

[0066] Step 204 dynamically selects a specific hardware operator 161B for a specific physical operator 161 from multiple interchangeable hardware operators, including a first hardware operator 161A executed on a first processing hardware such as a CPU and a second hardware operator 161B executed on a second processing hardware that is functionally different from the first processing hardware (such as a GPU). For example, step 204 can be based on the actual hardware inventory, fluctuating hardware workload, and / or (one or more) hardware allocation quotas, as discussed later in this document. Various embodiments can have various numbers and types of hardware processors, such as other types of hardware in the following examples.

[0067] · Single Instruction Multiple Data (SIMD) processors, as explained later in this document,

[0068] · Field Programmable Gate Arrays (FPGAs),

[0069] · Direct Access (DAX) coprocessors for non - volatile Random Access Memory (RAM), and

[0070] · Application - Specific Integrated Circuits (ASICs) containing pipelined parallelism, as explained later in this document.

[0071] Step 205 generates a response 150 to the data access request 110 based on the following: a tuple, a particular implementation 131A of a particular relational operator 131, and a particular hardware operator 161B. For example, as discussed earlier herein, a DAG of hardware operators in an optimized query plan can be executed as a data flow graph for manipulating and transmitting relational data, as discussed later herein, to generate the response 150. The response 150 is an answer to the data access request 110 and can include a final result set, such as a set of rows in column- or row-major format as discussed later herein. The DBMS 100 can send the response 150 to the same client that submitted the data access request 110, such as via ODBC as discussed earlier herein.

[0072] 3.0 Example SQL Parse Tree

[0073] Figure 3 is a block diagram depicting an example relational algebra analysis tree and two alternative example result sets for the same example Structured Query Language (SQL) query. The example SQL query is: select dept_name, avg(emp_sal) from emp, dept where dept.dept_id = emp.dept_id;

[0074] In the example SQL query, each row of the emp table represents an employee and each row of the dept table represents a department. The example SQL query calculates the average salary for each department. As shown in the example result sets, the dept table contains deptl-2 as an identifier and the example SQL query calculates the corresponding avgl-2 as a number.

[0075] Each example result set contains two rows and two columns. The top result set is the actual answer to the example SQL query and may or may not be arranged as desired. Other queries may instead generate the bottom result set as a final result or intermediate set of rows, which is the matrix transpose of the top result set. In other words, both result sets contain the same four values but are arranged differently. The detailed physical query plan consisting of the physical operators of the query is somewhat similar to the example SQL query and includes a transpose, as shown below.

[0076] 4.0 Example Transpose Process

[0077] Presented later herein for the subsequent figures are techniques for accelerating the execution of any combination of ad-hoc queries, heterogeneous hardware, and fluctuating workloads. The following transpose operator demonstrates that physical operators can provide hardware-independent singular functionality and can be in a special way in Figure 1The relational operators 131-132 are used internally and among. This transpose operator demonstrates that various physical operators may be beyond the vocabulary of relational algebra due to finer granularity and singular semantics. Other singular kinds of physical operators will be introduced later.

[0078] Transpose is singular because it is not built into SQL. Known workarounds for transpose typically require manually hard-coded SQL logic for a specific table, such as using a pivot operation or complex use of a database cursor. Known general workarounds that are table-independent require subqueries and dynamic composition of SQL, which is expensive.

[0079] In any case, known workarounds have a query plan that includes multiple relational operations, and according to the technology of this article, each relational operation can include multiple physical operators. The transpose operator implements the same transpose as a single physical operator. Also, the transpose operator and its hardware operator better utilize special hardware (such as GPUs), which accelerates matrix operations, such as with tabular data. Different from the pivot operator in SQL, the transpose operator does not use pivot columns.

[0080] Figure 4 is depicted by Figure 1 a flowchart of an example query execution performed by the DBMS 100 that includes an example transpose operator that applies matrix transpose to a relational table or other set of rows. Refer to Figure 1 and Figure 3 discussed Figure 4 .

[0081] Step 402 receives Figure 1 a data access request 110, such as those discussed earlier in this article, such as a data manipulation language (DML) statement for SQL. As discussed above, the data access request 110 does not specify pivot columns.

[0082] Step 404 compiles the data access request 110 into a query plan that includes physical operators including the transpose operator. The matrix transpose of a relational table or other set of rows can be explicitly specified in the data access request 110. Alternatively, such a transpose can be implicitly selected based on various dynamic conditions, such as: a) conversion between the output format of an upstream operator and the input format of a downstream operator, b) conversion between the operator format and the input or output file format, c) isolation of specific data in a set of rows, such as when transpose is used in combination with horizontal and / or vertical slicing, as discussed later in this article, or d) using a hardware operator that utilizes special hardware (such as GPUs) that requires or benefits from a specific format of tabular data.

[0083] The transpose operator is the physical operator that performs the above transpose. In an embodiment, the transpose physical operator has a transpose hardware operator that provides matrix acceleration through special hardware such as a GPU. In an embodiment and instead of or in addition to the transpose operator, the rotate operator is a physical operator that logically rotates a set of rows according to a configuration setting that specifies a positive or negative multiple of a quarter turn. Neither the transpose operator nor the rotate operator uses the pivot columns required for SQL pivoting.

[0084] When the query plan is executed in step 406, the transpose operator transposes the set of rows. As explained earlier herein, Figure 3 illustrates the transpose of a set of rows that is not a relational table but rather a result set of a join, grouping, and statistical average in that order. As Figure 3 shown, the result sets before and after the transpose are shown as the top and bottom sets of rows that, when compared, reveal that the transpose operator leaves the matrix diagonal unchanged, which in this case includes the shown dept1 and avg2 values. In other words, the transpose operator takes the shown top set of rows as input and produces the shown bottom set of rows as output.

[0085] Step 408 generates response 150 based on the transpose of step 406. For example, response 150 can include some or all of the bottom set of rows or otherwise be based on the bottom set of rows. For example, the shown bottom set of rows is emitted as the output of the transpose operator and can be or can not be an intermediate set of rows that is used as input for further processing by downstream operators.

[0086] 5.0 Example Data Flow

[0087] Figure 5 is a block diagram of an example directed acyclic graph (DAG) 500 of physical operators for a query that depicts a physical plan that is easily portable to different hardware architectures and can be opportunistically offloaded to different hardware such as one or more coprocessors as explained later herein. Refer to Figure 3 discussion Figure 5 . Figure 5 illustrates DAG 500 and various tabular results 510, 520N, 520S, 530, and 540 that are not part of DAG 500 but rather example data generated at different times by the operations of the various shown physical operators that are part of DAG 500 as discussed below and subsequently herein.

[0088] DAG 500 can operate logically as a data flow graph. As explained later herein, data flows through the physical operators of DAG 500 and flows in the direction of the shown arrows that interconnect the physical operators. Figure 3Shows the parse tree of the relational operators, which is generated as the high-level execution plan for the example query presented earlier in this document.

[0089] Similarly, the DAG 500 can be generated from the Figure 3 parse tree as an intermediate-level execution plan. Figure 3 And Figure 5 the intuitive comparison between and reveals that generating the intermediate execution plan from the initial plan increases the complexity of the specification. However, the semantics of the plan between the two plans remain the same. In other words, Figure 3 and Figure 5 the execution plans shown in represent the same example query and achieve exactly the same query result.

[0090] As explained earlier for Figure 3 the example query calculates the average salary by department, which requires in sequence: a) joining the department table to the employee table, b) grouping the join result by department, and c) averaging the salaries of the department groups. For simplicity, Figure 3 shows the grouping and averaging combined into a single relational operator AGG (aggregate), but the actual implementation of the parse tree may instead have separate relational operators for grouping and averaging.

[0091] The DAG 500 is more complex than the parse tree because the physical operators are finer-grained than the relational operators, such that one relational operator can be represented by multiple physical operators. Therefore, visually identifying the join, grouping, and averaging of the example query in the DAG 500 can be less obvious, as follows and explained in more detail later in this document.

[0092] The build physical operator, probe physical operator, and hash table HT in the DAG 500 cooperate to perform the join of the example query. The dense grouping key (DGK) physical operator in the DAG 500 performs the grouping of the example query. The average (AVG) physical operator in the DAG 500 performs the averaging of the example query. However, the DAG 500 contains many more specialized physical operators that cooperate in the execution and data flow for the example query, as shown below.

[0093] While some file formats such as Apache Parquet can keep some or all columns of the same relational table in the same column file, this example keeps one column per column file. Therefore, the table scan physical operators (such as the shown DD, ED, DN, and ES) load one column. Other table scan operator embodiments can load multiple columns from the same column file, or can load row-major data.

[0094] As explained later in this document, each table scan operator produces a data stream path that is scanned separately, enabling some or all of the table scan operators to be executed in parallel. Similarly, as explained later in this document, multiple physical operators in the same scanned data stream path can cooperate as a processing pipeline. For example, the table scan operator ED produces a scanned data stream path that includes a downstream recoding operator and a hash operator, as shown in the figure. The semantics of physical operators of the type shown (such as recoding and hashing) are singular, as explained later in this document.

[0095] Data flow graphs such as DAG 500 can transfer data between operators in ways that parse trees cannot, as described below. Of particular importance for topological composition is the fan-in and fan-out of connecting operators. Fan-in is the convergence of multiple upstream data stream paths into the same operator.

[0096] In other words, an operator can have multiple inputs, regardless of whether the operator is a relational operator or a physical operator. Thus, Figure 3 both the parse tree and DAG 500 show fan-in. For example, the probe physical operator in DAG 500 has a fan-in to accept inputs from multiple upstream physical operators.

[0097] Fan-out is the distribution of the same or different data from one operator to multiple downstream data stream paths. In other words, a physical operator can have multiple outputs, while a relational operator cannot. Thus, data flow graphs such as DAG 500 can transfer data between operators in ways that parse trees cannot, which includes emitting multiple downstream data stream paths that can be executed concurrently, as discussed later in this document. Therefore, DAG 500 can have more parallelization than Figure 3 the query tree of.

[0098] Figure 5 DAG 500 is shown as a graph rather than a tree because the output of the probe physical operator has a fan-out, shown as the fan-out output FO, such that two downstream aggregation operators N and S receive the same probe operator output. As shown in the figure, a DAG can have both fan-in and fan-out, while a tree cannot have both.

[0099] In an embodiment, many or most physical operators and hardware operators (not shown) can process columnar data rather than row-major data. For example, vector hardware such as a GPU or single instruction multiple data (SIMD) may be more suitable for columnar data. Similarly, most of relational algebra focuses on specific columns, such as joining, filtering, sorting, grouping, and projecting. Therefore, converting row-major data to columnar data can be necessary or beneficial.

[0100] For example, the first part of the DAG may have row-major data flow and the second part may have columnar data flow, and a conversion may be needed to make the data flow between these two parts. As discussed above for Figure 3 the matrix transpose discussed or the aggregation discussed later in this document can accomplish this conversion from row-major to columnar and vice versa.

[0101] In particular, in the example shown, the physical operators shown below are interconnected to implement the join relational operator (not shown) below. Generally speaking and as shown, the upstream build operator and the downstream probe operator cooperate to complete the join, such as shown below. Since as shown the build and probe operators are preceded by the corresponding upstream hash operators, this is a hash join.

[0102] 5.1 Example Parallelization

[0103] Since the build operator is preceded by the upstream partitioning operator, the build phase of the hash table HT of the shown partitioning for the hash join is horizontally partitioned for horizontal scaling to be accelerated by parallelization, and the probe phase of the hash table HT is not partitioned but executed serially, as explained later in this document. Here, horizontal scaling may require distributed programming, such as clustered computing and / or symmetric multi-processing (SMP), such as with a multi-core CPU. In an extreme example, distributed programming may require elastic horizontal scaling, such as with a cloud of computers (such as virtual computers).

[0104] For example, the partitioning operator may accept a configuration setting indicating the degree of parallelization, and the DBMS may assign the degree of parallelization based on fluctuating conditions such as the amount of idle computers, the amount of virtual computers already provisioned, or the unit price of elasticity in a public cloud. Another dynamic condition that may help determine the degree of parallelization is how much parallelization quota is currently unused.

[0105] For example, a particular client of the DBMS may be restricted to using at most five processing cores simultaneously, and two of those cores may already be allocated to another part of the DAG 500. Similarly, the DBMS may be hosted by a virtual computer limited to using four GPUs simultaneously. If three of those GPUs have already been allocated to another client of the DBMS, then the degree of parallelization may be restricted to 1CPU + 1GPU = two.

[0106] In any case, a single constructed physical operator that is shown as accepting partitions of the input (shown as horizontal slice HS as explained later in this document) can have multiple instances of the same or different hardware operators. For example, for so-called offline batch data processing for reporting, data mining, or online analytical processing (OLAP), such as according to a cycle scavenging scheme for different computers (such as a loosely coupled computer including two blade computers and one desktop computer), a single constructed physical operator can have three constructed hardware operator instances, namely, one desktop hardware operator and two instances of the same blade hardware operator. Thus, horizontal scaling can be heterogeneous and encourages dynamic decisions to offload the load to different computers or coprocessors. Thus, the query plans and optimizations in this document can be highly opportunistic, such as according to fluctuating workloads.

[0107] Partitioning will be further explained in this document later. More generally, other kinds of parallelization of physical operators are as follows. Somewhat similar to a circuit schematic that arranges elements in series or parallel, DAG 500 arranges physical operators in series or parallel. More specifically, DAG 500 consists of parallel data flow paths.

[0108] 5.2 Examples of Join and Fork of Data Flow Paths

[0109] For example, as shown in the figure, two parallel paths each containing a hash operator fan in to a probe operator, and two other parallel paths each containing an aggregation operator fan out from the output FO of the fan out of the probe operator. Due to its role as a topological connection point for multiple data flow paths, the probe operator is the pairing center of the join. As described below, the output FO of the fan out provides the join result 510 as input to the aggregation operators N and S.

[0110] In this example, the probe operator is used for an equi-join of tables dept and emp, and the involved primary keys dept.dept_id and emp.emp_id and foreign key emp.dept_id are loaded by table scan operators DD and ED as discussed earlier in this document. As shown in the figure, the matching of the probe operator can generate the example join result 510. As discussed below and later in this document, the join result 510 only contains reference data that can be materialized or not as follows.

[0111] Although not shown, a non-materialized embodiment of the join result 510 will only contain pointers to values within a column vector or to rows in row-major data, such as memory addresses or array offsets (e.g., encoded dictionary codes used as ordinals). For example, what is shown as a pair of identifiers in the join result 510 (such as Dept1 and Emp1) can instead be a pair of pointers. When the join result 510 is not materialized, the aggregation operators N and S are indirect aggregations as discussed later in this document.

[0112] In the illustrated materialized embodiment, the join result 510 includes the primary key columns of the rows that match during the pairing. Even though the column emp.dept_id provided by the table scan operator ED is a join key, as a foreign key, emp.dept_id is excluded from the join result 510. In this example, each employee Emp1-4 is matched once, such that the join result 510 includes four pairs of primary key values from the two scanned and joined tables dept and emp.

[0113] Other columns of the joined table can be assembled downstream, such as with one or more aggregation operators, as discussed later herein. Thus, even when the join result 510 includes the materialized primary key, the materialization is somewhat partial, such that the materialization of other columns is deferred to downstream aggregation operators N and S, for efficiency, as discussed later herein.

[0114] Depending on the embodiment, the join result 510 can be included in a buffer, batch, or stream, and can be columnar or row-major, as discussed elsewhere herein. Depending on the embodiment, the fanout output FO provides the same or separate copies of the join result 510 as input to the aggregation operators N and S. The mechanism for communicating the join result 510 may require copying values or some type of pointer to the values, such as discussed later herein with direct and indirect aggregation.

[0115] In an embodiment, the aggregation operators N and S each receive a pointer to the same buffer that contains the join result 510 as input. In a columnar embodiment, the join result 510 is provided as multiple pointers that each point to a vector of values for each column in the join result 510, and those vectors do not need to be adjacent to each other in memory.

[0116] 5.2 Example Operator Collaboration

[0117] Physical operators on separate parallel paths can be executed in parallel, such as with separate hyperthreads, cores, or processors, for acceleration. For example, two hash operators can be executed in parallel because they participate in separate data flow paths. Physical operators that reside in the same data flow path in the DAG are arranged serially, but can still be accelerated through pipelining parallelism, such that the upstream operator processes the next row or batch of rows while the downstream operator processes the previous row or batch simultaneously.

[0118] Thus, the serial data flow path can be divided into multiple parts such that: each part is a separate pipeline stage; each part contains a subsequence of one or more physical operators; and each physical operator participates in exactly one pipeline stage. For example, the Gather N, DGK, and AVG operators explained later in this article are arranged in series and can participate in the same pipeline. For example, the previous stage of the pipeline can contain Gather N and DGK operators, while the next stage of that pipeline can contain the AVG operator.

[0119] Both physical aggregation operators N and S receive the same or separate copies of the join result 510, as explained above. Although the aggregation mechanism is discussed later in this article, the aggregation results of the aggregation operators N and S are as follows. Shown together are two mutually exclusive embodiments. One embodiment generates separate aggregation results 520N and 520S from the aggregation operators N and S, respectively. Another embodiment instead generates a combined aggregation result 530 that the aggregation operators N and S collaborate to fill.

[0120] Whether the aggregation operator N and / or S is a direct aggregation or an indirect aggregation depends on whether the input of the aggregation is materialized. As shown in the figure, the aggregation operators N and S each have two inputs, and for these two aggregation operators, one of the inputs is the join result 510. For the aggregation operators N and S, the other input is the columnar output for the table scan operators DN and ES, respectively. That is, the aggregation operators have fan-in, so that they accept multiple inputs from multiple corresponding data streams, as discussed elsewhere in this article.

[0121] In this example, the table scan operators DN and ES emit materialized output, but the join result 510 may be materialized or not, as discussed earlier in this document. Thus, an aggregation operator may be configured to accept only materialized or non-materialized inputs or certain combinations of inputs, as discussed later in this document. Thus, what is exemplarily presented later in this document as mutually exclusive direct or indirect aggregations may occur together in the same aggregation operator with multiple inputs that differ in materialization.

[0122] Aggregation operator N generates aggregation result 520N by direct or indirect aggregation as compared later in this article. In either case, aggregation operator N fills aggregation result 520N with materialized data as shown in the figure. In various embodiments, aggregation result 520N is row-first or a pair of separate value vectors. In an embodiment, the dept.dept_id columns in all join results 510 and aggregation results 520N and 520S are the same (that is, shared) or separate (that is, copied) vectors. The mechanism for filling aggregation results 520N and 520S will be presented below.

[0123] As explained above, the combined aggregation result 530 is a design alternative to the individual aggregation results 520N and 520S such that the aggregation operators N and S cooperate to populate the combined aggregation result 530. The aggregation operators N and S populate the dept.dept_name and emp.emp_sal columns in the aggregation result 530, respectively. As shown, the aggregation result 530 is materialized, which can be row-major or columnar.

[0124] Only one of the aggregation operators N and S populates the dept.dept_id column in the aggregation result 530. Whether using the combined aggregation result 530 or the individual aggregation results 520N and 520S, the aggregation operators N and S can emit output concurrently because they reside on separate data flow paths as discussed elsewhere in this document.

[0125] 5.3 Example Optimizations

[0126] In various embodiments, a multi-column row set or a single column can be segmented into batches, such as in-memory compression units (IMCUs), such that the next pipeline stage processes a previous IMCU while the previous pipeline stage simultaneously processes the next IMCU. Similarly, the previous stage can produce IMCUs that are subsequently consumed by the next stage. Batches (such as IMCUs) can operate as buffers for decoupling adjacent stages of the same pipeline. For example, adjacent stages can operate asynchronously with each other.

[0127] For example, for any kind of buffering between adjacent stages, the previous stage can produce many rows or many batches while the next stage consumes only one row or one batch at a time due to mismatched bandwidths of the stages, which is tolerable. If the buffer overflows, then the execution of the upstream operator will pause until space becomes available in the buffer. For example, the DAG 500 will experience backpressure propagating backward along the data flow path, which is tolerable.

[0128] As explained earlier in this document, each physical operator is executed by a corresponding hardware operator. In embodiments where there are the same pipeline stage, some or all of the hardware operators or some or all of the physical operators are fused into a combined operator, as discussed later in this document. If two physical operators are fused into one physical operator, then their hardware operators are fused into one hardware operator, but the reverse is not necessarily true, such that even if the physical operators are not fused, two hardware operators can be fused.

[0129] Another form of parallel acceleration is single instruction multiple data (SIMD) for inelastic horizontal scaling. Whether the hardware operator is created through fusion or not, the hardware operator can internally use SIMD to be accelerated through data parallelization, as explained later in this document.

[0130] Two physical operators or two hardware operators can be fused even if they are included in implementations of different relational operators. For example, in Figure 1 any physical operator 161-162 and / or hardware operator 161B in the same implementation 131A of relational operator 131 can be fused with an operator in implementation 132B of a different relational operator 132. The mechanism for fusion can be as follows.

[0131] Physical operator instances are declarative and not directly executable. In an embodiment, fusing physical operators 161-162 in the same implementation 131A requires replacing the two physical operators with a combined physical operator that consists of references to physical operators 161-162. Fusing physical operators includes fusing hardware operators.

[0132] Hardware operators include execution constructs such as call stack frames, operand stack frames, hardware registers, and / or sequences of machine instructions (such as for a CPU or GPU). When an input is common to the two hardware operators being fused, the input can be fused. For example, in Figure 5 aggregates N and S share the same input. The instruction sequences of the fused operators can be fused by concatenation. The optimizer can eliminate redundancy in the concatenated instruction sequences. A stack frame or register file is a lexical scope that two hardware operators can share when fused.

[0133] 5.4 Example Aggregates

[0134] An aggregate is a category of physical operator that assembles data from different sources (such as different columns or row sets of the same relational table or the outputs of different upstream physical operators). Materialization is the purpose of aggregation, especially when the filtered or joined results are available but not materialized and the actual values required for materialization are available but materialized in an incompatible form. For example, as explained below, an aggregate can aggregate columns from different materialized row sets by copying, or can copy a vertical slice with a subset of the columns of a materialized row set to generate a new materialized row set. For example, projecting two columns of the same relational table that are kept in separate column files may require an aggregate operator. The aggregate operator outputs one or more tuples, each tuple having multiple fields, such as a row of the output row set.

[0135] An aggregate is different from a join because a join combines data that is not yet related, while an aggregate combines data that is already related although not yet actually stored together. An aggregate can complement a join, especially after a join, such as when projecting columns from the join result. For example, such a post-join projection is in Figure 5are shown as two downstream aggregation operators N and S, which fan out from the probe operator because separate aggregation operators are required for the dept_name and emp_sal columns being aggregated in the materialization scenario shown, as will be discussed later in this document. Depending on eager or deferred materialization, direct and indirect aggregations are eager or deferred respectively, as will be contrasted later in this document.

[0136] 5.5 Keys and Codes

[0137] In various embodiments, a recoding operator is a physical operator that can decode a dictionary-encoded column or can transcode a dictionary-encoded column from one encoding dictionary to another, such as when each IMCU has its own local encoding dictionary, such as between the separate local encoding dictionaries of two IMCUs and / or a canonical global encoding dictionary.

[0138] A Dense Grouping Key (DGK) operator is a physical operator that generates a corresponding distinct unsigned integer for each distinct value in a scalar column. In an embodiment, the integer values are fully or mostly consecutive within a value range. In an embodiment not shown, the DGK operator scales horizontally such that two computing threads can, in a thread-safe and asynchronous manner, for corresponding raw values: detect whether a dense key has already been generated for a value, detect which of the two threads should generate the dense key when the two corresponding values are exactly the same, and detect which corresponding consecutive dense key each thread should generate next.

[0139] In an embodiment, the DGK operator generates dense keys as dictionary codes for the encoding dictionary being generated. In other words, the DGK operator can be the inverse of a recoding operator. In an embodiment, the DGK operator detects distinct values in a column. In the example shown and as shown below, the DGK operator is used to group rows by dept_name so that the emp_sal can subsequently be statistically averaged by an average operator AVG that is a physical operator.

[0140] Depending on the embodiment as discussed earlier in this document, the aggregation operator N materializes the dept.dept_name column in the aggregation result 520N or 530, either of which can be the sole input to the DGK operator. Whenever the DGK operator encounters a unique value in the dept.dept_id column of the input aggregation result 520N or 530, the DGK operator can assign the next sequential unsigned integer dictionary code. In this example, the DGK operator acts as a transcoder that converts the input dept_id values into dense grouping key values output by the DGK operator.

[0141] For example, the dept_id values can be sparse, such as text strings or discontinuous integers. For example, after filtering or joining, large and / or many gaps can occur in the set of previously consecutive distinct dept_id values. The result of the DGK operator transcoding is that the gaps are removed, such that the DGK operator generates a continuous range of output values from a discontinuous range of input values. Thus, as discussed later in this document, the dense grouping key values can be used as array offsets.

[0142] Although not shown, the output of this DGK operator has two columns, namely, the dense grouping key column and the dept.dept_name column. These two columns, in row-major or columnar format, are provided as the first input to the AVG operator. The AVG operator also accepts a second input that is the output of the aggregation operator S, which can be the aggregation result 520S or 530 according to the embodiments explained earlier in this document.

[0143] The AVG operator has a fan-in as it accepts two inputs from different data flow paths. Even though the two data flow paths can operate concurrently, they should not reorder the data. Data ordering is important for the AVG operator, as shown below.

[0144] The AVG operator processes one row from each input at a time. That is, the AVG operator processes one row from the DGK operator and one row from the aggregation operator S together. The AVG operator expects the two rows to be processed together to belong to the same department, i.e., either Dept1 or Dept2, even if the dept.dept_id column is missing from the input from the DGK operator. As long as the AVG operator receives the rows from the two inputs in the same order, i.e., the rows appear in the join result 510 as emitted by the probe operator, the AVG operator can rely on the implicit correlation of the two input rows, which is important, as described below.

[0145] Based on the input provided by the DGK operator, the AVG operator uses the values in the dense key column as offsets into an array or list of grouping intervals (not shown) that are part of the AVG operator. Thus, the AVG operator detects which grouping interval should receive the emp.emp_sal value from the corresponding row in the aggregation result 520S or 530. The operation of a particular grouping interval is as follows.

[0146] Various examples can have various grouping interval contents and behaviors. In this example, each grouping interval contains a counter and an accumulator. When a row is directed to a particular grouping interval, the counter is incremented and the emp.emp_sal value is added to the accumulator for summation, which is important, as shown below.

[0147] As explained elsewhere in this document, various types of operators can behave as streaming, blocking, or batch (which is a hybrid of streaming and blocking). Aggregation operators can have any of these behaviors. For example, an aggregation operator can emit output rows separately for each individually processed input row, which is streaming.

[0148] The semantics of the AVG operator precludes streaming and batch. That is, the AVG operator is necessarily blocking, meaning that the AVG operator cannot emit any output rows until it has received and processed all input rows. After processing all input rows, for each grouping interval, the AVG operator arithmetically divides the accumulator value by the counter value to compute the corresponding arithmetic mean for each grouping interval.

[0149] Thus, in this example, the average operator AVG computes the corresponding average salary for each department. As a blocking operator and only after all averages have been computed, the AVG operator emits the final result 540 as output to be accepted as input by the ret operator. The ret operator can serialize the final result 540 in a format that conforms to standards such as SQL and / or ODBC and that the client may expect.

[0150] The DAG 500 was discussed above in a macroscopic way, considering data flow paths that diverge and converge through fan-out and fan-in to achieve the query plan. As discussed above, the data flow paths consist of a set of integrated physical operators. The following is a microscopic view of how some important physical operators actually process and transmit data. The following discussion contrasts different methods of data materialization that can affect the efficiency of the DAG 500 without changing the query semantics and can affect the relative placement of some physical operators within the DAG 500. In other words, the optimization of the DAG 500 can be based on the following available design alternatives, as Figures 6 - 9 demonstrated.

[0151] 5.6 Eager Materialization via Direct Aggregation

[0152] Direct aggregation requires eager materialization of the data required by other physical operator(s) downstream of the aggregation physical operator. Thus, direct aggregation can also be referred to as eager aggregation. As discussed below, eager materialization occurs within the direct aggregation operator such that the output of the direct aggregation operator contains the materialized data that may need to be replicated during propagation to downstream physical operators. This replication can be inexpensive when the physical operator that requires the replicated data is immediately downstream of the direct aggregation operator.

[0153] However, when the materialized data is repeatedly replicated to flow through intermediate physical operators in the same data flow path between a resident direct aggregation operator and further downstream physical operators that require materialized data, such replication of eagerly materialized data can be expensive. Although deferred materialization through indirect aggregation can improve efficiency, the mechanism of indirect aggregation may be more complex, as described later in this article. Therefore, the materialization mechanism is first discussed below based on eager materialization through direct aggregation, as follows.

[0154] Although, as discussed later in this article, Figure 5 aggregation is used after a join, the following various other scenarios are more straightforward in various aspects. Projection requires the most direct aggregation because both projection and aggregation require the materialization of a small number or only multiple scalar values or multiple tuple values. Aggregation for projection has the following three scenarios of different complexities. The three projection scenarios are explained for direct aggregation, which requires the following (one or more) materialized inputs. As explained later in this article, indirect aggregation accepts (one or more) non-materialized inputs.

[0155] Direct aggregation produces or consumes only two types of data, which are individual columns and row-major data, either of which can be an input or output of direct aggregation, as follows. If the aggregation projects only one column, the only output of any direct or indirect aggregation is the materialized column. Otherwise, the aggregation projects multiple columns, and the only output of the aggregation is the materialized row-major data. Therefore, the output of any aggregation is materialized, and materialization is the only purpose of aggregation, such as for projection, as follows.

[0156] Although any aggregation has only one output, an aggregation can have one or more inputs, as follows. The most direct aggregation is a direct aggregation that projects one column from row-major data, such as an aggregation operator that accepts materialized row-major data as its only input (such as a relational table or an intermediate row set). The input has the same row offset range as the aggregation output. That is, the same offset in the range associates the row from the aggregation input with the scalar value in the output column or the row in the output row set, depending on whether multiple columns are projected.

[0157] For example, the third row in the aggregation input is associated with the third scalar value or row in the aggregation output. For demonstration purposes, the offsets of the aggregation input and aggregation output are discussed. For example, a streaming aggregation may lack offsets as discussed later in this article. Even without streaming, the offsets can be related or not related to variable-width values as explained later in this article.

[0158] An aggregation operator can have multiple inputs, and each input can be a row-major data or a column of scalar values. If a direct aggregation has only one input, then it should not be a scalar column because the output would be the same as the input and there may be an additional inefficiency of copying the scalar from the input to the output, as discussed later in this document.

[0159] In the absence of any configuration settings, in the declared order of the inputs, scalars or rows with the same offset from each input are cascaded to produce a row at the same offset in the output. Thus, the aggregation output is produced by copying values such as scalars and / or tuples.

[0160] As discussed later in this document, copying is expensive and should be avoided or postponed as much as possible. Therefore, for efficiency, aggregation operations should be postponed as much as possible in the data stream, which means that the aggregation operator should be pushed as far downstream as possible in the DAG of physical operators. Deferred aggregation will be discussed later in this document.

[0161] An aggregation operator can have configuration settings that indicate which column(s) to project and / or in what order to cascade the columns. If a direct aggregation takes row-major data as the only input, then that configuration setting should be set, which prevents the output from being the same as the input.

[0162] Projection requires aggregating columns from the same or different sets of rows (such as a relational table). For example, projection may require vertical partitioning (also known as vertical slicing) of a subset of the columns of row-major data. As explained above, it is unnecessary to aggregate all columns of row-major data as the only input because the output would be the same as the input. However, if the columns are vertically partitioned in multiple column files, then projecting all columns of the same relational table may require direct aggregation. Thus, converting a columnar relational table to row-major data may require direct aggregation.

[0163] 5.7 Parallel Aggregation

[0164] In various embodiments below, such as for projection, aggregation can be serial or parallel. With serial direct aggregation, a single processor (such as a single core of a CPU) copies the value at the current offset from the input vector to the same offset in the same row in the output row set for each offset in that range and iterates one offset at a time. Any input to a direct aggregation can be composite, such as a materialized input row set. For example, aggregation can be done such that each row of the input row set becomes adorned with the corresponding value from another input to the aggregation. Thus, the output of the aggregation can be wider than either input to the aggregation.

[0165] Non-elastic horizontal scaling may require single instruction multiple data (SIMD) of a stride sequence. Each stride concurrently processes batches of a subset of contiguous offset quanta of a fixed size within a range of offsets, such as concurrently combining eight values of a scalar input vector from multiple values with eight corresponding values of another scalar input vector to concurrently generate eight rows of a stride in a set of output rows.

[0166] Although both horizontally scale, such as for aggregation, SIMD strides differ from horizontal partitioning as follows. Horizontal partitioning can be non-elastic, such as for a Beowulf cluster or multi-core symmetric multi-processing (SMP), or elastic, such as for a cloud of computers. The partitioning operator is a physical operator that divides a set of rows or columns into equal subsets for corresponding processing by corresponding instances of (one or more) downstream operators, such as on separate cores. Horizontal partitioning, especially elastic, may require heterogeneous hardware, as discussed earlier in this document, which encourages opportunistic offloading of horizontal scaling.

[0167] The partitioning operator can ultimately be followed by a downstream merge operator, which is a physical operator that cascades the outputs of the partitions processed by the upstream partitioning into a combined output, typically for subsequent serial processing by a downstream operator. Depending on the embodiment, the merge operator can preserve or not preserve ordering, and can preserve or not preserve sorting. In some embodiments where neither ordering nor sorting is preserved, a merge operator is not required, and the partitions are implicitly cascaded or queued into the same input of the downstream operator.

[0168] In an embodiment, some physical operators that are not merge operators can implicitly merge outputs. For example, the shown build operator is a multi-instance of a horizontal slice HS, but the probe operator is not a multi-instance because the build operator implicitly merges parallel data streams. Whether implicitly merged by a merge operator or explicitly, all operators are implicitly multi-instances in the data flow path that appears between an upstream partitioning operator and a downstream merge.

[0169] 5.8 Filtering

[0170] As discussed later in this document, indirect aggregation requires non-materialized inputs, especially when certain types of upstream physical operators emit non-materialized outputs as their only outputs, such as a filter operator or a join operator. The filter operator has one input and one output. The only input to the filter operator is as follows. The input is materialized. The input can be a column of scalars or a set of rows. The set of input rows can be columnar or row-major.

[0171] The only output of the filter operator is as follows. Depending on the embodiment, the filter output is materialized or not materialized. If the input is a column, then the output is a column. If the input is a set of rows, then the output is a set of rows. If the input set of rows is row-major, then the output set of rows is row-major. If the input set of rows is columnar, then the output set of rows is columnar.

[0172] The most straightforward example of filtering is a column to which a predicate is applied by a filter operator. The filter operator has a configuration setting that specifies the predicate. In an embodiment, a relational operator may have a compound predicate, but the filter operator may not, as shown below.

[0173] Embodiments can decompose a compound predicate into smaller non-compound predicates. For example, a compound predicate such as radius IN(1,3,7) OR radius>20 can be decomposed into two or more smaller non-compound predicates. Embodiments can use separate instances of the same or different filter operators for each corresponding smaller predicate.

[0174] For example, before applying another smaller predicate of the same compound predicate to any value, one smaller predicate of the compound predicate can be applied to many or all input values, such as rows. For example, some smaller predicates of a compound predicate can be applied in parallel, while other smaller predicates of the same compound predicate can be applied serially or pipelined, such as depending on whether the smaller predicate is conjunctive or disjunctive.

[0175] The predicate uses only the column(s) that are part of the only input to the filter operator. In an embodiment, a compound predicate is decomposed into smaller predicates, each of which can still be compound, but each smaller predicate uses only one corresponding column.

[0176] If the filtering is based on copying, then the output is materialized as follows. Otherwise, as discussed later in this document, the output is not materialized. The copy filter operation is as follows.

[0177] Unlike the aggregation operator and regardless of whether there is copying, the only output of the filter operator can have the same or fewer offsets than the only input. For example, some input values (whether rows or scalars) may not satisfy the predicate, in which case those values will be excluded from the output. The values that satisfy the predicate are included in the output. Copy filtering copies the satisfactory values from the input to the materialized output.

[0178] 5.9 Other Physical Operators

[0179] Various embodiments have various categories of operators, such as filtering operators, and there can be multiple operators or subtypes within the same category. For example, there can be multiple filter operators specialized for various scenarios. Example embodiments can have the following singular example physical operator types, some of which are shown in Figure 5 and discussed elsewhere in this document.

[0180] · Decompress - Decompress compressed data

[0181] · Decode - Decode encoded data

[0182] · Filter - Apply a single table predicate

[0183] · Project - Project out entries based on the filtering results

[0184] · Transpose - Convert between columnar data and row-based data

[0185] · Aggregate - Randomly aggregate data from columnar / row-based data using an array of indexes / pointers, including direct aggregation and indirect aggregation

[0186] · Hash - Hash data, working on a single column or composite (multiple columns)

[0187] · Partition - Partition the input data into multiple partitions based on a partition key

[0188] · Build - Insert data into a hash table

[0189] · Probe - Find a match for the input data in the hash table

[0190] · Dense key - For any given sparse input data (such as text), densify it so that the value range becomes unsigned integers [0...n]

[0191] · Group aggregate - Given an array of dense keys for aggregation and input data (e.g., columns), calculate the aggregation

[0192] · Classify - Classify the input data

[0193] · Classify - Merge - Given multiple classified input data streams, output as a single merged classified stream

[0194] · Merge - Join - Given classified data in two joined tables, produce a join result using a merge method

[0195] · Search - Search for a given keyword string

[0196] Thus, the rich mix of processing activities can include a hardware-neutral DAG of physical operators to represent any particular query. Thus, when compiling a query into a DAG of physical operators, the Turing completeness of SQL and scripted SQL (such as a procedural language for SQL (PL / SQL)) is preserved.

[0197] 6.0 Example Join Plan

[0198] As follows, Figures 6 - 7 illustrates example activities for planning and optimizing queries. Figure 6 illustrates example configuration activities for constructing and using hash tables to demonstrate both: an integration pattern generally used for coupling operators, and an arrangement of specific operators for hash table processing. Figure 7 has a higher-level view of planning and optimization. In other words, Figure 6 has a micro view of several operators, as shown below, while Figure 7 is based on a macro view of configuring the entire DAG.

[0199] Figure 6 is a flowchart of an example process that a DBMS 100 can use to plan and optimize a data flow including a hash table, such as for Figure 1 the hash join or other partitioning or grouping shown in Figure 5 . Refer to Figure 5 for discussion Figure 6 . As follows, Figure 6 includes six operators (not shown), which can all be physical operators or can all be hardware operators unless otherwise noted.

[0200] As discussed previously herein, Figure 5 illustrates a hash join based on the shown hash table HT, which is filled by the shown build operator during the build phase and subsequently used by the shown probe operator during the probe phase. The selection and optimization of the build operator and the probe operator can occur as follows.

[0201] Step 602 dynamically selects a build operator from various interchangeable build operators. Step 604 dynamically selects a probe operator from various interchangeable probe operators. Thus, the hash table processing can be dynamically configured, for example, according to fluctuating conditions.

[0202] For example, step 602 and / or step 604 can select different operators for separate executions of the same query submitted repeatedly because even on the same DBMS 100, the same query may not always have the same optimal plan. This flexibility and variation goes beyond known relational operator planning because Figure 6 it involves physical operators or hardware operators with a finer granularity than known relational operators.

[0203] Based on the selected probe operator and / or build operator, step 606 dynamically selects a hardware operator from various interchangeable hardware operators for another physical operator. For example, in Figure 5 , which hardware operator or physical operator to select for the shown partitioning operator and / or DGK operator can depend on which hardware operator or physical operator is selected for the shown build operator and / or probe operator. For example, details selected for the build operator and / or probe operator (such as data formatting and referencing discussed elsewhere herein) can limit or benefit the partitioning operator and / or DGK operator. For example, if the physical operator or hardware operator selected for the build operator is not suitable for null values as build key values, then the selection of a certain (or some) surrounding physical operators or hardware operators can also be specialized and / or optimized based on excluding nulls.

[0204] As Figure 5 shown, the build operator and the probe operator are connected. The build operator is also connected to the partitioning operator, and the probe operator is also connected to the hash operator and the aggregation operators N and S. That is, four operators interconnected as part of DAG 500, as well as the build operator and the probe operator. These four operators can alternatively or additionally include other operators in other examples, such as the following other example operators.

[0205] · Join key decoder operator,

[0206] · Decompression operator,

[0207] · Dictionary encoder operator,

[0208] · Dense grouping key operator, which can generate a series of different unsigned integers containing gaps,

[0209] · Statistical average operator,

[0210] · Filter operator that applies a simple predicate,

[0211] · Classification operator,

[0212] · Merge operator that preserves the order of multiple classification inputs,

[0213] · Text search operator,

[0214] · Horizontal row partitioning operator using a join key,

[0215] · Build key insertion operator using the buckets of a hash table, or

[0216] · Insertion overflow operator using the buckets of a hash table.

[0217] A build operator or a probe operator is connected to one of the other four operators according to an operator integration pattern in step 608, such as data batching and / or buffering, execution pipelining and / or synchronization (also known as blocking), or asynchronous coupling. For example, in one embodiment, Figure 5 The following physical operators shown in may involve the following example operator integration patterns and performance issues.

[0218] · Re-encoding and hashing: This is a fusible operator, and the calculation is bounded

[0219] · Partitioning: This is a cache type operator, and it will consume the space of the horizontal slice HS shown, and is bandwidth limited.

[0220] · Build, which is a blocking operator, bandwidth bounded, and generates the hash table HT shown.

[0221] · Probe, aggregation: These are pipelined operators, and bandwidth bounded.

[0222] · DGK: This is a blocking operator, and bandwidth bounded.

[0223] · AVG: This is a pipelined operator, and the calculation is bounded.

[0224] Thus, DAG 500 is configured to perform load balancing across heterogeneous hardware, such as by offloading and across the DAG of physical operators, which can be arranged in parallel data flow paths and pipeline stages, with a mix of slightly mismatched bandwidths, without having performance bottlenecks 500 within the DAG and without degrading the execution throughput of DAG 500.

[0225] 7.0 Example Plan Optimization Process

[0226] Presented later herein are techniques and diagrams for indirect aggregation, which may be more or less fundamental to minimizing data in motion by deferring aggregation. The following are other important optimizations that generally apply to directed acyclic data flow graphs (e.g., Figure 5 of DAG500). Query planning and execution may require a DAG of physical operators and / or a DAG of hardware operators, as shown below.

[0227] Figure 7 is a flowchart of an example DAG optimization process that a DBMS 100 depicted Figure 1 can use to plan, optimize, and execute a DAG 500 of physical operators and / or a DAG of hardware operators, such as for performing a data access request 110. Refer to Figure 1 discussed Figure 7 .

[0228] For illustration, a query plan can be considered a linear process that generates the following artifacts in sequence, one after the other, to generate the next artifact based on the previous one: a) a parse tree 110 of a data access request, which can be an initial query plan consisting of relational operators such as 131 - 132 and independent of hardware, b) an implementation query plan independent of hardware and based on a specific implementation (such as 131A and 132B) for the dynamic selection of relational operators, and including physical operators (such as 161 - 162) that are independent of hardware and arranged in a DAG of physical operators, and c) a corresponding optimized hardware operator DAG that represents the execution of the DAG of physical operators on heterogeneous hardware.

[0229] In practice, embodiments of the DBMS 100 can have various methods that can deviate from this rigid linear progression of planning and executing artifacts in various ways. For example, instead of a waterfall approach, planning and optimization can be an iterative approach where previously generated artifacts can be modified or replaced based on subsequently generated artifacts. For example, the DAG of hardware operators is generated based on the DAG of physical operators, and the physical operators may require or benefit from subsequent modification or replacement based on the selection, fusion, and topological reordering of hardware operators in the DAG of hardware operators.

[0230] For example, due to an initial cost calculation, implementation 131B may not be initially selected as shown. However, when the selected implementation 131a is customized for the hardware through selection, fusion, and reordering of hardware operators, the final cost of implementation 131a may be higher than expected. For example, due to dynamic conditions, some hardware, hardware operators, fusion, or other optimizations may unexpectedly become unavailable.

[0231] Therefore, there can be feedback between the planning and optimization phases, which results in the correction or replacement of the previously generated artifact(s), such as according to a feedback loop where the estimated cost is corrected with increasing accuracy. For example, an optimization iteration may reveal that implementation 131B is actually and unexpectedly less costly than the previously selected implementation 131A.

[0232] In operation, step 702 generates an initial execution plan that is not based on accurate hardware details, such as an initial tree of relational operators 131 - 132 based on an initial cost calculation that may, for example, incorrectly assume either pessimistically that no GPU is present or optimistically that all GPUs are idle. Such an initial assumption, while it may not be accurate, can speed up the initial plan and may actually be statistically accurate, reliable, and robust in many or most cases.

[0233] Step 704 initially and dynamically selects implementations 131A and 132B for respective relational operators 131-132. When selected separately and isolated from each other, implementations 131A and 132B may each independently appear to be optimal. However, there may be inter-operator issues across the selected implementations 131A and 132B, and better optimization may be achieved if implementations 131B and / or 132A were instead selected. For example, even if the initial cost of implementation 131A may be lower than that of implementation 131B, DBMS 100 may detect during iterative planning and optimization that the physical operator 164 of implementation 131B of relational operator 131 has more synergies, such as less resource consumption for interoperating with the plan artifacts of relational operator 132 than the physical operator 162 of the initially selected implementation 131A.

[0234] Accordingly, step 706 can iteratively generate an optimized execution plan that corrects or replaces based on a particular initial selection or non-selection of implementations of relational operators, such as based on an initial selection or non-selection of hardware operators, such as before or after physical operator or hardware operator fusion or reordering. According to convergence criteria, the iterations of planning and optimization ultimately converge to a final DAG of physical operators and a final DAG of hardware operators, which can facilitate a hasty optimization of inexpensive queries with fewer iterations, such as according to row counts and / or value distributions (such as cardinality), and can facilitate many iterations for long-term optimization of expensive queries (such as with a huge data warehouse).

[0235] As discussed above, steps 702, 704, and 706 perform query plan formulation, such as topology formulation and DAG reformulation, such as through operator fusion, reordering, and / or replacement. However, even after the iterations converge to a final plan, the plan and / or its operators may have configuration settings that may benefit from additional tuning. For example, the input or output of a physical operator or hardware operator may require buffers of configurable capacity, and the optimal capacity may depend on dynamic conditions, such as operator interconnectivity, operator fusion, separation of a subset of operators into pipeline stages, details of the participating hardware, and / or details of the payload data (such as capacity and data type).

[0236] DBMS 100 may include a machine learning (ML) model that predicts optimal configuration settings in step 708, such as the respective optimal fixed sizes of data batches and buffers for tuning different instances of different operators at different locations in the DAG. The predictions of the ML model are based on inputs called features, which may include details of the DAG, the operators of the DAG and their interconnectivity, the participating hardware, fluctuating resource availability, and / or statistics of the payload data.

[0237] Depending on the nature of the particular configuration settings (such as numerical values or categorizations), various ML models can be more or less suitable for providing predictions. As discussed later in this document, example types of ML models include decision trees, random forests, artificial neural networks (ANNs) (such as multi-layer perceptrons (MLPs)), linear or logistic regression, and support vector machines (SVMs). Reinforcement learning with historical operation information of one or more DBMSs such as DBMS 100 during offline training can prepare the ML model to make highly accurate predictions for optimality in live production settings, as discussed later in this document. Step 708 adds little or no latency and can significantly accelerate the subsequent execution of the DAG. After step 708, the DAG of the hardware operator is both optimal and ready to execute directly.

[0238] 8.0 Deferred Materialization of Indirect Aggregation

[0239] As discussed earlier in this document, eager materialization through direct aggregation may require excessive replication of the materialized data to physical operators that can be more downstream than the direct aggregation operator, which can be inefficient. Deferred materialization of indirect aggregation can improve efficiency in an innovative way, as discussed below. Indirect aggregation can also be referred to as deferred aggregation because aggregation can be deferred by placing the indirect aggregation operator as far downstream as possible (such as adjacent to a physical operator far downstream of the data that needs to be materialized). The payload replication reduced by this topological optimization will be discussed later in this document.

[0240] Figure 5 The probe operator for performs pairing between two tables but does not actually cascade the matched rows to materialize the join result. Instead, the probe operator only emits an unmaterialized join result that contains pairs of references to the matched rows of the two tables, such as having row identifiers, memory address pointers, or buffer internal offsets. The probe operator does not emit any table columns, not even the primary key column.

[0241] This column projection for materialization of the join result is delegated to one or more downstream aggregation operators. When two columns come from the same relational table or the same set of materialized rows, both columns can be aggregated by a single aggregation operator, even if the aggregation operator is downstream of the join, such as downstream of the probe operator. However, when the two columns come from separate relational tables, such as the dept_name and emp_sal columns coming from Figure 3 the dept and emp tables respectively, then aggregating these two columns requires two aggregation operators N and S, as Figure 5 shown. In an embodiment, both aggregation operators N and S are executed in parallel, as discussed earlier in this document.

[0242] An important innovation of the present technology is to minimize the actual data flow (so-called in-motion or in-flight data), including minimizing the occurrence and scope of data replication. Thus, due to over-replication, direct aggregation may be discouraged, especially when either input is wide (such as having multiple fields) or has a wide field (such as text). In the present text, replication can be minimized indirectly by passing references (such as row identifiers) rather than actual values (such as multi-field tuples) in the data flow of the DAG of physical operators. Similarly, direct aggregation can only be used when all values to be replicated into the aggregation output are directly provided in some input of the direct aggregation.

[0243] For example, direct aggregation cannot be used with non-materialized inputs (such as join results as explained above). This limitation may require values to be replicated into the input vector of the direct aggregation, which is generally not optimal and is often the reason for not using direct aggregation. Thus, the following kinds of indirect aggregations are often more efficient in time and / or space than direct aggregation. Another benefit of indirect aggregations is that since they expect non-materialized inputs (such as those produced by a join), indirect aggregations complement joins as discussed above.

[0244] Figure 8 is a block diagram depicting three indirect aggregations that are specifically for the corresponding scenarios as follows. Non-materialized data such as the output of a filter operator or a relational join as discussed in the present signature can be provided as input to a downstream indirect aggregation and can indicate a subset of the rows in a row set that have been evaluated as satisfying the filter.

[0245] As shown, the three indirect aggregations use double indirection such that dereferencing one reference obtains another reference that must then be dereferenced to reach the actual rows of the row set. Thus, using two buffers (such as two separate inputs to the same indirect aggregation operator) causes the first buffer to contain a reference to the second buffer, as shown and explained below. This double indirection can preserve the filtering that has been applied, as shown below.

[0246] In other words, the second buffer can contain all source rows, and the first buffer can contain references to a subset of the rows that satisfy the filter. Thus, the first buffer can have fewer and narrower entries than the second buffer. Thus, using the first buffer as an additional buffer for double indirection does not consume a large amount of additional memory.

[0247] The array indirect and CLA indirect examples shown have two buffers for double indirection such that a first buffer is shown on the left and a second buffer is shown on the right. The pointer indirect example may or as shown may not have a second buffer. For the array indirect and CLA indirect examples, the second buffer combines the rows of the original set of rows into contiguous memory. As shown, dataIN points to the second buffer which may be a segment as discussed below.

[0248] Since the first buffer of the pointer indirect example contains pointers rather than offsets, these pointers can point anywhere in the address space for full random access such that there is no need to store the rows of the set of rows contiguously. Thus, the pointer indirect example does not require a second buffer to supply the rows of the set of rows. While having a second buffer does not interfere with pointer indirection, pointers are wider than offsets, so pointer indirection is typically only used when there is no second buffer.

[0249] As follows and as discussed later in this document, indirect aggregation accommodates segmented inputs such as when the input: a) is too large to allocate contiguous memory, b) is segmented according to a special scheme such as an IMCU for batching or caching, and / or c) is partitioned into horizontal slices as discussed earlier in this document, such as with an upstream partitioning operator. Such segmentation may require indirect aggregation to maintain pointers to the segments currently being processed by the aggregation, shown as dataIN, and which references correspond to which segments, shown as baseIN or clalN. Those corresponding references can be processed as follows.

[0250] The current segment is processed as a contiguous array of references which can be accessed individually such as by iteration (such as according to the variable i shown). For example, baseIN[i] can randomly access one of the references in a first array, shown as an offset or a pointer, which can be used to randomly access the actual data in a second array.

[0251] In all three example indirect aggregations, the first array contains the same corresponding fixed-width references. The second array of the array indirect contains fixed-width data such that the offsets shown are used as array indices into the second array. The pointers of the pointer indirect shown can point to data of fixed width or whose variable width is self-described by a null terminator or a length counter.

[0252] Another way to indirectly aggregate variable-width data is with a Cumulative Length Array (CLA), which serially packs non-self-delimiting variable-width data. The CLA indirectly requires a two-byte offset to access a piece of data in a second array, as shown. In the first array, the shown offset[i] is a one-byte offset that indicates how many bytes after the start of the second buffer a given piece of data begins. In the first array, the shown offset[i+1] is a one-byte offset that indicates how many bytes after the start of the second buffer the next consecutive piece of data begins. Thus, the variable-width given piece of data is delimited in the second array by these two byte offsets.

[0253] Figure 8 is a block diagram depicting three indirect aggregations dedicated to respective scenarios as follows. Non-materialized data such as the output of a filter operator or a relational join discussed earlier in this document can be provided as input to a downstream indirect aggregation, and can indicate a subset of the rows in a row set that have been evaluated as satisfying the filter.

[0254] 9.0 Segment Aggregation

[0255] Figure 9 is a block diagram depicting two segment aggregations. Refer to Figure 8 discussion Figure 9 . As shown, Figure 9 the conventional segment aggregation of Figure 8 is as discussed above for

[0256] Figure 9 where the data segments are the second input buffer, and corresponding references to the second input buffer are stored in the first input buffer, such that the conventional segment aggregation operator accepts the first and second buffers as separate inputs.

[0257] 10.0 Database Overview

[0258] Embodiments of the present invention are used in the context of a Database Management System (DBMS). Accordingly, a description of an example DBMS is provided.

[0259] Generally, a server such as a database server is a combination of integrated software components and the allocation of computing resources such as memory, nodes, and processes on the nodes for executing the integrated software components, where the combination of software and computing resources is dedicated to providing a particular type of functionality on behalf of clients of the server. The database server controls and facilitates access to a particular database, processing requests from clients to access the database.

[0260] A user interacts with the database server of a DBMS by submitting commands to the database server that cause the database server to perform operations on data stored in the database. The user can be one or more applications running on a client computer that is interacting with the database server. Multiple users can also be collectively referred to as the user in this document.

[0261] The database includes data and a database dictionary, which are stored on a persistent storage mechanism such as a hard disk set. The database is defined by its own separate database dictionary. The database dictionary includes metadata that defines the database objects contained in the database. In fact, the database dictionary defines many databases. Database objects include tables, table columns, and table spaces. A table space is a collection of one or more files for storing data of various types of database objects such as tables. If the data for a database object is stored in a table space, then the database dictionary maps the database object to one or more table spaces that hold the data for the database object.

[0262] The DBMS refers to the database dictionary to determine how to execute database commands submitted to the DBMS. Database commands can access database objects defined by the dictionary.

[0263] Database commands can be in the form of database statements. For the database server to process a database statement, the database statement must conform to the database language supported by the database server. A non-limiting example of a database language supported by many database servers is SQL, including proprietary forms of SQL supported by database servers such as Oracle (such as Oracle Database 11g). SQL data definition language ("DDL") instructions are issued to the database server to create or configure database objects such as tables, views, or complex types. Data manipulation language ("DML") instructions are issued to the DBMS to manage data stored within the database structure. For example, SELECT (select), INSERT (insert), UPDATE (update), and DELETE (delete) are common DML instruction examples in some SQL implementations. SQL / XML is a common extension of SQL used when manipulating XML data in object-relational databases.

[0264] A multi-node database management system consists of interconnected nodes that share access to the same database. Typically, the nodes are interconnected via a network and share access to a shared storage device to varying degrees, such as shared access to a set of disk drives and the data blocks stored thereon. The nodes in a multi-node database system can be in the form of a set of computers (such as workstations and / or personal computers) interconnected via a network. Alternatively, the nodes can be nodes of a grid consisting of server blades in the form of nodes interconnected with other server blades on a rack.

[0265] Each node in a multi-node database system hosts a database server. A server (such as a database server) is a combined allocation of integrated software components and computing resources (such as memory, nodes, and processes on the nodes for executing the integrated software components on a processor), a combination of software and computing resources dedicated to performing a specific function on behalf of one or more clients.

[0266] Resources from multiple nodes in a multi-node database system can be allocated to run the software of a specific database server. Each combination of the allocation of software and resources in a node is a server referred to herein as a "server instance" or "instance". A database server can include multiple database instances, some or all of which run on separate computers (including separate server blades).

[0267] 10.1 Query Processing

[0268] A query is an expression, command, or set of commands that, when executed, causes a server to perform one or more operations on a data set. A query can specify the (one or more) source data objects from which to determine the (one or more) result sets, such as the (one or more) tables, (one or more) columns, (one or more) views, or (one or more) snapshots. For example, the (one or more) source data objects can appear in the FROM clause of a Structured Query Language ("SQL") query. SQL is a well-known example language for querying database objects. As used herein, the term "query" is used to refer to any form representing a query, including queries in the form of database statements and any data structure for internal query representation. The term "table" refers to any source object that is referenced or defined by a query and represents a collection of rows (such as a database table, view, or inline query block (such as an inline view or subquery)).

[0269] Queries can perform operations on data from source data objects line by line when (one or more) objects are loaded, or on (one or more) entire source data objects after (one or more) objects have been loaded. The result sets generated by some operations can make (one or more) other operations available, and in this way, the result sets can be filtered or narrowed down based on certain criteria, and / or joined or combined with (one or more) other result sets and / or (one or more) other source data objects.

[0270] A subquery is a part or component of a query that is distinct from the other (one or more) parts or (one or more) components of the query and can be evaluated separately (i.e., as a separate query) from the other (one or more) parts or (one or more) components of the query. The other (one or more) parts or (one or more) components of the query can form an outer query, which may or may not include other subqueries. A subquery nested within an outer query can be evaluated one or more times separately while computing the result for the outer query.

[0271] Generally, a query parser receives a query statement and generates an internal query representation of the query statement. Typically, the internal query representation is a collection of interconnected data structures that represent the various components and structures of the query statement.

[0272] The internal query representation can be in the form of a node graph, where each interconnected data structure corresponds to a node and a component of the query statement being represented. The internal representation is typically generated in memory for evaluation, manipulation, and transformation.

[0273] Hardware Overview

[0274] According to one embodiment, the techniques described herein are implemented by one or more special-purpose computing devices. The special-purpose computing device can be hard-wired to perform the techniques, or can include digital electronic devices (such as one or more application-specific integrated circuits (ASICs) or field-programmable gate arrays (FPGAs) that are persistently programmed to perform the techniques), or can include one or more general-purpose hardware processors programmed to perform the techniques according to program instructions in firmware, memory, other storage devices, or a combination. Such special-purpose computing devices can also combine custom hard-wired logic, ASICs, or FPGAs with custom programming to implement the techniques. The special-purpose computing device can be a desktop computer system, a portable computer system, a handheld device, a networking device, or any other device that combines hard-wired and / or program logic to implement the techniques.

[0275] For example, Figure 10FIG. is a block diagram of a computer system 1000 on which embodiments of the present invention can be implemented. Computer system 1000 includes a bus 1002 or other communication mechanism for conveying information, and a hardware processor 1004 coupled to bus 1002 for processing information. The hardware processor 1004 can be, for example, a general-purpose microprocessor.

[0276] Computer system 1000 also includes a main memory 1006 coupled to bus 1002, such as a random access memory (RAM) or other dynamic storage device, for storing information and instructions to be executed by processor 1004. The main memory 1006 can also be used to store temporary variables or other intermediate information during execution of instructions by processor 1004. When stored in a non-transitory storage medium accessible to processor 1004, these instructions cause the computer system 1000 to become a special-purpose machine customized to perform the operations specified in the instructions.

[0277] Computer system 1000 also includes a read only memory (ROM) 1008 or other static storage device coupled to bus 1002 for storing static information and instructions for processor 1004. A storage device 1010, such as a magnetic disk, optical disk, or solid state drive, is provided and coupled to bus 1002 for storing information and instructions.

[0278] Computer system 1000 can be coupled via bus 1002 to a display 1012, such as a cathode ray tube (CRT), for displaying information to a computer user. An input device 1014 including alphanumeric keys and other keys is coupled to bus 1002 for conveying information and command selections to processor 1004. Another type of user input device is a cursor control 1016, such as a mouse, trackball, or cursor direction keys, for conveying direction information and command selections to processor 1004 and for controlling cursor movement on display 1012. Such input devices typically have two degrees of freedom in two axes, a first axis (e.g., x) and a second axis (e.g., y), which allows the device to specify a position in a plane.

[0279] The computer system 1000 can implement the techniques described herein using custom hardwired logic, one or more ASICs or FPGAs, firmware, and / or program logic that in combination with the computer system causes, or programs, the computer system 1000 to be a special-purpose machine. According to one embodiment, the computer system 1000 performs the described techniques in response to one or more sequences of one or more instructions contained in main memory 1006 being executed by processor 1004. These instructions can be read into main memory 1006 from another storage medium, such as storage device 1010. Execution of the instruction sequence contained in main memory 1006 causes processor 1004 to perform the processing steps described herein. In an alternative embodiment, hardwired circuitry may be used in place of, or in combination with, software instructions.

[0280] As used herein, the term “storage medium” refers to any non-transitory medium that stores data and / or instructions that cause a machine to operate in a particular fashion. Such storage medium may include non-volatile and / or volatile media. Non-volatile media includes, for example, optical discs, magnetic disks, or solid state drives such as storage device 1010. Volatile media includes dynamic memory such as main memory 1006. Common forms of storage medium include, for example, floppy disk, flexible disk, hard disk, solid state drive, magnetic tape, or any other magnetic data storage medium, CD-ROM, any other optical data storage medium, any physical medium with patterns of holes, RAM, PROM, and EPROM, FLASH-EPROM, NVRAM, any other memory chip or cartridge.

[0281] Storage media is distinct from but can be used in conjunction with transmission media. Transmission media participates in transferring information between storage media. For example, transmission media includes coaxial cables, copper wire, and fiber optics, including the wires that comprise bus 1002. Transmission media can also take the form of acoustic or light waves, such as those generated during radio wave and infrared data communications.

[0282] Various forms of media can participate in carrying one or more sequences of one or more instructions to the processor 1004 for execution. For example, the instructions can initially be carried on a magnetic disk or solid state drive of a remote computer. The remote computer can load the instructions into its dynamic memory and send the instructions using a modem over a telephone line. A modem local to the computer system 1000 can receive the data on the telephone line and use an infrared transmitter to convert the data into an infrared signal. An infrared detector can receive the data carried in the infrared signal, and appropriate circuitry can place the data on the bus 1002. The bus 1002 transfers the data to the main memory 1006, and the processor 1004 retrieves and executes the instructions from the main memory 1006. The instructions received by the main memory 1006 can optionally be stored on the storage device 1010 before or after being executed by the processor 1004.

[0283] The computer system 1000 also includes a communication interface 1018 coupled to the bus 1002. The communication interface 1018 provides two-way data communication coupled to a network link 1020, where the network link 1020 is connected to a local network 1022. For example, the communication interface 1018 can be an Integrated Services Digital Network (ISDN) card, a cable modem, a satellite modem, or a modem that provides a data communication connection to a corresponding type of telephone line. As another example, the communication interface 1018 can be a Local Area Network (LAN) card to provide a data communication connection to a compatible LAN. A wireless link can also be implemented. In any such implementation, the communication interface 1018 sends and receives electrical, electromagnetic, or optical signals that carry digital data streams representing various types of information.

[0284] The network link 1020 typically provides data communication through one or more networks to other data devices. For example, the network link 1020 can provide a connection through the local network 1022 to a main computer 1024 or to a data device operated by an Internet Service Provider (ISP) 1026. The ISP 1026 in turn provides data communication services through the global packet data communication network (now commonly referred to as the “Internet” 1028). Both the local network 1022 and the Internet 1028 use electrical, electromagnetic, or optical signals that carry digital data streams. Signals through the various networks and signals on the network link 1020 and through the communication interface 1018, which carry digital data to and from the computer system 1000, are example forms of transmission media.

[0285] The computer system 1000 can send messages and receive data, including program code, via one or more networks, network link 1020, and communication interface 1018. In an Internet example, server 1030 can send the requested code for an application program via Internet 1028, ISP 1026, local network 1022, and communication interface 1018.

[0286] The received code can be executed by processor 1004 when received, and / or stored in storage device 1010 or other non-volatile memory for later execution.

[0287] Software Overview

[0288] Figure 11 is a block diagram of a basic software system 1100 that can be used to control the operation of computing system 1000. Software system 1100 and its components, including their connections, relationships, and functions, are merely exemplary and are not meant to limit the implementation of one or more example embodiments. Other software systems suitable for implementing one or more example embodiments may have different components, including components with different connections, relationships, and functions.

[0289] Software system 1100 is used to direct the operation of computing system 1000. Software system 1100, which can be stored on system memory (RAM) 1006 and fixed storage device (e.g., hard disk or flash memory) 1010, includes a kernel or operating system (OS) 1110.

[0290] OS 1110 manages the low-level aspects of computer operation, including managing the execution of processes, memory allocation, file input and output (I / O), and device I / O. One or more applications, represented as 1102A, 1102B, 1102C... 1102N, can be "loaded" (e.g., transferred from fixed storage device 1010 into memory 1006) for execution by system 1100. Applications or other software intended to be used on computer system 1000 can also be stored as a downloadable set of computer-executable instructions, e.g., for downloading and installation from an Internet location (e.g., a web server, app store, or other online service).

[0291] Software system 1100 includes a graphical user interface (GUI) 1115 for receiving user commands and data in a graphical manner (e.g., "clicking" or "touch gestures"). In turn, these inputs can be operated on by system 1100 according to instructions from operating system 1110 and / or one or more applications 1102. GUI 1115 is also used to display the results of operations from OS 1110 and one or more applications 1102, where the user can provide additional input or terminate the session (e.g., log off).

[0292] The OS 1110 can execute directly on the bare hardware 1120 of the computer system 1000 (e.g., the (one or more) processors 1004). Alternatively, a hypervisor or virtual machine monitor (VMM) 1130 can be inserted between the bare hardware 1120 and the OS 1110. In this configuration, the VMM 1130 acts as a software “buffer” or virtualization layer between the OS 1110 and the bare hardware 1120 of the computer system 1000.

[0293] The VMM 1130 instantiates and runs one or more virtual machine instances (“guest machines”). Each guest machine includes a “guest” operating system (such as the OS 1110), and one or more applications (such as the (one or more) applications 1102) designed to execute on the guest operating system. The VMM 1130 presents a virtual operating platform to the guest operating system and manages the execution of the guest operating system.

[0294] In some cases, the VMM 1130 can allow the guest operating system to run as if it were running directly on the bare hardware 1120 of the computer system 1000. In these instances, the same version of the guest operating system configured to execute directly on the bare hardware 1120 can also execute on the VMM 1130 without modification or reconfiguration. In other words, the VMM 1130 can provide full hardware and CPU virtualization to the guest operating system in some cases.

[0295] In other cases, the guest operating system can be specifically designed or configured to execute on the VMM 1130 for increased efficiency. In these instances, the guest operating system “is aware” that it is executing on the virtual machine monitor. In other words, the VMM 1130 can provide para-virtualization to the guest operating system in certain cases.

[0296] A computer system process includes the allocation of hardware processor time, as well as the allocation of memory (physical and / or virtual), the allocation of memory for storing instructions executed by the hardware processor, for storing data generated by the execution of instructions by the hardware processor, and / or for storing the hardware processor state (e.g., the contents of registers) between allocations of hardware processor time when the computer system process is not running. Computer system processes run under the control of an operating system and can also run under the control of other programs that can execute on the computer system.

[0297] Cloud Computing

[0298] This document generally uses the term “cloud computing” to describe a computing model that enables on-demand access to a shared pool of computing resources, such as computer networks, servers, software applications, and services, and allows for the rapid provisioning and release of resources with minimal administrative effort or service provider interaction.

[0299] Cloud computing environments (sometimes referred to as cloud environments or the cloud) can be implemented in a variety of different ways to best suit different requirements. For example, in a public cloud environment, the underlying computing infrastructure is owned by an organization that makes its cloud services available to other organizations or the public. In contrast, a private cloud environment is generally only used by or within a single organization. Community clouds are intended to be shared by several organizations within a community; and hybrid clouds include two or more types of clouds (e.g., private, community, or public) bound together through data and application portability.

[0300] Generally speaking, cloud computing models enable some of those responsibilities that may previously have been provided by an organization's own information technology department to instead be delivered as service layers within a cloud environment for consumption by consumers (either within or outside the organization, depending on the public / private nature of the cloud). Depending on the specific implementation, the precise definition of the components or features provided by or within each cloud service layer may vary, but common examples include: Software as a Service (SaaS), where the consumer uses software applications running on cloud infrastructure while the SaaS provider manages or controls the underlying cloud infrastructure and applications. Platform as a Service (PaaS), where the consumer can use software programming languages and development tools supported by the PaaS provider to develop, deploy, and otherwise control their own applications while the PaaS provider manages or controls other aspects of the cloud environment (i.e., everything under the runtime execution environment). Infrastructure as a Service (IaaS), where the consumer can deploy and run any software applications, and / or provision processes, storage, networks, and other basic computing resources while the IaaS provider manages or controls the underlying physical cloud infrastructure (i.e., everything below the operating system layer). Database as a Service (DBaaS), where the consumer uses a database server or database management system running on cloud infrastructure while the DbaaS provider manages or controls the underlying cloud infrastructure and applications.

[0301] The foregoing basic computer hardware, software, and cloud computing environment are presented to illustrate the basic underlying computer components that can be used to implement one or more example embodiments. However, one or more example embodiments need not be limited to any particular computing environment or computing device configuration. Instead, in accordance with the present disclosure, one or more example embodiments can be implemented in any type of system architecture or processing environment that those skilled in the art will understand to be capable of supporting the features and functionality presented herein.

[0302] In the foregoing specification, embodiments of the invention have been described with reference to numerous specific details, which may vary from implementation to implementation. Accordingly, the specification and drawings are to be regarded as illustrative rather than restrictive. The sole and exclusive indication of the scope of the invention, and what the applicant intends to be the scope of the invention, is the literal and equivalent scope of the set of claims that is issued from this application in the specific form of such claims, including any subsequent corrections.

Claims

1. A method, comprising: Receiving a data access request for a plurality of tuples; Compiling the data access request into one or more relational operators; Dynamically selecting a specific implementation for a specific relational operator among the one or more relational operators from a plurality of interchangeable implementations, wherein each implementation among the plurality of interchangeable implementations includes a corresponding one or more physical operators; Dynamically selecting a specific hardware operator for a specific physical operator among the one or more physical operators from a plurality of interchangeable hardware operators, the hardware operators including: a first hardware operator executed on a first processing hardware, and a second hardware operator executed on a second processing hardware, the second processing hardware being functionally different from the first processing hardware; Wherein the dynamically selecting the specific implementation is further based on the dynamic availability of: a) Available memory, b) Available processing bandwidth, or c) The expected memory consumption or processing time of: the specific implementation, the specific physical operator, or the specific hardware operator; Generating an execution plan for the data access request, the execution plan being a directed acyclic graph (DAG): It is not a tree, and Includes the one or more physical operators for each relational operator among the one or more relational operators; Interpret or otherwise execute the generated execution plan; Generating a response to the data access request, the response being based on: the plurality of tuples, the specific implementation of the specific relational operator, and the specific hardware operator; Wherein the response is a reply to the data access request, the reply including a final result set.

2. The method according to claim 1, wherein: The method further includes transmitting columnar data to or from the specific hardware operator; The transmitting of the columnar data is based on pipeline parallelization and: asynchronous or data batches.

3. The method according to claim 2, further including a machine learning (ML) model predicting an optimal fixed size for the data batch.

4. The method according to claim 1, wherein: Compiling the data access request includes generating an initial execution plan for the data access request, the initial execution plan not being based on processing hardware; The dynamically selecting the specific implementation is based on the initial execution plan; Wherein generating the execution plan for the data access request is based on: the specific implementation of the specific relational operator, and the specific hardware operator.

5. The method according to claim 1, further including: Detecting whether the specific hardware operator and the second hardware operator can be fused into a combined hardware operator for a relational operator that is the same as or different from the specific hardware operator, and Fusing the specific hardware operator and the second hardware operator into the combined hardware operator within the same lexical scope and based on the detection.

6. The method according to claim 1, wherein: The plurality of tuples contain columns; The method further includes: Transmitting specific data from the specific physical operator to a specific physical operator that is an aggregation operator, the specific data: being based on the plurality of tuples and not based on the columns; and Combining the columns with the specific data by the aggregation operator.

7. The method according to claim 6, wherein: the plurality of interchangeable embodiments includes a second embodiment, the second embodiment including a second physical operator that is a second aggregation operator different from the aggregation operator; each of the aggregation operator and the second aggregation operator is: direct aggregation that replicates row identifiers or does not use row identifiers, indirect aggregation that dereferences row identifiers, or segmented aggregation that can process segmented arrays.

8. The method according to claim 7, wherein the segments of the segmented array contain: data values and references to the data values.

9. The method according to claim 7, further comprising dynamically selecting the indirect aggregation from at least two of the following: aggregation of an array for reading data, aggregation of dereferencing multiple memory pointers, and double indirect aggregation.

10. The method according to claim 1, wherein: the plurality of tuples includes build data rows and probe data rows; the data access request requires a relational join of the build data rows and the probe data rows; the dynamically selecting a specific embodiment includes: dynamically selecting, as a build operator, a first physical operator from a plurality of interchangeable build operators, and dynamically selecting, as a probe operator, a second physical operator from a plurality of interchangeable probe operators.

11. The method according to claim 10, wherein: the specific physical operator includes a build operator or a probe operator; a third physical operator includes: a join key decoder operator, a decompression operator, a dictionary encoder operator, a dense grouping key operator that can generate a sequence containing gaps, a statistical mean operator, a filter operator that applies a simple predicate, a classification operator, a merge operator that preserves the sorting of inputs with multiple classifications, a text search operator, a horizontal row partitioning operator that uses a join key, a build key insertion operator that uses buckets of a hash table, or an insertion overflow operator that uses buckets of a hash table; the method further comprises dynamically selecting, based on the specific physical operator, a specific hardware operator for the third physical operator from a second plurality of interchangeable hardware operators.

12. The method according to claim 11, wherein: operator integration includes: fusion into a combined operator, or asynchronous pipelining; the method further comprises applying operator integration to connect two of the following: a build operator, a probe operator, and a third physical operator.

13. The method according to claim 1, wherein the first processing hardware is: a single instruction multiple data (SIMD) processor, a graphics processing unit (GPU), a field programmable gate array (FPGA), a direct access (DAX) coprocessor, or an application specific integrated circuit (ASIC) that includes pipelined parallelization.

14. One or more non-transitory computer-readable storage media storing instructions that, when executed by one or more processors, cause operations including those recited in any one of claims 1-13 to be performed.

Images