前几天帮同事优化了一个SQL，主要是nested_loop与hash_join两种连接方式的效率问题。

原SQL写法如下：

   select t2.*, (select name from t1 where t1.id=t2.id) from t2

 

优化后的SQL写法如下：

  select t2.*,t1.name from t2  left join t1 on t1.id=t2.id

模拟数据环境：

SQL> conn scott/scott
Connected.
SQL> create table t1(id number(5),name varchar2(4));

Table created.

SQL> create table t2(id number(5),name varchar2(4));

Table created.

SQL> declare   
  2    begin
  3    for i in 1..1000 loop
  4    insert into t1 values(i,'aaaa');
  5    end loop;
  6    end;
  7    /

PL/SQL procedure successfully completed.

SQL> declare   
  2    begin
  3    for i in 1..1000 loop
  4    insert into t2 values(i,'bbbb');
  5    end loop;
  6    end;
  7    /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> create index t1_pk on t1(id);

Index created.

SQL> create index t2_pk on t2(id);

Index created.

SQL> begin
  2  DBMS_STATS.GATHER_TABLE_STATS
  3  (ownname=>'scott',
  4  tabname=>'t1',
  5  estimate_percent=>null,
  6  method_opt=>'FOR ALL COLUMNS SIZE 1');
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> begin
  2  DBMS_STATS.GATHER_TABLE_STATS
  3  (ownname=>'scott',
  4  tabname=>'t2',
  5  estimate_percent=>null,
  6  method_opt=>'FOR ALL COLUMNS SIZE 1');
  7  end;
  8  /

PL/SQL procedure successfully completed.

看看两个SQL的执行计划：

SQL> select t2.*, (select name from t1 where t1.id=t2.id) from t2;

1000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1222452721

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  1000 |  9000 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     9 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_PK |     1 |       |     1   (0)| 00:00:01 |
|   3 |  TABLE ACCESS FULL          | T2    |  1000 |  9000 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("T1"."ID"=:B1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        239  consistent gets
          0  physical reads
          0  redo size
      17877  bytes sent via SQL*Net to client
       1145  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL> select t2.*,t1.name from t2  left join t1 on t1.id=t2.id;

1000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1823443478

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1000 | 18000 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |  1000 | 18000 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |  1000 |  9000 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |  1000 |  9000 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("T2"."ID"="T1"."ID"(+))

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         79  consistent gets
          0  physical reads
          0  redo size
      17847  bytes sent via SQL*Net to client
       1145  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

从执行时间上看不出两个SQL差别，这是因为表的数据量少，而且取的数据也少。但是从统计信息上还是可以看出的，第一个SQL的consistent gets为239个，而第二个只有79个，可以看出第二个效率要高很多。

但是不是第一种写法就比第二种效率低呢，其实不一定。

我把表t1中的数据量加到10W条，再看看执行效果。

SQL> declare   
  2    begin
  3    for i in 1001..99999 loop
  4    insert into t1 values(i,'aaaa');
  5    end loop;
  6    end;
  7    /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select t2.*, (select name from t1 where t1.id=t2.id) from t2;

1000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1222452721

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  1000 |  9000 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    10 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_PK |     1 |       |     1   (0)| 00:00:01 |
|   3 |  TABLE ACCESS FULL          | T2    |  1000 |  9000 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("T1"."ID"=:B1)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        239  consistent gets
          0  physical reads
          0  redo size
      17877  bytes sent via SQL*Net to client
       1145  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL> select  t2.*,t1.name from t2  left join t1 on t1.id=t2.id;

1000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1426054487

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1000 | 19000 |    73   (3)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |  1000 | 19000 |    73   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T2   |  1000 |  9000 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   | 99999 |   976K|    69   (2)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("T1"."ID"(+)="T2"."ID")

Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
        326  consistent gets
          0  physical reads
          0  redo size
      17847  bytes sent via SQL*Net to client
       1145  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

由上面可以看到，第一个SQL的consistent gets为239，比第二个SQL的326小。

可以看到如果两个表所用数据量相差较大的时候，用nested_loop 比hash_join的效率要高，但如果所用数据量相差较小的时候，还是hash_join效率高。