Re: Possible tuse of EXISTS in PL/SQL?

Another way to emulate the 'EXIST' functionality is to limit your query results with the built in 'ROWNUM'. Include in the where clause: "AND ROWNUM = 1" This will stop the query once the first row is retrieved, thus eliminating the full table scan.

Cheers.
- Brendan

In article <9BXG5.1014$G3.180981760_at_news.telia.no>,   "Randi Wølner" <rw_at_computas.no> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 17 2000 - 09:14:16 CDT