Bill’s Dev Blog

MySQL has some nifty extensions to ANSI SQL. Two of which are “INSERT … ON DUPLICATE KEY UPDATE” and “REPLACE” (As of MySQL 4.1). These statements are used when inserting data into a table where the unique key of the row you are inserting may already exist, and if it does, you want this row to be updated with new data. Instead of doing a select/insert/update routine, this gives a significant performance increase for frequently updated schemas; and what is even more important, the application code becomes simpler, less error prone, and easy to read.

“INSERT … ON DUPLICATE KEY UPDATE” will actually perform either an UPDATE or an INSERT, whichever one is necessary. The “REPLACE” statement will actually delete the row if it exists and insert the new row.

Example of “INSERT … ON DUPLICATE KEY UPDATE” (assuming column ‘a’ is the unique key):

1
2
3

INSERT INTO TABLE (a,b,c)
VALUES (1,2,3)
ON DUPLICATE KEY UPDATE b=2, c=3;

Example of “REPLACE”:

1
2

REPLACE INTO table_a (a,b,c)
VALUES(1,2,3);

Oracle 9i+ also has an extension that can somewhat similarly handle this conditional INSERT-or-UPDATE task: the MERGE statement, though not as intuitive and the code is not as easy to read. Here is an example of how one might implement MERGE for this situation:

1
2
3
4
5
6
7
8
9
10
11

MERGE INTO SCHEMA.table_a TA
USING (
 SELECT '1' a, '2' b, '3' c
 FROM DUAL
 ) E
ON (TA.a = E.a)
WHEN MATCHED THEN
 UPDATE SET TA.b = E.b, TA.c = E.c
WHEN NOT MATCHED THEN
 INSERT (TA.a, TA.b, TA.c)
 VALUES (E.a, E.b, E.c);

Comparing Oracle’s 11 line method to MySQL’s 3 line method, I must hand it to MySQL for its simpler and superior method.

References:
forums.devshed.com, dev.mysql.com, dev.mysql.com, www.mysqlperformanceblog.com, www.oracle.com

I use MySQL a lot and when designing queries, I use the EXPLAIN keyword to help optimize performance. This can be done at any MySQL command prompt. However, it is not so easy in Oracle I am finding out. One drawback is that you need to use the SQL Plus client, as other Oracle command prompts do not work.

Tested against Oracle 10.2g.

Here is a chart of how to analyze Oracle queries, starting with how to set the proper permissions, and detailing the options when executing the trace. I am including this table here so I do not have to count on the site I got it from to stay active.

Info “borrowed” from: http://www.psoug.org/reference/autotrace.html

For MS SQL Server equivalent information, do a search on “Query Execution Plan”.