Method and apparatus for viewing the effect of changes to an index for a database table on an optimization plan for a database query

• US 6,560,593 B1
• Filed: 07/20/1999
• Issued: 05/06/2003
• Est. Priority Date: 07/20/1999
• Status: Expired due to Term

• Current Assignee: Google LLC (Alphabet Inc.)
• Original Assignee: Computer Associates Think Inc. (Broadcom, Inc.)
• Inventors: Menon, Sreekumar, Vo, Hung-Vuong, Kosciuszko, Edward
• Primary Examiner(s): Coby, Frantz
• Application Number:
  US09/356,797
• Time in Patent Office: 1,386 Days
• Field of Search: 707/2, 707/102
• US Class Current: 1/1
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A method and apparatus for viewing the effect of changes to an index for a database table on an optimization plan for a database query, such as an SQL statement. A virtual table is created which mimics the structure of an original table on the database. The virtual table is created by copying the original table, excluding any data in the original table. Any existing original indexes associated with original table are copied to define a virtual index associated with the virtual table. References in the query to the original table are replaced with references to the virtual table. The database management system then determines a new optimization plan for the query.

Claims

1. In a database management system including an original table having structural information and data stored in a database, a method for determining changes to an original optimization plan for a query to the database, the query including a reference to the original table, the method comprising:

copying the original table, including the structural information but excluding the data, to define a virtual table;

replacing, in the query, the reference to the original table with a reference to the virtual table; and

determining a new optimization plan for the query.

2. The method of claim 1 wherein the structural information of the original table includes statistical information.

3. The method of claim 2 wherein the statistical information includes a number of rows of the original table.

4. The method of claim 1 further comprising comparing the new optimization plan with the original optimization plan.

5. The method of claim 1 further comprising, before comparing the new optimization plan and the original optimization plan:

replacing, in the new optimization plan, a reference to the virtual table with the reference to the original table.

6. The method of claim 5 further comprising, before comparing the new optimization plan and the original optimization plan:

displaying the new optimization plan with the reference to the original table.

7. In a database management system including an original table having structural information and data stored in a database, a method for determining changes to an original optimization plan for a query to the database, the query including a reference to the original table, the method comprising:

copying the original table, including the structural information but excluding the data, to define a virtual table;

defining a virtual index associated with the virtual table;

replacing, in the query, the reference to the original table with a reference to the virtual table;

adding, to the query, a reference to the virtual index; and

determining a new optimization plan for the query.

8. The method of claim 7 wherein defining the virtual index includes copying an original index associated with the original table.

9. The method of claim 8 wherein defining the virtual index further includes changing the original index.

10. The method of claim 8 wherein copying the original index includes copying statistical information associated with the original index.

11. The method of claim 7 further comprising comparing the new optimization plan with the original optimization plan.

12. The method of claim 7 further comprising, before comparing the new optimization plan and the original optimization plan:

replacing, in the new optimization plan, a reference to the virtual table with the reference to the original table.

13. The method of claim 12 further comprising, before comparing the new optimization plan and the original optimization plan:

displaying the new optimization plan with the reference to the original table.

14. A processor readable storage medium having processor readable code embodied on the processor readable storage medium, the processor readable code for programming a processor to perform a method for determining changes to an original optimization plan for a query to a database, the query including a reference to an original table having structural information and data stored in a database, the method comprising:

copying the original table, including the structural information but excluding the data, to define a virtual table;

replacing, in the query, the reference to the original table with a reference to the virtual table; and

determining a new optimization plan for the query.

15. The processor readable storage medium of claim 14, wherein the structural information of the original table includes statistical information.

16. The processor readable storage medium of claim 15 wherein the statistical information includes a number of rows of the original table.

17. The processor readable storage medium of claim 14, the method further comprising comparing the new optimization plan with the original optimization plan.

18. The processor readable storage medium of claim 14, the method further comprising, before comparing the new optimization plan and the original optimization plan:

replacing, in the new optimization plan, a reference to the virtual table with the reference to the original table.

19. The processor readable storage medium of claim 18, the method further comprising, before comparing the new optimization plan and the original optimization plan:

displaying the new optimization plan with the reference to the original table.

20. A processor readable storage medium having processor readable code embodied on the processor readable storage medium, the processor readable code for programming a processor to perform a method for determining changes to an original optimization plan for a query to a database, the query including a reference to an original table having structural information and data stored in a database, the method comprising:

copying the original table, including the structural information but excluding the data, to define a virtual table;

defining a virtual index associated with the virtual table;

replacing, in the query, the reference to the original table with a reference to the virtual table;

adding, to the query, a reference to the virtual index; and

determining a new optimization plan for the query.

21. The processor readable storage medium of claim 20, wherein defining the virtual index includes copying an original index associated with the original table.

22. The processor readable storage medium of claim 21, wherein defining the virtual index further includes changing the original index.

23. The processor readable storage medium of claim 21, wherein copying the original index includes copying statistical information associated with the original index.

24. The processor readable storage medium of claim 20, the method further comprising comparing the new optimization plan with the original optimization plan.

25. The processor readable storage medium of claim 20, the method further comprising, before comparing the new optimization plan and the original optimization plan:

replacing, in the new optimization plan, a reference to the virtual table with the reference to the original table.

26. The processor readable storage medium of claim 25, the method further comprising, before comparing the new optimization plan and the original optimization plan:

displaying the new optimization plan with the reference to the original table.

27. In a database management system including an original table having structural information and data stored in a database, an apparatus for determining changes to an original optimization plan for a query to the database, the query including a reference to the original table, the apparatus comprising:

means for copying the original table, including the structural information but excluding the data, to define a virtual table;

means for replacing, in the query, the reference to the original table with a reference to the virtual table; and

means for determining a new optimization plan for the query.

28. The apparatus of claim 27 wherein the structural information of the original table includes statistical information.

29. The apparatus of claim 28 wherein the statistical information includes a number of rows of the original table.

30. The apparatus of claim 27 further comprising:

means for comparing the new optimization plan with the original optimization plan.

31. The apparatus of claim 27 further comprising:

means for replacing, in the new optimization plan, a reference to the virtual table with the reference to the original table.

32. The apparatus of claim 31 further comprising:

means for displaying the new optimization plan with the reference to the original table.

33. In a database management system including an original table having structural information and data stored in a database, an apparatus for determining changes to an original optimization plan for a query to the database, the query including a reference to the original table, the apparatus comprising:

means for copying the original table, including the structural information but excluding the data, to define a virtual table;

means for defining a virtual index associated with the virtual table;

means for replacing, in the query, the reference to the original table with a reference to the virtual table;

means for adding, to the query, a reference to the virtual index; and

means for determining a new optimization plan for the query.

34. The apparatus of claim 33 wherein the means for defining the virtual index includes means for copying an original index associated with the original table.

35. The apparatus of claim 34 wherein the means for defining the virtual index further includes means for changing the original index.

36. The apparatus of claim 34 wherein the means for copying the original index includes means for copying statistical information associated with the original index.

37. The apparatus of claim 33 further comprising:

means for comparing the new optimization plan with the original optimization plan.

38. The apparatus of claim 33 further comprising:

means for replacing, in the new optimization plan, a reference to the virtual table with the reference to the original table.

39. The method of claim 38 further comprising:

means for displaying the new optimization plan with the reference to the original table.