Teradata Us, Inc.

Hewlett-packard Development Company, L.p.

Compaq Computer Corporation, Inc.

Robert Matthew Wehrmeister

Awny Kayed Al-Omari

Kashif Abdullah Siddiqui

Mark E Melton

Peter N Lohman

Hansjorg Zeller

Mohammed Yassin Al-Kateb

Mahbub Hasan

Tom C Reyes

B Anantha Subramanian

Mohamed Yassin Eltabakh

Sanjay Nair

David W Birdsall

QiFan Chen

James Shau

Anoop Sharma

Michael James Hanlon

Gary Martin Gilbert

Ahmed Kamal Ezzat

Subbarao Kakarlamudi

Zbigniew Omanski

Xin Tang

Peter Friedenbach

Daniel T Yu

Thomas Canivel Reyes

Christina Pavlopoulou

A database system receives a request from a user. The request invokes a data set function (DSF) and uses a property to be provided by the DSF. The database system determines that a function descriptor is available for the DSF. The function descriptor is expressed as markup language instructions. The function descriptor defines the property of the DSF. The database system uses the function descriptor to define a property for the DSF.

Enabling cross-platform query optimization via expressive markup language

Execution of a query invoking an analytical function (AF) is optimized. The query includes a join operation between an AF table and an AuxiliaryTable. A determination is made that the AF includes a plurality of AF properties. Query-level properties about the query are inferred. A determination is made to change an order of the join operation from the plurality of AF properties and query-level properties.

Optimizing the execution order between analytical functions and joins in SQL queries

Disclosed are systems and methods for implementing contract-based polymorphic and parallelizable user-defined scalar and aggregate functions. The systems and methods can include receiving a query including a plurality of user-defined functions, parsing the query into a plurality of nodes (e.g., basic operation unit or atomic operator), generating an execution plan that minimizes data transfer between the plurality of nodes, and executing the plan in a distributed environment. Each of the plurality of user-defined functions can correspond to one of a plurality of nodes.

Implementing contract-based polymorphic and parallelizable SQL user-defined scalar and aggregate functions

The execution of the one or more database queries can be optimized by using a multi-platform cost model that take into account the respective cost of execution in each one of the multiple execution platforms. The respective costs of the execution can, for example, include cost associated with one or more operators for executing the one or more database queries in each one of the multiple execution platforms and/or the costs of exchanging data between the multiple execution platforms.

Multi-platform optimization

As an abstract representation, a set of equivalent logical structures representative of multiple execution plans for execution of a database query can be used to optimize a database query. A logical structure can include one or more logical operators each representing multiple physical operators for executing the database query. Group and Operator Rules can be applied as rules to the set of equivalent logical structures to obtain additional equivalent logical structures and logical operator until no additional logical operators can be obtained. A set of possible implementation plans for the total number of the obtained logical operators can be obtained, for example, based on physical and/or implementation context. An optimization request can be effectively propagated through an implantation plan in a top-down manner, for example, recursively for each child of physical operators, where only new contexts are optimized, in order to generate an optimized structure, for example, in consideration of, implementation details, costs, physical properties, etc. One of the optimized structures can be selected as an optimal plan.

Optimization of database queries for database systems and environments

An enterprise data warehouse or business intelligence system having a plurality of processors and memory resources. The system includes at least a database server, a workload management system, a query compiler, and a query executor. The database server configured to receive a database query from an application at a database client system. The query compiler configured to prepare the execution plan for the query and compute the number of executive server processes (ESPs) in each ESP layer of the query. The workload management system is configured to generate an affinity value, and the query executor is configured to execute the query. As disclosed herein, placement of the executive server process layers of the query onto processors of the computing system is determined using the affinity value. Other embodiments, aspects and features are also disclosed.

Allocation of resources for concurrent query execution via adaptive segmentation

Optimized query plans are partitioned into equivalence groups that each include equivalence classes. Each equivalence group corresponds to a particular compiled, normalized, and parameterized query plan prior to optimization. Each equivalence class within an equivalence group corresponds to a different query plan corresponding to the particular compiled, normalized, and parameterized query plan represented by the equivalence group that has been optimized with respect to the selectivity of one or more predicate clauses of the query that is compiled to produce the particular compiled, normalized, and parameterized query plan. Optimized query plans are cached according to their respective equivalence groups and equivalence classes. When a query, similar to a query already compiled, optimized, and cached, is subsequently received and compiled, a selectivity for a predicate of the compiled query is computed, allowing the database management system to retrieve a cached query plan optimized for a similar query with similar selectivity.

Selectivity-based optimized-query-plan caching

Systems and methods to indicate whether to store a database query language module in a local directory or a global directory are disclosed. An application program can be created once, tested once, and deployed in separate environments. The database query language modules can be shared among a group of application programs running in a common environment. Separate query language modules can be maintained for each environment.

Systems and methods for handling multiple static query modules for distinct environments

In one embodiment, a computer system comprises one or more processors, and a memory module communicatively connected to the one or more processors. The memory module comprises logic instructions stored on a computer readable medium which, when executed on the one or more processors configure the one or more processors to receive, in a hybrid caching module, database query, determine whether at least a portion of an existing execution plan stored in a memory module coupled to the hybrid caching module can be reused to implement the database query, reuse at least a portion of an existing execution plan stored in the memory module coupled to the hybrid caching module when possible, and generate a new execution plan when the memory module lacks an existing execution plan adaptable for use with the database query.

Hybrid database query caching

A database query compiler and compilation method has special facilities for compiling a query that includes one or more of a predefined set of running and moving sequence functions. The compiler converts the query into a predefined normalized form suitable for compilation using a running and moving function normalizer. The running and moving function normalizer converts each running and moving sequence function in the set into a corresponding ordered set of one or more executable statements, which include at least one Offset sequence function that accesses data in an auxiliary field of a row of a table. An offset sequence function compiler, compiles each Offset sequence function, Offset(argument, index), in the normalized database query into a compiled set of instructions, including instructions for storing and reading the auxiliary fields to and from a buffer that is separate from the table. The buffer is preferably stored in volatile, main memory. As a result, when the Offset sequence function is executed, information from a previous is accessed without having to change the cursor position for the table.

System and method for computing running and moving sequence functions in a database system

A database query compiler and compilation method has special facilities for compiling a query that includes an Offset sequence function, Offset(argument, index). During execution of the query, while the cursor for a table is pointing to a current row, the Offset function is used to access information from a previously accessed row. The argument of the Offset sequence function is a specified function of information associated with the previously accessed row of the table. The previously accessed row has a position that is index rows before the current row referenced by the cursor for the table. During compilation, the argument of the Offset sequence function is parsed to determine a set of auxiliary fields for each row of the table. Each auxiliary field of a row contains information that may be accessed during execution of the Offset sequence function while the cursor for that table is pointing to a subsequent row. The Offset sequence function is converted into a compiled set of instructions, including instructions for storing and reading the auxiliary fields to and from a buffer that is separate from the table. The buffer is preferably stored in volatile, main memory. As a result, the Offset sequence function is executed, and information from a previous row is accessed, without having to change the cursor position for the table.

System and method for computing an offset sequence function in a database system

A database query compiler and compilation method has special facilities for compiling a query that includes a Rows Since sequence function, Rows Since (search condition). A query normalizer includes a Rows Since function normalizer for normalizing the Rows Since sequence function. The Rows Since function normalizer parses the search condition of the Rows Since function to identify each reference to information from a previously access row and converts each such identified reference into an Offset sequence function, Offset(argument, index). The argument of the Offset sequence function is a specified function of the identified information from a previously accessed row of the table. The previously accessed row has a position that is index rows before the current row referenced by the cursor for the table. The query compiler places the resulting normalized search condition in a search loop that traverses successively earlier rows in the table until it finds a row that satisfies the search condition. An offset sequence function compiler, compiles each Offset sequence function, Offset(argument, index), in the normalized database query into a compiled set of instructions, including instructions for storing and reading the auxiliary fields to and from a buffer that is separate from the table. The buffer is preferably stored in volatile, main memory. As a result, when the Offset sequence function is executed, information from a previous is accessed without having to change the cursor position for the table.

Growing community of inventors

Austin, TX, United States of America

Robert Matthew Wehrmeister