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

From: Rafael Ramirez <raffy_at_erika.upd.edu.ph>
Date: 1997/04/28
Message-ID: <Pine.SUN.3.91.970428082437.10651B-100000@erika.upd.edu.ph>#1/1

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. You don't use a GROUP BY as impied by a COUNT() and you don't have the hassle of checking TOO_MANY_ROWS or NO_DATA_FOUND as in a SELECT INTO (also, SELECT INTO would make an extra fetch even if one row was already obtained - that's how it determines that it doesn't need to set the TOO_MANY_ROWS exception). The implicit cursor FOR eliminates the need for OPEN, FETCH and CLOSE - but if you use the search more than once, say in the same package or procedure, it is better to use the explicit cursor.

Another benefit is if you want to get something else from the table aside from check for existence, it is easy to do it and you can even replace the 'found' variable with it as follows:

DECLARE
  ...
  code_value VARCHAR2; /* its value is NULL originially */ BEGIN
  ...
  FOR dummy_row IN (SELECT code_value

                      FROM ...
                     WHERE ...) LOOP

    code_value := dummy_row.code_value;
    EXIT; /* to exit FOR once a row has been found */   END LOOP;
  ... /* now code_value is NULL if no row was found, else

         you can now use it for further processing */ END; HTH. Rafael S. Ramirez raffy_at_erika.upd.edu.ph Independent Oracle Consultant Professor, Department of Electrical and Electronics Engg. Snail Mail: c/o EEE Dept., University of the Philippines, Diliman QC 1101

On Thu, 24 Apr 1997, Jonas Plumecocq wrote:

>
> Bud Lo wrote:
>
> On Sat, 19 Apr 1997 16:39:13 +0100, Jim Smith
> <jim_at_jimsmith.demon.co.uk> wrote:
>
> >
> >In article <335b45b1.12345729_at_news.netvigator.com>, Bud Lo
> ><budlo_at_netvigator.com> writes
> >>Hi all,
> >>My requirement is very simple.
> >>
> >>To check a record's existence in a table. ( i am using Oracle 7)
> >>
> >>Obviously, this SQL will do:
> >>
> >>select count(*) into nCount from table where ColA='ABC';
> >>
> >>But i only want to know whether the record
> >>exists or not, the exact count of record is immaterial.
> >>As my table is very large, the count(*) will scan
> >>for all records in the table. My required behavior is that
> >>the SQL returns whenever the first record found and stop
> >>further scanning. So it would be faster.
> >>
> >>The solution i thought:
> >>
> >>1st
> >>===
> >>nCount := 0;
> >>
> >>select 1 into nCount from dual
> >>where exists (select * from table where ColA='ABA');
> >>
> >>2nd (Don't sure whether it performs as i required)
> >>==================================================
> >>select count(*) into nCount from table where ColA='ABC' and
> >>rownum=1;
> >>
> >>I want to find the elegant solution to this problem,
> >>2nd alternative will do if it stop scanning when
> >>1st record is found, anyone can confirm? or anyone
> >>suggest a better solution?
> >>
> >>
> >The first option is bestt, but neither will perform very well
 without a
> >(unique ?) index on colA.
> >
> >The second option won't work as you expect. A count(*) only returns
 one
> >row so the rownum=1 is meaningless.
> >--
> >Jim Smith
>
> The row count is immaterial to me, i just want to know if any record
>
> exists or not. The count(*) used in second option is used just to
> facilitate the checking for value return (0 or 1) in a SQL statement
> .
> like
>
> select count(*) into nCount from table where x='aa' and rownum=1;
> if nCount = 1 then
> ....
> end if;
>
> The reason i prefer 2nd option is that it is more compact and less
> coding, but not sure whether it would fetch all records matching
> the condition and return the 1st record OR it would only fetch the
> 1st
> found record and return.
>
> thanks,
> Bud
>
> Isn't rownum an SQL*PLUS extension and hence only applies to the
> retrieved result set as obtained from the server?
>
> --
> Jonas Plumecocq
> jp_at_internex.com.au
>
>
>
Received on Mon Apr 28 1997 - 00:00:00 CDT