关于ORACLE做HASH JOIN的问题

Question

如果把HASH JOIN时读入内存的小表叫做驱动表，那么在做HASH JOIN时，是否驱动表一定是做TABLE ACCESS FULL?

Answer 1

不一定对，也可读索引的吧。（未曾测试，不敢下结论）这里的小表，是个相对的概念。 比如A表有1KW，B表有50W，那么CBO选择B做驱动表。但是B表的记录数也不少，要根据数据以及索引情况判断是走全表扫描 

Answer 2

谢谢你的回复，我是发现我的SQL做HASH JOIN的时候第一张表（就是要读入内存的表）都是做HASH JOIN，有些疑问。不知道有没有谁知道详细点的。

Answer 3

当然不会全是做fts了。读入内存做hash处理小表也只是把需要的数据读入内存。
做个实验：
OPER@tl> create table test as select * from dba_objects;表已创建。OPER@tl> create table test2 as
  2  select * from dba_objects
  3  where rownum<=5000;表已创建。OPER@tl> create index ind_test on test(object_id);索引已创建。OPER@tl> create index ind_test2 on test2(object_id);索引已创建。OPER@tl> exec dbms_stats.gather_table_stats('OPER','TEST',cascade=>true)PL/SQL 过程已成功完成。OPER@tl> exec dbms_stats.gather_table_stats('OPER','TEST2',cascade=>true)PL/SQL 过程已成功完成。OPER@tl> set autot traceonly explain
OPER@tl> select a.*
  2  from test a,test2 b
  3  where a.object_id=b.object_id;执行计划
----------------------------------------------------------
Plan hash value: 2053579432-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |  5000 |   473K|   164   (2)| 00:00:02 |
|*  1 |  HASH JOIN            |           |  5000 |   473K|   164   (2)| 00:00:02 |
|   2 |   INDEX FAST FULL SCAN| IND_TEST2 |  5000 | 20000 |     4   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | TEST      | 50033 |  4544K|   159   (2)| 00:00:02 |
-----------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")OPER@tl> select a.*
  2  from test a,test2 b
  3  where a.object_id=b.object_id
  4  and b.object_id in(20,44,28,15);执行计划
----------------------------------------------------------
Plan hash value: 118016711-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |     4 |   388 |     8   (0)| 00:00:01 |
|*  1 |  HASH JOIN                    |           |     4 |   388 |     8   (0)| 00:00:01 |
|   2 |   INLIST ITERATOR             |           |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| TEST      |     4 |   372 |     6   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IND_TEST  |     4 |       |     5   (0)| 00:00:01 |
|   5 |   INLIST ITERATOR             |           |       |       |            |          |
|*  6 |    INDEX RANGE SCAN           | IND_TEST2 |     4 |    16 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
   4 - access("A"."OBJECT_ID"=15 OR "A"."OBJECT_ID"=20 OR "A"."OBJECT_ID"=28 OR
              "A"."OBJECT_ID"=44)
   6 - access("B"."OBJECT_ID"=15 OR "B"."OBJECT_ID"=20 OR "B"."OBJECT_ID"=28 OR
              "B"."OBJECT_ID"=44)OPER@tl>