After you create a table, you can create an index on one or more columns in the table to speed up the execution of SQL statements on the table.
Definition
An index is a database structure created for a table to sort data in one or more columns of the table in a specific order. It improves the query speed and reduces the performance overhead of database systems.
Benefits
With indexes, the data query speed is increased.
With indexes, optimizers can be used during queries to improve the system performance.
Usage notes
Create indexes on fields that are frequently used for queries, but do not create excess indexes on tables that are frequently updated.
Do not use indexes for tables with small data volume. This is because the time consumed in querying all data may be shorter than that in traversing the indexes. In this case, indexes may not be useful.
Do not create indexes when the modification performance is far greater than the query performance.
Index attributes
Tables in OceanBase Database are index-organized tables (IOTs). If you do not specify a primary key for a table, the system automatically generates a hidden primary key for the table.
OceanBase Database allows you to create an index on both partitioned and non-partitioned tables. An index can be either a local index or a global index. In addition, it can either be a unique index or a normal index.
A local unique index on a partitioned table must include a partitioning key of the table.
Create efficient indexes
An index table is an entity table like a general data table. When a data table is updated, the corresponding index table is updated first.
Indexes must be created on all the columns to be queried. This can reduce the number of rows returned from table access by index primary key.
Indexes of equivalent conditions should always be placed in the front of the index table.
Indexes for large data filtering and sorting should be placed in the front of the index table.