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

Question

提问by Sambath Prum

I have read a book whose title is "Oracle PL SQL Programming" (2nd ed.) by Steven Feuerstein & Bill Pribyl. On page 99, there is a point suggested that

我读过一本书，书名是 Steven Feuerstein 和 Bill Pribyl 的“Oracle PL SQL 编程”（第二版）。在第 99 页上，有一点建议

Do not "SELECT COUNT(*)" from a table unless you really need to know the total number of "hits." If you only need to know whether there is more than one match, simply fetch twice with an explicit cursor.

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

Could you anyone explain this point more to me by providing example? Thank you.

你能通过提供例子向我解释这一点吗？谢谢你。

Update:

更新：

As Steven Feuerstein & Bill Pribyl recommends us not to use SELECT COUNT() to check whether records in a table exist or not, could anyone help me edit the code below in order to avoid using SELECT COUNT(*) by using explicit cursor instead? This code is written in the Oracle stored procedure.

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

I have a table emp(emp_id, emp_name, ...), so to check the provided employee ID corret or not:

我有一个表 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

回答by Tony Andrews

There are a number of reasons why developers might perform select COUNT(*) from a table in a PL/SQL program:

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

1) They genuinely need to know how many rows there are in the table.

1）他们真的需要知道表中有多少行。

In this case there is no choice: select COUNT(*) and wait for the result. This will be pretty fast on many tables, but could take a while on a big table.

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

2) They just need to know whether a row exists or not.

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

This doesn't warrant counting all the rows in the table. A number of techniques are possible:

这并不保证计算表中的所有行。许多技术是可能的：

a) Explicit cursor method:

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 method

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) SELECT COUNT(*) with ROWNUM method

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

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) They need to know whether more than 1 row exists.

3) 他们需要知道是否存在超过 1 行。

Variations on the techniques for (2) work:

(2) 工作技术的变化：

a) Explicit cursor method:

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 method

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(*) with ROWNUM method

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;

Which method you use is largely a matter of preference (and some religious zealotry!) Steven Feuerstein has always favoured explicit cursors over implicit (SELECT INTO and cursor FOR loops); Tom Kyte favours implicit cursors (and I agree with him).

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

The important point is that to select COUNT(*) without restricting the ROWCOUNT is expensive and should therefore only be done when a count is trully needed.

重要的一点是在不限制 ROWCOUNT 的情况下选择 COUNT(*) 是昂贵的，因此应该只在真正需要计数时进行。

As for your supplementary question about how to re-write this with an explicit cursor:

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

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;

That would be:

那将是：

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;

But really, in your example it is no better or worse, since you are selecting the primary key and Oracle is clever enough to know that it only needs to fetch once.

但实际上，在您的示例中，它没有好坏之分，因为您正在选择主键，而 Oracle 足够聪明，知道它只需要获取一次。

Answer 2

回答by EvilTeach

If two is all you are interested in, try

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

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

It will take less code than doing the manual cursor method, and it is likely to be faster.

与使用手动游标方法相比，它需要的代码更少，而且速度可能更快。

The rownum trick means to stop fetching rows once it has two of them.

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

If you don't put some sort of limit on the count(*), it could take a long while to finish, depending on the number of rows you have. In that case, using a cursor loop, to read 2 rows from the table manually, would be faster.

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

Answer 3

回答by TheSoftwareJedi

This comes from programmers writing code similar to the following (this is psuedo code!).

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

You want to check to see if the customer has more than one order:

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

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

That is a terribly inefficient way to do things. As Mark Bradywould say, if you want to know if a jar contains pennies, would you count all the pennies in the jar, or just make sure there is 1 (or 2 in your example)?

这是一种非常低效的做事方式。正如马克布雷迪所说，如果你想知道一个罐子里是否有硬币，你会计算罐子里的所有硬币，还是只是确保有 1 个（或在你的例子中是 2 个）？

This could be better written as:

这可以更好地写成：

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

This prevents the "counting all of the coins" dilemma since Oracle will fetch 2 rows, then finish. The previous example would cause oracle to scan (an index or table) for ALL rows, then finish.

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

Answer 4

回答by Cade Roux

He means open a cursor and fetch not only the first record but the second, and then you will know there is more than one.

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

Since I never seem to need to know that SELECT COUNT(*)is >= 2, I have no idea why this is a useful idiom in any SQL variant. Either no records or at least one, sure, but not two or more. And anyway, there's always EXISTS.

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

That, and the fact that Oracle's optimizer seems to be pretty poor... - I would question the relevance of the technique.

那个，以及 Oracle 的优化器似乎很差的事实...... - 我会质疑该技术的相关性。

To address TheSoftwareJedi's comments:

针对 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

Appropriately indexed, I've never had performance problems even with whole universe queries like this in SQL Server. However, I have consistently run into comments about Oracle optimizer problems here and on other sites.

适当地编入索引，即使在 SQL Server 中使用像这样的整个 Universe 查询，我也从未遇到过性能问题。但是，我一直在这里和其他站点上遇到有关 Oracle 优化器问题的评论。

My own experience with Oracle has not been good.

我自己在 Oracle 方面的体验并不好。

The comment from the OP appears to be saying that full COUNT(*)from tables are not well handled by the optimizer. i.e.:

来自 OP 的评论似乎是说COUNT(*)优化器没有很好地处理完整的表。IE：

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

(which, when a primary key exists, can be reduced to a simple index scan - in a case of extreme optimization, one can simply query the index metadata in sysindexes.rowcnt - to find the number of entries - all without a cursor) is to be generallyavoided in favor of:

（当主键存在时，可以简化为简单的索引扫描 - 在极端优化的情况下，可以简单地查询 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

That, to me would result in less readable, less portable, and less maintainable code.

对我来说，这会导致代码可读性降低、可移植性降低和可维护性降低。

Answer 5

回答by Erich Kitzmueller

Before you take Steven Feuerstein's suggestions too serious, just do a little benchmark. Is count(*) noticeably slower than the explicit cursor in your case? No? Then better use the construct that allows for simple, readable code. Which, in most cases, would be "select count(*) into v_cnt ... if v_cnt>0 then ..."

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

PL/SQL allows for very readable programs. Don't waste that just to nano-optimize.

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

Answer 6

回答by Mike Kale

Depending on the DB, there may be a sys table which stores an approximate count and can be queried in constant time. Useful if you want to know whether the table has 20 rows or 20,000 or 20,000,000.

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

Answer 7

回答by yfeldblum

SQL Server:

SQL 服务器：

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'

Also, don't ever use cursors. If you think you really really need them, beat yourself with a shovel until you change your mind. Let relics from an ancient past remain relics from an ancient past.

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

Answer 8

回答by Samnang

If you want to get number of rows in a table, please don't used count(*), I would suggest count(0) that 0 is the column index of your primary key column.

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

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

提问by Sambath Prum

Update:

更新：

回答by Tony Andrews

1) They genuinely need to know how many rows there are in the table.

1）他们真的需要知道表中有多少行。

2) They just need to know whether a row exists or not.

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

a) Explicit cursor method:

a) 显式游标方法：

b) SELECT INTO method

b) SELECT INTO 方法

c) SELECT COUNT(*) with ROWNUM method

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

3) They need to know whether more than 1 row exists.

3) 他们需要知道是否存在超过 1 行。

a) Explicit cursor method:

a) 显式游标方法：

b) SELECT INTO method

b) SELECT INTO 方法

c) SELECT COUNT(*) with ROWNUM method

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

回答by EvilTeach

回答by TheSoftwareJedi

回答by Cade Roux

回答by Erich Kitzmueller

回答by Mike Kale

回答by yfeldblum

回答by Samnang

相关推荐

最近更新

标签

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

提问by Sambath Prum

Update:

更新：

回答by Tony Andrews

1) They genuinely need to know how many rows there are in the table.

1）他们真的需要知道表中有多少行。

2) They just need to know whether a row exists or not.

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

a) Explicit cursor method:

a) 显式游标方法：

b) SELECT INTO method

b) SELECT INTO 方法

c) SELECT COUNT(*) with ROWNUM method

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

3) They need to know whether more than 1 row exists.

3) 他们需要知道是否存在超过 1 行。

a) Explicit cursor method:

a) 显式游标方法：

b) SELECT INTO method

b) SELECT INTO 方法

c) SELECT COUNT(*) with ROWNUM method

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

回答by EvilTeach

回答by TheSoftwareJedi

回答by Cade Roux

回答by Erich Kitzmueller

回答by Mike Kale

回答by yfeldblum

回答by Samnang

相关推荐

Oracle 10g PL/SQL 中的“CONTINUE”关键字

Oracle user_contraints 表的constraint_type 列中的字母代码代表什么？

Oracle 中 varchar2 PL/SQL 子程序参数的大小限制是多少？

oracle 对特定用户拥有的所有表授予选择权

相关推荐

最近更新

标签