驱动表 oracle - 锐客网

1.什么是驱动表？

通俗的讲就是先从哪个表开始检索啦,找到好的驱动表语句的优化就成功一半了
eg: select * from a,b where a.id = b.id and a.姓名 = '美格瑞恩' and b.性别 = '女'; 在a,b表同等数量级的情况下显然用a表做为驱动表比较好因为姓名相对于性别来说可以过滤掉更多的数据,所以想办法使你的执行计划扫描a表先再通过nest loop与b表关连比较理想
2.RBO和CBO
RBO中一般情况下是from后最后一张是驱动表，CBO中输出数据量最少的是驱动表！驱动表是基表，其他表是基于驱动表来嵌套。主要是调优时用的，考虑性能优化。
RBO - 1、基于规则的优化方式(Rule-Based Optimization，简称为RBO) 优化器
CBO - 1、基于代价的优化方式(Cost-Based Optimization，简称为CBO) 优化器
如果你是学DBA可能需要深入一些，如果你是技术开发，没有必要了解这么多。数据库是哪种优化器不是开发人员所能决定的。http://www.itpub.net/thread-144375-2-1.html

3.oracle如何选择驱动表

RBO或者CBO没有分析table的情况下，对于2个表的操作，FROM子句中，RBO选择最右的表作为驱动表(一般也就是from 中最后的表作为驱动表 )。所以对于NESTED LOOPS、HASH JOIN、SORT MERGE JOIN方式，驱动表选择较小的表 (放在from 最右端)，速度会更快；存在主、外键关系的表，由于主键由oracle自动建立索引，外键上最好也建索引，以避免全表扫描。而对于3个或以上table连接查询，对于FROM子句，RBO以从右到左的顺序处理表连接，也就是from 子句最右端table作为驱动表。我们常说，驱动表一定是小表，指的是根据条件获得的子集合一定要小，而不是说实体表本身一定要小，大表如果获得的子集合小，一样可以简称这个大表为驱动表。，最好选择与其他表的主键字段进行比较，或者与已经索引的字段进行比较，这样一来，就有意识地将业务需求的主表，作为驱动表处理了，Oracle也会在选择最优执行计划时，比较容易的找到驱动表。如果WHERE条件过于复杂，或者业务上获得信息的主表并不容易确定，我们可以根据业务的实际情况，评估关联各表的数据量和数据增长量，并分析关键条件字段的区分度，考虑在区分度高的字段，或者区分度高的组合字段上创建索引，以最大限度的降低某个表的结果集，增加其作为驱动表的机会。

补充RBO选择驱动表原则：rule 下所谓驱动表还有nested loopsandhashjoin之份
我们以 nestedloop 为例：如果两个表连接字段都没有索引（通常这个时候是 sortmerge /hashjoin），则驱动表会选择后者; 若两个表其中有一个有索引而另外一个没有索引，则驱动表是没有索引那一个，跟顺序无关; 若两个表都有索引，则驱动表为后面那一个表。所以事实上，RULE下，只有在两个表都存在连接字段的索引的情况下才需要考虑顺序问题，也就是小表放在后面大表放在前面(当然到底哪个好这实际上还跟符合条件的记录数、数据分布等因素相关！！！，所以应该以实际测试为准)
如果是 CBO 下则跟顺序无关驱动表选择http://blog.csdn.net/ys_565137671/article/details/6361730 4. oracle优化器的选择 http://www.itpub.net/thread-263395-1-1.html

优化模式包括 Rule、Choose、First rows、All rows 四种方式：
Rule：基于规则的方式。
Choolse：默认的情况下 Oracle 用的便是这种方式。指的是当一个表或或索引有统计信息，则走 CBO 的方式，如果表或索引没统计信息，表又不是特别的小，而且相应的列有索引时，那么就走索引，走 RBO 的方式。
First Rows：它与 Choose 方式是类似的，所不同的是当一个表有统计信息时，它将是以最快的方式返回查询的最先的几行，从总体上减少了响应时间。
All Rows:也就是我们所说的 Cost 的方式，当一个表有统计信息时，它将以最快的方式返回表的所有的行，从总体上提高查询的吞吐量。没有统计信息则走 RBO 的方式。
设定选用哪种优化模式：
A、Instance 级别我们可以通过在 initSID.ora 文件中设定 OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS 如果没设定 OPTIMIZER_MODE 参数则默认用的是 Choose 方式。
B、Sessions 级别通过 ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS 来设定。

oracle的 session、共享、缓冲池、日志备份目录，都是通过这个设置的。

[oracle@localhost oracleinstall]$ find . -type f -name "inityangkai.ora"
./oracle/product/db_yk/dbs/inityangkai.ora
[oracle@localhost oracleinstall]$ cat ./oracle/product/db_yk/dbs/inityangkai.ora
yangkai.__db_cache_size=176160768
yangkai.__java_pool_size=4194304
yangkai.__large_pool_size=4194304
yangkai.__shared_pool_size=96468992
yangkai.__streams_pool_size=0
*.audit_file_dest='/oracleinstall/oracle/admin/yangkai/adump'
*.background_dump_dest='/oracleinstall/oracle/admin/yangkai/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oracleinstall/oracle/oradata/yangkai/control01.ctl','/oracleinstall/oracle/oradata/yangkai/control02.ctl','/oracleinstall/oracle/oradata/yangkai/control03.ctl'
*.core_dump_dest='/oracleinstall/oracle/admin/yangkai/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='yangkai'
*.db_recovery_file_dest='/oracleinstall/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=yangkaiXDB)'
*.job_queue_processes=10
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracleinstall/oracle/admin/yangkai/udump'
[oracle@localhost oracleinstall]$

关键的是明白执行计划而不在于记什么规则。举例，表连接返回一条记录存在两个表，一个 10条记录，一个1000万条记录
若2表都存在连接字段索引，若以小表为驱动表，则代价： 10* (通过索引在大表查询一条记录的代价) ；若以大表为驱动表：1000万 * (通过索引在小表中查询一条记录的代价) 通过索引获取一条记录，10rows的表，代价通常在 3 blocks 。索引2块，表一块，而如果是1000万的表，索引可能达到4块表一块.

驱动表，又称为外层表，就是在嵌套循环连接和哈希连接中，用来最先获得数据，并以此表的数据为依据，逐步获得其他表的数据，直至最终查询到所有满足条件的数据的第一个表。排序合并连接由于不存在优先访问那张表的顺序问题，因此也没有驱动表的概念。值得注意的是，驱动表并不一定指的是表，而是一个满足条件的记录的集合，Oracle依据这个集合，与其他的表的数据进行连接，这个集合叫做ROW SOURCE,即数据行源，顾名思义，就是由某个表中满足条件的数据行，组成子集合后，再以此子集合作为连接其他表的数据来源。这个子集合，才是真正的驱动表，有时候为了简洁，直接将最先按照条件或得子集合的那张表叫做驱动表。我们常说，驱动表一定是小表，指的是根据条件获得的子集合一定要小，而不是说实体表本身一定要小，大表如果获得的子集合小，一样可以简称这个大表为驱动表。那么，究竟怎么写SQL,才能让Oracle按照我们的意愿，指定某个表作为驱动表呢？在采用RBO作为优化模式的情况下，SQL语句中表的书写顺序，以及WHERE条件的书写顺序非常重要，Oracle基本上按照这个顺序选择驱动表，但是在CBO模式下，Oracle会根据数据库中的统计信息，以及WHERE条件中的预估结果集，自己识别驱动表，如果统计信息不准确，或者我们的SQL写法不当，都会让CBO错误的选择驱动表。因此，在CBO模式下，表的顺序，WHERE条件的顺序，已不太重要，我们在写SQL时，非常重要的一点就是：根据业务逻辑，确定需要获得信息的主表，然后最大可能的在这个主表上增加限定条件，使得从该表上查询获得的数据最少，再根据这些数据上的字段，关联到其他表，在关联其他表时，

最好选择与其他表的主键字段进行比较，或者与已经索引的字段进行比较，这样一来，就有意识地将业务需求的主表，作为驱动表处理了，Oracle也会在选择最优执行计划时，比较容易的找到驱动表。如果WHERE条件过于复杂，或者业务上获得信息的主表并不容易确定，我们可以根据业务的实际情况，评估关联各表的数据量和数据增长量，并分析关键条件字段的区分度，考虑在区分度高的字段，或者区分度高的组合字段上创建索引，以最大限度的降低某个表的结果集，增加其作为驱动表的机会。此外，如果由于WHERE条件不太明确等因素，导致Oracle在选择执行计划时，可能错误的选择驱动表，我们可以添加提示，固定leading表，则Oracle会按照我们的指定要求选择驱动表
1、根据业务逻辑需求，有意识的给出驱动表的限定条件。
对于测试库的表Client_info和Staff_info两张表
例：业务需求，通过已知客户的客户号，查找该客户的联系业务员的姓名。
分析：这里已知条件为客户的客户号，很显然涉及到的主表是客户表，那么驱动表最好是client_info表，因为通过客户的客户号查询出来结果集以后，再根据这个结果集中的conta_agent,通过与staff_info的empno相等，直接利用staff_info的主键就可以快速获得数据，因此，就要千方百计限制通过客户的客户号查询出来的结果集。分析client_info表发现，这个字段为该表的主键，结果集本身就很小，因此SQL直接写成如下的方式即可：
Select b.emp_name from client_info a,staff_info b
Where a.clientno=:b1
And a.conta_agent=b.empno;
但是，万一很不幸，用户不是要求根据客户姓名来查找联系业务员，而是要求根据客户类型来查找来联系业务员姓名，这么一来，通过客户类型从client_info获得的数据量就很大，这个时候以client_info为驱动表就可能效率很不好，换作以staff_info表作为驱动的表的话，也需要全表扫描staff_info或者驱动数据，效率也不会很高。
这个时候，就需从业务的角度，分析能否尽可能的限定查询条件，例如查询某个客户类型下，特定证件号码的联系业务员（这种情况下，倾向staff_info表就作为驱动表）；或某个客户类型下，特定服务业务员的某类客户的联系业务员（倾向以client_info为驱动表），一方面使得用户的查询更有实际意义，另一方面也能减少最终查询到的数据量，提高SQL效率。这是需要和用户沟通，也是需要我们在写SQL时尽量考虑的地方。
Select b.emp_name from client_info a,staff_info b
Where a.client_type=:b1
And b.emp_idno=:b2
And a.conta_agent=b.empno; ---staff_info为驱动表

Select b.emp_name from client_info a,staff_info b
Where a.client_type=:b1
And a.serve_agent=:b2
And a.conta_agent=b.empno; ---client_info为驱动表
2、增加hint,强制某个表为驱动表
对于上面提到的情况，如果用户一定要求根据客户类型来查找联系业务员姓名，经分
析根据客户类型获得的数据结果集，可能比staff_info的业务员数据量要大得多，即使全表扫描staff_info表，效率也比先获得clinet_info的conta_agent,再查询联系业务员姓名要好的话，我们可以采用如下提示固定驱动表：
Select /*+ leading(b) full(b) use_hash(b,a)*/ emp_name from client_info a,staff_info b
Where a.client_type=:b1
And b.empno=a.conta_agent;
注意：分析思路如此，并不代表一定和实际的运行状况相同，SQL写好以后，最好看看执行计划，并带入实际的数据值进行测试验证。此外，示例仅为了说明问题，抛砖引玉，不一定与符合实际。驱动表的测试链接： http://space.itpub.net/10972173/viewspace-343059