Once an index is created, OceanBase Database automatically maintains it, and all Data Manipulation Language (DML) operations will update the corresponding data records in the index table in real-time. The optimizer will also automatically decide whether to use the index based on the user's query. This topic describes how to scan indexes.
When a SQL query specifies predicate conditions on indexed columns, the database automatically extracts these conditions as the range for querying the index table. This range consists of a start key and an end key for querying the index table. By using the start key, the database can locate the starting position of the data, and by using the end key, it can determine the ending position of the data. The data within this range is the data that needs to be scanned for this query.
For an index table, OceanBase Database uses MemTable and SSTable to store data. MemTable uses a B+ tree structure, while SSTable uses a macroblock structure. Both MemTable and SSTable are scanned according to the process described above to retrieve the corresponding data. The final result is a complete data row obtained by merging the data rows from both MemTable and SSTable.
Therefore, the complete process of querying data from an index table in OceanBase Database is as follows:
Query data in MemTable.
Query data in SSTable.
Merge data from MemTable and SSTable to obtain the complete row.
When a SQL query involves only the columns in the index table, the database will query the corresponding MemTable and SSTable of the index table based on the specified columns, following the above query process to obtain the complete data row.
When a SQL query includes columns from the index table as well as other columns, the database first retrieves the relevant rows through the index table. Then, based on the primary key of each row, it proceeds with the aforementioned query process to retrieve the required data columns from the primary table. This process is commonly known as table access by index primary key.