You would specify the where_clause when you want Oracle to execute the insert operation only if the specified condition is true.
The condition can refer only to the MERGE data source.
ON Clause - The ON clause specifies the condition that the MERGE operation uses to determine whether it updates or inserts.
When the search condition evaluates to true, Oracle updates the row in the target table with corresponding data from the MERGE source.
If both are specified, then the order of the clauses is not important.
merge_insert_clause To specify the values used for insert operations the merge_insert_clause is used.
Oracle performs the specified update if the condition of the ON clause is true.
If the MERGE statement deletes a row, any delete triggers defined on the target table will be activated for each row deletion. The WHERE clause can be specified by itself or in concert with the merge_insert_clause.
An example of a constant filter predicate would be: A predicate such as the one shown is recognized by Oracle as a special case, and Oracle makes an unconditional insert of all source rows into the table.
The benefit of this approach over just omitting the merge_update_clause, Is that Oracle still must perform a join if the merge_update_clause is left out, while with a constant filter predicate, no join is performed.
You should use the MERGE statement to select rows from one or more sources for insert or update of one or more tables.
The decision to perform an insert or update is based on conditions specified by you.