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
