Systems and methods for database index optimization
In one embodiment, a method includes selecting a plurality of indexes of a database table, receiving an indication of a set of index pairs in the plurality of indexes that are deemed to at least partially overlap, and determining a degree of overlap between the indexes of each index pair. The method further includes determining a suggested merge relationship between the indexes of each of the index pairs. The method also includes generating an index model comprising interconnected index objects. The interconnected index objects represent the indexes of the index pairs. Interconnections between the interconnected index objects reflect the suggested merge relationship between the indexes of each index pair. Moreover, the method includes outputting a graphical representation of the index model. Additionally, the method includes allowing a user to graphically select index pairs to merge in a system-determined order.
1. A method comprising, by a computer system:
selecting a plurality of indexes of a database table;
receiving an indication of a set of index pairs in the plurality of indexes that are deemed to at least partially overlap;
determining a degree of overlap between the indexes of each index pair of the set of index pairs;
determining a suggested merge relationship between the indexes of each of the index pairs, wherein the suggested merge relationship is variable based, at least in part, on the degree of overlap, wherein, for at least one index pair of the set of index pairs, the indexes of the at least one index pair include some but not all of the same columns;
generating an index model comprising interconnected index objects, the interconnected index objects representing the indexes of the index pairs, wherein interconnections between the interconnected index objects reflect the suggested merge relationship between the indexes of each index pair;
outputting a graphical representation of the index model;
allowing a user to graphically select index pairs to merge in a system-determined order, wherein the system-determined order is based, at least in part, on a type of each of the suggested merge relationships;
merging the at least one index pair responsive to user authorization, the at least one index pair comprising a surviving index and a non-surviving index, the merging comprising adding, to the surviving index, columns of the non-surviving index that are missing from the surviving index;
automatically updating the index model to reflect the merged at least one index pair; and
automatically outputting an updated graphical representation of the automatically updated index model.
2. The method of claim 1 , wherein the merging comprises a simulation that results in no changes to the database table.
3. The method of claim 1 , comprising, responsive to the automatically outputting, again allowing the user to graphically select index pairs to merge in the system-determined order.
4. The method of claim 1 , wherein the index model comprises one or more non-interconnected index objects, the one or more non-interconnected index objects representing one or more indexes of the plurality of indexes that are not included in any of the index pairs.
5. The method of claim 1 , wherein the allowing of the user to graphically select index pairs to merge in the system-determined order comprises allowing the user to make decisions regarding index pairs to merge in the following order:
each index pair that is a full sequence match;
each index pair that is a partial sequence match;
each index pair that is a full-sequence non-directional match; and
each index pair that is a partial-sequence non-directional match.
6. The method of claim 1 , wherein the receiving of the indication results from execution of a database script.
7. The method of claim 1 , wherein the suggested merge relationship is selected from the group consisting of: a full sequence match, a partial sequence match, a full sequence non-directional match, and a partial sequence non-directional match.
8. An information handling system comprising a computer processor and memory, wherein the computer processor and memory in combination are operable to implement a method comprising:
selecting a plurality of indexes of a database table;
receiving an indication of a set of index pairs in the plurality of indexes that are deemed to at least partially overlap;
determining a degree of overlap between the indexes of each index pair of the set of index pairs;
determining a suggested merge relationship between the indexes of each of the index pairs, wherein the suggested merge relationship is variable based, at least in part, on the degree of overlap, wherein, for at least one index pair of the set of index pairs, the indexes of the at least one index pair include some but not all of the same columns;
generating an index model comprising interconnected index objects, the interconnected index objects representing the indexes of the index pairs, wherein interconnections between the interconnected index objects reflect the suggested merge relationship between the indexes of each index pair;
outputting a graphical representation of the index model;
allowing a user to graphically select index pairs to merge in a system-determined order, wherein the system-determined order is based, at least in part, on a type of each of the suggested merge relationships;
merging the at least one index pair responsive to user authorization, the at least one index pair comprising a surviving index and a non-surviving index, the merging comprising adding, to the surviving index, columns of the non-surviving index that are missing from the surviving index;
automatically updating the index model to reflect the merged at least one index pair; and
automatically outputting an updated graphical representation of the automatically updated index model.
9. The information handling system of claim 8 , wherein the merging comprises a simulation that results in no changes to the database table.
10. The information handling system of claim 8 , the method comprising, responsive to the automatically outputting, again allowing the user to graphically select index pairs to merge in the system-determined order.
11. The information handling system of claim 8 , wherein the index model comprises one or more non-interconnected index objects, the one or more non-interconnected index objects representing one or more indexes of the plurality of indexes that are not included in any of the index pairs.
12. The information handling system of claim 8 , wherein the allowing of the user to graphically select index pairs to merge in the system-determined order comprises allowing the user to make decisions regarding index pairs to merge in the following order:
each index pair that is a full sequence match;
each index pair that is a partial sequence match;
each index pair that is a full-sequence non-directional match; and
each index pair that is a partial-sequence non-directional match.
13. The information handling system of claim 8 , wherein the receiving of the indication results from execution of a database script.
14. The information handling system of claim 8 , wherein the suggested merge relationship is selected from the group consisting of: a full sequence match, a partial sequence match, a full sequence non-directional match, and a partial sequence non-directional match.
15. A computer-program product comprising a non-transitory computer-usable medium having computer-readable program code embodied therein, the computer-readable program code adapted to be executed to implement a method comprising:
selecting a plurality of indexes of a database table;
receiving an indication of a set of index pairs in the plurality of indexes that are deemed to at least partially overlap;
determining a degree of overlap between the indexes of each index pair of the set of index pairs;
determining a suggested merge relationship between the indexes of each of the index pairs, wherein the suggested merge relationship is variable based, at least in part, on the degree of overlap, wherein, for at least one index pair of the set of index pairs, the indexes of the at least one index pair include some but not all of the same columns;
generating an index model comprising interconnected index objects, the interconnected index objects representing the indexes of the index pairs, wherein interconnections between the interconnected index objects reflect the suggested merge relationship between the indexes of each index pair;
outputting a graphical representation of the index model;
allowing a user to graphically select index pairs to merge in a system-determined order, wherein the system-determined order is based, at least in part, on a type of each of the suggested merge relationships;
merging the at least one index pair responsive to user authorization the at least one index pair comprising a surviving index and a non-surviving index, the merging comprising adding, to the surviving index, columns of the non-surviving index that are missing from the surviving index;
automatically updating the index model to reflect the merged at least one index pair; and
automatically outputting an updated graphical representation of the automatically updated index model.
16. The computer-program product of claim 15 , wherein the merging comprises a simulation that results in no changes to the database table.
17. The computer-program product of claim 15 , the method comprising, responsive to the automatically outputting, again allowing the user to graphically select index pairs to merge in the system-determined order.