System and method for automatically generating database queries

• US 8,019,750 B2
• Filed: 01/09/2006
• Issued: 09/13/2011
• Est. Priority Date: 05/26/2000
• Status: Expired due to Fees

• Current Assignee: Computer Associates Think Inc. (Broadcom, Inc.)
• Original Assignee: Computer Associates Think Inc. (Broadcom, Inc.)
• Inventors: Menon, Sreekumar, Werling, Thomas K., Lau, Joyce H., Vincent, John K., Vo, Hung-Vuong, Kosciusko, Edward
• Primary Examiner(s): Cottingham; John R.
• Assistant Examiner(s): Allen; Nicholas E
• Application Number:
  US11/327,945
• Publication Number:
  US 20070038618A1
• Time in Patent Office: 2,073 Days
• Field of Search: 707/718
• US Class Current: 707/718
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/24534 Query rewriting; Transformation

Abstract

A method of tuning a database query includes selecting a database query, parsing the selected database query to determine relationships between portions of the selected database query, selecting an optimization mode from a plurality of available optimization modes, tuning the selected database query by modifying, at least one portion of the selected database query based on the determined relationships and the selected optimization mode and displaying the modified database query.

Claims

1. A method of tuning a database query, comprising:

allowing a user to select a query of a database;

parsing the selected database query to determine that the database query includes a first operator;

selecting an optimization mode from a plurality of available optimization modes, wherein;

a first optimization mode is automatically selected if one or more statistics exist for a table identified by the database query, and

a second optimization mode is selected if one or more statistics do not exist for the table identified by the database query;

tuning automatically the selected database query based on the structure of the database and the selected optimization mode; and

displaying the tuned database query, and

wherein automatically tuning the selected database query comprises automatically rewriting the selected database query by converting the first operator to a second operator.

2. The method as recited in claim 1,

wherein the parsing determines tokens within the database query, tokens being words separated by delimiters.

3. The method as recited in claim 1, wherein the plurality of available optimization modes include Cost based and Rule based modes.

4. The method as recited in claim 3, wherein the Cost based modes include First_Rows mode and All_Rows mode.

5. The method as recited in claim 1, further comprising determining a cost associated with using the tuned database query.

6. The method as recited in claim 5, further comprising comparing a cost associated with using the selected database query to the cost associated with using the tuned database query.

7. The method as recited in claim 1, further comprising parsing the selected database query to determine whether the database query includes at least one subquery joined by at least one of a NOT EXISTS, a NOT IN and an ALL clause.

8. The method as recited in claim 7, further comprising prompting a user to select preferences to be used during tuning based on whether the database query includes at least one of a NOT EXISTS, a NOT IN and an ALL clause.

9. The method of claim 1, wherein tuning automatically the selected database query comprises changing a join order of the selected database query or moving a subquery within the selected database query.

10. The method of claim 1, further comprising displaying a diagram of each subquery included in the selected database query.

11. The method of claim 1, further comprising displaying a primary key for each table referenced in the selected database query.

12. The method as recited in claim 8, wherein the preferences include rewrite preferences for enabling a user to select at least one of a conversion of NOT EXISTS operators to a NOT IN operator and conversion of the selected database query to an outer join.

13. The method as recited in claim 8, wherein the preferences include rewrite preferences for enabling a user to select to convert subqueries joined by an ALL operator to a join or outer-join.

14. The method as recited in claim 8, wherein the preferences include rewrite preferences for enabling a user to select whether to use at least one of a NOT EXISTS operator and an outer-join to convert subqueries joined by a NOT In operator.

15. A computer storage medium including computer executable code for tuning a database query, comprising:

computer executable code for allowing a user to select a query of a database;

computer executable code for parsing the selected database query to determine that the database query includes a first operator;

computer executable code for selecting an optimization mode from a plurality of available optimization modes, wherein;

a first optimization mode is automatically selected if one or more statistics exist for a table identified by the database query, and

a second optimization mode is selected if one or more statistics do not exist for the table identified by the database query;

computer executable code for automatically tuning the selected database query based on the structure of the database and the selected optimization mode; and

computer executable code for displaying the tuned database query, and

wherein the computer executable code for automatically tuning the selected database query comprises computer executable code for automatically rewriting the selected database query by converting the first operator to a second operator.

16. The computer storage medium as recited in claim 15, wherein the parsing determines tokens within the database query, tokens being words separated by delimiters.

17. The computer storage medium as recited in claim 15, wherein the plurality of available optimization modes include Cost based and Rule based modes.

18. The computer storage medium as recited in claim 17, wherein the Cost based modes include First_Rows mode and All_Rows mode.

19. The computer storage-medium as recited in claim 15, further comprising code for determining a cost associated with using the tuned database query.

20. The computer storage medium as recited in claim 19, further comprising code for comparing a cost associated with using the selected database query to the cost associated with using the tuned database query.

21. The computer storage medium as recited in claim 15, further comprising code for parsing the selected database query to determine whether the database query includes at least one subquery joined by at least one of a NOT EXISTS, a NOT IN and an ALL clause.

22. The computer storage medium as recited in claim 21, further comprising code for prompting a user to select preferences to be used during tuning based on whether the database query includes at least one of a NOT EXISTS, a NOT IN and an ALL clause.

23. The computer storage medium as recited in claim 22, wherein the preferences include rewrite preferences for enabling a user to select at least one of a conversion of NOT EXISTS operators to a NOT IN operator and conversion of the selected database query to an outer join.

24. The computer storage medium as recited in claim 22, wherein the preferences include rewrite preferences for enabling a user to select to convert subqueries joined by an ALL operator to a join or outer-join.

25. The computer storage medium as recited in claim 22, wherein the preferences include rewrite preferences for enabling a user to select whether to use at least one of a NOT EXISTS operator and an outer-join to convert subqueries joined by a NOT In operator.

26. A programmed computer system for tuning a database query, comprising:

a display for displaying at least one of a plurality of database queries of a database to a user;

a user input allowing the user to select a database query from among the displayed database queries; and

a processor for parsing the selected database query to determine that the database query includes a first operator and for automatically tuning the selected database query based on the structure of the database, the tuned database query being displayed to the user via the display, wherein automatically tuning the selected database query comprises;

automatically selecting a first optimization mode if one or more statistics exist for a table identified by the database query, and

selecting a second optimization mode is selected if one or more statistics do not exist for the table identified by the database query, and

wherein automatically tuning the selected database query comprises automatically rewriting the selected database query by converting the first operator to a second operator.

27. The system as recited in claim 26, wherein the parsing determines tokens within the database query, tokens being words separated by delimiters.

28. The system as recited in claim 26, wherein the plurality of available optimization modes include Cost based and Rule based modes.

29. The system as recited in claim 28, wherein the Cost based modes include First_Rows mode and All_Rows mode.

30. The system as recited in claim 26, wherein the processor determines a cost associated with using the tuned database query.

31. The system as recited in claim 30, wherein the processor compares a cost associated with using the selected database query to the cost associated with using the tuned database query.

32. The system as recited in claim 26, wherein the processor parses the selected database query to determine whether the database query includes at least one subquery joined by at least one of a NOT EXISTS, a NOT IN and an ALL clause.

33. The system as recited in claim 32, wherein the processor prompts the user to select preferences to be used during tuning based on whether the database query includes at least one of a NOT EXISTS, a NOT IN and an ALL clause.

34. The system as recited in claim 33, wherein the preferences include rewrite preferences for enabling a user to select at least one of a conversion of NOT EXISTS operators to a NOT IN operator and conversion of the selected database query to an outer join.

35. The system as recited in claim 33, wherein the preferences include rewrite preferences for enabling a user to select to convert subqueries joined by an ALL operator to a join or outer-join.

36. The system as recited in claim 33, wherein the preferences include rewrite preferences for enabling a user to select whether to use at least one of a NOT EXISTS operator and an outer-join to convert subqueries joined by a NOT In operator.

37. A method of tuning a database query, comprising:

allowing a user to select a query of a database;

parsing the selected database query to determine that the database query includes a first operator;

selecting an optimization mode from a plurality of available optimization modes;

tuning automatically the selected database query based on the structure of the database and the selected optimization mode; and

displaying the tuned database query,

wherein automatically tuning the selected database query comprises automatically rewriting the selected database query by converting the first operator to a second operator, and

wherein selecting the optimization mode from the plurality of available optimization modes comprises;

determining whether one or more statistics exist for a table identified by the query;

automatically selecting a first optimization mode if the one or more statistics exist for the table; and

automatically selecting a second optimization mode if the one or more statistics do not exist for the table.

38. A method of tuning a database query, comprising:

allowing a user to select a query of a database;

parsing the selected database query to determine that the database query includes a first operator;

selecting an optimization mode from a plurality of available optimization modes;

tuning automatically the selected database query based on the structure of the database and the selected optimization mode; and

displaying the tuned database query,

wherein automatically tuning the selected database query comprises automatically rewriting the selected database query by converting the first operator to a second operator, and

wherein selecting the optimization mode from the plurality of available optimization modes comprises;

determining whether one or more statistics exist for a table identified by the query;

automatically selecting a first optimization mode if the one or more statistics exist for the table; and

receiving user preferences for selecting a second optimization mode if the one or more statistics do not exist for the table.