It is our improved handling in 19c.
11g was actually wrong because we should not have allowed a parallel operation on a PLSQL function that was not guaranteed to be parallel safe.
Here's a full walk through
SQL>
SQL> CREATE TABLE "TABLE_X_PURGE_DATA"
2 ( "OBJID" NUMBER,
3 "DEV" NUMBER,
4 "X_PURGE_OBJID" NUMBER,
5 "X_PURGE_TABLE" VARCHAR2(80),
6 "X_PURGE_TRANSACTION" NUMBER,
7 "X_THREAD_ID" NUMBER,
8 "X_MAIN_ENTITY" VARCHAR2(80),
9 "X_PURGE_BAN" VARCHAR2(40),
10 PRIMARY KEY ("OBJID")
11 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
12 ) SEGMENT CREATION IMMEDIATE;
Table created.
SQL>
SQL>
SQL>
SQL> CREATE TABLE "TABLE_X"
2 ( "OBJID" NUMBER,
3 "DEV" NUMBER,
4 "X_ACCOUNTID" VARCHAR2(40),
5 "S_X_ACCOUNTID" VARCHAR2(40),
6 "X_DATE" DATE,
7 "X_USERID" VARCHAR2(20),
8 "X_SITED" VARCHAR2(80),
9 "X_ACTIVITY" VARCHAR2(40),
10 "X_SCREEN_NAME" VARCHAR2(60),
11 "X_DATA_NAME_1" VARCHAR2(60),
12 "X_VALUE_NAME_1" VARCHAR2(255),
13 "X_DATA_NAME_2" VARCHAR2(60),
14 "X_VALUE_NAME_2" VARCHAR2(60),
15 "X_DATA_NAME_3" VARCHAR2(60),
16 "X_VALUE_NAME_3" VARCHAR2(60),
17 "S_X_USERID" VARCHAR2(20),
18 "X_CLIENT_HOST" VARCHAR2(60),
19 "X_SERVER_HOST" VARCHAR2(60)
20 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
21 PARTITION BY RANGE ("X_DATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
22 (
23 PARTITION "WEEK_01_2016" VALUES LESS THAN (date '2016-01-04') SEGMENT CREATION IMMEDIATE,
24 PARTITION "WEEK_02_2016" VALUES LESS THAN (date '2016-01-11') SEGMENT CREATION IMMEDIATE,
25 PARTITION "WEEK_03_2016" VALUES LESS THAN (date '2016-01-18') SEGMENT CREATION IMMEDIATE,
26 PARTITION "WEEK_04_2016" VALUES LESS THAN (date '2016-01-25') SEGMENT CREATION IMMEDIATE,
27 PARTITION "WEEK_05_2016" VALUES LESS THAN (date '2016-02-01') SEGMENT CREATION IMMEDIATE
28 );
Table created.
SQL>
SQL> create sequence PURGE_OBJID_SEQ;
Sequence created.
SQL>
SQL> create or replace package purge is
2 function func_date_minus(d date) return date;
3 end;
4 /
Package created.
SQL>
SQL> create or replace package body purge is
2 function func_date_minus(d date) return date is begin return sysdate; end;
3 end;
4 /
Package body created.
SQL>
SQL> explain plan for
2 INSERT INTO TABLE_X_PURGE_DATA (OBJID, X_PURGE_OBJID, X_PURGE_TABLE)
3 SELECT /*+ full(xa) parallel(xa 8) */ PURGE_OBJID_SEQ.NEXTVAL, XA.OBJID, 'TABLE_X'
4 FROM TABLE_X XA
5 WHERE XA.X_DATE < date '2021-01-01' -- PURGE.FUNC_DATE_MINUS (:B2 )
6 AND XA.X_DATE > TO_DATE ('1753.01.01', 'yyyy.mm.dd')
7 AND ROWNUM <= :B1;
Explained.
SQL> select * from dbms_xplan.display();
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
Plan hash value: 1394344913
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 22 | 2 (0)| 00:00:01 | | | | | |
| 1 | LOAD TABLE CONVENTIONAL | TABLE_X_PURGE_DATA | | | | | | | | | |
| 2 | SEQUENCE | PURGE_OBJID_SEQ | | | | | | | | | |
|* 3 | COUNT STOPKEY | | | | | | | | | | |
| 4 | PX COORDINATOR | | | | | | | | | | |
| 5 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 22 | 2 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
|* 6 | COUNT STOPKEY | | | | | | | | Q1,00 | PCWC | |
| 7 | PX BLOCK ITERATOR | | 1 | 22 | 2 (0)| 00:00:01 | 1 | 64 | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL| TABLE_X | 1 | 22 | 2 (0)| 00:00:01 | 1 | 64 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<=TO_NUMBER(:B1))
6 - filter(ROWNUM<=TO_NUMBER(:B1))
8 - filter("XA"."X_DATE"<TO_DATE(' 2021-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "XA"."X_DATE">TO_DATE(' 1753-01-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 8 because of table property
- PDML is disabled in current session
29 rows selected.
SQL>
SQL> explain plan for
2 INSERT INTO TABLE_X_PURGE_DATA (OBJID, X_PURGE_OBJID, X_PURGE_TABLE)
3 SELECT /*+ full(xa) parallel(xa 8) */ PURGE_OBJID_SEQ.NEXTVAL, XA.OBJID, 'TABLE_X'
4 FROM TABLE_X XA
5 WHERE XA.X_DATE < PURGE.FUNC_DATE_MINUS (:B2 )
6 AND XA.X_DATE > TO_DATE ('1753.01.01', 'yyyy.mm.dd')
7 AND ROWNUM <= :B1;
Explained.
SQL> select * from dbms_xplan.display();
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
Plan hash value: 2575781037
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 22 | 2 (0)| 00:00:01 | | |
| 1 | LOAD TABLE CONVENTIONAL | TABLE_X_PURGE_DATA | | | | | | |
| 2 | SEQUENCE | PURGE_OBJID_SEQ | | | | | | |
|* 3 | COUNT STOPKEY | | | | | | | |
|* 4 | FILTER | | | | | | | |
| 5 | PARTITION RANGE ALL | | 1 | 22 | 2 (0)| 00:00:01 | 1 |1048575|
|* 6 | TABLE ACCESS FULL | TABLE_X | 1 | 22 | 2 (0)| 00:00:01 | 1 |1048575|
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<=TO_NUMBER(:B1))
4 - filter("PURGE"."FUNC_DATE_MINUS"(:B2)>TO_DATE(' 1753-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
6 - filter("XA"."X_DATE">TO_DATE(' 1753-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"XA"."X_DATE"<"PURGE"."FUNC_DATE_MINUS"(:B2))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
6 - SEL$1 / XA@SEL$1
U - parallel(xa 8)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
32 rows selected.
SQL>
SQL> create or replace package purge is
2 function func_date_minus(d date) return date parallel_enable;
3 end;
4 /
Package created.
SQL>
SQL> create or replace package body purge is
2 function func_date_minus(d date) return date parallel_enable is begin return sysdate; end;
3 end;
4 /
Package body created.
SQL> explain plan for
2 INSERT INTO TABLE_X_PURGE_DATA (OBJID, X_PURGE_OBJID, X_PURGE_TABLE)
3 SELECT /*+ full(xa) parallel(xa 8) */ PURGE_OBJID_SEQ.NEXTVAL, XA.OBJID, 'TABLE_X'
4 FROM TABLE_X XA
5 WHERE XA.X_DATE < date '2021-01-01' -- PURGE.FUNC_DATE_MINUS (:B2 )
6 AND XA.X_DATE > TO_DATE ('1753.01.01', 'yyyy.mm.dd')
7 AND ROWNUM <= :B1;
Explained.
SQL> select * from dbms_xplan.display();
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
Plan hash value: 1394344913
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 22 | 2 (0)| 00:00:01 | | | | | |
| 1 | LOAD TABLE CONVENTIONAL | TABLE_X_PURGE_DATA | | | | | | | | | |
| 2 | SEQUENCE | PURGE_OBJID_SEQ | | | | | | | | | |
|* 3 | COUNT STOPKEY | | | | | | | | | | |
| 4 | PX COORDINATOR | | | | | | | | | | |
| 5 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 22 | 2 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
|* 6 | COUNT STOPKEY | | | | | | | | Q1,00 | PCWC | |
| 7 | PX BLOCK ITERATOR | | 1 | 22 | 2 (0)| 00:00:01 | 1 | 64 | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL| TABLE_X | 1 | 22 | 2 (0)| 00:00:01 | 1 | 64 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<=TO_NUMBER(:B1))
6 - filter(ROWNUM<=TO_NUMBER(:B1))
8 - filter("XA"."X_DATE"<TO_DATE(' 2021-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "XA"."X_DATE">TO_DATE(' 1753-01-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 8 because of table property
- PDML is disabled in current session
29 rows selected.
SQL>
SQL>