FROMHZCZRK_JBXXB JB,HZCZRK_ZPXXB ZP

WHEREJB.RYID = ZP.RYI;

Oracle在执行该语句时，将根据该表的并行度(DOP)定义或者根据语句的并行处理HINT自动从并行处理缓冲池中分配相应数量的并行过程进行并行操作，并行处理协调器(Parallel Execution Coordinator)自动将各并行子进程处理的结果合并返回给客户，并释放并行子进程返回到缓冲池中。

执行计划如下：

----------------------------------------------------------

Plan hashvalue: 993606438

--------------------------------------------------------------------------------

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

|

--------------------------------------------------------------------------------

---

| 0 | SELECT STATEMENT | | 4714 | 478K| 3050 (1)| 00:00:3

7 |

|* 1 | HASH JOIN | | 4714 | 478K| 3050 (1)| 00:00:3

7 |

| 2 | TABLE ACCESS FULL| HZCZRK_JBXXB | 4713 | 405K| 30 (0)| 00:00:0

1 |

| 3 | TABLE ACCESS FULL| HZCZRK_ZPXXB | 17999 | 281K| 3019 (1)| 00:00:3

7 |

--------------------------------------------------------------------------------

--

PredicateInformation (identified by operation id):

---------------------------------------------------

1 -access("JB"."RYID"="ZP"."RYID")

Note

-----

- dynamic sampling used for this statement(level=2)

统计信息

----------------------------------------------------------

58 recursive calls

1 db block gets

25467 consistent gets

0 physical reads

240 redo size

557645 bytes sent via SQL*Net to client

3627 bytes received via SQL*Net from client

263 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3924 rows processed

l 在对分区表进行处理时，Oracle会自动为每个分区分配一个进程，从而达到并行处理的目的。不仅分区之间可以并行处理，而且Oracle会自动根据资源使用情况和数据分布情况，自动在分区内部进行并行处理，因此可进一步提高大批量数据处理的吞吐量。

SELECT/*+PARALLER_INDEX(oi,ghoi_ix,8)*/ JB.RYID, JB.GMSFHM,JB.XM, JB.XB, JB.CSRQ,ZP.ZP

FROMHZCZRK_JBXXB JB,HZCZRK_ZPXXB ZP

WHERE JB.RYID = ZP.RYID;

语句的执行计划如下：

----------------------------------------------------------

Plan hashvalue: 993606438

--------------------------------------------------------------------------------

-----------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|

Time |

--------------------------------------------------------------------------------

-----------

| 0 | SELECT STATEMENT | | 458K| 45M| | 38437 (1)|

00:07:42 |

|* 1 | HASH JOIN | | 458K| 45M| 43M| 38437 (1)|

00:07:42 |

| 2 | TABLE ACCESS FULL| HZCZRK_JBXXB | 458K| 38M| | 323 (5)|

00:00:04 |

| 3 | TABLE ACCESS FULL| HZCZRK_ZPXXB | 2439K| 37M| | 32674 (1)|

00:06:33 |

--------------------------------------------------------------------------------

-----------

PredicateInformation (identified by operation id):

---------------------------------------------------

1 - access("JB"."RYID"="ZP"."RYID")

Note

-----

- dynamic sampling used for this statement(level=2)

统计信息

----------------------------------------------------------

52 recursive calls

3 db block gets

265779 consistent gets

224230 physical reads

528 redo size

5738732 bytes sent via SQL*Net to client

30236 bytes received via SQL*Net from client

2682 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

40200 rows processed

16. 表空间迁移技术(Transportable Tablespace)

Transportable Tablespace技术是所有Oracle ETL工具中速度最快的！

17. 尝试物化表视图技术与语句重写技术