最快的查询来检查Oracle中是否存在行? [英] Quickest query to check for the existence of a row in Oracle?

问题描述

我正在使用Oracle，并且我有一个很大的表.我需要检查是否存在满足某些简单条件的任何行.使用简单SQL进行此操作的最佳方法是什么?

I'm using Oracle, and I have a very large table. I need to check for the existence of any row meeting some simple criteria. What's the best way to go about this using simple SQL?

这是我的最佳猜测，尽管事实证明它足够快以达到我的目的，但我还是想学习一种规范的方法来基本上在Oracle中执行SQL Server的存在":

Here's my best guess, and while it may turn out to be fast enough for my purposes, I'd love to learn a canonical way to basically do SQL Server's "exists" in Oracle:

select count(x_id) from x where x.col_a = value_a and x.col_b = value_b;

然后，count()将在另一层中作为布尔值返回.要点是，我希望Oracle为该查询做最少的工作-我只需要知道是否有符合条件的行即可.

The count() would then be returned as a boolean in another tier. The main point is that I want Oracle to do the bare minimum for this query - I only need to know if there are any rows matching the criteria.

是的，这些列肯定会被索引.

And yes, those columns will most definitely be indexed.

推荐答案

如果还使用rownum = 1，则使用COUNT(*)是可以的:

Using COUNT(*) is OK if you also use rownum=1:

declare
   l_cnt integer;
begin
   select count(*)
   into   l_cnt
   from   x
   where  x.col_a = value_a 
   and    x.col_b = value_b
   and    rownum = 1;
end;

这将始终返回一行，因此无需处理任何NO_DATA_FOUND异常. l_cnt的值将为0(无行)或1(至少存在1行).

This will always return a row, so no need to handle any NO_DATA_FOUND exception. The value of l_cnt will be 0 (no rows) or 1 (at least 1 row exists).

这篇关于最快的查询来检查Oracle中是否存在行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！