Hi All,
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;
Step 3, I assumed would take a DIRECT PATH insert, but this took relatively
longer to copy 12 GB of data to the MAIN table.
Looking at the plan, it went for LOAD TABLE CONVENTIONAL
