浅析驱动表和leading

首先大致讲一下个人对驱动表的理解：

驱动表（driving table/outer table）又称外层表,驱动表用于nested_loop join 和hash join,驱动表是用来驱动查询的，再CBO中，优化器会根据cost自动选择驱动表，并非根据表的顺序。

在RBO的优化模式下，对于两个表的操作中，RBO选择最右的表作为驱动表，对于三个或三个以上的表，RBO选择以从右至左的顺序处理表连接，也就是from中最右端的表作为驱动表。对于NESTED LOOPS，HASH JOIN，SORT MERGE JOIN方式，驱动表以小表的话查询速度会更快（更为确切的说，以经过where条件过滤后的结果集小的表作为驱动表），如果涉及到多个表进行关联的话，驱动表应该选择关联次数最多的表。

 

创建测试用户。

SQL> create user hint_test identified by hint_test;

User created.

SQL> grant dba to hint_test;

Grant succeeded.

SQL> conn hint_test/hint_test;

Connected.

 

创建测试表

SQL> create table t1 as select * from user_tables;

Table created.

SQL> create table t2 as select * from user_indexes;

Table created.

 

查看相对应的执行计划

SQL> select count(*) from t1,t2 where t1.table_name=t2.table_name;

Execution Plan

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

Plan hash value: 906334482

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

| Id  | Operation     | Name | Rows  | Bytes | Cost (%CPU)| Time    |

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

|   0 | SELECT STATEMENT    |    |  1 | 34 | 24   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE     |    |  1 | 34 | |    |

|*  2 |   HASH JOIN     |    |  1251 | 42534 | 24   (0)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| T1   |  1014 | 17238 | 11   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL| T2   |  1251 | 21267 | 13   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME")

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

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

157  recursive calls

  0  db block gets

230  consistent gets

 76  physical reads

  0  redo size

527  bytes sent via SQL*Net to client

524  bytes received via SQL*Net from client

  2  SQL*Net roundtrips to/from client

 12  sorts (memory)

  0  sorts (disk)

  1  rows processed

 

SQL> select count(*) from t2,t1 where t1.table_name=t2.table_name;

Execution Plan

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

Plan hash value: 906334482

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

| Id  | Operation     | Name | Rows  | Bytes | Cost (%CPU)| Time    |

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

|   0 | SELECT STATEMENT    |    |  1 | 34 | 24   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE     |    |  1 | 34 | |    |

|*  2 |   HASH JOIN     |    |  1251 | 42534 | 24   (0)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| T1   |  1014 | 17238 | 11   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL| T2   |  1251 | 21267 | 13   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME")

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

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

  7  recursive calls

  0  db block gets

166  consistent gets

  0  physical reads

  0  redo size

527  bytes sent via SQL*Net to client

524  bytes received via SQL*Net from client

  2  SQL*Net roundtrips to/from client

  2  sorts (memory)

  0  sorts (disk)

  1  rows processed

 

这时候我们发现oracle对这两条语句产生的执行计划是一样的，我们再查看下相对应的优化器是什么模式。

SQL> show parameter optimizer_mode;         

NAME      TYPE  VALUE

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

optimizer_mode      string  ALL_ROWS

 

优化器的模式包括Rule，Choose，First rows，All rows这四种模式,大致介绍下这四种模式：

 

Rule：基于规则的优化器（RBO优化方式）

Choose：指的是当一个表存在索引或者统计信息，则会走CBO的方式，如果一个表有索引，但是没有统计信息，表又不是很小，而且相对应的列有索引，那么就走索引，走的是RBO的方式。

First Rows：这种优化器的模式与Choose的优化差不多，但是有所不同的地方在于当我一个表存在统计信息的时候，他将以最快的速度返回查询的前几行，从总体上来说，减少了响应时间。

All Rows：这是我们所说的cost的方式。当我一个表存在统计信息的时候，它将会以最快的速度返回表中的所有行，从总体上提高查询的吞吐量，没有统计信息就会走则会走基于规则的方式。

 

所以这时候我们修改下优化器模式，再查看下执行计划。

SQL> alter system set optimizer_mode = 'Rule' scope=both;

System altered.

SQL> show parameter optimizer_mode;

NAME      TYPE  VALUE

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

optimizer_mode      string  RULE

 

SQL>  select count(*) from t1,t2 where t1.table_name=t2.table_name;

Execution Plan

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

Plan hash value: 1400713901

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

| Id  | Operation      | Name |

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

|   0 | SELECT STATEMENT     |     |

|   1 |  SORT AGGREGATE      |     |

|   2 |   MERGE JOIN      |     |

|   3 |    SORT JOIN      |     |

|   4 |     TABLE ACCESS FULL| T2   |

|*  5 |    SORT JOIN      |     |

|   6 |     TABLE ACCESS FULL| T1   |

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

Predicate Information (identified by operation id):

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

   5 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME")

       filter("T1"."TABLE_NAME"="T2"."TABLE_NAME")

Note

-----

   - rule based optimizer used (consider using cbo)

Statistics

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

  1  recursive calls

  0  db block gets

  0  consistent gets

  0  physical reads

  0  redo size

525  bytes sent via SQL*Net to client

524  bytes received via SQL*Net from client

  2  SQL*Net roundtrips to/from client

  1  sorts (memory)

  0  sorts (disk)

  1  rows processed

 

以上T2是驱动表

 

SQL> select count(*) from t2,t1 where t1.table_name=t2.table_name;

Execution Plan

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

Plan hash value: 2721375620

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

| Id  | Operation      | Name |

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

|   0 | SELECT STATEMENT     |     |

|   1 |  SORT AGGREGATE      |     |

|   2 |   MERGE JOIN      |     |

|   3 |    SORT JOIN      |     |

|   4 |     TABLE ACCESS FULL| T1   |

|*  5 |    SORT JOIN      |     |

|   6 |     TABLE ACCESS FULL| T2   |

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

Predicate Information (identified by operation id):

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

   5 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME")

       filter("T1"."TABLE_NAME"="T2"."TABLE_NAME")

Note

-----

   - rule based optimizer used (consider using cbo)

Statistics

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

  1  recursive calls

  0  db block gets

  0  consistent gets

  0  physical reads

  0  redo size

525  bytes sent via SQL*Net to client

524  bytes received via SQL*Net from client

  2  SQL*Net roundtrips to/from client

  1  sorts (memory)

  0  sorts (disk)

  1  rows processed

 

以上T1是驱动表

 

 

/*+

ordered

use_hash(t2)

use_hash(t3)

swap_join_inputs(t3)

use_hash(t4)

no_swap_join_inputs(t4)

*/

在oracle中改变表连接的hint有ordered和leading，ordered表示根据from后面写的表的顺序作为连接，以上面的hint为例分开写思路会清晰些。ordered后from t1,t2,t3,t4说明首先使用t1作为驱动表，使用hint use_hash(t2)代表连接t2的方式是hash join;然后用use_hash(t3)表示t3的方式是hash join，以swap_join_inputs(t3)代表t3做build表和t1-t2结果集做连接,依次类推。leading可以用来替换ordered,后面无需跟from，直接leading(t1,t2,t3,t4),也可以leading(t1)表示以t1作为驱动表

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30604784/viewspace-2132539/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30604784/viewspace-2132539/