非分区表是否可以使用并行DML问题

vincent

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

SQL> SELECT t.PARTITIONED FROM user_tables t WHERE table_name ='CARD_SEGMENT_18';

PAR
---
NO

SQL> set timing on;
SQL> UPDATE /*+parallel(CT,8)*/CARD_SEGMENT_18 CT
  2     SET (CT.DEPOT_ID, CT.AREA_ID, CT.COUNTY_ID) =
  3         (SELECT TS.DEPART_CODE, TS.CITY_CODE, TS.COUNTY_CODE
  4            FROM TAB_STORE_2_DEPT_CODE TS
  5           WHERE CT.DEPOT_ID = TS.OLD_STORE_CODE
  6             AND CT.PROVINCE_ID = TS.PROV_CODE
  7             AND CT.AREA_ID = TS.CITY_CODE)
  8   WHERE EXISTS (SELECT 1
          FROM TAB_STORE_2_DEPT_CODE TS
         WHERE CT.DEPOT_ID = TS.OLD_STORE_CODE
  9   10   11             AND CT.PROVINCE_ID = TS.PROV_CODE
12             AND CT.AREA_ID = TS.CITY_CODE)
13     AND EXISTS (SELECT 1
14            FROM PROVINCE_CONFIG_GJ PCG
15           WHERE CT.PROVINCE_ID = PCG.PROVINCE_ID
16             AND CT.AREA_ID = PCG.AREAID
17             AND PCG.FLAG = '1')
18     AND CT.PROVINCE_ID = 18;

3713609 rows updated.

Elapsed: 00:06:09.24
SQL> rollback;     

Rollback complete.

Elapsed: 00:02:57.49
SQL> alter session enable parallel dml;

Session altered.

Elapsed: 00:00:00.00
SQL> UPDATE /*+parallel(CT,8)*/CARD_SEGMENT_18 CT
  2     SET (CT.DEPOT_ID, CT.AREA_ID, CT.COUNTY_ID) =
  3         (SELECT TS.DEPART_CODE, TS.CITY_CODE, TS.COUNTY_CODE
  4            FROM TAB_STORE_2_DEPT_CODE TS
         WHERE CT.DEPOT_ID = TS.OLD_STORE_CODE
  5    6             AND CT.PROVINCE_ID = TS.PROV_CODE
  7             AND CT.AREA_ID = TS.CITY_CODE)
  8   WHERE EXISTS (SELECT 1
  9            FROM TAB_STORE_2_DEPT_CODE TS
10           WHERE CT.DEPOT_ID = TS.OLD_STORE_CODE
11             AND CT.PROVINCE_ID = TS.PROV_CODE
12             AND CT.AREA_ID = TS.CITY_CODE)
13     AND EXISTS (SELECT 1
          FROM PROVINCE_CONFIG_GJ PCG
         WHERE CT.PROVINCE_ID = PCG.PROVINCE_ID
14   15   16             AND CT.AREA_ID = PCG.AREAID
17             AND PCG.FLAG = '1')
18     AND CT.PROVINCE_ID = 18;

3713609 rows updated.

Elapsed: 00:01:09.41
SQL> rollback;

Rollback complete.

Elapsed: 00:00:31.74
SQL> UPDATE CARD_SEGMENT_18 CT
  2     SET (CT.DEPOT_ID, CT.AREA_ID, CT.COUNTY_ID) =
       (SELECT TS.DEPART_CODE, TS.CITY_CODE, TS.COUNTY_CODE
          FROM TAB_STORE_2_DEPT_CODE TS
  3    4    5           WHERE CT.DEPOT_ID = TS.OLD_STORE_CODE
  6             AND CT.PROVINCE_ID = TS.PROV_CODE
  7             AND CT.AREA_ID = TS.CITY_CODE)
  8   WHERE EXISTS (SELECT 1
  9            FROM TAB_STORE_2_DEPT_CODE TS
10           WHERE CT.DEPOT_ID = TS.OLD_STORE_CODE
11             AND CT.PROVINCE_ID = TS.PROV_CODE
12             AND CT.AREA_ID = TS.CITY_CODE)
13     AND EXISTS (SELECT 1
          FROM PROVINCE_CONFIG_GJ PCG
         WHERE CT.PROVINCE_ID = PCG.PROVINCE_ID
           AND CT.AREA_ID = PCG.AREAID
14   15   16   17             AND PCG.FLAG = '1')
   AND CT.PROVINCE_ID = 18 18  ;

3713609 rows updated.

Elapsed: 00:02:24.43

非分区表使用并行dml可以提高效率，请刘大看看~