All Products
Search
Document Center

COUNT

Last Updated: Jun 18, 2021

The COUNT operator is used to ensure compatibility with the Oracle ROWNUM function, 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 function into one that contains the LIMIT operator. In this case, the COUNT operator is no longer assigned.

Assign a COUNT operator

Example 1: Assigning a COUNT operator in an SQL query that contains the ROWNUM function

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(3, 3);
Query OK, 1 rows affected (0.12 sec)

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

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 | Keyboard      |
|    2 | Mouse      |
|    5 |      3 |
+------+--------+
3 rows in set (0.01 sec)

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

Field

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, the 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 the auto-increment of the ROWNUM expression.

A scenario where the COUNT operator is not assigned

Example 2: Rewriting an SQL statement that contains ROWNUM into 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 enables you to optimize the query further. For more information, see LIMIT.