In this recipe we will see how to insert many rows in a table using a particular INSERT statement to boost performance.
The following steps demonstrates multiple row insertions in the same INSERT statement:
Connect to the
SHschema:CONNECT sh@TESTDB/shCreate an empty table
MY_SALESwith theSALEStable structure:CREATE TABLE MY_SALES AS SELECT * FROM SALES WHERE ROWNUM < 1;Insert all the rows from
SALEStable in the newly-created table:SET TIMING ON INSERT INTO MY_SALES SELECT * FROM SALES; COMMIT; SET TIMING OFF
Empty the
MY_SALEStable:TRUNCATE TABLE MY_SALES;Insert all of the rows from the
SALEStable in the newly-created table using direct path inserting:SET TIMING ON INSERT /*+ APPEND */ INTO MY_SALES SELECT * FROM SALES; COMMIT; SET TIMING OFF
Show the execution plan for classical
INSERTstatement:EXPLAIN PLAN FOR INSERT INTO MY_SALES SELECT * FROM SALES; SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY(null,null,'TYPICAL -BYTES'));
Show the...