Re: I/O bottleneck, bufwaits too high
Jeff Glenn wrote:
> Performance severely degrades when running an update on a 400,000+ row
> table. Pretty straitforward stuff--here is the SQL:
>
> UPDATE evs_tape
> SET (prm_address1) =
> ((SELECT address1
> FROM t_prm
> WHERE t_prm.emp_id=evs_tape.emp_id))
> WHERE emp_id IN
> (SELECT emp_id FROM t_prm)
It could be the IN clause - it works fine when the subquery returns a handful
of rows, but with 400K odd rows? Try this functionally equivalent SQL that uses
the EXISTS clause
UPDATE evs_tape
SET (prm_address1) =
((SELECT address1
FROM t_prm
WHERE t_prm.emp_id=evs_tape.emp_id))
WHERE EXISTS (
SELECT 1 FROM t_prm
WHERE t_prm.emp_id=evs_tape.emp_id);
Rudy