Transforming SQL Queries with Table Subqueries

• US 20070219943A1
• Filed: 03/19/2006
• Published: 09/20/2007
• Est. Priority Date: 03/19/2006
• Status: Abandoned Application

• Current Assignee: Alphonza Draughn, Optisoftware Incorporated
• Original Assignee: Alphonza Draughn, Optisoftware Incorporated
• Inventors: Draughn, Alphonza
• Application Number:
  US11/308,361
• Publication Number:
  US 20070219943A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24535 of sub-queries or views

Abstract

A computer automated technique for rewriting SQL with table subqueries into more optimal table expression equivalents without derived tables. The context of the table subqueries, including any join and filter predicates are moved to the encompassing outer query. The advantage of this technique is to provide optimized source SQL to the relational database optimizers.

Claims

1. A computer-implemented method for transformation of an SQL query comprising Table Subqueries comprising the steps of:

a) identifying each table subquery that does not return any grouped or aggregated rows;

b) iteratively removing each table subquery and replacing with a logically equivalent table expression with no intervening derived table.

2. The computer-implemented method of claim 1 further comprising determining the eligibility of each table subquery for transformation by searching within the table subquery for the existence of a “

Group By”

clause or any of following Aggregate or Regression functions;

AVG, CORRELATION, COUNT, COUNT_BIG, COVARIANCE, GROUPING, MAX, MIN, STDDEV, SUM, and VARIANCE.

3. The computer-implemented method of claim 1 further comprising moving all of the Where Clause search conditions from each eligible table subquery to the Where clause of the outer encompassing Query, preserving the application of specified logical operators to the result of each predicate.

4. The computer-implemented method of claim 1 further comprising moving all of the From Clause table expressions from the table subquery to the From Clause of the outer encompassing Query, preserving the application of specified logical operators to the result of each Join condition.

5. The computer-implemented method of claim 4, wherein a Correlation name is associated with the table subquery comprising replacing the table subquery Correlation name with original table subquery “

From clause”

Correlation names if used in the Select clause, search or Join conditions in the outer query.

6. The computer-implemented method of claim 1, further comprising removing the remaining parenthesis of an empty table subquery.

7. A computer program product comprising a computer usable medium tangibly embodying computer readable program code means for transforming an SQL query, the computer readable program code means comprising code means for carrying out the steps of the method of claim 1.

8. A computer program product comprising a computer usable medium tangibly embodying computer readable program code means for transforming an SQL query, the computer readable program code means comprising code means for carrying out the steps of the method of claim 2.

9. A computer program product comprising a computer usable medium tangibly embodying computer readable program code means for transforming an SQL query, the computer readable program code means comprising code means for carrying out the steps of the method of claim 3.

10. A computer program product comprising a computer usable medium tangibly embodying computer readable program code means for transforming an SQL query, the computer readable program code means comprising code means for carrying out the steps of the method of claim 4.

11. A computer program product comprising a computer usable medium tangibly embodying computer readable program code means for transforming an SQL query, the computer readable program code means comprising code means for carrying out the steps of the method of claim 5.

12. A computer program product comprising a computer usable medium tangibly embodying computer readable program code means for transforming an SQL query, the computer readable program code means comprising code means for carrying out the steps of the method of claim 6.