The More You Know

Oracle Upsert: Insert or Update

When working with relational data in a stateless environment (e.g. web applications) it is sometimes not known if something is being created (insert) or being updated. This is generally seen when an object has an 0..N number of sub elements.

When the data from the user is to be processed, it would be best to handle both inserting and updating with a single statement. Developers new to databases generally handle this transaction with multiple statements using conditional “IF” statements to determine the number of rows a result set has. Based on the number of rows (0 or 1) an insert or update is performed. This approach suffers from lag between the logical language (Java, PHP, PL/SQL, etc) and the SQL engine. Even Oracle suffers overhead from this approach due to context switch overhead that is incurred during PL/SQL to SQL (or vice versa) switches.

This performance loss may be trivial or non-trivial based on use. If the process in question will accept thousands of items it can perform poorly. A few hundred rows may not. However, it is possible to write a single version that will work better overall and scale well.

The first step is changing from a multiple query approach to a single query approach. This can be done with UPSERTS or more commonly known as a MERGE statement. In Oracle a MERGE statement is defined by the following syntax:

1
2
3
4
5
6
7

MERGE INTO destination_table dest
    USING (SELECT col1, col2, col3 FROM source_table) source
    ON (dest.col1 = source.col1)WHEN MATCHED THEN
        UPDATE SET dest.col2 = source.col2,dest.col3 = source.col3
    WHEN NOT MATCHED THEN
        INSERT (dest.col1, dest.col2, dest.col3)
      VALUES (source.col1, source.col2, source.col3)

A more complete Oracle MERGE syntax definition can be found here (Oracle 11g Database)

For a more practical example, here is a PL/SQL function that updates a data column on a table with a single primary key named “ID”.

1
2
3
4
5
6
7
8
9

PROCEDURE UPSERT_TEST_BY_MERGE(P_ID NUMBER, P_DATA VARCHAR2) IS
BEGIN
    MERGE INTO MY_UPSERT_TEST_TABLE DEST
    USING( SELECT p_id AS ID, p_data AS DATA FROM DUAL ) src
    ON (DEST.ID = P_ID)WHEN MATCHED THEN
        UPDATE SET DEST.DATA = P_DATA
    WHEN NOT MATCHED THEN
        INSERT(DEST.ID,DEST.DATA) VALUES(P_ID,P_DATA);
END;

The select statement is required in the USING clause of the MERGE statement. In this example a fast dual look up is used to fulfill the USING clause. It is notable that neither the INSERT or UPDATE clauses need to refer to the USING clause data. It is possible to reffer to simple PL/SQL variables that are also valid SQL types (VARCHAR2, NUMBER, etc but NOT TABLE/complex types without casting/treating).

The above example is a good single entry UPSERT/MERGE. However it will still suffer from heavy serial use. Again using this function for thousands of rows from within a “WHILE” loop in PL/SQL will result in performance issues. To solve this issue in PL/SQL collections (read as: arrays) are used in conjunction with a “FORALL” PL/SQL statement.

The following example uses PL/SQL collections with a PL/SQL “FORALL” statement in order to MERGE hundreds of thousands of rows into a table in sub-second speed. It is VERY important to remember that MERGE is a SQL statement and thusly can only work with SQL (not PL/SQL) types. Thusly, a collection is defined as a table of PL/SQL types that are cast or “treated” as schema level types. This requires a PL/SQL type being defined in a package and also as a schema level type. Both definitions need to have matching data type properties and must use SQL safe types only.

The last example uses a PL/SQL table defined as MY_USERT_TYPE. This type is defined as a “TABLE OF MY_UPSERT_ROW”. MY_UPSERT_ROW is defined exactly the same as the schema level object MY_UPSERT_OBJ.

1
2
3
4
5
6
7
8
9
10

PROCEDURE UPSERT_TEST_BY_MERGE_COL( p_records MY_UPSERT_TYP) IS
BEGIN    FORALL i IN 1..p_records.COUNT
        MERGE INTO MY_UPSERT_TEST_TABLE DEST
        USING( SELECT TREAT(p_records(i) AS My_Upsert_Obj) AS obj FROM DUAL ) src
            ON (DEST.ID =  src.obj.id)
        WHEN MATCHED THEN
            UPDATE SET DEST.DATA = src.obj.data
        WHEN NOT MATCHED THEN
             INSERT (id, data) VALUES( src.obj.id, src.obj.data );
END;