Getpage-workload based index optimizer
First Claim
1. A method for selecting optimized indexes for a relational database, comprising:
- (a) collecting data about all database queries in a representative time interval;
(b) determining database tables which have been queried during the representative time period so as to have triggered activity above a predetermined amount;
(c) computing an optimized index set based on table read/change activity; and
(d) recommending the optimized index set to a user.
1 Assignment
0 Petitions
Accused Products
Abstract
The index optimization (XOP) program is a method to recommend new or improved performance indexes for use by an IBM mainframe DB2 database server for a given set of SQL statements. The recommendations are calculated based on a single call of the DB2 optimizer for each SQL statement with the existing index set, parsing of the SQL statements, the filter factors of the SQL statement predicates, and the getpage workload of an SQL statement. The getpage workload of an SQL statement is the read/change activity on each table and each index currently used for all invocations of an SQL statement. The XOP program computes new and improved performance indexes based on the getpage workload of all relevant SQL statements and does not use the DB2 optimizer to determine the usefulness of potential indexes.
-
Citations
25 Claims
-
1. A method for selecting optimized indexes for a relational database, comprising:
-
(a) collecting data about all database queries in a representative time interval;
(b) determining database tables which have been queried during the representative time period so as to have triggered activity above a predetermined amount;
(c) computing an optimized index set based on table read/change activity; and
(d) recommending the optimized index set to a user. - View Dependent Claims (2, 3, 4, 5, 6, 7, 8, 9)
-
-
10. An apparatus for determining an optimized index set for a database, comprising:
-
(a) one or more processors; and
(b) a memory coupled to the one or more processors and containing one or more sequences of one or more instructions which, when executed by the one or more processors, cause the one or more processors to perform the steps of;
(i) collecting data about all database queries in a representative time interval;
(ii) determining database tables which have been queried during the representative time period so as to have triggered activity above a predetermined amount;
(iii) computing an optimized index set based on table read/change activity; and
(iv) recommending the optimized index set to a user. - View Dependent Claims (11, 12, 13, 14, 15, 16, 17)
-
-
18. A computer-readable medium carrying one or more sequences of one or more instructions for determining, in a relational database management system, an optimized set of database indexes, the one or more sequences of one or more instructions including instructions which, when executed by one or more processors, cause the one or more processors to perform the steps of:
-
(i) collecting data about all database queries in a representative time interval;
(ii) determining database tables which have been queried during the representative time period so as to have triggered activity above a predetermined amount;
(iii) computing an optimized index set based on table read/change activity; and
(iv) recommending the optimized index set to a user. - View Dependent Claims (19, 20, 21, 22, 23, 24, 25)
-
Specification