Problem with query and NVL()
504369Apr 5 2006 — edited Apr 5 2006Hi all
Basically, we're dealing with an awful lot of records here (over 3 million). Anyway, in my example, the possibility exists where b.mktg_dist_pin is null. So I modified an existing update statement by adding the nvl function to the statement. I had to cancel my script after running over 14 hours. Here it is:
UPDATE mktg_people A
SET A.PIN = (SELECT MIN(b.MCH_PIN)
FROM MATCHED_SCHOOLS b
WHERE b.MCH_PIN IS NOT NULL
AND nvl(A.DIST_ID,'x') = nvl(b.MKTG_DIST_PIN,'x')
AND b.MKTG_SCHOOL_PIN = A.SCH_ID);
Then I removed the nvl functions on both sides of the equation and the update statement ran in about 15 or 20 minutes.
I'm baffled. Can someone explain the difference? Why would adding nvl() make such a difference in performance? FYI... both columns A.DIST_ID and b.MKTG_DIST_PIN are defined as varchar2(7).
Thanks,
Gerald