|
|
优化器如何优化表连接操作
表连接操作
表连接操作应该是算比较难优化的一种操作类型，
对于表连接操作需要记住的一件事情是，任何一个时候只可能有两个表参加表的连接操作，如果from后面有多于两个表的时候，一般会先选择两个表做连接，然后再把连接的结果继续和另一个表做连接，直到所有的表被连接完为止
优化器对于表连接的决定主要是在于：
表连接的顺序
表连接的方法
中间连接结果的访问路径
优化器连接操作的优化
规则一：任何一个能够通过where条件里的一部分来确定一个结果集的表将被优先连接，比如一个SQL语句：
Select * from a,b,c where a.col1=b.col1 and b.col2=c.col2 and a.col3=:var
则一般而言在以上的例子中a表将被优先连接，因为连接的成本和结果集的大小性能关系很大，所以一旦能够缩小结果集，则该缩小结果集的条件会被优先考虑。
规则二：被外连接的表在连接顺序上优先，比如contries表和customers表，如果你希望能够列出所有的国家和该国家的客户，这个就是一个对于contries的外连接，因为有些国家可能并没有客户，如果以customers来做驱动表，结果将只能列出所有有客户的国家，所以只能用contries来做驱动表，这个应该说甚至不是一种优化的方法，因为不这么执行将不能够得出正确结果，因而只能这么执行。
规则三：from语句后面的表越多，则连接的可能执行计划越多，因此SQL语句的解析时间越长，如两个表，连接可能性就是2！=2，而三个表的连接可能性则是3！=6，随着表的数量的增加，可能的执行计划的数量也大大增加，从而无论是从parse时间或者针对每一个执行计划去计算成本的时间也大大增加。
表连接的方法
把表进行连接的方法主要有三种
Nested Loop：NL连接的方法是，首先需要有一个驱动表（Driving Table或者Outer Table），然后在扫描驱动表的一条记录的时候，再去查找另一个表（又称内部表或inner Table）的相匹配的记录，这样来产生结果集，如下图：
一般而言，驱动表一般使用全表扫描来访问，而内部表则使用连接键上的索引来访问。当连接是非等连接的时候，有可能在内部表上也使用全表扫描从而极大降低性能。 当被连接的数据集比较少而且连接条件可以被用于非常高效地获取第二个表的数据的时候，适合使用NL连接，从NL连接的解释可以看出，从驱动表的一条记录高效地获取内部表的记录的效率是保证NL连接效率的关键，所以表连接的顺序对于NL连接是至关重要的，当你觉得优化器的选择顺序不好的时候，可以使用hint USE_NL(T1,T2)来让执行计划按照你设定的方式执行。
Hash Join：Hash Join的原理是Oracle优化器使用连接键在较小的表上在内存里建立一个哈希表（就像是较小表上建立在连接键上的哈希索引，只不过索引里没有rowid，而是包含了所有的数据）；然后在扫描大表的过程中去根据每一个连接值去内存的哈希表里找到相应的记录进行匹配（大家应该还记得对于“等于”操作而言哈希索引是相当快的操作），从哈希连接的上面的原理上看哈希连接只适用于“等于”连接，当被连接的数据集很大的时候，使用哈希连接相对比于NL连接而言能够得到比较好的性能，可以使用hint USE_HASH来强制优化器选择哈希连接。
Sort Merge Join：Sort Merge连接是另一种比较有意思的连接类型，在某些时候可以很快地完成连接的任务。它首先是需要把被连接的两个表进行排序，然后在排序表上进行连接操作（所以Sort Merge连接最影响性能的部分是排序部分，因为很多时候排序是非常昂贵的操作，比如有可能使用磁盘去缓冲排序的中间结果，以Oracle的专业术语来讲叫做不能进行one-pass的排序性能会比较慢，所以如果可以使用某种机制去掉排序部分，比如使用某个索引，Sort Merge连接的性能就会大大增加），为了清楚说明Sort Merge连接的原理，我们举个例子：假设有两种表T1，T2，连接键是col1，首先Sort Merge连接需要做的是对于T1对于col1形成一个排序后的结果集，然后针对T2形成一个排序后的结果集，假如在我们的结果集里，T1表里col1的值是1～10，T2表里col2的值是20～100，则排序后，按照col1形成的两个结果集之一是以1～10开始，而另一个结果集是以20～100开始，这样无论是寻找两个结果集之间的相等关系还是大于小于关系都是非常容易的，在这个例子里根本不可能有等于关系，因为（1～10）显然不可能等于（20～100），从而就可以很快完成连接的操作，这个是我想出来的说明Sort Merge连接的一个例子，希望能够把Sort Merge说得清楚明白些了。
从上面的例子可以看出，Sort Merge连接在有些时候是非常高效的，特别是对于哈希连接所不能满足的“非等于”类型连接！
最后需要说明的是，对于一个特点的SQL连接语句，很多时候你不能够说使用某种连接手段就一定性能最快，因为连接的性能本身还受非常多参数的影响（比如我们以前说过的PGA_AGG*参数将会影响哈希连接和sort-merge连接的性能等），所以知道这些连接的工作原理，将有助于你对于应该使用什么连接最有效进行判断，但是这个判断不是绝对的，否则优化器就能够自己做出判断了，当你自己对于所用到的表的数据量，数据值的分布，每一个中间结果的大小等知道得越清楚，你就越能够把这些信息以某种方式告诉优化器，从而帮助它选择一个正确的执行计划，这个就是数据库优化的本质！
写到这里，我不知道是否还会继续写这个主题的下一篇文章了，所以我也不知道这是不是文章的结尾？
--------不知道是否还有的“待续” |
|
楼主: NHCS_cn
IP卡
狗仔卡
楼主
显身卡
发表于 2007-12-10 16:06
发表于 2007-12-11 09:11
