第一种情况：

表上没有索引，cbo自动选择 hash join

SQL> select count(*) from t;

COUNT(*)
----------
50371

SQL> select count(*) from t1;

COUNT(*)
----------
50

SQL> select * from t,t1 where t.object_id=t1.object_id;

已选择50行。

执行计划
----------------------------------------------------------
Plan hash value: 1444793974

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    50 | 17700 |   147   (5)| 00:00:02 |
|* 1 | HASH JOIN         |      |    50 | 17700 |   147   (5)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| T1   |    50 | 8850 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T    | 50473 | 8724K|   143   (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
        532 recursive calls
          0 db block gets
        827 consistent gets
          0 physical reads
          0 redo size
       6213 bytes sent via SQL*Net to client
        418 bytes received via SQL*Net from client
          5 SQL*Net roundtrips to/from client
          2 sorts (memory)
          0 sorts (disk)
         50 rows processed

SQL>

现在强制 cbo 走 nested loops

如果走nested loop 效率比较差，不论 use_nl 后面表的顺序与from字句后面的顺序是否相同，执行计划都一样

SQL> select /*+ use_nl(t t1) */ * from t1,t where t.object_id=t1.object_id;

已选择50行。

执行计划
----------------------------------------------------------
Plan hash value: 2196473728

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    50 | 17700 | 7077   (4)| 00:01:25 |
|   1 | NESTED LOOPS      |      |    50 | 17700 | 7077   (4)| 00:01:25 |
|   2 |   TABLE ACCESS FULL| T1   |    50 | 8850 |     2   (0)| 00:00:01 |
|* 3 |   TABLE ACCESS FULL| T    |     1 |   177 |   141   (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("T"."OBJECT_ID"="T1"."OBJECT_ID")

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      34861 consistent gets
          0 physical reads
          0 redo size
       6213 bytes sent via SQL*Net to client
        418 bytes received via SQL*Net from client
          5 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         50 rows processed

SQL> select /*+ use_nl(t1 t) */ * from t1,t where t.object_id=t1.object_id;

已选择50行。

执行计划
----------------------------------------------------------
Plan hash value: 2196473728

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    50 | 17700 | 7077   (4)| 00:01:25 |
|   1 | NESTED LOOPS      |      |    50 | 17700 | 7077   (4)| 00:01:25 |
|   2 |   TABLE ACCESS FULL| T1   |    50 | 8850 |     2   (0)| 00:00:01 |
|* 3 |   TABLE ACCESS FULL| T    |     1 |   177 |   141   (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("T"."OBJECT_ID"="T1"."OBJECT_ID")

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      34861 consistent gets
          0 physical reads
          0 redo size
       6213 bytes sent via SQL*Net to client
        418 bytes received via SQL*Net from client
          5 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         50 rows processed

第二种情况

两个表都有索引，cbo自动选择走 nested loops

首先创建了两个表 t、t1

create table t as select * from dba_objects;

create table t1 as select * from dba_objects where rownum<51;

SQL> select count(*) from t;
COUNT(*)
----------
50371

SQL> select count(*) from t1;
COUNT(*)
----------
50

然后在表 t 、t1上创建索引

SQL> alter table t modify object_id not null;
表已更改。

SQL> alter table t1 modify object_id not null;
表已更改。

SQL> create index idx_t on t(object_id);
索引已创建。

SQL> create index idx_t1 on t1(object_id);
索引已创建。
SQL>
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);
PL/SQL 过程已成功完成。
SQL>
SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);
PL/SQL 过程已成功完成。

SQL> select * from t,t1 where t.object_id=t1.object_id;

已选择50行。

执行计划
----------------------------------------------------------
Plan hash value: 2462202995

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

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

| 0 | SELECT STATEMENT | | 50 | 8200 | 102 (0)| 00:00:02 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 93 | 2 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 50 | 8200 | 102 (0)| 00:00:02 |

| 3 | TABLE ACCESS FULL | T1 | 50 | 3550 | 2 (0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN | IDX_T | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
         71 consistent gets
          0 physical reads
          0 redo size
       6213 bytes sent via SQL*Net to client
        418 bytes received via SQL*Net from client
          5 SQL*Net roundtrips to/from client
          0 sorts (memory)




因为 nested loops为相关联合型查询，首先执行3 ，对小表 t1 只进行一次全表扫描，然后返回一个结果集，该结果集包含50条记录。

然后从结果集中每次拿出一条记录在 t 表进行扫描，因为 t表上有索引 IDX_T , 所以就应用条件 "T"."OBJECT_ID"="T1"."OBJECT_ID"

进行索引范围扫描。索引范围扫描循环执行50次，因此个人认为 4 中的 rows列应为 50 。

从 t 表索引 IDX_T 中扫描得到 50个，然后 1 通过键值对应的 rowid 访问 t 表，一共要访问 50 条数据，因此个人认为 1 中的 rows 列应为 50 。

这种方法看到的计划只能理解为驱动表返回多少行，就循环多少次，所以他里面很多1，每次只能找到1行

可以通过下面的语句获取我想得到的结果

SQL> select /*+ gather_plan_statistics */ * from t,t1 where t.object_id=t1.object_id;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 25j7fws7h3zhd, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ * from t,t1 where t.object_id=t1.object_id
Plan hash value: 2462202995
----------------------------------------------------------------------------------------------------
| Id | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 | TABLE ACCESS BY INDEX ROWID | T     |      1 |      1 |     50 |00:00:00.01 |      71 |
|   2 |   NESTED LOOPS                |       |      1 |     50 |    101 |00:00:00.01 |      66 |
|   3 |    TABLE ACCESS FULL          | T1    |      1 |     50 |     50 |00:00:00.01 |       7 |
|* 4 |    INDEX RANGE SCAN           | IDX_T |     50 |      1 |     50 |00:00:00.01 |      59 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")

已选择20行。

starts 表示操作执行的次数

也可以通过 10046 分析

Rows     Row Source Operation
------- ---------------------------------------------------
     50 TABLE ACCESS BY INDEX ROWID T (cr=71 pr=0 pw=0 time=121 us)
    101   NESTED LOOPS (cr=66 pr=0 pw=0 time=8806 us)
     50    TABLE ACCESS FULL T1 (cr=7 pr=0 pw=0 time=203 us)
     50    INDEX RANGE SCAN IDX_T (cr=59 pr=0 pw=0 time=259 us)(object id 67167)

也可以验证执行了 50次，而 NESTED LOOPS 行中的 101 是前面两步的累计值，循环的次数取决于驱动表 t1

现在强制 cbo 走 hash join

leading 提示要求先访问t1表，也就是小表 t1 作为构造输入表

use_hash(t) 表示使用 hash join ，t表作为探测输入表

SQL> select /*+ leading(t1) use_hash(t) */ * from t1,t where t.object_id=t1.object_id;

已选择50行。

执行计划
----------------------------------------------------------
Plan hash value: 1444793974

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    50 | 8200 |   147   (5)| 00:00:02 |
|* 1 | HASH JOIN                   |      |    50 | 8200 |   147   (5)| 00:00:02 |
|   2 |   TABLE ACCESS FULL | T1 |    50 | 3550 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FUL L| T    | 50371 | 4574K|   143   (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
        704 consistent gets
          0 physical reads
          0 redo size
       6213 bytes sent via SQL*Net to client
        418 bytes received via SQL*Net from client
          5 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)

加入index提示，强制走 t、t1表的索引

SQL> select /*+ leading(t1) use_hash(t) index(t1) index(t) */ * from t1,t where t.object_id=t1.object_id;

已选择50行。

执行计划
----------------------------------------------------------
Plan hash value: 844093764

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

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

| 0 | SELECT STATEMENT | | 50 | 8200 | 887 (1)| 00:00:11 |

|* 1 | HASH JOIN | | 50 | 8200 | 887 (1)| 00:00:11 |

| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 50 | 3550 | 2 (0)| 00:00:01 |

| 3 | INDEX FULL SCAN | IDX_T1 | 50 | | 1 (0)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| T | 50371 | 4574K| 883 (1)| 00:00:11 |

| 5 | INDEX FULL SCAN | IDX_T | 50371 | | 114 (2)| 00:00:02 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        887 consistent gets
          0 physical reads
          0 redo size
       4745 bytes sent via SQL*Net to client
        418 bytes received via SQL*Net from client
          5 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         50 rows processed

SQL>

总结：

nested loops 适用场景：

1：有一个表比较小

2：被关联字段上有索引

3：索引键值重复率不应该很高

hash join 使用场景：

1：一个大表、一个小表

2：关联字段上没有索引（字段hash完以后，字段的搜索不再受索引影响，只收到hash列表效率的影响）

3：返回的结果集比较大

ITPub博客

nested loops 和hash join的一点测试

最新文章