Re: Possible tuse of EXISTS in PL/SQL?

"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 Tue Oct 17 2000 - 08:51:54 CDT