Oracle trigger is fired more times then expected

Question

I'm working on the job which updates using the next SQL

UPDATE (SELECT a.column_1,
           TO_NUMBER(SUBSTR(b.column_3, 1, 5)) new_column_1,
    FROM table_2 b
             INNER JOIN
         table_1 a
             on a.column_0 = b.column_0)
SET column_1 = new_column_1;

The actual code updates more columns but to be discreet I shortened it.

There is trigger table_1

create or replace trigger trigger_on_table_1
before insert or update
on table_1
for each row
BEGIN
 :NEW.column_4 := S_COLUMN_4.NEXTVAL;
END;

I added insert into custom table to check how many times trigger_on_table_1 is executed and it's executed -> (count row from table_1 + count rows from table_2). To my mind, since it's on table_1 it should be fired only (count table_1) times

Which doesn't seem right and causes some performance issues. Any ideas about what may be wrong?

In table_2 column_0 is unique since it's PK.
In table_1 it's not unique but it's only values that are present in table_2.

The table that is being updated has 10M records hence subquery in SET seems to degraded performance even more.

Answer 1

Your inline view:

SELECT a.column_1,
           TO_NUMBER(SUBSTR(b.column_3, 1, 5)) new_column_1,
    FROM table_2 b
             INNER JOIN
         table_1 a
             on a.column_0 = b.column_0

Will result in as many records from table_1 as it finds column_0 matches on in table_2. If that's a one-to-many join, you will get multiple rows returned for every original table_1 row. If you then feed that into an update, your "for each row" trigger will fire that many times.

Don't update this way through inline views. Instead, you can use a normal UPDATE with a subquery, or perhaps better (performance-wise), a MERGE:

MERGE INTO table_1
USING table_2
ON (table_1.column_0 = table_2.column_0)
WHEN MATCHED THEN UPDATE SET table_1.column_1 = TO_NUMBER(SUBSTR(table_2.column_3, 1, 5))