Oracle Bulk Insert tips. Question:  I learned that the Oracle bulk. PL/SQL. Answer:  Oracle introduced the .

Question: I learned that the Oracle bulk insert can reduce context switches, but want to understand how bulk inserts work inside PL/SQL. Body Board With Viewing Window Birdhouse. Free Oracle Magazine Subscriptions and Oracle White Papers: Oracle Merge Statements: Version 11.1: Note: Primarily of value when moving large amounts of data in data.

MERGE is a DML command that enables us to optionally update or insert data into a target table, depending on whether matching records already exist. In versions prior to 9i, we would have to code this scenario either in separate bulk SQL statements or in PL/SQL. We will compare MERGE to these methods later in this article. Before we begin, we will create two scratch tables to work with; a source table and a target table. We will start by creating the source table as a selection of ALL. Class E License Ny Fee Schedules. Like the source table, it will also have a primary key on OBJECT. In the following example, we will merge the source table into the target table.

We will capture a count of the target table rows before and after the merge. Note the following clauses: MERGE (line 1): as stated previously, this is now the 4th DML statement in Oracle. Any hints we might wish to add directly follow this keyword (i. MERGE /*+ HINT */); INTO (line 2): this is how we specify the target for the MERGE. The target must be either a table or an updateable view (an in- line view cannot be used here); USING (line 3): the USING clause represents the source dataset for the MERGE. This can be a single table (as in our example) or an in- line view; ON () (line 4): the ON clause is where we supply the join between the source dataset and target table. Note that the join conditions must be in parentheses; WHEN MATCHED (line 5): this clause is where we instruct Oracle on what to do when we already have a matching record in the target table (i.

• In Oracle, is it possible to INSERT or UPDATE a record (a row) through a view?
• Find the definition for different types of Oracle joins, and read a discussion on LEFT JOIN vs. LEFT OUTER JOIN in this expert Q/A.

Question: What are the steps for tuning an insert SQL? It's running far too long and I need to understand how to optimize the insert for performance.

We obviously want an UPDATE in this case. One of the restrictions of this clause is that we cannot update any of the columns used in the ON clause (though of course we don't need to as they already match). Any attempt to include a join column will raise an unintuitive invalid identifier exception; and. WHEN NOT MATCHED (line 1.

INSERT records for which there is no current match. Note that sqlplus reports the number of rows merged. This includes both the updates and inserts. Oracle treats MERGE as a MERGE and not an UPDATE+INSERT statement. The same is true of SQL%ROWCOUNT in PL/SQL. As a rough sanity check, we can report the record count in the target table following the MERGE.

We can see that this is the same as the MERGE count. If we explain a MERGE statement, we can see the mechanism Oracle uses to determine whether source and target rows match. The following output is an Autotrace explain plan for our original MERGE statement from above. This creates a non- mergeable view (this is an unfortunate coincidence in terminology) that is applied back to the target table. Without the outer join, Oracle would need to implement a . In addition, we can achieve some minor gains by ordering the WHEN clauses in a MERGE according to which event is the most likely (i.

In the following example, we will use a variation of Tom Kyte's RUNSTATS utility to compare our original MERGE with a solution that runs a bulk update followed by a bulk insert. We will pause the statistics between the two runs to reset the data. We will begin with the MERGE.

The update is written as an updateable in- line view which is often the fastest technique for bulk updating one table from another. The sqlplus feedback gives us the breakdown of the previous merge rowcount.

We will only report major differences to keep the output to a minimum, as follows. It generated more redo and used more latches. We can repeat the test against a typical PL/SQL- coded merge (common in older applications). We will replace the two- part SQL solution with a PL/SQL loop that will attempt an update first and insert only if the update affects no rows. The alternative to this would be to insert first and only update when a DUP.

We could speed up the latter by using bulk processing, but we wouldn't be able to achieve a reduction of two- thirds required to match the MERGE. MERGE is a deterministic, key- preserved operation. This means that for each source row, Oracle needs to be able to identify a single target record for update. The simplest method of ensuring that the MERGE is key- preserved is to join source and target according to the primary key of the target. We can demonstrate what happens if we cannot ensure key- preservation by modifying our MERGE to join on a column other than that of the primary key. It has been raised because Oracle found more than one target row that matched a single source row. MERGE will allow multiple updates of a single target row, however, as long as the join is key- preserved.

In the following example, we'll remove the primary key from the source table and duplicate some source data. We will revert to the key- preserved join and this time our MERGE should be successful.

The following example shows how we might use MERGE with FORALL. To use MERGE with either row- by- row PL/SQL or FORALL (as we saw above), each source record must be selected from DUAL to generate a rowsource.

We cannot use the variables in direct assignments within the update or insert sections of the MERGE itself. This will generally perform better than row- by- row .

Ironically, we have stated more than once that MERGE is a DML statement in its own right, yet Oracle will fire UPDATE and INSERT triggers if these events occur within the merge. We can demonstrate this quite easily below. We will create separate insert and update triggers and simply output a message from each. For an example of how to break down a MERGE rowcount into separate update and insert counts, read this oracle- developer. The oracle- developer. Tom Kyte's RUNSTATS utility can be downloaded here. The source code for the examples in this article can be downloaded from here.

Adrian Billington, September 2.