The present invention relates to optimized access of a database. Herein are techniques to accelerate execution of any combination of ad hoc query, heterogenous hardware, and fluctuating workload. In an embodiment, a computer receives a data access request for data tuples and compiles the data access request into relational operators. A particular implementation of a particular relational operator is dynamically selected from multiple interchangeable implementations. Each interchangeable implementation contains respective physical operators. A particular hardware operator for a particular physical operator is selected from multiple interchangeable hardware operators that include: a first hardware operator that executes on first processing hardware, and a second hardware operator that executes on second processing hardware that is functionally different from the first processing hardware. A response to the data access request is generated based on: the data tuples, the particular implementation of the particular relational operator, and the particular hardware operator.

Using Logical Partition Change Tracking (LPCT), a database system is able track the staleness of a materialized view at the level of logical partitions of a base database object, in addition to or instead of tracking the staleness of a materialized view at the level of physical partitions of the base database object. When the base database object is logically partitioned, it is possible using LPCT for the system to identify the records of the materialized view that correspond to changed logical partitions of the base database object. The records of the materialized view corresponding to the changed logical partitions become stale while other records of the materialized view corresponding to unchanged logical partitions remain fresh. The ability to identify which records of a materialized view are fresh and which are stale at the level of logical partitions of the base database object allows the system to rewrite user queries to use those records of the materialized view that are fresh.

Techniques are described herein for subquery removal given two set operation-based subqueries in a query, where one subquery contains the result of the other. The described optimization technique of subquery removal is enabled by join and set operation-based containment of the set operation-based subqueries where semantic equivalence can be established for a given pair of set operation-based subqueries when some table(s)—with associated join condition(s), correlation condition(s), and/or filter predicate(s)—in one subquery are not considered. Subquery removal reduces multiple access to the same table and multiple evaluations of the same join conditions required to evaluate the query. When a subquery is removed from a disjunction, this may lead to other optimizations such as subquery unnesting, e.g., when the original query configuration would not permit query unnesting and the rewritten query (with one or more removed subqueries) permits unnesting.

Described are methods, systems and computer readable media for performance logging of complex query operations.

The present approach assigns a code to each node class of a data tree modeling a database. The node class codes may be used to generate a node class path for each node class. This class path may be used as a discriminator to reference a given node class or portion of the tree including the class path and may be stored in a field of the database and/or cached. Use of the class path in query operations reduces the complexity of certain queries, thereby speeding up query performance.

According to an embodiment, a method includes rewriting a particular query to generate a rewritten query. The particular query specifies a window function operator, a particular input to the window function operator, and an analytical function. Rewriting the particular query includes assigning the particular input to an intermediate relation and replacing the window function operator with a replacement operator. The method further includes assigning to the replacement operator an aggregate function corresponding to the analytical function, and the intermediate relation. In this embodiment, the method also includes placing a join operator that joins the intermediate relation with an output of the replacement operator.

A computer program product provides efficient multiple aggregation distinct processing. The computer program product including a computer readable storage medium having program instructions embodied therewith. The program instructions are executable by a processor to cause the processor to convert a query with multiple aggregation distincts into a two-stage group-by process using a nest operator. The two-stage group-by process including further program instructions to cause the processor to: perform a first stage group-by process including the nest operator taking a single input row, and concatenating a grouping key with a measure value for each aggregation distinct that forms multiple inputs to a single group-by table, and perform a second stage group-by process including the nest operator bringing together entries for each original group.

A system and a method are disclosed for efficiently executing database queries using a computing device that includes a central processing unit (CPU) and a processing unit based on single instruction multiple thread (SIMT) architecture, for example, a GPU. A query engine determines a target processing unit to execute a database query based on factors including the type and amount of data processed by the query, the complexity of the query, and the current load on the processing units. An intermediate executable representation generator generates an intermediate executable representation for executing a query on a database virtual machine. If the query engine determines that the database query should be executed on an SIMT based processing unit, a native code generator generates native code from the intermediate executable representation. The native code is optimized for execution using a particular processing unit.

A system, method, and computer-readable medium for performing an auto-query construction operation for use with a distributed analytics operation. More specifically, in certain embodiments, the auto-query construction operation provides automatically generates SQL code instructions via an auto-query construction user interface (UI) settings in a computational system, such as the Dell Statistica computational system. The auto-query construction operation allows a user to interact with a common interface to provide query information including decision variables, parameters of an analysis and convergence criteria. The query information provided via the UI is automatically transformed to database queries and subsequent computation system operations. Thus, the user experience remains intact whether the analytics is performed in database or within the computation system.

The disclosure includes a system and method for generating a platform independent response to an input query. A query parsing application receives an input query from a user, generates an intermediate query token from the input query, converts the intermediate query token to a destination specific query, receives a response to the destination specific query, and formats the response to the destination specific query to generate a platform independent response to the input query.