CA2468398C - System and method of query transformation - Google Patents
System and method of query transformation Download PDFInfo
- Publication number
- CA2468398C CA2468398C CA002468398A CA2468398A CA2468398C CA 2468398 C CA2468398 C CA 2468398C CA 002468398 A CA002468398 A CA 002468398A CA 2468398 A CA2468398 A CA 2468398A CA 2468398 C CA2468398 C CA 2468398C
- Authority
- CA
- Canada
- Prior art keywords
- group
- query
- sql
- aggregates
- transformation
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Expired - Fee Related
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
- G06F16/24537—Query rewriting; Transformation of operators
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
- G06F16/244—Grouping and aggregation
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/28—Databases characterised by their database models, e.g. relational or object models
- G06F16/283—Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
Abstract
A group query transformation system for transforming structured query langua ge group query windowed aggregates is provided. The group query transformation system comprises a group query analysis module for analyzing one or more structured query language (SQL)/ on-line analytical programming (OLAP) windowed aggregates th at are not supported by a target database system, and a group query transformation module for transforming the one or more SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system.
Description
System and Method of Quern Transformation 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 mufti-tier architecture. For description purposes, it can be separated into three distinct tiers:
~ Web server ~ Applications ~ Data The tiers are based on business function, and are typically separated by firewalls. 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 engine, which is data access middleware that provides universal data access to a variety of heterogeneous database systems. The query engine formulates queries (typically SQL) and passes them on to the data tier, through a native database API (such as ODBC) for execution.
The data tier contains database management systems (DBMS), which manage raw data stored in a database. Examples of such systems include Oracle, DB2, and Microsoft SQL Server.
Although a mufti-tier architecture can be configured in several different ways, a typical configuration places each tier on a separate computer (server). A
database server is typically a "high end" server, and thus can process queries at a relatively fast speed.
An application server cannot generally process queries as quickly as a database server.
In order to solve many business questions, a query engine may generate SQL
queries that utilize the SQL/OLAP technology introduced in the SQL-99 standard.
However, many database systems do not support this technology. Thus, the SQL
queries would have to be performed on the report server that is generally slower than the database server. It is desirable to have as much processing performed on the database server.
There is a need to prevent or reduce the amount of local (application server) processing required to process a query. In the past, the application would be responsible for generating separate SQL queries involving the GROUP BY operator to compute aggregates over different partitions and stitching together the results. Quite often, this is quite difficult since it involves multiple queries and post processing.
One way of overcoming this problem is for the query engine to generate separate GROUP BY queries for aggregates computed over different partitions, generate a separate query to retrieve detail information, and then stitch together the results to produce the desired report. Unfortunately, this problem requires processing time on the report server. It is desirable to have a way of transferring the SQL queries to the database server with minimal processing on the report server.
SUMMARY OF THE INVENTION
It is an object of the present invention to provide a method of structured query language group query transformation in a database system that does not support standard.
ZO In accordance with an embodiment of the present invention, there is provided a group query transformation system for transforming structured query language group query windowed aggregates. The group query transformation system comprises a group query analysis module for analyzing one or more structured query language (SQL) / on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system, and a group query transformation module for transforming the one or more SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system.
In accordance with another embodiment of the present invention, there is provided a method of transforming structured query language (SC~L) group query windowed aggregates. The method comprises the steps of analyzing one or more SQL/ on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system, and transforming the one or more SQL/OLAP windowed aggregates
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 mufti-tier architecture. For description purposes, it can be separated into three distinct tiers:
~ Web server ~ Applications ~ Data The tiers are based on business function, and are typically separated by firewalls. 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 engine, which is data access middleware that provides universal data access to a variety of heterogeneous database systems. The query engine formulates queries (typically SQL) and passes them on to the data tier, through a native database API (such as ODBC) for execution.
The data tier contains database management systems (DBMS), which manage raw data stored in a database. Examples of such systems include Oracle, DB2, and Microsoft SQL Server.
Although a mufti-tier architecture can be configured in several different ways, a typical configuration places each tier on a separate computer (server). A
database server is typically a "high end" server, and thus can process queries at a relatively fast speed.
An application server cannot generally process queries as quickly as a database server.
In order to solve many business questions, a query engine may generate SQL
queries that utilize the SQL/OLAP technology introduced in the SQL-99 standard.
However, many database systems do not support this technology. Thus, the SQL
queries would have to be performed on the report server that is generally slower than the database server. It is desirable to have as much processing performed on the database server.
There is a need to prevent or reduce the amount of local (application server) processing required to process a query. In the past, the application would be responsible for generating separate SQL queries involving the GROUP BY operator to compute aggregates over different partitions and stitching together the results. Quite often, this is quite difficult since it involves multiple queries and post processing.
One way of overcoming this problem is for the query engine to generate separate GROUP BY queries for aggregates computed over different partitions, generate a separate query to retrieve detail information, and then stitch together the results to produce the desired report. Unfortunately, this problem requires processing time on the report server. It is desirable to have a way of transferring the SQL queries to the database server with minimal processing on the report server.
SUMMARY OF THE INVENTION
It is an object of the present invention to provide a method of structured query language group query transformation in a database system that does not support standard.
ZO In accordance with an embodiment of the present invention, there is provided a group query transformation system for transforming structured query language group query windowed aggregates. The group query transformation system comprises a group query analysis module for analyzing one or more structured query language (SQL) / on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system, and a group query transformation module for transforming the one or more SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system.
In accordance with another embodiment of the present invention, there is provided a method of transforming structured query language (SC~L) group query windowed aggregates. The method comprises the steps of analyzing one or more SQL/ on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system, and transforming the one or more SQL/OLAP windowed aggregates
-2-into semantically equivalent standard aggregate functions that are supported by the target database system.
In accordance with another embodiment of the present invention, there is provided a method of group query transformation. The method comprises the steps of determining if a detailed table is required, traversing an aggregate List and assigning a group index to each aggregate based on the location of a control break in a control break List, determining a detail table T", traversing the control break list, traversing the select List and analyzing each expression and performing a specific action based on the type of expression encountered, and constructing a final query. For each control break in the control break list, the columns in the control break are added to a select list of a derived table, and for each column in the control break, a join condition of the form T".C~ - T;.C~ OR (T".C~ IS NULL AND T;.C~ IS NULL) is constructed.
In accordance with another embodiment of the present invention, there is provided 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 transforming structured query language (SQL) group query windowed aggregates. The method comprises the steps of analyzing one or more SQL/ on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system, and transforming the one or more SQLIOLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system.
In accordance with another embodiment of the present invention, there is provided a computer-readable medium having computer readable code embodied therein for use in the execution in a computer of a method of transforming structured query language (SQL) group query windowed aggregates. The method comprises the steps of analyzing one or more SQL/ on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system, and transforming the one or more SQLIOLAP
windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system.
In accordance with another embodiment of the present invention, there is provided a computer program product for use in the execution in a computer of a group query transformation system for transforming structured query language (SQL) group _3_ windowed aggregates. The computer program product comprises a group query analysis module for analyzing one or more SQL/ on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system, and a group query transformation module for transforming the one or more SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system.
BRIEF DESCRIPTION OF THE DRAWINGS
Figure 1 shows a typical data access environment.
Figure 2 shows a group query transformation system, in accordance with an embodiment of the present invention.
Figure 3 shows in a flowchart an example of a method of a group query transformation, in accordance with the group query transformation system.
Figure 4 shows in a flowchart another example of a method of group query transformation, in accordance with an embodiment of the group query transformation system.
Figure 5 shows in a flowchart another example of a method of group query transformation, in accordance with an embodiment of the group query transformation system.
DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS
Figure 1 shows a typical data access environment 10 for processing data.
Typically, data is stored in a database 11. A DBMS running on a database server 12 accesses the raw data stored in the database 11. A query engine 15, running on a report server (or application server) 13 is used to generate reports on the raw data and instruct the DBMS
on the database server 12 to obtain information pertaining to the raw data in the database 11.
The query engine 15 provides universal data access to a variety of heterogeneous database systems. An end user uses a client application 14, running on a client workstation, to facilitate application server 13 operations.
In order to solve many business questions, a query engine 15 generates SQL
queries that utilize the SQL/online analytical programming (OLAP) technology introduced in the SQL-99 standard. These SQL queries include SQL/OLAP functions (windowed aggregates).
However, many database systems 12 do not support this technology. In order to prevent or reduce the amount of local (application server) processing required to process these types of queries, the query engine 15 attempts to generate semantically equivalent queries that can be processed on the database server 12 by the target database system. These semantically equivalent queries include standard aggregate functions and the GROUP BY operator.
Figure 2 shows a group query transformation system 20, in accordance with an embodiment of the present invention. The group query transformation system 20 comprises a group query analysis module 21 for analyzing SQL/OLAP windowed aggregates that are not supported by a target database system, and a group query transformation module 22 for transforming SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system.
The group query transformation system 20 is implemented as a sub-system of the query engine I 5 in the data access environment 10. This transformation 20 may generate queries that can be processed in their entirety on the database server 12, or queries that require processing on both the application server 13 and the database server 12.
Advantageously, the group query transformation system 20 reduces processing that might otherwise be required on the application server by generating a semantically equivalent query, thereby improving performance in many cases. Furthermore, the group query transformation system 20 takes advantage of functionality provided by the target database. In particular, the group query system 20 utilizes the functionality provided by standard aggregates and the GROUP BY operator.
Another advantage is that performing SQL group query transformation may eliminate any processing that may be required on the report server 13, since the group query transformation system 20 could end up generating a single query in the event that all SQL/OLAP functions map to a single aggregate.
There are two types of OLAP functions: framed functions and report functions.
Framed OLAP functions contain a window frame specification (ROWS or RANGE) and an ORDER BY clause. Through window frames, capabilities such as cumulative (running) sums and moving averages can be supported. Report functions do not contain a window frame specification, and produce the same value for each row in a partition.
The group query transformation of a SQL-99 query involves mapping windowed OLAP functions into a semantically equivalent query involving derived tables and the standard GROUP BY clause.
Figure 3 shows a flowchart of an example of a method of SQL group transformation (30), in accordance with an embodiment of the group query transformation system 20. The method (30) begins with analysing a query containing a group query that is not supported by a target database system (3I). Next, the query is transformed into a semantically equivalent query that is supported by the target database system (32). The method (30) is done (33).
Figure 4 shows in a flowchart an example of a method of SQL group transformation (40), in accordance with an embodiment of the group query transformation system 20. The method (40) begins with analyzing a group query select list to determine if transformations can be performed (41). A select list is a group of one or more expressions that define the required information for a particular query. If transformations cannot be performed (42), the method (40) is done (47). If a transformation is to be performed (42), a number of derived tables required is determined based upon a number of partitions (or control breaks) (43). A control break is a set of columns over which a particular OLAP function is computed. A partition is a set of rows that have the same value for columns that make up the control break. The derived tables are created and grouping columns are added to an appropriate derived table select list (44). A
WHERE
clause is generated based upon partitions and nullability of grouping columns (45). The group query select list is traversed and candidate aggregates are converted and moved into appropriate derived table select lists (46). The method is done (47).
As described above, the original query is first analyzed to determine what SQLIOLAP functions are present, which of these functions can be mapped to standard aggregates (MIN, MAX, SUM, AVG, COUNT, COUNT(*)), and what control breaks (partitions) exist (41 ). Each unique control break represents a separate derived table in the transformed query, and all functions with the same control break appear in the same derived table. The derived tables axe then joined based on the columns that make up the longest (most columns) control break.
To determine if the transformation can be performed, all aggregates in the select list are analyzed. This analysis includes assigning a group index and keeping track of control breaks (or partitions). The group index indicates whether the aggregate is eligible for transformation. Any aggregate assigned a group index of -1 is not eligible. Control break information is stored in an ordered list, with control breaks having the fewest number of columns (least amount of detail) appearing f rst. Each unique control break represents a separate derived table in the transformed query.
Report aggregates (MIN, MAX, SUM, AVG, COUNT, and COUNT(*)) are eligible for transformation, based on meeting certain criteria. A count of these aggregates (nSqlGroupAggrs) is maintained. This count is not incremented if a group index of -1 is assigned. Framed aggregates are not eligible for transformation.
A group index of -1 is assigned to report aggregates of the form:
AGG( Co ) OVER ( AT C1, C2, ..., Cm PARTITION BY Ci, C2, ..., C" ), where m <
n or m>(n+1).
AGG( Co ) OVER ( AT C1, C~, ..., Cm ), where m > 1.
AGG( Co ) OVER ( AT C1 ), where C1 ~ Co, AGG( Co ) OVER ( AT Ci, C2, ..., Cm PARTITION BY Ci, C2, ..., C" ), where m =
( n +
1 ), and Cm ~ Ca.
Otherwise, the group index assigned to the aggregate is positive (> 0) and based on the control break. All aggregates with an identical control break are assigned the same group index.
The following table gives examples of aggregates that are not eligible for transformation, as well as aggregates that are eligible for transformation.
Not Eligible SUM( QTY ) OVER ( AT SNO PARTITION BY SNO, PNO
SUM( QTY ) OVER ( AT SNO, PNO, JNO FOR SNO ) SUM( QTY ) OVER ( AT SNO, PNO ) SUM( QTY ) OVER ( AT SNO ) SUM( QTY ) OVER ( AT SNO, PNO PARTITION BY SNO ) Eligible SUM( QTY ) OVER Q
SUM( QTY ) OVER ( AT QTY ) SUM( QTY ) OVER ( AT SNO, QTY PARTITION BY SNO ) In addition to assigning a group index to all aggregates in the select list of the original query, this analysis phase determines a level of optimization. Level optimization requires an additional derived table to be constructed to retrieve detail information, whereas Level 2 optimization does not require this additional derived table.
Level 1:
To achieve Level 1 optimization, each of the following conditions must be met:
1. All aggregates are assigned a positive (> 0) group index.
2. All aggregate control breaks are compatible.
In accordance with another embodiment of the present invention, there is provided a method of group query transformation. The method comprises the steps of determining if a detailed table is required, traversing an aggregate List and assigning a group index to each aggregate based on the location of a control break in a control break List, determining a detail table T", traversing the control break list, traversing the select List and analyzing each expression and performing a specific action based on the type of expression encountered, and constructing a final query. For each control break in the control break list, the columns in the control break are added to a select list of a derived table, and for each column in the control break, a join condition of the form T".C~ - T;.C~ OR (T".C~ IS NULL AND T;.C~ IS NULL) is constructed.
In accordance with another embodiment of the present invention, there is provided 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 transforming structured query language (SQL) group query windowed aggregates. The method comprises the steps of analyzing one or more SQL/ on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system, and transforming the one or more SQLIOLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system.
In accordance with another embodiment of the present invention, there is provided a computer-readable medium having computer readable code embodied therein for use in the execution in a computer of a method of transforming structured query language (SQL) group query windowed aggregates. The method comprises the steps of analyzing one or more SQL/ on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system, and transforming the one or more SQLIOLAP
windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system.
In accordance with another embodiment of the present invention, there is provided a computer program product for use in the execution in a computer of a group query transformation system for transforming structured query language (SQL) group _3_ windowed aggregates. The computer program product comprises a group query analysis module for analyzing one or more SQL/ on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system, and a group query transformation module for transforming the one or more SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system.
BRIEF DESCRIPTION OF THE DRAWINGS
Figure 1 shows a typical data access environment.
Figure 2 shows a group query transformation system, in accordance with an embodiment of the present invention.
Figure 3 shows in a flowchart an example of a method of a group query transformation, in accordance with the group query transformation system.
Figure 4 shows in a flowchart another example of a method of group query transformation, in accordance with an embodiment of the group query transformation system.
Figure 5 shows in a flowchart another example of a method of group query transformation, in accordance with an embodiment of the group query transformation system.
DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS
Figure 1 shows a typical data access environment 10 for processing data.
Typically, data is stored in a database 11. A DBMS running on a database server 12 accesses the raw data stored in the database 11. A query engine 15, running on a report server (or application server) 13 is used to generate reports on the raw data and instruct the DBMS
on the database server 12 to obtain information pertaining to the raw data in the database 11.
The query engine 15 provides universal data access to a variety of heterogeneous database systems. An end user uses a client application 14, running on a client workstation, to facilitate application server 13 operations.
In order to solve many business questions, a query engine 15 generates SQL
queries that utilize the SQL/online analytical programming (OLAP) technology introduced in the SQL-99 standard. These SQL queries include SQL/OLAP functions (windowed aggregates).
However, many database systems 12 do not support this technology. In order to prevent or reduce the amount of local (application server) processing required to process these types of queries, the query engine 15 attempts to generate semantically equivalent queries that can be processed on the database server 12 by the target database system. These semantically equivalent queries include standard aggregate functions and the GROUP BY operator.
Figure 2 shows a group query transformation system 20, in accordance with an embodiment of the present invention. The group query transformation system 20 comprises a group query analysis module 21 for analyzing SQL/OLAP windowed aggregates that are not supported by a target database system, and a group query transformation module 22 for transforming SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system.
The group query transformation system 20 is implemented as a sub-system of the query engine I 5 in the data access environment 10. This transformation 20 may generate queries that can be processed in their entirety on the database server 12, or queries that require processing on both the application server 13 and the database server 12.
Advantageously, the group query transformation system 20 reduces processing that might otherwise be required on the application server by generating a semantically equivalent query, thereby improving performance in many cases. Furthermore, the group query transformation system 20 takes advantage of functionality provided by the target database. In particular, the group query system 20 utilizes the functionality provided by standard aggregates and the GROUP BY operator.
Another advantage is that performing SQL group query transformation may eliminate any processing that may be required on the report server 13, since the group query transformation system 20 could end up generating a single query in the event that all SQL/OLAP functions map to a single aggregate.
There are two types of OLAP functions: framed functions and report functions.
Framed OLAP functions contain a window frame specification (ROWS or RANGE) and an ORDER BY clause. Through window frames, capabilities such as cumulative (running) sums and moving averages can be supported. Report functions do not contain a window frame specification, and produce the same value for each row in a partition.
The group query transformation of a SQL-99 query involves mapping windowed OLAP functions into a semantically equivalent query involving derived tables and the standard GROUP BY clause.
Figure 3 shows a flowchart of an example of a method of SQL group transformation (30), in accordance with an embodiment of the group query transformation system 20. The method (30) begins with analysing a query containing a group query that is not supported by a target database system (3I). Next, the query is transformed into a semantically equivalent query that is supported by the target database system (32). The method (30) is done (33).
Figure 4 shows in a flowchart an example of a method of SQL group transformation (40), in accordance with an embodiment of the group query transformation system 20. The method (40) begins with analyzing a group query select list to determine if transformations can be performed (41). A select list is a group of one or more expressions that define the required information for a particular query. If transformations cannot be performed (42), the method (40) is done (47). If a transformation is to be performed (42), a number of derived tables required is determined based upon a number of partitions (or control breaks) (43). A control break is a set of columns over which a particular OLAP function is computed. A partition is a set of rows that have the same value for columns that make up the control break. The derived tables are created and grouping columns are added to an appropriate derived table select list (44). A
WHERE
clause is generated based upon partitions and nullability of grouping columns (45). The group query select list is traversed and candidate aggregates are converted and moved into appropriate derived table select lists (46). The method is done (47).
As described above, the original query is first analyzed to determine what SQLIOLAP functions are present, which of these functions can be mapped to standard aggregates (MIN, MAX, SUM, AVG, COUNT, COUNT(*)), and what control breaks (partitions) exist (41 ). Each unique control break represents a separate derived table in the transformed query, and all functions with the same control break appear in the same derived table. The derived tables axe then joined based on the columns that make up the longest (most columns) control break.
To determine if the transformation can be performed, all aggregates in the select list are analyzed. This analysis includes assigning a group index and keeping track of control breaks (or partitions). The group index indicates whether the aggregate is eligible for transformation. Any aggregate assigned a group index of -1 is not eligible. Control break information is stored in an ordered list, with control breaks having the fewest number of columns (least amount of detail) appearing f rst. Each unique control break represents a separate derived table in the transformed query.
Report aggregates (MIN, MAX, SUM, AVG, COUNT, and COUNT(*)) are eligible for transformation, based on meeting certain criteria. A count of these aggregates (nSqlGroupAggrs) is maintained. This count is not incremented if a group index of -1 is assigned. Framed aggregates are not eligible for transformation.
A group index of -1 is assigned to report aggregates of the form:
AGG( Co ) OVER ( AT C1, C2, ..., Cm PARTITION BY Ci, C2, ..., C" ), where m <
n or m>(n+1).
AGG( Co ) OVER ( AT C1, C~, ..., Cm ), where m > 1.
AGG( Co ) OVER ( AT C1 ), where C1 ~ Co, AGG( Co ) OVER ( AT Ci, C2, ..., Cm PARTITION BY Ci, C2, ..., C" ), where m =
( n +
1 ), and Cm ~ Ca.
Otherwise, the group index assigned to the aggregate is positive (> 0) and based on the control break. All aggregates with an identical control break are assigned the same group index.
The following table gives examples of aggregates that are not eligible for transformation, as well as aggregates that are eligible for transformation.
Not Eligible SUM( QTY ) OVER ( AT SNO PARTITION BY SNO, PNO
SUM( QTY ) OVER ( AT SNO, PNO, JNO FOR SNO ) SUM( QTY ) OVER ( AT SNO, PNO ) SUM( QTY ) OVER ( AT SNO ) SUM( QTY ) OVER ( AT SNO, PNO PARTITION BY SNO ) Eligible SUM( QTY ) OVER Q
SUM( QTY ) OVER ( AT QTY ) SUM( QTY ) OVER ( AT SNO, QTY PARTITION BY SNO ) In addition to assigning a group index to all aggregates in the select list of the original query, this analysis phase determines a level of optimization. Level optimization requires an additional derived table to be constructed to retrieve detail information, whereas Level 2 optimization does not require this additional derived table.
Level 1:
To achieve Level 1 optimization, each of the following conditions must be met:
1. All aggregates are assigned a positive (> 0) group index.
2. All aggregate control breaks are compatible.
3. The total number of unique control breaks is equal to 1 or there are no aggregates in the select list that has a compute break (AT clause).
Level 2:
To achieve Level 2 optimization, each of the following conditions must be met:
1. Level 1 optimization is possible.
2. DISTINCT was specified or GROUP BY was specified and the group list is compatible with the longest control break.
3. Any simple column referenced in the select list is part of the grouping list.
Figure 5 shows in a flowchart another example of a method of group query transformation (50), in accordance with an embodiment of the group query transformation system 20. The basic steps in performing the transformation are as follows:
1. Determine if a detail table is required (51) based on the level of optimization.
2. Traverse the aggregate list, assigning a group index to each aggregate based on the location of its control break in the control break list (52). This index identifies the derived table into which the aggregate is moved.
3. Determine the detail table Tn (53). This is the table on which all join conditions are based.
Level 2:
To achieve Level 2 optimization, each of the following conditions must be met:
1. Level 1 optimization is possible.
2. DISTINCT was specified or GROUP BY was specified and the group list is compatible with the longest control break.
3. Any simple column referenced in the select list is part of the grouping list.
Figure 5 shows in a flowchart another example of a method of group query transformation (50), in accordance with an embodiment of the group query transformation system 20. The basic steps in performing the transformation are as follows:
1. Determine if a detail table is required (51) based on the level of optimization.
2. Traverse the aggregate list, assigning a group index to each aggregate based on the location of its control break in the control break list (52). This index identifies the derived table into which the aggregate is moved.
3. Determine the detail table Tn (53). This is the table on which all join conditions are based.
4. Traverse the control break list (54), and for each control break i (55), perform the following:
a. Add all columns in the control break to the select list of the derived table T~ (56).
b. For each column j in the control break, construct a join condition of the form T~. C~ = T;. C~ OR ( T,~ C~ IS NULL AND T;. C~ IS NULL), where Tn is the detail table (57).
_g_ S. Traverse the select list (58), analyze each expression and perform a specific action based on the type of expression encountered:
a. If the expression is an aggregate (S9), convert the aggregate to its standard form and move it to the select list of the derived table identified by its S group index (60).
b. Traverse all other expressions to determine if:
i. The expression is supported by the target database (61).
ii. All aggregates in the expression have the same (positive) group index (62).
iii. No other detail information is being referenced (63).
If these conditions are met, all aggregates in the expression are converted to their standard form, and the expression is moved to the select list of the derived table identified by the group index (64).
6. Construct the final query (6S). The final query is consists of a simple join I S between all of the generated derived tables based on the condition constructed in step 4.
For the purpose of the following examples, assume the target database for each example does not support the SQL/OLAP technology introduced in the SQL-99 standard.
The native SQL shown in each example is the SQL passed to the target database.
Ezample 1 In this example, the target database can process the transformed query in its entirety.
Original Query SELECT SNO, PNO, SUM( QTY ) OVER Q, SUM( QTY ) OVER ( PARTITION BY SNO ), SUM( QTY ) OVER ( PARTITION BY SNO, PNO ) FROM SUPPLY
_g_ Transformed Ouery SELECT T3.C0, .C1, TO.CO, T1.C1, T2.C2 FROM ( SELECT SUM( QTY ) CO
FROM SUPPLY ) T0, ( SELECT SNO C0, SUM( QTY ) Cl FROM SUPPLY
GROUP BY SNO ) T1, ( SELECT SNO C0, PNO Cl, SUM( QTY ) C2 FROM SUPPLY
GROUP BY SNO, PNO ) T2, ( SELECT SNO C0, PNO C 1 FROM SUPPLY ) T3 WHERE ( T3.C0 Tl.CO OR ( T3.C0 IS NULL AND T1.C0 = IS NULL ) ) AND ( T3.C0 T2.C0 OR ( T3.C0 IS NULL AND T2.C0 = IS NULL
AND ( T3.C T2.C 1 OR ( T3.C 1 IS NULL AND T2.C
1 = 1 IS NULL ) ) Native SQL
SELECT T3.C0, T3.C1, TO.CO, T1.C1, T2.C2 FROM ( SELECT SUM( QTY ) CO
FROM SUPPLY ) T0, ( SELECT SNO C0, SUM( QTY ) C 1 FROM SUPPL~i' GROUP BY SNO ) Tl, ( SELECT SNO C0, PNO Cl, SUM( QTY ) C2 FROM SUPPLY
GROUP BY SNO, PNO ) T2, ( SELECT SNO C0, PNO C I
FROM SUPPLY ) T3 WHERE ( T3.C0 T1.C0 OR ( T3.C0 IS NULL AND Tl.CO IS NULL ) ) =
AND ( T3.C0 T2.C0 OR ( T3.C0 IS NULL AND T2.C0 IS NULL ) ) =
AND ( T3.C T2.C i OR ( T3.C 1 IS NULL AND T2.C 1 IS NULL ) 1 = ) Ex~ lan nation The original query contains OLAP SUM functions computed over the partitions (), (SNO), and (SNO, PNO), with all detail information retained. Hence, four derived tables are required. Derived table TO computes the overall sum, derived table computes a sum for the (SNO) group, derived table T2 computes a sum for the (SNO, PNO) group, and finally, derived table T3 retrieves all detail information.
These tables are then joined based on the grouping columns. When generating the WHERE
clause, the algorithm must take into consideration whether or not a particular grouping column allows NULL values. In this example, it is assumed that all of the grouping columns allow null values. This corresponds to Level 1 optimization.
Example 2 In this example, the target database can process the transformed query in its entirety. Note that with the presence of the DISTINCT keyword, one less derived table is generated since detail information is not required (this is an optimization performed by the query transformation).
Ori~inai Ouery SELECT DISTINCT SNO, PNO, SUM( QTY ) OVER Q, SUM( QTY ) OVER ( PARTITION BY SNO ), SUM( QTY ) OVER ( PARTITION BY SNO, PNO ) FROM SUPPLY
Transformed Query SELECT T2.C0, T2.C1, TO.CO, TI.CI, T2.C2 FROM ( SELECT SUM( QTY ) CO
FROM SUPPLY ) T0, ( SELECT SNO C0, SUM( QTY ) C1 FROM SUPPLY
GROUP BY SNO ) Tl, ( SELECT SNO C0, PNO C1, SUM( QTY ) C2 FROM SUPPLY
GROUP BY SNO, PNO ) T2 WHERE ( T2.C0 = TI .CO OR ( T2.C0 IS NULL AND TI .C0 IS NULL ) ) Native SOL
SELECT T2.C0, T2.C1, TO.CO, TI.Cl, T2.C2 FROM ( SELECT SUM{ QTY ) CO
FROM SUPPLY ) T0, ( SELECT SNO C0, SUM( QTY ) C l FROM SUPPLY
GROUP BY SNO ) T1, ( SELECT SNO C0, PNO C1, SUM( QTY ) C2 FROM SUPPLY
GROUP BY SNO, PNO ) T2 WHERE ( T2.C0 = T1.C0 OR ( T2.C0 IS NULL AND T1.C0 IS NULL ) ) Ext~Ianation The original query is identical to that in Example l, except that DISTINCT is specified. Hence, only three derived tables are required, since detail information is not being retained. Derived table TO computes the overall sum, derived table TI
computes a sum for the (SNO) group, and derived table T2 computes a sum for the (SNO, PNO) group. This corresponds to Level 2 optimization.
Example 3 In this example, the target database cannot process the transformed query in its entirety due to the presence of the RANK OLAP function. The SQL server does not support this function, and it cannot be transformed into a standard aggregate specification.
Original Ouery SELECT SNO, PNO, SUM( QTY ) OVER (), SUM( QTY ) OVER ( PARTITION BY SNO ), SUM( QTY ) OVER ( PARTITION BY SNO, PNO ), RANKQ OVER ( ORDER BY QTY DESC ) FROM SUPPLY
Transformed Query SELECT T3.C0, .C1, TO.CO, C4, T1.C1, T2.C2, RANK() OVER
( ORDER
BY T3.C2 DESC
) FROM ( SELECT SUM( QTY ) CO
FROM SUPPLY ) T0, ( SELECT SNO C0, SUM( QTY ) C 1 FROM SUPPLY
GROUP BY SNO ) T1, ( SELECT SNO C0, PNO Cl, SUM( QTY ) C2 FROM SUPPLY
GROUP BY SNO, PNO ) T2, ( SELECT SNO C0, PNO C1, QTY C2 FROM SUPPLY ) T3 WHERE ( T3.C0 Tl.CO OR ( T3.C0 IS NULL AND T1.C0 IS
= NULL ) AND ( T3.C0 T2.C0 OR ( T3.C0 IS NULL AND T2.C0 IS
= NULL ) AND ( T3.C1 T2.C1 OR ( T3.C1 IS NULL AND T2.C1 IS
= NULL ) ) ) Dl Native SOL
SELECT T3.C0, .C1, TO.CO, C4, Tl.CI, T2.C2, T3.C2 FROM ( SELECT SUM( QTY ) CO
FROM SUPPLY ) T0, ( SELECT SNO C0, SUM( QTY ) C 1 FROM SUPPLY
GROUP BY SNO ) T1, ( SELECT SNO C0, PNO Cl, SUM( QTY ) C2 FROM SUPPLY
GROUP BY SNO, PNO ) T2, ( SELECT SNO C0, PNO C 1, QTY C2 FROM SUPPLY ) T3 WHERE ( T3.C0 = T1.C0 OR ( T3.C0 IS NULL AND T1.C0 IS NULL ) ) AND ( T3.C0 = T2.C0 OR ( T3.C0 IS NULL AND T2.C0 IS NULL ) AND ( T3.C1= T2.C1 OR ( T3.C1 IS NULL AND T2.C1 IS NULL ) ) ORDER BY T3.C2 Explanation The original query is similar to that in Example 1, except for the RANK
function being specified. Four derived tables are required which are identical to those generated for Example 1, except that derived table T3 contains the additional detail column QTY in the select list which is required to compute the RANK function (this OLAP
function does not have a corresponding aggregate function). This corresponds to Level 1 optimization.
I O The systems and methods according to the present invention may be implemented by any hardware, software or a combination of hardware and software having the above-described functions. The software code, either in its entirety or a part thereof, may be stored in a computer readable memory. Further, a computer data signal representing the software code that may be embedded in a carrier wave may be transmitted via a I S communication network. Such a computer readable memory and a computer data signal are also within the scope of the present invention, as well as the haxdware, software and the combination thereof.
While particular embodiments of the present invention have been shown and described, changes and modifications may be made to such embodiments without 20 departing from the true scope of the invention.
a. Add all columns in the control break to the select list of the derived table T~ (56).
b. For each column j in the control break, construct a join condition of the form T~. C~ = T;. C~ OR ( T,~ C~ IS NULL AND T;. C~ IS NULL), where Tn is the detail table (57).
_g_ S. Traverse the select list (58), analyze each expression and perform a specific action based on the type of expression encountered:
a. If the expression is an aggregate (S9), convert the aggregate to its standard form and move it to the select list of the derived table identified by its S group index (60).
b. Traverse all other expressions to determine if:
i. The expression is supported by the target database (61).
ii. All aggregates in the expression have the same (positive) group index (62).
iii. No other detail information is being referenced (63).
If these conditions are met, all aggregates in the expression are converted to their standard form, and the expression is moved to the select list of the derived table identified by the group index (64).
6. Construct the final query (6S). The final query is consists of a simple join I S between all of the generated derived tables based on the condition constructed in step 4.
For the purpose of the following examples, assume the target database for each example does not support the SQL/OLAP technology introduced in the SQL-99 standard.
The native SQL shown in each example is the SQL passed to the target database.
Ezample 1 In this example, the target database can process the transformed query in its entirety.
Original Query SELECT SNO, PNO, SUM( QTY ) OVER Q, SUM( QTY ) OVER ( PARTITION BY SNO ), SUM( QTY ) OVER ( PARTITION BY SNO, PNO ) FROM SUPPLY
_g_ Transformed Ouery SELECT T3.C0, .C1, TO.CO, T1.C1, T2.C2 FROM ( SELECT SUM( QTY ) CO
FROM SUPPLY ) T0, ( SELECT SNO C0, SUM( QTY ) Cl FROM SUPPLY
GROUP BY SNO ) T1, ( SELECT SNO C0, PNO Cl, SUM( QTY ) C2 FROM SUPPLY
GROUP BY SNO, PNO ) T2, ( SELECT SNO C0, PNO C 1 FROM SUPPLY ) T3 WHERE ( T3.C0 Tl.CO OR ( T3.C0 IS NULL AND T1.C0 = IS NULL ) ) AND ( T3.C0 T2.C0 OR ( T3.C0 IS NULL AND T2.C0 = IS NULL
AND ( T3.C T2.C 1 OR ( T3.C 1 IS NULL AND T2.C
1 = 1 IS NULL ) ) Native SQL
SELECT T3.C0, T3.C1, TO.CO, T1.C1, T2.C2 FROM ( SELECT SUM( QTY ) CO
FROM SUPPLY ) T0, ( SELECT SNO C0, SUM( QTY ) C 1 FROM SUPPL~i' GROUP BY SNO ) Tl, ( SELECT SNO C0, PNO Cl, SUM( QTY ) C2 FROM SUPPLY
GROUP BY SNO, PNO ) T2, ( SELECT SNO C0, PNO C I
FROM SUPPLY ) T3 WHERE ( T3.C0 T1.C0 OR ( T3.C0 IS NULL AND Tl.CO IS NULL ) ) =
AND ( T3.C0 T2.C0 OR ( T3.C0 IS NULL AND T2.C0 IS NULL ) ) =
AND ( T3.C T2.C i OR ( T3.C 1 IS NULL AND T2.C 1 IS NULL ) 1 = ) Ex~ lan nation The original query contains OLAP SUM functions computed over the partitions (), (SNO), and (SNO, PNO), with all detail information retained. Hence, four derived tables are required. Derived table TO computes the overall sum, derived table computes a sum for the (SNO) group, derived table T2 computes a sum for the (SNO, PNO) group, and finally, derived table T3 retrieves all detail information.
These tables are then joined based on the grouping columns. When generating the WHERE
clause, the algorithm must take into consideration whether or not a particular grouping column allows NULL values. In this example, it is assumed that all of the grouping columns allow null values. This corresponds to Level 1 optimization.
Example 2 In this example, the target database can process the transformed query in its entirety. Note that with the presence of the DISTINCT keyword, one less derived table is generated since detail information is not required (this is an optimization performed by the query transformation).
Ori~inai Ouery SELECT DISTINCT SNO, PNO, SUM( QTY ) OVER Q, SUM( QTY ) OVER ( PARTITION BY SNO ), SUM( QTY ) OVER ( PARTITION BY SNO, PNO ) FROM SUPPLY
Transformed Query SELECT T2.C0, T2.C1, TO.CO, TI.CI, T2.C2 FROM ( SELECT SUM( QTY ) CO
FROM SUPPLY ) T0, ( SELECT SNO C0, SUM( QTY ) C1 FROM SUPPLY
GROUP BY SNO ) Tl, ( SELECT SNO C0, PNO C1, SUM( QTY ) C2 FROM SUPPLY
GROUP BY SNO, PNO ) T2 WHERE ( T2.C0 = TI .CO OR ( T2.C0 IS NULL AND TI .C0 IS NULL ) ) Native SOL
SELECT T2.C0, T2.C1, TO.CO, TI.Cl, T2.C2 FROM ( SELECT SUM{ QTY ) CO
FROM SUPPLY ) T0, ( SELECT SNO C0, SUM( QTY ) C l FROM SUPPLY
GROUP BY SNO ) T1, ( SELECT SNO C0, PNO C1, SUM( QTY ) C2 FROM SUPPLY
GROUP BY SNO, PNO ) T2 WHERE ( T2.C0 = T1.C0 OR ( T2.C0 IS NULL AND T1.C0 IS NULL ) ) Ext~Ianation The original query is identical to that in Example l, except that DISTINCT is specified. Hence, only three derived tables are required, since detail information is not being retained. Derived table TO computes the overall sum, derived table TI
computes a sum for the (SNO) group, and derived table T2 computes a sum for the (SNO, PNO) group. This corresponds to Level 2 optimization.
Example 3 In this example, the target database cannot process the transformed query in its entirety due to the presence of the RANK OLAP function. The SQL server does not support this function, and it cannot be transformed into a standard aggregate specification.
Original Ouery SELECT SNO, PNO, SUM( QTY ) OVER (), SUM( QTY ) OVER ( PARTITION BY SNO ), SUM( QTY ) OVER ( PARTITION BY SNO, PNO ), RANKQ OVER ( ORDER BY QTY DESC ) FROM SUPPLY
Transformed Query SELECT T3.C0, .C1, TO.CO, C4, T1.C1, T2.C2, RANK() OVER
( ORDER
BY T3.C2 DESC
) FROM ( SELECT SUM( QTY ) CO
FROM SUPPLY ) T0, ( SELECT SNO C0, SUM( QTY ) C 1 FROM SUPPLY
GROUP BY SNO ) T1, ( SELECT SNO C0, PNO Cl, SUM( QTY ) C2 FROM SUPPLY
GROUP BY SNO, PNO ) T2, ( SELECT SNO C0, PNO C1, QTY C2 FROM SUPPLY ) T3 WHERE ( T3.C0 Tl.CO OR ( T3.C0 IS NULL AND T1.C0 IS
= NULL ) AND ( T3.C0 T2.C0 OR ( T3.C0 IS NULL AND T2.C0 IS
= NULL ) AND ( T3.C1 T2.C1 OR ( T3.C1 IS NULL AND T2.C1 IS
= NULL ) ) ) Dl Native SOL
SELECT T3.C0, .C1, TO.CO, C4, Tl.CI, T2.C2, T3.C2 FROM ( SELECT SUM( QTY ) CO
FROM SUPPLY ) T0, ( SELECT SNO C0, SUM( QTY ) C 1 FROM SUPPLY
GROUP BY SNO ) T1, ( SELECT SNO C0, PNO Cl, SUM( QTY ) C2 FROM SUPPLY
GROUP BY SNO, PNO ) T2, ( SELECT SNO C0, PNO C 1, QTY C2 FROM SUPPLY ) T3 WHERE ( T3.C0 = T1.C0 OR ( T3.C0 IS NULL AND T1.C0 IS NULL ) ) AND ( T3.C0 = T2.C0 OR ( T3.C0 IS NULL AND T2.C0 IS NULL ) AND ( T3.C1= T2.C1 OR ( T3.C1 IS NULL AND T2.C1 IS NULL ) ) ORDER BY T3.C2 Explanation The original query is similar to that in Example 1, except for the RANK
function being specified. Four derived tables are required which are identical to those generated for Example 1, except that derived table T3 contains the additional detail column QTY in the select list which is required to compute the RANK function (this OLAP
function does not have a corresponding aggregate function). This corresponds to Level 1 optimization.
I O The systems and methods according to the present invention may be implemented by any hardware, software or a combination of hardware and software having the above-described functions. The software code, either in its entirety or a part thereof, may be stored in a computer readable memory. Further, a computer data signal representing the software code that may be embedded in a carrier wave may be transmitted via a I S communication network. Such a computer readable memory and a computer data signal are also within the scope of the present invention, as well as the haxdware, software and the combination thereof.
While particular embodiments of the present invention have been shown and described, changes and modifications may be made to such embodiments without 20 departing from the true scope of the invention.
Claims (7)
1. A group query transformation system for transforming structured query language group query windowed aggregates, the system comprising:
a group query analysis module for analyzing one or more structured query language (SQL) / on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system, including:
means for determining if a transformation is to be performed; and means for determining a number of derived tables required based upon a number of control breaks; and a group query transformation module for transforming the one or more SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system, including:
means for generating the derived tables;
means for adding grouping columns to derived table select lists;
means for generating a WHERE clause based upon control breaks and nullability of grouping columns;
means for traversing a group query select list; and means for converting and moving candidate aggregates into the derived table select lists.
a group query analysis module for analyzing one or more structured query language (SQL) / on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system, including:
means for determining if a transformation is to be performed; and means for determining a number of derived tables required based upon a number of control breaks; and a group query transformation module for transforming the one or more SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system, including:
means for generating the derived tables;
means for adding grouping columns to derived table select lists;
means for generating a WHERE clause based upon control breaks and nullability of grouping columns;
means for traversing a group query select list; and means for converting and moving candidate aggregates into the derived table select lists.
2. A method of transforming structured query language (SQL) group query windowed aggregates, the method comprising the steps of:
analyzing one or more SQL/ on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system, including the steps of:
determining if a transformation is to be performed; and determining a number of derived tables required based upon a number of control breaks; and transforming the one or more SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system, including the steps of:
generating the derived tables;
adding grouping columns to derived table select lists;
generating a WHERE clause based upon control breaks and nullability of grouping columns;
traversing a group query select list; and converting and moving candidate aggregates into the derived table select lists.
analyzing one or more SQL/ on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system, including the steps of:
determining if a transformation is to be performed; and determining a number of derived tables required based upon a number of control breaks; and transforming the one or more SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system, including the steps of:
generating the derived tables;
adding grouping columns to derived table select lists;
generating a WHERE clause based upon control breaks and nullability of grouping columns;
traversing a group query select list; and converting and moving candidate aggregates into the derived table select lists.
3. The method as claimed in claim 2, wherein the step of determining if a transformation is to be performed comprises the steps of:
assigning a group index; and tracking control breaks.
assigning a group index; and tracking control breaks.
4. The method as claimed in claim 2, wherein the WHERE clause is of the form T1.C1 = T2.C1 or ( T1.C1 IS NULL and T2.C1 IS NULL) AND ...
if grouping columns are nullable.
if grouping columns are nullable.
5. A method of group query transformation, the method comprising the steps of:
determining if a detailed table is required;
traversing an aggregate list and assigning a group index to each aggregate based on the location of a control break in a control break list;
determining a detail table T n;
traversing the control break list and for each control break in the control break list:
adding all columns in the control break to a select list of a derived table;
and for each column in the control break, constructing a join condition of the form T n.C j - T i.C j OR (T n.C j IS NULL AND T i.C j IS NULL);
traversing the select list and analyzing each expression and performing a specific action based on the type of expression encountered; and constructing a final query.
determining if a detailed table is required;
traversing an aggregate list and assigning a group index to each aggregate based on the location of a control break in a control break list;
determining a detail table T n;
traversing the control break list and for each control break in the control break list:
adding all columns in the control break to a select list of a derived table;
and for each column in the control break, constructing a join condition of the form T n.C j - T i.C j OR (T n.C j IS NULL AND T i.C j IS NULL);
traversing the select list and analyzing each expression and performing a specific action based on the type of expression encountered; and constructing a final query.
6. A computer-readable medium having computer readable code embodied therein for use in the execution in a computer of a method of transforming structured query language (SQL) group query windowed aggregates, the method comprising the steps of:
analyzing one or more SQL/ on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system, including the steps of:
determining if a transformation is to be performed; and determining the number of derived tables required based upon the number of control breaks; and transforming the one or more SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system, including the steps of:
generating the derived tables;
adding grouping columns to derived table select lists;
generating a WHERE clause based upon control breaks and nullability of grouping columns;
traversing a group query select list; and converting and moving candidate aggregates into the derived table select lists.
analyzing one or more SQL/ on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system, including the steps of:
determining if a transformation is to be performed; and determining the number of derived tables required based upon the number of control breaks; and transforming the one or more SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system, including the steps of:
generating the derived tables;
adding grouping columns to derived table select lists;
generating a WHERE clause based upon control breaks and nullability of grouping columns;
traversing a group query select list; and converting and moving candidate aggregates into the derived table select lists.
7. A computer program product for use in the execution in a computer of a group query transformation system for transforming structured query language (SQL) group windowed aggregates, the computer program product comprising:
a group query analysis module for analyzing one or more structured query language (SQL) / on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system, including:
means for determining if a transformation is to be performed; and means for determining the number of derived tables required based upon the number of control breaks; and a group query transformation module for transforming the one or more SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system, including:
means for generating the derived tables;
means for adding grouping columns to derived table select lists;
means for generating a WHERE clause based upon control breaks and nullability of grouping columns;
means for traversing a group query select list; and means for converting and moving candidate aggregates into the derived table select lists.
a group query analysis module for analyzing one or more structured query language (SQL) / on-line analytical programming (OLAP) windowed aggregates that are not supported by a target database system, including:
means for determining if a transformation is to be performed; and means for determining the number of derived tables required based upon the number of control breaks; and a group query transformation module for transforming the one or more SQL/OLAP windowed aggregates into semantically equivalent standard aggregate functions that are supported by the target database system, including:
means for generating the derived tables;
means for adding grouping columns to derived table select lists;
means for generating a WHERE clause based upon control breaks and nullability of grouping columns;
means for traversing a group query select list; and means for converting and moving candidate aggregates into the derived table select lists.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CA002468398A CA2468398C (en) | 2003-05-27 | 2004-05-27 | System and method of query transformation |
Applications Claiming Priority (3)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CA002429910A CA2429910A1 (en) | 2003-05-27 | 2003-05-27 | System and method of query transformation |
| CA2,429,910 | 2003-05-27 | ||
| CA002468398A CA2468398C (en) | 2003-05-27 | 2004-05-27 | System and method of query transformation |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CA2468398A1 CA2468398A1 (en) | 2004-11-27 |
| CA2468398C true CA2468398C (en) | 2009-07-14 |
Family
ID=33453244
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CA002468398A Expired - Fee Related CA2468398C (en) | 2003-05-27 | 2004-05-27 | System and method of query transformation |
Country Status (1)
| Country | Link |
|---|---|
| CA (1) | CA2468398C (en) |
-
2004
- 2004-05-27 CA CA002468398A patent/CA2468398C/en not_active Expired - Fee Related
Also Published As
| Publication number | Publication date |
|---|---|
| CA2468398A1 (en) | 2004-11-27 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| EP1482426A2 (en) | System and method of query transformation | |
| AU2005239366B2 (en) | Partial query caching | |
| US8620888B2 (en) | Partitioning in virtual columns | |
| CA2484009C (en) | Managing expressions in a database system | |
| US6609131B1 (en) | Parallel partition-wise joins | |
| AU2002312104B2 (en) | Method and system for improving response time of a query for a partitioned database object | |
| US6370522B1 (en) | Method and mechanism for extending native optimization in a database system | |
| US7730055B2 (en) | Efficient hash based full-outer join | |
| Wang et al. | ATLAS: A small but complete SQL extension for data mining and data streams | |
| WO2010042238A1 (en) | System and method for data warehousing and analytics on a distributed file system | |
| US6957210B1 (en) | Optimizing an exclusion join operation using a bitmap index structure | |
| US7213011B1 (en) | Efficient processing of multi-column and function-based in-list predicates | |
| WO2018107942A1 (en) | System and method of adaptively partitioning data to speed up join queries on distributed and parallel database systems | |
| CA2468398C (en) | System and method of query transformation | |
| CA2468617C (en) | System and method of query transformation | |
| CA2468404A1 (en) | System and method of client/server aggregate transformation | |
| US7177856B1 (en) | Method for correlating data from external databases | |
| CA2468614A1 (en) | System and method of query transformation | |
| CA2468624A1 (en) | System and method of processing queries | |
| Ruberg et al. | A Cost Model for the Evaluation of Path Expressions in Distributed Object Databases | |
| CA2468403A1 (en) | System and method of query transformation | |
| Mazumdar | STREAMING DATA: STORAGE, QUERYING & ANALYSIS |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| EEER | Examination request | ||
| MKLA | Lapsed |