Teradata Us, Inc.

Teradata Corporation

Yu Xu

Olli Pekka Kostamaa

Yan Qi

Xin Zhou

Jian Wen

O Pekka Kostamaa

Pekka Kostamaa

Ambuj Shatdal

Ahmad Said Ghazal

Todd A Walter

Mark Sirek

Keliang Zhao

A system, method, and computer-readable medium for optimized processing of queries that feature maximum or minimum equality conditions are provided. The disclosed mechanisms provide for a single-scan of the table on which the group-by query is applied. When the table is scanned, each processing module dynamically keeps track of the row(s) having a value of the attribute on which the equality condition is applied that equals or exceeds the maximum attribute value (assuming a maximum equality condition is applied) previously encountered by the processing module. Subsequently, a global aggregation process is then performed to compute the query's result without rescanning the table. Queries featuring a minimum equality condition are similarly processed in accordance with the disclosed embodiments.

Optimizing processing of group-by queries featuring maximum or minimum equality conditions in a parallel processing system

Techniques that facilitate management of skewed data during a parallel multiple join operation are provided. Portions of tables involved in the join operation can be distributed among a plurality of processing modules, and each of the processing modules can be provided with a list of skewed values of a join column of a larger table involved in the join operation. Each of the processing modules can scan the rows of first and second tables distributed to the processing modules and compare values of the join columns of both tables with the list of skewed values. One or more of the processing modules can then redistribute the skewed values.

System, method and computer-readable medium for optimization of multiple-parallel join operations on skewed data

Techniques for data assignment from an external distributed file system (DFS) to a database management system (DBMS) are provided. Data blocks from the DFS are represented as first nodes and access module processors of the DBMS are represented as second nodes. A graph is produced with the first and second nodes. Assignments are made for the first nodes to the second nodes based on evaluation of the graph to integrate the DFS with the DBMS.

Techniques for data assignment from an external distributed file system to a database management system

Techniques for accessing a parallel database system via an external program using vertical and/or horizontal partitioning are provided. An external program to a database management system (DBMS) configures external mappers to process a specific portion of query results on specific access module processors of the DBMS that are to house query results. The query is submitted by the external program to the DBMS and the DBMS is directed to organize the query results in a vertical or horizontal manner. Each external mapper accesses its portion of the query results for processing in parallel on its designated AMP or set of AMPS to process the query results.

Techniques for accessing a parallel database system via external programs using vertical and/or horizontal partitioning

Techniques for guided access to an external distributed file system (DFS) from a database management system (DBMS) are provided. A user access a graphical user interface (GUI) to interactively define a search on an external DFS. The search and any filtering conditions are processed on the external DFS and results are imported to the DBMS and mapped to a table in the DBMS for manipulation and access by the user within the DBMS.

Techniques for guided access to an external distributed file system from a database management system

Techniques for external application-directed data partitioning in data exported from a parallel database management system (DBMS) are provided. An external application sends a query, a total number of requested access module processors (AMPs), and an application-defined data partitioning expression to the DBMS. The DBMS executes the query with the results vertical partitioned on the identified number of AMPs. Individual external mappers access their assigned AMPs asking for specific partitions that they are assigned to process the query results.

Techniques for external application-directed data partitioning in data exporting from a database management system

Techniques for fast loading data from an external distributed file system (DFS) to a database management system (DBMS) are provided. Assigning a portion of data from a file located in the DFS to each of a plurality of DFS clients. Configuring each DFS client to interact with a specific gateway from a plurality of gateways associated with the DBMS. Each gateway configured to send its portion of the file received from its designated client to every access module processor (AMP) controlled by that gateway. Each AMP processes a portion of the data received from the gateway to load the data into a table of the DBMS.

Techniques for fast loading of data from an external distributed file system to a database management system

Techniques for optimizing outer joins in database operations are provided. In an embodiment, a query having an outer join with a GROUP BY clause is rewritten and expanded to expand a first level partition of that GROUP BY clause to produce a modified query. In another situation, rows associated with processing a query are each row split and then hashed based at least in part on attributes of the outer join predicate. A left outer join is performed and a global aggregation processed to produce a spooled table to process the query.

Techniques for optimizing outer joins

A system, method, and computer-readable medium that facilitate counting the number of distinct values in several columns of a table utilizing parallel aggregation mechanisms.

System, method, and computer-readable medium that facilitates efficient processing of distinct counts on several columns in a parallel processing system

To process a sequence of outer joins in a database system, the database system performs a first outer join of the sequence of outer joins. A result of the first outer join is stored in a result table stored across plural storage modules of the database system. At least a subset of records of the result table is redistributed across the storage modules according to a first join attribute of the result table, where any record of the result table that has a null value for the first join attribute is not redistributed. A second outer join of the sequence is performed using the redistributed result table and another table, where the second outer join is based on the first join attribute of the result table.

Processing a sequence of outer joins

A small table S is outer joined to a large table L on a join condition on a database system with a plurality B of parallel units (PUs). S and L are partitioned across the PUs. Each row in S has a unique row-id. Each row of S is duplicated on all PUs to form S. On each PU, dangling rows in S that do not have a match in L under the join condition are identified and the row-ids of the dangling rows are saved in T. Tis partitioned across the PUs. P is formed from dangling rows of S whose corresponding entries in Tappear in all PUs. A result is produced by unioning P and I. I is formed by inner joining non-dangling rows of S with L. The result is saved.

Performing an outer join between a small table and a large table

In general, a technique or mechanism is provided to efficiently transfer data of a distributed file system to a parallel database management system using an algorithm that avoids or reduces sending of blocks of files across computer nodes on which the parallel database management system is implemented.

Assigning blocks of a file of a distributed file system to processing units of a parallel database management system

A system, method, and computer-readable medium for dynamic detection and management of data skew in parallel join operations are provided. Rows allocated to processing modules involved in a join operation are redistributed among the processing modules by a hash redistribution of the join attributes. Receipt by a processing module of an excessive number of redistributed rows having a skewed value on the join attribute is detected by a processing module which notifies other processing modules of the skewed value. Processing modules then terminate redistribution of rows having a join attribute value matching the skewed value and either store such rows locally or duplicate the rows. The processing module that has received an excessive number of redistributed rows removes rows having a skewed value of the join attribute from a redistribution spool allocated thereto and duplicates the rows to each of the processing modules. The join operation is completed by performing a local join at each processing module and merging the results of the local join operations.

Growing community of inventors

San Diego, CA, United States of America

Yu Xu