Abstract: Query evaluation is optimized using parallel optimization techniques to make repartitioning more efficient. Efficiency is improved by recognizing the possible partitioning requirements for achieving parallelism for a query operation, and by recognizing when the partitioning property of data satisfies the partitioning requirements of a query operation. A data base management system in accordance with the invention uses parallel query processing techniques to optimize data repartitioning, or to avoid it altogether.

Abstract: A relational data base management system includes a query processor that uses a query operator partition property to perform QEP pruning and to ensure that data input to a query operator is partitioned appropriately for the operation. The partition property indicates the group of network nodes across which a table is distributed. The query processor also makes use of partition classes that are designated "interesting classes" to perform preoptimization planning and query pruning, and to perform look-ahead partitioning based on partition classes that are identified as being of interest to future operations, thereby more efficiently evaluating complex query statements in an MPP, shared-nothing environment.

Abstract: A database system and method provide support for a positioned data set modification operation on a collection of data sets involved in a data set query operation, in which the data set modification operation is based on the position of a query execution iterator. At query compile time, the database system generates an iterator position retrieval function and a data set modification function for each data set involved in the query operation. At query execution time, the database system invokes each of the iterator retrieval functions until the iterator position retrieval function for a current open data set returns the current the position of the query execution iterator on that data set. The database system then invokes each of the data set modification functions until the data set modification function for the current open data set performs a data set modification operation at the current position of the query execution iterator specified by the iterator position retrieval function.

Abstract: A database system and method provide support for a positioned data set modification operation on a collection of data sets involved in a data set query operation, in which the data set modification operation is based on the position of a query execution iterator. At query compile time, the database system generates an iterator position retrieval function and a data set modification function for each data set involved in the query operation. At query execution time, the database system invokes each of the iterator retrieval functions until the iterator position retrieval function for a current open data set returns the current the position of the query execution iterator on that data set. The database system then invokes each of the data set modification functions until the data set modification function for the current open data set performs a data set modification operation at the current position of the query execution iterator specified by the iterator position retrieval function.

Abstract: A procedure for detecting a reordering requirement in a directed record stream during query execution in a relational database processing system. The query compiler component of a relational database processing system includes procedures for building query execution plans (QEPs) for evaluation preparatory to selecting an optimal plan for execution. These plans are constructed from the bottom up using an internal graphical representation for the user query that has a number of relation nodes interconnected by directed record streams (data flows). A relational operation within each node imposes an "order requirement" on the outflow stream represented by an order requirement vector O.sub.R. The records within each directed record stream have an "order property" represented by an order property vector O.sub.P. Order detection occurs when these two vectors are compared to determine whether the order property satisfies the order requirement.

Abstract: The present invention optimizes SQL queries by exploiting uniqueness properties. In identifying whether the generalized 1-tuple condition exists, the query is first analyzed to determine whether any columns referenced in a predicate of the query are bound. According to the present invention, columns may be bound to constant values or correlated columns or columns that are already bound. The bound columns, if any, are then analyzed to determine whether any of the bound columns comprise a key for its associated table. If these conditions exist, then the query satisfies the 1-tuple condition, in that it returns at most one tuple. Once the generalized 1-tuple condition has been identified to exist for the query, important query transformations can be performed for optimization purposes. These query transformations comprise the transformation of scalar subqueries into joins, or the elimination of distinctiveness requirements (i.e., DISTINCT keywords) from SELECT clauses.

Abstract: A method and apparatus for optimizing SQL queries by propagating and exploiting column nullability. Column nullability is identified and propagated using a three-valued logic, wherein a column of a table can be identified nullability information is exploited to optimize query operations through transformations. In one aspect of the present invention, quantified predicates (such as ">ALL") are transformed into simple predicates involving singleton subqueries so that indexing can be exploited. In another aspect of the present invention, "is not null" predicates are generated and pushed for certain aggregate queries. In still another aspect of the present invention, intersect operations are transformed into joins. The end result is that the present invention can significantly enhance the performance of the queries.

Abstract: A method and apparatus for optimizing SQL queries in a relational database management system uses early-out join transformations. An early-out join comprises a many-to-one existential join, wherein the join scans an inner table for a match for each row of the outer table and terminates the scan for each row of the outer table when a single match is found in the inner table. To transform a many-to-many join to an early-out join, the query must include a requirement for distinctiveness, either explicitly or implicitly, in one or more result columns for the join operation. Distinctiveness can be specified using the DISTINCT keyword in the SELECT clause or can be implied from the predicates present in the query. The early-out join transformation also requires that no columns of the inner table be referenced after the join, or if an inner table column is referenced after the join, that each referenced column be "bound".

Abstract: A method and apparatus for optimizing SQL queries in a relational database management system uses early-out join transformations. An early-out join comprises a many-to-one existential join, wherein the join scans an inner table for a match for each row of the outer table and terminates the scan for each row of the outer table when a single match is found in the inner table. To transform a many-to-many join to an early-out join, the query must include a requirement for distinctiveness, either explicitly or implicitly, in one or more result columns for the join operation. Distinctiveness can be specified using the DISTINCT keyword in the SELECT clause or can be implied from the predicates present in the query. The early-out join transformation also requires that no columns of the inner table be referenced after the join, or if an inner table column is referenced after the join, that each referenced column be "bound".