An index is an optional structure that you can create in a table to accelerate queries on specified columns. You can create an index on a column based on your business requirements. This topic describes the advantages and disadvantages of using indexes, as well as the availability and visibility of indexes.
Advantages and disadvantages of indexes
The advantages of indexes are as follows:
You can accelerate queries without modifying SQL statements. An index allows you to scan only the data that you need.
An index stores fewer columns, which reduces I/O operations during queries.
The disadvantages of indexes are as follows:
You need to have a deep understanding of your business and data model to decide on which columns to create indexes.
When your business changes, you need to reevaluate whether the existing indexes still meet your requirements.
Indexes consume performance when you write data to a database.
Indexes occupy resources such as memory and disks.
Availability and visibility of indexes
Availability of indexes
If you drop a partition without specifying the rebuild index option, the index is marked as UNUSABLE, which indicates that the index is unavailable. In DML operations, indexes are not maintained, and the optimizer ignores the index.
Visibility of indexes
The visibility of an index refers to whether the optimizer ignores the index. If an index is invisible, the optimizer ignores it, but indexes are maintained during DML operations. Before you delete an index, you can set the index to invisible to observe its impact on your business. If the index has no impact, you can delete it.
Relationship between indexes and keys
A key is a set of columns or expressions on which you can create an index. However, an index and a key are different. An index is an object stored in a database, whereas a key is a logical concept.
Index types
Global and local indexes
Global index
A global index is an index created on a table that contains data of all partitions of the table. Global indexes are suitable for queries that span partitions.
Local index
A local index is an index created on each partition of a table that contains data of only the partition. Local indexes are suitable for queries that are performed within a partition.
Unique and non-unique indexes
Unique index
A unique index is an index that contains unique values in the indexed column. Each index value corresponds to only one record. A unique index ensures the uniqueness of a field in a table.
Non-unique index
A non-unique index is an index that allows duplicate values in the indexed column. That is, multiple index values can correspond to one or multiple records. A non-unique index accelerates queries on the indexed column but does not ensure the uniqueness of the indexed column.
Spatial index
A spatial index is a special index type that accelerates queries on spatial data. It is mainly used in geographic information systems (GIS) and spatial data processing. A spatial index allows you to efficiently query spatial objects such as points, lines, and areas and analyze spatial relationships.
Function index
A function index is an index created based on the values of one or more columns in a table. A function index is an optimization technique that allows you to quickly locate matching function values during queries, thus avoiding repeated calculations and improving query efficiency.