Database index design based upon request importance and the reuse and modification of similar existing indexes

Claim 1

For a database having plural tables, each of plural columns, stored in memory, a processor controlled method of generating indexes comprising: identifying importance values for individual database requests; and designing indexes to tables for the requests by identifying, by order of request importance, candidate indexes for requests, searching previously identified indexes for an index that is similar to each candidate index, and building upon previously identified indexes by reusing existing indexes and modifying existing indexes based upon match between indexes and upon importance values of requests for which the indexes are created.

Claim 33

A method of physically designing a database having plural tables, each of plural columns, the method comprising: A) identifying an absolute importance value for each database request based on the individual importance of the request, a transaction which includes the request and the program which includes the transaction, with greater weighting toward transaction importance and with greatest weighting toward program importance; B) in a table index design: 1. by order of request importance, identifying candidate indexes from the identified columns for individual contexts where: a. operator type of an associated operator is such that the database management system uses an index, b. column size is less than a threshold size, and c. column data type is permissible in an index; 2. identifying candidate index retrieval modes, including by sorting and hash, as a function of operator;

Claim 34

A method of physically designing a database having plural tables, each of plural columns, the method comprising: A) identifying an absolute importance value for each database request based on the individual importance of the request, a transaction which includes the request and the program which includes the transaction, with greater weighting toward transaction importance and with greatest weighting toward program importance; B) in a table index design: 1. by order of request importance, identifying candidate indexes from the identified columns for individual contexts where: a. operator type of an associated operator is such that the database management system uses an index, b. column size is less than a threshold size, and c. column data type is permissible in an index; 2. identifying candidate index retrieval modes, including by sorting and hash, as a function of operator;

Claim 37

A method of physically designing a database having plural tables, each of plural columns, the method comprising: A) identifying an absolute importance value for each database request based on the individual importance of the request, a transaction which includes the request and the program which includes the transaction, with greater weighting toward transaction importance and with greatest weighting toward program importance; B) in a table index design: 1. by order of request importance, identifying candidate indexes from the identified columns for individual contexts where: a. the table of the context has sufficient cardinality, b. operator type of an associated operator is such that the database management system uses an index, c. column size is less than a threshold size, and d. column data type is permissible in an index; 2. identifying candidate index retrieval modes, including by sorting and hash, as a function of operator; 3. with identification of each candidate index, searching previously identified indexes for a best existing index that is similar to the candidate index and, where no best existing index is located, creating a new existing index from the candidate index and, where a best existing index is located, choosing from the steps of a) creating a new index, b) revising the best existing index and c) modifying the best existing index to satisfy the context of the candidate index, choice of steps a, b and c being dependent on importance of and retrieval mode of the candidate index and the best existing index, importance of an index being derived from absolute importance of requests for which the index is identified; 4. identifying a benefit of an index, derived from importance of the requests for which the index is identified, and identifying a cost of the index, derived from importance of insert and delete requests on the indexed table and update requests on the indexed table and update requests on indexed columns, and pruning indexes where the benefit is less than the cost; 5. adding columns to indexes for index only retrieval; C) establishing hashed or sorted record placement strategy within tables, dependent on importance and retrieval mode of indexes to the table, data types within the table and table volatility; D) establishing cluster groups including: 1. clustering together tables and hashed indexes where the tables are placed via the hashed indexes and the tables are joined using columns in the hashed indexes; 2. clustering together tables and hashed indexes where the tables are placed via the hashed indexes and not clustered in step 1; 3. clustering tables and indexes in separate clusters where the tables are placed via sorted indexes; 4. establishing a cluster for each hash index not clustered in step 1 and 2; 5. establishing clusters for tables of importance over a threshold which are not clustered in steps 1 through 4; and 6. establishing clusters for tables of volatility over a threshold; and 7. establishing clusters for system files; and E) sizing the clusters of step D to fit available storage areas and assigning clusters to storage areas.

Claim 38

A method of physically designing a database having plural tables, each of plural columns, the method comprising: A) identifying an absolute importance value for each database request based on the individual importance of the request, a transaction which includes the request and the program which includes the transaction, with greater weighting toward transaction importance and with greatest weighting toward program importance; B) in a table index design: 1. by order of request importance, identifying candidate indexes from the identified columns for individual contexts where: a. the table of the context has sufficient cardinality, b. operator type of an associated operator is such that the database management system uses an index, c. column size is less than a threshold size, and d. column data type is permissible in an index; 2. identifying candidate index retrieval modes, including by sorting and hash, as a function of operator; 3. with identification of each candidate index, searching previously identified indexes for a best existing index that is similar to the candidate index and, where no best existing index is located, creating a new existing index from the candidate index and, where a best existing index is located, choosing from the steps of a) creating a new index, b) revising the best existing index and c) modifying the best existing index to satisfy the context of the candidate index, choice of steps a, b and c being dependent on importance of and retrieval mode of the candidate index and the best existing index, importance of an index being derived from absolute importance of requests for which the index is identified; 4. identifying a benefit of an index, derived from importance of the requests for which the index is identified, and identifying a cost of the index, derived from importance of insert and delete requests on the indexed table and update requests on the indexed table and update requests on indexed columns, and pruning indexes where the benefit is less than the cost; 5. adding columns to indexes for index only retrieval; C) establishing hashed or sorted record placement strategy within tables, dependent on importance and retrieval mode of indexes to the table, data types within the table and table volatility; D) establishing cluster groups including: 1. clustering together tables and hashed indexes where the tables are placed via the hashed indexes and the tables are joined using columns in the hashed indexes; 2. clustering together tables and hashed indexes where the tables are placed via the hashed indexes and not clustered in step 1; 3. clustering tables and indexes in separate clusters where the tables are placed via sorted indexes; 4. establishing a cluster for each hash index not clustered in step 1 and 2; 5. establishing clusters for tables of importance over a threshold which are not clustered in steps 1 through 4; and 6. establishing clusters for tables of volatility over a threshold; and 7. establishing clusters for system files; and E) sizing the clusters of step D to fit available storage areas and assigning clusters to storage areas.