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, the table access logic for normal indexes is encapsulated in the TABLE SCAN operator. For global indexes, however, the logic of table access by index primary key is completed by the TABLE LOOKUP operator.
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)])
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 two forms: TABLE SCAN and TABLE GET.
|
| 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 itself 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. |
| 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 |
|