今天发现包含clob字段的表不能parallel insert

实验如下：

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_lock (id number);

Table created.

SQL> alter session enable parallel dml;

Session altered.

SQL> explain plan for insert /*+ parallel(test_lock,4) */ into test_lock select object_id from user_objects;

Explained.

SQL> @s_xplan

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

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name         | Rows | Bytes | Cost (%CPU)| Time     |    TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                     |              | 5307 | 68991 | 1792   (2)| 00:00:22 |        |      |            |
|   1 | PX COORDINATOR                      |              |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)                | :TQ10001     | 5307 | 68991 | 1792   (2)| 00:00:22 | Q1,01 | P->S | QC (RAND) |
|   3 |    LOAD AS SELECT                    | TEST_LOCK    |       |       |            |          | Q1,01 | PCWP |            |
|   4 |     BUFFER SORT                      |              |       |       |            |          | Q1,01 | PCWC |            |
|   5 |      PX RECEIVE                      |              | 5307 | 68991 | 1792   (2)| 00:00:22 | Q1,01 | PCWP |            |
|   6 |       PX SEND ROUND-ROBIN            | :TQ10000     | 5307 | 68991 | 1792   (2)| 00:00:22 |        | S->P | RND-ROBIN |
|   7 |        VIEW                          | USER_OBJECTS | 5307 | 68991 | 1792   (2)| 00:00:22 |        |      |            |
|   8 |         UNION-ALL                    |              |       |       |            |          |        |      |            |
|* 9 |          FILTER                      |              |       |       |            |          |        |      |            |
|* 10 |           TABLE ACCESS FULL          | OBJ$         | 6197 |   205K| 1790   (2)| 00:00:22 |        |      |            |
|* 11 |           TABLE ACCESS BY INDEX ROWID| IND$         |     1 |     9 |     2   (0)| 00:00:01 |        |      |            |
|* 12 |            INDEX UNIQUE SCAN         | I_IND1       |     1 |       |     1   (0)| 00:00:01 |        |      |            |
|* 13 |          INDEX RANGE SCAN            | I_LINK1      |     8 |    32 |     2   (0)| 00:00:01 |        |      |            |
----------------------------------------------------------------------------------------------------------------------------------

SQL>

SQL> alter table test_lock add text clob;

Table altered.

SQL> explain plan for insert /*+ parallel(test_lock,4) */ into test_lock (id) select object_id from user_objects;

Explained.

SQL> @s_xplan

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

-----------------------------------------------------------------------------------------------
| Id | Operation                      | Name         | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |              | 5307 | 68991 | 1792   (2)| 00:00:22 |
|   1 | VIEW                          | USER_OBJECTS | 5307 | 68991 | 1792   (2)| 00:00:22 |
|   2 |   UNION-ALL                    |              |       |       |            |          |
|* 3 |    FILTER                      |              |       |       |            |          |
|* 4 |     TABLE ACCESS FULL          | OBJ$         | 6197 |   205K| 1790   (2)| 00:00:22 |
|* 5 |     TABLE ACCESS BY INDEX ROWID| IND$         |     1 |     9 |     2   (0)| 00:00:01 |
|* 6 |      INDEX UNIQUE SCAN         | I_IND1       |     1 |       |     1   (0)| 00:00:01 |
|* 7 |    INDEX RANGE SCAN            | I_LINK1      |     8 |    32 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

SQL>

计划中没有px了．

SQL> explain plan for update /*+ parallel(test_lock,4) */ test_lock set id = id+100;

Explained.

SQL> @s_xplan

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

----------------------------------------------------------------------------------------------------------------
| Id | Operation             | Name      | Rows | Bytes | Cost (%CPU)| Time     |    TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |           |    30 |   390 |     3   (0)| 00:00:01 |        |      |         |
|   1 | PX COORDINATOR       |           |       |       |            |          |        |      |         |
|   2 |   PX SEND QC (RANDOM) | :TQ10000 |    30 |   390 |     3   (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
|   3 |    UPDATE             | TEST_LOCK |       |       |            |          | Q1,00 | PCWP |         |
|   4 |     PX BLOCK ITERATOR |           |    30 |   390 |     3   (0)| 00:00:01 | Q1,00 | PCWC |         |
|   5 |      TABLE ACCESS FULL| TEST_LOCK |    30 |   390 |     3   (0)| 00:00:01 | Q1,00 | PCWP |         |
----------------------------------------------------------------------------------------------------------------

是可以parallel．注意这时test_lock是有clob字段的．

总结：一个表是否可以进行paralle dml是有很多限制的，这种限制需要大家来总结．
　　　从上面实验可以看到：
　　　　　对于有clob字段的表：parallel insert 是禁止的；parallel update 确是可以的．(我想对于blob应该也时一样的)

另外：
<<让Oracle跑得更快>>的书中提到：对于Delete\update\Merge的操作，只有操作的对象是分区表时，Oracle才会启用并行操作．
通过上面的测试后，parallel update 对于普通的表也时可以并行的．

ITPub博客

clob字段对于parallel dml的限制

最新文章