Re: SQL Tunning

<bblfree_at_my-deja.com> wrote in message news:94q5sm$6jj$1_at_nnrp1.deja.com...
> Hi,
> sorry for my bad english....
> I want to know if the following procedure has low performance when the
> table is large.
> Procedure xxx
>
> select count(*)
> into cant
> from table1
> where table1.col1 = value1
>
> if cant=0
> insert into table1....
>
> end
> The application that we're testing has procedures of that kind for all
> operations. They don't manage exceptions because they verify the
> condition before; but I think it's worst...
> Is that OK?
>
> Thanks.
>
>
>
>
>
>
> Sent via Deja.com
> http://www.deja.com/

1 if col1 isn't indexed the query will result in a full table scan 2 if the purpose of this select is to check for existence, and col1 is a foreign key, and possibly has 1000 of values, *all* those records are going to be read.
Obviously this is a performance killer
I would perform a little trick like

cursor check_existence(p_val in <datatype>) is select 'x'
from dual
where exists
(select 'x'
 from tab
 where col1 = p_val);
dummy char(1);
begin
open check_existence(val1);
fetch check_existence into dummy
if check_existence%notfound then
insert

However, if you only do this to prevent dup_val_on_index errors and you have proper keys, you might ask why you just don't trap the exception as Oracle has more efficient internal mechanisms to check for the existence of a key.

Hth,

Sybrand Bakker, Oracle DBA Received on Thu Jan 25 2001 - 16:02:05 CST