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

FC wrote:

> 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

What's wrong with an old fashioned cursor? Open it, read - if it fails: no records, if it doesn't: you have one or more records.

Requires 1 read.

You do not want to know how many, you just want to know if any - so don't use a count. (After Steven Feuerstein)

-- 

Regards,
Frank van Bortel