Techniques for heterogeneous hardware execution of SQL analytics queries for mass data processing

Abstract

The method is used for the heterogeneous hardware execution technology of SQL analysis query for large-capacity data processing. More particularly, the invention relates to optimized access to a database. This document is a technique to accelerate 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 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 a particular physical operator is selected from a plurality of interchangeable hardware operators, including a first hardware operator executing on a first processing hardware, and a second hardware operator executing on a second processing hardware, the second processing hardware being functionally distinct from the first processing hardware. The response to the data access request is generated based on a data tuple, a particular implementation of a particular relational operator, and a particular hardware operator.

Description

[0001] This application is a divisional application of Chinese patent application 202080063355.9, whose application date is September 10, 2020 and whose invention name is “Technology for heterogeneous hardware execution of SQL analytical queries for large-capacity data processing”. Technical Field

[0002] The present invention relates to optimized access to a database. This invention relates to techniques for accelerating the execution of any combination of self-organizing queries, heterogeneous hardware, and fluctuating workloads. Background Art

[0003] In a relational database system, queries can be defined using relational algebra, and query plans can be compiled and represented as a binary tree of relational operators. Subsequently, during the execution phase, the database data is traversed through different relational operators to compute the results. At runtime, a row in a set of rows (such as a relational table) is extracted individually from the child relational operators in the tree and returned to the parent relational operator. In some cases, rows are transferred between relational operators in batches of several hundred rows.

[0004] Relational operators are coarse-grained logical units that must be prepared to handle billions of sub-use cases, supporting all potential use cases that are combinatorially possible due to the varying relational schemas and data value distribution. The decision about which of the many control flow branches to take for a particular row among millions of rows is made partly at compile time using static plan optimization and partly at runtime using self-organizing data-driven algorithms. The technical challenge is that the algorithms for relational operators must be suitable for all use cases, including those that cannot arise for the current query on the current data and those that will never arise on a given database server. Regardless of whether batch processing is used, the relational operator tree approach results in inefficient data processing for large amounts of data.

[0005] Prior art database systems can support Structured Query Language (SQL) analytical queries on large data by vertically partitioning the data, such as using columnar databases that can encode or unencode and compress the columns of relational tables. As a result, thousands or millions of rows of data in the same column are stored together and ready to benefit from vectorized processing. However, the relational operator tree approach in the SQL execution engine only has data uniformly encoded in the columns in the base table scan, and then the relational operators of the table scan eagerly and completely decode from this data and convert the columns back to row-first data for analysis. At best, this is just a retrofit of the old-fashioned row processing model to modem hardware. Generally speaking, such approaches involve data structures, formats, and algorithms designed for outdated computing styles and hardware. BRIEF DESCRIPTION OF THE DRAWINGS

[0006] In the attached figure:

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

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

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

[0010] Figure 4 is a flow diagram depicting an example query executed by a DBMS that includes an example transpose operator that applies a matrix transpose to a relational table or other set of rows.

[0011] Figure 5 is a block diagram describing an example directed acyclic graph (DAG) of physical operators for an example physical plan for a query that is easily portable to different hardware architectures and that can be opportunistically offloaded to different hardware, such as coprocessor(s).

[0012] Figure 6 is a flow diagram depicting an example process that a DBMS may use to plan and optimize a data flow that includes a hash table, such as for a hash join.

[0013] Figure 7 is a flow chart depicting an example DAG optimization process that a DBMS may 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.

[0014] Figure 8 It is a block diagram depicting three types of indirect gathering specifically used for corresponding scenarios.

[0015] Figure 9 is a block diagram depicting two types of segmented aggregation.

[0016] Figure 10 is a block diagram illustrating a computer system upon which embodiments of the present invention may be implemented;

[0017] Figure 11 is a block diagram illustrating a basic software system that may be used to control the operation of a computing system. DETAILED DESCRIPTION

[0018] 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. However, it will be apparent that the present invention can be practiced without these specific details. In other instances, well-known structures and devices are shown in block diagram form to avoid unnecessarily obscuring the present invention.

[0019] General Overview

[0020] This paper presents a novel execution technique for relational queries, comprising a collection of hardware-friendly operations, referred to herein as physical operators, that are finer-grained than known relational operators, yet hardware-independent and capable of accepting columnar data for increased throughput. Each relational operator can be represented as a dataflow network of physical operators. Each physical operator represents the smallest unit of beneficial work that is easily mapped to hardware for processing, as described herein.

[0021] Important characteristics of physical operators include the following design dimensions that affect query planning and optimization, as discussed in this article.

[0022] There are four types based on how physical operators are scheduled: blocking, pipelined, cacheable, and fusible.

[0023] 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 release memory for recycling, such as by discarding intermediate data that is no longer needed;

[0024] • Based on the primary resource requirements of the physical operator, such as compute-bound versus bus / memory bandwidth-bound.

[0025] Optimization requires tuning the above dimensions to balance these issues based on heterogeneous hardware and dynamic conditions such as fluctuating workloads and data value distributions. This optimization, along with the approach described in this paper, which decomposes each relational operator, fundamentally changes how a database management system (DBMS) processes queries, facilitating increased efficiency and other performance improvements in the DBMS computer itself, including the following benefits.

[0026] By decomposing relational operators into physical operators, query plans are transformed into directed acyclic graphs (DAGs) with finer-grained operators and more interconnected data flow paths between operators, thereby exposing more optimization opportunities, both within and between operators.

[0027] 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.

[0028] Collect features and metrics offline to model different workload scenarios, and use historical data to train machine learning (ML) models for optimal tuning of operator configuration settings (such as batch size);

[0029] At runtime, the ML model observes the current workload and predicts the optimal values ​​for configuration settings for each physical operator.

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

[0031] Because the query is first decomposed into physical operators that are smaller units of work, and depending on the characteristics of the interconnected physical operators (such as blocking, pipelining, caching, and fusibility discussed in this article), there is a huge solution space of possible execution graphs for which the optimizer / scheduler can be resource-aware, such as based on the fluctuating conditions and heterogeneous variety, amount, and capacity of the available hardware.

[0032] As described herein, awareness of fluctuating conditions and hardware diversity facilitates highly opportunistic optimizations. Different optimization techniques described herein can be applied to the same physical operator when executed on different platforms, even during the same execution of a query. The uniformity of the physical operator interface facilitates reusable optimization heuristics, even for DBMSs with heterogeneous hardware, thereby encouraging various horizontal and / or pipeline parallelisms, such as offloading operators to differential coprocessors.

[0033] For example, known SQL processing is decomposed solely into relational operators. Therefore, regardless of compiler choices, the entire operator and / or operator tree is optimized purely for the central processing unit (CPU). Our fine-grained physical operators are individually optimized for heterogeneous approaches, such as single instruction multiple data (SIMD) instructions and / or coprocessor offloads, particularly for columnar data.

[0034] For example, in this article, the join relation operator can be decomposed into multiple finer-grained operations, such as hashing, building a hash table, building a bloom filter, probing a hash table and / or gathering fields from a hash table (such as for projection from a hash table). Some of these operations can be further decomposed. For example, the construction of a hash table is further decomposed into the decoding of the join key value, the partitioning of the row based on the join key value, the insertion of the join key value and other accompanying values ​​into the bucket of the hash table, and the disposal of overflow. The physical operators for all those activities can be interconnected to realize pipeline parallelism and / or horizontal scaling, such as with symmetric multiprocessing (SMP). Fine-grained operators provide more decomposition, more decoupling, and therefore provide more asynchrony for load balancing and unloading, thereby improving throughput.

[0035] In one embodiment, a computer receives a data access request for a data tuple and compiles the data access request into a relational operator. A specific implementation of a particular relational operator is dynamically selected from a plurality of interchangeable implementations. Each interchangeable implementation includes a corresponding physical operator. A specific hardware operator for the particular physical operator is selected from a plurality of interchangeable hardware operators, including: a first hardware operator executed on first processing hardware; and a second hardware operator executed on 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 specific implementation of the particular relational operator, and the specific hardware operator.

[0036] 1.0 Sample Computer

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

[0038] In various embodiments, DBMS 100 stores and provides access to a large-scale 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 a document 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 DBMS 100. The data stored in the data repository can reside in volatile and / or non-volatile storage devices.

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

[0040] In operation, DBMS 100 receives or generates data access requests 110 to read and / or write data in a data repository. In an embodiment, 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, data access request 110 may be a structured query language (SQL) DML statement, such as a query. In an embodiment, data access request 110 is received via Open Database Connectivity (ODBC).

[0041] DBMS 100 compiles or otherwise interprets data access request 110 based on generalized operators that respectively inspect, rearrange, modify, transfer, or otherwise process large blocks of data such as tuples 121-122 or derived data such as intermediate results. In an embodiment, DBMS 100 compiles data access request 110 into a query plan that is arranged as a logic tree (not shown) of relational operators 131-132.

[0042] In an embodiment, the logical tree is derived by modifying or otherwise transforming a parse tree generated from the data access request 110. In a modified embodiment, each node of the parse tree may 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.

[0043] 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 may be relational algebraic operators. For example, the relational operator 131 may represent a relational table scan or a relational join of two relational tables.

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

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

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

[0047] Each of the relational operators 131-132 is composed of the same or different amounts of (one or more) physical operators. None, some, or all of the physical operators of one relational operator may be the same as the physical operators of another relational operator. For example, embodiments 131A-B both include physical operator 161, which means that physical operator 161 has two occurrences or instances that may be configured the same or slightly differently.

[0048] An important aspect of configuring a physical operator is which hardware operator is used for that 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 illustrated instances of physical operator 161 can have corresponding instances of the same hardware operator 161B. Alternatively, one instance of physical operator 161 can have hardware operator 161B as shown, while another instance of physical operator 161 can instead have hardware operator 161A.

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

[0050] Query plan optimization herein involves dynamically selecting a specific implementation from among several interchangeable implementations of the same relational operator based on fluctuation conditions. For example, DBMS 100 should dynamically select any of the most efficient implementations 131A-B for relational operator 131. In embodiments discussed later herein, this dynamic selection is based on cost according to various computer resources (such as processing hardware, processing time, and / or memory space).

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

[0052] As explained later herein, cost calculations 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 with 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 to actually execute the optimized query plan for data access request 110.

[0053] All relational operators, their implementations (such as 131AB and 132A-B), physical operators, and hardware operators are reusable, either directly or by instantiation (such as from a template), and can therefore be incorporated into different execution plans for the same or different queries. For example, DBMS 100 may 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 one hardware operator to each instance of the physical operator.

[0054] An embodiment may have a templated predefined relational operator implementation that includes physical operators but not hardware operators, such that DBMS 100 eagerly or lazily generates precise relational operator implementations (such as 131A-B and 132A-B) by assigning hardware operators to instances of physical operators. In that way, the templated predefined relational operator implementation and its physical operators are hardware independent and fully portable, such as across different instruction set architectures and processing methods, such as GPUs and CPUs.

[0055] In other words, when DBMS 100 has heterogeneous hardware, such as a mix of GPUs and CPUs, such operator components herein readily adapt to the new hardware to accommodate the future and seamlessly leverage 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 storage computers that provide data persistence, such as with smart scans).

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

[0057] In fact, the ultimate distinction 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 directed acyclic graph of hardware operators. 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.

[0058] As discussed later in this document, operators are interconnected, such as in a data flow graph, so 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.

[0059] Each operator instance also has configuration settings that are set when the operator instance is generated and are not typically adjusted subsequently. Topological details such as which upstream operators provide which inputs and which downstream operators receive outputs can be configuration settings for an operator instance. Additional configuration settings can 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 location within a query plan (such as within the operator's DAG).

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

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

[0062] 2.0 Example Query Compilation Process

[0063] Figure 2is a flow chart depicting an example query compilation process that DBMS 100 performs to dynamically select a specialized implementation from among several interchangeable implementations of the same relational operator to be invoked during database query execution based on fluctuation conditions.

[0064] Step 201 receives a data access request 110 for tuples 121 - 122 , such as an SQL query via ODBC as discussed previously herein.

[0065] Step 202 compiles the data access request 110 into the relational operators 131 - 132 , such as by parsing the data access request 110 into a parse tree according to the relational algebra discussed previously herein.

[0066] Step 203 dynamically selects a particular implementation 131A of the relational operator 131 from a plurality of interchangeable implementations 131A-B. As previously discussed herein, each of the implementations 131A-B includes some of the physical operators 161-164 that are independent of the hardware architecture. The implementations 131A-B can be predefined and / or templated as previously discussed herein. The DBMS 100 can dynamically generate or otherwise dynamically select implementation 131A based on fluctuating conditions such as resource availability and / or expected resource consumption of the physical operators 161-164, such as through cost calculations as previously discussed herein.

[0067] Step 204 dynamically selects a specific hardware operator 161B for a specific physical operator 161 from a plurality of 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 actual hardware inventory, fluctuating hardware workloads, and / or hardware allocation quotas, as discussed later herein. Various embodiments can have various numbers and types of hardware processors, such as the other types of hardware exemplified below.

[0068] Single Instruction Multiple Data (SIMD) processors, as explained later in this article,

[0069] Field Programmable Gate Array (FPGA),

[0070] A direct access (DAX) coprocessor for nonvolatile random access memory (RAM), and

[0071] • Application-Specific Integrated Circuits (ASICs) that incorporate pipeline parallelism, as explained later in this article.

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

[0073] 3.0 Example SQL Parse Tree

[0074] Figure 3 is a block diagram depicting an example relational algebra parse 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)fromemp,dept where dept.dept_id=emp.dept_id;

[0075] In the example SQL query, each row in the emp table represents an employee, and each row in the dept table represents a department. This example SQL query calculates the average salary for each department. As shown in the example result set, the dept table contains dept1-2 as identifiers, and the example SQL query calculates the corresponding avg1-2 as a number.

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

[0077] 4.0 Example Transposition Process

[0078] The following diagrams, presented later in this article, show various techniques for accelerating the execution of any combination of self-organizing queries, heterogeneous hardware, and fluctuating workloads. The following transpose operator demonstrates that physical operators can provide exotic hardware-independent functionality and can be used in special ways to Figure 1The relational operators 131-132 are used within and between them. This transposition operator illustrates that various physical operators may be beyond the vocabulary of relational algebra due to finer granularity and exotic semantics. Other exotic kinds of physical operators will be introduced later.

[0079] Transposition is unique because it is not built into SQL. Known workarounds for transposition typically require hard-coded SQL logic for a specific table, such as using pivot operations or complex use of database cursors. Known general workarounds that are independent of the table require subqueries and dynamically composed SQL, which is expensive.

[0080] In any case, known workarounds have query plans that include multiple relational operations, each of which can include multiple physical operators according to the techniques herein. The transpose operator implements the same transposition as a single physical operator. Similarly, the transpose operator and its hardware counterparts better utilize specialized hardware (such as GPUs) that accelerate matrix operations, such as those with tabular data. Unlike SQL's pivot operator, the transpose operator does not use a pivot column.

[0081] Figure 4 is depicted by Figure 1 A flowchart of an example query execution performed by DBMS 100 of FIG. 1 includes an example transpose operator for applying a matrix transpose to a relational table or other set of rows. Figure 1 and Figure 3 discuss Figure 4 .

[0082] Step 402: Receive Figure 1 The data access request 110, such as previously discussed herein, is a data manipulation language (DML) statement such as for SQL. As discussed above, the data access request 110 does not specify a pivot column.

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

[0084] The transpose operator is a physical operator that performs the transposition described above. In one embodiment, the transpose physical operator is a hardware transpose operator that provides matrix acceleration via specialized hardware, such as a GPU. In one embodiment, and in lieu 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 specifying a positive or negative multiple of a quarter turn. Neither the transpose operator nor the rotate operator uses the pivot column required for SQL pivoting.

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

[0086] Step 408 generates a response 150 based on the transposition of step 406. For example, the response 150 may include some or all of the bottom row set, or be based on the bottom row set in other ways. For example, the bottom row set shown is emitted as the output of the transpose operator and may or may not be an intermediate row set that is used as input for further processing by downstream operators.

[0087] 5.0 Example Data Flow

[0088] Figure 5 is a block diagram depicting an example directed acyclic graph (DAG) 500 of physical operators that are easily portable to different hardware architectures and that can be opportunistically offloaded to different hardware (such as coprocessor(s) explained later herein) for an example physical plan for a query. Figure 3 discuss Figure 5 . Figure 5 Shown is a DAG 500 and various tabular results 510, 520N, 520S, 530, and 540, which are not part of the DAG 500 but are example data generated at different times by the operation of various illustrated physical operators discussed below and subsequently herein as part of the DAG 500.

[0089] DAG 500 can logically operate as a data flow graph.As explained later herein, data flows through and between the physical operators of DAG 500 in the direction of the arrows shown interconnecting the physical operators. Figure 3shows the parse tree for the relational operators that was generated as a high-level execution plan for the example query presented earlier in this article.

[0090] Likewise, DAG 500 can be obtained from Figure 3 The parse tree is generated as an intermediate-level execution plan. Figure 3 and Figure 5 An intuitive comparison reveals that generating an intermediate execution plan from an initial plan increases the complexity of the specification. However, the semantics of the plan between the two plans remains unchanged. In other words, Figure 3 and Figure 5 The execution plans shown in represent the same example query and achieve exactly the same query results.

[0091] As previously mentioned Figure 3 As explained, the example query calculates the average salary by department, which requires, in order: a) joining the department table to the employee table, b) grouping the join results by department, and c) averaging the salaries for the department group. For simplicity, Figure 3 The grouping and averaging are shown combined into a single relational operator AGG (aggregate), but actual embodiments of the parse tree may instead have separate relational operators for grouping and averaging, respectively.

[0092] DAG 500 is more complex than a parse tree because physical operators are finer-grained than relational operators, such that one relational operator can be represented by multiple physical operators. Therefore, visually identifying joins, groupings, and averages for the example query in DAG 500 may not be obvious, as explained below and in more detail later in this document.

[0093] The Build physical operator, Probe physical operator, and Hash Table HT in DAG 500 collaborate to perform the join in the example query. The Dense Group Key (DGK) physical operator in DAG 500 performs the grouping in the example query. The Average (AVG) physical operator in DAG 500 performs the averaging in the example query. However, DAG 500 includes many more specialized physical operators that collaborate in execution and data flow for the example query, as shown below.

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

[0095] As explained later in this document, each table scan operator generates a separately scanned dataflow path, allowing some or all table scan operators to execute in parallel. Also as explained later in this document, multiple physical operators in the same scanned dataflow path can collaborate as a processing pipeline. For example, the table scan operator ED generates a scanned dataflow path that includes a downstream re-encode operator and a hash operator, as shown in the figure. The semantics of the shown types of physical operators (such as re-encode and hash) are distinct, as explained later in this document.

[0096] Dataflow graphs such as DAG 500 can transfer data between operators in ways that parse trees cannot, as described below. Particularly important for topological composition is the fan-in and fan-out of connection operators. Fan-in is the process of converging multiple upstream dataflow paths into the same operator.

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

[0098] Fan-out is the distribution of the same or different data from one operator to multiple downstream dataflow paths. In other words, physical operators can have multiple outputs, while relational operators cannot. Therefore, dataflow graphs such as DAG 500 can transmit data between operators in ways that parse trees cannot, including issuing multiple downstream dataflow paths that can execute concurrently as discussed later in this article. Therefore, DAG 500 can be more efficient than Figure 3 The query tree has more parallelization.

[0099] 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 fan-out output FO, so that two downstream aggregation operators N and S receive the same probe operator output. As shown, a DAG can have both fan-in and fan-out, while a tree cannot have both.

[0100] 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 GPUs or single instruction multiple data (SIMD) may be more suitable for columnar data. Similarly, most relational algebra operations focus on specific columns, such as joins, filters, sorts, groupings, and projections. Therefore, converting row-major data to columnar data may be necessary or beneficial.

[0101] For example, the first part of the DAG may have row-first data flow and the second part may have columnar data flow, and transformations may be required to make the data flow between the two parts. Figure 3 The matrix transpose discussed, or aggregation as discussed later in this article, can accomplish this conversion from row-major to column-major and vice versa.

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

[0103] 5.1 Example Parallelization

[0104] Because the build operator is preceded by an upstream partition operator, the build phase of the partitioned hash table HT for the hash join is horizontally partitioned for horizontal scaling to accelerate through 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 multiprocessing (SMP), such as with multi-core CPUs. In extreme examples, distributed programming may require elastic horizontal scaling, such as with a cloud of computers (such as virtual computers).

[0105] For example, a partitioning operator can accept a configuration setting indicating the degree of parallelism, and the DBMS can assign the degree of parallelism 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 can help determine the degree of parallelism is how much of the parallelization quota is currently unused.

[0106] For example, a particular client of the DBMS may be limited to using a maximum of five processing cores simultaneously, and two of those cores may already be allocated to another portion of DAG 500. Similarly, the DBMS may be hosted by a virtual machine that is limited to using only four GPUs simultaneously. If three of those GPUs are allocated to another client of the DBMS, the degree of parallelism may be limited to 1 CPU + 1 GPU = two.

[0107] In any case, a single build physical operator (shown as a horizontal slice HS as explained later in this document) that is shown as accepting partitioned input 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 clearing scheme for different computers (such as a loosely federated computer including two blade computers and one desktop computer), a single build physical operator can have three build hardware operator instances, i.e., 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 load to different computers or coprocessors. Therefore, query planning and optimization in this document can be highly opportunistic, such as according to fluctuating workloads.

[0108] Partitioning will be explained further later in this article. More generally, other types of parallelization of physical operators are as follows. Similar to circuit diagrams that arrange components in series or parallel, DAG 500 arranges physical operators in series or parallel. More specifically, DAG 500 consists of parallel data flow paths.

[0109] 5.2 Example Joins and Forks of Data Flow Paths

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

[0111] In this example, the probe operator is used to equijoin the tables dept and emp, involving primary keys dept.dept_id and emp.emp_id, and the foreign key emp.dept_id, loaded by table scan operators DD and ED, as discussed earlier herein. As shown, matching the probe operator can generate an example join result 510. As discussed below and later herein, join result 510 contains only reference data that may or may not be materialized as follows.

[0112] Although not shown, a non-materialized embodiment of the join result 510 will only contain pointers to values ​​within a columnar vector or to rows in row-major data, such as memory addresses or array offsets (e.g., encoding a dictionary code used as an ordinal). For example, what is shown in the join result 510 as a pair of identifiers (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 herein.

[0113] In the materialized example shown, join result 510 includes the primary key columns of the rows that matched during 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 join result 510. In this example, each employee, Emp1-4, matches once, resulting in join result 510 containing four pairs of primary key values ​​from the two scanned and joined tables, dept and emp.

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

[0115] Depending on the embodiment, the join result 510 may be contained in a buffer, batch, or stream, and may be column-oriented or row-major, as discussed elsewhere herein. Depending on the embodiment, the fan-out output FO provides identical or separate copies of the join result 510 as input to the aggregate operators N and S. The mechanism for communicating the join result 510 may require copying the value or some type of pointer to the value, such as discussed later herein with direct and indirect aggregates.

[0116] In an embodiment, the aggregation operators N and S each receive as input a pointer to the same buffer containing the join result 510. In a column-oriented embodiment, the join result 510 is provided as multiple pointers, each pointing to a value vector for each column in the join result 510, and those vectors need not be adjacent to each other in memory.

[0117] 5.2 Example Operator Collaboration

[0118] Physical operators on separate parallel paths can be executed in parallel for acceleration, such as using separate hyperthreads, cores, or processors. For example, two hash operators can execute in parallel because they participate in separate dataflow paths. Physical operators residing in the same dataflow path in a DAG are arranged serially but can still be accelerated through pipelining, allowing upstream operators to process the next row or batch of rows while downstream operators simultaneously process the previous row or batch.

[0119] Therefore, a serial data flow path can be divided into multiple sections such that: each section is a separate pipeline stage; each section 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 serially and can participate in the same pipeline. For example, the previous stage of a pipeline can contain Gather N and DGK operators, while the next stage of that pipeline can contain an AVG operator.

[0120] Both physical aggregation operators N and S receive the same or separate copies of the join result 510, as explained above. While the aggregation mechanism is discussed later in this article, the aggregated results of aggregation operators N and S are as follows. Two mutually exclusive embodiments are shown together. One embodiment generates separate aggregated results 520N and 520S from aggregation operators N and S, respectively. Another embodiment instead generates a combined aggregated result 530 that aggregation operators N and S collaboratively populate.

[0121] Whether aggregation operators N and / or S aggregate directly or indirectly depends on whether the aggregated inputs are materialized. As shown, aggregation operators N and S each have two inputs, one of which is the join result 510. For aggregation operators N and S, the other input is the columnar output of table scan operators DN and ES, respectively. That is, aggregation operators have fan-in, so that they accept multiple inputs from multiple corresponding data streams, as discussed elsewhere herein.

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

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

[0124] As explained above, the combined aggregate result 530 is a design alternative to the individual aggregate result pairs 520N and 520S, such that the aggregate operators N and S collaboratively populate the combined aggregate result 530. The aggregate operators N and S respectively populate the dept.dept_name and emp.emp_sal columns in the aggregate result 530. As shown, the aggregate result 530 is materialized, which can be row-major or column-wise.

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

[0126] 5.3 Example Optimization

[0127] In various embodiments, multi-column row sets or individual columns can be segmented into batches, such as in-memory compression units (IMCUs), so that the next pipeline stage processes the 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 to decouple adjacent stages of the same pipeline. For example, adjacent stages can operate asynchronously with respect to each other.

[0128] For example, with any kind of buffering between adjacent stages, it's tolerable that the previous stage can produce many rows or batches while the next stage consumes only one row or batch simultaneously due to mismatched bandwidth between stages. If the buffer overflows, the execution of upstream operators is suspended until space becomes available in the buffer. For example, DAG 500 can experience backpressure that propagates backward along the dataflow path, which is tolerable.

[0129] As explained earlier in this document, each physical operator is executed by a corresponding hardware operator. In embodiments within the same pipeline stage, some or all hardware operators or some or all physical operators are fused into a combination 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 two hardware operators can be fused even if their physical operators are not fused.

[0130] Another type of parallel acceleration is single instruction multiple data (SIMD) for inelastic horizontal scaling. Regardless of whether the hardware operator is created through fusion, the hardware operator can use SIMD internally to accelerate through data parallelism, as explained later in this article.

[0131] Two physical operators or two hardware operators can be fused even if they are included in the implementation of different relational operators. Figure 1 In

[0066] , any physical operator 161-162 and / or hardware operator 161B in the same implementation 131A of a relational operator 131 may be fused with an operator in an implementation 132B of a different relational operator 132. The mechanism of fusion may be as follows.

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

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

[0134] 5.4 Example Aggregation

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

[0136] Aggregation differs from joins in that joins combine data that is not yet related, whereas aggregation combines data that is related, although not actually stored together. Aggregation can complement joins, especially after a join, such as when projecting columns from the join results. For example, such a post-join projection is used in Figure 5In the example, two downstream aggregation operators, N and S, are shown fanning out from the probe operator because both the dept_name and emp_sal columns being aggregated in the materialization scenario shown require separate aggregation operators for the join results, as discussed later in this article. Due to eager or deferred materialization, the direct and indirect aggregations are eager or deferred, respectively, as compared later in this article.

[0137] 5.5 Keys and Codes

[0138] In various embodiments, a recode 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 encoding dictionary, such as when each IMCU has its own local encoding dictionary, such as between separate local encoding dictionaries of two IMCUs and / or a canonical global encoding dictionary.

[0139] A dense group 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 completely or mostly continuous within a range of values. In an embodiment not shown, the DGK operator is horizontally scaled so that two computation threads can, for corresponding raw values, in a thread-safe and asynchronous manner: detect whether a dense key has already been generated for a value; detect which of the two threads should generate a dense key when two corresponding values ​​are identical; and detect which corresponding consecutive dense key each thread should generate next.

[0140] In one embodiment, the DGK operator generates a dense key as a dictionary code for the encoding dictionary being generated. In other words, the DGK operator can be the inverse of the recode operator. In one 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 emp_sal can be statistically averaged using the averaging operator AVG as a physical operator.

[0141] Depending on the embodiment discussed previously herein, the aggregate operator N materializes the dept.dept_name column in the aggregate result 520N or 530, either of which can be the only input to the DGK operator. Whenever the DGK operator encounters a unique value in the dept.dept_id column of the input aggregate 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, converting the input dept_id value into a dense grouping key value output by the DGK operator.

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

[0143] 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 aggregate operator S, which can be the aggregate result 520S or 530 according to the embodiments explained earlier in this document.

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

[0145] 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 aggregate operator S together. The AVG operator expects the two rows it processes together to belong to the same department, that is, either Dept1 or Dept2, even if the dept.dept_id column from the DGK operator is missing. As long as the AVG operator receives the rows from both inputs in the same order, that is, the rows appear in the join result 510 as emitted by the probe operator, the AVG operator can rely on the implicit dependency of the rows from the two inputs, which is important, as described below.

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

[0147] 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 specific grouping interval, the counter is incremented by one and the emp.emp_sal value is added to the accumulator for summation, which is important, as shown below.

[0148] As explained elsewhere in this document, various types of operators can behave as streaming, blocking, or batching (which is a hybrid of streaming and blocking). Aggregate operators can have any of these behaviors. For example, an aggregate operator can emit a separate output row for each individually processed input row, which is streaming.

[0149] The semantics of the AVG operator preclude both streaming and batch processing. Specifically, the AVG operator is necessarily blocking, meaning that it cannot emit any output rows until all input rows have been received and processed. After processing all input rows, for each grouped interval, the AVG operator arithmetically divides the accumulator value by the counter value to compute the corresponding arithmetic mean for each grouped interval.

[0150] Thus, in this example, the AVG operator calculates the corresponding average salary for each department. As a blocking operator, and only after all averages have been calculated, the AVG operator emits the final result 540 as output to be accepted as input by the return (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.

[0151] The DAG 500 was discussed above in a macro sense, considering data flow paths that diverge and converge via fan-out and fan-in to implement a query plan. As discussed above, a data flow path consists of a collection of integrated physical operators. The following is a microscopic view of how some important physical operators actually process and deliver data. The following discussion contrasts different approaches to data materialization that can affect the efficiency of the DAG 500 without changing the query semantics, and that can affect the relative placement of some physical operators within the DAG 500. In other words, optimization of the DAG 500 can be based on the following available design alternatives, such as Figure 6-Figure 9 As demonstrated in .

[0152] 5.6 Eager Materialization via Direct Aggregation

[0153] Direct aggregates require eager materialization of data needed by other physical operators (one or more) located downstream of the aggregate physical operator. Therefore, direct aggregates can also be referred to as eager aggregates. As discussed below, eager materialization occurs within the direct aggregate operator, so that the output of the direct aggregate operator contains materialized data that may need to be copied during propagation to downstream physical operators. This copying can be inexpensive when the physical operator that requires the copied data is immediately downstream of the direct aggregate operator.

[0154] However, this duplication of eagerly materialized data can be expensive when the materialized data is repeatedly copied to flow through intermediate physical operators in the same data flow path between the direct aggregation operator and the further downstream physical operator that needs to materialize the data. While deferred materialization via indirect aggregation can improve efficiency, the mechanism of indirect aggregation can be more complex, as described later in this article. Therefore, the materialization mechanism is first discussed below based on eager materialization via direct aggregation, as shown below.

[0155] Although, as discussed later in this paper, Figure 5 Aggregation is used after joining, but the following various other scenarios are more straightforward in various respects. Projection requires the most straightforward aggregation, as both projection and aggregation require little or no materialization of multiple scalar values ​​or multiple tuple values. Aggregation for projection has three scenarios of varying complexity. The three projection scenarios are explained for direct aggregation, which requires (one or more) materialized inputs as follows. As explained later in this article, indirect aggregation accepts (one or more) non-materialized inputs.

[0156] Direct aggregations produce or consume only two types of data: individual columns and row-major data, either of which can be input or output to a direct aggregation, as shown below. If the aggregation projects only one column, then 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 sole purpose of the aggregation, such as for projections, as shown below.

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

[0158] For example, the third row in the aggregate input is correlated with the third scalar value or row in the aggregate output. For demonstration purposes, offsets for the aggregate input and aggregate output are discussed. For example, streaming aggregates may lack offsets as discussed later herein. Even without streaming, offsets may or may not be correlated, such as with variable-width values ​​as explained later herein.

[0159] Aggregate operators can have multiple inputs, each of which can be a column of row-major data or a scalar value. If a direct aggregate has only one input, it should not be a scalar column, because the output will be the same as the input, and there may be the additional inefficiency of copying the scalar from the input to the output, as discussed later in this article.

[0160] In the absence of any configuration settings, in the declared ordering of the inputs, scalars or rows from the same offset in each input are concatenated to produce rows at the same offset in the output. Thus, the aggregate output is generated by replicating values ​​such as scalars and / or tuples.

[0161] As discussed later in this article, replication is expensive and should be avoided or deferred whenever possible. Therefore, for efficiency, aggregate operations should be deferred as far as possible in the data flow, which means pushing aggregate operators as far downstream as possible in the DAG of physical operators. Deferred aggregation is discussed later in this article.

[0162] Aggregation operators may have configuration settings that indicate which column(s) to project and / or in what order to concatenate the columns. This configuration setting should be set if a direct aggregation takes row-major data as the only input, which prevents the output from being identical to the input.

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

[0164] 5.7 Parallel Aggregation

[0165] In various embodiments, such as for projections, 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 of the output row set for each offset in the range and one offset per iteration. Any input to a direct aggregation can be composite, such as a materialized input row set. For example, the aggregation can perform modification so that each row of the input row set becomes modified with a corresponding value from another input to the aggregation. Thus, the output of the aggregation can be wider than any of the inputs to the aggregation.

[0166] Inelastic horizontal scaling may require single instruction multiple data (SIMD) in stride sequences. Each stride concurrently processes batches of contiguous offset subsets of a fixed size within a range of offsets, such as a stride that concurrently combines eight values ​​from one scalar input vector of multiple values ​​with eight corresponding values ​​from another scalar input vector to concurrently generate eight rows in an output row set.

[0167] While both scale horizontally, such as for aggregates, SIMD strides are distinct from horizontal partitioning as follows. Horizontal partitioning can be inelastic, such as for a Beowulf cluster or multi-core symmetric multiprocessing (SMP), or elastic, such as for a cloud of computers. A partition operator is a physical operator that divides a set of rows or columns into subsets of equal storage for corresponding processing by corresponding instances of (one or more) downstream operators concurrently, such as on separate cores. Horizontal partitioning, especially elastic, can require heterogeneous hardware, which, as discussed earlier in this article, encourages opportunistic horizontal scaling offloads.

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

[0169] In an embodiment, some physical operators that are not merge operators can implicitly merge outputs. For example, the Build operator shown is a multi-instance of the horizontal slice HS, but the Probe operator is not a multi-instance because the Build operator implicitly merges parallel data streams. Regardless of whether they are merged implicitly or explicitly by the Merge operator, all operators that appear in the data flow path between the upstream Partition operator and the downstream Merge are implicitly multi-instanced.

[0170] 5.8 Filtering

[0171] As discussed later in this article, indirect aggregation requires non-materialized input, especially when certain types of upstream physical operators, such as filter or join operators, emit non-materialized output as their only output. A filter operator has one input and one output. The filter operator's only input is as follows. The input is materialized. The input can be a scalar column or a rowset. The input rowset can be columnar or row-major.

[0172] The filter operator's unique output is as follows. Depending on the embodiment, the filter output is either materialized or non-materialized. If the input is a column, then the output is a column. If the input is a row set, then the output is a row set. If the input row set is row-major, then the output row set is row-major. If the input row set is columnar, then the output row set is columnar.

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

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

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

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

[0177] If the filter is based on replication, then the output is materialized as follows. Otherwise, as discussed later in this article, the output is unmaterialized. The replication filter operates as follows.

[0178] Unlike aggregate operators, and regardless of replication, a filter operator's unique output can have the same or fewer offsets than its unique input. For example, some input values ​​(whether rows or scalars) may not satisfy the predicate, in which case those values ​​are excluded from the output. Values ​​that satisfy the predicate are included in the output. Replication filtering copies the satisfying values ​​from the input to the materialized output.

[0179] 5.9 Other physical operators

[0180] Various embodiments have various categories of operators, such as filter 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 may have the following singular example physical operator types, some of which are in Figure 5 and discussed elsewhere herein.

[0181] Decompression – decompresses compressed data

[0182] Decode - decode the encoded data

[0183] Filter - applies a single table predicate

[0184] Projection - Projects items based on the filter results

[0185] Transpose - convert between columnar and row-based data

[0186] Aggregate - Randomly aggregate data from columnar / row-based data using arrays of indices / pointers, including direct and indirect aggregation

[0187] Hash - hashes data, working on single columns or composite (multiple columns)

[0188] Partitioning - Partition the input data into multiple partitions based on the partition key

[0189] Build - insert data into the hash table

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

[0191] Dense Keys - For any given sparse input data (such as text), densify it so that the value range is an unsigned integer [0...n]

[0192] Grouped Aggregation - Given an array of dense keys for aggregation and input data (e.g., columns), compute the aggregate

[0193] Classification – categorize the input data

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

[0195] Merge-Join - Given categorical data in two joined tables, use the merge method to produce a joined result.

[0196] Search - Search for a given keyword string

[0197] Therefore, a rich mix of processing activities can be included in the hardware-neutral DAG of physical operators to represent any specific query. As a result, when compiling queries into DAGs of physical operators, the Turing completeness of SQL and scripted SQL (such as the Procedural Language for SQL (PL / SQL)) is preserved.

[0198] 6.0 Example Join Plan

[0199] as follows, Figure 6-Figure 7 Example activities for planning and optimizing queries are shown. Figure 6 Example configuration activities for constructing and using a hash table are shown to demonstrate both the integration pattern for coupling operators generally, and the arrangement of specific operators for hash table processing. Figure 7 A higher-level view of planning and optimization. In other words, Figure 6 A microscopic view with several operators is shown below, while Figure 7 Based on configuring a macro view of the entire DAG.

[0200] Figure 6 It is a depiction Figure 1 A flowchart of an example process that the DBMS 100 may use to plan and optimize data flows involving hash tables, such as for Figure 5 Hash joins or other inter-partition or grouping joins as shown in . Figure 5 discuss Figure 6 .as follows, Figure 6 Six operators are included (not shown), which may all be physical operators or may all be hardware operators unless otherwise stated.

[0201] As discussed earlier in this article, Figure 5 A hash join based on the shown hash table HT is shown, which is populated 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 and probe operators may occur as follows.

[0202] Step 602 dynamically selects a build operator from a variety of interchangeable build operators. Step 604 dynamically selects a probe operator from a variety of interchangeable probe operators. Thus, hash table processing can be dynamically configured, for example, based on fluctuation conditions.

[0203] For example, step 602 and / or step 604 may select different operators for separate executions of the same query submitted repeatedly, because the same query may not always have the same optimal plan even on the same DBMS 100. This flexibility and variation goes beyond known relational operator plans because Figure 6 Involves physical or hardware operators that are finer-grained than the known relational operators.

[0204] Based on the selected probe operator and / or build operator, step 606 dynamically selects a hardware operator for another physical operator from a variety of interchangeable hardware operators. Figure 5 , which hardware operator or physical operator is selected for the partition operator and / or DGK operator shown can depend on which hardware operator or physical operator is selected for the build operator and / or probe operator shown. For example, details selected for the build operator and / or probe operator (such as data formatting and quoting discussed elsewhere herein) can limit or benefit the partition 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 one (or some) surrounding physical operators or hardware operators can also be specialized and / or optimized based on excluding nulls.

[0205] like Figure 5 As shown in FIG, the build operator and the probe operator are connected. The build operator is also connected to the partition operator, and the probe operator is also connected to the hash operator and the aggregation operators N and S. That is, four operators, along with the build operator and the probe operator, are interconnected as part of DAG 500. These four operators may alternatively or additionally include other operators in other examples, such as the following other example operators.

[0206] Join key decoder operator,

[0207] · Unpack operator,

[0208] Dictionary encoder operator,

[0209] A dense grouping key operator, which produces a sequence of distinct unsigned integers that includes gaps,

[0210] Statistical average operator,

[0211] filter operators that apply simple predicates,

[0212] Classification operators,

[0213] A merge operator that preserves the order of multiple categorical inputs,

[0214] Text search operators,

[0215] · A horizontal row partitioning operator using the join key,

[0216] Use the hash table's bucket-building key insertion operator, or

[0217] • Use the insertion overflow operator of the hash table's buckets.

[0218] The build operator or the probe operator and one of the other four operators are connected via step 608 according to an operator integration mode, such as data batching and / or buffering, execution pipelining, and / or synchronous (also known as blocking) or asynchronous coupling. For example, in one embodiment, Figure 5 The following physical operators shown in can involve the following example operator integration patterns and performance issues.

[0219] Recode and hash: These are fusible operators and computationally bounded

[0220] • Partition: This is a cache type operator and will consume space of the horizontal slice HS shown and is bandwidth limited.

[0221] • Construct, which is a blocking operator and has bounded bandwidth, and generates the hash table HT shown.

[0222] Probe, Aggregate: These are pipeline operators and have bounded bandwidth.

[0223] DGK: This is a blocking operator and has bounded bandwidth.

[0224] AVG: This is a pipeline operator and the computation is bounded.

[0225] Thus, DAG 500 is configured to load balance across heterogeneous hardware, such as by offloading and across the DAG of physical operators that can be arranged in parallel dataflow paths and pipeline stages, with a mix of slightly mismatched bandwidths, without creating performance bottlenecks within DAG 500 and without degrading the execution throughput of DAG 500.

[0226] 7.0 Example Planning Optimization Process

[0227] Presented later in this paper are techniques and graphs for indirect aggregation that may be more or less fundamental to minimizing data in motion by deferring aggregation. The following is generally applicable to directed acyclic dataflow graphs (e.g., Figure 5 Query planning and execution may require a DAG of physical operators and / or a DAG of hardware operators, as shown below.

[0228] Figure 7 It is a depiction Figure 1 Flowchart of an example DAG optimization process that DBMS 100 may use to plan, optimize, and execute physical operator DAG 500 and / or hardware operator DAG, such as for executing data access request 110. Figure 1 discuss Figure 7 .

[0229] For demonstration purposes, query planning can be considered a linear process that sequentially generates the following artifacts in the following order, whereby the next artifact is generated based on the previous artifact: a) a parse tree 110 for the data access request, which may be an initial query plan consisting of relational operators such as 131-132 and hardware-independent, b) an implementation query plan that is hardware-independent and based on a particular implementation for dynamic selection of relational operators (such as 131A and 132B), and includes physical operators (such as 161-162) that are hardware-independent and arranged in a DAG of physical operators, and c) a corresponding optimized hardware operator DAG that represents the DAG of physical operators executed on heterogeneous hardware.

[0230] In practice, embodiments of DBMS 100 may have various approaches that may deviate from this rigid linear progression of planning and execution artifacts in various ways. For example, instead of a waterfall approach, planning and optimization may be an iterative approach where previously generated artifacts may be modified or replaced based on subsequently generated artifacts. For example, a DAG of hardware operators may be generated based on a 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.

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

[0232] Thus, there can be feedback between the planning and optimization phases that results in the revision or replacement of previously generated artifact(s), such as according to a feedback loop where the estimated costs are revised with increasing accuracy. For example, an optimization iteration may reveal that embodiment 131B actually and unexpectedly costs less than previously selected embodiment 131A.

[0233] 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 that no GPUs are present or optimistically assume that all GPUs are idle. Such initial assumptions, while potentially inaccurate, can speed up the initial plan and may actually be statistically accurate, reliable, and robust in many or most cases.

[0234] Step 704 initially and dynamically selects implementations 131A and 132B for the corresponding relational operators 131-132. When selected individually and isolated from each other, implementations 131A and 132B may independently appear to be optimal. However, there may be inter-operator issues that span the selected implementations 131A and 132B, and better optimization may be achieved if implementations 131B and / or 132A are selected instead. For example, even though implementation 131A may initially cost less than implementation 131B, DBMS 100 may detect during iterative planning and optimization that the physical operators 164 of implementation 131B of relational operator 131 have more synergy, such as consuming fewer resources to interoperate with the planning artifacts of relational operator 132, than the physical operators 162 of the initially selected implementation 131A.

[0235] Thus, step 706 can iteratively generate an optimized execution plan by modifying or replacing specific initially selected or unselected implementations based on relational operators, such as based on initially selected or unselected hardware operators, such as before or after physical operators or hardware operators are fused or reordered. Based on a convergence criterion, the iterations of planning and optimization eventually converge to a final DAG of physical operators and a final DAG of hardware operators, which can be advantageous for hasty optimization of cheap queries with fewer iterations, such as based on row counts and / or value distributions (such as cardinality), and can be advantageous for long-term optimization of expensive queries (such as with large data warehouses) with many iterations.

[0236] As discussed above, steps 702, 704, and 706 perform query plan formulation, such as topology formulation and reformulation of the DAG, such as through fusion, reordering, and / or replacement of operators. However, even after iterations converge on 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 or hardware operator may require a buffer of configurable capacity, and the optimal capacity may depend on dynamic conditions, such as the interconnectivity of the operators, fusion of operators, separation of subsets of operators into pipeline stages, details of the participating hardware, and / or details of the payload data (such as capacity and data type).

[0237] The DBMS 100 may include a machine learning (ML) model that predicts optimal configuration settings, 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, in step 708. The ML model's predictions are based on inputs called features, which may include details of the DAG, its operators and their interconnectivity, participating hardware, fluctuating resource availability, and / or statistics of payload data.

[0238] Depending on the nature of the particular configuration settings (such as numerical or categorical), various ML models may be more or less suitable for providing predictions. As discussed later herein, example classes of ML models include decision trees, random forests, artificial neural networks (ANNs) such as multilayer perceptrons (MLPs), linear or logistic regressors, and support vector machines (SVMs). Reinforcement learning with historical operational information of (one or more) DBMSs such as DBMS 100 during offline training can prepare the ML models to make highly accurate predictions of optimality in a live production setting, such as discussed later herein. Step 708 adds little or no latency and can significantly speed up subsequent execution of the DAG. After step 708, the DAG of the hardware operator is both optimal and ready for direct execution.

[0239] 8.0 Deferred Materialization via Indirect Aggregation

[0240] As discussed earlier in this article, eager materialization through direct aggregation may require over-copying of the materialized data to physical operators that may be further downstream than the direct aggregation operator, which can be inefficient. Deferred materialization through indirect aggregation can improve efficiency in innovative ways, as discussed below. Indirect aggregation can also be called deferred aggregation because aggregation can be deferred by placing the indirect aggregation operator as far downstream as possible (such as adjacent to a far downstream physical operator of the data that needs to be materialized). The reduced payload copying through this topology optimization will be discussed later in this article.

[0241] Figure 5 The probe operator performs a matchmaking between two tables but does not actually concatenate the matching rows to materialize the join result. Instead, the probe operator emits only a non-materialized join result, which contains pairs of references to the matching rows of both tables, such as with row identifiers, memory address pointers, or internal buffer offsets. The probe operator does not emit any table columns, not even primary key columns.

[0242] 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 materialized rowset, both columns can be aggregated by a single aggregation operator, even if that aggregation operator is downstream of the join, such as downstream of a probe operator. However, when two columns come from separate relational tables, such as dept_name and emp_sal columns, each from Figure 3 If we have dept and emp tables, then aggregating these two columns requires two aggregation operators N and S, such as Figure 5 In an embodiment, both aggregation operators N and S are executed in parallel, such as discussed earlier herein.

[0243] A key innovation of the techniques herein is minimizing the actual flow of data (so-called data in motion or in flight), including minimizing the occurrence and extent of data copying. Consequently, direct aggregation may be discouraged due to excessive copying, especially when any input is wide (such as having multiple fields) or has a single wide field (such as text). Indirection herein can minimize copying by passing references (such as row identifiers) rather than actual values ​​(such as multi-field tuples) in the data flow of the physical operator's DAG. Similarly, direct aggregation can be used only when all values ​​to be copied to the aggregate output are directly provided in one of the direct aggregation's inputs.

[0244] For example, direct aggregation cannot be used with non-materialized inputs (such as join results as explained above). This limitation may require copying values ​​into the input vectors of the direct aggregation, which is generally not optimal and is often a reason not to use direct aggregation. Therefore, the following kinds of indirect aggregation are often more efficient in time and / or space than direct aggregation. Another benefit of indirect aggregation is that indirect aggregation complements joins as discussed above because they expect non-materialized input(s) such as those produced by joins.

[0245] Figure 8 The following diagram depicts three types of indirect aggregates that are specifically used in the following scenarios: Non-materialized data, such as the output of a filter operator or a relational join discussed in the signature of this document, can be provided as input to a downstream indirect aggregate and can indicate the subset of rows in the row set that have been evaluated to satisfy the filter.

[0246] As shown in the figure, the three indirect aggregates use double indirection, so that dereferencing one reference yields another reference, which must then be dereferenced to reach the actual row in the row set. Therefore, two buffers are used (such as two separate inputs to the same indirect aggregate operator) so that the first buffer contains a reference to the second buffer, as shown below and explained. This double indirection can preserve already applied filtering, as shown below.

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

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

[0249] Because the first buffer in the pointer indirection example contains pointers rather than offsets, these pointers can point anywhere in the address space for fully random access, eliminating the need for contiguous storage of the rows of a row set. Therefore, the pointer indirection example does not require a second buffer to provide the rows of a row set. While having a second buffer does not interfere with pointer indirection, pointers are wider than offsets, so pointer indirection is typically used only when there is no second buffer.

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

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

[0252] In all three example indirect aggregates, the first array contains references to the same corresponding fixed width. The array-indirected second array contains fixed-width data, so the offset shown serves as an array index into the second array. The pointers shown in the pointer-indirection can point to fixed-width or variable-width data, which is self-describing by a null terminator or a length counter.

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

[0254] Figure 8 is a block diagram depicting three types of indirect aggregates specifically designed for the following scenarios. Non-materialized data, such as the output of a filter operator or a relational join discussed earlier in this article, can be provided as input to a downstream indirect aggregate and can indicate the subset of rows in the row set that have been evaluated to satisfy the filter.

[0255] 9.0 Segment Aggregation

[0256] Figure 9 is a block diagram depicting two types of segmented aggregation. Figure 8 discuss Figure 9 As shown in the figure, Figure 9 The conventional segmented aggregation is as described above for Figure 8 As discussed, where the data segment is the second input buffer, and a corresponding reference to the second input buffer is stored in the first input buffer, such that a conventional segmented aggregate operator accepts the first and second buffers as separate inputs.

[0257] Figure 9 Also shown is the aggregation of a CLA segment with only one input, which is self-delimiting, even though its variable width data is not self-delimiting. That is, as shown, the first and second buffers are concatenated into a single self-delimiting input buffer, as shown.

[0258] 10.0 Database Overview

[0259] Embodiments of the present invention are used in the context of a database management system (DBMS).Therefore, a description of an example DBMS is provided.

[0260] Generally speaking, a server, such as a database server, is a combination of integrated software components and an allocation of computing resources, such as memory, nodes, and processes on the nodes used to execute the integrated software components, where the combination of software and computing resources is dedicated to providing a specific type of functionality on behalf of the server's clients. A database server controls and facilitates access to a specific database, processing client requests to access the database.

[0261] 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 the data stored in the database. A user can be one or more applications running on a client computer that interacts with the database server. Multiple users may also be collectively referred to as users in this article.

[0262] A database consists of data and a database dictionary, which is stored on a persistent storage mechanism such as a hard disk. A database is defined by its own separate database dictionary. The database dictionary includes metadata that defines the database objects contained within the database. In practice, the database dictionary defines many databases. Database objects include tables, table columns, and tablespaces. A tablespace is a collection of one or more files used to store data for various types of database objects, such as tables. If the data for a database object is stored in a tablespace, the database dictionary maps the database object to the tablespace or tables that hold the data for the database object.

[0263] 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.

[0264] Database commands can be in the form of database statements. In order for a database server to process a database statement, the database statement must conform to a 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 a database server to create or configure database objects, such as tables, views, or complex types. Data manipulation language ("DML") instructions are issued to a DBMS to manage data stored in a database structure. For example, SELECT, INSERT, UPDATE, and DELETE are examples of DML instructions common in some SQL implementations. SQL / XML is a common extension of SQL used when manipulating XML data in an object-relational database.

[0265] 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, to varying degrees, share access to shared storage, such as 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 group of computers (such as workstations and / or personal computers) interconnected via a network. Alternatively, the nodes can be nodes of a grid consisting of nodes in the form of server blades interconnected with other server blades on a rack.

[0266] Each node in a multi-node database system hosts a database server. A server, such as a database server, is a combination of integrated software components and an allocation of computing resources, such as memory, nodes, and processes on the nodes that execute the integrated software components on processors, dedicated to performing specific functions on behalf of one or more clients.

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

[0268] 10.1 Query Processing

[0269] 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 may specify source data object(s), such as table(s), column(s), view(s), or snapshot(s), from which result set(s) are to be determined. For example, source data object(s) may 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 of representation of a query, including queries in the form of database statements and any data structures used for internal query representation. The term "table" refers to any source object that is referenced or defined by a query and that represents a collection of rows, such as a database table, a view, or an inline query block (such as an inline view or subquery).

[0270] Queries can perform operations on data from source data objects row by row as the object(s) are loaded, or on the entire source data object(s) after the object(s) have been loaded. The result sets generated by some operations can make available to other operations(s), and in this way, the result sets can be filtered out or narrowed based on certain criteria, and / or joined or combined with other result set(s) and / or other source data objects(s).

[0271] A subquery is a portion or component of a query that is distinct from the other portion(s) or component(s) of the query and can be evaluated separately from the other portion(s) or component(s) of the query (i.e., as a separate query). The other portion(s) or component(s) 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 separately, one or more times, while computing a result for the outer query.

[0272] Generally speaking, 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 various components and structures of the query statement.

[0273] The internal query representation may be in the form of a node graph, with each interconnected data structure corresponding to a node and a component of the represented query statement. The internal representation is typically generated in memory for evaluation, manipulation, and transformation.

[0274] Hardware Overview

[0275] According to one embodiment, the technology described herein is implemented by one or more special-purpose computing devices. Special-purpose computing devices can be hard-wired to perform these technologies, or can include digital electronic devices (such as one or more application-specific integrated circuits (ASICs) or field programmable gate arrays (FPGAs) that are permanently programmed to perform these technologies), or can include one or more general-purpose hardware processors that are programmed to perform these technologies according to program instructions in firmware, memory, other storage devices, or combinations. Such special-purpose computing devices can also combine customized hard-wired logic, ASICs, or FPGAs with custom programming to implement these technologies. Special-purpose computing devices can be desktop computer systems, portable computer systems, handheld devices, networking devices, or any other devices that combine hard-wiring and / or program logic to implement these technologies.

[0276] For example, Figure 101 is a block diagram illustrating a computer system 1000 on which embodiments of the present invention may be implemented. Computer system 1000 includes a bus 1002 or other communication mechanism for communicating information, and a hardware processor 1004 coupled with bus 1002 for processing information. Hardware processor 1004 may be, for example, a general-purpose microprocessor.

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

[0278] 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.

[0279] The computer system 1000 may be coupled to a display 1012, such as a cathode ray tube (CRT), via the bus 1002 for displaying information to a computer user. An input device 1014, including alphanumeric and other keys, is coupled to the bus 1002 for communicating information and command selections to the processor 1004. Another type of user input device is a cursor control 1016, such as a mouse, trackball, or cursor direction keys, for communicating direction information and command selections to the processor 1004 and for controlling cursor movement on the 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 allow the device to specify a position in a plane.

[0280] Computer system 1000 can implement the techniques described herein using custom hardwired logic, one or more ASICs or FPGAs, firmware, and / or program logic (which, in combination with the computer system, enables or programs the computer system 1000 to function as a special-purpose machine). According to one embodiment, computer system 1000 performs the described techniques in response to processor 1004 executing one or more sequences of one or more instructions contained in main memory 1006. These instructions may be read into main memory 1006 from another storage medium, such as storage device 1010. Execution of the sequences of instructions contained in main memory 1006 causes processor 1004 to perform the process steps described herein. In alternative embodiments, hardwired circuitry may be used in place of or in combination with software instructions.

[0281] As used herein, the term "storage medium" refers to any non-transient medium that stores data and / or instructions that cause a machine to operate in a particular manner. Such storage media may include non-volatile media and / or volatile media. Non-volatile media include, for example, optical disks, magnetic disks, or solid-state drives, such as storage device 1010. Volatile media include dynamic memory, such as main memory 1006. Common forms of storage media include, for example, floppy disks, flexible disks, hard disks, solid-state drives, magnetic tape or any other magnetic data storage medium, CD-ROMs, any other optical data storage medium, any physical medium with a pattern of holes, RAM, PROM and EPROM, FLASH-EPROM, NVRAM, any other memory chip, or a cassette tape.

[0282] Storage media are distinct from, but can be used in conjunction with, transmission media. Transmission media participate in the transfer of information between storage media. For example, transmission media include 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.

[0283] Various forms of media may be involved in carrying one or more sequences of one or more instructions to processor 1004 for execution. For example, the instructions may 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 over a telephone line using a modem. A modem local to computer system 1000 can receive the data on the telephone line and use an infrared transmitter to convert the data to an infrared signal. An infrared detector can receive the data carried in the infrared signal and appropriate circuitry can place the data on bus 1002. Bus 1002 carries the data to main memory 1006, from which processor 1004 retrieves and executes the instructions. The instructions received by main memory 1006 may optionally be stored on storage device 1010 before or after execution by processor 1004.

[0284] Computer system 1000 also includes a communication interface 1018 coupled to bus 1002. Communication interface 1018 provides bidirectional data communication coupled to network link 1020, wherein network link 1020 is connected to local network 1022. For example, 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 with a corresponding type of telephone line. As another example, communication interface 1018 can be a local area network (LAN) card to provide a data communication connection with a compatible LAN. A wireless link can also be implemented. In any such implementation, communication interface 1018 sends and receives electrical signals, electromagnetic signals, or optical signals that carry digital data streams representing various types of information.

[0285] The network link 1020 typically provides data communication to other data devices through one or more networks. For example, the network link 1020 can provide a connection through the local network 1022 to a host computer 1024 or to data equipment 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. The signals through the various networks and the signals on the network link 1020 and through the communication interface 1018, which carry the digital data to and from the computer system 1000, are example forms of transmission media.

[0286] Computer system 1000 can send messages and receive data, including program code, through the network(s), network link 1020, and communication interface 1018. In the Internet example, server 1030 can send the requested code for an application program through Internet 1028, ISP 1026, local network 1022, and communication interface 1018.

[0287] The received code may be executed by processor 1004 as it is received, and / or stored in storage device 1010 or other non-volatile storage for later execution.

[0288] Software Overview

[0289] Figure 11 1 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 functionality, are merely exemplary and are not meant to limit implementation of the example embodiment(s). Other software systems suitable for implementing the example embodiment(s) may have different components, including components with different connections, relationships, and functionality.

[0290] Software system 1100 is used to direct the operation of computing system 1000. Software system 1100 includes a kernel or operating system (OS) 1110, which may be stored in system memory (RAM) 1006 and fixed storage (eg, hard disk or flash memory) 1010.

[0291] The OS 1110 manages 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 1010 into memory 1006) for execution by the system 1100. Applications or other software intended for use on the computer system 1000 can also be stored as a downloadable set of computer-executable instructions, for example, for download and installation from an Internet location (e.g., a web server, app store, or other online service).

[0292] Software system 1100 includes a graphical user interface (GUI) 1115 for receiving user commands and data in a graphical manner (e.g., "point and click" or "touch gestures"). These inputs can then be acted upon by system 1100 based on instructions from operating system 1110 and / or application(s) 1102. GUI 1115 is also used to display the results of operations from OS 1110 and application(s) 1102, and the user can provide additional input or terminate the session (e.g., log out).

[0293] OS 1110 may execute directly on the bare hardware 1120 (e.g., processor(s) 1004) of computer system 1000. Alternatively, a hypervisor or virtual machine monitor (VMM) 1130 may be interposed between the bare hardware 1120 and OS 1110. In this configuration, VMM 1130 acts as a software "buffer" or virtualization layer between OS 1110 and the bare hardware 1120 of computer system 1000.

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

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

[0296] In other cases, the guest operating system may be specifically designed or configured to execute on the VMM 1130 to improve efficiency. In these instances, the guest operating system is "aware" that it is executing on a virtual machine monitor. In other words, the VMM 1130 may provide paravirtualization to the guest operating system in certain circumstances.

[0297] A computer system process includes an allocation of hardware processor time, as well as an allocation of memory (physical and / or virtual), an 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 hardware processor state (e.g., the contents of registers) between allocations of hardware processor time when the computer system process is not running. A computer system process runs under the control of an operating system and may run under the control of other programs executing on the computer system.

[0298] cloud computing

[0299] The term "cloud computing" is used generally herein 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 resources to be rapidly provisioned and released with minimal management effort or service provider interaction.

[0300] Cloud computing environments (sometimes referred to as cloud environments or just clouds) 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 intended for use by or within a single organization. A community cloud is intended to be shared by several organizations within a community, while a hybrid cloud comprises two or more types of clouds (e.g., private, community, or public) bound together by data and application portability.

[0301] Generally speaking, the cloud computing model enables some of those responsibilities that might previously have been provided by an organization's own information technology department to instead be delivered as a service layer within the cloud environment for consumption by consumers (either internally or externally, depending on the public / private nature of the cloud). The precise definition of the components or features provided by or within each cloud service layer can vary depending on the specific implementation, but common examples include: Software as a Service (SaaS), in which consumers use software applications running on cloud infrastructure, while the SaaS provider manages or controls the underlying cloud infrastructure and applications; Platform as a Service (PaaS), in which consumers 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 below the runtime execution environment); Infrastructure as a Service (IaaS), in which consumers can deploy and run arbitrary 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), in which 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.

[0302] The above basic computer hardware and software and cloud computing environment are presented to illustrate the basic underlying computer components that can be used to implement the example embodiment(s). However, the example embodiment(s) are not necessarily limited to any particular computing environment or computing device configuration. Instead, according to the present disclosure, the example embodiment(s) can be implemented in any type of system architecture or processing environment that a person skilled in the art will understand as being capable of supporting the features and functions of the example embodiment(s) presented herein.

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

Claims

1. A method comprising: Generates a query plan for a database statement that references multiple columns in a set of relation tuples, where The query plan contains a hardware-independent rotation operator; executing the query plan, wherein executing the query plan includes pivoting the plurality of columns via a graphics processing unit (GPU); and generating and sending a response to the database statement based on the rotation of the plurality of columns; The method is executed by one or more computers. 2 . The method of claim 1 , wherein the hardware-independent rotation operator is undefined in at least one selected from the group consisting of: relational algebra and a query language. 3 . The method of claim 1 , wherein at least one selected from the group consisting of: the database statement and the query plan does not use a database cursor. The method of claim 1 , wherein the database statement does not contain a subquery. The method of claim 1 , wherein generating a query plan does not include dynamically generating a query language query. The method of claim 1 , wherein the rotating does not utilize a pivoting column.

7. The method of claim 1, wherein the hardware-independent rotation operator is configurable for at least one selected from the group consisting of: a direction of rotation, an amount of rotation, and a count of quarter turns.

8. A method comprising: Generates a query plan for a database statement that references multiple columns in a set of relation tuples, where: The database statement does not specify transposition, The matrix contains the set of relation tuples, and The query plan includes a hardware-independent transpose operator; executing the query plan, wherein executing the query plan includes transposing the plurality of columns without changing values ​​on a matrix diagonal by a graphics processing unit (GPU); and generating and sending a response to the database statement based on the transposition; The method is executed by one or more computers.

9. The method of claim 1 , further comprising: It is detected that the output format of the upstream operator needs to be converted to a format selected from the group consisting of: the input format or the output file format of the downstream operator; as well as In response to the detecting, the hardware-independent rotation operator is selected.

10. The method of claim 1, further comprising passing the rotated output to a downstream operator that performs vertical slicing or horizontal slicing.

11. The method of claim 8, wherein the transposing comprises accessing the plurality of columns in a form selected from the group consisting of: column-major form and row-major form.

12. One or more non-transitory computer-readable storage media storing instructions that, when executed by one or more processors, cause the steps of the method of any one of claims 1-11 to be performed.

Images