SELECT COUNT(*) 与使用显式游标获取两次

Question

我读过一本书，书名是“Oracle PL SQL 编程”（第二版），作者是 Steven Feuerstein & 。 比尔·普比尔. 在第 99 页上，有一点建议

不要从表中“SELECT COUNT(*)”，除非您确实需要知道“点击”的总数。 如果您只需要知道是否存在多个匹配项，只需使用显式游标获取两次即可。

有人可以通过提供示例向我详细解释这一点吗？ 谢谢。

更新：

作为 Steven Feuerstein & Bill Pribyl 建议我们不要使用 SELECT COUNT() 来检查表中的记录是否存在，任何人都可以帮我编辑下面的代码，以避免使用显式游标来避免使用 SELECT COUNT(*) 吗？ 这段代码是写在Oracle存储过程中的。

我有一个表 emp(emp_id, emp_name, ...)，因此要检查提供的员工 ID 是否正确：

CREATE OR REPLACE PROCEDURE do_sth ( emp_id_in IN emp.emp_id%TYPE )
IS
v_rows INTEGER;
BEGIN
    ...

    SELECT COUNT(*) INTO v_rows
    FROM emp
    WHERE emp_id = emp_id_in;

    IF v_rows > 0 THEN
        /* do sth */
    END;

    /* more statements */
    ...

END do_sth;

Answer 1

开发人员在 PL/SQL 程序中从表中执行 select COUNT(*) 的原因有很多：

1) 他们确实需要知道表中有多少行。

在这种情况下，别无选择：选择 COUNT(*) 并等待结果。 这在许多表上会相当快，但在大表上可能需要一段时间。

2）他们只需要知道一行是否存在。

这并不能保证计算表中的所有行。 可以采用多种技术：

a) 显式游标方法：

DECLARE
   CURSOR c IS SELECT '1' dummy FROM mytable WHERE ...;
   v VARCHAR2(1);
BEGIN
   OPEN c;
   FETCH c INTO v;
   IF c%FOUND THEN
      -- A row exists
      ...
   ELSE
      -- No row exists
      ...
   END IF;
END;

b) SELECT INTO 方法

DECLARE
   v VARCHAR2(1);
BEGIN
   SELECT '1' INTO v FROM mytable 
   WHERE ... 
   AND ROWNUM=1; -- Stop fetching if 1 found
   -- At least one row exists
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      -- No row exists
END;

c) 使用 ROWNUM 方法的 SELECT COUNT(*)

DECLARE
   cnt INTEGER;
BEGIN
   SELECT COUNT(*) INTO cnt FROM mytable 
   WHERE ... 
   AND ROWNUM=1; -- Stop counting if 1 found
   IF cnt = 0 THEN
      -- No row found
   ELSE
      -- Row found
   END IF;
END;

3) 他们需要知道是否存在多于 1 行。

(2) 工作技术的变体：

a) 显式游标方法：

DECLARE
   CURSOR c IS SELECT '1' dummy FROM mytable WHERE ...;
   v VARCHAR2(1);
BEGIN
   OPEN c;
   FETCH c INTO v;
   FETCH c INTO v;
   IF c%FOUND THEN
      -- 2 or more rows exists
      ...
   ELSE
      -- 1 or 0 rows exist
      ...
   END IF;
END;

b) SELECT INTO 方法

DECLARE
   v VARCHAR2(1);
BEGIN
   SELECT '1' INTO v FROM mytable 
   WHERE ... ;
   -- Exactly 1 row exists
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      -- No row exists
   WHEN TOO_MANY_ROWS THEN
      -- More than 1 row exists
END;

c) SELECT COUNT(*) 和 ROWNUM 方法

DECLARE
   cnt INTEGER;
BEGIN
   SELECT COUNT(*) INTO cnt FROM mytable 
   WHERE ... 
   AND ROWNUM <= 2; -- Stop counting if 2 found
   IF cnt = 0 THEN
      -- No row found
   IF cnt = 1 THEN
      -- 1 row found
   ELSE
      -- More than 1 row found
   END IF;
END;

您使用哪种方法很大程度上取决于偏好（以及一些宗教狂热！） Steven Feuerstein始终偏爱显式游标而不是隐式游标（SELECT INTO 和游标 FOR 循环）； Tom Kyte 赞成隐式游标（我同意他的观点）。

重要的一点是，在不限制 ROWCOUNT 的情况下选择 COUNT(*) 的成本很高，因此只有在真正需要计数时才应该这样做。

至于你关于如何用显式游标重写它的补充问题：

CREATE OR REPLACE PROCEDURE do_sth ( emp_id_in IN emp.emp_id%TYPE )
IS
v_rows INTEGER;
BEGIN
    ...

    SELECT COUNT(*) INTO v_rows
    FROM emp
    WHERE emp_id = emp_id_in;

    IF v_rows > 0 THEN
        /* do sth */
    END;

    /* more statements */
    ...

END do_sth;

那就是：

CREATE OR REPLACE PROCEDURE do_sth ( emp_id_in IN emp.emp_id%TYPE )
IS
    CURSOR c IS SELECT 1
                FROM emp
                WHERE emp_id = emp_id_in;
    v_dummy INTEGER;
BEGIN
    ...

    OPEN c;    
    FETCH c INTO v_dummy;
    IF c%FOUND > 0 THEN
        /* do sth */
    END;
    CLOSE c;

    /* more statements */
    ...

END do_sth;

但实际上，在你的例子中它没有更好或更坏，因为你正在选择主键并且Oracle足够聪明知道它只需要获取一次。

Answer 2

如果您只对两个感兴趣，请尝试

SELECT 'THERE ARE AT LEAST TWO ROWS IN THE TABLE'
FROM DUAL
WHERE 2 =
(
    SELECT COUNT(*)
    FROM TABLE
    WHERE ROWNUM < 3
)

它比执行手动光标方法需要更少的代码，
而且可能会更快。

rownum 技巧意味着一旦有两行就停止获取行。

如果您不对 count(*) 设置某种限制，则可能需要很长时间才能完成，具体取决于您拥有的行数。 在这种情况下，使用游标循环手动从表中读取 2 行会更快。

Answer 3

这来自于编写类似于以下代码的程序员（这是伪代码！）。

您想要检查客户是否有多个订单：

if ((select count(*) from orders where customerid = :customerid) > 1)
{
    ....
}

这是一种非常低效的做事方式。 正如 Mark Brady 所说，如果你想知道一个罐子里是否有硬币，你会数一下里面的所有硬币吗？罐子，或者只是确保有 1 个（或在您的示例中为 2 个）？

这可以更好地写为：

if ((select 1 from (select 1 from orders where customerid = :customerid) where rownum = 2) == 1)
{
    ....
}

这可以防止“计算所有硬币”的困境，因为 Oracle 将获取 2 行，然后完成。 前面的示例将导致 Oracle 扫描（索引或表）所有行，然后完成。

Answer 4

他的意思是打开一个游标，不仅获取第一条记录，还获取第二条记录，然后你就会知道有多个记录。

因为我似乎从来不需要知道 SELECT COUNT(*) 是 >= 2，所以我不知道为什么这在任何 SQL 变体中都是一个有用的习惯用法。 要么没有记录，要么至少有一个，当然，但不是两个或更多。 不管怎样，总有EXISTS。

事实上，Oracle 的优化器似乎相当糟糕...... - 我会质疑该技术的相关性。

针对 TheSoftwareJedi 的评论：

WITH CustomersWith2OrMoreOrders AS (
    SELECT CustomerID
    FROM Orders
    GROUP BY CustomerID
    HAVING COUNT(*) >= 2
)
SELECT Customer.*
FROM Customer
INNER JOIN CustomersWith2OrMoreOrders
    ON Customer.CustomerID = CustomersWith2OrMoreOrders.CustomerID

适当的索引，即使在 SQL Server 中进行像这样的全宇宙查询，我也从未遇到过性能问题。 然而，我经常在这里和其他网站上遇到有关 Oracle 优化器问题的评论。

我自己使用 Oracle 的经验没有很好。

OP 的评论似乎是说优化器不能很好地处理表中的完整 COUNT(*) 。 即：（

IF EXISTS (SELECT COUNT(*) FROM table_name HAVING COUNT(*) >= 2)
BEGIN
END

当主键存在时，可以简化为简单的索引扫描 - 在极端优化的情况下，可以简单地查询 sysindexes.rowcnt 中的索引元数据 - 查找条目数 - 全部无需游标）是通常避免的，有利于：

DECLARE CURSOR c IS SELECT something FROM table_name;
BEGIN
    OPEN c
    FETCH c INTO etc. x 2 and count rows and handle exceptions
END;

IF rc >= 2 THEN BEGIN
END

对我来说，这会导致代码可读性较差，可移植性较差，并且可维护性较差。

Answer 5

在你太认真地对待 Steven Feuerstein 的建议之前，先做一些基准测试。 在您的情况下， count(*) 是否明显比显式光标慢？ 不？ 然后更好地使用允许简单、可读代码的构造。 在大多数情况下，这将是“select count(*) into v_cnt ... if v_cnt>0 then ...”

PL/SQL 允许非常可读的程序。 不要仅仅为了纳米优化而浪费时间。

Answer 6

根据数据库的不同，可能有一个 sys 表存储近似计数并可以在恒定时间内查询。 如果您想知道表是否有 20 行、20,000 行或 20,000,000 行，此功能很有用。

Answer 7

SQL Server：

if 2 = (
    select count(*) from (
        select top 2 * from (
            select T = 1 union
            select T = 2 union
            select T = 3 ) t) t)
    print 'At least two'

另外，永远不要使用游标。 如果你认为你真的需要它们，那就用铲子敲打自己，直到你改变主意。 让远古的遗迹继续成为远古的遗迹。

Answer 8

如果你想获取表中的行数，请不要使用count(*)，我建议使用count(0)，0是主键列的列索引。