Re: Possible tuse of EXISTS in PL/SQL?

The EXISTS you are thinking of would be used in a WHERE clause. In your case, this would apply if your insert used a SELECT (or could use SELECT <stuff> FROM DUAL). Add:
WHERE EXISTS (select 1 from rtest where rcol = 'XX')

(You can also use NOT EXISTS, and I typically change the subquery to select 0. The value is just a placeholder.)

"Crocodile" <tav_at_prbank.msk.su> wrote in message news:39EC5979.209A7574_at_prbank.msk.su...
>
>
> "Randi Wølner" wrote:
>
> > I think that somewhere I have seen EXISTS been used in a smart way to
 find
> > out whether some value exists in a table. To better explain what I mean,
 I
> > will show how it is I want to use it:
> >
> > I have a very large table called rtest. If rtest have one or more rows
 with
> > the value 'XX' in colum rcol, then I do not want to insert a new row,
> > otherwise I will. My PL/SQL code now looks something like:
> >
> > select count(*)
> > into rvariable
> > from rtest
> > where rcol='XX';
> >
> > if rvariable=0 then
> > < code to do an insert >
> > end if;
> >
> > This select will make Oracle perform a full table scan (unless I have an
> > index on it, which in this case I do not), but if it is possible to use
> > EXISTS in some way, I think Oracle would stop reading the table when the
> > first record matching the query was found(??).
> >
> > Very thankful for help on this.
> >
> > Kind regards,
> > Randi Wølner
>
> select count(*) into rvariable
> from rtest
> where rcol='XX' and rownum = 1;
>
Received on Wed Oct 18 2000 - 10:41:21 CDT