CODEWITHSUNDEEP
oracle-databasetriggersdatabase-performancedatabase-trigger
shwartz
shwartz

Reputation: 671

Do DB trigger conditions improve performance?

I know that a DB trigger condition can only be a SQL expression and that it cannot include a subquery. However, inside the trigger code it is possible to use PL/SQL and subqueries. I was thinking that maybe this is so that using trigger conditions, where applicable, we can gain better performance (for example, maybe the context switch between the SQL engine and the PL/SQL is saved).

For example, using a trigger condition we'd have:

CREATE TRIGGER hr.salary_check
  BEFORE INSERT OR UPDATE OF salary, job_id ON hr.employees
  FOR EACH ROW
    WHEN (new.job_id <> 'AD_VP')
  BEGIN
    --pl/sql_block
  END;

and not using a trigger condition we'd have:

CREATE TRIGGER hr.salary_check
  BEFORE INSERT OR UPDATE OF salary, job_id ON hr.employees
  FOR EACH ROW
  BEGIN
    IF (new.job_id <> 'AD_VP') THEN
      --pl/sql_block
    END IF;
  END;

Is there a difference in performance between using a DB trigger condition in order to avoid the execution of code and using that same condition inside an "IF" statement in the trigger code to avoid the execution of that code? If so, I'd appreciate your comment on the performance impact.

Upvotes: 1

Views: 708

Answers (1)

krokodilko
krokodilko

Reputation: 36107

Just do a simple test and measure times.

CREATE TABLE employees_0 AS
SELECT
    x * employee_id as employee_id,
    first_name,
    last_name,
    email,
    phone_number,
    hire_date,
    'AD_VP' As job_id,
    salary,
    commission_pct,
    manager_id,
    department_id
FROM
    employees
CROSS JOIN (
    SELECT level as x FROM dual CONNECT BY LEVEL <= 10000
) x
;
CREATE TABLE employees_1 AS SELECT * FROM employees_0;
CREATE TABLE employees_2 AS SELECT * FROM employees_0;

CREATE OR REPLACE TRIGGER hr.salary_check_1
  BEFORE INSERT OR UPDATE OF salary, job_id ON hr.employees_1
  FOR EACH ROW
    WHEN (new.job_id <> 'AD_VP')
  BEGIN
    :new.salary := :new.salary + 1;
  END;
/

CREATE TRIGGER hr.salary_check_2
  BEFORE INSERT OR UPDATE OF salary, job_id ON hr.employees_2
  FOR EACH ROW
  BEGIN
    IF (:new.job_id <> 'AD_VP') THEN
      :new.salary := :new.salary + 1;
    END IF;
  END;
/

And now:

set timing on;

update employees_0 set salary = salary + 2;

update employees_1 set salary = salary + 2;

update employees_2 set salary = salary + 2;

And results are:

1 070 000 rows updated.

Elapsed: 00:00:37.273

1 070 000 rows updated.

Elapsed: 00:00:37.232

1 070 000 rows updated.

Elapsed: 00:00:38.874

The test shows that there is negligible difference between a table without a trigger at all and tables with both version of the trigger while doing the UPDATE on tables in which the column job_id is different than AD_VP for all rows.

You can do another measures on these test tables yourself - for example INSERT 1 mln. rows, change a value of job_id to AD_VP and do the update etc.

My system is:

select * from v$version;

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE   12.1.0.2.0  Production"
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Upvotes: 2

Related Questions