ORACLE DATABASE ADMINISTRATOR
Merging datasets with NULL values
Carmen Beitzer
03.15.2004
Rating: -4.42- (out of 5) Hall of fame tip of the month winner
|
The MERGE functionality introduced in Oracle9i offers a simplified, yet powerful enhancement to the commonly known UPSERT approach of earlier editions. Just as its predecessor, MERGE is a valuable functionality when integrating same datasets over time, (i.e. when extracting incremental data from a production environment data source and merging it into a holding/collection area as is often necessary for data migration projects). It allows already existing records to be updated and new records to be inserted into the holding/collection area. However, its weakness of handling NULL values has lead to inserts instead of updates, leaving us with redundant records on the one hand and outdated records on the other. Consequently, our data became unreliable. Several of our scripts and applications started throwing errors or returned erroneous results. This can be avoided by utilizing the tip I submitted.
A MERGE (which is to be done on the PK fields of the tables involved) might appear to work properly, however, carefully check your target dataset by applying unique indicies. Reason be...
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
ing, the moment you have NULL values in any of your PK fields (which can happen for instance when migrating less then perfect data) the comparison of these two nulls will evaluate to unknown. This will cause an insert rather than an update, leading to undesired record duplicates and failed updates. This has been tested on Oracle9i Enterprise Edition Release 9.2.0.3.0 and Oracle9i Enterprise Edition Release 9.2.0.4.0.
For example: a simple merge approach would be:
However, the resuts are as follows:
In order to avoid this dilemma, you should use the function SYS_OP_MAP_NONNULL function for NULL comparisons. (This is currently NOT
documented!) The enhanced MERGE statement looks as follows:
... and voila, yields the desired results:
Reader Feedback
Daniel C. writes: One correction to the article: Note the example uses a unique key constraint, which does allow NULLs. Other than that, it's a good tip, especially since it mentions an undocumented feature (SYS_OP_MAP_NONNULL), which by the way is not mentioned anywhere in the 9iR2 or 10g docs.
|
|
|
|
|
|
