Looking for breakthrough ideas for innovation challenges? Try Patsnap Eureka!

System and method of query transformation

a query and transformation technology, applied in the field of data access middleware, can solve the problems of generating this type of sql more difficult, many database systems do not support this technology, and application servers cannot generally process queries as quickly

Inactive Publication Date: 2005-01-13
IBM CORP
View PDF34 Cites 17 Cited by
  • Summary
  • Abstract
  • Description
  • Claims
  • Application Information

AI Technical Summary

Problems solved by technology

An application server cannot generally process queries as quickly as a database server.
However, many database systems do not support this technology.
Quite often, generating this type of SQL is more difficult since it is more complex.
Unfortunately, this solution requires extensive processing time on the application server.

Method used

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
View more

Image

Smart Image Click on the blue labels to locate them in the text.
Viewing Examples
Smart Image
  • System and method of query transformation
  • System and method of query transformation
  • System and method of query transformation

Examples

Experimental program
Comparison scheme
Effect test

example 1

In this example, a nested OLAP function appears in the operand of another OLAP function, so the query must be transformed.

Original Query

SELECT MAX( SUM( QTY ) OVER ( PARTITION BY SNO ))OVER ( )FROM SUPPLY

Transformed Query

SELECT MAX(C0) OVER ( )FROM ( SELECT SUM( QTY ) OVER ( PARTITION BY SNO ) C0  FROM SUPPLY ) T1

Explanation

Against Oracle, the original query will result in an error since the OLAP function MAX contains a nested SUM function in its operand. To eliminate the nesting, a derived table T1 is created, and the SUM function is pushed into the select list.

example 2

In this example, a nested OLAP function appears in the operand of an OLAP function. As well, the same nested OLAP function appears on its own in the select list. This query must be transformed.

Original Query

SELECT SNO, PNO, SUM( QTY ) OVER ( PARTITION BY SNO ),  MAX( SUM( QTY ) OVER ( PARTITION BY SNO )) OVER ( )FROM SUPPLY

Transformed Query

SELECT C0, C1, C2, MAX( C2 ) OVER ( )FROM ( SELECT SNO C0, PNO C1,    SUM( QTY ) OVER ( PARTITION BY SNO ) C2  FROM SUPPLY ) T1

Explanation

Against Oracle, the original query will result in an error since the OLAP function MAX contains a nested SUM function in its operand. To eliminate the nesting, a derived table T1 is created, and the SUM function is pushed into the select list. Note also that a small optimization is performed with respect to the. first OLAP function (SUM) in the select list of the original query, since it is identical to the operand of the MAX function. The SUM function only needs to be computed once.

example 3

In this example, a nested OLAP function appears in the operand of an OLAP function. As well, the nested OLAP function itself contains a nested OLAP function in its PARTITION BY clause. This query must be transformed.

Original Query

SELECT SNO, PNO,  SUM( QTY ) OVER ( PARTITION BY    AVG( QTY ) OVER ( PARTITION BY JNO )),  MAX( SUM( QTY ) OVER ( PARTITION BY    AVG( QTY ) OVER ( PARTITION BY JNO ))) OVER ( )FROM SUPPLY

Transformed Query

Pass 1SELECT C0, C1, C2, MAX( C2 ) OVER ( )FROM ( SELECT C0, C1,    SUM( C2 ) OVER ( PARTITION BY      AVG( QTY ) OVER ( PARTITION BY JNO ) ) C2  FROM SUPPLY ) T0Pass 2SELECT C0, C1, C2, MAX( C2 ) OVER ( )FROM ( SELECT C0, C1, SUM( C2 ) OVER ( PARTITION BY C3 ) C2  FROM ( SELECT SNO C0, PNO C1, QTY C2,      AVG( QTY ) OVER ( PARTITION BY JNO ) C3    FROM SUPPLY ) T0 ) T1

Explanation

Against Oracle9i, the original query will result in an error since the OLAP functions SUM and MAX contain nested OLAP functions. This particular example requires 2 pa...

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to View More

PUM

No PUM Login to View More

Abstract

A query transformation system for transforming nested aggregates in a query is provided. The query transformation system comprises a nested aggregate analysis module for analysing a query that is not supported by a target database system, and a nested aggregate transformation module for transforming the query into a semantically equivalent query that is supported by the target database system.

Description

FIELD OF THE INVENTION The invention relates generally to data access middleware, and in particular to a system and method of query transformation. BACKGROUND OF THE INVENTION A typical data access environment has a multi-tier architecture. For description purposes, it can be separated into three distinct tiers: a Web server Applications Data The tiers are based on business function, and are typically separated by fireballs. Client software, such as a browser or a report-authoring tool, sits above the tiers. The web server contains a firewall and one or more gateways. All web communication is performed through a gateway. A gateway is responsible for passing on requests to the application server, in tier 2. for execution. The applications Tier contains one or more application servers. The application server runs requests, such as reports and queries that are forwarded by a gateway running on the web server. Typically, one of the components of the applications tier is a query ...

Claims

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to View More

Application Information

Patent Timeline
no application Login to View More
IPC IPC(8): G06F17/22G06F17/30
CPCG06F17/2247G06F17/30412G06F17/30471G06F17/30592Y10S707/954Y10S707/99935Y10S707/99933Y10S707/99934Y10S707/99931G06F16/24547G06F16/283G06F16/244
Inventor STYLES, MICHAEL E.
Owner IBM CORP
Who we serve
  • R&D Engineer
  • R&D Manager
  • IP Professional
Why Patsnap Eureka
  • Industry Leading Data Capabilities
  • Powerful AI technology
  • Patent DNA Extraction
Social media
Patsnap Eureka Blog
Learn More
PatSnap group products

Browse by: Latest US Patents, China's latest patents, Technical Efficacy Thesaurus, Application Domain, Technology Topic, Popular Technical Reports.

© 2024 PatSnap. All rights reserved.Legal|Privacy policy|Modern Slavery Act Transparency Statement|Sitemap|About US| Contact US: help@patsnap.com