After you create an index, OceanBase Database automatically maintains the index. All Data Manipulation Language (DML) operations update corresponding data records in the index table in real time. The optimizer automatically determines whether to use the index based on your queries. This topic describes how to scan indexes.
When an SQL statement specifies that the conditional predicate is intended to query an indexed column, the database automatically extracts the conditional predicate as the query range, which refers to the start key and end key for querying the index table. The database can locate the start position of data based on the start key, and locate the end position of data based on the end key. The data between the start and end positions needs to be scanned in this query.
OceanBase Database stores the data of an index table by using a MemTable and an SSTable. The MemTable is organized based on the B+ tree structure, and the SSTable is organized based on the macroblock structure. Both the MemTable and SSTable are scanned in the preceding process to obtain corresponding data. Data rows of the MemTable and SSTable are combined into final data rows.
To sum up, OceanBase Database queries data of an index table in the following steps:
Query data in the MemTable.
Query data in the SSTable.
Merge data in the MemTable and SSTable into complete rows.
If an SQL query statement involves only a column in an index table, the database queries the MemTable and SSTable of the index table based on the specified column according to the preceding query process, to obtain complete data rows.
If an SQL query statement involves a column in an index table and other columns, the database queries related rows based on the index table, and then queries the required data columns in the primary table based on primary keys in the rows according to the preceding query process. This process is also known as table access by index primary key.