TABLE SCAN|V4.3.5| docs|Distributed Database

TABLE SCAN

Last Updated：2025-07-14 11:58:10 Updated

The TABLE SCAN operator provides an interface between the storage and SQL layers and shows which index is selected by the optimizer for data access.

In OceanBase Database V4.1.0, the table access logic for normal indexes and global indexes are encapsulated in the TABLE SCAN operator. When the execution plan is displayed, is_index_back indicates whether table access by index primary key is required by the operator, and is_global_index indicates whether the operator scans a global index. The following sample code shows an execution plan that contains a 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 preceding example, the Outputs & filters section shows in detail the output information of the TABLE SCAN operator.

Field	Description
operator	The `TABLE SCAN` operator has three forms: `TABLE SCAN`, `TABLE GET`, and `TABLE SKIP SCAN`. `TABLE SCAN` performs a range scan and returns zero or multiple rows of data. `TABLE GET` locates a row by using a primary key and returns zero or one row of data. `TABLE SKIP SCAN` performs a skip range scan and returns zero or multiple rows of data.
name	The index selected for accessing data. The name of the selected index follows the table name. The absence of the index name means that the primary table is scanned. In OceanBase Database, the primary table has the same structure as the index, and the primary table is an index.
output	The output columns of the operator.
filter	The filter predicates of the operator. In query Q1, `filter` is set to `nil` because no filter condition is configured for the `TABLE GET` operator.
partitions	The partitions to be scanned in the query.
is_index_back	Indicates whether table access by index primary key is required by the operator. In query Q1, the primary table is selected. Therefore, table access by index primary key is not required.
is_global_index	Indicates whether the operator performs a global index scan. For example, in query Q2, a normal index is used. Therefore, `is_global_index` is `false`. In query Q3, index `i2` of table `t2` is used, and index `i2` is a global index. Therefore, `is_global_index` is `true`.
range_key/range/range_cond	`range_key`: the `rowkey` columns of the index. `range`: the start and end positions in an index scan. You can verify whether it is a full table scan based on the value of `range`. For example, in a scenario with three `rowkey` columns, `range(MIN, MIN, MIN; MAX, MAX, MAX)` represents a full table scan. `range_cond`: predicates that determine the start and end positions in an index scan.

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 preceding example, the Outputs & filters section shows in detail the output information of the TABLE SCAN operator.

Field	Description
operator	The `TABLE SCAN` operator has three forms: `TABLE SCAN`, `TABLE GET`, and `TABLE SKIP SCAN`. `TABLE SCAN` performs a range scan and returns zero or multiple rows of data. `TABLE GET` locates a row by using a primary key and returns zero or one row of data. `TABLE SKIP SCAN` performs a skip range scan and returns zero or multiple rows of data. In query Q2, `TABLE RANGE SCAN` is an operation that retrieves data from a table based on the specified range.
name	The index selected for accessing data. The name of the selected index follows the table name. The absence of the index name means that the primary table is scanned. In OceanBase Database, the primary table has the same structure as the index, and the primary table is an index.
output	The output columns of the operator.
filter	The filter predicates of the operator. In query Q2, `filter([T1.C2 < 1], [T1.C3 < 1], [T1.C4 < 1])` indicates that three filter conditions are applied to the results of a full table scan on table T1. These conditions are: the value of column C2 is less than 1, the value of column C3 is less than 1, and the value of column C4 is less than 1. During the execution of the query, rows that meet these conditions will be included in the final result set.
partitions	The partitions to be scanned in the query.
is_index_back	Indicates whether table access by index primary key is required by the operator. In query Q1, the primary table is selected. Therefore, table access by index primary key is not required. In query Q2, the indexed column is `(c2,c3)`. Since the query needs to return column `c4`, a table access by index primary key is required.
is_global_index	Indicates whether the operator performs a global index scan. For example, in query Q2, a normal index is used. Therefore, `is_global_index` is `false`. In query Q3, index `i2` of table `t2` is used, and index `i2` is a global index. Therefore, `is_global_index` is `true`.
filter_before_indexback	Corresponds to each `filter` and indicates whether the `filter` directly applies to the index or after the table access by index primary key. For example, in query Q2, `filter` `c3 < 1` can be directly applied to the index, which reduces the number of table access by index primary key operations. However, `filter` `c4 < 1` can be applied only after column `c4` is fetched through table access.
range_key/range/range_cond	`range_key`: the `rowkey` columns of the index. `range`: the start and end positions in an index scan. You can verify whether it is a full table scan based on the value of `range`. For example, in a scenario with three `rowkey` columns, `range(MIN, MIN, MIN; MAX, MAX, MAX)` represents a full table scan. `range_cond`: predicates that determine the start and end positions in an index scan.

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 preceding example, the Outputs & filters section shows in detail the output information of the TABLE SCAN operator.

Field	Description
operator	The `TABLE SCAN` operator has three forms: `TABLE SCAN`, `TABLE GET`, and `TABLE SKIP SCAN`. `TABLE SCAN` performs a range scan and returns zero or multiple rows of data. `TABLE GET` locates a row by using a primary key and returns zero or one row of data. `TABLE SKIP SCAN` performs a skip range scan and returns zero or multiple rows of data. In query Q3, `DISTRIBUTED TABLE RANGE SCAN` indicates that a range scan will be performed on the distributed table during the execution of the query.
name	The index selected for accessing data. The name of the selected index follows the table name. The absence of the index name means that the primary table is scanned. In OceanBase Database, the primary table has the same structure as the index, and the primary table is an index.
output	The output columns of the operator.
filter	The filter predicates of the operator. In query Q1, `filter` is set to `nil` because no filter condition is configured for the `TABLE GET` operator.
partitions	The partitions to be scanned in the query.
is_index_back	Indicates whether table access by index primary key is required by the operator. In query Q1, the primary table is selected. Therefore, table access by index primary key is not required. In query Q2, the indexed column is `(c2,c3)`. Since the query needs to return column `c4`, a table access by index primary key is required.
is_global_index	Indicates whether the operator performs a global index scan. For example, in query Q2, a normal index is used. Therefore, `is_global_index` is `false`. In query Q3, index `i2` of table `t2` is used, and index `i2` is a global index. Therefore, `is_global_index` is `true`.
filter_before_indexback	Corresponds to each `filter` and indicates whether the `filter` directly applies to the index or after the table access by index primary key. For example, in query Q2, `filter` `c3 < 1` can be directly applied to the index, which reduces the number of table access by index primary key operations. However, `filter` `c4 < 1` can be applied only after column `c4` is fetched through table access.
range_key/range/range_cond	`range_key`: the `rowkey` columns of the index. `range`: the start and end positions in an index scan. You can verify whether it is a full table scan based on the value of `range`. For example, in a scenario with three `rowkey` columns, `range(MIN, MIN, MIN; MAX, MAX, MAX)` represents a full table scan. `range_cond`: predicates that determine the start and end positions in an index scan.

Applicability

The MySQL tenant of OceanBase Database does not support the CONNECT BY LEVEL syntax as shown in query 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 preceding example, the Outputs & filters section shows in detail the output information of the TABLE SCAN operator.

Field	Description
operator	The `TABLE SCAN` operator has three forms: `TABLE SCAN`, `TABLE GET`, and `TABLE SKIP SCAN`. `TABLE SCAN` performs a range scan and returns zero or multiple rows of data. `TABLE GET` locates a row by using a primary key and returns zero or one row of data. `TABLE SKIP SCAN` performs a skip range scan and returns zero or multiple rows of data.
name	The index selected for accessing data. The name of the selected index follows the table name. The absence of the index name means that the primary table is scanned. In OceanBase Database, the primary table has the same structure as the index, and the primary table is an index.
output	The output columns of the operator.
filter	The filter predicates of the operator. In query Q1, `filter` is set to `nil` because no filter condition is configured for the `TABLE GET` operator.
partitions	The partitions to be scanned in the query.
is_index_back	Indicates whether table access by index primary key is required by the operator. In query Q1, the primary table is selected. Therefore, table access by index primary key is not required. In query Q2, the indexed column is `(c2,c3)`. Since the query needs to return column `c4`, a table access by index primary key is required.
is_global_index	Indicates whether the operator performs a global index scan. For example, in query Q2, a normal index is used. Therefore, `is_global_index` is `false`. In query Q3, index `i2` of table `t2` is used, and index `i2` is a global index. Therefore, `is_global_index` is `true`.
filter_before_indexback	Corresponds to each `filter` and indicates whether the `filter` directly applies to the index or after the table access by index primary key. For example, in query Q2, `filter` `c3 < 1` can be directly applied to the index, which reduces the number of table access by index primary key operations. However, `filter` `c4 < 1` can be applied only after column `c4` is fetched through table access.
range_key/range/range_cond	`range_key`: the `rowkey` columns of the index. `range`: the start and end positions in an index scan. You can verify whether it is a full table scan based on the value of `range`. For example, in a scenario with three `rowkey` columns, `range(MIN, MIN, MIN; MAX, MAX, MAX)` represents a full table scan. `range_cond`: predicates that determine the start and end positions in an index scan.
prefix_columns_cnt	The number of prefix columns for the `TABLE SKIP SCAN` operator.
skip_scan_range	The scan range extracted by the `TABLE SKIP SCAN` operator from the suffix columns.

In query Q4, the TABLE SKIP SCAN operator can accelerate scanning by using existing indexes in specific circumstances. OceanBase Database uses the TABLE SKIP SCAN operator in the following circumstances:

Statistics have been collected from the table.
The query conditions include the suffix columns of a composite index and the suffix columns are not the leftmost prefixes of other indexes.
The optimizer finds that a table skip scan has lower costs than a full table scan does.

Applicability

String-to-number conversion is not supported in MySQL 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 displays the following output information about the TABLE RANGE SCAN operator:

Parameter	Description
operator	The `operator` of the `TABLE SCAN` operator can take one of the following values: `TABLE SCAN`, `TABLE GET`, and `TABLE SKIP SCAN`. `TABLE SCAN` is a range scan that returns 0 or more rows. `TABLE GET` directly locates a row by using its primary key and returns 0 or 1 row. `TABLE SKIP SCAN` is a range scan that skips rows and returns 0 or more rows. `TABLE RANGE SCAN` is a range scan that returns 0 or more rows.
name	The name of the index used to access data. The name of the index is appended to the name of the table. If no index name is specified, the primary table is scanned. Note that in OceanBase Database, the primary table and indexes have the same structure. The primary table is also an index.
output	The output columns of the operator.
filter	The filtering condition of the operator. The `TABLE RANGE SCAN` operator of the Q5 query does not have a `filter` column, so the value is `nil`.
partitions	The partitions to be scanned.
is_index_back	Whether the operator needs to access data by using the table. In the Q1 query, the primary table is selected, so no table access is required. In the Q2 query, the index column is `(c2,c3,c1)`, and the query needs to return column c4. Therefore, table access is required.
is_global_index	Whether the operator accesses data by using a global index. In the Q2 query, the ordinary index is selected, so `is_global_index` is `false`. In the Q3 query, the `i2` index of the `t2` table is selected, and the `i2` 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 start and end positions for scanning the index. Whether a full table scan is performed depends on the range of `range`. For example, in a scenario where the `rowkey` column has three columns, `range(MIN,MIN, MIN ; MAX, MAX, MAX)` represents a full table scan. `range_cond`: the predicates for determining the start and end positions for scanning the index.