All Products
Search
Document Center

LIMIT

Last Updated: Jun 18, 2021

The LIMIT operator limits the number of output rows. It has the same function as the MySQL LIMIT operator.

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 necessary in some particular scenarios, for example, when the LIMIT operator can be pushed down to the base table.

In Oracle mode of the OceanBase Database, SQL optimizer assigns a LIMIT operator in the following two scenarios:

  • The ROWNUM is rewritten by the SQL optimizer.

  • It is necessary to be compatible with the FETCH clause of Oracle 12c.

SQL statements that have a LIMIT clause in MySQL mode

Example 1: An SQL statement that includes a LIMIT clause in MySQL mode of the OceanBase Database.

obclient>CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected (0.12 sec)

obclient>CREATE TABLE t2(c1 INT, c2 INT);
Query OK, 0 rows affected (0.12 sec)

obclient>INSERT INTO t1 VALUES(1, 1);
Query OK, 1 rows affected (0.12 sec)

obclient>INSERT INTO t1 VALUES(2, 2);
Query OK, 1 rows affected (0.12 sec)

obclient>INSERT INTO t1 VALUES(3, 3);
Query OK, 1 rows affected (0.12 sec)

obclient>INSERT INTO t2 VALUES(1, 1);
Query OK, 1 rows affected (0.12 sec)

obclient>INSERT INTO t2 VALUES(2, 2);
Query OK, 1 rows affected (0.12 sec)

obclient>INSERT INTO t2 VALUES(3, 3);
Query OK, 1 rows affected (0.12 sec)

Q1: 
obclient>EXPLAIN SELECT t1.c1 FROM t1,t2 LIMIT 1 OFFSET 1\G;
*************************** 1. row ***************************
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\G;
*************************** 1. row ***************************
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 in the execution plan display of query Q1 shows in detail the output information of the LIMIT operator.

Field

Description

output

The output expression of the operator.

filter

The filter conditions of the operator.

In this example, the condition is set to nil because no filter 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 this example, the offset value is nil in the generated plan because the SQL statement does not have an offset.

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 behavior of LIMIT pushdown is an optimization method by the SQL optimizer. For more information, see TABLE SCAN.

Rewriting an SQL statement that has a COUNT function to one that has a LIMIT clause in Oracle mode

This scenario is described in the topic of the COUNT operator. For more information, see COUNT.

SQL statements that have an 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 (0.12 sec)

obclient>CREATE TABLE T1(c1 INT, c2 INT);
Query OK, 0 rows affected (0.12 sec)

obclient>INSERT INTO t1 VALUES(1, 1);
Query OK, 1 rows affected (0.12 sec)

obclient>INSERT INTO t1 VALUES(2, 2);
Query OK, 1 rows affected (0.12 sec)

obclient>INSERT INTO t1 VALUES(3, 3);
Query OK, 1 rows affected (0.12 sec)

obclient>INSERT INTO t2 VALUES(1, 1);
Query OK, 1 rows affected (0.12 sec)

obclient>INSERT INTO t2 VALUES(2, 2);
Query OK, 1 rows affected (0.12 sec)

obclient>INSERT INTO t2 VALUES(3, 3);
Query OK, 1 rows affected (0.12 sec)

Q3: 
obclient>EXPLAIN SELECT * FROM t1,t2 OFFSET 1 ROWS 
           FETCH NEXT 1 ROWS ONLY\G;
*************************** 1. row ***************************
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\G;
*************************** 1. row ***************************
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\G;
*************************** 1. row ***************************
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 of queries Q3 and Q4 are almost the same as that of queries Q1 and Q2 in MySQL mode, because the FETCH clause of Oracle 12c has a similar function as that of the LIMIT clause of MySQL. Their differences are shown in the execution plan display of query Q5.

In the execution plan display, the Outputs & filters section shows in detail the output information of the LIMIT operator.

Field

Description

output

The output expression of the operator.

filter

The filter conditions of the operator.

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