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. Well-designed indexes can reduce physical or logical I/O operations.
Tables in OceanBase Database are clustered index tables. If you do not specify a primary key for a table, the system automatically generates a hidden primary key for the table.
The advantages, disadvantages, and usage notes of indexes are as follows:
Advantages:
With indexes, the data query speed is increased.
With indexes, optimization hiders can be used during queries to improve the system performance.
Disadvantages:
Index creation and maintenance are time-consuming, and the amount of time consumed increases as the data volume grows.
When you add, delete, or modify data in tables, you must also dynamically maintain indexes, which slows down data maintenance.
Usage notes:
Do not create too many indexes on a table that is frequently updated. Index the columns that are often queried.
Do not use indexes for tables with a 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.
If the maintenance cost arising from a new index is much greater than the cost of retrieval-based performance improvement, the index should not be created.