Table update efficiency, Oracle 11G

Question

I am a relatively new developer in PL-SQL Oracle11g. As part of my studies, I am in a debate with the lecturer regarding the effectiveness of the UPDATE operation on a table.

In the following code, I argue that the part of WHERE EXISTS should be added, because it optimizes the operation and prevents an unnecessary update in case no values were found in the placement, i.e. SET c.text = NULL

UPDATE customer c
   SET c.text =
       (SELECT tmp.offer_desc
          FROM tmp_main tmp
         WHERE c.o_id = tmp.o_id
           AND c.c_id = tmp.c_id)
** WHERE EXISTS (SELECT 1
          FROM tmp_main tmp tmp
         WHERE c.o_id = tmp.o_id
           AND c.c_id = tmp.c_id)**;

It should be noted that the operation affects millions of records, so the runtime and resource usage is critical.

On the other hand, my lecturer claims that it is unnecessary, and the very fact that I perform a SELECT operation twice in this run is pointless and the WHERE EXISTS code segment should be removed.

I am completely confused. I also use Oracle execution plan to verify both ways, and it is clearly seen that the cost is lower when using the aforementioned WHERE EXISTS. But my lack of experience may be misleading me..?

So what should/better/effective?

You are absolutely correct and your lecturer is wrong, for the reasons you gave. It does seem unfortunate that there is duplication of the WHERE clause on the "temp" table, but it is necessary. (In your example the "temp" table name changes, I presume that is a mistake?) — Tony Andrews
– Tony Andrews, Commented Nov 1, 2024 at 10:12
Unless (a) there will be always be a one-to-one match for every row (which is unlikely, unless this is a one-off update of everything), but checking won't cost you anything anyway as you're already hitting the other table; or (b) there is actually a requirement to null the column to remove any existing value for any rows which don't have a match (also unlikely, and your lecturer should have said if that was the case). Incidentally, you might also want to look at a merge to avoid the repetitive code, and possibly the argument... — Alex Poole
– Alex Poole, Commented Nov 1, 2024 at 11:28
@AlexPoole there is no requirement to null the column. Using this temporary table, we update a main table that is significantly larger than it. As for MERGE, I thought about that too, but the lecturer 'doesn't like' using this ability, so he canceled it immediately — SEYATI
– SEYATI, Commented Nov 3, 2024 at 5:48
I would be surprised to see this in real life. To me, it feels like premature optimization at best. Also, agree it should be a MERGE if you are concerned. — Randy
– Randy, Commented Nov 30, 2024 at 17:43

Jan Suchanek · Accepted Answer · 2024-11-01 12:25:55Z

2

Your reasoning about using WHERE EXISTS for this UPDATE operation is correct. but there is still a possible performance improvement

I would use a single MERGE command like:

MERGE INTO customer c
USING tmp_main tmp
ON (c.o_id = tmp.o_id AND c.c_id = tmp.c_id)
WHEN MATCHED THEN
   UPDATE SET c.text = tmp.offer_desc;

Opt for MERGE over UPDATE:

The MERGE statement typically offers superior efficiency when performing conditional updates based on matched rows in another table. Unlike UPDATE, MERGE will only act upon rows where a match exists, effectively replacing the need for WHERE EXISTS and enabling Oracle to execute the operation in a single scan, which is both faster and more resource-efficient.

Preventing Unnecessary NULL Assignments:

Using MERGE naturally prevents setting c.text to NULL in cases where there are no matching rows, which would otherwise occur in an UPDATE operation if you were to use SET c.text = NULL with unmatched records. The MERGE statement restricts updates solely to rows with matching o_id and c_id, ensuring it only performs necessary updates and conserves resources.

edited Nov 1, 2024 at 12:25

answered Nov 1, 2024 at 12:24

Jan Suchanek

3161 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SEYATI Over a year ago

True, that was my first thought. However, my lecturer doesn't like to use this ability because 'we don't use it', so he canceled it immediately

Collectives™ on Stack Overflow

Table update efficiency, Oracle 11G

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related