Re: Oracle Direct Path Inserts

> 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.
>

Could there by any specific reason Optimiser chose CONVENTIONAL over DIRECT PATH INSERTS? Thanks,
RK

--
http://www.freelists.org/webpage/oracle-l