Speeding Up Bulk Data Loads in Oracle with Hints

Bulk loading volumes of data from staging tables into production tables is a common task, but a slow and tedious process using basic INSERT statements in Oracle. But using query hints can dramatically improve load times, as I recently discovered how dramatical it could be.

In our ETL process, we needed to load order data from a staging table to the main orders table. Our initial INSERT statement looked like:

    INSERT INTO Orders (order_id, customer_id, order_date, total_amount)
       SELECT order_id, customer_id, order_date, total_amount
         FROM Staging_Orders;

This took many hours to millions of rows. But then we came across hint syntax that lets you fine-tune SQL behavior.
Adding a parallel and direct-path hint like:

INSERT /*+ APPEND PARALLEL(4) */ INTO Orders (order_id, customer_id, order_date, total_amount)
SELECT order_id, customer_id, order_date, total_amount
FROM Staging_Orders;

In the above SQL statement, the /*+ APPEND */ hint instructs Oracle to perform a Direct Path Insert for faster data loading. and the PARALLEL(4) hint instructs Oracle to use parallel processing with four parallel execution servers (you can adjust the number as needed based on your system's resources).

The results were mind-blowing - loading finished in under 10 minutes! For big data loads, enabling direct-path INSERT with parallelism cuts the time from hours to minutes.

I hope this example gives you some ideas on optimizing bulk data operations in Oracle through hints. Let me know if you have any other tips for supercharged ETL performance!

Move Large Amount of Data Using Insert Into

When we insert many records into a table, it is faster to use hint /*+ append */. This way, Oracle will use direct path load to insert large amount of data that is much faster than conventional insert. As we see from the example below, when /*+ apeend */ hint is used, insert only takes 0.18 second vs 0.49 with conventional insert.

SQL> insert /*+ append */ into tbl_y select * from TBL_CLAIM_ALL@dl_apex2;

1031 rows created.

Elapsed: 00:00:00.18
SQL> commit;

Commit complete.

Elapsed: 00:00:00.07
SQL> insert into tbl_y select * from TBL_CLAIM_ALL@dl_apex2;

1031 rows created.

Elapsed: 00:00:00.49
SQL> commit;