Shared nothing parallel execution of procedural constructs in sql

Abstract

An automated methodology, and an apparatus for practicing the methodology, which enables the power and flexibility inherent in shared nothing parallel database systems (MPP) to be utilized on complex queries which have, heretofore, contained query elements requiring local computation or local coordination of data computation performed across the nodes of the distributed system. The present invention provides these features and advantages by identifying and marking the subgraphs containing these types of query elements as 'no TQ zones' in the preparation phase prior to optimization. When the optimizer sees the markings, it builds a plan that will force the computation of the marked subgraphs to be in the same section. This preparation phase also provides the partitioning information for all inputs to the 'no TQ zones'. This allows the bottom-up optimizer to correctly plan the partitioning for the 'no TQ zones'. These partitionings can force the operation to a single-node, the coordinator node, the catalog node, or to a particular partition class on multiple nodes, or nodegroups.