Dynamic selection of optimal grouping sequence at runtime for grouping sets, rollup and cube operations in SQL query processing

Abstract

A method, apparatus, and article of manufacture for optimizing a query in a computer system. During compilation of the query, a GROUP BY clause with one or more GROUPING SETS, ROLLUP or CUBE operations is maintained in its original form until after query rewrite. The GROUP BY clause with the GROUPING SETS, ROLLUP or CUBE operations is then translated into a plurality of levels having one or more grouping sets. After compilation of the query, a grouping sets sequence is dynamically determined for the GROUP BY clause with the GROUPING SETS, ROLLUP or CUBE operations based on intermediate grouping sets, in order to optimize the grouping sets sequence. The execution of the grouping sets sequence is optimized by selecting a smallest grouping set from a previous one of the levels as an input to a grouping set on a next one of the levels. Finally, a UNION ALL operation is performed on the grouping sets.

1 . A method of optimizing a query in a computer system, the query being performed by the computer system to retrieve data from a database stored on the computer system, the method comprising: 
 (a) during compilation of the query, maintaining a GROUP BY clause with one or more GROUPING SETS, ROLLUP or CUBE operations in its original form until after query rewrite; and    (b) translating the GROUP BY clause with the GROUPING SETS, ROLLUP or CUBE operations into a plurality of levels having one or more grouping sets.    
   
   
       2 . The method of  claim 1 , further comprising: 
 (1) after compilation of the query, dynamically determining a grouping sets sequence for the GROUP BY clause with the GROUPING SETS, ROLLUP or CUBE operations based on intermediate grouping sets, in order to optimize the grouping sets sequence.    (2) optimizing execution of the grouping sets sequence by selecting a smallest grouping set from a previous one of the levels as an input to a grouping set on a next one of the levels.    
   
   
       3 . The method of  claim 2 , further comprising performing a UNION ALL operation on the grouping sets.  
   
   
       4 . A computer-implemented apparatus for optimizing a query, the query being performed to retrieve data from a database, the apparatus comprising: 
 (a) a computer system;    (b) logic, performed by the computer system, for 
 (1) during compilation of the query, maintaining a GROUP BY clause with one or more GROUPING SETS, ROLLUP or CUBE operations in its original form until after query rewrite; and  
 (2) translating the GROUP BY clause with the GROUPING SETS, ROLLUP or CUBE operations into a plurality of levels having one or more grouping sets.  
   
   
   
       5 . The apparatus of  claim 4 , further comprising logic for: 
 (1) after compilation of the query, dynamically determining a grouping sets sequence for the GROUP BY clause with the GROUPING SETS, ROLLUP or CUBE operations based on intermediate grouping sets, in order to optimize the grouping sets sequence.    (2) optimizing execution of the grouping sets sequence by selecting a smallest grouping set from a previous one of the levels as an input to a grouping set on a next one of the levels.    
   
   
       6 . The apparatus of  claim 5 , further comprising logic for performing a UNION ALL operation on the grouping sets.  
   
   
       7 . An article of manufacture embodying logic for performing a method for optimizing a query, the query being performed by a computer system to retrieve data from a database stored in a data storage device coupled to the computer system, the method comprising: 
 (a) during compilation of the query, maintaining a GROUP BY clause with one or more GROUPING SETS, ROLLUP or CUBE operations in its original form until after query rewrite; and    (b) translating the GROUP BY clause with the GROUPING SETS, ROLLUP or CUBE operations into a plurality of levels having one or more grouping sets.    
   
   
       8 . The article of manufacture of  claim 7 , further comprising: 
 (1) after compilation of the query, dynamically determining a grouping sets sequence for the GROUP BY clause with the GROUPING SETS, ROLLUP or CUBE operations based on intermediate grouping sets, in order to optimize the grouping sets sequence.    (2) optimizing execution of the grouping sets sequence by selecting a smallest grouping set from a previous one of the levels as an input to a grouping set on a next one of the levels.    
   
   
       9 . The article of manufacture of  claim 8 , further comprising performing a UNION ALL operation on the grouping sets.