Indexes are optional structures. You can create indexes on specific columns so that SQL queries in these columns are executed more quickly. This topic describes the advantages and disadvantages of indexes, and the usability and visibility of indexes.
Advantages and disadvantages
Indexes have the following advantages:
Indexes allow you to speed up SQL queries without the need to modify SQL statements, because only the data that you require is scanned.
Indexes usually store a small number of columns. This reduces the query I/O overheads.
Indexes have the following disadvantages:
To create indexes on proper columns, you must have a deep understanding of your business and the business data model.
If any change occurs in your business, you must evaluate whether the existing indexes can still meet your business requirements.
Data stored in index tables must be maintained when incremental data is written to the table. This affects the database performance.
Index tables consume resources, such as memory and disk resources.
Usability and visibility
Usability of indexes
When you drop a partition, if you do not specify the rebuild index field, the corresponding index is marked as unusable. In this case, the index does not need to be maintained in DML operations and is ignored by the optimizer.
Visibility of indexes
The visibility of an index determines whether the index is ignored by the optimizer. If an index is invisible, it is ignored by the optimizer but needs to be maintained in DML operations. Before you drop an index, you can make it invisible to test the effect on your business. If your business is not affected, you can drop the index.
Relationships between indexes and keys
A key is one or more columns or expressions on which you can create indexes. A key is a logical concept, whereas an index is an object stored in a database.