nested loops以选择驱动表实验

     如果两表都没有索引，那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