System and method of query transformation

• US 20050038778A1
• Filed: 05/27/2004
• Published: 02/17/2005
• Est. Priority Date: 05/27/2003
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: International Business Machines Corporation
• Inventors: Styles, Michael E.
• Granted Patent: US 7,428,532 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/244 Grouping and aggregation
• CPC Class Codes: G06F 16/24547 Optimisations to support specific applications; Extensibility of optimisers
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/954 Relational
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

Abstract

A system for transforming client/server aggregates is provided. The system comprises a client/server analysis component for analyzing one or more queries that are not supported by a target database system, and a client/server transformation component for transforming the one or more queries into semantically equivalent queries that are supported by the target database system.

Claims

1. A system for transforming client/server aggregates, the system comprising:

a client/server analysis component for analyzing a query containing a client/server aggregate that are not supported by a target database system; and

a client/server transformation component for transforming the query into a semantically equivalent query that is supported by the target database system.

2. A method of client/server aggregate transformation, the method comprising the steps of:

analyzing a query having a client/server aggregate that is not supported by a target database system; and

transforming the query into a semantically equivalent query that is supported by the target database system.

3. The method as claimed in claim 2, further comprising the steps of:

determining if a transformation is to be performed;

traversing all expressions in an original select list;

copying expressions contained in G to an inner select list, and traversing the expression otherwise, applying the rules outlined here;

applying one of the following transformations if m>

1 or k≈

n;

replacing an aggregate of the form AVG (C₀) OVER

with an expression of the form SUM (SUM (C₀)) OVER

/SUM (COUNT (C₀)) OVER and

moving the standard (nested) aggregates into the inner select list (43); and

replacing an aggregate of the form AVG (C₀) OVER (PARTITION BY C₁, C₂, . . . , C_k) with an expression of the form SUM (SUM (C₀) OVER (PARTITION BY C₁, C₂, . . . , C_k)) OVER

/SUM (COUNT (C₀) OVER (PARTITION BY C₁, C₂, . . . , C_k)) OVER and

moving the standard (nested) aggregates into the inner select list (44); and

constructing the final query.

4. The method as claimed in claim 3, wherein the step of determining if a transformation should be performed comprises the steps of:

assigning a group index to all aggregates;

keeping track of control breaks; and

determining whether certain criteria are met.

5. The method as claimed in claim 3, wherein the step of applying a client/server transformation comprises the step of replacing AVG (C₀) OVER

with an expression of the form SUM (SUM (C₀)) OVER

/SUM (COUNT (C₀)) OVER and

moving the standard (nested) aggregates into the inner select list.

6. The method as claimed in claim 3, wherein the step of applying a client/server transformation comprises the step of replacing AVG (C₀) OVER (PARTITION BY C₁, C₂, . . . , C_k) with an expression of the form SUM (SUM (C₀)) OVER (PARTITION BY C₁, C₂, . . . , C_k)/SUM (COUNT (C₀)) OVER (PARTITION BY C₁, C₂, . . . , C_k) and moving the standard (nested) aggregates into the inner select list.

7. A method of client/server aggregate transformation, the method comprising the steps of:

analyzing a client/server select list to determine if a transformation is to be performed;

generating a derived table;

traversing all expressions in the main select list;

adding columns or expressions that are grouping columns to the derived table select list; and

converting and moving eligible aggregates into the derived table select list.

8. A computer data signal embodied in a carrier wave and representing sequences of instructions which, when executed by a processor, cause the processor to perform a method of client/server aggregate transformation, the method comprising the steps of:

analyzing one or more queries that are not supported by a target database system; and

transforming the one or more queries into semantically equivalent queries that are supported by the target database system.

9. A computer-readable medium having computer readable code embodied therein for use in the execution in a computer of a method of client/server aggregate transformation, the method comprising the steps of:

analyzing one or more queries that are not supported by a target database system; and

transforming the one or more queries into semantically equivalent queries that are supported by the target database system.

10. A computer program product for use in the execution in a computer of a query transformation system for transforming client/server aggregates, the computer program product comprising:

a client/server analysis component for analyzing one or more queries that are not supported by a target database system; and

a client/server transformation component for transforming the one or more queries into semantically equivalent queries that are supported by the target database system.