本次测试数据库两种连接类型哈希连接与嵌套连接跟表的顺序的关系；
1.1 测试的方法
构造两个数量级别悬殊的两个堆栈比表，通过hint来执行，实现不同的表的连接顺序，检查相应的执行计划；
1.2 测试的环境
1、表的信息：
表A1：100万条记录；
表A2：10条记录；
2、执行的语句：
select * from wu.test4 b ,wu.test2 a where b.object_id=a.object_id
3、以上两个表均收集了统计信息；
1、 使用A1（大表）做构造表，A2（小表）做探测表；（数据库先执行A1,然后执行A2）
2、 使用A2做构造表，A1做探测表；（数据库先执行A2,然后执行A1）
2.1 测试1的执行计划
PLAN_TABLE_OUTPUT
Plan hash value: 3406100614
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 174 | 17748 | | 10339 (1)| 00:02:05 |
|* 1 | HASH JOIN | | 174 | 17748 | 118M| 10339 (1)| 00:02:05 |
| 2 | TABLE ACCESS FULL| A1 | 1133K| 105M| | 4429 (1)| 00:00:54 |
| 3 | TABLE ACCESS FULL| A2 | 11 | 44 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
access("A1"."OBJECT_ID"="A2"."OBJECT_ID")
总结耗时：125S
2.2 测试2的执行计划
PLAN_TABLE_OUTPUT
Plan
hash value: 1713954154
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 174 | 17748 | 4439 (1)| 00:00:54 |
|* 1 | HASH JOIN | | 174 | 17748 | 4439 (1)| 00:00:54 |
| 2 | TABLE ACCESS FULL| A2 | 11 | 44 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| A1 | 1133K| 105M| 4429 (1)| 00:00:54 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
access("A1"."OBJECT_ID"="A2"."OBJECT_ID")
总结总耗时：54S
3、 使用A1（大表）做构造表，A2（小表）做探测表；（数据库先执行A1,然后执行A2）
4、 使用A2做构造表，A1做探测表；（数据库先执行A2,然后执行A1）
3.1 测试1的执行计划
脚本：如下
EXPLAIN PLAN FOR
select /*+leading(A2 A1) use_Nl(A1) use_Nl(A2)*/*
from A1,A2
WHERE A1.OBJECT_ID=A2.OBJECT_ID
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 176 | 19712 | 51589 (2)| 00:10:20 |
| 1 | NESTED LOOPS | | 176 | 19712 | 51589 (2)| 00:10:20 |
| 2 | TABLE ACCESS FULL| A2 | 11 | 154 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| A1 | 16 | 1568 | 4690 (2)| 00:00:57 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A1"."OBJECT_ID"="A2"."OBJECT_ID")
总结：耗时620S
3.2 测试2的执行计划
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 176 | 19712 | 316K (1)| 01:03:17 |
| 1 | NESTED LOOPS | | 176 | 19712 | 316K (1)| 01:03:17 |
| 2 | TABLE ACCESS FULL| A1 | 1147K| 107M| 4691 (2)| 00:00:57 |
|* 3 | TABLE ACCESS FULL| A2 | 1 | 14 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
3 - filter("A1"."OBJECT_ID"="A2"."OBJECT_ID")
总结：耗时3600S以上；
1、 无论进行哈希连接还是嵌套循环连接的时候，选择结果小的表都要先执行；
2、 连接条件没有选择性的时候，哈希连接的速度会比嵌套循环连接的速度快，因为哈希连接只要运行一次就可以了，而嵌套循环连接需要运行多次（由外表的结果决定）
相关原理: 1． 一张小表被hash在内存中。因为数据量小，所以这张小表的大多数数据已经驻入在内存中，剩下的少量数据被放置在临时表空间中；
2． 每读取大表的一条记录，就和小表中内存中的数据进行比较，如果符合，则立即输出数据（也就是说没有读取临时表空间中的小表的数据）。而如果大表的数据与小表中临时表空间的数据相符合，则不直接输出，而是也被存储临时表空间中。
3． 当大表的所有数据都读取完毕，将临时表空间中的数据以其输出。 如果小表的数据量足够小（小于hash area size），那所有数据就都在内存中了，可以避免对临时表空间的读写。