ORACLE sql merge into update where conditional location and performance consumption

Environment:

Operating system version: linux redhat 6.6

Database version: oracle 11.2.0.4

Problem description: today, implementation colleagues reported a very strange problem, that is, the sql merge usage of the oracle database. The update statement should place the partition key of the partition table in the matching keyword.

In On, the sql statement execution plan shows that the partition key can act as a partition filter:

On the contrary, if the partition key in the where condition of the update statement is not placed in the merge matching keyword on, the merge into update statement does not correctly execute the partition table partition filter, the sql statement

And the implementation plan is as follows:

Summary: first of all, let's talk about the personal understanding of this example. Merge first performs the conditional filtering followed by on, and then data matching, and then performs subsequent update update operations on matching, so

Put the partition key in the condition matching after the on, first filter the partition of the distinguishing table to avoid the full partition scan of the partition table, thus greatly reducing the merge into update statement

Consumption of host cpu resources.