Oracle Direct Path Inserts

We have an Oracle Database running on 11.2.0.4.

We had a requirement to delete 90% of data from a table. With the restriction we had in renaming the original tables; here is the approach we followed.

Size of the table is 200 GB.

1. Create a TMP table with the 10% data that is required.

CREATE TABLE MAIN_TMP TABLESPACE TS_APP_DATA DIRECT PARALLEL ( DEGREE 4) AS SELECT * FROM MAIN WHERE <<REQUIRED_DATA>>

This step completed in less than 3 minutes.

2) TRUNCATE TABLE MAIN; 3) Insert the records from TMP table to MAIN.

     INSERT /*+ PARALLEL(M,16) APPEND */ INTO MAIN R
     SELECT /*+ PARALLEL(T,16) */ T.* FROM MAIN_TMP T;