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 20020010701A1
• Filed: 07/06/2001
• Published: 01/24/2002
• Est. Priority Date: 07/20/1999
• Status: Active Grant

• Current Assignee: Google LLC (Alphabet Inc.)
• Original Assignee: PLATINUM Technology IP, Inc. (Broadcom, Inc.)
• Inventors: Menon, Sreekumar, Vo, Hung-Vuong, Kosciuszko, Edward
• Granted Patent: US 6,898,588 B2
• Time in Patent Office: Days
• US Class Current: 707/100
• 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, a method for viewing changes to an original optimization plan for a query having a reference to an original table with data stored in a database, the method comprising:

copying the original table excluding the data to define a virtual table;

providing 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;

determining a new optimization plan for the query; and

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

3. The method of claim 2 further comprising, before comparing the new and the original optimization plans:

replacing, in the new optimization plan, the reference to the virtual table with the reference to the original table; and

displaying, to a user, the new optimization plan with the reference to the original table.

4. In a database management system, a method for viewing changes to an original optimization plan for a query having:

(i) a reference to an original table with data stored in a database, and (ii) a reference to an original index associated with the original table, the method comprising;

copying the original table excluding the data to define a virtual table;

changing the original index to define a virtual index associated with the virtual table;

determining a new optimization plan for the query; and

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

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

replacing, in the new optimization plan, the references to the virtual table and the virtual index with, respectively, the references to the original table and original index; and

displaying, to a user, the new optimization plan with the references to the original table and the original index.

7. In a database management system, a method for viewing changes to an original optimization plan for a query having:

(i) a reference to an original table with data stored in a database, and (ii) a reference to an original index associated with the original table, the method comprising;

copying the original table 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;

deleting, from the query, the reference to the virtual table;

determining a new optimization plan for the query; and

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

9. The method of claim 8 further comprising, before comparing the new and the original optimization plans:

replacing, in the new optimization plan, the reference to the virtual table with the reference to the original table; and

displaying, to a user, the new optimization plan with the reference to the original table.