Re: Oracle Direct Path Inserts

Hi,

Thanks for the reply.

> It was a typo I gave here while pasting. It was actually (R, 16) in the
hint.
> Also, the session had FORCE PARALLEL DML, FORCE PARALLEL DDL options
enabled.

Thanks,
RK

On Tue, Jan 23, 2018 at 11:43 AM, Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

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

-- 
Thanks,
Ramakrishna.V
+91 7674 976 123

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