表连接

Oracle中一些常见的访问B树索引的方法：

  1. 索引唯一性扫描
  2. 索引范围扫描
  3. 索引全扫描          -->单块读    NOT NULL约束  自带排序
  4. 索引快速全扫描   -->多块读   无法保证结果的顺序   
  5. 索引跳跃式扫描   -->适用前导列 distinct值不多的情形

 表连接的方法：

1.排序合并连接

      • 步骤如下：　　
    1. 首先以目标SQL中指定的谓词条件（如果有的话）去访问T1,然后对访问结果按照表T1中的连接列来排序，排好序后的结果集我们记为1。(因为是对访问结果排序，所以要减少不必要的列)
    2. 接着以目标SQL中指定的谓词条件（如果有的话）去访问T2，然后对访问结果按照表T2中的连接列来排序，排好序后的结果集我们记为2。(因为是对访问结果排序，所以要减少不必要的列)
    3. 最后对结果集1和结果集2执行合并操作，从中取出匹配记录来作为排序合并连接的最终执行结果。
      • 优缺点：

通常情况下，排序合并连接的执行效率远不如哈希连接，但前者的使用范围更广，因为哈希连接通常只能用于等值连接条件，而排序合并连接还能用于其他连接条件（例如<、<=、>、>=）。

两表的连接条件加上索引，只可以消除一边的排序动作。

 

2.嵌套循环连接

两个表在做表连接时依靠两层嵌套循环（分别为外层循环和内层循环）来得到连接结果集的表连接方法。

    • 步骤如下：
    1. 首先，优化器会按照一定的规则来决定T1表和T2表中谁是驱动表、谁是被驱动表。驱动表用于外层循环，被驱动表用于内层循环。这里假设驱动表是T1，被驱动表是T2。
    2. 接着以目标SQL中指定的谓词条件（如果有的话）去访问驱动表T1，访问驱动表T1后得到的结果集记为1.
    3. 然后遍历结果集1并同时遍历被驱动表T2，即先取出驱动结果集1中的第1条记录，接着遍历被驱动表T2并按照连接条件去判断T2中是否存在匹配的记录，然后再取出驱动结果集1中的第2条记录，按照同样的连接条件再去遍历被驱动表T2并判断T2中是否还存在匹配的记录，直到遍历完驱动结果集1中所有的记录为止。这里的外层循环是指遍历驱动结果集1所对应的循环，内层循环是指遍历被驱动表T2所对应的循环。显然，外层循环所对应的驱动结果集1有多少条记录，遍历被驱动表T2的内层循环就要做多少次，这就是所谓的“嵌套循环“的含义。
    • 优缺点：
    1. 如果驱动表所对应的驱动结果集的记录数较少，同时在被驱动表的连接列上又存在唯一性索引（或者在被驱动表的连接列上存在选择性很好的非唯一性索引），那么此时使用嵌套循环连接的执行效率就会非常高：但如果驱动表所对应的驱动结果集的记录数很多，即便在被驱动表的连接列上存在索引，此时使用嵌套循环连接的执行效率也不会高。
    2. 只要驱动结果集的记录数较少，那就具备了做嵌套循环连接的前提条件，而驱动结果集是在对驱动表应用了目标SQL中指定的谓词条件（如果有的话）后得到的结果集，所以大表也可以作为嵌套循环连接的驱动表，关键看目标SQL中指定的谓词条件（如果有的话）能否将驱动结果集的数据量降下来。
    3. 嵌套循环连接有一个其他方法所没有的一个优点：嵌套循环连接可以实现快速响应，即它可以第一时间先返回已经连接过且满足连接条件的记录，而不必等待所有的连接操作全部做完后才返回连接结果。虽然排序合并连接和哈希连接也可以先返回已经连接过且满足连接条件的记录，而不必等待所有的连接操作都做完，但是它们并不是第一时间返回，因为排序合并连接要等到先排序后做合并操作才能开始返回数据，而哈希连接则要等到驱动结果集所对应的Hash Table全部建完后才能开始返回数据。

驱动表的限制条件应该加上索引，被驱动表的连接条件加上索引，被驱动表的限制条件加上索引也用不上。

3.哈希连接

哈希连接（Hash Join）是一种两个表在做表连接时主要依靠哈希运算来得到连接结果集的连接方法。

    • 步骤如下：
    1. Oracle会根据一堆参数的值来决定Hash Partiton的数量（Hash Bucket组成Hash Partition,Hash Partition组成Hash Table）。
    2. 表T1和T2在施加了目标SQL中指定的谓词条件（如果有的话）后，得到的结果集中数据量较少的那个结果集会被Oracle选为哈希连接的驱动结果集，这里假设T1所对应的结果集的数据量相对较少，记为S;T2所对应的结果集的数据量相对较多，记为B。显然这里S是驱动结果集，B是被驱动结果集。
    3. 接着Oracle会遍历S，读取S中的每一条记录，并对每一条记录按照该记录在表T1中的连接列做哈希运算。这个哈希运算会使用两个内置哈希函数，这两个哈希函数会同时对该连接列计算哈希值，我们把这两个内置哈希函数分别记为hash_func_1和hash_func_2，它们所计算出来的哈希值分为记为hash_value_1和hash_value_2。
    4. 然后oracle会按照hash_value_1的值把相应的S中的对应记录存储在不同的Hash Partiton的不同Hash Bucket里，同时和该记录存储在一起的还有该记录用Hash_func_2计算出来的hash_values_2。注意，存储在Hash Bucket里的记录并不是目标表的完整行记录，只需要存储位于目标SQL中与目标表相关的查询列和连接列就足够了。我们把S所对应的每一个Hash Partiton记为Si。
    5. 在构建Si的同时，Oracle会构建一个位图（BITAMP）,这个位图用来标记Si所包含的每一个Hash Bucket是否有记录（即记录数是否大于0）。
    6. 如果S的数据量很大，那么在构建S所对应的Hash Table时，就可能会出现PGA的工作区被填满的情况。这时候Oracle会把工作区中包含记录数最多的Hash Partiton写到磁盘上（TEMP表空间）。接着会继续构建S所对应的Hash Table，在构建的过程中，如果工作区又满了，则Oracle会继续重复上述动作，即挑选包含记录数最多的Hash Partiton并写回到磁盘上。如果要构建的记录所对应的Hash Partition已经事先被Oracle写回磁盘，则此时Oracle就会去磁盘上更新该Hash Partiton，即把该记录和hash_value_2直接加到这个已经位于磁盘上的Hash Partition的相应的Hash Bucket中。注意，极端情况下可能会出现只有某个Hash Partition的部分记录还在内存中，该Hash Partiton的剩余部分和余下的所有Hash Pattiton都已经被写回到磁盘上。
    7. 上述构建S所对应的Hash Table的过程会一直持续下去，直到遍历完S中的所有记录为止。
    8. 接着Oracle会对所有的Si按照它们所包含的记录数来排序，然后把这些已经排好序的Hash Patition按顺序依次且尽可能全部放到内存中（PGA的工作区），当然，如果实在放不下，放不下的那部分Hash Partition还是会位于磁盘上。
    9. 至此Oracle已经处理完S，现在可以开始处理B了。
    10. Oracle会遍历B，读取B中的每一条记录，并按照该记录在表T2中的连接列做哈希运算，这个哈希运算和步骤3中的哈希运算是一摸一样的。即还是会用步骤3中的hash_func_1和hash_func_2，并且也会计算出两个哈希值hash_value_1和hash_value_2。

　　　　　。。。。。剩下的看书吧，太多字了

    • 优缺点：
    1. 哈希连接不一定会排序，或者说大多数情况下都不需要排序。
    2. 哈希连接的驱动表所对应的连接列的可选择性应尽可能好，因此这个可选择性会影响对应的Hash Bucket中的记录数，而Hash Buket中的记录数又会直接影响从该Hash Bulket中查找匹配记录的效率。如果一个Hash Bulket里包含的记录数过多，则可能会严重降低所对应哈希连接的执行效率，此时典型的表现就是该哈希连接执行了很长时间都没结束，数据库所在数据库服务器上的cpu占用率很高，但目标SQL所消耗的逻辑读却很低，因为此时大部分时间都耗费在了遍历上述Hash Bucket里的所有记录上，而遍历Hash Bucket里的记录这个动作发生在PGA的工作区里，所以不耗费逻辑读。
    3. 哈希连接只适用于CBO，它也只能用于等值连接条件（即使是哈希反连接，Oracle实际上也是将其转换成了等价的等值连接）。
    4. 哈希连接很适合于小表和大表之间做表连接且连接结果集的记录数较多的情形，特别是在小表的连接列的可选择性非常好的情况下，这时候哈希连接的执行时间就可以近似看作是和全表扫描那个大表所耗费的时间相当。

自我理解：就是先对驱动表（小表）计算好哈希值并存储在内存或磁盘中，再对被驱动表（大表）的每一行计算哈希值，在遍历这个大表，用哈希值找小表中是否有匹配记录。匹配就返回记录。

驱动表和被驱动表的限制条件都应该加上索引来加快返回的结果集速度（针对索引条件返回记录很少的情况）。

4.笛卡尔积连接

其他关键字：

1.FILTER

FILTER直译过来就是过滤、筛选的意思，它是一种特殊的执行计划，所对应的执行过程就是如下三步。

（1）得到一个驱动结果集。

（2）根据一定的过滤条件从上述驱动结果集中滤除不满足条件的记录。

（3）结果集中剩下的记录就会返回给最终用户或者继续参与下一个执行步骤。

注意：FILTER类型的执行计划实际上是一种改良的嵌套循环连接，它并不像嵌套循环连接那样，驱动结果集中有多少记录就得访问多少次被驱动表

而是驱动表有多少distinct数量就访问被驱动结果集多少次。如下：

 2. CONCAT

CONCAT就是IN-List扩展（IN-List Expansion）或OR扩展（OR Expansion）,IN-List扩展/OR扩展在执行计划中对应的关键字是"CONCATENATION" 。

 

 

 这里的“CONCATENATION”的含义就相当于UNION ALL。

 

反连接： 

  1. HASH JOIN ANTI ：普通哈希反连接（not exists），对NULL值不敏感
  2. HASH JOIN ANTI NA ：改良后的、能够处理NULL值的哈希反连接（not in），就是对NULL值敏感

 

半连接：

　　HASH JOIN SEMI

 

星型连接：事实表和维度表组成

 

第一章总结：

  • 在使用RBO的情况下，我们可以通过调整相关对象在数据字典缓存中的缓存顺序，改变目标SQL中所涉及的各对象在该SQL文本中出现的先后顺序，或者等价改写该SQL来调整其执行计划。
  • 成本是指Oracle根据相关对象的统计信息计算出来的一个值，它实际上代表了Oracle根据相关统计信息估算出来的目标SQL的对应执行路径的I/O、CPU和网络资源的消耗量。
  • 集合的势和可选择率的值会直接影响CBO对于相关执行步骤成本值的估算，进而影响CBO对于目标SQL执行计划的选择。
  • 可传递性的意义在于提供了更多的执行路劲给CBO做选择，增加了走出更高效执行计划的可能性。
  • 优化器的模式对CBO计算成本（进而对CBO选择执行计划）有者决定性的影响。
  • 不是说全表扫描不好，事实上Oracle在做全表扫描操作时会使用多块读，这在目标表的数据量不大时执行效率是非常高的，但全表扫描最大的问题就在于走全表扫描的目标SQL的执行时间会不稳定、不可控，这个执行时间一定会随着目标表数据量的递增而递增。
  • 通过B树索引访问表里行记录的效率并不会随着相关表的数据量的递增而显著降低，即通过走索引访问数据的时间是可控的、基本稳定的，这也是走索引和全表扫描的最大区别。
  • Oracle中索引全表扫描的执行结果是有序的，并且是按照该索引的索引键值列来排序的，这意味着走索引全扫描能够既可达到排序的效果，同时又能避免对该索引的的索引键值列的真正排序操作。另外，Oracle中能做索引全扫描的前提条件是目标索引至少有一个索引键值列的属性是NOT NULL。
  • 索引快速全扫描是可以并行执行的，它的执行结果不一定是有序的。
  • Oracle中的索引跳跃式扫描仅仅适用于那些目标索引的前导列的dsitinct值数量较少、后续非前导列的可选择性又非常好的情形，因为索引跳跃式扫描的执行效率一定会随着目标索引前导列的distinct值数量的递增而递减。
  • 关键字“（+）”出现在哪个表的连接列后面，就表明哪个表会以NULL值来填充那些不满足连接条件并位于该表中的查询列，此时应该以关键字“（+）”对面的表来作为外连接的驱动表，这里的关键是决定哪个表是驱动表。
  • 通常情况下，排序合并连接的执行效率会远不如哈希连接的执行效率高，但排序合并连接的使用范围更广，因此哈希连接只能用于等值连接条件，而排序合并连接还能用于其他连接条件（例如<、<=、>、>=）。
  • 通常情况下，排序合并连接并不适合OLTP类型的系统，其本质原因是对于OLTP类型的系统而言，排序是非常昂贵的操作，当然，如果能避免排序操作，那么即使是OLTP类型的系统，还是可以使用排序合并连接的。
  • 如果驱动表所对应的驱动结果集的记录数较少，同时在驱动表的连接列上又存在唯一性索引（或者在被驱动表的连接列上存在选择性很好的非唯一性索引），那么此时使用嵌套循环连接的执行效率就会非常高；但如果驱动表所对应的驱动结果集的记录数很大，即便在被驱动表的连接列上存在索引，此时使用嵌套循环连接的执行效率也不会高。
  • 大表也可以作为嵌套循环连接的驱动表，关键看目标SQL中指定的谓词条件（如果有的话）能否将驱动结果集的数据量降下来。
  • 哈希连接只适用于CBO，它只能用于等值连接条件（即使是哈希反链接，Oracle实际上也是将其转换成了等价的等值连接）。
  • 哈希连接很适合与小表和大表之间做表连接且连接结果集的记录数较大的情形，特别是在小表的连接列的可选择性非常好的情况下，这时候哈希连接的执行时间就可以近似看作是和全表扫描那个大表所耗费的时间相当。
  • 半连接和普通的内连接不同，半连接实际上会去重。
posted @ 2019-09-27 08:39  数鸭哥  阅读(243)  评论(0)    收藏  举报