Re: Oracle Direct Path Inserts

Thanks Andrew and everyone for the inputs.

I tested the query again and TRIGGERS were the culprit in this case.

Thanks,
RK

On Tue, Jan 23, 2018 at 12:57 PM, Andy Sayer <andysayer_at_gmail.com> wrote:

> Hi,
>
> There’s a bunch of restrictions you’ll find in the docs
> https://docs.oracle.com/cloud/latest/db112/SQLRF/
> statements_9014.htm#SQLRF01604
> Most obvious ones would be triggers or foreign keys on the target table.
>
> When you upgrade to 12c, you’ll get the exact reason why it was not
> possible in the notes section of the plan. But that doesn’t help you right
> now.
>
> Hope this helps,
> Andrew
>
> On Tue, 23 Jan 2018 at 12:51, Rama Krishna <ramakrishna.vydyula_at_gmail.com>
> wrote:
>
>> 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 <076749%2076123>
>>
>

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

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