All Products
Search
Document Center

TABLE SCAN

Last Updated: Jun 18, 2021

The TABLE SCAN operator provides an interface between the storage layer and SQL layer, and shows which index the optimizer selected when accessing data.

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 with 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 (0.09 sec)

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.

Field

Description

operator

TABLE SCAN and TABLE GET are two forms of the TABLE SCAN operator.

  • TABLE SCAN is a range scan and returns 0 or multiple rows of data.

  • TABLE GET locates a row by using a primary key and returns 0 or 1 row of data.

name

Indicates the index selected for accessing data. The name of the selected index appears after the table name. The absence of the index name means that the primary table is scanned.

Note that, in OceanBase Database, the primary table has the same structure as the index, and the primary table is itself an index.

output

The output columns of the operator.

filter

The filter predicates of the operator.

In this example, the condition is set to nil because no filter is configured for the TABLE SCAN 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.

For example, in query Q1,Table access by index primary key is not needed because the primary table is selected. In query Q2, where the indexed columns are c2, c3, and c1, table access by index primary key is required as 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 < 1can be directly applied to the index, which reduces the number of TABLE ACCESS BY INDEX PRIMARY KEY operations. However, filter c4 < 1 is 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 tell if it is a full table scan by the value of range. For example, in a scenario with three rowkey columns, range(MIN,MIN, MIN ; MAX, MAX, MAX) definitely represents a full table scan.

  • range_cond: predicates that determine the start and end positions of an index scan.