TABLE SCAN|V5.0.1|OceanBase Database|docs|OceanBase Documentations

TABLE SCAN

Last Updated：2026-09-04 11:28:26 Updated

The TABLE SCAN operator is an interface between the storage layer and the SQL layer, used to show which index the optimizer chooses to access data.

In OceanBase Database V4.1.0, for normal indexes and global indexes, the logic for backtracking to the index is encapsulated within the TABLE SCAN operator. When displaying execution plans, the is_index_back flag indicates whether the operator requires backtracking to the index, and the is_global_index flag indicates whether it is scanning a global index. Example: Execution plan containing the TABLE SCAN operator

Q1:
obclient> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT, c4 INT);
obclient> CREATE INDEX e1 ON t1(c1,c2);
obclient> EXPLAIN SELECT * FROM t1 WHERE c1 = 1;
+--------------------------------------------------------------------------+
| Query Plan                                                               |
+--------------------------------------------------------------------------+
| =========================================                                |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)|                                |
| -----------------------------------------                                |
| |0 |TABLE GET|T1  |1       |3           |                                |
| =========================================                                |
| Outputs & filters:                                                       |
| -------------------------------------                                    |
|   0 - output([T1.C1], [T1.C2], [T1.C3], [T1.C4]), filter(nil), rowset=16 |
|       access([T1.C1], [T1.C2], [T1.C3], [T1.C4]), partitions(p0)         |
|       is_index_back=false, is_global_index=false,                        |
|       range_key([T1.C1]), range[1 ; 1],                                  |
|       range_cond([T1.C1 = 1])                                            |
+--------------------------------------------------------------------------+

In the example above, the outputs & filters section of the execution plan details the output information of the TABLE SCAN operator as follows:

Information Name	Meaning
operator	`TABLE SCAN`Operator's`operator`There are three forms:`TABLE SCAN`、`TABLE GET`and`TABLE SKIP SCAN`. `TABLE SCAN`Belongs to a range scan and returns 0 or multiple rows of data. `TABLE GET`Directly locate by primary key, returning 0 or 1 row of data. `TABLE SKIP SCAN`Belongs to a range scan and is executed with jumps, returning 0 or multiple rows of data.
name	Specifies which index is used to access data. The name of the selected index follows the table name; if no index name is provided, a full table scan is performed. Note that in OceanBase Database, the primary table and indexes have the same organizational structure, with the primary table itself being an index.
output	Output column of this operator.
filter	The filter predicate of this operator. In the example, the query in Q1 asks for`TABLE GET`The operator is not set.`filter`, so it is`nil`.
partitions	The partitions to be scanned in the query.
is_index_back	Whether the operator requires a back-to-table query. For example, in the Q1 query, since the primary table was selected, no back-to-table query is needed.
is_global_index	Whether this operator scans global indexes. For example, in a Q2 query, if the index used is a normal index, then`is_global_index`is`false`In the Q3 query, the`t2`Table's`i2`Indexes,`i2`The index is a global index, so`is_global_index`is`true`.
range_key/range/range_cond	`range_key`: index's`rowkey` The column name. `range`: The positions where the index scan starts and ends. To determine if a full table scan is required, pay attention to`range`. For example, for a`rowkey`For scenarios with three columns,`range(MIN,MIN, MIN ; MAX, MAX, MAX)`represents a full table scan in the true sense. `range_cond`: Predicates that determine the start and end positions of index scanning.

Q2:
obclient> CREATE INDEX e1 ON t1(c2,c3);
obclient> EXPLAIN SELECT * FROM t1 WHERE c2 < 1 AND c3 < 1 AND c4 < 1;
+---------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                  |
+---------------------------------------------------------------------------------------------------------------------------------------------+
| ==================================================                                                                                          |
| |ID|OPERATOR        |NAME  |EST.ROWS|EST.TIME(us)|                                                                                          |
| --------------------------------------------------                                                                                          |
| |0 |TABLE RANGE SCAN|T1(E1)|1       |5           |                                                                                          |
| ==================================================                                                                                          |
| Outputs & filters:                                                                                                                          |
| -------------------------------------                                                                                                       |
|   0 - output([T1.C1], [T1.C2], [T1.C3], [T1.C4]), filter([T1.C3 < cast(1, NUMBER(-1, -85))], [T1.C4 < cast(1, NUMBER(-1, -85))]), rowset=16 |
|       access([T1.C1], [T1.C2], [T1.C3], [T1.C4]), partitions(p0)                                                                            |
|       is_index_back=true, is_global_index=false, filter_before_indexback[true,false],                                                       |
|       range_key([T1.C2], [T1.C3], [T1.C1]), range(MIN,MIN,MIN ; 1,MIN,MIN),                                                                 |
|       range_cond([T1.C2 < cast(1, NUMBER(-1, -85))])                                                                                        |
+---------------------------------------------------------------------------------------------------------------------------------------------+
12 rows in set

In the example above, the outputs & filters section of the execution plan details the output information of the TABLE SCAN operator as follows:

Information Name	Meaning
operator	`TABLE SCAN`operator's`operator`There are three forms:`TABLE SCAN`、`TABLE GET`and`TABLE SKIP SCAN`. `TABLE SCAN`Belongs to a range scan and returns 0 or multiple rows of data. `TABLE GET`Directly locate by primary key, returning 0 or 1 row of data. `TABLE SKIP SCAN`Belongs to a range scan and is executed with jumps, returning 0 or multiple rows of data. In the query in Q2,`TABLE RANGE SCAN`Represents the operation of retrieving data from a table based on RANGE conditions.
name	Specifies which index is used to access data. The name of the selected index follows the table name; if no index name is provided, a full table scan is performed. Note that in OceanBase Database, the primary table and indexes have the same organizational structure, with the primary table itself being an index.
output	Output column of this operator.
filter	The filtering predicate of this operator. In Example Q2, it is`filter([T1.C2 < 1], [T1.C3 < 1], [T1.C4 < 1])`indicates that the result of a full table scan on table T1 is filtered by three conditions: the value in column C2 is less than 1, the value in column C3 is less than 1, and the value in column C4 is less than 1. During query execution, rows that meet these conditions are included in the final result set.
partitions	The partitions to be scanned in the query.
is_index_back	Whether the operator requires a table access. For example, in query Q1, no table access is required because the primary table was selected. In query Q2, the index column is`(c2,c3)`, because the query needs to return`c4`columns, so a table access is required.
is_global_index	Whether this operator scans global indexes. For example, in a Q2 query, if the index used is a normal index, then`is_global_index`is`false`In the Q3 query, the`t2`of the table`i2`indexes,`i2`The index is a global index, so`is_global_index`is`true`.
filter_before_indexback	With each`filter`corresponds to indicate that this`filter`Whether the calculation can be performed directly on the index or requires a back-to-table access to compute the values. For example, in a Q2 query, when`filter`is`c3 < 1`When it is needed, calculations can be performed directly on the index, reducing the number of table accesses. When`filter`is`c4 < 1`When the data changes, you need to retrieve it from the table.`c4`can be calculated only after the column is computed.
range_key/range/range_cond	`range_key`: The index's`rowkey` column. `range`: The position where the index scan starts and ends. To determine if a full table scan is required, pay attention to`range`. For example, for a`rowkey`For scenarios with three columns,`range(MIN,MIN, MIN ; MAX, MAX, MAX)`represents a full table scan in the true sense. `range_cond`: Predicates that determine the start and end positions for index scanning.

Q3:
obclient> CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT, c3 INT) PARTITION BY HASH(c1) PARTITIONS 4;
obclient> CREATE INDEX i2 ON t2(c2) GLOBAL;
obclient> EXPLAIN SELECT * FROM t2 WHERE c2 = 1;
+-----------------------------------------------------------------+
| Query Plan                                                      |
+-----------------------------------------------------------------+
| ==============================================================  |
| |ID|OPERATOR                    |NAME  |EST.ROWS|EST.TIME(us)|  |
| --------------------------------------------------------------  |
| |0 |DISTRIBUTED TABLE RANGE SCAN|T2(I2)|1       |30          |  |
| ==============================================================  |
| Outputs & filters:                                              |
| -------------------------------------                           |
|   0 - output([T2.C1], [T2.C2], [T2.C3]), filter(nil), rowset=16 |
|       access([T2.C1], [T2.C2], [T2.C3]), partitions(p0)         |
|       is_index_back=true, is_global_index=true,                 |
|       range_key([T2.C2], [T2.C1]), range(1,MIN ; 1,MAX),        |
|       range_cond([T2.C2 = 1])                                   |
+-----------------------------------------------------------------+

In the example above, the outputs & filters section of the execution plan details the output information of the TABLE SCAN operator as follows:

Information Name	Meaning
operator	`TABLE SCAN`of the operator`operator`There are three forms:`TABLE SCAN`、`TABLE GET`and`TABLE SKIP SCAN`. `TABLE SCAN`Belongs to a range scan and returns 0 or multiple rows of data. `TABLE GET`Directly locate by primary key, returning 0 or 1 row of data. `TABLE SKIP SCAN`Belongs to a range scan, executed with jumps, and returns zero or multiple rows of data. In the query in Q3,`DISTRIBUTED TABLE RANGE SCAN`Indicates that an interval scan will be performed on the distributed table during query execution.
name	Specifies which index is used to access data. The name of the selected index follows the table name; if no index name is provided, a full table scan is performed. Note that in OceanBase Database, the primary table and indexes have the same organizational structure, with the primary table itself being an index.
output	Output column of this operator.
filter	The filter predicate of this operator. In the example, the query in Q1 asks for`TABLE GET`The operator is not set.`filter`, so it is`nil`.
partitions	The partitions to be scanned in the query.
is_index_back	Whether the operator requires a back-to-table access. For example, in query Q1, since the primary table was selected, no back-to-table access is needed. In query Q2, the index column is`(c2,c3)`, Because the Q2 query needs to return`c4`columns, so a table access is required.
is_global_index	Whether this operator scans global indexes. For example, in a Q2 query, if a normal index is used, then`is_global_index`is`false`In the Q3 query, the`t2`Table's`i2`Indexes,`i2`The index is a global index, so`is_global_index`is`true`.
filter_before_indexback	With each`filter`corresponds, indicating that this`filter`Whether the calculation can be performed directly on the index or requires a back-to-table access to compute the result. For example, in a Q2 query, when`filter`is`c3 < 1`When the value is available, it can be calculated directly on the index, reducing the number of table accesses. When the`filter`is`c4 < 1`When the data is updated, you need to retrieve it from the table.`c4`can be calculated only after the column is computed.
range_key/range/range_cond	`range_key`: index's`rowkey` column `range`: The position where the index scan starts and ends. To determine if a full table scan is required, pay attention to`range`The scope of the variable. For example, for a`rowkey`For scenarios with three columns,`range(MIN,MIN, MIN ; MAX, MAX, MAX)`represents a full table scan in the true sense. `range_cond`: Predicates that determine the start and end positions for index scanning.

Applicability

The MySQL-compatible tenant of OceanBase Database does not support the CONNECT BY LEVEL syntax in Example Q4.

Q4:
obclient> CREATE TABLE t3(pk INT, c1 INT, c2 INT, PRIMARY KEY(pk));
obclient> INSERT INTO t3 (pk,c1,c2) SELECT LEVEL, MOD(LEVEL,3), LEVEL FROM DUAL CONNECT BY LEVEL <= 10000;
obclient> CREATE INDEX i1 ON t3(c1,c2);
obclient> EXPLAIN SELECT /*+ INDEX_SS(t3 i1) */ * FROM t3 WHERE c2 = 1;
+-----------------------------------------------------------------------------------------+
| Query Plan                                                                              |
+-----------------------------------------------------------------------------------------+
| =================================================                                       |
| |ID|OPERATOR       |NAME  |EST.ROWS|EST.TIME(us)|                                       |
| -------------------------------------------------                                       |
| |0 |TABLE SKIP SCAN|T3(I1)|1       |17          |                                       |
| =================================================                                       |
| Outputs & filters:                                                                      |
| -------------------------------------                                                   |
|   0 - output([T3.PK], [T3.C1], [T3.C2]), filter([T3.C2 = 1]), rowset=16                 |
|       access([T3.PK], [T3.C2], [T3.C1]), partitions(p0)                                 |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],       |
|       range_key([T3.C1], [T3.C2], [T3.PK]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true |
|       prefix_columns_cnt = 1 , skip_scan_range(1,MIN ; 1,MAX)                           |
+-----------------------------------------------------------------------------------------+

In the example above, the outputs & filters section of the execution plan details the output information of the TABLE SCAN operator as follows:

Information Name	Meaning
operator	`TABLE SCAN`of the operator`operator`There are three forms:`TABLE SCAN`、`TABLE GET`and`TABLE SKIP SCAN`. `TABLE SCAN`Belongs to a range scan and returns 0 or multiple rows of data. `TABLE GET`Directly locate by primary key, returning 0 or 1 row of data. `TABLE SKIP SCAN`Belongs to a range scan and is executed in a skip mode, returning 0 or multiple rows of data.
name	Specifies which index is used to access data. The name of the selected index follows the table name; if no index name is provided, a full table scan is performed. Note that in OceanBase Database, the primary table and indexes have the same organizational structure, with the primary table itself being an index.
output	Output column of this operator.
filter	The filter predicate of this operator. In the example, the query in Q1 asks for`TABLE GET`The operator is not set.`filter`, so it is`nil`.
partitions	The partitions to be scanned in the query.
is_index_back	Whether the operator requires a back-to-table access. For example, in query Q1, since the primary table was selected, no back-to-table access is needed. In query Q2, the index column is`(c2,c3)`, because the Q2 query needs to return`c4`columns, so a table access is required.
is_global_index	Whether this operator scans global indexes. For example, in a Q2 query, if the index used is a normal index, then`is_global_index`is`false`In the Q3 query, the`t2`The table's`i2`Indexes,`i2`The index is a global index, so`is_global_index`is`true`.
filter_before_indexback	With each`filter`corresponds, indicating that this`filter`Whether the calculation can be performed directly on the index or requires a back-to-table access. For example, in a Q2 query, when`filter`is`c3 < 1`When it is available, calculations can be performed directly on the index, reducing the number of table accesses. When`filter`is`c4 < 1`When the value changes, you need to retrieve it from the table.`c4`can be calculated only after the column is computed.
range_key/range/range_cond	`range_key`: The index's`rowkey` The column name. `range`: The position where the index scan starts and ends. To determine if a full table scan is required, pay attention to`range`. For example, for a`rowkey`For scenarios with three columns,`range(MIN,MIN, MIN ; MAX, MAX, MAX)`represents a full table scan in the true sense. `range_cond`: Predicates that determine the start and end positions of index scanning.
prefix_columns_cnt	means`TABLE SKIP SCAN`Number of prefix columns.
skip_scan_range	means`TABLE SKIP SCAN`The scan range extracted from the suffix column.

In a Q4 query, TABLE SKIP SCAN can use existing indexes to accelerate scanning under certain conditions. OceanBase Database attempts to choose TABLE SKIP SCAN when the following limitations are met:

Statistics have been collected on the table.
The query conditions include a suffix column of a composite index, and it is not the leftmost prefix of another index.
The optimizer compares the cost of TABLE SKIP SCAN with that of a full table scan and finds that TABLE SKIP SCAN is less costly.

Applicability

String-to-numeric conversion is not currently supported in MySQL-compatible tenants of OceanBase Database.

Q5:
obclient> CREATE TABLE t1(c1 NUMBER PRIMARY KEY, c2 DATE);
Query OK, 0 rows affected (0.17 sec)
obclient> CREATE INDEX i1 ON t1(c2);
Query OK, 0 rows affected (0.51 sec)
obclient> EXPLAIN SELECT /*+ index(t1 i1) */  c2 FROM t1 WHERE c2 = TIMESTAMP '2024-01-01 11:22:00';
+-----------------------------------------------------------------------------------------------+
| Query Plan                                                                                    |
+-----------------------------------------------------------------------------------------------+
| ==================================================                                            |
| |ID|OPERATOR        |NAME  |EST.ROWS|EST.TIME(us)|                                            |
| --------------------------------------------------                                            |
| |0 |TABLE RANGE SCAN|T1(I1)|1       |4           |                                            |
| ==================================================                                            |
| Outputs & filters:                                                                            |
| -------------------------------------                                                         |
|   0 - output([T1.C2]), filter(nil), rowset=16                                                 |
|       access([T1.C2]), partitions(p0)                                                         |
|       is_index_back=false, is_global_index=false,                                             |
|       range_key([T1.C2], [T1.C1]), range(2024-01-01 11:22:00,MIN ; 2024-01-01 11:22:00,MAX),  |
|       range_cond([cast(T1.C2, TIMESTAMP(19, 9)) = '2024-01-01 11:22:00.000000000'])           |
+-----------------------------------------------------------------------------------------------+

In the preceding example, the outputs & filters section of the execution plan details the output information for the TABLE RANGE SCAN operator as follows:

Information Name	Meaning
operator	`TABLE SCAN`of the operator`operator`There are three forms:`TABLE SCAN`、`TABLE GET`and`TABLE SKIP SCAN`. `TABLE SCAN`Belongs to a range scan and returns 0 or multiple rows of data. `TABLE GET`Directly locate by primary key, returning 0 or 1 row of data. `TABLE SKIP SCAN`Belongs to a range scan and is executed in a skipping manner, returning zero or multiple rows of data. `TABLE RANGE SCAN`This is a range scan and will return 0 rows or multiple rows of data.
name	Specifies which index is used to access data. The name of the selected index follows the table name; if no index name is provided, a full table scan is performed. Note that in OceanBase Database, the primary table and indexes have the same organizational structure, with the primary table itself being an index.
output	Output column of this operator.
filter	The filtering predicate of this operator. In the example, the query in Q5 asks for`TABLE RANGE SCAN`The operator is not set.`filter`, so it is`nil`.
partitions	The partitions to be scanned in the query.
is_index_back	Whether the operator requires a table access. For example, in query Q1, since the primary table is selected, no table access is required. In query Q2, the index column is`(c2,c3,c1)`, because the query needs to return`c4`columns, so a table access is required.
is_global_index	Whether this operator scans global indexes. For example, in a Q2 query, if the index used is a normal index, then`is_global_index`is`false`In the Q3 query, the`t2`Table's`i2`Indexes,`i2`The index is a global index, so`is_global_index`is`true`.
range_key/range/range_cond	`range_key`: The `rowkey` column of the index. `range`: The starting and ending positions for the index scan. To determine if a full table scan is occurring, pay attention to the range specified by `range`. For example, in a scenario where a `rowkey` consists of three columns, `range(MIN,MIN, MIN ; MAX, MAX, MAX)` indicates a true full table scan. `range_cond`: The predicate that determines the starting and ending positions for the index scan.

OceanBase

Customer Stories

Documentation

TABLE SCAN

Applicability

Applicability