LIMIT演算子は、データ出力の行数を制限するために使用され、これはMySQLのLIMIT演算子と同じ機能です。
OceanBaseデータベースのMySQLモードでLIMITを含むSQL文を処理する際、SQLオプティマイザーは常にLIMIT演算子を生成しますが、一部の特殊なシナリオでは割り当てられない場合もあります。例えば、LIMITがベーステーブルにダウンロード可能な場合、割り当てる必要はありません。
一方、OceanBaseデータベースのOracleモードでは、以下の2つのシナリオでLIMIT演算子が割り当てられます：
ROWNUMがSQLオプティマイザーによって書き換えられて生成された場合Oracle12cの
FETCH機能との互換性を保つため
MySQLモードにおけるLIMIT演算子を含むSQLのシナリオ
例1：OceanBaseデータベースのMySQLモードにおけるLIMIT演算子を含むSQLのシナリオ
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)
上記の例では、Q1クエリの実行計画表示におけるoutputs & filters詳細列に、LIMIT演算子の出力情報が以下のように詳細に示されています：
| 情報名 | 意味 |
|---|---|
| output | この演算子が出力する式。 |
| filter | この演算子におけるフィルタ条件。例ではLIMIT演算子にfilterが設定されていないため、nilとなります。 |
| limit | 出力する行数を制限する定数。 |
| offset | 現在の位置からのオフセット行数であり、定数です。 |
Q2クエリの実行計画表示では、SQLにLIMITが含まれているものの、LIMIT演算子は割り当てられず、関連する式がTABLE SCAN演算子にダウンコンパイルされています。例のSQLにoffsetが含まれていないため、生成された計画ではnilとなっています。このようなLIMIT行のダウンコンパイルは、SQLオプティマイザーの一種の最適化手法です。詳細については、TABLE SCANを参照してください。
OracleモードにおけるCOUNT演算子を含むSQLのLIMIT演算子への書き換え
Oracleモードでは、COUNTを含むSQLをLIMITに書き換える場合があります。この書き換えについては、COUNT演算子の章で既に説明しています。詳細については、COUNTを参照してください。
OracleモードにおけるFETCHを含むSQLシナリオ
例2：OceanBaseデータベースのOracleモードにおけるFETCHを含むSQLシナリオ
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)
上記の例では、Q3とQ4のクエリの実行計画表示は、以前のMySQLモードのQ1とQ2のクエリと基本的に同じです。これは、Oracle 12cのFETCH機能がMySQLのLIMIT機能と類似しているためです。両者の違いは、Q5の実行計画表示に示されています。
実行計画表示のoutputs & filters列には、LIMIT演算子の出力情報が詳細に示されています。
| 情報名 | 意味 |
|---|---|
| output | この演算子が出力する式。 |
| filter | この演算子におけるフィルタ条件。例ではLIMIT演算子にfilterが設定されていないため、nilとなります。 |
| limit | 出力する行数を制限する定数。 |
| offset | 現在の位置からのオフセット行数であり、定数です。 |
| percent | データの総行数に対する割合で出力する定数。 |
| with_ties | ソート後に最後の行を等しい値で一緒に出力するかどうか。例えば、最後の行を出力するよう求められている場合、ソート後に2行の値が1となっているとします。もし最後の行を等しい値で一緒に出力するよう設定されている場合、その2行は両方とも出力されます。 |
上記のLIMIT演算子に追加された計画表示プロパティはすべて、OracleモードのFETCH機能特有のものであり、MySQLモードの計画には影響しません。Oracle12cのFETCH構文の詳細については、Oracle 12c Fetch Rowsを参照してください。