昨天说了clob字段对于parallel dml的限制(http://space.itpub.net/195110/viewspace-745353).

那对于parallel ddl呢？

一起来验证吧：

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

5 rows selected.

SQL> create table test (id number,name clob);

Table created.

SQL> insert into test select object_id,object_name from dba_objects;

304441 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> explain plan for
2 create table test2 parallel 4 as
3 select * from test;

Explained.

SQL> @s_xplan

执行计划如下：
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1978863831

----------------------------------------------------------------------------------------------------------------
| Id | Operation              | Name     | Rows | Bytes | Cost (%CPU)| Time     |    TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT |          |   320K|   616M| 3768   (1)| 00:00:53 |        |      |         |
|   1 | LOAD AS SELECT        | TEST2    |       |       |            |          |        |      |         |
|   2 |   PX COORDINATOR       |          |       |       |            |          |        |      |         |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |   320K|   616M|   160   (1)| 00:00:03 | Q1,00 | P->S | QC (RAND) |
|   4 |     PX BLOCK ITERATOR |          |   320K|   616M|   160   (1)| 00:00:03 | Q1,00 | PCWC |         |
|   5 |      TABLE ACCESS FULL | TEST     |   320K|   616M|   160   (1)| 00:00:03 | Q1,00 | PCWP |         |
----------------------------------------------------------------------------------------------------------------

SQL> explain plan for
2 create index idx_test_1 on test(name) parallel 4;
create index idx_test_1 on test(name) parallel 4
*
ERROR at line 2:
ORA-02327: cannot create index on expression with datatype LOB

SQL> alter table test move parallel 2;

在另一个sessoin中：
SQL> select sid,SERIAL#,qcsid,qcserial# from v$px_session;

       SID    SERIAL#      QCSID QCSERIAL#
---------- ---------- ---------- ----------
      2073       8150       2833      48723
      2833      48723       2833
      2932      18603       2833      48723
可以parallel

不一一试了，包含clob字段的表对于parallel ddl应该没有限制.

ITPub博客

clob字段对于parallel ddl的限制

最新文章