The first Futamura projection in the context of SQL expression evaluation

Abstract

This invention relates to database query execution optimization. Specifically, it describes techniques for optimal execution based on query interpretation translated into a domain-specific language (DSL) using optimizations such as partial evaluation, abstract syntax tree (AST) rewriting, just-in-time (JIT) compilation, dynamic analysis, speculative logic, and Futamura projection. In an embodiment, a database management system (DBMS) hosted on a computer generates a query tree representing a database query, which contains expressions represented by subtrees of the query tree. The DBMS generates a sequence of DSL instructions representing the subtrees. During the execution of the database query, the sequence of DSL instructions is executed to evaluate the expressions. In an embodiment, the AST is generated from the sequence of DSL instructions. In an embodiment, the DSL AST is optimally rewritten based on a runtime feedback loop that includes dynamic profiling information.

Description

Technical Field

[0001] This invention relates to database query execution optimization. Specifically, it describes techniques for optimizing query interpretation based on optimized translation into a domain-specific language (DSL) using methods such as partial evaluation, abstract syntax tree (AST) rewriting, just-in-time (JIT) compilation, dynamic parsing, speculative logic, and Futamura projection. Background Technology

[0002] Recent advances in research and industry have enabled the generation of Low-Level Virtual Machine (LLVM) bitcode for a given query plan. The LLVM bitcode is then compiled into machine code by the LLVM compiler (LLC) through a code generation step and by compiling the generated code during query compilation.

[0003] Code generation and LLVM compilation methods have significant limitations. The generated code is statically compiled, meaning it only considers information available at compile time. This has significant limitations because current runtime information cannot be taken into account. There are several reasons why current research and industry solutions fail to leverage dynamically profiled information.

[0004] There is no built-in parsing infrastructure in SQL expression operands. Even if there were, it would incur significant runtime overhead.

[0005] Prior knowledge at compile time is also unavailable because the entire query plan, including all intermediate results, needs to be evaluated.

[0006] ο does not support speculative logic. Attached Figure Description

[0007] In the attached diagram:

[0008] Figure 1 It is a block diagram depicting an example computer that optimizes the execution of database queries by translating them into a domain-specific language (DSL) using optimizations such as partial evaluation and just-in-time (JIT) compilation.

[0009] Figure 2 It is a flowchart depicting an example computer process used to optimize the execution of database queries based on interpretation by translating them into a DSL using optimizations such as partial evaluation and JIT compilation;

[0010] Figure 3 This is a block diagram depicting an example database management system (DBMS) that replaces subtrees in a query tree with synthesized query nodes that have partial evaluation of the subtrees.

[0011] Figure 4It is a flowchart depicting an example DBMS procedure that replaces a subtree in a query tree with a synthesized query node that has a partial evaluation of the subtree;

[0012] Figure 5 It is a block diagram depicting a DBMS that demonstrates incremental optimizations such as utilizing partial evaluation;

[0013] Figure 6 It is a flowchart depicting an example DBMS process that is generated and compiled directly or indirectly from a sequence of DSL instructions;

[0014] Figure 7 It is a flowchart depicting an example of optimization performed by a DBMS, such as that used to incorporate optimizations into a compilation.

[0015] Figure 8 This is a flowchart illustrating an example of two program transformations performed by a DBMS, which accelerate the execution of interpretation and / or compilation;

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

[0017] Figure 10 It is a block diagram of a basic software system that can be used to control the operation of a computing system. Detailed Implementation

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

[0019] General Overview

[0020] The approach presented in this paper differs from the static techniques of Low-Level Virtual Machines (LLVM), particularly by adding runtime feedback loops required for dynamic optimization. By using the Truffle language framework and the Graal Just-In-Time (JIT) compiler, runtime information can be considered, resulting in more optimized code. Thus, the Truffle language is implemented using an Abstract Syntax Tree (AST) interpreter. More specifically, Domain-Specific Language (DSL) elements are mapped to syntax nodes implemented according to the Truffle framework. Each syntax node has an execution method that implements the logic of the language element. To execute the language program, the Truffle framework invokes the language parser. The output of the Truffle language parser is the AST. For each parsed language element, the parser instantiates the corresponding syntax node and links all AST nodes to instantiate the AST. The result of the parsing step is the instantiated AST. The Truffle language program is then executed by interpretation, which means invoking the execution method of the root AST node. The execution method of the root node then invokes the execution methods of the syntax nodes attached as children of the root node. This process recursively processes the child nodes of a node in a top-down, depth-first manner.

[0021] Runtime information is collected during the interpretation process. Truffle's strength lies in its ability to fully leverage profiling information to trigger self-optimizing techniques. Some of these key optimization techniques include: type specialization, rewriting indirect function calls, polymorphic inlining caching, branch elimination, and speculative function inlining. If these speculative assumptions prove to be false, the specialized AST can be reverted to a more general version that provides functionality for more general cases (if available and compiled), or de-optimization can be triggered.

[0022] When the execution count of a syntactic node reaches a predefined threshold, Truffle triggers partial evaluation by invoking the Graal compiler. The first Futamura projection is applied by compiling the program represented by the AST and its interpreter. The output is highly optimized machine code with deoptimization points. These points are implemented as checkpoints, where deoptimization is triggered when speculative assumptions no longer hold. Deoptimization means transferring control from the compiled code back to the AST interpreter, where specialized nodes are reverted to more general versions.

[0023] Instead of interpreting SQL expressions or generating and statically compiling every LLVM code, SQL expressions can be evaluated by fully leveraging the Truffle framework. The SQL standard defines the syntax for the SQL language, including the syntax for SQL expressions. Therefore, SQL expressions can be described using a language. Consequently, SQL expressions can be implemented as the Truffle language.

[0024] By implementing SQL expressions as the Truffle language, the advantages of the Truffle framework and the Graal compiler can be fully utilized. Example implementations may incorporate the following techniques:

[0025] The overall idea is to recognize SQL expressions as a language that can be "truffelized". The new language is the Multilingual Engine Stub Language (MSL), which will be presented later in this article;

[0026] οMSL generator: An algorithm for generating MSL code for a given expression tree;

[0027] οMSL parser: An algorithm for parsing MSL code and instantiating the corresponding MSL Truffle syntax nodes;

[0028] This describes how to embed the Truffle and Graal compiler framework into a database management system (DBMS) architecture. A new SQL expression node is introduced, responsible for storing MSL code during the query compilation phase and executing the MSL code during the query execution phase. The new SQL expression node replaces the root of the corresponding SQL expression tree.

[0029] In one embodiment, a DBMS hosted on a computer generates a query tree representing a database query, which contains expressions represented by subtrees of the query tree. The DBMS generates a sequence of Domain-Specific Language (DSL) instructions representing the subtrees. During the execution of the database query, the sequence of DSL instructions is executed to evaluate the expressions. In one embodiment, an Abstract Syntax Tree (AST) is generated from the sequence of DSL instructions. In another embodiment, the DSL AST is optimally rewritten based on a runtime feedback loop that includes dynamically parsed information.

[0030] Unlike general-purpose programming languages, DSLs typically have a narrow (i.e., special) purpose, more or less limited to a specific subject area, such as a specific technical problem. Because the scope of a DSL is narrower than that of a general-purpose language, the syntax of a DSL can be much smaller (i.e., simpler). Therefore, DSL tools, such as taggers, parsers, semantic analyzers, code generators, optimizers, runtime profilers, and / or interpreters, can be greatly simplified compared to general-purpose tools.

[0031] Such lean tools should inherently have smaller codebases and simpler controls and data flow. Using the optimization techniques presented in this paper, such lean tools (as optimizable artifacts themselves) become even leaner, for example, when type inference excludes some of the DSL interpreter's own logic. The excluded interpreter logic is suitable for dead code elimination, which results in smaller and faster DSL interpreters, such as those via Futamura projection, as presented in this paper.

[0032] Futamura projection is a way of shrinking and accelerating the interpreter's codebase in memory during interpretation (i.e., interpreter operation). As explained later in this paper, lean and optimized tools, such as DSL interpreters, especially through Futamura projection, can save computing resources (such as time and staging space). Optimized DSL interpreters can become so small that each query can have its own DSL interpreter instance that is optimized independently for that query. With the amortization of optimization overhead (i.e., latency) across database records (such as in database tables or intermediate result sets), optimizable DSL interpreters can significantly accelerate self-organizing queries. Therefore, according to the techniques presented in this paper, optimizable DSL interpreters can be easily embedded into the query engine of a DBMS.

[0033] 1.0 Sample Computer

[0034] Figure 1 This is a block diagram depicting an example computer 100 in an embodiment. Computer 100 optimizes the execution of database queries 110 by translating them into a domain-specific language (DSL) using optimizations such as partial evaluation and just-in-time (JIT) compilation. Further optimizations will be presented later.

[0035] Computer 100 may be at least one computer, such as a rack server, a blade computer, a personal computer, a mainframe, a virtual computer, or other computing device. When computer 100 has multiple computers, the multiple computers are interconnected through a communication network.

[0036] Although not shown, computer 100 may host a database management system (DBMS) that receives and executes database queries 110, which may contain relational algebra, such as according to a data manipulation language (DML) such as Structured Query Language (SQL). For example, the DBMS may be a relational DBMS (RDBMS) containing one or more databases, and the databases(s)(s) contain relational tables. Alternatively, the DBMS may contain one or more other data repositories, such as column repositories, tuple repositories (such as Resource Description Framework (RDF) ternary repositories), NoSQL databases, and / or Hadoop File System (HDFS) (such as those with Hadoop and / or Apache Hive).

[0037] Database query 110 can be an autoorganizing query or other DML statements, such as prepared (i.e., batch) statements and / or create, read, update, and / or delete (CRUD) statements. The DBMS can receive and parse database query 110 to create query tree 130, which represents database query 110 in a format that can be executed (e.g., interpreted) by the DBMS's database server to apply relational algebra.

[0038] Query tree 130 contains operation nodes as tree nodes (not shown) arranged as a logical tree data structure, such as those created when a query parser (not shown) parses database query 110 (e.g., before the query plan). According to one or more other diagrams herein, the DBMS may contain a query engine that includes pipelines of stages that generate increasingly faster representations of database query 110. Some of these generative stages may be ordered as follows: query tree 130, domain-specific language (DSL) instruction sequence 150, and abstract syntax tree (AST) for DSL instruction sequence 150, as shown in one or more other diagrams.

[0039] Therefore, a DBMS can generate a query tree and a DSL AST. Thus, two logical trees can be generated, each representing some or all of the database query 110.

[0040] In this embodiment, query tree 130 is also an AST, but for a language different from the DSL AST, such as SQL. In any case, both query tree 130 and the DSL AST contain tree nodes. In this document, a query node is any tree node in the query tree. A syntax node is any tree node in the DSL AST. DSL and AST will be presented later. In this embodiment, query tree 130 may be a query plan.

[0041] Both types of trees can have their tree nodes arranged hierarchically. A tree node can have another tree node as its parent node, and that parent node can have more child nodes. A tree can have subtrees of nodes that indirectly share the same ancestor node, such as 140. A tree node with no child nodes is a leaf node.

[0042] Each query node can execute a portion of database query 110, enabling the operations of all nodes in query tree 130 to complete database query 110. Database query 110 contains expression 120, which can be logical and / or arithmetic expressions, such as compound expressions with many terms and operators, such as clauses in database query 110, such as those used for filtering, such as SQL WHERE clauses. Query subtree 140 represents expression 120 and is generated from it.

[0043] In an embodiment, the DBMS may directly execute some or all of the query nodes to complete database query 110. In an embodiment, the DBMS alternatively or additionally generates sequences (such as 150) of DSL instructions such as 161-162 (such as imperative statements), or lines of DSL source logical text. For example, DSL sequence 150 may be a DSL script, such as stored in a file or random access memory (RAM). The Multilingual Engine Stub Language (MSL) is an example implementation of a particular DSL, based on several innovative techniques and mechanisms presented later herein.

[0044] A DSL sequence can represent and be generated from the entire query tree 130, or, for partial evaluation, one or more DSL sequences can represent and be generated from the corresponding subtrees or individual query nodes of the query tree 130. Therefore, DSL sequence 150 represents both subtree 140 and expression 120, and is generated directly from subtree 140, and thus indirectly from expression 120. Thus, the DBMS can execute either subtree 130 or DSL sequence 150 to actually apply expression 120.

[0045] For example, an optimizer such as a query planner (e.g., an eager optimizer) can generate the DSL sequence 150 more or less immediately and switch to using it once the DSL sequence 150 is generated. In an embodiment, the optimizer can wait for the generation of the DSL sequence 150 to complete. A lazy optimizer can decide whether to continue operating on subtree 130 or instead (e.g., pause to) generate the DSL sequence 150, such as even after query results (not shown) have begun to be generated.

[0046] DSL sequence 130 may eventually, or even initially, run faster than subtree 130, due to incremental and / or speculative optimizations, as presented later in this paper. Therefore, even though generating DSL sequence 150 incurs overhead such as latency, these costs can be amortized across, for example, a database table with millions of rows. For instance, the DSL sequence used for querying tree 130 or subtree 140 can be cached, just as querying tree 130 or subtree 140 can be cached.

[0047] As will be presented later in this document, additional levels of code generation may exist, such as compiling DSL sequence 150 into bytecode and / or machine code, and such further generated code may also be cached, speculative, and / or partially evaluated, and those optimizations may be cooperative, as discussed later in this document. For example, directly executable machine code may have already been generated for subtree 140. However, another subtree of query tree 130 may still only have bytecode for interpretation.

[0048] In this embodiment, the DBMS may maintain statistical metadata such as table cardinality (i.e., the number of rows), which can facilitate cost calculations to predict whether the amortization justifies a particular optimization. In this embodiment, database table columns may be run-length encoded, and cost calculations may be based on run-length(s).

[0049] 2.0 Example DSL Generation Process

[0050] Figure 2 This is a flowchart depicting, in an embodiment, how computer 100 optimizes the execution of database query 110 by interpreting it using optimizations such as partial evaluation and JIT compilation translated into a DSL. (See reference) Figure 1 discuss Figure 2 .

[0051] Step 202 generates a query tree 130 representing database query 110, which contains expressions 120 represented by subtrees 140. For example, a DBMS of computer 100 may receive a (e.g., self-organizing) database query 110, such as a text statement, on a network socket via Open Database Connectivity (ODBC). The DBMS includes a parser that tags and parses the database query 110 to generate the query tree 130 and its query nodes.

[0052] Step 204 generates a sequence 150 of DSL instructions that directly represent the query subtree 140 and indirectly represent the query expression 120. For example, such as according to visitor software design patterns, the DBMS can traverse the query nodes of subtree 140 to generate DSL instructions 161-162. For example, each of DSL instructions 161-162 can be generated from the corresponding query node of subtree 140. In some cases, multiple (e.g., consecutive) DSL instructions can be generated for the same query node. A working example implementation of the DSL instruction sequence 150 for query expression 120 is presented later in this document.

[0053] Step 206 involves executing a DSL instruction sequence 150 during the execution of database query 110 to evaluate query expression 120. For example, the DBMS may compile the DSL instruction sequence 150 into bytecode or machine code for execution in step 206, as discussed later herein.

[0054] 3.0DSL Embedded

[0055] Figure 3This is a block diagram depicting an example DBMS 300 in an embodiment. In query tree 340, DBMS 300 replaces subtree 350 with a synthesized query node 366, which has a partial evaluation of subtree 350. DBMS 300 may be an implementation of a DBMS (not shown) hosted by computer 100.

[0056] DBMS 300 generates a query tree 340 from the received database query 310 containing DML expression 320, with subtree 350 generated from DML expression 320. Repeated interpretation of subtree 350 can be slow and can mature immediately or eventually for partial evaluation. For example, the lazy threshold will be discussed later. DBMS 300 generates a DSL instruction sequence 370 from subtree 350. Modifying the DSL instruction sequence 370 into query tree 340 can occur as follows.

[0057] A composite tree node 366 can be generated as an adapter (e.g., a container) for the DSL instruction sequence 370. While subtree 350 may contain many query nodes (such as 361-362), subtree 350 has a single query node (not shown) as its root. In query tree 340, DBMS 300 replaces subtree 350 with a single query node 366. Therefore, execution of query tree 340 will trigger the execution of DSL instruction sequence 370, not subtree 350.

[0058] In this embodiment, the leaf nodes of subtree 350 (such as 363-365) are handled specially. Unlike query nodes 361-362, which can be discarded or cached after being removed from query tree 340, leaf nodes 363-365 instead find a new parent node to become children of the synthesized tree node 366. In this embodiment, leaf nodes 363-365 can be children of query nodes 361 or both 362 and 366 simultaneously.

[0059] Database query 310 can access table columns 331-332, such as in CRUD expressions, for filtering purposes. Within the DSL instruction sequence 370, local variable declarations, such as 381-382, can be generated separately for each of table columns 331-332. Therefore, the semantics of local variables can be utilized, such as those defined by a specific DSL, and can include issues of Boolean algebra, data type, and lexical scope, such as visibility and lifetime.

[0060] Data types can be more or less universally imposed on data, such as for table columns, items in DML expression 320, and / or local variables. Data types induce (e.g., speculative) optimizations. For example, some data types consume less storage space, execute with fewer instructions, and / or utilize special hardware for acceleration (such as registers, vector hardware, and / or coprocessors). Therefore, the DSL instruction sequence 370 may contain type-specific instructions (such as 390) that, if applied to data of the wrong (e.g., unexpected) data type, may cause malfunctions, as discussed later herein.

[0061] 4.0 Example DSL Embedding Process

[0062] Figure 4 This is a flowchart depicting, in an embodiment, DBMS 300 replacing subtree 350 in query tree 340 with a synthesized query node 366, which has a partial evaluation of subtree 350. (See reference...) Figure 3 discuss Figure 4 .

[0063] Figure 4 The process occurs in two phases that can happen rapidly and sequentially. Steps 402 and 404 generate the DSL instruction sequence 370. Steps 406A-B insert the DSL instruction sequence 370 into the query tree 340.

[0064] Step 402 may read the database schema and / or database dictionary of the database in DBMS 300 to discover illustrative details, such as tables, their columns, and explicit or implicit relationships between tables. Step 402 may alternatively or additionally receive this information from the query optimizer, query planner, and / or semantic analyzer used for query tree 340. Therefore, step 402 detects that query expression 320 references table columns 331-332. Local variable declarations 381-382 are generated as DSL instructions, which can provide functionality such as accessing and / or typing actual data in the corresponding table columns 331-332.

[0065] Step 404 generates type-specific instructions. For example, the data type of table column 331 or 332 can be obtained as described in other illustrative details for step 402. For example, query nodes 361-362 may have been specifically designed for the corresponding data type, and / or query nodes 361-362 may have metadata indicating the corresponding data type.

[0066] For example, table column 331 can store text that should be processed differently from numbers. Therefore, step 404 generates a type-specific DSL instruction 390 specifically for text processing. In an example not shown, steps 402 and 404 occur together as a single step to generate the same single instruction when DSL instructions 382 and 390 are actually the same single instruction. In other words, local variable declaration 382 can be type-specific. For example, a particular DSL might be strongly typed.

[0067] Step 406A generates a composite query node 366 containing the DSL instruction sequence 370. For example, the DSL instruction sequence 370 may reside in a RAM buffer, and the query node 366 may store addresses, pointers, handles, and / or offsets into and / or in that buffer.

[0068] As explained elsewhere in this document, the AST, bytecode, and / or machine code may be generated directly or indirectly from the DSL instruction sequence 370 at different times. This generated material can be dynamically attached to the query node 366, such as via memory pointers, so that (e.g., repeatedly) operating the query node 366 always executes the most recently generated implementation. Therefore, operating the query node 366 may sometimes interpret the bytecode and at other times directly execute the machine code.

[0069] In all cases, query node 366 should be inserted into query tree 340 before query node 366 is invoked. In query tree 340, step 406B replaces subtree 350 with query node 366. Therefore, query tree 340 is variable to some extent, such as for optimization purposes.

[0070] 5.0 Partial Assessment

[0071] Figure 5 This is a block diagram depicting an example DBMS 500 in an embodiment. DBMS 500 demonstrates incremental optimizations such as utilizing partial evaluation. For example, DBMS 500 parses a DSL instruction sequence 520 to generate a DSL AST 530, which can then be optimally rewritten and from which an initial compilation 551 can be immediately generated and further refined into an optimized compilation 552, as shown below. DBMS 500 may be an implementation of a DBMS (not shown) of computer 100.

[0072] Upon receiving database query 510, and although not shown, a query tree can be generated from database query 510. DSL instruction sequence 520 can be generated from some or all of that query tree. DSL instruction sequence 520 can be parsed to generate DSL AST 530. Therefore, two trees can be generated, each representing at least a portion of database query 510.

[0073] In a more efficient embodiment (not shown), instead of generating the DSL instruction sequence 520, the DSL AST 530 is generated directly from some or all of the query tree. In a less efficient embodiment, the database query 510 is a statement in DML, which is also an Extensible Markup Language (XML), such as SQL / XML or XQuery, allowing Extensible Style Sheet Language (XSL) to be applied to part or all of the database query 510 to generate the DSL instruction sequence 520.

[0074] In one embodiment, Oracle Truffle can parse the DSL instruction sequence 520 to generate a DSL AST 530. In another embodiment, the configurable parser can use a formal grammar that defines a specific DSL.

[0075] The Oracle technology stack can include Truffle, Graal, Substrate, and the Java Virtual Machine (JVM). As discussed in this article, this toolchain provides robust optimizations such as partial evaluation, AST rewriting, just-in-time (JIT) compilation, dynamic analysis, data type inference, speculative logic, and Futamura projection.

[0076] For example, the JVM can defer garbage collection (GC) until convenient, while C programs typically do not. For instance, in the worst case, C's free function and C++'s delete operator can be somewhat unreliable for the heap management that actually occurs in the foreground (i.e., the critical path of execution) of the standard library.

[0077] JVM garbage collection (GC) techniques (such as parallel GC and generational sweeping) and Just-In-Time (JIT) compilation can result in faster execution than C in some cases. Therefore, JVM-based database query engines may (soon or eventually) constitute a better use of rich-chip circuitry systems compared to state-of-the-art database operations.

[0078] Code optimization / generation can be urgent or lazy and occur in the foreground (i.e., the critical path) or the background (e.g., spare CPU cores or threads). Databases can store regularized data that encourages iteration, such as table rows. Therefore, the execution of database query 510, even if only once, may require numerous repetitions, such as utilizing table scans. Incremental optimizations can be generated in stages, more or less, during the execution of database query 510. Those optimization / generation stages may or may not be separated by lazy thresholds that can only be crossed with sufficient repetition, such as utilizing hotspots.

[0079] Lazy optimization offers two main benefits. First, laziness enables prioritization, ensuring that hotspots are optimized first and unused code is never optimized. Second, as discussed later in this article, laziness promotes dynamic optimization. In contrast, eager optimization only offers static optimization. For example, data type inference and speculative logic can rely on laziness, as discussed later. For instance, laziness can optimize feedback loops, which eager optimization cannot.

[0080] For example, query result 570 can be generated when database query 510 is completed. Query result 570 can be generated incrementally, such as generating parts 574-576 sequentially. First, only the query tree can be generated and directly interpreted to process some rows of the table to generate part 574. Concurrently with generating result part 574, a DSL AST 530 can be generated from the query tree, and an initial compilation 551 of bytecode and / or machine code can be generated from some or all of the DSL AST 530. The initial compilation 551 may be convenient (i.e., naive), with limited optimization or no optimization, such as static optimization only.

[0081] When the initial compilation 551 is ready (i.e., generated), the direct interpretation of the query tree can stop, and the initial compilation 551 can be executed at time T1 to process more rows of the same table to generate result portion 575. The initial compilation 551 may be accompanied by mechanisms (e.g., instrumentation) to record dynamic profile data 560 while the initial compilation 551 is executed during T1. Therefore, the execution of the initial compilation 551 generates result portion 575 and dynamic profile data 560. Dynamic profile data 560 may include details such as observed data types used for data type inference to generate speculative logic, and execution frequencies such as having basic blocks or individual instructions, and for dead code elimination or hotspot detection (such as tight loops).

[0082] At time T2, further optimizations are possible in scenarios such as generating an optimized compilation 552 as a replacement for the initial compilation 551. For example, the optimized compilation 552 could have intensive static optimizations. For instance, the generation of compilations 551-552 could be initiated simultaneously on separate processor cores, while the initial compilation 551 is generated and put into use first. In another example, dynamic profile data 560 can provide the feedback needed to generate the optimized compilation 552, which is generated directly from the DSL AST 530 at different times. In yet another example, the optimized compilation 552 is a refinement of the initial compilation 551 based on dynamic profile data 560 and / or auxiliary static optimizations (such as utilizing a peephole).

[0083] Utilizing the data type inference from dynamic profile data 560, the optimized compilation 552 may include speculative logic 590 to infer the data type. When the type inference proves incorrect, the speculative logic 590 may be accompanied by protection against optimization. For example, the initial compilation 551 may be cached, and then resumed if the speculative logic 590 becomes invalid. Otherwise, at time T3, the optimized compilation 552 successfully executes to process more rows of the same table to generate result portion 576. Thus, all result portions 574-576 can be generated for the same DML expression in database query 510, but each is generated by a correspondingly (e.g., very) different generation implementation. For example, portion 574 may be generated by interpreting DSL AST 530; portion 575 may be generated by bytecode interpretation; and portion 576 may be generated by direct execution of machine code.

[0084] What makes DBMS 500 unique is its ability to propagate runtime feedback backward along the optimization / generation pipeline, returning it to any pipeline stage, all the way back to the earliest stage. Existing technologies, which can be optimized using a low-level virtual machine (LLVM), lack this capability. For example, data type inference and hotspot detection (such as for dynamic profile data 560) can propagate backward far enough for innovative optimizations, such as rewriting the DSL AST 530 or even regenerating the DSL instruction sequence 520. This deep feedback, especially when combined with partial evaluation, enables radical optimizations, such as the Futamura projection explained later in this paper.

[0085] To further optimize, later stages of the optimization / generation pipeline can blur the structural boundaries artificially imposed in earlier stages. For example, DSL AST 530 contains syntactic nodes, such as 541-542, that more or less reflect the semantics of database query 510. For example, individual clauses of database query 510 can be represented by individual subtrees of DSL AST 530.

[0086] However, the optimization / generation of the optimized compiler 552 can have dense optimizations based on ignoring artificial boundaries (such as query nodes, syntax nodes 541-542, and subtrees of the query tree or DSL AST 530). For example, the optimized compiler 552 can contain a sequence 580 of machine or bytecode instructions that interleave instructions 583 and 585 for syntax node 541 with instructions 584 and 586 for syntax node 542. Therefore, the DSL AST 530 can be more or less obfuscated (i.e., lost) within the instruction sequence 580.

[0087] 6.0 Example Optimization Process

[0088] Figure 6 This is a flowchart depicting how, in this embodiment, DBMS 500 generates compilations 551-552 directly or indirectly from DSL instruction sequence 520. (See reference...) Figure 5 discuss Figure 6 .

[0089] The generation and use of DSL AST 530 are exemplary. Depending on the implementation, a compiler such as 551 may be generated from DSL AST 530 or directly from DSL instruction sequence 520 without creating DSL AST 530.

[0090] Step 601 compiles DSL AST 530 to generate an initial compilation 551, which may be a naive compilation of bytecode and / or machine code with little optimization.

[0091] Steps 602-603 occur concurrently at time T1. Step 602 performs an initial compilation 551 to generate part 575 of query result 570. While the initial compilation 551 is executed, step 603 records dynamic profile data 560 of the initial compilation 551. For example, step 603 may record the observed data types used for type inference and the logical frequencies used for hotspot detection.

[0092] At time T2, step 604 generates an optimized compilation 552 based on dynamic profile data 560. In one example, dynamic profile data 560 is applied to the initial compilation 551 to generate the optimized compilation 552. In another example, both dynamic profile data 560 and DSL AST 530 are used to generate the optimized compilation 552. In either case, step 604 can use dynamic profile data 560 for optimization analysis, such as type inference and / or hotspot detection.

[0093] At time T3, step 605 performs an optimized compilation 552 to generate result portion 576. Examples of optimizations (such as those used to incorporate into the optimized compilation 552) are as follows.

[0094] 7.0 additional optimizations

[0095] Figure 7 This is a flowchart depicting an example of optimization performed by DBMS 500 in an embodiment, such as for incorporation into an optimized compilation 552. References Figure 5 discuss Figure 7 .

[0096] Step 701 generates an instruction sequence that interleaves the processing of multiple tree nodes. The embodiment can apply interleaving at any optimization / generation pipeline stage of instruction generation. For example, and although not shown, database query 510 can be resolved into a query tree of query nodes. A corresponding DSL instruction(s) can be generated from each query node, and DSL instructions for different query nodes can be interleaved. For example, local variable declarations from multiple query subtrees can be hoisted to the beginning of the DSL instruction sequence 520.

[0097] In another example shown, corresponding bytecode or machine code can be generated from each syntax node 541-542, and those codes for different syntax nodes can be interleaved. For example, the code generator can reorder instructions by dispersing (i.e., decomposing) clusters of memory-bus intensive code for syntax node 541 to interleave computations for syntax node 542. In another reordering example, some instructions for syntax nodes 541-542 can become clustered together, such as when shared data from RAM is temporarily available in a register.

[0098] Step 701 may or may not be based solely on static analysis. However, steps 702-706 may involve dynamic optimization of the dynamic profile data 560, for example, for type inference.

[0099] Step 702 implements type specialization in the optimized compilation 552, DSL AST 530, and / or DSL instruction sequence 520. For example, instruction 583 can be specialized for text processing, such as type inference.

[0100] Motivations for type specialization include simplification to save time and / or space. For example, step 703 selects a subtype that requires less storage space than the original (i.e., generic) type. For instance, an integer variable indicating a calendar month can be downgraded from machine words to bytes. Similarly, database table columns can be dictionary-encoded, and their encoding dictionary can imply at most a few bits or bytes are needed. For example, when the dictionary has only a few dozen keys, one byte is sufficient to store any value. In embodiments, the encoding dictionary itself is stored in the database schema or database dictionary.

[0101] Step 704A generates speculative logic 590 based on the inferred type. For example, a byte can be wide enough to store any value observed so far for a particular variable. Therefore, speculative logic 590 treats that variable as a byte.

[0102] However, the inferred type may eventually be violated. For example, that particular byte variable may eventually encounter a value that requires more bytes to store. Therefore, when the protection detects a violation (i.e., a large) value, step 704B is triggered. Step 704B de-optimizes the optimized logic 552. For example, the initial compilation 551 may be rebuilt or retrieved from the cache.

[0103] Step 705 eliminates control flow branches, such as those used to eliminate dead code. For example, type inference can reveal that all values ​​of a numeric variable are non-zero. Therefore, the logic for detecting and specifically handling arithmetic division by zero can be eliminated.

[0104] Type inference is driven by polymorphism, where the same source logic can handle values ​​of different actual types. Object-oriented techniques such as inheritance and / or duck typing encourage polymorphism, for which mechanisms such as dynamic dispatch of virtual methods are available. Therefore, a caller can invoke a method without knowing that it can be implemented in multiple ways.

[0105] Techniques based on thunks (double-form conversion), trampolines, or dispatch tables can provide the indirection needed to select and invoke a specific implementation of a method. Type inference facilitates the prompt selection of a specific implementation of a method, which is efficient. Therefore, step 706 can be rewritten to directly invoke the specific implementation instead of traversing the indirect structure.

[0106] For example, step 707 can be further improved by directly inlining (i.e., embedding) the chosen implementation into the caller's logic. For instance, a spider can be a subtype of an animal, whose leg counting method always returns eight. Therefore, the literal constant eight can be inlined into a caller whose animal is a spider, as determined by type inference.

[0107] For example, step 708 can go further by manipulating the polymorphic inline cache, which is helpful when data type inference can narrow a variable to several different data types but not to a single type. The polymorphic inline cache operates as an incrementally growing dispatch table, which is initially empty and can add and retain bindings to a specific implementation of the method as it eventually encounters a corresponding subtype. If that dispatch table has a cap on the number of bindings it can store, then the dispatch table will stop growing, but will not evict cached bindings. Each calling site that invokes the same method can have its own corresponding polymorphic inline cache, and its contents can differ (i.e., be distinguishable) even if different calling sites target the same nominal (i.e., abstract) method.

[0108] 8.0 Program Transformation

[0109] Figure 8 This is a flowchart illustrating an example of two program transformations performed by DBMS 500 for accelerated interpretation and / or compilation in an embodiment. (Reference) Figure 5 discuss Figure 8 .

[0110] Step 802 describes DSL AST optimization based on runtime feedback. For example, DSL AST 530 can be rewritten (i.e., optimized) based on dynamic profile data 560. For example, dynamic profile data 560 can indicate that a control flow branch remains unused after iterating more than 1000 table rows to date. DSL AST 530 can be rewritten, and this dead code is eliminated and replaced with a guard. If the guard eventually encounters a need for the missed control branch, then the previous DSL AST 530 can be restored (e.g., regenerated or from the cache), or the missed branch can be copied from the previous DSL AST 530 and inserted into the defective current DSL AST 530.

[0111] When DSL AST 530 is ready, its bytecode and / or machine code can come from the cache or be (re)generated, either eagerly or lazily. Instead of waiting for code generation to complete, DBMS 500 can continue executing DSL AST 530 through direct interpretation, which can happen simultaneously with code generation in the background. That is, DBMS 500 can traverse and manipulate syntax nodes 541-542 to continue processing table rows.

[0112] Engineers might expect DSL AST interpretation to be slow. However, a DSL AST interpreter that can preferably evolve itself can accumulate many mutations (i.e., optimizations). For example, a safeguard might notice the expiration of one type of inference, but this doesn't need to invalidate most other type inferences. Through partial evaluation, the interpreter's reduction can be highly selective in scope.

[0113] Typically, for example, when speculative logic expires, most of the interpreter should remain unchanged. Therefore, optimizer stacks such as Truffle, Grail, and Substrate can invest heavily in optimizing the DSL AST interpreter, with the expectation that most of the investment will remain amortized. For example, the interpreter itself undergoes dynamic analysis, such as partial evaluation, specialization, and other (e.g., speculative) optimizations, as well as JIT compilation.

[0114] The DSL AST interpreter itself can be optimized based on dynamic profile data 560, which makes the interpreter more or less specialized for database queries 510, such as when each query has its own DSL AST interpreter instance, which can be independently optimized and discarded immediately after the query is completed. An example is caching the interpreter instance to prevent queries from being reused.

[0115] As explained earlier in this article, unlike general-purpose programming languages, DSLs typically have a narrow (i.e., special) purpose, more or less limited to a specific subject area, such as a specific technical problem. Because the scope of a DSL is narrower than that of a general-purpose language, the syntax of a DSL can be much smaller (i.e., simpler). Therefore, DSL tools, such as taggers, parsers, semantic analyzers, code generators, optimizers, runtime analyzers, and / or interpreters, can be greatly simplified compared to general-purpose tools.

[0116] Such lean tools should inherently have smaller codebases and simpler controls and data flow. Using the optimization techniques presented in this paper, such lean tools (as optimizable artifacts themselves) become even leaner, for example, when type inference excludes some of the DSL interpreter's own logic. The excluded interpreter logic is suitable for dead code elimination, which results in smaller and faster DSL interpreters, such as those via Futamura projection.

[0117] Futamura projection is a way to shrink and speed up the interpreter's codebase in memory during interpretation (i.e., interpreter operation). A naturally lean DSL interpreter, further optimized through Futamura projection, can save computer resources such as time and staging space. An optimized DSL interpreter can become so small that each query can have its own DSL interpreter instance, which is optimized independently for that query only.

[0118] Optimizable DSL interpreters can significantly accelerate self-organizing queries by amortizing the optimization overhead (i.e., latency) across database records (such as in database tables or intermediate result sets). Therefore, based on the techniques presented in this paper, optimizable DSL interpreters can be easily embedded into the query engine of a DBMS. However, state-of-the-art C / C++ database operations are more or less impossible to optimize in innovative ways (such as Futamura projection).

[0119] Interpreter specialization tailored to a specific query is an example of Futamura projection, where there are several asymptotic degrees (i.e., projections) along the spectrum of intensity, optimizing the interpreter from one end of the spectrum to the other, recasting the optimized interpreter into the compiler to optimize the compiler. Steps 804A-C implement at least the first degree of Futamura projection (i.e., interpreter optimization). Steps 804A and 804C show that the DSL AST interpreter can process some table rows before and after the interpreter undergoes Futamura projection in step 804B. The efficiency of Futamura projection can depend on large-scale iterations of table rows to amortize optimization overhead (e.g., initial latency).

[0120] 9.0 Example Implementation

[0121] This example implementation is based on Truffle, Graal, and Substrate as software layers, which work together as follows. In this example, the specific DSL is the Multilingual Engine Stub Language (MSL) for SQL expressions. The MSL AST is initially interpreted (i.e., directly manipulated).

[0122] When the execution count of an AST node reaches a predefined threshold, Truffle triggers partial evaluation by invoking the Graal compiler. The first Futamura projection is achieved by compiling the program represented by the AST tree and its interpreter. The output is highly optimized machine code with deoptimization points. These points are implemented as checkpoints, where deoptimization is triggered when speculative assumptions no longer hold. Deoptimization means transferring control from the compiled code back to the AST interpreter, where specialized nodes are reverted to more general versions.

[0123] Instead of interpreting MSL or generating code and statically compiling it like LLVM, MSL can be evaluated by fully leveraging the Truffle framework. The SQL standard defines the syntax for the SQL language and subsequently defines the syntax for SQL expressions. Therefore, SQL expressions can be described by a language. Consequently, SQL expressions can be implemented as Truffle languages, such as MSL.

[0124] By implementing SQL expressions as the Truffle language, the advantages of the Truffle framework and the Graal compiler can be fully utilized. Specifically, this includes:

[0125] The overall idea is to recognize SQL expressions as a language that can be "truffelized." The new language is the Multilingual Engine Stub Language (MSL).

[0126] • MSL generator: An algorithm for generating MSL code for a given expression tree;

[0127] • MSL parser: An algorithm for parsing MSL code and instantiating the corresponding MSL Truffle syntax nodes;

[0128] • How to embed the Truffle and Graal compiler framework into the DBMS architecture. Specifically, a new SQL expression node is introduced, which is responsible for storing MSL code during the query compilation phase and executing the MSL code during the query execution phase. The new SQL expression node replaces the root of the corresponding SQL expression tree.

[0129] The MSL generator translates a given SQL expression tree into MSL code. MSL code is generated during the query compilation phase against the given SQL expression tree. MSL code is stored in dedicated fields of the novel expression query nodes. During query execution, when the expression nodes are evaluated, the DBMS invokes the MSL parser to parse the MSL code to instantiate the corresponding MSLTruffle AST node.

[0130] At this point, the AST nodes can be evaluated. During SQL execution, runtime profiles that may lead to type specialization are collected. After a certain threshold, Graal triggers partial evaluation when the SQL expression is executed multiple times. During this step, the Graal compiler generates optimized machine code. Aggressive code inlining is an example of the optimization technique applied here. By inlining code, Truffle nodes are fused along the expression operand pipeline, which enforces data locality and eliminates call overhead.

[0131] By applying these strategies, the traditional interpretation overhead is eliminated. This paper's method leverages Truffle's self-optimizing features, such as type specialization based on collected runtime profiles or branch elimination. Just-in-time (JIT) compilation is deferred until Graal deems it worthwhile. At this point, Graal can utilize already performed code specializations and incorporate them into partial evaluation steps. A beneficial side effect of this is that, compared to LLVM methods, this approach avoids incurring high upfront compilation costs. However, those costs are merely deferred and incurred when Graal triggers a background compilation thread.

[0132] Specializing code relies on the correctness of the previously made specialization assumptions. However, if the characteristics of the input data change, these assumptions may no longer hold. Therefore, specialized code needs to be non-specialized. However, Graal does not abort the query but provides two alternatives: (1) it either reverts to a more general code path during interpretation, or, if already compiled, (2) Graal provides de-optimization. This adaptive approach ensures correctness and improved performance for those types of scenarios.

[0133] The proposed method consists of two phases: MSL plan generation and MSL plan execution. These phases are triggered by corresponding RDBMS phases: plan generation and plan execution. During RDBMS plan generation, an SQL expression tree is generated. At the end of SQL expression tree generation, the RDBMS checks whether an MSL plan can be generated for the corresponding SQL expression tree.

[0134] In some cases, RDBMS cannot generate an equivalent MSL plan. The most common reason is that an operation has not yet been implemented in the MSL. In those cases, RDBMS may take over by attaching to a sub-expression tree of an infeasible operation.

[0135] In most scenarios where an equivalent MSL plan is generated, the root node of the SQL (sub)expression tree is replaced by an SQL expression node. Starting from the root node, all SQL expression tree nodes that are descendants of the root node, except for the leaf nodes, are replaced with a single, identical SQL expression node. The leaves of the SQL expression tree are attached to the SQL expression node as direct children.

[0136] The generated MSL plan is stored in the SQL expression node. During the RDBMS execution phase when evaluating the SQL expression node, all its child nodes are evaluated first. Afterward, the MSL plan is resolved, instantiated, and executed by Graal.

[0137] Below is a sample SQL query. It evaluates a simple SQL expression that adds the number 1 to the empno column. This requires a simple SQL expression that adds empno+1.

[0138] SELECT empno+1 FROM emp;

[0139] The MSL plan used for that query is shown below in JSON format.

[0140]

[0141]

[0142]

[0143]

[0144] The following plan provides a better approach to the MSL just shown above. The following plan is in a human-readable format.

[0145]

[0146] By reviewing the plan above, we can identify several MSL elements of the MSL plan:

[0147] • STUB FUNCTION. The stub function has VARIABLES and BODY parts.

[0148] ·VARIABLES: The variable section declares local variables for the MSL program.

[0149] •BODY: The actual implementation of the MSL program can be found in its body. The body is as follows.

[0150] The first command block assigns the input column to column 0 of MLRIRVARIABLE. In this example, column 0 corresponds to the empno column in the SQL expression.

[0151] The second command block specializes the input. The example uses an Oracle Number implementation. Since OracleNumber is a variable-length data type of up to 22 bytes, computations can be very computationally intensive. The DBMS provides a decimal-scaled binary (DSB) specialization for OracleNumber, with performance comparable to integer specialization. If the underlying data of a given input can be represented by a DSB, then the DBMS can specialize the input to a DSB. This significantly improves performance. Regardless of whether the input is in a specialized form or represented as an Oracle Number, it is assigned to column 0, which is specialized to 0.

[0152] The third command block specifies how the result should be calculated. Here, OPTTAD represents the summation operation. The input is a column of 0s specialized to 0 and the constant 1. The result is assigned to the variable OPTTAD_cse_0_0.

[0153] The last command block de-specializes the result and stores it in the buffer of the result that the RDBMS expects to be in Oracle Number form.

[0154] This example implementation has an MSL code generator that produces the MSL code shown above.

[0155] SQL expression trees can have the following hierarchical structures.

[0156] oSQLEXPRESSION: Each SQL expression node is of type SQLEXPRESSION, but has specific subtypes:

[0157] `SQLEXPRESSIONNODE`: These are internal nodes of the SQL expression tree. Examples are binary operations such as SUM or MULTIPLICATION.

[0158] οSQLCOLUMNNODE: This is a leaf node of the SQL expression tree and represents the input of the SQL expression.

[0159] οSQLCONSTANTNODE: is a leaf node and represents a constant like "1" in the previous example.

[0160] The algorithm produces several MSL IR (Multi-language Engine Stub Language Intermediate Representation) elements. Here is its type hierarchy.

[0161] οMSLIRVARIABLE

[0162] οMSLIRCOLUMNVARIABLE

[0163] οMSLIRSPECIALIZEDVARIABLE

[0164] οMSLIEXPRESSION

[0165] οMSLIRREADEXPRESSION

[0166] οMSLIROPERATIONEXPRESSION

[0167] οMSLIRASSIGNVALUESTMT

[0168] οMSLIRCOLUMNTONUMBERSPECIALIZATIONEXPRESSION

[0169] οMSLIRCOLUNTODOUBLEEXPRESSION

[0170] οMSLILITERLEXPRESSION

[0171] MSL plans can be generated by calling COMPILESQLEXPRESSION, as shown below. Therefore, ANALYZESQLEXPRESSION (as shown below) is called to analyze the SQL expression tree from top to bottom. Different sub-methods are called depending on the type of the SQL expression node.

[0172] The `ANALYZESQLEXPRESSIONNODE` function (as shown below) checks if the exact same SQL expression node has already been analyzed. If so, the corresponding variable will be found in the `FRAMESLOTVARIABLEMAP`. If found, `MSLIRREADEXPRESSION` is returned. If not, the child nodes of the SQL expression node need to be analyzed before processing can continue. For each child node, `MSLIREXPRESSION` is expected as the return value. If the SQL expression node can consume specialized operands, each input should be specialized if it has not been specialized before. This is handled by calling `SPECIALIZE` (as shown below). The following is an example implementation of `COMPILESQLEXPRESSION`.

[0173]

[0174] The following is a sample implementation of ANALYZESQLEXPRESSION.

[0175]

[0176]

[0177] Once the input has been processed, an MSLIROPERATIONEXPRESSION is generated to encapsulate the actual operation by looking at GETOPERANDTYPE(). A call to COMMONSUBEXPRESSIONELEMINATION (as shown below) preserves the result by storing it in a variable so it can be reused when needed.

[0178] The following shows how to handle SQLCOLUMNNODE and SQLCONSTANTNODE. It is important to note that ANALYZESQLCOLUMNNODE calls REGISTERSQLEXPRESSIONARGUMENT, which registers the leaf node of the SQL expression tree so that it can later be attached as a direct child node to a new expression node via GENERATEEXPRESSIONOPERATOR.

[0179] Once the call to ANALYZESQLEXPRESSION returns, the MSL plan is generated by calling GENERATESTUBLANGUAGEFUNCTION (as follows). GENERATESTUBLANGUAGEFUNCTION is examining the MSLIRVARLIST and MSLIREXPLIST generated by ANALYZESQLEXPRESSION.

[0180] The GENERATEEXPRESSIONOPERATOR generates a new MSL expression node that replaces the RDBMS SQL expression tree. It registers the leaves of the SQL expression tree as its child nodes. As mentioned earlier, the leaves have already been collected by the REGISTERSQLEXPRESSIONARGUMENT. The new SQL expression node will store the MSL plan in JSON format, as shown above.

[0181] The following is an example implementation of ANALYZESQLEXPRESSIONNODE.

[0182]

[0183] The following is an example implementation of SPECIALIZE.

[0184]

[0185] The following is an example implementation of COMMONSUBEXPRESSIONELEMINATION.

[0186]

[0187] The following is an example implementation of ANALYZESQLCOLUMNNODE.

[0188]

[0189] The following is an example implementation of ANALYZESQLCONSTANTNODE.

[0190]

[0191] The following is an example implementation of GENERATESTUBLANGUAGEPLAN.

[0192]

[0193] 10.0 Database Overview

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

[0195] Generally speaking, a server, such as a database server, is a combination of integrated software components and the allocation of computing resources, such as memory, nodes, and processes on those nodes for executing the integrated software components. This combination of software and computing resources is dedicated to providing specific types of functionality on behalf of clients. A database server controls and facilitates access to a specific database, handling client requests to access the database.

[0196] Users interact with the DBMS database server by submitting commands to the database server, instructing it to perform operations on the data stored in the database. A user can be one or more applications running on the client computer interacting with the database server. Multiple users may also be collectively referred to as users in this document.

[0197] A database consists of data and a database dictionary, stored on a persistent storage mechanism such as a set of disks. Each database is defined by its own separate database dictionary. The database dictionary contains metadata that defines the database objects contained within the database. In fact, a database dictionary defines many databases. Database objects include tables, table columns, and tablespaces. A tablespace is a collection of one or more files used to store data for various types of database objects, such as tables. If data for database objects is stored in tablespaces, then the database dictionary maps the database objects to one or more tablespaces that hold the data for those database objects.

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

[0199] Database commands can take the form of database statements. For a database server to process database statements, those statements 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 within database structures. For example, SELECT, INSERT, UPDATE, and DELETE are common examples of DML instructions in some SQL implementations. SQL / XML is a common extension of SQL used when manipulating XML data in object-relational databases.

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

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

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

[0203] 10.1 Query Processing

[0204] A query is an expression, command, or set of commands that, when executed, causes a server to perform one or more operations on a dataset. A query can specify one or more source data objects from which to determine 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, 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 of query, including queries in the form of database statements and any data structure used for inner query representation. The term “table” refers to any source object that is referenced or defined by a query and represents a collection of rows (such as a database table, view, or inline query block, such as an inline view or subquery).

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

[0206] A subquery is a part or component of a query that is distinct from the other parts or components of the query and can be evaluated separately from the other parts or components (i.e., as a separate query). The other parts or components of the query can form an outer query, which may or may not include other subqueries. Subqueries nested within an outer query can be evaluated individually once or multiple times, while the results are computed for the outer query.

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

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

[0209] Hardware Overview

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

[0211] For example, Figure 9 This is a block diagram illustrating a computer system 900 on which embodiments of the present invention can be implemented. The computer system 900 includes a bus 902 or other communication mechanism for transmitting information, and a hardware processor 904 coupled to the bus 902 to process information. The hardware processor 904 may be, for example, a general-purpose microprocessor.

[0212] Computer system 900 also includes main memory 906, such as random access memory (RAM) or other dynamic storage devices, coupled to bus 902, for storing information and instructions to be executed by processor 904. Main memory 906 can also be used to store temporary variables or other intermediate information during the execution of instructions by processor 904. When stored in non-transient storage media accessible to processor 904, these instructions make computer system 900 a dedicated machine customized to perform the operations specified in the instructions.

[0213] The computer system 900 also includes a read-only memory (ROM) 908 or other static storage device coupled to a bus 902 for storing static information and instructions for the processor 904. A storage device 910 (such as a disk, optical disk, or solid-state drive) is provided and coupled to the bus 902 for storing information and instructions.

[0214] Computer system 900 can be coupled to display 912 (such as a cathode ray tube (CRT)) via bus 902 for displaying information to the computer user. Input device 914, including alphanumeric keys and other keys, is coupled to bus 902 for transmitting information and command selections to processor 904. Another type of user input device is cursor control 916 (such as a mouse, trackball, or arrow keys) for transmitting directional information and command selections to processor 904 and for controlling cursor movement on display 912. Such input devices typically have two degrees of freedom on two axes, a first axis (e.g., x) and a second axis (e.g., y), which allows the device to specify a position in a plane.

[0215] Computer system 900 may implement the techniques described herein using custom hardwired logic, one or more ASICs or FPGAs, firmware and / or program logic (which, in conjunction with the computer system, enable or program the computer system 900 as a special-purpose machine). According to one embodiment, computer system 900 performs the techniques described herein in response to processor 904 executing one or more sequences of one or more instructions contained in main memory 906. These instructions may be read into main memory 906 from another storage medium (such as storage device 910). Execution of the sequence of instructions contained in main memory 906 causes processor 904 to perform the processing steps described herein. In alternative embodiments, hardwired circuitry may be used instead of or in combination with software instructions.

[0216] As used herein, the term "storage medium" refers to any non-transient medium that stores data and / or instructions that enable a machine to operate in a particular manner. Such storage media can include non-volatile media and / or volatile media. Non-volatile media include, for example, optical discs, magnetic disks, or solid-state drives, such as storage device 910. Volatile media include dynamic memory, such as main memory 906. 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 media, CD-ROMs, any other optical data storage media, any physical media with a perforated pattern, RAM, PROMs and EPROMs, FLASH-EPROMs, NVRAMs, any other memory chips, or magnetic tape cassettes.

[0217] Storage media differ from transmission media but can be used in conjunction with them. Transmission media participate in the transfer of information between storage media. For example, transmission media include coaxial cables, copper wires, and optical fibers, including conductors containing bus 902. Transmission media can also take the form of sound waves or light waves, such as those generated during radio wave and infrared data communication.

[0218] Various forms of media can be used to transfer one or more sequences of one or more instructions to processor 904 for execution. For example, instructions may initially be carried on a disk or solid-state drive of a remote computer. The remote computer may load the instructions into its dynamic memory and transmit them over a telephone line using a modem. A modem local to computer system 900 may receive data over the telephone line and convert the data into an infrared signal using an infrared transmitter. An infrared detector may receive the data carried in the infrared signal, and appropriate circuitry may place the data on bus 902. Bus 902 transfers the data to main memory 906, from which processor 904 retrieves and executes the instructions. Instructions received by main memory 906 may optionally be stored on storage device 910 before or after execution by processor 904.

[0219] Computer system 900 also includes a communication interface 918 coupled to bus 902. Communication interface 918 provides bidirectional data communication coupled to network link 920, which is connected to local network 922. For example, communication interface 918 may be an Integrated Services Digital Network (ISDN) card, a cable modem, a satellite modem, or a modem providing data communication connectivity with a corresponding type of telephone line. As another example, communication interface 918 may be a Local Area Network (LAN) card to provide data communication connectivity with a compatible LAN. Wireless links may also be implemented. In any such implementation, communication interface 918 transmits and receives electrical, electromagnetic, or optical signals carrying streams of digital data representing various types of information.

[0220] Network link 920 typically provides data communication to other data devices via one or more networks. For example, network link 920 can provide a connection via local network 922 to host computer 924 or to data devices operated by Internet Service Provider (ISP) 926. ISP 926 then provides data communication services via a global packet data communication network (now commonly referred to as the "Internet" 928). Both local network 922 and Internet 928 use electrical, electromagnetic, or optical signals carrying digital data streams. Signals through various networks, as well as signals on network link 920 and through communication interface 918 (which carries digital data to and from computer system 900), are example forms of transmission media.

[0221] Computer system 900 can send messages and receive data, including program code, through one or more networks, network links 920, and communication interfaces 918. In the Internet example, server 930 can send requested code to the application through the Internet 928, ISP 926, local network 922, and communication interface 918.

[0222] The received code can be executed by processor 904 upon receipt and / or stored in storage device 910 or other non-volatile memory for later execution.

[0223] Software Overview

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

[0225] Software system 1000 is used to guide the operation of computing system 900. Software system 1000, which may be stored on system memory (RAM) 906 and fixed storage device (e.g., hard disk or flash memory) 910, includes a kernel or operating system (OS) 1010.

[0226] OS 1010 manages the low-level aspects of computer operations, including managing process execution, memory allocation, file input and output (I / O), and device I / O. One or more applications, designated 1002A, 1002B, 1002C...1002N, can be "loaded" (e.g., transferred from fixed storage device 910 to memory 906) for execution by system 1000. Applications or other software intended for use on computer system 900 can also be stored as downloadable computer-executable instruction sets, for example, for downloading and installing from internet locations (e.g., web servers, app stores, or other online services).

[0227] Software system 1000 includes a graphical user interface (GUI) 1015 for receiving user commands and data graphically (e.g., "click" or "touch gestures"). These inputs can then be manipulated by system 1000 according to instructions from operating system 1010 and / or (one or more) applications 1002. GUI 1015 also displays the results of operations from OS 1010 and (one or more) applications 1002, allowing the user to provide additional input or terminate the session (e.g., log off).

[0228] OS 1010 can execute directly on the bare hardware 1020 of computer system 900 (e.g., one or more processors 904). Alternatively, a hypervisor or virtual machine monitor (VMM) 1030 can be inserted between the bare hardware 1020 and OS 1010. In this configuration, VMM 1030 acts as a software “buffer” or virtualization layer between OS 1010 and the bare hardware 1020 of computer system 900.

[0229] VMM 1030 instantiates and runs one or more virtual machine instances (“guest machines”). Each guest machine includes a “guest” operating system (such as OS 1010) and one or more applications (such as application(s)1002) designed to run on the guest operating system. VMM 1030 presents a virtual operating platform to the guest operating system and manages the execution of the guest operating system.

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

[0231] In other cases, the guest operating system can be specifically designed or configured to run on the VMM 1030 for improved efficiency. In these instances, the guest operating system is "aware" that it is running on the virtual machine monitor. In other words, the VMM 1030 can provide paravirtualization to the guest operating system under certain circumstances.

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

[0233] cloud computing

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

[0235] Cloud computing environments (sometimes called cloud environments or the cloud itself) can be implemented in various 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, private cloud environments are generally used only by a single organization or within a single organization. Community clouds are designed to be shared by several organizations within a community; while hybrid clouds include two or more types of clouds (e.g., private, community, or public) bound together by data and application portability.

[0236] Generally, cloud computing models enable some of the responsibilities that might have previously been provided by an organization's own IT department to be delivered as service layers within the cloud environment for consumer use (depending on the public / private nature of the cloud, within or outside the organization). 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, while 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, while the PaaS provider manages or controls other aspects of the cloud environment (i.e., everything in the runtime execution environment); and Infrastructure as a Service (IaaS), where consumers can deploy and run arbitrary software applications and / or provide processes, storage devices, networks, and other basic computing resources, while the IaaS provider manages or controls the underlying physical cloud infrastructure (i.e., everything below the operating system layer). Database as a Service (DBaaS) is a service where consumers use database servers or database management systems running on cloud infrastructure, while the DBaaS provider manages or controls the underlying cloud infrastructure and applications.

[0237] The basic computer hardware and software, as well as the cloud computing environment, presented above are intended to illustrate the basic underlying computer components that can be used to implement one or more example embodiments. However, the one or more example embodiments are not necessarily limited to any particular computing environment or computing device configuration. Instead, according to this disclosure, the one or more example embodiments can be implemented in any type of system architecture or processing environment that will be understood by those skilled in the art to be capable of supporting the features and functionality of the one or more example embodiments presented herein.

[0238] In the foregoing description, embodiments of the invention have been described with reference to numerous specific details, which may vary from implementation to implementation. Therefore, the description and drawings should be considered illustrative rather than restrictive. The unique and exclusive indication of the scope of the invention, and the content that the applicant intends to define as the scope of the invention, is the literal and equivalent scope of the set of claims published from this application in the specific form of such claims, including any subsequent corrections.

Claims

1. A method comprising: Generate a query tree representing a database query, which contains expressions represented by subtrees of the query tree, wherein the expressions access multiple table columns in a table in the database; The subtree of the query tree is directly interpreted to generate the first part of the result for the expression based on the first subset of rows of the table; While generating the first part of the result, a sequence of domain-specific language (DSL) instructions representing the subtrees of the query tree is generated from the subtrees of the query tree; The sequence of DSL instructions executed during the execution of a database query to evaluate the expression includes: Initial compilation to generate a sequence of DSL instructions; The initial compilation of the sequence of DSL instructions is performed for the first time based on a second subset of the rows of the table to generate a second part of the result for the expression; Record dynamic profile data based on the first execution of the initial compilation; Optimized builds are generated based on dynamic profile data and the initial build. The optimized compilation is performed a second time based on a third subset of the rows of the table to generate a third part of the result for the expression; The result used in the expression includes a first part, a second part, and a third part of the result.

2. The method of claim 1, wherein the sequence of executing DSL instructions comprises: The sequence of DSL instructions is executed for the first time by the initial interpreter to generate the first and second parts of the result for the expression; The Futamura projection is applied to generate an optimized interpreter based on the sequence of DSL instructions and the initial interpreter; The optimized interpreter executes the sequence of DSL instructions a second time to generate a second portion of the result for the expression.

3. The method of claim 1, wherein the generation of optimized compilation includes optimizing the abstract syntax tree (AST) representing the sequence of DSL instructions based on dynamic profile data.

4. The method of claim 3, wherein the AST representing the sequence of DSL instructions is generated before the recording dynamic profile data.

5. The method of claim 3, wherein: An AST consists of multiple syntax nodes; The compilation based on dynamic profile data generation optimization includes generating a sequence of machine instructions that interweave the processing of multiple syntactic nodes among the multiple syntactic nodes.

6. The method of claim 3, wherein: The execution of the DSL instruction sequence also includes interpreting the AST representing the DSL instruction sequence to generate another part of the result for the expression based on another subset of the rows of the table; and The initial compilation of the execution DSL instruction sequence is in response to the execution count of the AST node reaching a predefined threshold.

7. The method of claim 1, wherein the compilation optimized based on dynamic profile data generation includes: Implement type specialization, Rewrite indirect function calls, Speculative inlining of functions Operate polymorphic inline cache, and / or Perform branch elimination.

8. The method of claim 7, wherein the implementation type specialization includes selecting a subtype of the general data type that consumes less space than the general data type for the general data type.

9. The method of claim 8, wherein: General data types represent numbers, and / or General data types have variable widths.

10. The method of claim 1, wherein: The compilation based on dynamic profile data generation optimization includes generating speculative logic; The second execution of the optimized compilation includes de-optimizing the optimized compilation.

11. The method of claim 1, wherein: The DSL is strongly typed; The sequence of generating DSL instructions includes generating a corresponding local variable declaration for each of the plurality of table columns, the corresponding local variable declaration having a corresponding data type in the DSL.

12. The method of claim 1, wherein: The sequence for generating DSL instructions includes generating tree nodes containing the sequence of DSL instructions; The generation of a query tree representing a database query includes replacing the subtrees of the query tree with the tree nodes.

13. The method of claim 12, wherein the tree node and the subtree of the query tree comprise the same plurality of leaf nodes.

14. The method of claim 1, wherein: The sequence of generating DSL instructions includes generating at least one type-specific instruction based on the data type of the database table columns in the plurality of table columns.

15. A non-transitory computer-readable medium storing one or more instructions, which, when executed by one or more processors, cause to perform the steps of the method as claimed in any one of claims 1-14.

Images