Re: Oracle Direct Path Inserts

Hi Rama

There is wrong alias M in the hint, while right one should be R. Btw, have you enabled/forced parallel dml on session level?

23 янв. 2018 г. 14:40 пользователь "Rama Krishna" < ramakrishna.vydyula_at_gmail.com> написал:

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