Insert query optimisation - Oracle One of my developers is trying to perform insert into a table by selecting from a table.

Below is the query that was run. The query takes extremely long time for execution (4hrs odd).

Number of records on the table where it is getting imported is 1.2 million.

Database - Oracle 12c on Linux with archivelog mode on.

insert  /*+ parallel(24) */  into data1.frido_dev select  /*+ parallel(24) */  *
from DATA_MAIN;


How can the insert of such bulk volumes be optimized? Can we use append? Will that be helpful?

I'm not good at tuning queries, would be great if someone helps me out here.
Unless you have a special case, an INSERT with 1.2 million rows should be completed in seconds or minutes at most.

Yes, direct-path insert may significantly reduce execution time.

The above hint in INSERT has no effect without the necessary prerequisites.

Enable Parallel DML Mode


  A DML statement can be parallelized only if you have explicitly
  enabled parallel DML in the session or in the SQL statement. To enable
  this mode in a session, run the following SQL statement:
  
  ALTER SESSION ENABLE PARALLEL DML;
  
  To enable parallel DML mode in a
  specific SQL statement, include the ENABLE_PARALLEL_DML SQL hint. For
  example:
  
  INSERT /*+ ENABLE_PARALLEL_DML */ …
  
  This mode is required because parallel DML and serial DML have different locking, transaction, and
  disk space requirements and parallel DML is disabled for a session by
  default.
  
  When parallel DML is disabled, no DML is executed in parallel even if
  the PARALLEL hint is used.