You Asked

Is a triggers on partitioned table are the evil?
Having several actually independent tables in the form of partitions with local indexes, it is possible to have the high level of parallelism, for example, in case of an insertion. Having created a trigger on an insertion, we have parallelism lowering because all requests will be processed by the single copy of the trigger, even in a case when the partition's key doesn't participate in a code of the trigger.
In case of triggers is required the decision similar to indexes - global triggers (using a partition's key) and local triggers (not using a partition's key).
I am mistaken? Correct me please.

and Chris said...

I say they have the same amount of evilness as triggers on non-partitioned tables ;)

That's not to say they are evil! While triggers are certainly abused, there are use cases for them. Take a look at this for some examples:

http://harmfultriggers.blogspot.co.uk/

I'm not sure what you mean by "Having several actually independent tables in the form of partitions with local indexes, it is possible to have the high level of parallelism, for example, in case of an insertion".

Parallel DML doesn't work on tables with triggers. As the docs say:

"Parallel DML operations cannot be done on tables with triggers."

http://docs.oracle.com/database/121/VLDBG/GUID-6626C70C-876C-47A4-8C01-9B66574062D8.htm#GUID-6626C70C-876C-47A4-8C01-9B66574062D8

There's no such thing as global vs local triggers. All triggers are "global". Though you can restrict when they fire by using the when clause:

http://docs.oracle.com/database/121/LNPLS/create_trigger.htm#LNPLS2031

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Yury Shumsky, June 01, 2016 - 9:15 am UTC

We have the table without a triggers with several unique indexes and external keys and several generators of data. The increase of inserting generates high indexes contention.
We divide the table into partitions and create local indexes for its. Generators have uniform distribution of a partition's key we have increase in productivity of system. It is "parallelism" about which I wrote.
Next we add the "global" trigger on the table. Is it a new a bottleneck for the entire table? And we can't take advantage of partitioning?

June 01, 2016 - 10:35 am UTC

Multiple sessions can execute a trigger at the same time. So if your inserts are in separate sessions the trigger won't block them.

Unless you do something in the trigger to explicitly serialize your sessions that is (call dbms_lock, take out table locks, etc.).

So probably not, but it depends upon what your trigger does.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs