The TABLE SCAN operator is the interface between the storage layer and the SQL layer. It indicates which index the optimizer chooses to access data.
In OceanBase AI Database, for normal indexes and global indexes, the table lookup logic is encapsulated in the TABLE SCAN operator. In an execution plan, the is_index_back flag indicates whether the operator requires a table lookup, and the is_global_index flag indicates whether the operator scans 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 preceding example, the outputs & filters section of the execution plan shows the output information of the TABLE SCAN operator as follows:
Information name |
Description |
|---|---|
| operator | The operator of the TABLE SCAN operator has three forms: TABLE SCAN, TABLE GET, and TABLE SKIP SCAN.
|
| name | Specifies the index used to access data. The name of the selected index follows the table name. If no index name is specified, a full table scan is performed. Note that in OceanBase AI Database, the primary table and indexes have the same organizational structure, and the primary table itself is an index. |
| output | The output columns of this operator. |
| filter | The filter predicate of this operator. In query Q1, no filter is set for the TABLE GET operator, so the value is nil. |
| partitions | The partitions to be scanned for the query. |
| is_index_back | Indicates whether the operator requires a table lookup. For example, in query Q1, because the primary table is selected, no table lookup is required. |
| is_global_index | Indicates whether the operator scans a global index. For example, in query Q2, the index used is a normal index, so is_global_index is false. In query Q3, index i2 of table t2 is used, and i2 is a global index, so is_global_index is true. |
| range_key/range/range_cond |
|
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 of the execution plan shows the output information of the TABLE SCAN operator as follows:
Information name |
Description |
|---|---|
| operator | The operator of the TABLE SCAN operator has three forms: TABLE SCAN, TABLE GET, and TABLE SKIP SCAN.
TABLE RANGE SCAN indicates that data is retrieved from a table based on range conditions. |
| name | Specifies the index used to access data. The name of the selected index follows the table name. If no index name is specified, a full table scan is performed. Note that in OceanBase AI Database, the primary table and indexes have the same organizational structure, and the primary table itself is an index. |
| output | The output columns of this operator. |
| filter | The filter predicate of this operator. In query Q2, filter([T1.C2 < 1], [T1.C3 < 1], [T1.C4 < 1]) indicates that three filter conditions are applied to the result of the full table scan on table T1: the value of C2 is less than 1, the value of C3 is less than 1, and the value of C4 is less than 1. During query execution, only rows that meet these conditions are included in the final result set. |
| partitions | The partitions to be scanned for the query. |
| is_index_back | Indicates whether the operator requires a table lookup. For example, in query Q1, because the primary table is selected, no table lookup is required. In query Q2, the index columns are (c2,c3). Because the query needs to return column c4, a table lookup is required. |
| is_global_index | Indicates whether the operator scans a global index. For example, in query Q2, the index used is a normal index, so is_global_index is false. In query Q3, index i2 of table t2 is used, and i2 is a global index, so is_global_index is true. |
| filter_before_indexback | Corresponds to each filter and indicates whether the filter can be calculated directly on the index or requires a table lookup. For example, in query Q2, when filter is c3 < 1, the calculation can be performed directly on the index, reducing the number of table lookups. When filter is c4 < 1, the data must be retrieved from the table because c4 can be calculated only after the table lookup. |
| range_key/range/range_cond |
|
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 of the execution plan shows the output information of the TABLE SCAN operator as follows:
Information name |
Description |
|---|---|
| operator | The operator of the TABLE SCAN operator has three forms: TABLE SCAN, TABLE GET, and TABLE SKIP SCAN.
DISTRIBUTED TABLE RANGE SCAN indicates that a range scan is performed on the distributed table during query execution. |
| name | Specifies the index used to access data. The name of the selected index follows the table name. If no index name is specified, a full table scan is performed. Note that in OceanBase AI Database, the primary table and indexes have the same organizational structure, and the primary table itself is an index. |
| output | The output columns of this operator. |
| filter | The filter predicate of this operator. In query Q1, no filter is set for the TABLE GET operator, so the value is nil. |
| partitions | The partitions to be scanned for the query. |
| is_index_back | Indicates whether the operator requires a table lookup. For example, in query Q1, because the primary table is selected, no table lookup is required. In query Q2, the index columns are (c2,c3). Because the query needs to return column c4, a table lookup is required. |
| is_global_index | Indicates whether the operator scans a global index. For example, in query Q2, the index used is a normal index, so is_global_index is false. In query Q3, index i2 of table t2 is used, and i2 is a global index, so is_global_index is true. |
| filter_before_indexback | Corresponds to each filter and indicates whether the filter can be calculated directly on the index or requires a table lookup. For example, in query Q2, when filter is c3 < 1, the calculation can be performed directly on the index, reducing the number of table lookups. When filter is c4 < 1, the data must be retrieved from the table because c4 can be calculated only after the table lookup. |
| range_key/range/range_cond |
|
Applicability
The user tenant of OceanBase AI Database does not support the CONNECT BY LEVEL syntax 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 of the execution plan shows the output information of the TABLE SCAN operator as follows:
Information name |
Description |
|---|---|
| operator | The operator of the TABLE SCAN operator has three forms: TABLE SCAN, TABLE GET, and TABLE SKIP SCAN.
|
| name | Specifies the index used to access data. The name of the selected index follows the table name. If no index name is specified, a full table scan is performed. Note that in OceanBase AI Database, the primary table and indexes have the same organizational structure, and the primary table itself is an index. |
| output | The output columns of this operator. |
| filter | The filter predicate of this operator. In query Q1, no filter is set for the TABLE GET operator, so the value is nil. |
| partitions | The partitions to be scanned for the query. |
| is_index_back | Indicates whether the operator requires a table lookup. For example, in query Q1, because the primary table is selected, no table lookup is required. In query Q2, the index columns are (c2,c3). Because the query needs to return column c4, a table lookup is required. |
| is_global_index | Indicates whether the operator scans a global index. For example, in query Q2, the index used is a normal index, so is_global_index is false. In query Q3, index i2 of table t2 is used, and i2 is a global index, so is_global_index is true. |
| filter_before_indexback | Corresponds to each filter and indicates whether the filter can be calculated directly on the index or requires a table lookup. For example, in query Q2, when filter is c3 < 1, the calculation can be performed directly on the index, reducing the number of table lookups. When filter is c4 < 1, the data must be retrieved from the table because c4 can be calculated only after the table lookup. |
| range_key/range/range_cond |
|
| prefix_columns_cnt | The number of prefix columns of TABLE SKIP SCAN. |
| skip_scan_range | The scan range extracted from suffix columns for TABLE SKIP SCAN. |
In query Q4, TABLE SKIP SCAN can accelerate scanning by using existing indexes under certain conditions. OceanBase AI Database attempts to choose TABLE SKIP SCAN when the following conditions 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 SCANwith that of a full table scan and finds thatTABLE SKIP SCANis less costly.
Applicability
String-to-numeric conversion is not currently supported in the user tenant of OceanBase AI 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 shows the output information of the TABLE RANGE SCAN operator as follows:
Information name |
Description |
|---|---|
| operator | The operator of the TABLE SCAN operator has three forms: TABLE SCAN, TABLE GET, and TABLE SKIP SCAN.
|
| name | Specifies the index used to access data. The name of the selected index follows the table name. If no index name is specified, a full table scan is performed. Note that in OceanBase AI Database, the primary table and indexes have the same organizational structure, and the primary table itself is an index. |
| output | The output columns of this operator. |
| filter | The filter predicate of this operator. In query Q5, no filter is set for the TABLE RANGE SCAN operator, so the value is nil. |
| partitions | The partitions to be scanned for the query. |
| is_index_back | Indicates whether the operator requires a table lookup. For example, in query Q1, because the primary table is selected, no table lookup is required. In query Q2, the index columns are (c2,c3,c1). Because the query needs to return column c4, a table lookup is required. |
| is_global_index | Indicates whether the operator scans a global index. For example, in query Q2, the index used is a normal index, so is_global_index is false. In query Q3, index i2 of table t2 is used, and i2 is a global index, so is_global_index is true. |
| range_key/range/range_cond |
|