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

Chuck <chuckh_nospam_at_softhome.net> wrote in message news:<Xns94937D64E2FFchuckhsofthomenet_at_130.133.1.4>...
> Mark.Powell_at_eds.com (Mark D Powell) wrote in
> news:2687bb95.0402180702.19b9e3f7_at_posting.google.com:
>
> > flavio_at_tin.it (FC) wrote in message
> > news:<2bd78ddf.0402180239.62960672_at_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
> >
> > Flavio, select count(*) always returns a value so no rows = 0 and by
> > using an exists you limit the maximum value returned to 1.
> >
> > HTH -- Mark D Powell --
> >
>
> But you probably don't want to query the entire table of there are
> millions of rows. I'd use a simple query like this...
>
> select count(*) from mytable where rownum < 2;
>
> Returns 0 if no rows exist, or 1 if 1 or more rows exit.

Chuck, if the desired test was to check to see if any data existed in the target table your query would be correct; however, the original requirement specified that additional conditions besides just rows exist as part of the problem. Still your query points out a potential problem in some cases. While the exists clause would stop the execution of the query as soon as a single row meeting the conditions was found, in the case where no row exists Oracle still has to do all the work necessary to find all possible rows that meet the query conditions. In some queries this can be expensive and lead to uneven test performance; however, normally this is the most efficient way to conditionally test for the existance of data meeting certain requirements.

HTH -- Mark D Powell -- Received on Thu Feb 19 2004 - 08:43:54 CST