如果两表都没有索引,那nested loops如何选择驱动表呢?下面我们来做个实验
SQL> create table t_xiao(a1 number(10));
SQL> create table t_da(a2 number(10));SQL> begin
2 for i in 1 .. 10 loop
3 insert into t_xiao values(i);
4 end loop;
5 commit;
6 end;
7 /
SQL> begin
2 for i in 1 .. 100000 loop
3 insert into t_da values(i);
4 end loop;
5 commit;
6 end;
7 /
SQL> exec dbms_stats.gather_table_stats(user,'t_xiao');
SQL> exec dbms_stats.gather_table_stats(user,'t_da');
SQL> select /*+leading(t1,t2) use_nl(t1,t2)*/ * from t_xiao t1,t_da t2 where t1.a1 = t2.a2;
已选择10行。
执行计划
----------------------------------------------------------
Plan hash value: 241767964
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 426 (7)| 00:00:06 |
| 1 | NESTED LOOPS | | 1 | 7 | 426 (7)| 00:00:06 |
| 2 | TABLE ACCESS FULL| T_XIAO | 10 | 30 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T_DA | 1 | 4 | 42 (5)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."A1"="T2"."A2")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1849 consistent gets
0 physical reads
0 redo size
587 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> select /*+leading(t2,t1) use_nl(t2,t1)*/ * from t_xiao t1,t_da t2 where t1.a1 = t2.a2;
已选择10行。
执行计划
----------------------------------------------------------
Plan hash value: 1273388556
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 110K (1)| 00:22:01 |
| 1 | NESTED LOOPS | | 1 | 7 | 110K (1)| 00:22:01 |
| 2 | TABLE ACCESS FULL| T_DA | 100K| 390K| 44 (7)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T_XIAO | 1 | 3 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."A1"="T2"."A2")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
700186 consistent gets
0 physical reads
0 redo size
587 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> select s.segment_name,s.blocks from user_segments s where s.segment_name in('T_XIAO','T_DA');
SEGMENT_NAME BLOCKS
--------------------------------------------------------------------------------- ----------
T_DA 256
T_XIAO 8
T_XIAO为驱动表:8 + 10*256 = 2568
T_DA为驱动表:256 + 100000*8 = 800256