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

Frank van Bortel wrote:
>
> 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

The only problem with

open cur
fetch
close

is that you've given Oracle no information as to the fact that you're just doing an existence check. The way to do this would be to add "and rownum=1" or the wrapper with dual as previously posted.

hth
connor

-- 
-------------------------------
Connor McDonald
http://www.oracledba.co.uk
Co-Author: "Mastering Oracle PL/SQL - Practical Solutions"