1. 查看具体语句的执行计划及消耗的内存/CPU量
当前两张表中各有数据1508875条
原始语句为：
SELECT/*+rule*/ JB.RYID, JB.GMSFHM,JB.XM, JB.XB, JB.CSRQ, ZP.ZP
FROMHZCZRK_JBXXB JB,HZCZRK_ZPXXB ZP
WHEREJB.RYID = ZP.RYID
AND ( JB.RYID>= 1 AND JB.RYID < 10001)
采用以下方式可以获得该语句的执行计划：
执行计划
----------------------------------------------------------
Plan hashvalue: 2567408823
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | SORT JOIN | |
| 3 | TABLE ACCESS FULL| HZCZRK_ZPXXB |
|* 4 | SORT JOIN | |
|* 5 | TABLE ACCESS FULL| HZCZRK_JBXXB |
--------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
4 -access("JB"."RYID"="ZP"."RYID")
filter("JB"."RYID"="ZP"."RYID")
5 -filter("JB"."RYID"<10001 AND"JB"."RYID">=1)
Note
-----
- rule based optimizer used (consider usingcbo)
统计信息
----------------------------------------------------------
2 recursive calls
1 db block gets
544 consistent gets
0 physical reads
176 redo size
11999 bytes sent via SQL*Net to client
811 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
79 rows processed
如果不设置总行数，则执行计划是：
执行计划
----------------------------------------------------------
Plan hashvalue: 2567408823
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | SORT JOIN | |
| 3 | TABLE ACCESS FULL| HZCZRK_ZPXXB |
|* 4 | SORT JOIN | |
| 5 | TABLE ACCESS FULL| HZCZRK_JBXXB |
--------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
4 - access("JB"."RYID"="ZP"."RYID")
filter("JB"."RYID"="ZP"."RYID")
Note
-----
- rule based optimizer used (consider usingcbo)
统计信息
----------------------------------------------------------
2 recursive calls
1 db block gets
1103 consistent gets
0 physical reads
176 redo size
23915 bytes sent via SQL*Net to client
866 bytes received via SQL*Net from client
12 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts(disk)
164 rows processed
这里我们主要需要关注两个点：consistent gets代表内存消耗，physical reads代表磁盘I/O消耗，单位都是数据块(DB_BLOCK_SIZE)。
从上面的执行计划可以看出表之间的连接关系基本上就是采用排序合并连接技术，所以下面对连接技术做出一些说明
适合于大批量数据处理的连接技术只有两种：
1． 排序合并连接(Sort/Merge)技术
两个表先按连接字段进行排序，再将两个表的排序结果进行顺序匹配，将合并结果返回给客户。
2． 哈希连接(HASH)技术
A hashjoin is executed as follows:
Bothtables are split into as many partitions as required, using a full table scan.
For eachpartition pair, a hash table is built in memory on the smallest partition.
The otherpartition is used to probe the hash table.
两种技术都适合于大表与大表的查询，而且通常情况下，HASH优于Merge，更优于嵌套循环(Nested_Loop)连接技术，尤其是当HASH与Oracle并行处理技术相结合的情况下，将极大地提高系统的整体吞吐量。
2. 对两张表做一个分析
由于从Oracle10G开始已经不采用基于规则优先的算法，而是交由RBO来进行，所以需要在优化前对表进行分析
execute dbms_stats.gather_table_stats(ownname =>'SYSTEM',tabname => 'HZCZRK_JBXXB' ,estimate_percent => null ,method_opt=> 'for all indexed columns' ,cascade => true);
分析结果可以在这里看：
selecta.owner,a.segment_name,a.segment_type,a.tablespace_name,round(a.bytes/1024/1024/1024,2)tablesize,b.last_analyzed
from dba_segments a,dba_tables b
wherea.segment_name=b.table_name；
看起来表一切正常。