MERGE your application to improve performanceApril 23, 2012 at 2:09 pm | Posted in Oracle, Technical Tips | Leave a comment
Tags: Oracle, tech tips
Hello, Oraclites. Have you taken a look at the new (well, relatively new) MERGE command that is now part of ANSI SQL? In many cases, incorporating the MERGE command into your application can replace writing a 10 to 20 line program in PL/SQL. And that will lead to significantly improved performance. This is definitely one of those cases where “less is more.”
How does MERGE work?
The MERGE command functions as either an INSERT command or an UPDATE command all in one, depending upon a condition that you specify. And just like INSERT and UPDATE, the new MERGE command is a DML command. If you totally hose your data trying out the new MERGE command, you can do a ROLLBACK. No harm, no foul.
Here’s an example that everyone can probably relate to. WIDGETS Incorporated has 10,000 active and inactive employees, and thus 10,000 rows in the emp table. The column status contains an I for inactive employees, and an A for active employees.
Here is the description of the emp table:
id NUMBER(5) PRIMARY KEY name VARCHAR2(20) NOT NULL dept VARCHAR2(10) NOT NULL salary NUMBER(10,2) hire_date DATE status CHAR(1)
There’s also a new_emp table created by HR and used nightly to update the emp table. HR assigns a new unique ID to new employees who have never worked for WIDGET before, but those who have are reassigned their previous ID. The number of rows in new_emp varies night by night, but averages around 20 rows. Usually one or two out of the 20 represents a rehire. The structure of the new_emp table is exactly the same as the emp table; only the data is different.
What HR wants to do, and the MERGE command allows them to accomplish, is to add the personnel data for a new hire who has never before worked for the company, and assign that employee a status of A (for active). In the same pass through the data, the command should update the emp table by locating inactive employees who have been rehired. When those records are found, the status should be changed from Inactive to Active, and the starting salary when this person is rehired should be updated in his or her row in the emp table. We’ll take care of updating the other columns later.
MERGE INTO emp E USING (SELECT id, name, dept, salary FROM new_emp) N ON (e.id = n.id) WHEN MATCHED THEN UPDATE SET E.dept = N.dept, E.salary = N.salary, E.hire_date = sysdate, E.status = ‘A’ WHEN NOT MATCHED THEN INSERT VALUES (N.id, N.name, N.dept, N.salary, SYSDATE, ‘A’);
This should solve their problem! The merge (either Inserts or Updates) are going to happen to the emp table. We are going to use the data returned by the SELECT to compare each returned row with each row in emp to determine if the values of id are equal. If they are, we’ll do the update in the “WHEN MATCHED” clause. If they aren’t equal, we’ll do the INSERT in the “WHEN NOT MATCHED” clause.
That’s pretty neat, and what’s really great is we don’t need to write a program to accomplish it. Furthermore, we can deal with all the rows in a single pass of the data.
If you like this, take a look at multitable INSERTS, another great feature for developers and DBAs.
–Bob the OrclTestGuy