fastest way to determine if 0 or more records are present in table?

From: FC <flavio_at_tin.it>
Date: 18 Feb 2004 02:39:44 -0800
Message-ID: <2bd78ddf.0402180239.62960672@posting.google.com>

Hi all,
while looking for past discussions on the fastest way to check if any records are in a table, I found a 4 years old thread suggesting that

   select 1 from dual
   where exists (select 1 from some_table where ...some condition...);

would be the best one.

In the last message of the thread, Connor McDonald stated that adding "rownum < 2" to limit the search would be pointless because Oracle would stop anyway in virtue of the "exists" clause. A quick autotrace output seems to confirm this, there is no difference in execution time and block reads with or without rownum < 2.

Now, I'd like to receive a value back in either case, say 0 if no records are present, or 1 if at least one is present.

  select 1 as flag
    from dual
   where exists (select 1 from some_table)   union all
  select 0
    from dual
   where not exists (select 1 from some_table)

Then I thought of wrapping the SQL in a function, passing the inner query as a parameter and then execute the SQL dynamically, generalizing the code.
This method seems fairly fast, replacing the "union all" with the error trapping clause saves some sorts and half of consistent gets in some situations.

FUNCTION Any_Records(par_sql_stmt in varchar2) RETURN PLS_INTEGER
IS

   flag     number;
BEGIN           

   EXECUTE IMMEDIATE 
      'select 1 from dual where exists (' || par_sql_stmt || ')' 
      INTO flag;

   RETURN 1;     EXCEPTION
   WHEN no_data_found THEN

      RETURN 0;
   WHEN OTHERS THEN RAISE;
END; But prior to go too far, do you see any other (better) methods?

Bye,
Flavio Received on Wed Feb 18 2004 - 04:39:44 CST