Home » SQL & PL/SQL » SQL & PL/SQL » - PDML disabled because single fragment or non partitioned table used (Oracle 12c)
- PDML disabled because single fragment or non partitioned table used [message #681553] Mon, 03 August 2020 17:14 Go to next message
manoj12
Messages: 203
Registered: March 2008
Location: India
Senior Member
Hello Sir,

I am trying to below update the statement through ODI. Before running this update statement i have also executed Parallel Updates in the below query as shown below

ALTER SESSION ENABLE PARALLEL DML;

 update /*+ PARALLEL(24) */ BI_REP.REP_WO_DETAIL T  set     (   
T.LATEST_FLAG   ) =    (    select S.LATEST_FLAG    from 
BI_STG.I$_REP_WO_DETAIL S    where T.SNAPSHOT_DATE 
=S.SNAPSHOT_DATE         )  where exists (    select  'X'    from 
BI_STG.I$_REP_WO_DETAIL T2    where T2.SNAPSHOT_DATE 
=T.SNAPSHOT_DATE   );

Plan hash value: 1677052872
 
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                     |                       |       |       |  5482K(100)|          |        |      |            |
|   1 |  UPDATE                              | REP_WORP_WO_DETAIL    |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR                     |                       |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)               | :TQ10001              |  1822K|   112M| 14448   (1)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|*  4 |     HASH JOIN RIGHT SEMI             |                       |  1822K|   112M| 14448   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      BUFFER SORT                     |                       |       |       |            |          |  Q1,01 | PCWC |            |
|   6 |       PX RECEIVE                     |                       |     1 |    11 |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   7 |        PX SEND BROADCAST             | :TQ10000              |     1 |    11 |     1   (0)| 00:00:01 |        | S->P | BROADCAST  |
|   8 |         INDEX FULL SCAN              | I$_REP_WO_DETAIL |     1 |    11 |     1   (0)| 00:00:01 |        |      |            |
|   9 |      PX BLOCK ITERATOR               |                       |    11M|   570M| 14446   (1)| 00:00:01 |  Q1,01 | PCWC |            |
|* 10 |       TABLE ACCESS FULL              | REP_WORP_WO_DETAIL    |    11M|   570M| 14446   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|  11 |   TABLE ACCESS BY INDEX ROWID BATCHED| I$_REP_WORP_WO_DETAIL |     1 |    13 |     2   (0)| 00:00:01 |        |      |            |
|* 12 |    INDEX RANGE SCAN                  | I$_REP_WORP_WO_DETAIL |     1 |       |     1   (0)| 00:00:01 |        |      |            |
-------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("T2"."SNAPSHOT_DATE"="T"."SNAPSHOT_DATE")
  10 - access(:Z>=:Z AND :Z<=:Z)
  12 - access("S"."SNAPSHOT_DATE"=:B1)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=AUTO)
   - Degree of Parallelism is 24 because of hint
   - PDML disabled because single fragment or non partitioned table used
 


Since I am running the session in Parallel I am getting PDML disabled because single fragment or non partitioned table used in the execution plan. This above update statement is not running in Parallel Mode.

What could be the possible solution for running the update statement in Parallel Mode.

Regards
Re: - PDML disabled because single fragment or non partitioned table used [message #681565 is a reply to message #681553] Tue, 04 August 2020 06:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
Does table have LOB columns?

SY.
Re: - PDML disabled because single fragment or non partitioned table used [message #681571 is a reply to message #681565] Tue, 04 August 2020 15:56 Go to previous messageGo to next message
manoj12
Messages: 203
Registered: March 2008
Location: India
Senior Member
Hello Sir,

Yes. The table have a column with clob datatype.

Regards,
Re: - PDML disabled because single fragment or non partitioned table used [message #681590 is a reply to message #681571] Thu, 06 August 2020 10:12 Go to previous message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
PDML UPDATE is not allowed when table has LOB columns.

SY.
Previous Topic: Converting XML to JSON using Apex
Next Topic: INSERT WHEN NOT EXIST
Goto Forum:
  


Current Time: Fri Sep 18 19:42:37 CDT 2020