clob欄位對於parallel ddl的限制

昨天說了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應該沒有限制.