Re: Oracle Direct Path Inserts

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
>

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