Method, system and program for executing a query having a union operator

• US 7,539,667 B2
• Filed: 11/05/2004
• Issued: 05/26/2009
• Est. Priority Date: 11/05/2004
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: International Business Machines Corporation
• Inventors: Sharpe, David C., Sutyanyong, Nattavut, Neugebauer, Robert Paul, Zuzarte, Calisto Paul, Pirahesh, Mir Hamid, Lindsay, Bruce Gilbert, Liu, Linqi
• Primary Examiner(s): Breene; John E
• Assistant Examiner(s): Myint; Dennis
• Application Number:
  US10/982,441
• Publication Number:
  US 20060101011A1
• Time in Patent Office: 1,663 Days
• Field of Search: 707 2- 5
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24537 of operators
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

Disclosed is a data processing system implemented method, a data processing system and an article of manufacture for executing a query having a union operator. A data processing system implemented method direct the data processing system to execute a query against a database having data objects. The query has sub-queries and having a union operator. The union operator is operable on sub-queries associated with the query. The database is operatively coupled to the data processing system. The data processing system implemented method including grouping the sub-queries of the union operator according to identified structural similarities, the identified structural similarities being based on an analysis of the sub-queries, grouping the data objects of the database according to the grouped sub-queries, replacing the grouped data objects and any sub-queries associated with the grouped data objects with a reference to a representative data object and a representative sub-query, and accessing at least one member of the grouped data objects, the accessing of the at least one member of the grouped data object being based on the reference.

Claims

1. A data processing system implemented method of directing a data processing system to execute a query against a database having data objects, the query having sub-queries being subject of a union operator, the database being operatively coupled to the data processing system, the method comprising:

analyzing a sub-query of the union operator for structural uniformity and statistical uniformity with any previously analyzed sub-query;

generating groupings of the sub-queries being structurally and statistically uniform;

generating a parameterized sub-query for each of the groupings, the parameterized sub-query being one of the sub-queries of the grouping and including a parameter for each table or index object of the sub-query; and

generating access plans for each parameterized sub-query and for the sub-queries not being part of any of the groupings,

wherein the structural uniformity indicates that the structure of the sub-queries are identical,

wherein the structure of a sub-query includes expression of each sub-query, predicates of each sub-query, distribution of tables to multiple nodes and definition of underlying data objects including indexes, columns and data types of each column,

wherein the statistical uniformity indicates that the statistics of data objects of the sub-queries are similar based on a pre-determined performance criteria, and

wherein the statistics of a data object includes performance, size and data distribution of the data object.

2. A data processing system for executing a query against a database having data objects, the query having sub-queries operated upon by a union operator, the database being operatively coupled to the data processing system, the data processing system comprising:

a processor;

a usable memory medium;

an analyzer module for analyzing a sub-query of the union operator for structural uniformity and statistical uniformity with any previously analyzed sub-query;

a groupings module for generating groupings of the sub-queries being structurally and statistically uniform;

a generator module for generating a parameterized sub-query for each of the groupings, the parameterized sub-query being one of the sub-queries of the grouping and including a parameter for each table or index object of the sub-query; and

an access module for generating access plans for each parameterized sub-query and for the sub-queries not being part of any of the groupings,

wherein the structural uniformity indicates that the structure of the sub-queries are identical,

wherein the structure of a sub-query includes expression of each sub-query, predicates of each sub-query, distribution of tables to multiple nodes and definition of underlying data objects including indexes, columns and data types of each column,

wherein the statistical uniformity indicates that the statistics of data objects of the sub-queries are similar based on a pre-determined performance criteria, and

wherein the statistics of a data object includes performance, size and data distribution of the data object.

3. An article of manufacture for directing a data processing system to execute a query against a database having data objects, the query having sub-queries and having a union operator, the union operator operable on sub-queries associated with the query, the database being operatively coupled to the data processing system, the article of manufacture comprising:

a processor;

a usable memory medium for storing one or more instructions executable by the data processing system, the one or more instructions comprising;

analyzing a sub-query for structural uniformity and statistical uniformity with any previously analyzed sub-query;

generating groupings of the sub-queries being structurally and statistically uniform;

generating a parameterized sub-query for each of the groupings, the parameterized sub-query being one of the sub-queries of the grouping and including a parameter for each table or index object of the sub-query; and

generating access plans for each parameterized sub-query and for the sub-queries not being part of any of the groupings,

wherein the structural uniformity indicates that the structure of the sub-queries are identical,

wherein the structure of a sub-query of the union operator includes expression of each sub-query, predicates of each sub-query, distribution of tables to multiple nodes and definition of underlying data objects including indexes, columns and data types of each column,

wherein the statistical uniformity indicates that the statistics of data objects of the sub-queries are similar based on a pre-determined performance criteria, and

wherein the statistics of a data object includes performance, size and data distribution of the data object.