Re: Q: To check a record's existence in a table, FAST!

In article <Pine.SUN.3.91.970428082437.10651B-100000_at_erika.upd.edu.ph>, Rafael Ramirez <raffy_at_erika.upd.edu.ph> writes
>The simplest and most efficient I know of is to use a PL/SQL FOR loop
>with an implicit cursor:
>
>DECLARE
> ...
> found BOOLEAN;
> ...
>BEGIN
> found := FALSE;
> FOR dummy_row IN (SELECT 1
> FROM table_to_be_searched
> WHERE condition_for_search) LOOP
> found := TRUE; /* this is reached only when a row is found */
> EXIT; /* terminates the loop once the first row is fetched */
> END LOOP;
> ...
>END;
>
>The benefit here of course is that only one row, if ever it exists, is
>fetched.

Rafael,

Just a thought...

If there are a million records matching the criteria, won't Oracle try and retrieve them all - and the 'found' will only be set after the first batch of results have been returned?

Wouldn't the use of 'rownum' reduce the number of rows retrieved to just the one required?

I'm probably wrong, I'm not really into the tuning side of life - that's my next job of learning!

Andy Hardy. PGP key available on request