Oraclenestloop

nested loop join 一般适和大小表，且大表有index，全表scan小表，然后按index抽取大表匹配记录，返会第一条记录快。




1.建立测试表




SQL> create table system.t1(id number(10),name varchar(10));


Table created.


SQL> begin
  2      for i in 1 .. 10000 loop      
  3               insert into system.t1(id,name) select trunc(dbms_random.value(0,10000)) ,'t1' from dual;
  4               commit;
  5      end loop;
  6  end;
  7  /




PL/SQL procedure successfully completed.


SQL> create index system.t1_ind on system.t1(id);


Index created.


SQL> create table system.t2(id number(10),name varchar(10));


Table created.


SQL> insert into system.t2 
  2  select 1 as id,'t2' as name from dual
  3  union
  4  select 2 as id,'t2' as name from dual
  5  union
  6  select 3 as id,'t2' as name from dual
  7  union
  8  select 4 as id,'t2' as name from dual
  9  union
 10  select 5 as id,'t2' as name from dual;


5 rows created.


SQL> commit;


Commit complete.




SQL> execute dbms_stats.gather_table_stats('SYSTEM','T1');


PL/SQL procedure successfully completed.


SQL> execute dbms_stats.gather_table_stats('SYSTEM','T2');


PL/SQL procedure successfully completed.




2.进行测试


SQL> select /*+ leading(t1) use_nl(t2)*/ * from t1,t2 where t1.id=t2.id  ;




Execution Plan
----------------------------------------------------------
Plan hash value: 1967407726


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     8 |   104 |  2721   (1)| 00:00:33 |
|   1 |  NESTED LOOPS      |      |     8 |   104 |  2721   (1)| 00:00:33 |
|   2 |   TABLE ACCESS FULL| T1   | 10000 | 70000 |     7   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     1 |     6 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------


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


   3 - filter("T1"."ID"="T2"."ID")




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      20024  consistent gets
          0  physical reads
          0  redo size
        799  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed




SQL> select /*+ leading(t2) use_nl(t1)*/ * from t1,t2 where t1.id=t2.id  ;




Execution Plan
----------------------------------------------------------
Plan hash value: 1634138066


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


| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Tim
e     |


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


|   0 | SELECT STATEMENT             |        |     8 |   104 |    17   (0)| 00:
00:01 |


|   1 |  NESTED LOOPS                |        |       |       |            |
      |


|   2 |   NESTED LOOPS               |        |     8 |   104 |    17   (0)| 00:
00:01 |


|   3 |    TABLE ACCESS FULL         | T2     |     5 |    30 |     2   (0)| 00:
00:01 |


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


|   5 |   TABLE ACCESS BY INDEX ROWID| T1     |     2 |    14 |     3   (0)| 00:
00:01 |


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




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


   4 - access("T1"."ID"="T2"."ID")




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        799  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed




当不同的表作为leading 表时，rows有明显的不同,第一个查询rows是 10000, 第二个rows是个位数
第一次查询先查T1表,第二次查询先查的T2表
我们总结一下. 应该对小表先查,然后根据小表的row到大表去走索引去查.这样最优.所以请将 小表作为 leading表