Topic: SQL*Plus and PL/SQL >> MERGE - any tips ?
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: MERGE - any tips ? |
| Burton |
Posted: Feb 14, 2007 09:22:14 AM |
Total Post: 53
Joined: Dec, 2006
|
Can anyone point the way to a good tutorial on the MERGE command. Im readying for the 007, and the Couchman text is skimpy on this topic.
B. |
|
|
 Oracle Virus |
| Posted: Feb 14, 2007 12:05:40 PM |  |
|
Total Post: 50
Joined: Jan, 2007
|
Hi Burton,
I thought of writing an article and most of the times not getting response to what i posted. So pasting it here.
Oracle9i introduces a new set of server functionality especially beneficial for the ETL (Extraction, Transformation, and Loading) part of any Business Intelligence process flow, addressing all the needs of highly scalable data transformation inside the database.
One of the most exciting new features addressing the needs for ETL is the SQL statement MERGE. The new SQL combines the sequence of conditional INSERT and UPDATE commands in a single atomic statement, depending on the existence of a record. This operation is commonly known as Upsert functionality.
In a data warehouse environment, tables (typically fact tables) need to be refreshed periodically with new data arriving from on-line systems. This new data may contain changes to existing rows in tables of the warehouse and/or new rows that need to be inserted. If a row in the new data corresponds to an item that already exists in the table, an UPDATE is performed; if the row's primary key does not exist in the table, an INSERT is performed. Many times, the source system cannot distinguish between newly inserted or changed information during the extraction; for very complex transformations, it's sometimes nearly impossible to know the effect of changed source data. These scenarios require the determination of insert versus update to be done during data loading.
Prior to Oracle9i, these operations were expressed either as a sequence of DMLs (INSERT/UPDATE) or as PL/SQL loops deciding, for each row, whether to insert or update the data. Both these techniques face performance handicaps: the first requires multiple data scans, and the second operates on a per-record basis. By extending SQL with a new syntax - the MERGE statement - Oracle9i overcomes the deficiencies of the old approaches and makes the implementation of warehousing applications more simple and intuitive.
The following is an example of the MERGE statement. The fact table SALES_FACT in a data warehouse for a retailer needs to be periodically updated with sales data coming from the on-line systems. If the retailer opens a new store, then the data for the store needs to be inserted into the SALES_FACT table.
Oracle9i Implementation:
In Oracle9i, the MERGE statement INSERTS and UPDATES the data with a single SQL statement.
MERGE INTO SALES_FACT D
USING SALES_JUL01 S
ON (D.TIME_ID = S.TIME_ID
AND D.STORE_ID = S.STORE_ID
AND D.REGION_ID = S.REGION_ID)
WHEN MATCHED THEN
UPDATE
SET d_parts = d_parts + s_parts,
d_sales_amt = d_sales_amt + s_sales_amt,
d_tax_amt = d_tax_amt + s_tax_amt,
d_discount = d_discount + s_discount
WHEN NOT MATCHED THEN
INSERT (D.TIME_ID ,D.STORE_ID ,D.REGION_ID,
D.PARTS ,D.SALES_AMT ,D.TAX_AMT ,D.DISCOUNT)
VALUES (
S.TIME_ID ,S.STORE_ID ,S.REGION_ID,
S.PARTS ,S.SALES_AMT ,S.TAX_AMT ,S.DISCOUNT);
Oracle8i Implementation:
In Oracle8i, you could choose to implement it as a sequence of DML statements.
UPDATE
(SELECT
S.TIME_ID ,S.STORE_ID ,S.REGION_ID,
S.PARTS s_parts ,S.SALES_AMT s_sales_amt ,S.TAX_AMT s_tax_amt ,S.DISCOUNT s_discount,
D.PARTS d_parts ,D.SALES_AMT d_sales_amt ,D.TAX_AMT d_tax_amt ,D.DISCOUNT d_discount
FROM SALES_JUL01 S, SALES_FACT D
WHERE D.TIME_ID = S.TIME_ID
AND D.STORE_ID = S.STORE_ID
AND D.REGION_ID = S.REGION_ID) JV
SET d_parts = d_parts + s_parts,
d_sales_amt = d_sales_amt + s_sales_amt,
d_tax_amt = d_tax_amt + s_tax_amt,
d_discount = d_discount + s_discount
;
INSERT INTO SALES_FACT (
TIME_ID,STORE_ID ,REGION_ID,
PARTS ,SALES_AMT ,TAX_AMT ,DISCOUNT)
SELECT
S.TIME_ID ,S.STORE_ID ,S.REGION_ID,
S.PARTS ,S.SALES_AMT ,S.TAX_AMT ,S.DISCOUNT
FROM SALES_JUL01 S
WHERE (S.TIME_ID, S.STORE_ID, S.REGION_ID) NOT IN (
SELECT D.TIME_ID, D.STORE_ID, D.REGION_ID
FROM SALES_FACT D
)
;
Alternatively, you could create a procedural implementation. It would need to check every load record against the target to find if the record already exists; only then could it decide whether to insert or update the data.
Both of these approaches suffer from deficiencies in performance and usability. The new MERGE command overcomes these deficiencies, processing the conditional INSERT-or-UPDATE within a single statement. The data is scanned only once, and the appropriate DML command is issued, either serially or in parallel.
The new MERGE command brings major performance benefits by providing an optimized internal feature for the common Upsert task within ETL processing. Furthermore, it simplifies the development of transformation processing inside the database - using Oracle9i as the transformation engine.
Hope this helps you
Regards,
Aneel Kanuri.
|
|
|
|
|
| Burton |
| Posted: Feb 14, 2007 01:12:15 PM | |
|
Total Post: 53
Joined: Dec, 2006
|
woah man, that will take me some time to digest. Will check back later and study it proper.
Nice action.
B
|
|
|
|
|
| Oracle Virus |
| Posted: Feb 14, 2007 02:31:50 PM | |
|
Total Post: 50
Joined: Jan, 2007
|
Its ok Burton, Hope this will give you the concept of the Merge statement. It came huge in size xplaining the whole. :-)
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Vinoth Kumar | 357 |
| Gopu Gopi | 340 |
| Gitesh Trivedi | 322 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
