Re: Newbie PL/SQL inquiry regarding existence check.

I still say declaring a global cursor with bind variables is the most efficient way. That way, if you use it all the time, Oracle will only optimize(?) it once in memory. Also select using a hardcoded value. It also takes time to actually retrieve a column. If you're just checking if something exists or not, then this cursor should work :

    cursor chk_exist_cur(v_var data_type)

     IS SELECT 1 as dummy
          FROM table_name
         WHERE field_name = v_var;

dorwig_at_madentech.com wrote in message <876310900.31302_at_dejanews.com>...
>Mick,
>
>A select count(*) will be expensive since all records will be accessed
>from the top of the table to the end of the table to get that count. You
>may want to look at the EXISTS function as an alternative. The function
>taks a SELECT statement as a parameter.
>
>Dan Orwig
>
>In article <343aed2c.1848618_at_news.psu.edu>,
> zlm101_at_psu.edu (Zernan Martinez) wrote:
>>
>> I don't think so. The "rownum" column will be evaluated before the
>> other conditions in the "where" clause.
>>
>> Zern
>>
>> On 7 Oct 1997 20:04:19 GMT, "Gerard H. Pille" <ghp_at_skynet.be> wrote:
>>
>> >You might try to add a "and rownum <= 1" to the where-clause.
>> >--
>> >Kind reGards,
>> >
>> >Gerard
>> >(ghp_at_santens.be; ghp_at_skynet.be)
>> >
>> >Mick Davies <mdavies_at_elekom.com> schreef in artikel
>> ><01bcd340$ad1b0c90$84e16acc_at_mdavies>...
>> >> Respects to all Oracle experts.
>> >> I would like to find the most processing efficient solution to the
>> >> following:
>> >>
>> >> Assume a table of records.
>> >> I would like to query this table for the existence of one or more
 records
>> >> meeting a WHERE clause criterion, and set a local PL/SQL variable
>> >> to 1 if ANY records exist, and 0 if NO records exist.
>> >>
>> >> For instance, I could use:
>> >>
>> >> Select count(*) into <local_var> From <Table> Where <criteria>;
>> >>
>> >> However, could this be a very costly query for a very large table?
>> >>
>> >> Is there a better way....anyone?
>> >>
>> >> Thanks in advance.
>> >> Posting and e-mail reply would be nice.
>> >>
>> >> Mick Davies, mdavies_at_elekom.com
>> >>
>
>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
Received on Sat Oct 11 1997 - 00:00:00 CDT