RE: Oracle Direct Path Inserts

alter session enable parallel dml  

or  

adding the hint  

ENABLE_PARALLEL_DML   should fix this.  

Yes, I saw that you wrote FORCE. IF memory serves, that keeps it PARALLEL if the optimizer evaluates it that serial is better. But if PARALLEL_DML is not enabled, you’re not going to get it as an even possible solution.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Andy Sayer Sent: Tuesday, January 23, 2018 7:57 AM
To: ramakrishna.vydyula_at_gmail.com
Cc: ORACLE-L; Sayan Malakshinov
Subject: 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