Best Practices for Multi-Row Inserts in Oracle Database with Performance Optimization

Needs and Challenges of Multi-Row Inserts

Inserting multiple rows of data is a common requirement in database operations, especially in scenarios like data migration and batch processing. Oracle Database offers several methods to achieve this, but they differ significantly in syntax, performance, and compatibility. This article explores best practices with a focus on Oracle 9i and later versions.

Core Method: Insert Using SELECT and UNION ALL

An efficient approach for multi-row inserts in Oracle involves using a SELECT statement combined with UNION ALL clauses. This method generates multiple rows of data via the virtual DUAL table and inserts them into the target table. The basic syntax is as follows:

INSERT INTO table_name (column1, column2, column3)
SELECT value1_1, value1_2, value1_3 FROM DUAL
UNION ALL
SELECT value2_1, value2_2, value2_3 FROM DUAL
UNION ALL
SELECT value3_1, value3_2, value3_3 FROM DUAL;

For example, adapting the Q&A data for the TMP_DIM_EXCH_RT table, it can be rewritten as:

INSERT INTO TMP_DIM_EXCH_RT 
(EXCH_WH_KEY, EXCH_NAT_KEY, EXCH_DATE, EXCH_RATE, FROM_CURCY_CD, TO_CURCY_CD, EXCH_EFF_DATE, EXCH_EFF_END_DATE, EXCH_LAST_UPDATED_DATE)
SELECT 1, 1, '28-AUG-2008', 109.49, 'USD', 'JPY', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008' FROM DUAL
UNION ALL
SELECT 2, 1, '28-AUG-2008', 0.54, 'USD', 'GBP', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008' FROM DUAL
UNION ALL
SELECT 3, 1, '28-AUG-2008', 1.05, 'USD', 'CAD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008' FROM DUAL
UNION ALL
SELECT 4, 1, '28-AUG-2008', 0.68, 'USD', 'EUR', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008' FROM DUAL
UNION ALL
SELECT 5, 1, '28-AUG-2008', 1.16, 'USD', 'AUD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008' FROM DUAL
UNION ALL
SELECT 6, 1, '28-AUG-2008', 7.81, 'USD', 'HKD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008' FROM DUAL;

The key to this method is the use of the FROM DUAL statement, where DUAL is a single-row, single-column virtual table in Oracle used for generating temporary data. Compared to single-row inserts, it reduces the number of database connections and parsing operations, thereby enhancing performance.

Comparison with Other Multi-Row Insert Methods

In addition to the SELECT and UNION ALL approach, Oracle supports the INSERT ALL syntax. For instance:

INSERT ALL
INTO TMP_DIM_EXCH_RT (EXCH_WH_KEY, EXCH_NAT_KEY, EXCH_DATE, EXCH_RATE, FROM_CURCY_CD, TO_CURCY_CD, EXCH_EFF_DATE, EXCH_EFF_END_DATE, EXCH_LAST_UPDATED_DATE) VALUES (1, 1, '28-AUG-2008', 109.49, 'USD', 'JPY', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008')
INTO TMP_DIM_EXCH_RT (EXCH_WH_KEY, EXCH_NAT_KEY, EXCH_DATE, EXCH_RATE, FROM_CURCY_CD, TO_CURCY_CD, EXCH_EFF_DATE, EXCH_EFF_END_DATE, EXCH_LAST_UPDATED_DATE) VALUES (2, 1, '28-AUG-2008', 0.54, 'USD', 'GBP', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008')
SELECT 1 FROM DUAL;

In Oracle 23c, a simplified multi-row insert syntax similar to MySQL has been introduced:

INSERT INTO TMP_DIM_EXCH_RT (EXCH_WH_KEY, EXCH_NAT_KEY, EXCH_DATE, EXCH_RATE, FROM_CURCY_CD, TO_CURCY_CD, EXCH_EFF_DATE, EXCH_EFF_END_DATE, EXCH_LAST_UPDATED_DATE)
VALUES
(1, 1, '28-AUG-2008', 109.49, 'USD', 'JPY', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'),
(2, 1, '28-AUG-2008', 0.54, 'USD', 'GBP', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008');

However, for Oracle 9i, INSERT ALL and the SELECT-based methods are more reliable. Performance tests indicate that the SELECT and UNION ALL approach is often faster for large datasets, as it minimizes context switching and parsing overhead.

Performance Optimization and Best Practices

The performance of multi-row inserts is influenced by several factors. First, avoid inserting more than 1000 rows at once to prevent exponential increases in parsing time. Second, wrap insert operations in transactions to ensure atomicity:

BEGIN
INSERT INTO table_name (col1, col2) SELECT val1, val2 FROM DUAL UNION ALL SELECT val3, val4 FROM DUAL;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;

For very large datasets, consider using PL/SQL's FORALL statement for bulk processing to further reduce context switches:

DECLARE
TYPE exch_tab IS TABLE OF TMP_DIM_EXCH_RT%ROWTYPE;
l_data exch_tab := exch_tab();
BEGIN
l_data.EXTEND(6);
l_data(1) := TMP_DIM_EXCH_RT(1, 1, '28-AUG-2008', 109.49, 'USD', 'JPY', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008');
l_data(2) := TMP_DIM_EXCH_RT(2, 1, '28-AUG-2008', 0.54, 'USD', 'GBP', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008');
-- Add more rows as needed
FORALL i IN 1..l_data.COUNT
INSERT INTO TMP_DIM_EXCH_RT VALUES l_data(i);
COMMIT;
END;

Additionally, pay attention to data types and constraints: numeric values do not require quotes, strings and dates should use single quotes, and dates are best handled with the TO_DATE function to avoid format issues. For example:

INSERT INTO TMP_DIM_EXCH_RT (EXCH_DATE) VALUES (TO_DATE('28-AUG-2008', 'DD-MON-YYYY'));

Error Handling and Data Integrity

In multi-row inserts, errors can lead to partial data insertion. Use the SAVE EXCEPTIONS clause to capture errors in bulk operations without interrupting the process:

DECLARE
TYPE rec_tab IS TABLE OF TMP_DIM_EXCH_RT%ROWTYPE;
l_data rec_tab;
BEGIN
-- Initialize data
FORALL i IN 1..l_data.COUNT SAVE EXCEPTIONS
INSERT INTO TMP_DIM_EXCH_RT VALUES l_data(i);
EXCEPTION
WHEN OTHERS THEN
FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Error at index ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX);
END LOOP;
END;

For handling duplicate data, use the MERGE statement or conditional inserts:

MERGE INTO TMP_DIM_EXCH_RT t
USING (SELECT 1 AS EXCH_WH_KEY FROM DUAL) s
ON (t.EXCH_WH_KEY = s.EXCH_WH_KEY)
WHEN NOT MATCHED THEN
INSERT (EXCH_WH_KEY, EXCH_NAT_KEY) VALUES (1, 1);

Conclusion and Recommendations

In Oracle Database, the multi-row insert method based on SELECT and UNION ALL excels in performance and compatibility, particularly for older versions like Oracle 9i. With updates such as Oracle 23c's simplified syntax, more options are available. Developers should choose the appropriate method based on their environment, emphasizing error handling and performance optimization to ensure efficient and reliable data operations.