Patent ID: 8055646
Filing Date: 2011-11-08
Classification: G06F

Abstract:
1. A method for preventing redundant indexes in a database management system (DBMS), said method implemented by execution of the DBMS on a processor of a computer system that includes data storage media, said method comprising: receiving a create-index command that specifies a new index to be created for retrieving data from a table created by the DBMS, wherein the create-index command specifies a redundancy check to be performed between a first set of keys for the new index at a second set of keys in each existing index in a set of existing indexes in conjunction with an action parameter in the create-index command, wherein the action parameter comprises a REPORT value or a PREVENT value, and wherein the storage media stores the table and an index database comprising the set of existing indexes, each existing index in the set of existing indexes being a data structure configured to retrieve data from the table; responsive to receiving the create-index command, determining whether the creation of the new index will cause at least one redundant index to exist based on the first set of keys for the new index and the second set of keys in each existing index in the set of existing indexes, wherein the at least one redundant index is either the new index or at least one existing index in the set of existing indexes; responsive to determining the existence of the at least one redundant index, ascertaining whether the action parameter comprises the REPORT value or the PREVENT value; responsive to the action parameter comprising the REPORT value, storing and/or displaying an identification of the at least one redundant index, creating the new index, and incorporating the new index in the index database; responsive to the action parameter comprising the PREVENT value and responsive to the at least one redundant index being redundant in view of the new index and the new index failing to be redundant in view of the at least one redundant index, deleting each existing index identified as the at least one redundant index from the index database, creating the new index, and incorporating the new index in the index database; and responsive to the action parameter comprising the PREVENT value and responsive to the at least one redundant index being redundant in view of each existing index in the set of existing indexes, inhibiting creation of the new index resulting the index database failing to comprise the new index.