dingjun在QQ群中说的,我只是测试一把
SQL> create table big_tab as select * from all_objects;
表已创建。
已用时间: 00: 00: 05.99
SQL> desc big_tab
名称 是否为空? 类型
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NOT NULL NUMBER
EDITION_NAME VARCHAR2(30)
SQL> explain plan for update /*+parallel (s,4)*/ big_tab s set object_name=lower(object_name);
已解释。
已用时间: 00: 00: 00.46
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3425284328
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 82514 | 1369K| 86 (0)| 00:00:01 | | | |
| 1 | UPDATE | BIG_TAB | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 82514 | 1369K| 86 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 82514 | 1369K| 86 (0)| 00:00:01 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| BIG_TAB | 82514 | 1369K| 86 (0)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
已选择16行。
已用时间: 00: 00: 00.05
SQL> alter session enable parallel dml;
ERROR:
ORA-12841: 无法变更事务处理中的会话并行 DML 状态
已用时间: 00: 00: 00.00
SQL> show user
USER 为 "TPCH"
SQL> disc
从 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
SQL> conn tpch/tpch
已连接。
SQL> alter session enable parallel dml;
会话已更改。
已用时间: 00: 00: 00.00
SQL> explain plan for update /*+parallel (s,4)*/ big_tab s set object_name=lower(object_name);
已解释。
已用时间: 00: 00: 00.02
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3465571411
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 82514 | 1369K| 86 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 82514 | 1369K| 86 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | UPDATE | BIG_TAB | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 82514 | 1369K| 86 (0)| 00:00:01 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| BIG_TAB | 82514 | 1369K| 86 (0)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
已选择16行。
已用时间: 00: 00: 00.04
SQL>