Teradata Us, Inc.

Ncr Corporation

Olli Pekka Kostamaa

Yu Xu

Xin Zhou

Yan Qi

Jian Wen

Bhashyam Ramesh

Donald Raymond Pederson

Steven Bart Cohen

Grace K Au

John R Catozzi

Guilian Wang

Curtis Ellmann

Gary Allen Roberts

Bibhu Ranjan Priyadarshi

Ramesh Bhashyam

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

A database system may implement compression management of tables in the database system. The compression management may include determination of a pattern of usage of various database tables in the database system. Based on this pattern of usage, the database tables may be selected as candidates for compression or decompression at the appropriate time. In one example, the pattern of usage may be based on the contents of a query log of the database system. The compression management may also include evaluation of various compression strategies to apply to a candidate database table. Each compression strategy may be evaluated to determine if application to a database table or a portion of the database table would be beneficial based on various conditions. The compression management may also include consideration of each available compression strategy to be applied solely or in combination with one another.

Selective data compression in a database 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

A system, method, and computer-readable medium that facilitate dynamic skew avoidance are provided. The disclosed mechanisms advantageously do not require any statistic information regarding which values are skewed in a column on which a query is applied. Query selectivity is evaluated at a check point and thereby facilitates accurate detection of an overloaded processing module. The successful detection of an overloaded processing module causes other processing modules to stop sending more skewed rows to the overloaded processing module. Detection of an overloaded processing module is made when the overloaded processing module has received more rows than a target number of rows. Further, skewed rows that are maintained locally rather than redistributed to a detected processing module may result in more processing modules becoming overloaded. Advantageously, the disclosed mechanisms provide for a final redistribution adjustment to provide for even distribution of rows among all processing modules.

System, method and computer-readable medium for dynamic skew avoidance for generic queries

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

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.

System, method, and computer-readable medium for dynamic detection and management of data skew in parallel join operations

A system, method, and computer-readable medium for optimization of query processing in a parallel processing system are provided. Skewed values and non-skewed values are treated differently to improve upon conventional DISTINCT and aggregation query processing. Skewed attribute values on which a DISTINCT selection or group by aggregation is applied are allocated entries in a hash table. In this manner, a processing module may consult the hash table to determine if a skewed attribute value has been encountered during the query processing in a manner that precludes repetitive redistribution of rows with highly skewed attribute values on which a DISTINCT selection or group by aggregation is applied.

System, method, and computer-readable medium for optimizing processing of distinct and aggregation queries on skewed data in a database system

A system, method, and computer-readable medium for optimized processing of queries that feature maximum or minimum equality conditions are provided. A table on which the query is applied is scanned a single time. Rows of the table distributed to respective processing modules are scanned by the processing modules. Each processing module maintains identification of any rows distributed to the respective processing module that have attribute values that equal the maximum or minimum attribute value locally identified by the processing module. Subsequently, a global aggregation mechanism is invoked to compute the query result without requiring an additional rescan of the table. Further, the disclosed mechanisms may be extended to compute top N queries featuring maximum or minimum equality conditions.

System, method, and computer-readable medium for optimizing processing of queries featuring maximum or minimum equality conditions in a parallel processing system

A system, method, and computer-readable medium for optimizing execution of a join operation in a parallel processing system are provided. A plurality of processing nodes that have at least one row of one or more tables involved in a join operation are identified. For each of the processing nodes, respective counts of rows that would be redistributed to each of the processing nodes based on join attributes of the rows are determined. A redistribution matrix is calculated from the counts of rows of each of the processing nodes. An optimized redistribution matrix is generated from the redistribution matrix, wherein the optimized redistribution matrix provides a minimization of rows to be redistributed among the nodes to execute the join operation.

System, method, and computer-readable medium for reducing row redistribution costs for parallel join operations

A system, method, and computer-readable medium for optimizing the performance of outer joins in a parallel processing system are provided. Predicates involving only attributes of a left table of a left outer join are pushed down to the outer relation for left outer joins having join predicates involving left table attributes and/or predicates involving attributes of both the right and left table. In such an instance, the rows of the left table may be partitioned into two sub-relations according to the predicate involving only attributes of the left table. Rows of the left table are allocated to a first sub-relation if the rows satisfy the predicate involving only attributes of the left table and rows of the left table are allocated to a second sub-relation if the rows fail to satisfy the predicate involving only attributes of the left table. Accordingly, only rows of the first sub-relation are required to be left outer joined with the right table. Advantageously, a reduction in the requisite number of rows to be redistributed and joined is facilitated. The disclosed embodiments may be similarly applied for optimization of right outer joins. Further, embodiments for optimizing full outer joins are disclosed.

System, method, and computer-readable medium for optimizing the performance of outer joins

A system, method, and computer-readable medium for optimizing join operations in a parallel processing system are provided. A respective set of rows of a first table and a second table involved in a join operation are distributed to each of a plurality of processing modules. The join operation comprises a join on a first column of the first table and a second column of the second table. Each of the plurality of processing modules redistributes at least a portion of the rows of the first table distributed thereto substantially equally among the other processing modules and duplicates at least a portion of the rows of the second table distributed thereto among the plurality of processing modules. The disclosed optimization mechanisms provide for reduced spool space requirements for execution of the parallel join operation.

System, method, and computer-readable medium for duplication optimization for parallel join operations on similarly large skewed tables

The invention relates to a method of determining the performance of an optimizer program arranged to generate a plan. The method generally comprises the steps of executing the optimizer utilising at least one test case to derive a pre-variation plan; executing the pre-variation plan to generate a first output; varying a strategy of the optimizer program; executing the varied optimizer program utilising the at least one test case to derive a post-variation plan; and executing the post-variation plan to generate a second output. The outputs are then grouped according to at least one element of the plans to assess the performance of the optimizer.

Growing community of inventors

Santa Monica, CA, United States of America

Olli Pekka Kostamaa