Generating native compiled query plan by recompiling existing C-code by partial evaluation

Abstract

In an embodiment, a database management system (DBMS) hosted by a computer receives a request to execute database statements and responsively generates an interpretable execution plan representing the database statements. The DBMS decides that execution of the database statements would need or would not need to interpret the interpretable execution plan, and if not, the interpretable execution plan is compiled into target code based on the partial evaluation. In this case, the database statements are executed by executing the compiled target code of the plan, which provides acceleration. In embodiments, partial evaluation and Turing Complete Template Metaprogramming (TMP) are based on the use of an interpretable execution plan as a compilation-time constant that is an actual parameter for a parameter of an evaluation template.

Description

Technical Field

[0001] The present invention relates to the execution optimization of database queries. This document is about techniques for using partial evaluation and evaluation templates for compiling the optimal execution of an interpretable query plan. Background Art

[0002] A database management system (DBMS) typically executes a query by performing query interpretation. Query interpretation evaluates a query to generate a query execution plan for executing the query. The query execution plan is represented as an evaluation tree in shared memory. Each node in the tree represents a row source that returns a single row or a stream of rows. A parent row source reads the results of its child row sources and computes its own result, which the parent row source returns to its parent. Each row source is implemented as a previously compiled query plan operator that reads a node descriptor and performs the indicated computation on itself and calls the function of its child via a function pointer.

[0003] A DBMS can perform native compilation of a query. Native compilation of a query means that the DBMS compiles a database statement to generate machine instructions that are directly executed on a central processing unit (CPU) to perform at least a part of the operations required to execute the database statement. To execute a database statement, the DBMS can use a combination of native compilation and query interpretation.

[0004] Existing query native compilers cannot use query interpreter components such as database operators. For example, VoltDB lacks a query interpreter. Also, VoltDB cannot access the fluctuating statistics and runtime conditions of a database and thus cannot perform various optimizations when compiling a query.

[0005] Even in the case where the most advanced database systems have both a query compiler and a query interpreter, the compiler and the interpreter have different implementations of database operators and other execution plan components. Different implementations in the same database system require double maintenance, which is both expensive and error-prone. At runtime, different implementations may each expect computer resources such as processor time and staging space. Brief Description of the Drawings

[0006] In the drawings:

[0007] Figure 1is a block diagram depicting an example database management system (DBMS) that optimally executes database statements by using partial evaluation and evaluation templates for the compilation of an interpretable execution plan;

[0008] Figure 2 is a flowchart depicting an example computer process that a DBMS can execute to optimally execute database statements by using partial evaluation and evaluation templates for the compilation of an interpretable execution plan;

[0009] Figure 3 is a flowchart depicting an example computer process that a DBMS can execute to apply partial evaluation and / or the first Futamura projection;

[0010] Figure 4 is a block diagram depicting an example partial evaluation;

[0011] Figure 5 is a block diagram depicting an example partial evaluation;

[0012] Figure 6 is a flowchart depicting an example computer process that a DBMS can execute to switch from an initial interpretation to a final compilation of the same repeated database statement;

[0013] Figure 7 is a flowchart depicting an example computer process that a DBMS can execute to optimize before query compilation and defer query compilation unless the combined cost estimate meets a compilation threshold;

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

[0015] Figure 9 is a block diagram illustrating a basic software system that can be employed to control the operation of a computing system. Detailed Description

[0016] 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 may 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.

[0017] General Overview

[0018] As storage performance improves and the use of in-memory databases increases, it becomes increasingly important to reduce the usage of the database central processing unit (CPU). Although just-in-time compilation or vectorization can be used to accelerate online analytical processing (OLAP), for online transaction processing (OLTP) database statements, just-in-time compilation can be the most promising approach for reducing CPU usage. Just-in-time compilation of new queries is expensive, and unlike VoltDB which uses just-in-time compilation for every query, the techniques of this disclosure are adaptive based on the frequency of query execution. However, supporting different just-in-time compilation-based engines and interpretation-based engines introduces maintenance and compatibility issues, which the techniques of this disclosure avoid.

[0019] This disclosure is a novel approach for generating plan-specific compiled code from legacy C operators of a legacy query interpreter. In an embodiment, by treating components of an SQL plan as constant values, a plan compiler uses C++ version 20 template features for partial evaluation. This technique is discussed in this disclosure and is referred to as the first Futamura projection. An SQL plan includes: a) encoded expressions from an SQL statement and b) schema information required for the tables being accessed, such as column metadata, triggers, constraints, auditing, column encryption, label-based security, and database indexes to be used or maintained. All this information is seamlessly constant-folded and optimized by a C++ compiler into the generated just-in-time compiled code, which provides the fastest execution.

[0020] This approach is agnostic to how specialized plans are generated and compiled. One example uses C++ version 20 template features by providing constant query plan data as a non-type template parameter for template instantiation, then wrapping the template around existing interpreter code and adaptively inlining internal function calls of the existing interpreter. Another example instead uses a text macro preprocessor to incorporate the SQL plan and its metadata into a compiled version generated from an interpreted version. Both examples eliminate the dual maintenance discussed in the background art.

[0021] This approach does not require manually identifying parts of a query plan as immutable because the SQL compiler performs this operation. Partial evaluation produces a just-in-time compiled plan by recompiling existing SQL row sources and providing the entire immutable part of the plan as compile-time constants.

[0022] Example embodiments use an entire immutable plan for partial evaluation, which promotes a simpler and more understandable implementation. Using adaptive compilation, frequently executed SQL statements are partially evaluated with the entire immutable plan, while infrequently executed SQL statements are not partially evaluated at all. When using an external compiler, the cost of compilation can be high, but if the compiler is linked into the database codebase itself and the line sources to be recompiled are stored in a more efficient intermediate representation, then the cost of compilation can be reduced.

[0023] In an embodiment, a database management system (DBMS) hosted by a computer receives a request to execute a database statement and responsively generates an interpretable execution plan representing the database statement. The DBMS determines whether the execution of the database statement will require interpretation of the interpretable execution plan and, if not, then compiles the interpretable execution plan into target code based on partial evaluation. In that case, the database statement is executed by executing the target code of the compiled plan, which provides an acceleration. In an embodiment, partial evaluation and Turing-complete template metaprogramming (TMP) are based on using the interpretable execution plan as a compile-time constant that is an argument for evaluating the parameters of a template.

[0024] 1.0 Example Computer

[0025] Figure 1 is a block diagram depicting an example database management system (DBMS) 100 in an embodiment. The DBMS 100 optimally executes a database statement 110 by using partial evaluation and an evaluation template 160 for the compilation of an interpretable execution plan 130. The DBMS 100 can be hosted by at least one computer such as a rack server (such as a blade), a personal computer, a mainframe, a virtual computer, or other computing device.

[0026] The DBMS 100 can contain and operate one or more databases such as a relational database that includes a relational schema, a database dictionary, and relational tables composed of rows and columns that are stored in a file that can be composed of database blocks, each database block containing one or more records, in a row-major or column-major (i.e., column-major) format. Techniques for configuring and operating a database are given later herein.

[0027] 1.1 Database Statement

[0028] The DBMS 100 receives execution requests 121-122 to execute the same or different database statements, such as database statement 110. The execution requests can be received from a client on a remote computer via a communication network, or from a client on the computer hosting the DBMS 100 via inter-process communication (IPC). The execution requests can be received for a database session on a network connection using a protocol such as Open Database Connectivity (ODBC).

[0029] In various examples, any of the requests 121-122 can contain a database statement or an identifier of a database statement that the DBMS 100 already contains. The database statement 110 can contain relational algebra, such as according to a Data Manipulation Language (DML) (such as Structured Query Language (SQL)). For example, the DBMS can be a Relational DBMS (RDBMS) that includes one or more databases that include relational tables. Instead, the DBMS might include one or more other data repositories, such as a column store, a tuple store (such as a Resource Description Framework (RDF) triple store), a NoSQL database, or a Hadoop File System (HDFS) (such as Hadoop and / or Apache Hive).

[0030] The database statement 110 can be an ad hoc query or other DML statement, such as a prepared (i.e., batched) statement and / or a Create, Read, Update, or Delete (CRUD) statement. The DBMS 100 that receives the execution request can cause the DBMS 100 to parse the database statement 110 to generate an interpretable execution plan 130 that represents the database statement 110 in a format that can be repeatedly interpreted to repeatedly execute the database statement 110.

[0031] 1.2 Execution Plan

[0032] The interpretable execution plan 130 includes operation nodes that are arranged as a tree data structure as tree nodes, such as a tree data structure created when a query parser parses the database statement 110. In an embodiment, the interpretable execution plan 130 is also an Abstract Syntax Tree (AST) for a database language such as SQL.

[0033] Each node in the interpretable execution plan 130 can be interpreted as performing a corresponding part of the database statement 110, and the interpretation of all nodes in the interpretable execution plan 130 satisfies the database statement 110. The database statement 110 can include expressions that are logical or arithmetic expressions, such as compound expressions with many terms and operators. The expressions can appear in clauses of the database statement 110, such as for filtering via an SQL WHERE clause. Nodes in the interpretable execution plan 130 can be specialized to perform table scans, expression evaluation, grouping, sorting, filtering, joining, concatenation of columns, or projection.

[0034] Output data from one or several nodes can be accepted as input by nodes in the next level in the tree of the interpretable execution plan 130. The data flow generally goes from leaf nodes to intermediate nodes and then to the root of the tree, and the output of the tree can be a set of multi-column rows, a single column, or a scalar value that can answer the database statement 110, and the DBMS 100 can return this output to the client on the same network connection that delivered the execution request from the client.

[0035] In an embodiment, the interpretable execution plan 130 is a directed acyclic graph (DAG) rather than a tree. In that case, DAG nodes can have a fan-out that transmits copies (by reference or by value) of the output of the DAG node to multiple nodes. While tree nodes can only provide output to one other node.

[0036] The interpretation of the interpretable execution plan 130 is data-driven, such as according to the visitor design pattern in an embodiment, which may require traversing the tree nodes. A post-order traversal visits child nodes before visiting the parent node. Tree nodes are interpreted when visited, which may require applying the logic of the node to the (one or more) inputs of the node. Each input of the node can be provided by a corresponding other node and can be a set of multi-column rows, a single column, or a scalar value. If the input is not a scalar, then the input contains multiple values, and the tree node can iterate over these values to process each value. For example, the logic of the node can include a loop for iteration.

[0037] Nodes are reusable through parameterized instantiation and can be combined into lists and subtrees to form the interpretable execution plan 130 as a composite. In this document and depending on the context, instantiation means either or both of the following: a) generating an object as an instance of a class, such as by calling a constructor method, and / or b) generating a class by providing argument values to the parameters of an evaluation template.

[0038] 1.3 Relational Operators

[0039] In this document, relational operators are parameterized and reusable data flow operators. Example parameters are not limited to upstream operators that provide input to the current operator or downstream operators that accept output from the current operator.

[0040] Different types of relational operators can appear in the interpretable execution plan 130. If the interpretable execution plan 130 is a tree, then different types of relational operators can have a fan-in by accepting different types and counts of parameters as input, respectively, but only one output parameter to identify only one downstream operator. If the interpretable execution plan 130 is a DAG, then the relational operator can have a fan-out that has multiple outputs and / or has outputs that identify multiple downstream operators.

[0041] In this document and depending on the context, a relational operator can mean either or both of the following: a) a reusable specification in a generalized form that cannot be used directly (e.g., a class or a template) or b) an instantiation of a relational operator specialized for a particular context (e.g., by the actual parameter values of the parameters). In this document, (b) can also be referred to as a node, a tree node, an operator node, or an operator instance. The interpretable execution plan 130 can be a tree or a DAG composed of nodes that are interconnected instances of relational operators. For example, a reusable relational operator can have multiple different instances in the same or different interpretable execution plans.

[0042] As a result of reusable composability, each node is more or less self - contained and depends as little as possible on other nodes in the interpretable execution plan 130. The logic of each node is independent to provide encapsulation and information hiding, such as by using callable subroutines or object classes. This may prevent many important optimizations applicable to collections of logical implementations (such as multiple subroutines discussed later in this document).

[0043] In an embodiment, a cost - based plan optimizer uses SQL database catalog information and the database statement 110 to construct a computation tree that describes the best execution (i.e., least computer resources, e.g., fastest) of decomposing the database statement 110 into SQL operators that are relational operators, which serve as row sources for retrieved or computed data tuples flowing up the tree from the leaves to the root. In other words, the query tree (or DAG) operates as a data flow graph.

[0044] Each SQL operator is an object that has a small set of virtual methods that are used to initialize itself (e.g., connect with other operators to form a tree or DAG) and return the result of a row source. Each operator instance contains constant data that these methods interpret slowly. Such constants can include any combination of the following:

[0045] · The expected input shape (i.e., row data structure, such as a column-based row data structure) from child (i.e., upstream) row sources, tables, or indexes,

[0046] · Encoded expressions that define a sequence of conditions to evaluate on incoming rows,

[0047] · Encoded expressions that define a sequence of results to compute from incoming rows and provide to the parent (i.e.,

[0048] downstream) row source, and / or

[0049] · A set of database indexes that must be updated, where the encoded expressions define the index keys for the database indexes.

[0050] The DBMS 100 can apply optimizations specific to database acceleration, such as rewriting database statement 110 and / or optimizing the interpretable execution plan 130 (such as during query planning). For example, the plan optimizer can generate multiple different and semantically equivalent plans and estimate their time and / or space costs to select the fastest plan. That is, the interpretable execution plan 130 can be the optimal plan for the database statement 110. However, the interpretation of the interpretable execution plan 130 is not necessarily the optimal execution of the interpretable execution plan 130.

[0051] 1.4 Plan Compilation

[0052] To execute as fast as possible rather than interpret, the DBMS 100 can include a plan compiler that compiles the interpretable execution plan 130 to generate target code, which is a series of native machine instructions of an instruction set architecture (ISA) that can be directly executed by a central processing unit (CPU). Just like interpretation, compilation may require tree traversal. When visiting a node, instead of interpreting the node, the plan compiler generates target code that implements the logic of the node.

[0053] In various embodiments, a plan compiler generates source code of a general-purpose programming language 140, which can be a high-level language (HLL), and then compiles the HLL source code using a compiler 152 to generate object code. For example, the HLL can be C, C++, or Java. Compiling the HLL source code can require any of the following operations: parsing by a parser 151 to generate an AST, semantic analysis, generating assembly language instructions, and / or generating optimized code of the object code.

[0054] In various embodiments, the plan compiler does not generate HLL source code, but instead directly generates any of those intermediate representations, such as an AST, an assembler, or bytecode from which native object code can be generated. In an embodiment, the plan compiler instead directly generates native object code without any intermediate representation (IR). Any generated material (such as the HLL representation, IR, and / or object code) can be written to a volatile buffer or a persistent file. In an accelerated embodiment, no files are generated, and all generated material is bufferred in volatile memory until no longer needed.

[0055] The object code generated from the nodes of the interpretable execution plan 130 can be relocatable or can be non-relocatable, and can be statically or dynamically linked into the logic and address space of the DBMS 100 for direct execution by the DBMS 100 to execute the database statement 110 without interpretation. Although the execution of the object code of the compiled plan is semantically equivalent to the interpretation of the uncompiled plan and their results are semantically equivalent, there can be semantically irrelevant differences between their results, such as the ordering of unordered results.

[0056] The general-purpose tools 140 and 151-153 are not explicitly designed to execute database statements, nor are they explicitly designed to be embedded (as shown in the DBMS 100). For example, the plan compiler can use the compiler 152 for code generation, but the compiler 152 itself is not a plan compiler.

[0057] For example, the DBMS 100 can have an SQL parser that generates a query tree, which is not the AST generated by the parser 151. The interpreter 153 for the general-purpose programming language 140 is shown in dashed outline to indicate that it: a) is generally not used by the techniques herein, b) is not a query interpreter and does not interpret the interpretable execution plan 130, c) may not exist, and d) is illustratively shown for contrast and disambiguation with the query interpreter. For example, any query execution technique that uses the interpreter 153 to interpret a Python script to execute the database statement 110 is different from the DBMS 100 interpreting the interpretable execution plan 130 to execute the database statement 110.

[0058] Unlike existing plan optimizers, the plan compiler can perform all code optimizations of the HLL source code compiler and the back-end code generator, such as discussed later in this article. Unlike existing plan optimizers, the plan compiler can also perform optimizations that require multiple relational operators (i.e., tree nodes) and cannot be applied to one node alone, as discussed later in this article. Therefore, the acceleration in this article can include three categories: avoiding interpretation, best breed optimization of machine code, and multi-operator optimization.

[0059] 1.5 Applying compile-time constants to evaluated templates

[0060] In an embodiment, the general programming language 140 natively supports parameterized evaluation templates, such as evaluation templates 160 with parameter(s) (such as template parameters 170) and evaluation templates that the plan compiler can generate for database statements 110. Predefined or newly generated (by a template generator included in the plan compiler) evaluation templates can be instantiated by the plan compiler by providing values ​​for corresponding arguments as parameters for the evaluation template 160. The arguments for the template parameters are effectively invariants in instances of the evaluation template 160. Different instances of the evaluation template 160 can have different argument values ​​for the same template parameter 170.

[0061] A template parameter may be a constant value, such as a compile-time constant 180, or may be a data type, such as a primitive data type or an abstract data type (ADT), such as an object class. A compile-time constant 180 is not a data type, an ADT, or an object class, although a compile-time constant 180 may be an instance of one of these. A data type is not materialized (i.e., lacks actual data), whereas a compile-time constant 180 specifies actual data.

[0062] In various embodiments, compile-time constant 180 may be a lexical constant, such as a literal or other lexical text acceptable to parser 151, or may be non-lexical binary data. If compile-time constant 180 is lexical, then evaluation template 160 is expressed as source logic acceptable to parser 151. In either case, artifacts 160, 170, and 180 facilitate partial evaluation, a general compilation technique that provides acceleration, as discussed later herein.

[0063] In an embodiment, compiler 152 is a C++ version 20 compiler that includes a parser 151, and template parameter 170 is a C++ non-type parameter. In that case, compile-time constant 180 can be a pointer to or a reference to an object that is an instance of a class. In an embodiment, interpretable execution plan 130 is an instance of a class, and compile-time constant 180 is a pointer to or a reference to that instance.

[0064] In an embodiment, compile-time constant 180 is instead a pointer to or a reference to the root node of a DAG or a tree. In non-C++ embodiments such as Java, compile-time constant 180 can be a literal specification of a lambda, closure, or anonymous class, and the compile-time constant can be the interpretable execution plan 130 or the root node of a DAG or a tree. In various embodiments, as discussed later herein, compile-time constant 180 can include (and interpretable execution plan 130 can include) any combination of the following:

[0065] · Multiple memory pointers,

[0066] · Multiple discontinuous memory allocations (such as in a heap),

[0067] · A tree or DAG,

[0068] · A composite expression based on database statement 110, and

[0069] · Database schema information.

[0070] In various embodiments, compiler 152 a) unconditionally or b) only when the template instance has different combinations of argument values for all the parameters of evaluation template 160, generates a new class or ADT for each instance of evaluation template 160. Herein, depending on the context, code generation can require any one or all of the following: a) generating evaluation template 160 by the template generator of the plan compiler, b) generating source logic for the lexical (e.g., text) of the template instance by the front end of compiler 152 for the plan compiler, and / or c) generating a native executable binary machine instruction sequence for the template instance by the back-end code generator of compiler 152.

[0071] 2.0 Example partial evaluation process

[0072] Figure 2 is a flowchart depicting an example computer process that the DBMS 100 can execute to optimally execute database statement 110 by using partial evaluation and evaluation template 160 for the compilation of interpretable execution plan 130. Refer to Figure 1 discussion Figure 2 .

[0073] Step 201 receives an execution request 121 to execute database statement 110, as discussed earlier in this document.

[0074] Step 202 generates an interpretable execution plan 130 representing database statement 110 from database statement 110, as discussed earlier in this document.

[0075] According to the execution criteria discussed later in this document, step 203 determines whether database statement 110 should be executed by interpreting interpretable execution plan 130. If step 203 determines "yes", then database statement 110 is executed by interpreting interpretable execution plan 130, as discussed earlier in this document, and Figure 2 the process stops. Otherwise, step 204 occurs.

[0076] Step 204 invokes a plan compiler, as discussed earlier in this document. Based on partial evaluation, step 204 compiles interpretable execution plan 130 into target code. The partial evaluation and other code generation optimizations that step 204 can apply are as follows.

[0077] In an embodiment, compiler 152 is a C++ version 20 compiler, and function templates for relational operators accept compile-time data structures as constexpr parameters, which facilitate partial evaluation of the function bodies of virtual functions of relational operators (such as row source functions). A natively compiled plan is produced by compiling a C++ file (e.g., stored only in volatile memory), and the C++ file uses the constant portions of the row sources to instantiate C++ function templates.

[0078] Step 204 avoids interpretation of interpretable execution plan 130. Plan interpretation is slow because the execution of each expression (or operation or operator) is deferred until it is actually and immediately needed, even if the expression has been executed before (such as in a previous iteration of a loop). The slowness of plan interpretation is caused by three main overlapping inefficiencies.

[0079] The first inefficiency is repetitive and redundant interpretation, such as due to iteration. For example, interpretable execution plan 130 may specify iteration over millions of rows of a relational table. Some of the decisions and operations may be needlessly repeated for each row / iteration. This problem is exacerbated by so-called non-blocking (e.g., streaming) operators, which are tree nodes that process and / or provide only one row at a time because the node does not support batch operations (such as for a batch of rows or all rows in a table). For example, a loop operator may repeatedly call a non-blocking operator, once per iteration to obtain the next row. Non-blocking operators are stateless and are fully interpreted during each call / iteration.

[0080] The second inefficiency in plan interpretation is control flow branching. A typical operator / node may have dozens of control flow branches to accommodate a rich variety of data types, pattern styles, constraints, and transaction conditions. The branches may impede hardware optimizations such as instruction pipelining, branch prediction, speculative execution, and instruction sequence caching.

[0081] The third inefficiency in plan interpretation is node encapsulation. Various important general optimizations are based on violating encapsulation boundaries, such as lexical block scope, information hiding, and the boundaries of classes and subroutines. For example, as explained later in this document, fusion of multiple operators, logical inlining, and cross-boundary semantic analysis are not available for plan interpretation.

[0082] Partial evaluation based on compile-time constant 180 provides a way to avoid the three main inefficiencies in plan interpretation for step 204. Partial evaluation requires performing some of the computational expressions that appear in the interpretable execution plan 130 during plan compilation and before actually accessing database content such as table rows. The literals and other constants specified in the interpretable execution plan 130 can be used to immediately resolve some of the expressions and conditions that appear in the implementation of the relational operators referenced / instantiated by the interpretable execution plan 130.

[0083] For example, a filter operator may contain one control flow path for comparison with a null value and another control flow path for comparison with a non-null value, and partial evaluation can select which control flow path to use during plan compilation for step 204 by detecting whether the WHERE clause of the database statement 110 specifies a null or non-null filter value. General examples of conditions that partial evaluation can detect and eagerly resolve and optimize include null value handling, zero value handling, data type polymorphism, down-casting, numeric demotion (i.e., narrowing conversions as opposed to widening promotions as discussed later in this document), branch prediction, and dead code elimination, virtual function linkage, inlining, node fusion, strength reduction, loop invariants, induction variables, constant folding, loop unrolling, and arithmetic operation substitution.

[0084] Those various general optimizations are complementary and in many cases synergistic, such that applying one optimization creates opportunities to use multiple other kinds of optimizations that might otherwise be unavailable. For example, inlining can overcome the lexical visibility barriers of the internal identifiers and expressions of the inlining. In turn, this can facilitate most other general optimizations. In some embodiments, some optimizations (such as fusion) may always be unavailable unless inlining occurs.

[0085] The compiler 152 can automatically provide partial evaluation and those optimizations based on the artifacts 130, 160, 170, and 180. The partial evaluation of step 204 requires transferring the specified details of the interpretable execution plan 130 into a reusable interpretable node implementation. In other words, step 204 uses partial evaluation to parse / embed the interpretable execution plan 130 into a reusable interpretable node implementation, which is referred to herein as the first Futamura projection. The result of the first Futamura projection is the generation of highly specialized logic that loses the ability of the generalization interpreter from which the logic is derived, in exchange for simplified processing of specific inputs such as the interpretable execution plan 130.

[0086] For example, if partial evaluation detects that null value handling is not required for the interpretable execution plan 130, then the null value handling logic in one or more operators is removed / missing in the highly specialized logic generated in step 204. Similarly, SQL has a high degree of data type polymorphism, but typical relational database schemas have little or no data type polymorphism. Partial evaluation can eliminate most type narrowing control flow branches because the interpretable execution plan 130 provides type specificity either explicitly as a compile-time constant 180 or indirectly as discussed later in this document.

[0087] In various embodiments, the compile-time constant 180 can include (and the interpretable execution plan 130 can include) database schema information and statistical information, which can include any combination of the following:

[0088] · Database dictionary,

[0089] · Identifiers of database tables,

[0090] · Statistical information of database tables,

[0091] · Column metadata,

[0092] · Identifiers of database triggers,

[0093] · Security labels,

[0094] · And identifiers of database indexes.

[0095] In various embodiments, the compile-time constant 180 can include (and the interpretable execution plan 130 can include) column metadata, which can include any combination of the following: column identifier, data type, column constraint, column statistical information, and encryption metadata.

[0096] In various embodiments, the compile-time constant 180 can include (and the interpretable execution plan 130 can include) security labels assigned to any one of the following: users, tables, or table rows.

[0097] In various embodiments, the compile-time constant 180 may include (and the interpretable execution plan 130 may include) any combination of metrics (e.g., for loop unrolling) of the database statement 110, such as:

[0098] · The count of table columns to be read,

[0099] · The count of table columns for filtering, and

[0100] · The count of columns to be projected (e.g., computed).

[0101] In most cases, the simplified logic generated by step 204 can only be used to execute the interpretable execution plan 130 for the database statement 110, and this simplified logic executes the interpretable execution plan faster than the plan interpreter. The plan interpreter is slower because it has to accommodate different plans for the same database statement and also has to accommodate different database statements and different database schemas.

[0102] The logic generated by step 204 can be simplified such that the control flow paths present in the simplified logic are only or almost only the few control flow paths actually required for the interpretable execution plan 130 of the database statement 110. Thus, the amount of branching that occurs dynamically during the execution of the object code generated by step 204 can be less (by one or more orders of magnitude) than the amount of branching incurred by interpreting the interpretable execution plan 130.

[0103] Step 204 generates object code based on the instruction set and architecture of the CPU of the DBMS 100, which may be an advanced CPU that was not considered or did not exist during application development when the database statement 110 was designed and shrink-wrapped. In other words, step 204 provides non-obsolete optimization for the database statement 110 by making intensive use of any general acceleration provided by the backend of the compiler 152 and the hardware at the time step 204 occurs.

[0104] For example, the evaluation template 160 may be generated by a template generator of a plan compiler designed before the latest and greatest release of the backend of the compiler 152. For example, after installing the plan generator and / or the template generator, the backend of the compiler 152 (e.g., the object code generator) may be repeatedly upgraded.

[0105] The Futamura projections can have multiple degrees of progression (i.e., numbered projections) along a range of strength, from optimizing the interpreter at one end of the range to recasting the optimized interpreter as a compiler, or optimizing the compiler at the other end of the range. The Futamura projection of step 204 includes reusing the planned interpreter as part of a planned compiler that generates highly optimized target code. Thus, step 204 performs what is referred to herein as the first Futamura projection, which actually implements compilation and generates target code.

[0106] Previous planned compilers did not perform Futamura projections. The planned compiler of step 204 performs the first Futamura projection because the planned compiler uses (but does not interpret) reusable interpretable node implementations and interpretable execution plan 130. Previous planned compilers used non-interpretable execution plans, which are not Futamura projections.

[0107] Having an interpretable execution plan is a necessary but not sufficient prerequisite for Futamura projection. For Futamura projection, the planned compiler must actually use the interpretable execution plan. A DBMS that can generate an interpretable plan for the planned interpreter and a separate non-interpretable plan for the planned compiler is different from a DBMS that can provide the interpretable execution plan 130 to either or both of the planned interpreter and the planned compiler for Futamura projection. In other words, the interpretable execution plan 130 has a dual purpose, which is novel. The novel efficiency provided by this dual purpose is discussed later in this document.

[0108] Step 204 generates target code representing database statement 110, and step 205 directly executes this target code, which may require statically and / or dynamically linking the target code into the address space of DBMS 100. The result of step 205 is semantically the same as the result provided if the interpretable execution plan 130 were interpreted rather than processed through steps 204 - 205.

[0109] 3.0 More Example Partial Evaluation Processes

[0110] Figure 3 is a flowchart depicting example computer processes A - C, and embodiments of DBMS 100 can implement and execute any of these computer processes to apply partial evaluation and / or the first Futamura projection, as discussed elsewhere in this document. Figures 2 - 3 The steps of the process are complementary and can be combined or interleaved. Refer to Figure 1 discussion Figure 3 .

[0111] Processes A - C have different starting points. Process B starts at step 301. Processes A and C start at steps 302A - B respectively, and the behaviors of processes A and C can be exactly the same and share the same implementation. Processes A and C occur entirely during the runtime phase 312 of the normal operation of the DBMS 100.

[0112] Process C is the most generalized because it anticipates the least functionality and integration of a general toolchain associated with a general - purpose programming language 140 including tools 151 - 152. With process C, the toolchain does not require specialized support for artifacts 160, 170, and 180. In an embodiment of process C, the compiler 152 is a C compiler that does not support C++ or a legacy C++ compiler lacking features of C++ version 20. In an embodiment of process C, the general - purpose programming language 140 does not have specialized support for evaluating templates.

[0113] Steps 302B and 304 occur only in process C. Step 302B receives an execution request 121 to execute a database statement 110, as discussed earlier in this document.

[0114] The following activities occur between steps 302B and 304. The query planner generates an interpretable execution plan 130. Generally, the interpretable execution plan 130 combines two software layers. In the reusable layer is the source code that defines reusable / instantiable relational operators. In the reusable layer, the operators are not instantiated and are loose / disconnected, meaning they are not connected to each other in any useful way.

[0115] The reusable layer can contain definitions of relational operators that are not related to the database statement 110 and will not be instantiated in the interpretable execution plan 130. The reusable layer is predefined during the original equipment manufacturer (OEM) build phase 311 that is not executed by the DBMS 100. The OEM build phase 311 is performed by an independent software vendor (ISV) (e.g., Oracle Corporation) that shrink - wraps the codebase of the DBMS 100. In other words, the reusable layer is included in the shrink - wrapped codebase of the DBMS 100.

[0116] The reusable layer contains the source code that defines reusable / instantiable relational operators. Although not part of the reusable layer and not used by the plan compiler, the object code compiled from the source code of the instantiable operators can also be included in the shrink - wrapped codebase of the DBMS 100. The object code of the instantiable operators is a component of the plan interpreter and is executed only during plan interpretation, while processes A - C do not perform plan interpretation.

[0117] Another layer is the plan layer that contains the interpretable execution plan 130, which may or may not contain references to relational operators from the reusable layer and instantiations of such relational operators. Different embodiments of any of Processes A - C may combine the plan layer and the reusable layer in various ways discussed herein.

[0118] For Process C, both ways are based on text substitution in step 304 by calling a general - purpose text macro pre - processor that is not built into the compiler 152 nor into the general - purpose programming language 140. For example, the macro pre - processor can be M4 or the C pre - processor (CPP).

[0119] In source code, macro pre - processing replaces occurrences of symbols with their expanded definitions. In other words, symbols operate as placeholders in the source code. The symbols to be replaced can be similar to identifiers and can be delimited by whitespace or other separators. The replacement definition of a symbol can be text consisting of one or more symbols (i.e., tokens).

[0120] Pre - processing can be recursive because symbols within the replacement text can themselves be replaced. A macro is a replacement definition that has parameters, and the original symbol can supply these parameters to be embedded into the replacement text.

[0121] Generally, the pre - processor in step 304 expects two bodies of text. One body of text contains an intermingled mix of text that should not be changed and symbols that should be replaced. The other text contains replacement definitions to be copied into the first body of text. For example, in C / C++, header files contain replacement definitions, and source files contain the body of text that will receive the replacements. To involve the pre - processor, the source file can reference the header file. The source file or the header file can reference multiple header files. A header file can be referenced by multiple source files and / or multiple header files.

[0122] Then, Process C performs step 305 of calling the compiler 152 to generate object code representing the interpretable execution plan 130 for the database statement 110. The object code can be executed directly, as discussed earlier herein. In an embodiment of Process C, steps 304 - 305 are combined, such as when a C / C++ compiler includes both a pre - processor and an object - code generator.

[0123] Instead of pre - processing, Process A relies on using evaluation template tools native to the general - purpose programming language 140 (such as version 20 of C++). Step 302A occurs as discussed above for step 302B.

[0124] Step 303 performs Turing-complete template metaprogramming (TMP) by using an interpretable execution plan 130 as a compile-time constant 180 that is provided for a template parameter 170 of an evaluation template 160 natively accepted by a general-purpose programming language 140. Turing-complete means that step 303 supports the full expressive power of SQL and any interpretable execution plan 130 for any database statement 110. For example, the evaluation template 160 can write to and / or declare static variables to implement stateful behavior.

[0125] In an embodiment, TMP is used for what is referred to herein as F-bounded polymorphism, which accelerates execution by not requiring virtual functions and virtual function tables, which are slow due to function-pointer-based indirection. In an embodiment, F-bounded polymorphism is used for polymorphism that occurs in composite structure design patterns such as query trees or DAGs where node types (i.e., the kinds of relational operators) are heterogeneous based on polymorphism. F-bounded polymorphism is faster than standard C++ polymorphism based only on classes without templates. Instead, F-bounded polymorphism is based on templated classes.

[0126] Step 303 can use a similar software layering between a reusable layer containing source code that defines instantiable operators and a plan layer containing the interpretable execution plan 130, as discussed above for step 304, where the interpretable execution plan may or may not contain references to and instantiations of relational operators from the reusable layer.

[0127] As discussed above for step 304, preprocessor text substitution can be recursive, in which case a replacement definition can contain placeholders that can be replaced by other replacement definitions. For example, preprocessor macros are composable and nestable such that one preprocessor macro can effectively be a composite of multiple other macros. Similarly for step 303, an evaluation template can effectively be a composite of multiple other templates. For example, one template can be used as an argument to another template, and a template can have multiple arguments.

[0128] Unlike processes A and C, which occur only during the runtime phase 312, process B is an implementation of process A with additional preparatory step 301 that occurs during the OEM build phase 311. Process B then executes process A during the runtime phase 312.

[0129] In a C++ embodiment, the reusable layer contains a header file that contains parameterized definitions of relational operators in the form of template classes, and the interpretable execution plan 130 can instantiate these template classes with arguments that are part of the interpretable execution plan 130.

[0130] Step 301 precompiles the header files of the reusable layer, which may include precompiling the definition of relational operators and / or some or all of the evaluation templates 160. For example, the evaluation template 160 may be part of the reusable layer, in which case the plan compiler does not need to generate the evaluation template 160 and only needs to instantiate the evaluation template 160. In this article, generating a template requires (either in a reusable manner or not in a reusable manner) defining the evaluation template. Template instantiation, instead, requires providing arguments for the parameters of the evaluation template.

[0131] Unlike the pre-tokenized header (PTH) which is only the result of lexical analysis, the generation of a precompiled header (PCH) also requires syntactic analysis and semantic analysis. The PCH may contain an abstract syntax tree (AST) in a binary format acceptable to the compiler 152. Step 301 may optimize the AST based on semantic analysis and / or specialization for the computer architecture of the DBMS 100. Example AST optimizations include inlining, constant folding, constant propagation, and dead code elimination.

[0132] Regardless of how the plan layer and the reusable layer are combined, and regardless of which of processes A - C occur, step 305 invokes the compiler 152 to generate the target code representing the interpretable execution plan 130 for the database statement 110, as discussed earlier in this article. Partial evaluation and / or Futamura projection may occur during a combination of some of steps 301 and 303 - 305.

[0133] The DBMS 100 may execute the target code generated through step 305 immediately or eventually directly, which may require statically and / or dynamically linking the target code into the address space of the DBMS 100. The result of executing the target code is semantically the same as the result provided if the interpretable execution plan 130 was interpreted instead of being compiled through one of processes A - C.

[0134] In various embodiments, the tools 151 - 152 are linked or not linked to the code library of the DBMS 100 and are loaded or not loaded into the address space of the DBMS 100. Regardless of process A - C, embodiments may build the general compiler 152 from the source code of the compiler 152 during the OEM build phase 311. For example, the limits on the size or count of inlining may be adjusted or disabled in the compiler 152, and other optimizations may be adjusted in the compiler 152. Query execution is a slightly special purpose, and a comprehensive optimization setting may be suboptimal for it. In an embodiment, the setting switches of the compiler 152 are adjusted, such as by embedding compiler - specific pragmas / directives in the source code of the reusable layer and / or the plan layer.

[0135] 4.0 First Example of Partial Evaluation

[0136] Figure 4 is a schematic diagram depicting an example partial evaluation that an embodiment of the DBMS 100 may perform (such as during Figure 2 step 204). Refer to Figure 1 for discussion Figure 4 .

[0137] In this document, a translation unit is a monolithic body (e.g., text) of source logic that the compiler 152 can accept and compile during a single invocation of the compiler 152. Although a translation unit is logically monolithic, the compiler 152 or preprocessor can generate a translation unit by linking or otherwise combining portions of source logic from different files, buffers, and / or streams. For example, an #include directive can cause the compiler 152 or preprocessor to logically insert the contents of one source file into the contents of another source file to generate a translation unit to be compiled as a whole.

[0138] The source code listing 410 can be all or part of a C++ translation unit accepted by the compiler 152, which does not necessarily mean that listing 410 is contained in a single source file. Listing 410 can contain a concatenation of different sequences of text lines, which are referred to herein as code snippets (snippet), such as the structure 420. Listing 410 contains the following enumerated code snippets 4A - 4D, which are vertically shown below in ascending order.

[0139] · 4A is a structure for both the template parameter 170 and the compile-time constant 180.

[0140] · 4B is the hybrid structure 420 explained below.

[0141] · 4C is the evaluation template 160 that is a template method in this case.

[0142] · 4D is an inline function implementing the relational operators of the specification.

[0143] Each of the code snippets 4A - 4D can be located in the same or separate source files. For example, listing 410 can only exist temporarily as a whole when the compiler 152 or preprocessor operates.

[0144] The code snippets 4A - 4D can be part of the reusable layer discussed earlier in this document, which is such as in Figure 3defined before or during the OEM build phase 311 (e.g., defined manually). Code snippets 4C-4D together can be a reusable specification of a relational operator, where code snippet 4C is mainly a wrapper for templatizing a relational operator (i.e., code snippet 4D) that is not defined in template form (e.g., legacy as explained below). For relational operators, all inputs and identifiers / references of the upstream and downstream operators are provided by structure 420, whose data members are a mix of constants and variables, as shown below.

[0145] In structure 420, field c represents a compile-time constant 180, which is in the format of code snippet 4A. Code snippet 4A has multiple fields that can aggregate constants from various sources, such as properties of computer 100 (e.g., machine word width), details of database statement 110, and elements of the database schema discussed elsewhere in this document. In this document, compile-time constant 180 can be any value (of any data type) that remains constant across repeated executions of database statement 110. Constants are suitable for partial evaluation, as discussed elsewhere in this document.

[0146] In structure 420, field d is a variable. In this document, a variable is data that can change between repeated executions of database statement 110. For example, even if database statement 110 does not mutate data, values contained in a relational table can mutate according to online transaction processing (OLTP) by other database statements. Variables are not suitable for partial evaluation.

[0147] In an embodiment, during the OEM build phase 311 Figure 3 step 301 pre-compiles manifest 410. For example, code snippets 4A-4D can be in the same header file or separate header files. In an embodiment, the header file containing code snippet 4C includes (one or more) other header files to combine code snippets 4A-4B and 4D. For example, code snippet 4C can provide a translation unit into which code snippets 4A-4B and 4D can be embedded. In any case, code snippet 4C is an evaluation template 160.

[0148] In various embodiments, pre-compilation can have various degrees. By more or less exhaustive pre-compilation, code snippet 4D can be inlined into code snippet 4C. By minimal pre-compilation (such as pre-tokenization), code snippets 4C-4D are tokenized without inlining, and inlining is instead deferred to Figure 3 partial evaluation during the runtime phase 312. Depending on the embodiment, pre-compilation requires or does not require partial evaluation.

[0149] In an evolving embodiment, most or all of the reusable layers were initially developed solely for interpretation and later developed query compilation with compiler 152 as an evolutionary improvement. For example, code snippet 4D could be a legacy artifact defined in a.c or.cpp file included in code snippet 4C. That is, the.c or.cpp file may counterintuitively be treated as a header file that can be included into another header file as an.h file.

[0150] In any case, manifest 410 is a composite of code snippets solely from the reusable layer. Code snippets 4A - 4D are part of the reusable layer. Neither manifest 410 nor code snippets 4A - 4D contain any details specific to a particular query. It is this query agnosticism that allows for optional pre - compilation of manifest 410.

[0151] For Figure 4 , when a real - time (e.g., highly specialized) query is received, Figure 3 the runtime phase 312 for Figure 4 can occur as follows. For

[0152] In the first phase where template instantiation is required, the following sequence occurs.

[0153] a) The query is parsed to generate an interpretable execution plan 130.

[0154] b) Query - specific constants such as compile - time constants 180 (e.g., a boolean value indicating the DISTINCT keyword, the count of columns being projected, or a boolean value indicating whether a column is defined as NOT NULL in the schema) are extracted or inferred from the query or from the interpretable execution plan 130. This requires initializing the fields of code snippet 4A.

[0155] c) Using those query - specific constants as arguments (such as template arguments 170), code snippet 4C (i.e., the evaluation template 160) is instantiated to generate instantiation 440.

[0156] This requires initializing the parameter in1 of code snippet 4C, as explained below.

[0157] In this example, the template argument 170 is the pointer in1 shown in code snippet 4C. Code snippet 4C also accepts variable (i.e., non - constant) inputs such as the pointer in2. However, the pointer in2 may change each time the query is repeated. Therefore, the pointer in2 is not involved during template instantiation.

[0158] Template instantiation is only part of the instantiation 440, which also includes additional source logic generated during the runtime phase 312. That is, the instantiation 440 is generated during the runtime phase 312. In addition, parameters such as the template parameter 170 are not the only places where constants (such as compile-time constants 180 or all or part of the interpretable execution plan 130) can be used. For example, in the instantiation 140, the identifier "compute_pe_2_2" and the actual argument "2,2" are generated as ordinary (i.e., non-template) logic based on compile-time constants, although they do not correspond to any part of the template materials 160 and 170.

[0159] The only part of the instantiation 440 that actually instantiates the template is the "compute_pe<&c_in>" of the template method of the instantiation code snippet 4C. In this way, the first phase generates the instantiation 440 as an instance of the code snippet 4C.

[0160] In the second phase, partial evaluation occurs in any way described elsewhere in this document. The second phase applies partial evaluation to the instantiation 440 to generate the expression 430, which can be understood according to Table 1 below. Table 1 maps the Figure 1 components to Figure 4 parts of

[0161] Figure 1 components Part of Listing 410 Part of Instantiation 440 Evaluation Template 160 Code Fragment 4C "compute_pe<…>” Template Parameter 170 "in1” Compile-Time Constant 180 "c_in”

[0162] In this example, when the second phase begins, the code snippet 4D has not been inlined into the code snippet 4C as discussed earlier in this document. Partial evaluation inlines the code snippet 4D into the instantiation 440.

[0163] After this inlining, partial evaluation uses loop unrolling and constant folding to generate the expression 430. Parts of the expression 430 are "8" and "val". "8" is calculated by partial evaluation. "val" is a variable to which partial evaluation does not apply. For example, the actual value of val may not be determined during partial evaluation. For example, the variable in2 in the code snippet 4C may not be initialized during partial evaluation. For example, the buffer that in2 will eventually point to may still not be filled and / or allocated during partial evaluation.

[0164] Counterintuitively, the variable val can be used to obtain compile-time constants. In code snippet 4D, “val->c.b” evaluates to b as a compile-time constant in code snippet 4A. In this way, code snippet 4D is used for a pre-built legacy interpreter and reused for queries accelerated by compilation and partial evaluation. This is important, especially because code snippet 4D can be a legacy implementation of a relational operator and, with little or no modification, can be transformed with code snippet 4C as a minimal template wrapper. In other words, most of the source code library of the legacy interpreter is reused as-is, which is not achieved by the prior art. For query compilation, the prior art rarely or does not reuse the legacy source logic of important components of the query interpreter, such as relational operators.

[0165] 5.0 Second Example Partial Evaluation

[0166] Figure 5 is a diagram depicting an example partial evaluation that an embodiment of DBMS100 may perform (such as during Figure 2 step 204). Refer to Figure 1 for discussion Figure 5 .

[0167] Source code listing 510 may be all or part of a C++ translation unit accepted by compiler 152. Listing 510 may contain a concatenation of different sequences of text lines, which are referred to herein as code snippets. Listing 510 contains the following enumerated code snippets 5A - 5B, which are shown vertically in ascending order: 5A) evaluation template 160 as a template method, and 5B) an inline function.

[0168] Code snippets 5A - 5B may be part of the reusable layer discussed earlier herein, which is defined (e.g., manually defined) before or during the OEM build phase 311 such as in Figure 3 . Code snippet 5B may be a reusable specification for a relational operator. For a relational operator, all inputs and identifiers of upstream and downstream operators are provided as a mix of constants and variables.

[0169] Code snippet 5B is referenced by interpretable execution plan 130. Code snippet 5B contains an if block and an else block. Compiler 152 performs partial evaluation including branch elimination, loop unrolling, and constant folding to transform the if block into expression 520 and the else block into expression 530, where two and three argument constants are injected respectively, as shown in the labels of the two arrows. Since the if and else blocks are mutually exclusive, only one of expression 520 or 530 is generated, depending on whether the if or else actually occurs.

[0170] 6.0 Example Execution Switching Process

[0171] Figure 6 is a flowchart depicting an example computer process that the DBMS 100 can perform to switch from an initial interpretation to a final compilation of the same repeated database statement 110. Figures 2 - 3 and Figure 6 the steps of the process are complementary and can be combined or interleaved. Refer to Figure 1 discussion Figure 6 .

[0172] Figure 6 The process of

[0173] occurs in response to receiving an execution request 121 to execute the database statement 110. Query compilation itself is slow, and if the database statement 110 will only be executed once or only a few times, then query compilation may be unjustified. In this embodiment, the DBMS 100 uses interpretation for the first or first few requests to execute the database statement 110.

[0174] Ultimately, step 602 executes the database statement 110 by interpreting the interpretable execution plan 130 without invoking tools of the general - purpose programming language 140, such as a parser 151, a compiler 152, or an interpreter 153. Generating the interpretable execution plan 130 may require parsing the database statement 110 by an SQL parser (which is not the parser 151).

[0175] Finally, step 604 receives a different execution request 122 to execute the same database statement 110 again. This time, the DBMS 100 instead decides not to interpret the interpretable execution plan 130. For example, an amortized cost threshold is discussed later in this document. Step 606 compiles the interpretable execution plan 130 into target code without generating bytecode or bitcode. In other words, the target code consists essentially of hardware instructions rather than a portable intermediate representation (IR). Whether the interpretable execution plan 130 itself contains no bytecode or bitcode depends on the embodiment.

[0176] In an embodiment based on the techniques presented earlier in this document, the DBMS 100 executes in sequence and without the DBMS 100 restarting:

[0177] 1. Execute the database statement 110 by interpreting the relational operators in the interpretable execution plan 130 representing the database statement 110, and

[0178] 2. Compile the text source code of the relational operators.

[0179] For example, the DBMS 100 itself does not need to be restarted between steps 602 and 606.

[0180] In an embodiment, execution requests 121-122 provide different corresponding values for the same literal in database statement 110. For example, database statement 110 can be a parameterized prepared statement, and the DBMS 100 can cache the statement for reuse. Preparing database statement 110 can require generating an interpretable execution plan 130. Database statement 110 can be prepared before or due to the first execution request 121.

[0181] Whether or not it is prepared, the interpretable execution plan 130 can be cached for reuse, such as in a query cache. In an embodiment, database statement 110 is not a prepared statement, and the interpretable execution plan 130 can be modified during reuse by substituting the value of the literal in the interpretable execution plan 130 based on which of execution requests 121-122 is executing. In various embodiments, substituting the value of the literal may or may not require recompiling the interpretable execution plan 130, which may or may not depend on whether database statement 110 is a prepared statement.

[0182] 7.0 Example Optimization Process Before Plan Compilation

[0183] Figure 7 is a flowchart depicting an example computer process that the DBMS 100 can execute to optimize before query compilation and defer query compilation unless the combined cost estimate meets a compilation threshold. Figures 2 - 3 and Figures 6 - 7 The steps of the process are complementary and can be combined or interleaved. Refer to Figure 1 for discussion Figure 7 .

[0184] Figure 7 The process of occurs in response to receiving execution request 121 to execute database statement 110. Step 702 optimally rewrites database statement 110. Example SQL rewrites include using UNION ALL instead of IN, generating temporary tables, and adding hints acceptable to the query optimizer. Unlike other query compilation schemes, step 702 can rewrite based on dynamic conditions and fluctuating statistics (such as the cardinality of tables, the cardinality of columns, and whether tables or columns or previous results have been cached in volatile memory).

[0185] Step 704 optimizes the interpretable execution plan 130, which may involve generating, calculating costs, and selecting the most efficient plan. Unlike other query compilation schemes, the generation and cost calculation in step 704 can be based on dynamic conditions and fluctuating statistics (such as the cardinality of tables, the cardinality of columns, and whether tables or columns or previous results have been cached in volatile memory).

[0186] Step 706 decides whether to compile or interpret the interpretable execution plan 130 by comparing the corresponding costs of interpreted execution and compiled execution. Step 706 can estimate three costs, which can measure time and / or space. The three listed costs are: a) the cost of interpreting the interpretable execution plan 130, b) the cost of compiling the interpretable execution plan 130, and c) the cost of executing the compiled plan.

[0187] For example, if the database statement 110 will be executed only once, then query compilation is only reasonable if cost (a) exceeds the arithmetic sum of cost (b) plus cost (c). If it is expected that the database statement 110 will ultimately be executed N times, then costs (a) and (c) will be multiplied by N, but cost (b) will not be multiplied by N. In other words, cost (b) is the only cost that can be amortized in repeated executions, which means that query compilation provides cost control while interpretation does not.

[0188] Step 706 can detect whether plan compilation is reasonable based on a detection related to costs (a)-(c) according to observed or predicted statistics of the execution requests 121-122 for the database statement 110. The detection in step 706 can describe at least one of costs (a)-(c).

[0189] To describe any of costs (a)-(c), step 706 can predict any combination of the following estimated statistics:

[0190] · The count of repetitions of requests to execute the database statement 110,

[0191] · The frequency of repetitions of requests to execute the database statement 110, and

[0192] · The variance of the frequency of repetitions of requests to execute the database statement 110.

[0193] In an embodiment, step 706 can decide to simultaneously: a) interpret the interpretable execution plan 130 in the foreground (i.e., the critical path) of the execution request 121, and b) compile the interpretable execution plan 130 in the background to speculatively anticipate potential future execution requests (such as the execution request 122).

[0194] 8.0 Database Overview

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

[0196] Generally, 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 for executing the integrated software components, where the combination of software and computing resources is dedicated to providing a particular type of function on behalf of a client of the server. A database server dominates and facilitates access to a particular database and processes requests from clients to access the database.

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

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

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

[0200] A database command can be in the form of a database statement. 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 (e.g., Oracle Database 11g). SQL data definition language (“DDL”) instructions are issued to the database server to create or configure database objects such as tables, views, or complex types. Data manipulation language (“DML”) instructions are issued to the DBMS to manage data stored in the database structure. For example, SELECT, INSERT, UPDATE, and DELETE are examples of common DML instructions in some SQL implementations. SQL / XML is a common extension of SQL used when manipulating XML data in object-relational databases.

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

[0202] 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 for executing the integrated software components on a processor, the combination of software and computing resources being dedicated to performing a specific function on behalf of one or more clients.

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

[0204] 8.1 Query Processing

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

[0206] A query can perform operations on data from the (one or more) objects row by row when loading the (one or more) source data objects, or perform operations on the (one or more) entire source data objects after loading the (one or more) source data objects. The result set generated by some operation(s) can be made available for (one or more) other operations, and, in this way, the result set can be filtered out or narrowed down based on some criteria, and / or joined or combined with (one or more) other result sets and / or (one or more) other source data objects.

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

[0208] Generally, a query parser receives a query statement and generates an internal query representation of the query statement. Typically, the internal query representation is a set of interrelated data structures representing the various components and structures of the query statement.

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

[0210] Hardware Overview

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

[0212] For example, Figure 8 is a block diagram of a computer system 800 on which embodiments of the present invention can be implemented. The computer system 800 includes a bus 802 or other communication mechanism for conveying information, and a hardware processor 804 coupled to the bus 802 for processing information. The hardware processor 804 can be, for example, a general-purpose microprocessor.

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

[0214] The computer system 800 also includes a read-only memory (ROM) 808 or other static storage device coupled to the bus 802 for storing static information and instructions for the processor 804. A storage device 810, such as a magnetic disk, an optical disk, or a solid-state drive, is provided and coupled to the bus 802 for storing information and instructions.

[0215] A computer system 800 can be coupled via a bus 802 to a display 812, such as a cathode ray tube (CRT), for displaying information to a computer user. An input device 814, including alphanumeric keys and other keys, is coupled to the bus 802 for transmitting information and command selections to the processor 804. Another type of user input device is a cursor control 816, such as a mouse, trackball, or cursor direction keys, for transmitting direction information and command selections to the processor 804 and for controlling cursor movement on the display 812. 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 facilitates specifying a position in a plane by the device.

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

[0217] As used herein, the term "storage medium" refers to any non-transitory medium that stores data and / or instructions that cause a machine to operate in a particular manner. Such storage media can include non-volatile media and / or volatile media. Non-volatile media includes, for example, optical discs, magnetic disks, or solid state drives, such as the storage device 810. Volatile media includes dynamic memory, such as the main memory 806. 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-ROM, any other optical data storage medium, any physical medium with patterns of holes, RAM, PROM, and EPROM, FLASH-EPROM, NVRAM, any other memory chip or cartridge.

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

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

[0220] The computer system 800 also includes a communication interface 818 coupled to the bus 802. The communication interface 818 provides two-way data communication coupled to a network link 820 that connects to a local network 822. For example, the communication interface 818 can be an integrated services digital network (ISDN) card, a cable modem, a satellite modem, or a modem that provides a data communication connection to a corresponding type of telephone line. As another example, the communication interface 818 can be a local area network (LAN) card to provide a data communication connection to a compatible LAN. A wireless link can also be implemented. In any such implementation, the communication interface 818 sends and receives electrical, electromagnetic, or optical signals that carry digital data streams representing various types of information.

[0221] The network link 820 typically provides data communication through one or more networks to other data devices. For example, the network link 820 can provide a connection through the local network 822 to a main computer 824 or to a data device operated by an Internet service provider (ISP) 826. The ISP 826 in turn provides data communication services through the global packet data communication network (now commonly referred to as the “Internet” 828). Both the local network 822 and the Internet 828 use electrical, electromagnetic, or optical signals that carry digital data streams. Signals through the various networks and signals on the network link 820 and through the communication interface 818, which carry digital data to and from the computer system 800, are example forms of transmission media.

[0222] The computer system 800 can send messages and receive data, including program code, through the (one or more) networks, the network link 820, and the communication interface 818. In the Internet example, a server 830 can send the code for an application request through the Internet 828, the ISP 826, the local network 822, and the communication interface 818.

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

[0224] Software Overview

[0225] Figure 9 is a block diagram of a basic software system 900 that can be used to control the operation of the computing system 800. The software system 900 and its components, including their connections, relationships, and functions, are merely exemplary and are not meant to limit the 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 functions.

[0226] The software system 900 is provided to bootstrap the operation of the computing system 800. The software system 900, which can be stored on the system memory (RAM) 806 and the fixed storage device (e.g., hard disk or flash memory) 810, includes a kernel or operating system (OS) 910.

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

[0228] The software system 900 includes a graphical user interface (GUI) 915 for receiving user commands and data in a graphical (e.g., "point and click" or "touch gesture") manner. In turn, these inputs can be operated on by the system 900 according to instructions from the operating system 910 and / or the application(s) 902. The GUI 915 is also used to display the operation results from the OS 910 and the application(s) 902, so that the user can provide additional input or terminate the session (e.g., log off).

[0229] The OS 910 can execute directly on the bare hardware 920 of the computer system 800 (e.g., the processor(s) 804). Alternatively, a hypervisor or virtual machine monitor system (VMM) 930 can be inserted between the bare hardware 920 and the OS 910. In this configuration, the VMM 930 acts as a software "buffer" or virtualization layer between the OS 910 of the computer system 800 and the bare hardware 920.

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

[0231] In some instances, the VMM 930 may allow the guest operating system (OS) to run as if it were running directly on the bare hardware 920 of the computer system 900. In these instances, the same version of the guest operating system configured to execute directly on the bare hardware 920 may also execute on the VMM 930 without modification or reconfiguration. In other words, the VMM 930 may provide full hardware and CPU virtualization to the guest operating system in some cases.

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

[0233] A computer system process includes the allocation of hardware processor time, and the allocation of memory (physical and / or virtual), the allocation of memory for storing instructions executed by the hardware processor, for storing data generated by the execution of instructions by the hardware processor, and / or for storing the hardware processor state (e.g., the contents of registers) between allocations of hardware processor time when the computer system process is not running. A computer system process runs under the control of an operating system and may run under the control of other programs executable on the computer system.

[0234] Cloud computing

[0235] The term “cloud computing” is generally used 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 for the rapid provisioning and release of resources with minimal management effort or service provider interaction.

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

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

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

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

Claims

1. A computer-implemented method, comprising: receiving a request to execute a database statement; in response to the request to execute the database statement, generating an interpretable execution plan representing the database statement; determining by a database management system (DBMS) that the execution of the database statement will not include interpreting the interpretable execution plan; compiling the interpretable execution plan based on partial evaluation; and executing the database statement based on the compiled interpretable execution plan.

2. The method according to claim 1, wherein said compiling based on the partial evaluation includes treating the interpretable execution plan as a compile-time constant.

3. The method according to claim 2, wherein: said compiling the interpretable execution plan includes invoking a compiler of a general-purpose programming language; said treating the interpretable execution plan as the compile-time constant includes using the interpretable execution plan as an argument of an evaluation template accepted by the general-purpose programming language.

4. The method according to claim 3, further comprising pre-compiling a part of the evaluation template before said receiving the request to execute the database statement.

5. The method according to claim 2, wherein the compile-time constant includes at least one selected from the group consisting of: a plurality of memory pointers, a plurality of discontinuous memory allocations, trees, directed acyclic graphs (DAGs), compound expressions based on the database statement, and database schema information.

6. The method according to claim 5, wherein the database schema information includes at least one selected from the group consisting of: a database dictionary, identifiers of database tables, statistical information of database tables, column metadata, identifiers of database triggers, security labels, and identifiers of indexes.

7. The method according to claim 2, wherein: said compiling the interpretable execution plan includes invoking a compiler of a general-purpose programming language; said treating the interpretable execution plan as the compile-time constant includes invoking a general text macro preprocessor that is neither built into the compiler nor built into the general-purpose programming language.

8. The method according to claim 1, wherein: the request to execute the database statement is a first request to execute a first database statement; the method further comprises: interpreting the interpretable execution plan to generate a response to the first request, and after said interpreting the interpretable execution plan, receiving a second request to execute a second database statement; and determining that the second execution of the database statement will not include interpreting the interpretable execution plan in response to receiving the second request to execute the second database statement.

9. The method according to claim 8, wherein: said interpreting the interpretable execution plan does not invoke tools of a general-purpose programming language; said tools of the general-purpose programming language include at least one selected from the group consisting of: a parser, a compiler, and an interpreter.

10. The method according to claim 8, wherein: the first request specifies a first value for a literal in the database statement; the second request specifies a second value for the literal in the database statement.

11. The method according to claim 1, further comprising detecting that the cost of interpreting the interpretable execution plan exceeds the combined cost of: compiling the interpretable execution plan and executing the database statement based on the compiled interpretable execution plan.

12. The method according to claim 11, wherein: the detection is based on statistical information estimated for the request to execute the database statement; the statistical information estimated for the request to execute the database statement includes at least one selected from the group consisting of: the count of repetitions of the request to execute the database statement, the frequency of repetitions of the request to execute the database statement, and the variance of the frequency of repetitions of the request to execute the database statement; the detection describes at least one selected from the group consisting of: the cost of interpreting the interpretable execution plan, the cost of compiling the interpretable execution plan, and the cost of executing the database statement based on the compiled interpretable execution plan.

13. The method according to claim 1, further comprising at least one selected from the group consisting of: rewriting the database statement before generating the interpretable execution plan, and optimizing the interpretable execution plan before compiling the interpretable execution plan.

14. The method according to claim 1, wherein at least one selected from the group consisting of: a) the interpretable execution plan does not include bytecode or bitcode, and b) the compilation of the interpretable execution plan does not include generating bytecode or bitcode.

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

Images