Re: [sqr-users] #sql-status and unique constraint

["George Jansen" <GJANSEN@aflcio.org>]

You wrote

>>> maillists@starbuckk.net 01/15/04 10:48PM >>>
To reduce database hits and network traffic.  99% of the time, there
will NOT be a record out there.  But out of maybe 200,000 records, I
might have 5 or six repeats.  Using a query ahead of time, or a "where
not exists" subquiery in this particular case simply generates
unnecessary database traffic.
>>>

Well, you know the business requirements. 

But do you know what this overhead amounts to? In my quick and dirty
testing, encapsulating with PL/SQL ups the runtime about 40% if I just
trap the DUP_VAL_ON_INDEX error, and about 80% if I apply the data in
the duplicate record.

Pretty grim, until you consider that the baseline is about 1000 records
per second to SCOTT.EMP on not a very fast machine. So if I had 200,000
records and my alternatives were 

 a. running in  3 min 20 sec and praying that the #sql-status would
come back -9
 b. running in 6 min and knowing that I had trapped my primary key
conflicts

maybe I'd choose option b) and make a coffe run.


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users