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@xxxxxxxxx>
wrote:
Hi,
Thanks for the reply.
It was a typo I gave here while pasting. It was actually (R, 16) in thehint.
Also, the session had FORCE PARALLEL DML, FORCE PARALLEL DDL optionsenabled.
Thanks,
RK
On Tue, Jan 23, 2018 at 11:43 AM, Sayan Malakshinov <xt.and.r@xxxxxxxxx>
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@xxxxxxxxx> написал:
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
