Shared nothing parallel execution of procedural constructs in SQL

US6081801A · kind A · utility

Assignee

• International Business Machines Corporation · US

Inventors

• Roberta Jo Cochrane · Los Gatos, US
• George Lapis · San Jose, US
• Mir Hamid Pirahesh · San Jose, US
• Richard S. Sidle · Ottawa, CA
• David E. Simmen · Mountain View, US
• Tuong Chanh Truong · San Jose, US
• Monica Sachiye Urata · Saratoga, US

Key dates

• Filing date: Jun 30, 1997
• Grant date: Jun 27, 2000
• Expiry date: Jun 30, 2017

Classification

• Technology area (CPC Y): Emerging Cross-Sectional Technologies
• CPC primary: Y10S707/99952
• WIPO field: Computer technology
• WIPO sector: Electrical engineering

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.