UPDATE is very slow in Oracle

[JetDeveloper]

Hi all:


UPDATE PERSON SET HISPANIC_FLAG =
(
SELECT CASE WHEN SPECIAL_CODE = 'H' THEN 'Y' ELSE 'N' END
FROM PERSON PER
LEFT JOIN LOOKUP_VALUES VAL ON (PER.ETHNIC_CODE = VAL.VALUE_CODE AND
VAL.LOOKUP_ID = 'A3247EE2772CB541AF1EB8B97A9E8D91')
WHERE PERSON.PERSON_ID = PER.PERSON_ID
)


The above query is VERY slow in Oracle (takes about 5 minutes). The PERSON table has around 56000 records. For the same database in SQL Server, it takes less than a second.

It seems to be executing the inner select 56000 times, instead of storing a copy of the LEFT JOIN in memory.

Any ideas on how to speed this up?

Thanks in advance

[olivthill2]

Here is another version of your query that should give you exactly the same results as your initial query (if I am not wrong).
The great benefit of this new version is that the PERSON table is used only once, which should speed up the process:

UPDATE PERSON
SET HISPANIC_FLAG = DECODE(SPECIAL_CODE, 'H', 'Y', 'N')
WHERE
ETHNIC_CODE
IN (SELECT VALUE_CODE FROM LOOKUP_VALUES
WHERE LOOKUP_ID = 'A3247EE2772CB541AF1EB8B97A9E8D91');

[JetDeveloper]

Thanks olivthill2, but this will not work because SPECIAL_CODE is a column of LOOKUP_VALUES and not PERSON.

[davide++]

Hi all.

I suggest to check the explain plan.

Anyway, try to put an index on ETHNIC_CODE.
Another change you can do is using DECODE instead of CASE ... WHEN (probably it's faster).