徐州企业建站怎样给自己做网站

小伙伴们，在从前的oracle版本中，对表进行并行 DML后，必须COMMIT的步骤可以改变了。在Oracle Database 23ai中，​Unrestricted Parallel DML（无限制并行DML）​​ 新特性，改进后将解决了长期困扰开发者的“Touch-Once”限制问题。

一、为什么需要Unrestricted Parallel DML

​Touch-Once机制​​

23ai的特性改进​

二、老版本 vs 23ai：对比与验证脚本 

环境准备

-- 1. 创建测试表
DROP TABLE test31 PURGE;
CREATE TABLE test31 AS SELECT * FROM all_objects;-- 2. 启用并行DML
ALTER SESSION ENABLE PARALLEL DML;
--disabled
DISABLE_PARALLEL_DML SQL;
--parallel
INSERT /*+ PARALLEL(test31, 4) */ 
SYS@CDB$ROOT> show pdbs;CON_ID CON_NAME    OPEN MODE     RESTRICTED
_________ ___________ _____________ _____________2 PDB$SEED    READ ONLY     NO3 FREEPDB1    READ WRITE    NO
SYS@CDB$ROOT> alter session set container=FREEPDB1;
Session altered.
SYS@CDB$ROOT> DROP TABLE test31 PURGE;
Error starting at line : 1 in command -
DROP TABLE test31 PURGE
Error report -
ORA-00942: table or view "SYS"."TEST31" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/00942. 00000 -  "table or view%s does not exist"
*Cause:    The specified table or view did not exist, or a synonympointed to a table or view that did not exist.To find existing user tables and views, query theALL_TABLES and ALL_VIEWS data dictionary views. Certainprivileges may be required to access the table. If anapplication returned this message, then the table that theapplication tried to access did not exist in the database, orthe application did not have access to it.
*Action:   Check each of the following- The spelling of the table or view name is correct.- The referenced table or view name does exist.- The synonym points to an existing table or view.
SYS@CDB$ROOT> CREATE TABLE test31 AS SELECT * FROM dba_users;Table TEST31 created.
SYS@CDB$ROOT> ALTER SESSION ENABLE PARALLEL DML;Session altered.

老版本（19c）的Touch-Once限制 验证脚本

drop table t1 purge;
create table t1 as select * from all_objects;
-- 并行插入数据
insert /*+ parallel(t1 4) */ into t1 select /*+ parallel(t1 4)*/ * from t1;
-- 立即查询表 → 触发ORA-12838
SELECT COUNT(*) FROM test31; 
第 1 行出现错误:
ORA-12838SYS@test19> !oerr ora 12838
12838, 00000, "cannot read/modify an object after modifying it in parallel"
// *Cause: Within the same transaction, an attempt was made to add read or
// modification statements on a table after it had been modified in parallel
// or with direct load. This is not permitted.
// *Action: Rewrite the transaction, or break it up into two transactions:
// one containing the initial modification and the second containing the
// parallel modification operation.

23 ai的Unrestricted Parallel DML 验证脚本

drop table test31 purge;
create table test31 as select * from all_objects;
alter session enable parallel dml;
-- 1. 并行插入后立即查询 → 成功
insert /*+ parallel(t1 4) */ into test31 select /*+ parallel(t1 4)*/ * from test31;SELECT COUNT(*) FROM test31;  -- 返回正确行数
SYS@CDB$ROOT> SELECT COUNT(*) FROM TEST31;
SYS@CDB$ROOT> SELECT COUNT(*) FROM test31;COUNT(*)
___________70201-- 2. 同一事务内多次并行DML操作
INSERT /*+ PARALLEL(test31, 4) */ INTO test31 SELECT * FROM test31;  -- 二次插入
UPDATE /*+ PARALLEL(test31, 4) */ test31 SET USERNAME = LOWER(USERNAME); -- 并行更新
272 rows updated.
SYS@CDB$ROOT> INSERT /*+ PARALLEL(test31, 4) */ INTO test31 SELECT * FROM test31;
272 rows inserted.
SYS@CDB$ROOT> UPDATE /*+ PARALLEL(test31, 4) */ test31 SET USERNAME = LOWER(USERNAME);
544 rows updated.
-- 返回最终行数（无错误）
SELECT COUNT(*) FROM test31;
SYS@CDB$ROOT> SELECT COUNT(*) FROM TEST31;COUNT(*)
___________544-- 直接路径追加，再次更新将会报错
INSERT /*+ APPEND */ INTO test31 SELECT * FROM test31;
SYS@CDB$ROOT> INSERT /*+ APPEND */ INTO test31 SELECT * FROM test31;
544 rows inserted.
SYS@CDB$ROOT> UPDATE /*+ PARALLEL(test31, 4) */ test31 SET USERNAME = LOWER(USERNAME);Error starting at line : 1 in command -
UPDATE /*+ PARALLEL(test31, 4) */ test31 SET USERNAME = LOWER(USERNAME)
Error report -
ORA-12838: cannot read/modify an object after modifying it in parallel
Help: https://docs.oracle.com/error-help/db/ora-12838/
More Details :
https://docs.oracle.com/error-help/db/ora-12838/
SYS@CDB$ROOT>
-- 返回最终行数发生错误
--SQL Error: ORA-12838: cannot read/modify an object after modifying it in parallel
COMMIT
-- APPEND 需要COMMIT提交后，才能返回最终行数（无错误）
SELECT COUNT(*) FROM test31;

 三、输出对比

四、23ai Unrestricted Parallel DML的应用场景

INSERT /*+ PARALLEL */ INTO target_table SELECT /*+ PARALLEL */ * FROM raw_data;
UPDATE /*+ PARALLEL */ target_table SET status = 'PROCESSED';
SELECT /*+ PARALLEL */ * FROM target_table; -- 即时验证

• 实时大事务处理​ 并行更新的场景中，批量更新账户后立即生成审计报告： 

UPDATE /*+ PARALLEL(acc, 8) */ accounts acc SET balance = balance * 1.05;
SELECT SUM(balance) FROM accounts; -- 实时汇总

五、验证后体会

sql
/*+ PARALLEL(test31, 6) */ -- 在8核服务器上设置6线程

TIPS：

Oracle 23ai的Unrestricted Parallel DML通过事务版本控制增强与无锁保留机制，解决了并行DML的隔离性枷锁，使开发者能在同一事务内自由组合批量操作与即时查询。这一特性大幅简化了大数据处理架构，为实时分析、AI训练等场景提供了原生级支持。