深入理解Oracle表(2):驱动表的那些事儿
http://blog.csdn.net/linwaterbin/article/details/8544601
以
一个比较两本字典来做例子:
一本字典有索引目录(dict a), 一本没有(dict b)
现在要找出所有a开头的单词的异同
那么比较的时候,你会怎么比较?
合理的做法应该是以没有索引的字典dict b开始, 找到a开头的那一页, 然后对于每个单词, 通过使用索引在dict a中找到相应的条目
如果反过来, 效率会相同吗?
驱动表,通俗的讲就是先从哪个表开始检索,良好的驱动表是成功优化的一半
例如:
select * from a,b where a.id = b.id and a.姓名 = '美格瑞恩' and b.性别 = '女';
在a,b表同等数量级的情况下,显然用a表做为驱动表比较好
因为姓名相对于性别来说可以过滤掉更多的数据
所以想办法使你的执行计划先扫描a表再通过nest loop与b表关连比较理想
一般而言,2个表的时候,选择小表;3个或者以上的时候,选择关联比较多的表作为驱动表
不过,事无绝对,看个案例先:
有两个表T1和T2,可以假设T1存放姓名、ID这些基本信息,每行较短
而T2也有ID列和一些说明、备注类信息,每行长的可能有一两千个字节
对于这两个表,使用嵌套循环连接,T1表占的块数比较少,是小表
而T2表占的块数几倍于T1,是大表。那么,应该T1做驱动表更好一些,但实际却是T2大表做驱动表更好
一个不知道DBA还是开发,不知道这一点,为了强制小表T1做驱动表,写了类如下语句:
select /*+ ordered use_nl(t1,t2) */ from t2,t1 where t1.id=t2.id;
后来这个语句造成了一些性能问题。通过了解嵌套循环的工作方式,发现这里的确是大表做驱动表好一些
这里就说明了一个问题,都说嵌套循环要小表做驱动表,如果不知道这是为什么,这很容易造成人云亦云
这样只知其表,而不知其里,很容易影响以后在某些问题上做出的判断
驱动表(driving table/outer table)又称为外层表,驱动表仅仅用于nested_loop join 和 hash join
驱动表是用来驱动查询的
在CBO中,优化器会根据cost自动选择驱动表,与表的顺序无关
通常情况下,驱动表的选择性较高(该列唯一键与列的比值较高),where中的限制条件较多,返回的行数较少的表适合做驱动表
事实上,大表有时候也会做驱动表,只要在大表上面限制条件较多,从大表上面返回的行数较少的话,大表也适合做驱动表
始终要记住的就是返回的结果集(某个表被过滤后的数据,或者某些表join 产生的数据)较小的表适合做驱动表
也就是说,哪个返回数据少 ,哪个就做驱动表,即连接操作应从返回较少行上驱动
我们在看执行计划的时候,离nested loops,hash join 近的表就是驱动表,也就是说执行计划中靠上的表是驱动表
以下是几个快速判断驱动表的经验,可能有错哦:
(1)用于连接的子句的列应被索引、在Where子句中应尽量利用索引,而不是避开索引
(2)连接操作应从返回较少行上驱动
(3)如果所连接的表A和B,A表长度远远大于B表,建议从较大的A表上驱动
(4)如果Where子句中含有选择性条件,Where No=20,将最具有选择性部分放在表达式最后
(5)如果只有一个表有索引,另一表无索引,无索引的表通常作为驱动表。如A表的No列以被索引
而B表的No列没被索引,则应当B表作为驱动表,A表作为被驱动表
RULE 下所谓驱动表
我们以 nested loop 为例:
如果两个表连接字段都没有索引(通常这个时候是 sort merge / hash join),则驱动表会选择后者
若两个表其中有一个有索引而另外一个没有索引,则驱动表是没有索引那一个,跟顺序无关
若两个表都有索引,则驱动表为 后面 那一个表
所以事实上,RULE下,只有在两个表都存在连接字段的索引的情况下才需要考虑顺序问题
也就是小表放在后面大表放在前面(当然到底哪个好这实际上还跟符合条件的记录数、数据分布等因素相关!,所以应该以实际测试为准)
而如果是 CBO 下,则跟顺序无关
关键的是明白执行计划,而不在于记住什么规则
例如,表连接返回一条记录
存在2张表,一个10条记录,一个1000万条记录
若2表都存在连接字段索引,
以小表为驱动表,则代价为:
10*(通过索引在大表查询一条记录的代价)
以大表为驱动表,则代价为:
1000万*(通过索引在小表查询一条记录的代价)
通过索引获取一条记录,10rows的表,代价通常在3 blocks,索引2块,表1块
而如果是1000万的表,索引可能达到4块,表1块,速度·掰着脚趾头都知道了哈
一本字典有索引目录(dict a), 一本没有(dict b)
现在要找出所有a开头的单词的异同
那么比较的时候,你会怎么比较?
合理的做法应该是以没有索引的字典dict b开始, 找到a开头的那一页, 然后对于每个单词, 通过使用索引在dict a中找到相应的条目
如果反过来, 效率会相同吗?
驱动表,通俗的讲就是先从哪个表开始检索,良好的驱动表是成功优化的一半
例如:
select * from a,b where a.id = b.id and a.姓名 = '美格瑞恩' and b.性别 = '女';
在a,b表同等数量级的情况下,显然用a表做为驱动表比较好
因为姓名相对于性别来说可以过滤掉更多的数据
所以想办法使你的执行计划先扫描a表再通过nest loop与b表关连比较理想
一般而言,2个表的时候,选择小表;3个或者以上的时候,选择关联比较多的表作为驱动表
不过,事无绝对,看个案例先:
有两个表T1和T2,可以假设T1存放姓名、ID这些基本信息,每行较短
而T2也有ID列和一些说明、备注类信息,每行长的可能有一两千个字节
对于这两个表,使用嵌套循环连接,T1表占的块数比较少,是小表
而T2表占的块数几倍于T1,是大表。那么,应该T1做驱动表更好一些,但实际却是T2大表做驱动表更好
一个不知道DBA还是开发,不知道这一点,为了强制小表T1做驱动表,写了类如下语句:
select /*+ ordered use_nl(t1,t2) */ from t2,t1 where t1.id=t2.id;
后来这个语句造成了一些性能问题。通过了解嵌套循环的工作方式,发现这里的确是大表做驱动表好一些
这里就说明了一个问题,都说嵌套循环要小表做驱动表,如果不知道这是为什么,这很容易造成人云亦云
这样只知其表,而不知其里,很容易影响以后在某些问题上做出的判断
驱动表(driving table/outer table)又称为外层表,驱动表仅仅用于nested_loop join 和 hash join
驱动表是用来驱动查询的
在CBO中,优化器会根据cost自动选择驱动表,与表的顺序无关
通常情况下,驱动表的选择性较高(该列唯一键与列的比值较高),where中的限制条件较多,返回的行数较少的表适合做驱动表
事实上,大表有时候也会做驱动表,只要在大表上面限制条件较多,从大表上面返回的行数较少的话,大表也适合做驱动表
始终要记住的就是返回的结果集(某个表被过滤后的数据,或者某些表join 产生的数据)较小的表适合做驱动表
也就是说,哪个返回数据少 ,哪个就做驱动表,即连接操作应从返回较少行上驱动
我们在看执行计划的时候,离nested loops,hash join 近的表就是驱动表,也就是说执行计划中靠上的表是驱动表
以下是几个快速判断驱动表的经验,可能有错哦:
(1)用于连接的子句的列应被索引、在Where子句中应尽量利用索引,而不是避开索引
(2)连接操作应从返回较少行上驱动
(3)如果所连接的表A和B,A表长度远远大于B表,建议从较大的A表上驱动
(4)如果Where子句中含有选择性条件,Where No=20,将最具有选择性部分放在表达式最后
(5)如果只有一个表有索引,另一表无索引,无索引的表通常作为驱动表。如A表的No列以被索引
而B表的No列没被索引,则应当B表作为驱动表,A表作为被驱动表
RULE 下所谓驱动表
我们以 nested loop 为例:
如果两个表连接字段都没有索引(通常这个时候是 sort merge / hash join),则驱动表会选择后者
若两个表其中有一个有索引而另外一个没有索引,则驱动表是没有索引那一个,跟顺序无关
若两个表都有索引,则驱动表为 后面 那一个表
所以事实上,RULE下,只有在两个表都存在连接字段的索引的情况下才需要考虑顺序问题
也就是小表放在后面大表放在前面(当然到底哪个好这实际上还跟符合条件的记录数、数据分布等因素相关!,所以应该以实际测试为准)
而如果是 CBO 下,则跟顺序无关
关键的是明白执行计划,而不在于记住什么规则
例如,表连接返回一条记录
存在2张表,一个10条记录,一个1000万条记录
若2表都存在连接字段索引,
以小表为驱动表,则代价为:
10*(通过索引在大表查询一条记录的代价)
以大表为驱动表,则代价为:
1000万*(通过索引在小表查询一条记录的代价)
通过索引获取一条记录,10rows的表,代价通常在3 blocks,索引2块,表1块
而如果是1000万的表,索引可能达到4块,表1块,速度·掰着脚趾头都知道了哈