The COUNT operator is used to ensure compatibility with the ROWNUM feature of Oracle and implement the auto-increment of the ROWNUM expression.
Generally, when an SQL query contains a ROWNUM expression, the SQL optimizer assigns a COUNT operator when it generates an execution plan for the query. In some cases, however, the SQL optimizer rewrites the SQL query that contains the ROWNUM expression to one that contains the LIMIT operator. In this case, the COUNT operator is no longer assigned.
Note
The COUNT operator is supported only in OceanBase Database in Oracle mode.
Assign a COUNT operator
Example 1: Assign a COUNT operator in an SQL query that contains the ROWNUM expression.
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(3, 3);
Query OK, 1 rows affected
obclient> INSERT INTO t1 VALUES(5, 5);
Query OK, 1 rows affected
Q1:
obclient> EXPLAIN SELECT c1,ROWNUM FROM t1\G
*************************** 1. row ***************************
Query Plan:
| ====================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
------------------------------------
|0 |COUNT | |1 |37 |
|1 | TABLE SCAN|T1 |1 |36 |
====================================
Outputs & filters:
-------------------------------------
0 - output([T1.C1], [rownum()]), filter(nil)
1 - output([T1.C1]), filter(nil),
access([T1.C1]), partitions(p0)
obclient> SELECT c1,ROWNUM FROM t1;
+------+--------+
| C1 | ROWNUM |
+------+--------+
| 1 | 1 |
| 3 | 2 |
| 5 | 3 |
+------+--------+
3 rows in set
In the preceding example, the outputs & filters section in the execution plan display shows in detail the output information of the COUNT operator.
| Parameter | Description |
|---|---|
| output | The output expression of the operator. rownum() indicates the expression corresponding to ROWNUM. |
| filter | The filter conditions of the operator. In this example, filter is set to nil because no filter condition is configured for the COUNT operator. |
The output of the preceding example shows that the initial value of the corresponding ROWNUM expression is 1. Every time the COUNT operator runs, it adds 1 to the value of the ROWNUM expression to implement auto-increment.
Assign no COUNT operator
Example 2: Rewrite an SQL statement that contains ROWNUM to one that contains LIMIT without assigning a COUNT operator.
Q2:
obclient> EXPLAIN SELECT 1 FROM DUAL WHERE ROWNUM < 2\G
*************************** 1. row ***************************
Query Plan:
| ====================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
------------------------------------
|0 |LIMIT | |1 |1 |
|1 | EXPRESSION| |1 |1 |
====================================
Outputs & filters:
-------------------------------------
0 - output([1]), filter(nil), limit(?), offset(nil)
1 - output([1]), filter(nil)
values({1})
The output of the preceding example shows that in the new SQL query, you can still find ROWNUM. However, the expression that contains ROWNUM is already placed with the equivalent LIMIT expression. This conversion allows you to optimize the query further. For more information, see LIMIT.