问题描述
嗨，汤姆，
我试图使用DBMS并行执行包从5000万行表 (X) 更新约4000万行的一列与来自另一个表 (Y) 的列数据与要更新的表有多到一个关系。
该表是一个索引组织的表，所以我不能使用rowid来创建块。
如果我使用sql与row_id选项true，那么它说无效的rowid。
只有number_col可以创建块，但是大多数块都出现错误，说无法将null更新为X。
我可以得到一些帮助来了解我可能遇到的问题吗？
我试图使用DBMS并行执行包从5000万行表 (X) 更新约4000万行的一列与来自另一个表 (Y) 的列数据与要更新的表有多到一个关系。
该表是一个索引组织的表，所以我不能使用rowid来创建块。
如果我使用sql与row_id选项true，那么它说无效的rowid。
只有number_col可以创建块，但是大多数块都出现错误，说无法将null更新为X。
我可以得到一些帮助来了解我可能遇到的问题吗？
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
);
专家解答
好的，我不能用一些基本的测试数据复制
所以我不认为它与并行处理有关。我的猜测是，当你这样做:
设置X.X_name = (从Y中选择Y.Y_name，其中Y.Y_id = X.X_id)
您没有得到一行，因此试图将X_NAME设置为null。
因此，请查看将您的陈述更改为:
或者
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 设置X.X_name = (从Y中选择Y.Y_name，其中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.
所以我不认为它与并行处理有关。我的猜测是，当你这样做:
设置X.X_name = (从Y中选择Y.Y_name，其中Y.Y_id = X.X_id)
您没有得到一行，因此试图将X_NAME设置为null。
因此，请查看将您的陈述更改为:
UPDATE X 设置X.X_name = (从Y中选择Y.Y_name，其中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 )
或者
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
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
