The LIMIT operator is used to limit the number of output rows and has the same function as the LIMIT operator in MySQL.
In the MySQL-compatible mode of OceanBase AI Database, the SQL optimizer generates a LIMIT operator for SQL statements that contain LIMIT. However, it may not assign one in some special scenarios. For example, if the LIMIT clause can be pushed down to the base table, no LIMIT operator is required.
SQL scenarios with the LIMIT operator
Example 1: SQL scenarios with the LIMIT operator in OceanBase AI Database
obclient> CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> CREATE TABLE t2(c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> INSERT INTO t1 VALUES(1, 1);
Query OK, 1 rows affected
obclient> INSERT INTO t1 VALUES(2, 2);
Query OK, 1 rows affected
obclient> INSERT INTO t1 VALUES(3, 3);
Query OK, 1 rows affected
obclient> INSERT INTO t2 VALUES(1, 1);
Query OK, 1 rows affected
obclient> INSERT INTO t2 VALUES(2, 2);
Query OK, 1 rows affected
obclient> INSERT INTO t2 VALUES(3, 3);
Query OK, 1 rows affected
Q1:
obclient> EXPLAIN SELECT t1.c1 FROM t1,t2 LIMIT 1 OFFSET 1;
Query Plan:
=====================================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
-----------------------------------------------------
|0 |LIMIT | |1 |39 |
|1 | NESTED-LOOP JOIN CARTESIAN| |2 |39 |
|2 | TABLE SCAN |t1 |1 |36 |
|3 | TABLE SCAN |t2 |100000 |59654|
=====================================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1]), filter(nil), limit(1), offset(1)
1 - output([t1.c1]), filter(nil),
conds(nil), nl_params_(nil)
2 - output([t1.c1]), filter(nil),
access([t1.c1]), partitions(p0)
3 - output([t2.__pk_increment]), filter(nil),
access([t2.__pk_increment]), partitions(p0)
Q2:
obclient> EXPLAIN SELECT * FROM t1 LIMIT 2;
Query Plan:
===================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1 |2 |37 |
===================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1], [t1.c2]), filter(nil),
access([t1.c1], [t1.c2]), partitions(p0),
limit(2), offset(nil)
In the preceding example, the execution plan of query Q1 shows the output information of the LIMIT operator in the outputs & filters section as follows:
Information name |
Description |
|---|---|
| output | The output columns of this operator. |
| filter | The filter predicate of this operator. In the example, no filter is set for the LIMIT operator, so the value is nil. |
| limit | The number of rows to output, which is a constant. |
| offset | The offset from the current position, which is a constant. |
In the execution plan of query Q2, although the SQL statement contains LIMIT, no LIMIT operator is assigned. Instead, the related expressions are pushed down to the TABLE SCAN operator. Because the SQL statement in this example does not contain an offset, the generated plan shows nil. This behavior of pushing down the LIMIT clause is an optimization technique of the SQL optimizer. For more information, see TABLE SCAN.