TABLE SCAN|V4.1.0| docs|Distributed Database

TABLE SCAN

Last Updated：2023-07-28 02:55:42 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.

Example: an execution plan containing a TABLE SCAN operator

obclient> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT, c4 INT,
      INDEX k1(c2,c3));
Query OK, 0 rows affected

Q1:
obclient> EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1 = 1\G
*************************** 1. row ***************************
Query Plan:
| ==================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
----------------------------------
|0 |TABLE GET|t1  |1        |53  |
==================================
Outputs & filters:
-------------------------------------
  0 - output([t1.c1(0x7f22fbe69340)], [t1.c2(0x7f22fbe695c0)], [t1.c3(0x7f22fbe69840)], [t1.c4(0x7f22fbe69ac0)]), filter(nil),
      access([t1.c1(0x7f22fbe69340)], [t1.c2(0x7f22fbe695c0)], [t1.c3(0x7f22fbe69840)], [t1.c4(0x7f22fbe69ac0)]), partitions(p0),
      is_index_back=false,
      range_key([t1.c1(0x7f22fbe69340)]), range[1 ; 1],
      range_cond([t1.c1(0x7f22fbe69340) = 1(0x7f22fbe68cf0)])

Q2:
obclient> EXPLAIN EXTENDED SELECT * FROM t1 WHERE c2 < 1 AND c3 < 1 AND
         c4 < 1\G
*************************** 1. row ***************************
Query Plan:
| ======================================
|ID|OPERATOR  |NAME  |EST. ROWS|COST |
--------------------------------------
|0 |TABLE SCAN|t1(k1)|100      |12422|
======================================

Outputs & filters:
-------------------------------------
  0 - output([t1.c1(0x7f22fbd1e220)], [t1.c2(0x7f227decec40)], [t1.c3(0x7f227decf9b0)], [t1.c4(0x7f22fbd1dfa0)]), filter([t1.c3(0x7f227decf9b0) < 1(0x7f227decf360)], [t1.c4(0x7f22fbd1dfa0) < 1(0x7f22fbd1d950)]),
      access([t1.c2(0x7f227decec40)], [t1.c3(0x7f227decf9b0)], [t1.c4(0x7f22fbd1dfa0)], [t1.c1(0x7f22fbd1e220)]), partitions(p0),
      is_index_back=true, filter_before_indexback[true,false],
      range_key([t1.c2(0x7f227decec40)], [t1.c3(0x7f227decf9b0)], [t1.c1(0x7f22fbd1e220)]),
      range(NULL,MAX,MAX ; 1,MIN,MIN),
      range_cond([t1.c2(0x7f227decec40) < 1(0x7f227dece5f0)])

Q3:
obclient> CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT, c3 INT) PARTITION BY
       HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> CREATE INDEX i2 ON t2(c2) GLOBAL;
Query OK, 0 rows affected
obclient> EXPLAIN SELECT * FROM t2 WHERE c2 = 1;
+------------------------------------------------------------------+
| Query Plan                                                       |
+------------------------------------------------------------------+
| ========================================================         |
| |ID|OPERATOR              |NAME  |EST.ROWS|EST.TIME(us)|         |
| --------------------------------------------------------         |
| |0 |DISTRIBUTED TABLE SCAN|t2(i2)|1       |28          |         |
| ========================================================         |
| Outputs & filters:                                               |
| -------------------------------------                            |
|   0 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=256 |
|       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])                                    |
+------------------------------------------------------------------+

O4:
obclient> CREATE TABLE t3(pk INT, c1 INT, c2 INT, PRIMARY KEY(pk));
Query OK, 0 rows affected
obclient> INSERT INTO t3 (pk,c1,c2) SELECT LEVEL, MOD(LEVEL,3), LEVEL FROM DUAL CONNECT BY LEVEL <= 10000;
Query OK, 10000 rows affected
Records: 10000  Duplicates: 0  Warnings: 0
obclient> CREATE INDEX i1 ON t3(c1,c2);
Query OK, 0 rows affected
obclient> EXPLAIN SELECT * FROM t3 WHERE c2 = 1;
+-----------------------------------------------------------------------------------------+
| Query Plan                                                                              |
+-----------------------------------------------------------------------------------------+
| =================================================                                       |
| |ID|OPERATOR       |NAME  |EST.ROWS|EST.TIME(us)|                                       |
| -------------------------------------------------                                       |
| |0 |INDEX SKIP SCAN|T3(I1)|1       |11          |                                       |
| =================================================                                       |
| Outputs & filters:                                                                      |
| -------------------------------------                                                   |
|   0 - output([T3.PK], [T3.C1], [T3.C2]), filter([T3.C2 = 1]), rowset=256                |
|       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)                           |
+-----------------------------------------------------------------------------------------+
12 rows in set

In the preceding example, the outputs & filters section in the execution plan display shows in detail the output information of the TABLE SCAN operator.

Parameter	Description
operator	The `TABLE SCAN` operator has three forms: `TABLE SCAN`, `TABLE GET` and `INDEX SKIP SCAN`. `TABLE SCAN` is a range scan that 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. `INDEX 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 this example, `filter` is set to `nil` because no filter condition is configured for the `TABLE GET` operator in Q1.
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 Q1, the primary table is selected. Therefore, table access by index primary key is not required. In query Q2, where the indexed columns are `c2, c3, and c1`, table access by index primary key is required because the query needs to return column `c4`.
is_global_index	Indicates whether the operator scans a global index. 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 operation. 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`: indicates 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 of an index scan.
prefix_columns_cnt	The number of prefix columns for the `INDEX SKIP SCAN` operator.
skip_scan_range	The scan range extracted by the `INDEX SKIP SCAN` operator from the suffix columns.

In query Q4, the INDEX SKIP SCAN operator can accelerate scanning by using existing indexes in specific circumstances. OceanBase Database uses the INDEX 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 an index skip scan has lower costs than a full-table scan does.