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

      Try this one:

   declare

      cursor c_exits_row (p_ColA  <ColA datatype>)
      is
         select ColA -- create an index upon this column to make is faster
         from table
         where ColA = p_ColA;

         w_ColA   table.ColA%TYPE;

      ...

   begin

      ...

      open c_exists_row ('ABC');
      fetch c_exists_row into w_ColA;
      if c_exists_row%FOUND
      then
         < code for existing row >
      end if;
      close c_exists_row;

      ...

   end;

      Since any "select ... into ..." issues two fetchs to make sure    there is just one line returned, the above code will do it faster,    having just one fetch. The open and close cursor statements are    also issued (implictly) by the select and the index on ColA is to    make the query faster and to enable Oracle to only look at the    index, skiping an access to the table. You can also use "select    <constant> ...", but you will still need an index on ColA.

      Hope it helps,

-- 

                     Aram Meguerian
                     aram_at_unisys.com.br

-------------------------------------------------------------------
  TANSTAAFL - There ain't no such thing as a free lunch 
                                           by Robert A. Heinlein
-------------------------------------------------------------------
     I don't work at Unisys, it is just my Internet Provider, 
     so don't blame it for anything I have just said.
-------------------------------------------------------------------



Bud Lo <budlo_at_netvigator.com> escreveu no artigo
<335b45b1.12345729_at_news.netvigator.com>...


> 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?

> 

> 

> thanks, 

> Bud

>