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表
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表
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7569309/viewspace-2135045/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7569309/viewspace-2135045/