In Oracle Database 23ai, Oracle has enhanced Data Pump to create indexes more efficiently. This can significantly reduce the time it takes to create indexes during a Data Pump import.

Oracle also backported the enhancement. You find the new features in:

• 23.8 with Data Pump Bundle Patch
• 19.26 with Data Pump Bundle Patch

In any case, the new feature is on by default. No configuration is needed; just enjoy faster imports.

Benchmark

I made a benchmark using a schema with:

• 100 small tables (125 MB)
• 50 medium tables (1,5 GB)
• 10 big tables (25 GB)
• 1 huge table (100 GB)
• Each table had three indexes – 483 indexes in total

Using the new index method, the import went from almost 18 minutes to 11 minutes.

Here are extracts from the import log file:

# The old method
10-MAY-25 16:36:46.902: W-30 Completed 483 INDEX objects in 1071 seconds

# The new method
10-MAY-25 15:59:17.006: W-3 Completed 483 INDEX objects in 686 seconds

Details

So far, I haven’t seen a case where the new method is slower than the former method. However, should you want to revert to the old way of creating indexes, you can do that with the Data Pump parameter ONESTEP_INDEX=TRUE.

What Happens

To understand what happens, let’s go back in time to Oracle Database 11g. Imagine an import with PARALLEL=16. Data Pump would use one worker process to create indexes one at a time using CREATE INDEX ... PARALLEL 16. This is efficient for large indexes.

In Oracle Database 12c, the algorithm changed to better fit schemas with more indexes and especially many smaller indexes. Now, Data Pump would use all 16 workers, and each would create indexes using CREATE INDEX ... PARALLEL 1. However, this turned out to be a performance-killer for large indexes.

In Oracle Database 23ai (and 19c), you get the best of both worlds. Data Pump uses the size of the table to determine an optimal parallel degree. It creates smaller indexes in large batches with PARALLEL 1, and larger indexes using an optimal parallel degree up to PARALLEL 15.

Happy importing!