Hi TOM,
I am trying to use DBMS PARALLEL EXECUTE package to update one column of around 40 million rows from a 50 million rows table(X) with the column data from another table(Y) which has a many to one relationship with the table to be updated.
The table is an INDEX organised table so i cant use rowid to create chunks.
and if i use sql with row_id option true then it says invalid rowid.
Only number_col works to create chunks but most of the chunks process with error saying that cannot update null to X.
can i get some help in understanding where i might be having issue?
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_NUMBER_COL(
task_name => 'mytask',
table_owner => 'T',
table_name => 'X',
table_column => 'X_ID',
chunk_size => 20000
);
l_sql_stmt := 'UPDATE X
SET X.X_name=(SELECT Y.Y_name FROM Y WHERE Y.Y_id=X.X_id)
where X.X_id between :start_id and :end_id
and REGEXP_LIKE(X.X_name, ''[^A-Za-z_-]'')';
DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', l_sql_stmt, DBMS_SQL.NATIVE,
parallel_level => 10);
DDL for X
CREATE TABLE "Project"."X"
( "X_ID" NUMBER(38,0) NOT NULL ENABLE,
"X_CREATE_DATE" DATE DEFAULT SYSDATE NOT NULL ENABLE,
"EMP_ID" NUMBER(38,0) NOT NULL ENABLE,
"X_START_DATE" DATE NOT NULL ENABLE,
"X_END_DATE" DATE,
"X_START_TIME" DATE,
"X_END_TIME" DATE,
"X_OLD_VALUE" VARCHAR2(4000 BYTE),
"X_NEW_VALUE" VARCHAR2(4000 BYTE),
"X_STATUS" VARCHAR2(40 BYTE) DEFAULT 'APPLY' NOT NULL ENABLE,
"XTYP_ID" NUMBER(38,0) NOT NULL ENABLE,
"X_COMMENT" VARCHAR2(4000 BYTE),
"X_MESSAGE" VARCHAR2(4000 BYTE),
"X_NAME" VARCHAR2(40 BYTE) NOT NULL ENABLE,
"X_FLAG1" VARCHAR2(1 BYTE),
"X_FLAG2" VARCHAR2(1 BYTE),
"X_FLAG3" VARCHAR2(1 BYTE),
"X_FLAG4" VARCHAR2(1 BYTE),
"X_FLAG5" VARCHAR2(1 BYTE),
"X_FLAG6" VARCHAR2(1 BYTE),
"X_FLAG7" VARCHAR2(1 BYTE),
"X_FLAG8" VARCHAR2(1 BYTE),
"X_FLAG9" VARCHAR2(1 BYTE),
"X_FLAG10" VARCHAR2(1 BYTE),
"X_UDF1" VARCHAR2(40 BYTE),
"X_UDF2" VARCHAR2(40 BYTE),
"X_UDF3" VARCHAR2(40 BYTE),
"X_UDF4" VARCHAR2(40 BYTE),
"X_UDF5" VARCHAR2(40 BYTE),
"X_UDF6" VARCHAR2(40 BYTE),
"X_UDF7" VARCHAR2(40 BYTE),
"X_UDF8" VARCHAR2(40 BYTE),
"X_UDF9" VARCHAR2(40 BYTE),
"X_UDF10" VARCHAR2(40 BYTE),
"X_ORDER" NUMBER(38,0),
"X_CANCELLED_DATE" DATE,
"PPU_NAME_ACTUAL" VARCHAR2(40 BYTE) NOT NULL ENABLE,
"CLIENT_ID" NUMBER(38,0) DEFAULT 1 NOT NULL ENABLE,
"X_CANCELLED_BY" VARCHAR2(40 BYTE),
"X_CANCELLED_BY_A" VARCHAR2(40 BYTE),
CONSTRAINT "CHK_X_STATUS" CHECK (X_STATUS IN
('PENDING','APPLIED','CANCEL','CANCELLED',
'ERROR','HOLDING','UNPUBLISHED','WARNING',
'UNPUBLISHED_WARNING')) ENABLE,
CONSTRAINT "PK_X" PRIMARY KEY ("X_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PP_TDE_INDX01" ENABLE,
CONSTRAINT "FK_ORIDE_XTYP_ID" FOREIGN KEY ("XTYP_ID")
REFERENCES "Project"."X_TYPE" ("XTYP_ID") ENABLE,
CONSTRAINT "FK_X_EMP_ID" FOREIGN KEY ("EMP_ID")
REFERENCES "Project"."EMPLOYEE" ("EMP_ID") ON DELETE CASCADE ENABLE NOVALIDATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PP_TDE_DATA01" ;
CREATE INDEX "Project"."IDX_XEP_ID_DATES" ON "Project"."X" ("EMP_ID", "X_START_DATE", "X_END_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PP_TDE_INDX01" ;
CREATE INDEX "Project"."IDX_XEP_STAT_CD" ON "Project"."X" ("EMP_ID", "X_STATUS", "X_CREATE_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PP_TDE_INDX01" ;
CREATE INDEX "Project"."IDX_X_CD_EMP_ID" ON "Project"."X" ("X_CREATE_DATE", "EMP_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PP_TDE_INDX01" ;
CREATE INDEX "Project"."IDX_X_EMPTPSTSDT" ON "Project"."X" ("EMP_ID", "XTYP_ID", "X_STATUS", "X_START_DATE", "CLIENT_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PP_TDE_INDX01" ;
CREATE INDEX "Project"."IDX_X_XTYP_ID" ON "Project"."X" ("XTYP_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PP_TDE_INDX01" ;
CREATE INDEX "Project"."IDX_X_ST_DT" ON "Project"."X" ("X_START_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PP_TDE_INDX01" ;
CREATE INDEX "Project"."IDX_X_UDF4" ON "Project"."X" ("X_UDF4")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PP_TDE_DATA01" ;
CREATE INDEX "Project"."IDX_X_UDF7" ON "Project"."X" ("X_UDF7")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PP_TDE_DATA01" ;
DDL for Y: ( this is the temporary table i created to update the data)
CREATE TABLE Y
(
Y_id NUMBER(38) NOT NULL PRIMARY KEY ,
Y_name VARCHAR2(40) NOT NULL
);
OK, I can't reproduce with some basic test data
SQL> CREATE TABLE X
2 (
3 X_ID NUMBER(38,0) NOT NULL ENABLE,
4 X_CREATE_DATE DATE DEFAULT SYSDATE NOT NULL ENABLE,
5 EMP_ID NUMBER(38,0) NOT NULL ENABLE,
6 X_START_DATE DATE NOT NULL ENABLE,
7 X_END_DATE DATE,
8 X_START_TIME DATE,
9 X_END_TIME DATE,
10 X_OLD_VALUE VARCHAR2(4000 BYTE),
11 X_NEW_VALUE VARCHAR2(4000 BYTE),
12 X_STATUS VARCHAR2(40 BYTE) DEFAULT 'APPLY' NOT NULL ENABLE,
13 XTYP_ID NUMBER(38,0) NOT NULL ENABLE,
14 X_COMMENT VARCHAR2(4000 BYTE),
15 X_MESSAGE VARCHAR2(4000 BYTE),
16 X_NAME VARCHAR2(40 BYTE) NOT NULL ENABLE,
17 X_FLAG1 VARCHAR2(1 BYTE),
18 X_FLAG2 VARCHAR2(1 BYTE),
19 X_FLAG3 VARCHAR2(1 BYTE),
20 X_FLAG4 VARCHAR2(1 BYTE),
21 X_FLAG5 VARCHAR2(1 BYTE),
22 X_FLAG6 VARCHAR2(1 BYTE),
23 X_FLAG7 VARCHAR2(1 BYTE),
24 X_FLAG8 VARCHAR2(1 BYTE),
25 X_FLAG9 VARCHAR2(1 BYTE),
26 X_FLAG10 VARCHAR2(1 BYTE),
27 X_UDF1 VARCHAR2(40 BYTE),
28 X_UDF2 VARCHAR2(40 BYTE),
29 X_UDF3 VARCHAR2(40 BYTE),
30 X_UDF4 VARCHAR2(40 BYTE),
31 X_UDF5 VARCHAR2(40 BYTE),
32 X_UDF6 VARCHAR2(40 BYTE),
33 X_UDF7 VARCHAR2(40 BYTE),
34 X_UDF8 VARCHAR2(40 BYTE),
35 X_UDF9 VARCHAR2(40 BYTE),
36 X_UDF10 VARCHAR2(40 BYTE),
37 X_ORDER NUMBER(38,0),
38 X_CANCELLED_DATE DATE,
39 PPU_NAME_ACTUAL VARCHAR2(40 BYTE) NOT NULL ENABLE,
40 CLIENT_ID NUMBER(38,0) DEFAULT 1 NOT NULL ENABLE,
41 X_CANCELLED_BY VARCHAR2(40 BYTE),
42 X_CANCELLED_BY_A VARCHAR2(40 BYTE),
43 CONSTRAINT CHK_X_STATUS CHECK (X_STATUS IN
44 ('PENDING','APPLIED','CANCEL','CANCELLED',
45 'ERROR','HOLDING','UNPUBLISHED','WARNING',
46 'UNPUBLISHED_WARNING')) ENABLE,
47 CONSTRAINT PK_X PRIMARY KEY (X_ID)
48 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
49 STORAGE(INITIAL 65536 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645
50 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
51 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
52 ENABLE
53 ) SEGMENT CREATION IMMEDIATE
54 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
55 NOCOMPRESS LOGGING
56 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
57 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
58 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
59 ;
Table created.
SQL>
SQL>
SQL>
SQL> CREATE TABLE Y
2 (
3 Y_id NUMBER(38) NOT NULL PRIMARY KEY ,
4 Y_name VARCHAR2(40) NOT NULL
5 );
Table created.
SQL>
SQL>
SQL> insert into x
2 ( X_ID
3 , X_CREATE_DATE
4 , EMP_ID
5 , X_START_DATE
6 , X_STATUS
7 , XTYP_ID
8 , X_NAME
9 , PPU_NAME_ACTUAL
10 , CLIENT_ID )
11 select
12 rownum,
13 sysdate,
14 rownum,
15 sysdate,
16 'PENDING',
17 rownum,
18 upper(substr(object_name,1,40)),
19 'x',
20 rownum
21 from dba_objects;
78955 rows created.
SQL>
SQL> insert into y
2 select rownum, lower(substr(object_name,1,40))
3 from dba_objects;
78955 rows created.
SQL>
SQL> exec DBMS_PARALLEL_EXECUTE.create_task (task_name => 'mytask');
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
2 DBMS_PARALLEL_EXECUTE.create_chunks_by_number_col(task_name => 'mytask',
3 table_owner => user,
4 table_name => 'X',
5 table_column => 'X_ID',
6 chunk_size => 10000);
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT chunk_id, status, start_id, end_id
2 FROM user_parallel_execute_chunks
3 WHERE task_name = 'mytask'
4 ORDER BY chunk_id;
CHUNK_ID STATUS START_ID END_ID
---------- -------------------- ---------- ----------
1641 UNASSIGNED 1 10000
1642 UNASSIGNED 10001 20000
1643 UNASSIGNED 20001 30000
1644 UNASSIGNED 30001 40000
1645 UNASSIGNED 40001 50000
1646 UNASSIGNED 50001 60000
1647 UNASSIGNED 60001 70000
1648 UNASSIGNED 70001 78955
8 rows selected.
SQL> DECLARE
2 l_sql_stmt VARCHAR2(32767);
3 BEGIN
4 l_sql_stmt := 'UPDATE X
5 SET X.X_name=(SELECT Y.Y_name FROM Y WHERE Y.Y_id=X.X_id)
6 where X.X_id between :start_id and :end_id
7 and REGEXP_LIKE(X.X_name, ''[^A-Za-z_-]'')';
8
9 DBMS_PARALLEL_EXECUTE.run_task(task_name => 'mytask',
10 sql_stmt => l_sql_stmt,
11 language_flag => DBMS_SQL.NATIVE,
12 parallel_level => 2);
13 END;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT chunk_id, status, start_id, end_id
2 FROM user_parallel_execute_chunks
3 WHERE task_name = 'mytask'
4 ORDER BY chunk_id;
CHUNK_ID STATUS START_ID END_ID
---------- -------------------- ---------- ----------
1641 PROCESSED 1 10000
1642 PROCESSED 10001 20000
1643 PROCESSED 20001 30000
1644 PROCESSED 30001 40000
1645 PROCESSED 40001 50000
1646 PROCESSED 50001 60000
1647 PROCESSED 60001 70000
1648 PROCESSED 70001 78955
8 rows selected.
SQL> select x_id, x_name
2 from x
3 where x_id in ( select y_id from y)
4 and rownum < 30;
X_ID X_NAME
---------- ----------------------------------------
253 I_CONTEXT
254 sql_version$
255 i_sql_version$_version#
256 jijoin$
257 i_jijoin$
258 i2_jijoin$
259 i3_jijoin$
260 jirefreshsql$
261 sys_il0000000262c00003$$
262 sys_lob0000000262c00003$$
263 i1_jirefreshsql$
264 i2_jirefreshsql$
265 trigger$
266 triggercol$
267 i_trigger1
268 i_trigger2
269 i_triggercol1
270 i_triggercol2
271 triggerjavaf$
272 triggerjavas$
273 triggerjavac$
274 triggerjavam$
275 I_TRIGGERJAVAF
276 I_TRIGGERJAVAS
277 I_TRIGGERJAVAC
278 I_TRIGGERJAVAM
279 viewtrcol$
280 i_viewtrcol1
281 atemptab$
29 rows selected.
So I don't its related to parallel processing. My guess is that when you do:
SET X.X_name=(SELECT Y.Y_name FROM Y WHERE Y.Y_id=X.X_id)
you are not getting a row, and hence trying to set X_NAME to null.
So look at changing your statement to:
UPDATE X
SET X.X_name=(SELECT Y.Y_name FROM Y WHERE Y.Y_id=X.X_id)
where X.X_id between :start_id and :end_id
and REGEXP_LIKE(X.X_name, '[^A-Za-z_-]')
and x_id in ( select y_id from y )
or
merge into ( select * from x where X.X_id between :start_id and :end_id )
using ( select y_id, y_name from y )
on ( x_id = y_id )
when matched then
update set x_name = y_name