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

"Frank van Bortel" <fvanbortel_at_netscape.net> wrote in message news:c14q3j$jte$1_at_news2.tilbu1.nb.home.nl...
> 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
>

Personally I've nothing on the contrary Frank. The fact you are suggesting the cursor option is an excellent contribution to the discussion, the aim of the original message was to say a definitive word about this topic, because I had a feeling that it could be possible to find out what the best approach is, in terms of cpu, read, gets, latches and so on.
I mean, if we cannot demonstrate what the best available method is for an apparently simple problem like this, what can we do with much more complex queries?

I'll set up some test and get some output from TKPROF, just to see whether we can say a definitive word about this subject.

Bye,
Flavio Received on Fri Feb 20 2004 - 15:21:08 CST