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
Remove "_nospam" to reply by email
Received on Wed Feb 18 2004 - 11:19:36 CST
