The LIMIT operator limits the number of output rows. It has the same function as the LIMIT operator of MySQL.
In MySQL mode of OceanBase Database, the SQL optimizer generates a LIMIT operator when processing an SQL statement that includes a LIMIT clause. However, this is not required in some particular scenarios, such as when the LIMIT operator can be pushed down to the base table.
In Oracle mode of the OceanBase Database, the SQL optimizer assigns a LIMIT operator in the following two scenarios:
The
ROWNUMis rewritten by the SQL optimizer.It is necessary to be compatible with the
FETCHclause of Oracle 12c.
SQL statements that have a LIMIT clause in MySQL mode
Example 1: an SQL statement that has a LIMIT clause in MySQL mode of OceanBase 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 Outputs & filters section for query Q1 shows in detail the output information of the LIMIT operator.
Field |
Description |
|---|---|
| output | The output expressions of the operator. |
| filter | The filter conditions of the operator. In this example, filter is set to nil because no filter condition is configured for the LIMIT operator. |
| limit | A constant that indicates the limited number of rows in the output. |
| offset | A constant that indicates the number of rows offset from the current position. |
In the execution plan display of query Q2, the SQL statement contains a LIMIT clause, but no LIMIT operator is assigned. Instead, the expression is pushed down to the TABLE SCAN operator. The value of this field is nil in the generated plan because the SQL statement does not contain offset. Pushdown of LIMIT is an optimization method by the SQL optimizer. For more information, see TABLE SCAN.
SQL statements rewritten for the execution plan to use a LIMIT operator instead of a COUNT operator in Oracle mode
For more information about this scenario, see COUNT.
SQL statements that have a FETCH clause in Oracle mode
Example 2: SQL statements that have a FETCH clause in Oracle mode of OceanBase Database
obclient> CREATE TABLE T1(c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> CREATE TABLE T1(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
Q3:
obclient> EXPLAIN SELECT * FROM t1,t2 OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;
Query Plan:
| =====================================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
-----------------------------------------------------
|0 |LIMIT | |1 |238670 |
|1 | NESTED-LOOP JOIN CARTESIAN| |2 |238669 |
|2 | TABLE SCAN |T1 |1 |36 |
|3 | MATERIAL | |100000 |238632 |
|4 | TABLE SCAN |T2 |100000 |64066|
=====================================================
Outputs & filters:
-------------------------------------
0 - output([T1.C1], [T1.C2], [T2.C1], [T2.C2]), filter(nil), limit(?) , offset(?)
1 - output([T1.C1], [T1.C2], [T2.C1], [T2.C2]), filter(nil),
conds(nil), nl_params_(nil)
2 - output([T1.C1], [T1.C2]), filter(nil),
access([T1.C1], [T1.C2]), partitions(p0)
3 - output([T2.C1], [T2.C2]), filter(nil)
4 - output([T2.C1], [T2.C2]), filter(nil),
access([T2.C1], [T2.C2]), partitions(p0)
Q4:
obclient> EXPLAIN SELECT * FROM t1 FETCH NEXT 1 ROWS ONLY;
Query Plan:
| ===================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|T1 |1 |37 |
===================================
Outputs & filters:
-------------------------------------
0 - output([T1.C1], [T1.C2]), filter(nil),
access([T1.C1], [T1.C2]), partitions(p0),
limit(?) , offset(nil)
Q5:
obclient> EXPLAIN SELECT * FROM t2 ORDER BY c1 FETCH NEXT 10 PERCENT ROW WITH TIES;
Query Plan:
| =======================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
---------------------------------------
|0 |LIMIT | |10000 |573070|
|1 | SORT | |100000 |559268|
|2 | TABLE SCAN|T2 |100000 |64066 |
=======================================
Outputs & filters:
-------------------------------------
0 - output([T2.C1], [T2.C2]), filter(nil), limit(nil), offset(nil), percent(?) , with_ties(true)
1 - output([T2.C1], [T2.C2]), filter(nil), sort_keys([T2.C1, ASC])
2 - output([T2.C1], [T2.C2]), filter(nil),
access([T2.C1], [T2.C2]), partitions(p0)
In the preceding example, the execution plans generated for Q3 and Q4 are basically the same as those for Q1 and Q2 in MySQL mode. This is because the FETCH feature in Oracle 12c is similar to the LIMIT feature in MySQL. Their differences are shown in the Q5 execution plan.
In the execution plan display, the Outputs & filters section shows in detail the output information of the LIMIT operator.
Field |
Description |
|---|---|
| output | The output expressions of the operator. |
| filter | The filter conditions of the operator. In this example, filter is set to nil because no filter condition is configured for the LIMIT operator. |
| limit | A constant that indicates the limited number of rows in the output. |
| offset | A constant that indicates the number of rows offset from the current position. |
| percent | A constant that indicates the percentage of rows to be included in the output among the total number of rows. |
| with_ties | Indicates whether to include rows that are equal to the last row in the output after sorting. Assume that you want to include the last row in the output after sorting. However, two rows share the same value 1 after sorting. If you set with_ties to true, the two rows are both included in the output. |
In the preceding execution plan display, new attributes of the LIMIT operator are specific to the FETCH clause in Oracle mode and do not affect plans in MySQL mode. For more information about the syntax of FETCH in Oracle 12c, see Oracle 12c Fetch Rows.